dbt (data build tool)¶
Last reviewed: October 2026
Sources: dbt's official documentation (docs.getdbt.com); verify against it before an interview. Content was rephrased for compliance with licensing restrictions.
What's changed recently
- dbt v2 is a Rust rewrite (the engine first previewed as Fusion in
2025). It ships as a single binary using ADBC drivers, parses much faster,
and is stricter: unknown YAML keys, undefined macros or vars, and
missing tests fail at parse time, and
-m/--modelsis gone in favor of-s/--select. dbt v1.x is still supported, and the two can run side by side. - Fivetran and dbt Labs completed their merger in June 2026.
- YAML key: use
data_tests:(introduced in 1.8) rather thantests:for data tests; unit tests are a separateunit_tests:block. - Newer features worth naming: the
microbatchincremental strategy (1.9), YAML-defined snapshots, model contracts and versions, and the dbt Semantic Layer (MetricFlow).
dbt is the T in ELT — analytics engineering with software best practices:
you write SELECT statements as models, dbt handles dependencies, builds
them in the right order, tests them, and documents them. It runs inside your
warehouse (Snowflake, Databricks, BigQuery, etc.).
How dbt works¶
flowchart LR
SRC[(Raw sources)] --> STG[Staging models]
STG --> INT[Intermediate models]
INT --> MART[Marts - facts & dims]
MART --> BI[BI / reporting]
subgraph dbt
REF["ref() & source() build the DAG"]
TEST[Tests]
DOC[Docs + lineage]
end
REF -.orders builds.-> STG
TEST -.validates.-> MART
- You reference other models with
{{ ref('model_name') }}and raw tables with{{ source('schema','table') }}. dbt reads those to build a DAG and runs models in dependency order. dbt runbuilds models,dbt testruns data tests,dbt docs generatebuilds a lineage-aware docs site.
Models & materializations¶
A model is a .sql file containing one SELECT. How it's persisted is the
materialization:
| Materialization | Persisted as | Use when |
|---|---|---|
view |
View | Lightweight, always fresh |
table |
Table (rebuilt) | Faster reads, small/medium data |
incremental |
Table (append/merge new rows) | Large, append-mostly data |
ephemeral |
Inlined CTE | Reusable logic, no object |
-- models/marts/fct_orders.sql
{{ config(materialized='incremental', unique_key='order_id') }}
SELECT
o.order_id,
o.customer_id,
o.order_date,
o.amount
FROM {{ ref('stg_orders') }} o
{% if is_incremental() %}
WHERE o.order_date > (SELECT MAX(order_date) FROM {{ this }})
{% endif %}
Tests¶
Catch data-quality issues in CI. Built-in generic tests + custom ones:
# models/marts/schema.yml
models:
- name: fct_orders
columns:
- name: order_id
data_tests: [unique, not_null]
- name: customer_id
data_tests:
- relationships:
to: ref('dim_customers')
field: customer_id
Sources, seeds, macros, snapshots¶
- Sources — declare raw tables, add freshness checks.
- Seeds — small CSVs versioned in the repo (lookups).
- Macros — reusable Jinja SQL (DRY); packages like
dbt_utils. - Snapshots — dbt's built-in SCD Type 2 to track history over time.
-- A simple macro
{% macro cents_to_dollars(col) %}
({{ col }} / 100.0)::numeric(16,2)
{% endmacro %}
Why teams adopt dbt¶
Version control, modular SQL, automated testing, auto-generated docs with lineage, and environment promotion (dev → prod) — bringing software engineering discipline to the transformation layer.
Interview questions¶
What problem does dbt solve?
It brings engineering rigor to SQL transformations: dependency management via
ref(), testing, documentation/lineage, and reproducible builds — the
governed T in ELT, run inside the warehouse.
ref() vs source()?
source() points at raw external tables (and enables freshness checks);
ref() points at other dbt models and is what builds the DAG and build order.
When would you use an incremental model?
For large, append-mostly tables where rebuilding fully is expensive — process
only new/changed rows using is_incremental() and a unique_key for merges.
How does dbt handle slowly changing dimensions?
Snapshots implement SCD Type 2 — dbt detects changes and maintains valid-from/valid-to history automatically.
Interview deep dive¶
60-second talking points¶
- "dbt is the governed T in ELT." SQL
SELECTs become versioned, tested, documented models; dbt manages dependencies and build order viaref(). - "
ref()builds the DAG." That single function gives you lineage, ordering, and environment-safe table names for free. - "Tests + docs make transformations trustworthy." Data quality is enforced in CI, and lineage docs are auto-generated.
Scenario & system-design questions¶
A nightly full rebuild of a 2-billion-row fact table takes hours. Fix it.
Switch to an incremental model with a unique_key and an
is_incremental() filter on a load/updated timestamp so only new/changed rows
are processed and merged. Consider partition/cluster hints for the warehouse
and periodic --full-refresh to correct drift.
How do you promote changes from dev to prod safely with dbt?
Separate targets/profiles per environment; develop in a dev schema, run
dbt build (models + tests) in CI on a PR, and only merge/deploy to prod
on green. Use slim CI (state:modified) to build just what changed.
How would you track history of a dimension that changes over time?
A dbt snapshot implements SCD Type 2 — dbt detects changes on a key and
maintains dbt_valid_from/dbt_valid_to, so you can query the dimension as of
any point in time.
A model passed but downstream numbers are wrong. How do tests help?
Add generic tests (unique, not_null, relationships,
accepted_values) and singular tests (custom SQL that returns failing
rows). Test at the grain — e.g. assert one row per key — to catch fan-out
from a bad join early.
Pitfalls interviewers probe¶
- Hardcoding table names instead of
ref()/source()→ breaks lineage and envs. - Overusing
tablematerialization whereview/incrementalfits (cost/time). - Incremental models without a
unique_key→ duplicates. - No tests → silent data-quality regressions.
- Business logic duplicated across models instead of macros/intermediate models.
Rapid-fire¶
| Q | A |
|---|---|
ref() vs source()? |
ref() = other models (builds DAG); source() = raw tables (+ freshness) |
| Materializations? | view, table, incremental, ephemeral |
| SCD Type 2 in dbt? | Snapshots |
| Generic vs singular test? | Generic = reusable (unique/not_null); singular = custom SQL |
What does dbt build do? |
Runs models + tests + snapshots + seeds in DAG order |
| dbt v2? | Rust engine (ex-Fusion): faster parse, stricter validation, --select only |
| Time-sliced incremental? | microbatch strategy (1.9+) |
Deep dive: how dbt actually builds¶
The mental model behind ref() that senior interviews probe.
flowchart LR
PARSE[Parse project: compile Jinja] --> GRAPH[Build DAG from ref/source]
GRAPH --> COMPILE[Compile each model to raw SQL]
COMPILE --> RUN[Execute in dependency order]
RUN --> TEST[Run tests]
RUN --> ARTIFACTS[manifest.json + run_results.json]
- Compile then run — dbt renders Jinja into plain SQL (
target/compiled/…) before executing. When debugging, read the compiled SQL, not the template. ref()does three jobs — builds the DAG (order), resolves the correct schema/database per target (dev vs prod), and enables lineage. Never hardcode a table name.- Artifacts —
manifest.json(the full graph + metadata) andrun_results.json(what ran, timings, pass/fail) power docs, slim CI, and observability tooling.
Materialization decision tree¶
| If the model is… | Use | Why |
|---|---|---|
| Small, cheap, must be fresh | view |
No storage, always current |
| Reused a lot, moderate size | table |
Fast reads, rebuilt each run |
| Large, append-mostly | incremental |
Process only new/changed rows |
| Just reusable logic | ephemeral |
Inlined as a CTE, no object |
Incremental strategies matter: append (facts, no updates), merge
(upsert on unique_key), delete+insert, insert_overwrite (partition
swap), and microbatch (1.9+; processes time-bounded batches on an
event_time column, which makes backfills and retries per batch easy). Wrong
strategy = duplicates or full rewrites.
Testing, contracts & data quality¶
Beyond unique/not_null:
- Generic tests —
unique,not_null,relationships,accepted_values, plusdbt_utils/dbt_expectations(row counts, ranges, freshness, expression checks). - Singular tests — a
.sqlfile that returns failing rows; use for business rules ("revenue is never negative"). - Unit tests — assert a model's SQL logic against mock inputs, so you catch transformation bugs without full data.
- Model contracts — enforce column names, types, and
not_null/uniqueconstraints at build time; a downstream consumer's schema can't silently break. - Test at the grain — the highest-value test is "one row per key"; it catches fan-out from a bad join, the #1 silent data bug.
models:
- name: fct_orders
config:
contract: {enforced: true}
columns:
- name: order_id
data_type: number
constraints: [{type: not_null}, {type: unique}]
Environments, CI/CD & performance¶
- Targets/profiles isolate dev vs prod (separate schemas/databases); the
same
ref()resolves correctly per environment. - Slim CI —
dbt build --select state:modified+builds only changed models and their children against a deferred prod manifest, so PR checks are fast. - Deploy on green — run
dbt build(models + tests + snapshots + seeds) in CI; merge/deploy only when tests pass. This is DevOps for AI/data discipline applied to the transformation layer — Kiro-assisted authoring, Jenkins/GitHub Actions running the build. - Performance — prefer
incrementalfor big facts; push heavy logic into the warehouse (dbt is just SQL); avoid unnecessarytablerebuilds; use warehouse-native clustering/partitioning; keep models single-responsibility so the DAG parallelizes.
Analytics-engineering soundbite
"dbt turns SQL into software: version-controlled, tested, documented models
with a DAG from ref(). In CI I run slim builds on just what changed, and
contracts stop a schema change from silently breaking a downstream mart."
Governance & observability¶
- Docs + lineage —
dbt docs generatebuilds a searchable, column-lineage site from the manifest; it's the "what feeds this and who uses it" answer. - Source freshness —
dbt source freshnesswarns/errors when raw data is stale, catching upstream pipeline failures early. - Exposures — declare downstream dashboards/apps so lineage extends past dbt and you know the blast radius of a change.
- Ownership + meta — tag models with owner/domain for accountability.
More system-design & production incidents¶
Each: diagnose → mitigate → prevent.
An incremental model is producing duplicate rows. Root cause?
Almost always a missing or wrong unique_key, or an is_incremental()
filter that lets overlapping rows in. Mitigate: set the correct
unique_key with a merge strategy and --full-refresh once to clean up.
Prevent: a unique test on the grain so CI catches recurrence.
A dashboard number silently went wrong after a PR. How should this have been caught?
A fan-out from a many-to-many join inflated the grain. Prevent: a
grain test (unique on the key), relationships tests, and model
contracts so column/type/constraint changes fail the build. Read the
compiled SQL to confirm the join.
Upstream data was late and the whole run built on stale data. Fix the process.
Diagnose: no freshness gate. Mitigate/prevent: add
dbt source freshness as a pre-run check that errors on stale sources,
and gate the build on it so you don't publish stale marts.
The nightly run is taking too long and blocking morning dashboards. Speed it up.
Convert big full-rebuild table models to incremental; split monolithic
models so the DAG parallelizes; use slim CI so only changed models rebuild
on PRs; move the schedule earlier or add warehouse concurrency. Profile with
run_results.json to find the slowest models.
How do you safely refactor a heavily-used model without breaking consumers?
Add a model contract to lock the interface, use exposures to see who depends on it, ship behind tests, and deploy via CI on green. If the shape must change, version the model and deprecate the old one.
60-second ramp checklist¶
- Explain
ref()'s three jobs (DAG, env resolution, lineage). - Pick a materialization and incremental strategy for a given case.
- Name generic vs singular vs unit tests and what a grain test catches.
- Describe model contracts and why they matter.
- Explain slim CI (
state:modified+) and deploy-on-green. - Use source freshness + exposures for governance.
- Debug a duplicate-rows and a silent-fan-out incident out loud.
Related in this site¶
- Snowflake · Databricks — the warehouses dbt runs inside.
- Data Engineering Interview Q&A · dbt Interview Q&A.
- DevOps for AI — CI/CD patterns that apply to dbt.
How interviewers probe this¶
You're asked to move a 1,500-model project to dbt v2. How do you plan it?
A strong answer covers: clearing all deprecation warnings on v1.x first, running the v2 parser in compatibility mode, using automated fix tooling, checking that every package supports v2 and every adapter has a v2 driver, running v1 and v2 side by side on the same manifest, comparing results, and cutting over environment by environment.
Design the dbt layer for a data mesh with several domain teams.
Separate projects per domain with public, contracted, versioned models as
interfaces, cross-project refs to those public models only, owners and
groups on each model, CI that builds only what changed plus its dependents,
and exposures to track consumers. Explain the governance trade-off against a
single monorepo.
A backfill of two years of events through an incremental model keeps timing out.
Use microbatch with event_time so the backfill runs as bounded batches
that can retry independently, or run a targeted --full-refresh with an
explicit date range. Size the warehouse for the backfill, and check that the
unique_key and lookback window handle late-arriving data.
How do you test dbt models without a full copy of production data?
Unit tests with mocked inputs for transformation logic, data tests on sampled or cloned (zero-copy) data in CI, contracts for interface shape, and deferral to production artifacts so CI only builds what changed.