dbt Models in the Lake#

SQL transformations are a dbt project in dbt/. dbt-duckdb runs each model, and every model becomes a Delta table in the lake, in its layer (D12): staging/, intermediate/ or marts/. The same project and SQL run against SeaweedFS (on-prem) and S3 (AWS). Only the lake the run points at changes (plan2 D13). Dagster runs dbt; each model is an asset, and each dbt test is an asset check.

How it works#

DuckDB reads Delta but can’t write it (Table Format: Delta Lake vs Apache Iceberg), so the project bridges the gap in three places:

Piece

File

What it does

delta materialization

dbt/macros/delta.sql

Builds the model as a DuckDB table, then hands it to the lake plugin. It is the project default (+materialized: delta).

lake plugin

dagster_platform/transform/plugin.py

store() writes the table with delta-rs (full overwrite; partition_by passes through). It also creates the DuckDB S3 secret for the run’s lake.

register_upstream_delta_models

dbt/macros/delta.sql, run on-run-start

Each run starts with an empty in-memory DuckDB. For models the run reads but doesn’t build, it creates a view over their Delta table (delta_scan).

Sources written outside dbt (dlt in raw/, Python assets in staging/) are declared in dbt/models/sources.yml and read with delta_scan too.

Schemas are layers. +schema: marts gives the schema marts, not dbt’s default main_marts (dbt/macros/schema.sql). A model models/marts/daily_revenue.sql is therefore the table s3://<bucket>/marts/daily_revenue and the Dagster asset marts/daily_revenue.

Targets. dbt/profiles.yml has onprem and aws, both an in-memory DuckDB. The Dagster asset picks the target named by the lake resource (LAKE_TARGET) and passes the lake (bucket, endpoint, keys) to dbt through the environment, because DbtCliResource.cli has no env argument.

Dagster#

dagster_platform/transform/assets.py loads the manifest into one @dbt_assets definition, dbt_lake, with a translator that:

  • keys models and sources [<schema>, <table>], matching every other lake table;

  • takes the D14 fields from meta.ods (owners, domain, sensitivity). Project-wide defaults live in dbt_project.yml; a model’s own meta.ods overrides them. Each column’s meta.sensitivity/meta.pii becomes a column tag;

  • declares every column from the model’s YAML (data_type, description) as the column schema.

After dbt writes a model, the asset adds the same runtime metadata as the Delta I/O manager: row count, URI and Delta version. Marts also get the declared_schema check, which compares the declared columns with the Delta table.

The image parses the manifest at build time (onprem/core/docker/Dockerfile), so the code server starts without running dbt. A checkout without a manifest parses on first import.

Adding a model#

  1. Write dbt/models/<layer>/<name>.sql, following the D12 naming (stg_<source>__<entity>, int_…, a plain name in marts).

  2. Add <name>.yml with a description and every column’s data_type and description. Marts must declare all columns; the definitions test enforces it.

  3. Override meta.ods if the owners, domain or sensitivity differ from the defaults.

  4. Add dbt tests (data_tests:). They become Dagster asset checks.

  5. Run tests/run.sh tests/definitions tests/transform.

Running dbt by hand#

From a shell in the image, with the lake environment set (LAKE_BUCKET, LAKE_ENDPOINT_URL, AWS_ACCESS_KEY_ID, AWS_SECRET_ACCESS_KEY):

cd /opt/dagster/app/dbt
dbt build --profiles-dir . --target onprem --select marts.daily_revenue

Prefer materializing through Dagster, which records the run, the metadata and the checks.

Limits#

  • Models are rewritten in full on every run. Incremental models (merge through DeltaTable.merge) are future work (docs/table_format.md).

  • Python models are not supported by the delta materialization.

  • The aws target authenticates with static keys today. The instance/task-role path (PROVIDER credential_chain) is written but untested until Phase 1.