dbt
Transformation as version-controlled SQL: models referencing each other, materializations, tests, snapshots and Jinja macros, all executed inside the warehouse. Interviewers ask about dbt because it is how analytics engineering brought software practice to SQL.
on this pageshowhide
guide
overview
~1 mindbt is the transformation layer of the modern warehouse stack: a project of SQL `SELECT` statements, templated with Jinja, that dbt compiles, orders by their dependencies and runs inside the warehouse you already have. Interviewers ask about it because it is where analytics engineering adopted software habits — version control, review, tests, environments — and each habit has a dbt mechanism precise enough to probe. The hub follows a project from its inputs to its guarantees. [Sources and seeds](/topics/data-dbt-sources-seeds) declare what the project reads but does not build. [Models and refs](/topics/data-dbt-models-refs) turn a folder of queries into a dependency graph. [Materializations](/topics/data-dbt-materializations) decide what each model becomes in the warehouse. [Tests and documentation](/topics/data-dbt-tests-docs) are how a project stays trustworthy. [Jinja and macros](/topics/data-dbt-jinja-macros) are the templating that makes reuse possible and debugging harder. [Snapshots and SCD](/topics/data-dbt-snapshots) keep history a mutable source throws away. Junior rounds check the vocabulary: what a model is, why `ref()` instead of a table name, what the built-in tests assert. Senior and principal rounds turn to failure stories — duplicates from an incremental model, history lost from a snapshot, a stale source feeding a green run — and to design questions about how much templating and ownership structure a project should carry. Start with models and refs, because every other section assumes the graph; then sources, materializations and tests, and the rest after.
primer
### A model is a query, not a table Each model is one `SELECT` in its own file. You do not write the `CREATE` statement; the configured materialization supplies the surrounding DDL. That split is the point: the logic stays reviewable as plain SQL, and how it lands in the warehouse is a setting you can change without touching it. ### The graph comes from ref and source dbt does not infer order from table names in your SQL. Dependencies exist only where a node calls `ref()` or `source()`, and that DAG drives build order, environment-aware naming, selection, lineage and docs. A dependency written as a literal name is invisible to all of these at once, which is why interviewers treat `ref()` as the first idea to check. ### Compile first, run second Jinja is rendered before the warehouse sees anything. So the SQL you debug is the compiled output, not the source file, and template code that queries the warehouse must cope with a parse phase in which no query runs. ### Materialization is a cost trade The same query can be a view, a table, an incremental build or an inlined CTE. Each moves cost between build time, read time and freshness. Incremental is the expensive choice in engineering effort: correctness then rests on a filter, a key and a strategy, and a wrong one fails silently. ### Tests are queries that look for bad rows A dbt test is a query whose result is the evidence of failure. Generic tests are reusable and declared in YAML; singular ones are one-off SQL. What you assert, and what a failure should block, are design decisions. ### Snapshots hold state the source does not A model can be rebuilt from its inputs; a snapshot cannot, because the history it recorded exists nowhere else. Treat it as data to protect, not a derived table.
- Model
- A single SELECT statement in a .sql file that dbt compiles and builds into a warehouse object named after the file.
- ref()
- The Jinja function a model uses to name another dbt-built node. It records a graph dependency and resolves to the relation for the active target.
- source()
- The function that names a raw table declared in YAML: something the project reads but never builds, and the entry point of its lineage.
- DAG
- The directed acyclic graph of models, sources, seeds, snapshots and tests that dbt derives from ref and source calls and uses to order every run.
- Materialization
- The strategy that decides what a model becomes in the warehouse: view, table, incremental or ephemeral, plus custom ones a project or adapter defines.
- Incremental model
- A model that, after its first build, processes only new or changed rows and merges or appends them into the existing table.
- Seed
- A small CSV kept in the project and loaded into the warehouse as a table, for static reference data the team itself maintains.
- Generic test
- A parameterised test defined once as a macro and applied to models and columns from YAML, such as unique or not_null.
- Macro
- A reusable block of Jinja, defined under macros/, that renders into SQL text wherever it is called.
- Snapshot
- A dbt resource that records changes to mutable source rows over time as a type-2 slowly changing dimension table.
- Target
- The named output in a connection profile that a run uses, such as dev or prod. It decides which database and schema refs and builds resolve to.
- Adapter
- The plugin that teaches dbt one warehouse's SQL dialect and connection, and supplies its materialization and incremental-strategy implementations.
Follow one `dbt build`. dbt first parses the project, turning every `ref()` and `source()` call into a graph edge and writing the manifest; selection then picks the part of the graph to run. For each selected node, in dependency order, it renders the Jinja for the active target, adds the statement its materialization needs, executes it in the warehouse, and runs that node's tests before moving downstream. The sections attach to that path at different points: - **Sources and seeds** are where the graph starts: sources are declared, seeds are loaded from CSV, and source freshness is a separate check on the raw side. - **Models and refs** form the interior of the graph, and selection syntax walks it upstream or downstream. - **Materializations** act at execution time, choosing the statement that wraps each compiled query. - **Jinja and macros** act at compile time, before the warehouse sees anything. - **Tests** run after the node they guard, and in `dbt build` a failure stops its descendants. - **Snapshots** sit beside models, usually reading sources and feeding models, but carrying their own state. Most of these meet in one YAML file per folder, where sources, model configuration and tests are declared side by side: ```yaml sources: - name: shop tables: - name: raw_orders loaded_at_field: _loaded_at models: - name: fct_orders config: materialized: incremental unique_key: order_id columns: - name: order_id data_tests: [unique, not_null] ``` The SQL holds the logic; the YAML holds the contract around it. A strong answer moves between the two and says which one a given guarantee lives in.
- Models and Refs →
The core abstraction: what a model is and how ref builds the dependency graph every other section assumes.
- Sources and Seeds →
Where the graph begins: declaring raw inputs, checking their freshness, and when small reference data belongs in the repository.
- Materializations →
What each model becomes in the warehouse, and the correctness traps that come with incremental builds.
- Tests and Documentation →
How a project proves its data is sound, what a failing test should block, and how the docs site is produced.
- Jinja and Macros →
Templating is easier to judge once you can read plain models; this covers macros, packages and where to stop.
- Snapshots and SCD →
History capture builds on sources and on SCD2 modelling, and its failure modes are the costliest to recover from.
Hard-coding a schema-qualified table name in a model: the build order, the environment switch and the lineage all lose that edge without any error.
Recommending incremental for every large model without naming the filter, the
unique_keyand the strategy, the three places where duplicates and lost rows come from.Assuming an incremental filter on the newest loaded timestamp catches everything; rows that arrive late but carry older dates slip past it without any failure.
Treating a snapshot like a model that can be rebuilt; dropping it throws away history no later run can recreate.
Debugging the Jinja source instead of reading the compiled SQL, which is what the warehouse actually executed.
Deleting a noisy test to unblock a pipeline instead of adjusting its severity or thresholds and storing its failures for investigation.
Assuming a dbt run checks source freshness on its own; stale inputs still produce a green build unless something gates them.
This guide assumes dbt Core 1.9 or later. A few questions depend on when a feature arrived, because many projects and most older write-ups predate it: - **0.21** introduced `dbt build`, running models, tests, seeds and snapshots together in graph order. Before it, a separate run followed by a test was the norm, which is why the difference still gets asked. - **1.5** added model governance: access levels, groups, contracts and model versions. - **1.8** renamed the YAML `tests:` key to `data_tests:` (the old key still works) and added unit tests on fixed inputs. - **1.9** let snapshots be configured in YAML instead of a Jinja block, made handling of rows deleted from the source an explicit setting, and added the microbatch incremental strategy. When an answer depends on one of these, say which version you mean.
dbt is the T in ELT: it assumes data is already loaded into a warehouse or lakehouse and transforms it there with that engine's compute. Loading belongs to an ingestion tool or pipeline. Scheduling usually belongs to an orchestrator such as Airflow, Dagster or Prefect, which triggers dbt, sequences it after ingestion and can gate it on freshness. A candidate should be able to say which layer owns which failure. dbt Labs, its maker, ships the open-source dbt Core command-line tool and a hosted commercial platform around it. dbt replaced hand-maintained stored procedures and scheduled SQL scripts, whose dependencies and tests lived in people's heads. Its closest alternative is SQLMesh, which also treats SQL transformations as versioned code; others are the transformation features built into individual data platforms. The trade an interviewer listens for is portability and a shared package ecosystem against tighter integration with one engine.
explore
- Q-06977In dbt, why should a model use ref('stg_orders') instead of a hard-coded table name?level: juniorimportance: must knowfreq 90%
- Q-06976In dbt, what is a model, and what does a model's .sql file actually contain?level: juniorimportance: must knowfreq 88%
- Q-06971In dbt, what is the difference between the view and table materializations?level: juniorimportance: must knowfreq 85%
- Models and Refs6 questions
- Materializations6 questions
- Sources and Seeds7 questions
- Tests and Documentation7 questions
- Jinja and Macros6 questions
- Snapshots and SCD6 questions
questions
page 2 of 2How do you decide how much Jinja templating a dbt project should allow in its models?
basics
~20 sAllow Jinja that removes duplication without hiding what a model does: shared expressions, environment switches, config. Ban templating that decides a model's grain, joins or output columns, because reviewers then read a template while the warehouse runs something else.
When should reference data live as a dbt seed rather than a pipeline-loaded table?
basics
~20 sSeed it when the analytics team is the system of record and the data is small, slow-moving and non-sensitive, so a pull request is the right change process. Ingest it when an upstream system already owns the data or it changes often.
In dbt, what does the ephemeral materialization do to a model's compiled SQL?
basics
~20 sAn ephemeral dbt model creates no object in the warehouse. Its SQL is interpolated as a common table expression into every downstream model that refs it, so the logic is reusable in dbt but not queryable outside it.
In a dbt snapshot table, what does the dbt_scd_id column identify?
basics
~20 sdbt_scd_id is a generated hash that uniquely identifies one version row in a dbt snapshot — the entity's unique_key combined with the timestamp that version starts at. It is dbt's internal row identity, not a business key.
A dbt seed of ZIP codes loads as integers and loses leading zeros — how do you fix it?
basics
~20 sdbt infers seed column types from the CSV contents, so an all-digit column becomes numeric. Pin it with a column_types config naming the column as a string type, then run dbt seed with full refresh so the table is dropped and recreated.
In dbt, how do you write a custom generic test that can be reused from schema YAML?
basics
~20 sDefine a Jinja block with the test tag taking model and column_name arguments, save it under tests/generic or macros, and return the failing rows. Any model's YAML can then declare it by name and pass extra arguments.
In dbt, how does adapter.dispatch let one macro work across Snowflake and BigQuery?
basics
~10 sadapter.dispatch resolves a macro name to an adapter-prefixed implementation at compile time: on Snowflake it looks for snowflake__my_macro and falls back to default__my_macro. You write one entry point plus one implementation per warehouse.
In dbt, what do model access levels and groups control about who can ref() a model?
basics
~20 sAccess marks a model private, protected or public. Private models can only be referenced from within their own group, protected ones from within the project, and public ones are the declared interface other projects may reference — turning the graph into an ownership boundary.
showing 31–38 of 38