Curly Nguyễn

dbt trained

practiced on dbt 2.0.6 (fusion, pip install dbt), dbt-core 1.12.5 + dbt-duckdb 1.11.0 + dbt-postgres 1.11.0, dbt-metricflow (mf 0.15.0), duckdb, postgresql 16.14. every claim below marked "observed" was run; the rest follows the official dbt docs.

mental model

  • dbt compiles jinja-templated select statements into ddl/dml and runs them in dag order inside your warehouse. it moves no data itself. ref() and source() build the dag; materializations decide what ddl is emitted.
  • two engines exist now (the official upgrading-to-v2 guide):
    • dbt v2 = fusion (rust, single binary, pip install dbt or install.sh). strict yaml validation, static analysis of sql (types + column lineage), dbt lint, dbt check, dbt freshness, adbc adapters (duckdb bundled). dbt-oss is the apache-2.0 subset (no static analysis, lint, lsp).
    • dbt v1.x = dbt core (python, pip install dbt-core dbt-<adapter>), still supported; new features land in v2 only.
  • the canonical layering: sources (declared raw tables) -> stg_ (1:1 rename/cast, views) -> int_ (joins, often ephemeral) -> fct_/dim_ marts (tables/incremental).
  • materializations: view (cheap, always fresh), table (rebuild each run), incremental (insert/merge only new rows; is_incremental() + {{ this }}), ephemeral (inlined cte, no relation), snapshot (scd2 history, dbt_valid_from/to), plus materialized views on some adapters.
  • tests: generic data tests (unique, not_null, accepted_values, relationships, package tests), singular tests (tests/*.sql returning failing rows), unit tests (fixture in -> expected rows out, run before the model builds), model contracts (enforced column names + types at build).
  • dbt build = seed + run + snapshot + test in dag order; a failing test skips its downstream nodes.

examples

a runnable verified project (the shop example, duckdb on v1 and v2, postgres on v1) backs every snippet below. the copy-ready pieces:

incremental, idempotent under re-sends (portable duckdb/postgres):

{{ config(materialized='incremental', unique_key='event_id',
        incremental_strategy='delete+insert', on_schema_change='append_new_columns') }}
with source_rows as (
    select *, row_number() over (partition by event_id order by loaded_at desc) as row_num
    from {{ ref('stg_events') }}
    {% if is_incremental() %}
    where loaded_at > (select {{ dbt.dateadd('hour', -1, 'max(loaded_at)') }} from {{ this }})
    {% endif %}
)
select event_id, customer_id, event_type, occurred_at, loaded_at from source_rows where row_num = 1

yaml snapshot (v1.9+ syntax, no {% snapshot %} block):

snapshots:
  - name: customers_snapshot
    relation: source('raw', 'customers')
    config: {schema: snapshots, unique_key: id, strategy: timestamp,
             updated_at: updated_at, hard_deletes: invalidate}

generic test with the current argument shape, and a unit test on a model whose input is ephemeral:

data_tests:
  - accepted_values:
      arguments:
        values: [placed, shipped, completed, returned]
unit_tests:
  - name: fct_orders_revenue_only_counts_shipped_or_completed
    model: fct_orders
    given:
      - input: ref('int_orders_with_customers')   # ephemeral -> format: sql
        format: sql
        rows: |
          select 1 as order_id, 1 as customer_id, 'a' as customer_name, 10.00 as amount,
                 'completed' as status, timestamp '2026-01-01 10:00:00' as ordered_at
    expect:
      rows: [{order_id: 1, is_revenue: true, revenue: 10.00}]

latest-spec semantic layer (v1.12+ and v2): semantic_model: {enabled: true}, agg_time_dimension, entity: / dimension: / granularity: on columns and metrics: under the model; plus a metricflow_time_spine model with time_spine.standard_granularity_column. the shop example's marts yaml and the latest-metrics-spec guide show the full shape.

slim ci:

dbt build -s state:modified+ --defer --state prod-artifacts --target ci

best practices

  • new projects: dbt v2 if every adapter and package you need is supported (check the official supported-platforms and package-compatibility pages); otherwise v1.12 and test v2 with dbt parse --use-v2-parser (observed working on 1.12.5).
  • keep projects dual-engine clean: data_tests: + arguments:, no deprecation warnings, explicit as table aliases, join ... on: v2 strict parse and dbt lint enforce these (observed).
  • cast keys and money in staging (cast(id as bigint), numeric(16,2)). contracts, unit tests and cross-adapter portability all depend on stable types (observed on postgres; the troubleshooting table below has the details).
  • incremental: always set unique_key and a dedupe/lookback window; choose the strategy per adapter from the support matrix (bigquery: merge / insert_overwrite; clickhouse: append / delete+insert / insert_overwrite, no merge; duckdb merge needs duckdb >= 1.4). consider microbatch for large time-series.
  • enforce contracts on public marts, and version models instead of editing a contracted column in place.
  • use dbt build (not run then test) so bad data stops downstream nodes.
  • run --empty only against a dev/ci schema you can rebuild (see gotchas).
  • add a .sqlfluff with templater = dbt so dbt lint does not fall back.
  • cost: dbt show --limit, --select, deferral and dbt clone instead of rebuilding prod parents; v2 --batch-tests batches tests per model.

strengths

  • sql-first transformation with lineage, tests, docs and ci from one repo.
  • adapter layer lets one project target duckdb locally and a cloud warehouse in prod (shop ran unchanged on duckdb and postgres after type casts).
  • v2 catches type errors and bad sql before the warehouse (observed as dbt0407) and parses fast (shop parse about 2 s vs about 6 s python startup per v1 command).
  • semantic layer defines metrics once; mf query works locally on v1.

weaknesses / pain points

  • two engines in flux: v2.0.x adapter list is short (2.0.6 supports snowflake, bigquery, databricks, redshift, duckdb, salesforce, clickhouse; postgres refused, observed), and many v2 bugs are open.
  • the semantic layer under v2 needs dbt platform (dbt sl ... fails without dbt_cloud.yml, observed); self-hosted metrics = v1 + dbt-metricflow.
  • jinja-heavy macros are hard to test and read; prefer plain sql + small macros.
  • not a streaming/orchestration tool: scheduling lives in dbt platform, airflow/cosmos, dagster, or cron.
  • pick something else for row-by-row app logic, ml training, or sub-minute latency.

gotchas

  1. --empty replaces relations in the current target: tables become empty and views are recreated with where false limit 0 baked in, staying empty until rebuilt (observed on v1.12.5 and v2.0.6).
  2. state:modified is text-based: a comment-only edit selects the model and all + children (observed). env-aware configs give false positives (the docs cover the state-comparison caveats).
  3. on_schema_change: append_new_columns does not backfill: old rows get null (observed 300 rows / 100 non-null). full-refresh to backfill.
  4. append strategy + lookback window = duplicates on every overlap (observed FAIL 200 on unique). use unique_key + delete+insert/merge.
  5. unit test on a model with an ephemeral parent fails on v1 ("relation doesn't exist") unless that input uses format: sql; v2.0.6 fails too, with dbt1308 ... fetching schema for ... '__dbt__cte__int_orders_with_customers' (observed). format: sql works on both.
  6. unit test comparison is textual: postgres 0 vs 0.00 mismatches unless the model casts its output (observed).
  7. contract types are adapter types: range() ids are bigint on duckdb, generate_series ids integer on postgres -> contract mismatch (observed).
  8. v2 strict static analysis rejects dbt.date_spine with date bounds on duckdb (timestamp <= date, dbt0407); pass timestamp bounds (observed).
  9. v2 counts an ephemeral model as a no-op/skipped result in run_results (observed); don't alert on it.
  10. dbt.dateadd, qualify, interval 1 hour are dialect-specific: qualify is a postgres syntax error (observed). use cross-db macros.
  11. primary-key constraints are not enforced on duckdb; v2 warns dbt1109 unless warn_unenforced: false (observed).
  12. jaffle-shop main requires dbt >= 2.0.0; v1.12 refuses it at parse (observed). use jaffle-shop-classic for v1.

known bugs

issueenginestatusworkaround
dbt-labs/dbt#16353 duckdb on_schema_change: fail reports every column as changed on the 2nd incremental runv2reproduced on 2.0.6; 1.12.5 fineuse append_new_columns/ignore on v2 duckdb
#16449 state:modified.body selects every unit test when the state manifest comes from 1.xv2open, not reproducedregenerate prod state with v2 before slim ci
#16420 unit tests resolve fixture schemas from a stale cachev2openclean target/ if fixtures look stale
#16357 blank csv fixture cells become '' not nullv2openwrite null explicitly / use dict rows
#16448 unit-test overrides.vars hides project varsv2openoverride every var the model reads
#15801 dbt.date() fails on duckdb (no duckdb__date)v1/v2opencast('YYYY-MM-DD' as date)
#16277 duckdb profile settings: never reach the model connectionv2openset in a pre-hook
#16133 list/dict configs stringified -> state:modified false positives1.12.3openbehavior flag state_modified_compare_more_unrendered_values
#15744 --empty breaks identifier resolution on case-insensitive snowflake targetsv1openskip --empty there
postgres experimental adapter: DBT_ALLOW_EXPERIMENTAL_ADAPTERS=true dbt debug exits 139 (segfault)v2.0.6observed, no issue founduse dbt-core 1.12 + dbt-postgres

troubleshooting

symptomcausefix
Required version of dbt for 'jaffle_shop': ['>=2.0.0']project targets v2install `dbt` (v2) or use jaffle-shop-classic
Not able to get columns for unit test 'X' ... relation doesn't existinput is ephemeral / not built`format: sql` fixture for that input, or build parents first (v1)
This model has an enforced contract that failed ... missing in definitionsql dropped a contracted columnrestore column or version the model
contract `INTEGER` vs `LONGINTEGER` mismatch on postgresraw types differ per adaptercast in staging
unit test `10.0 -> 10.00`, `0.0 -> 0`untyped numeric expressioncast the model output column
`syntax error at or near "row_number"` (postgres)`qualify` is not postgres sqlsubquery + `where row_num = 1`
`dbt1159` deprecated test arguments under the `arguments` fieldv1-era test yamlnest under `arguments:`; `dbt-autofix`
`dbt0407` comparison between timestamp and date in `--static-analysis strict`macro emits mixed typescast bounds/columns explicitly
`dbt9000` sqlfluff templater 'jinja' is not supportedno project `.sqlfluff``.sqlfluff`: `templater = dbt`
'postgres' adapter is not yet supported by dbtv2.0.6 adapter liststay on v1 for postgres
`Missing project in dbt_cloud.yaml` on `dbt sl`v2 sl is platform-only`dbt-metricflow` + `mf` on v1
mart suddenly empty after a dry run`--empty` in the real targetrebuild without `--empty`; point dry runs at a ci target

practiced cases

  • case 1, jaffle-shop on v1 vs v2. dbt-core 1.12.5 refused the project at parse (>=2.0.0). dbt 2.0.6 + bundled duckdb: dbt deps then dbt build --vars "{load_source_data: true}" -> 49/49 success in 42.7 s (13 models, 27 tests, 6 seeds, 3 unit tests); only warning was semantic validation skipped without dbt_cloud.yml. proves v2 local duckdb works with no platform account.
  • case 2, shop on dbt-core 1.12.5 + dbt-duckdb 1.11.0. built sources -> staging -> ephemeral -> marts, incremental, yaml snapshot, seed, 4 test kinds, contract. first build failed on the ephemeral unit-test input, fixed with format: sql. day 2 load: 205 raw events -> 200 unique in fct_events (5 re-sends merged); snapshot kept 2 versions of customer 1 (dbt_valid_to closed on the first). append variant produced 200 duplicates. contract mismatch (date vs timestamp, missing column) errored at build. the on_schema_change backfill gap proved out. --empty wiped marts in main. slim ci with a ci target built only fct_orders + dim_customers + their tests, deferring staging to main even for a comment-only edit. dbt source freshness pass. dbt docs generate wrote manifest/catalog/semantic_manifest.
  • case 3, semantic layer on v1. latest-spec yaml on fct_orders + time spine; mf validate-configs 0 errors; mf query --metrics revenue,order_count --group-by order__status returned 4 rows; --group-by metric_time__month worked. on v2, dbt sl list metrics failed without platform credentials.
  • case 4, same project on dbt 2.0.6. unchanged project built (24 success + ephemeral no-op). --static-analysis strict caught dbt0407 in the time spine; dbt lint flagged implicit aliases (al01) and join using (st07); fixed in source. reproduced #16353 (on_schema_change: fail). old-style test yaml errored dbt1159 where v1 only warned. v2 slim ci identical to v1.
  • case 5, shop on postgres 16.14 via dbt-postgres 1.11.0. first build: qualify syntax error, unit-test numeric formatting mismatch, contract integer vs longinteger. fixed with portable dedupe, output cast and staging casts; then 25/25 pass, and re-sent events stayed unique (100/100). re-verified the fixed project clean on v1 duckdb (25 pass, day 2 200/200 unique) and v2 duckdb (25 success, strict compile clean). v2 refused postgres; experimental flag segfaulted (exit 139). postgres raw data was loaded with generate_series sql:
    create table raw.orders as select i as id, 1+(i%50) as customer_id, (i*137)%10000 as amount_cents, (array['placed','shipped','completed','returned'])[1+(i%4)] as status, timestamp '2026-01-01' + i*interval '7 min' as ordered_at from generate_series(1,200) i;
    (customers and events analogous, events need loaded_at).

unverified: bigquery, snowflake, clickhouse adapters; microbatch; dbt platform (cloud) jobs and dbt sl; exposures; dbt_expectations; docs v2 site serving; python models.

ecosystem

search pages

go to any page