DBT Sync to Cubes v4

[cliftonc]

I think it would be an amazing idea if we can point (either CLI or otherwise) at a DBT config, e.g. in a remote github repo, and have it automatically generate all of the required cube files for Drizzle-Cube. This would either be a one off or something that could be run as a sync, so perhaps easiest to build it into the CLI first, and then we can explore how to automate it from there (e.g. a github action).

[last-light]

Agent Brief

Why this needs human implementation: This is a substantial product/design integration. It needs maintainer decisions on the dbt source contract, generated cube API shape, remote repository/auth handling, and whether recurring sync/GitHub Action automation belongs in the initial scope.

Category: enhancement
Summary: Add a dbt-to-Drizzle-Cube generation/sync path, likely starting as a CLI command.

Current behavior:
Drizzle-Cube does not have a documented workflow for pointing at a dbt project/config and generating the corresponding cube definitions.

Desired behavior:
A user should be able to provide a dbt project/config source, including a remote GitHub repository if supported by the chosen design, and generate the required Drizzle-Cube cube files. The initial feature should support a one-off CLI generation flow; recurring sync/automation can be designed around that foundation.

Key interfaces:

• CLI command/config — accepts the dbt project source, output location, and any options needed to control generation.
• dbt metadata input — define which dbt artifacts/config files are supported and how models/relationships/measures map to cube files.
• Generated cube files — deterministic output suitable for committing and re-running without unnecessary churn.

Acceptance criteria:

• Maintainers agree on the supported dbt input contract and model-to-cube mapping semantics.
• The CLI can generate Drizzle-Cube cube files from a supported dbt project/config source.
• Re-running generation is deterministic and either updates existing generated files safely or clearly reports conflicts.
• Tests or fixtures cover at least one representative dbt project and validate the generated cube output.
• Documentation explains the CLI usage, supported dbt features, limitations, and the intended path toward future sync/GitHub Action automation.

Out of scope:

• Building a GitHub Action before the CLI contract is validated.
• Supporting every dbt adapter or dbt feature in the first iteration.
• Replacing hand-written cube definitions or changing the existing runtime query behavior.

[cliftonc]

@last-light lets explore possible designs to solve this together

[last-light]

🤖 explore for #936

Branch: lastlight/936-issue-936

• ✅ Read existing context — Cloning cliftonc/drizzle-cube and reading the codebase...
• ✅ Socratic questioning
• ✅ Synthesize spec — Writing up the spec from our conversation...
• ✅ Publish spec — Spec published: DBT Sync to Cubes v4 #936 (comment)

[last-light]

Should the first version be artifact-first and local-only: drizzle-cube dbt generate --manifest target/manifest.json --catalog target/catalog.json --schema ./src/db/schema.ts --out ./src/cubes/generated?

I’d recommend yes: manifest.json/catalog.json give us a deterministic dbt graph without adding YAML/Jinja/dbt-runtime/git/network handling to the currently minimal parseArgs CLI. Remote GitHub input can then be a later wrapper once the generation contract is proven.
↳ If that sounds right, I’d treat raw dbt YAML parsing, running dbt, and remote clone/auth as explicitly out of scope for v1.

---

Just reply to this thread with your answers — no need to @mention me. I'll keep going until we have enough to write this up.
Say we're done at any point to jump straight to the spec draft.

[cliftonc]

Yes; lets build the core logic processing, we can add the github sync later.

[last-light]

For v1, should the dbt → Drizzle mapping be an explicit generator config with convention-based defaults, rather than trying to introspect the TypeScript schema?

I’d recommend: require --schema ./src/db/schema.ts plus a small config/mapping layer where dbt model names default to lower-camel Drizzle exports (orders → orders) and dbt column names default to lower-camel properties (customer_id → customerId), with explicit overrides for mismatches. The reason is that cube output must reference real Drizzle table/column symbols, while manifest.json/catalog.json only know SQL relation/column names; guessing silently would generate broken TypeScript.

↳ If that sounds right, I’d make unmapped/missing schema references a hard generation error by default, with maybe --allow-placeholders later for skeleton output.

---

Just reply to this thread with your answers — no need to @mention me. I'll keep going until we have enough to write this up.
Say we're done at any point to jump straight to the spec draft.

[cliftonc]

We need to assume that there is no schema, and we need to create it as well based on the DBT structure.

[last-light]

Since v1 now needs to generate the Drizzle schema too, should we make catalog.json mandatory and scope schema emission to an explicit dialect first?

I’d recommend: require --manifest, --catalog, and --dialect postgres for v1, and fail if catalog/type metadata is missing. The repo’s own example schema is Postgres-specific (pgTable, integer, text, real, boolean, timestamp, jsonb from drizzle-orm/pg-core), and manifest.json alone won’t reliably tell us which Drizzle column builders to emit.

↳ If that’s right, I’d treat MySQL/SQLite/Snowflake/etc. schema generation as later dialect adapters rather than trying a lossy cross-dialect mapper immediately.

---

Just reply to this thread with your answers — no need to @mention me. I'll keep going until we have enough to write this up.
Say we're done at any point to jump straight to the spec draft.

[cliftonc]

Yes; lets start with just postgres if that simplifies.

[last-light]

For generated cubes, should v1 be fail-closed on security: require the user to choose either:

• --security rls to emit no cube-level where, assuming DB-level RLS is configured, or
• a config mapping like security: { column: "organisation_id", context: "organisationId" } to emit where: eq(table.organisationId, ctx.securityContext.organisationId)?

I’d recommend yes. The existing examples put tenant filters directly in every cube, while adapter options also support conscious RLS mode; guessing from dbt column names would be risky, and generating insecure cubes by default would violate the project’s security-first pattern.

---

Just reply to this thread with your answers — no need to @mention me. I'll keep going until we have enough to write this up.
Say we're done at any point to jump straight to the spec draft.

[cliftonc]

The CLI should ask (e.g. what is the column name for tenant / organisation filtering?) - and allow an empty answer, so no security.

[last-light]

Should v1 generate cubes/schema only for dbt models that materialize to a physical Postgres relation (table, view, incremental, etc.), and skip ephemeral models plus sources/seeds/snapshots?

I’d recommend yes for the first slice: defineCube(...).sql() needs a real Drizzle from relation, and dbt ephemeral models don’t have one; limiting v1 to materialized models keeps schema generation deterministic. Sources/seeds/snapshots can be added later behind explicit flags once the core model flow works.

---

Just reply to this thread with your answers — no need to @mention me. I'll keep going until we have enough to write this up.
Say we're done at any point to jump straight to the spec draft.

[cliftonc]

yes; only materialised things