dbt Interview Questions

ref() is the backbone of dbt. At parse time dbt records every ref('stg_orders') as an edge in the project DAG, which lets it (1) build models in dependency order — stg_orders is guaranteed to exist before any model that refs it — and (2) resolve the relation to the right database/schema for the active target, so the same code points at your dev schema locally and the prod schema in deployment. A hard-coded analytics.stg_orders has neither property: dbt can't see the dependency, so it may build in the wrong order, and the name is frozen to one environment. ref() does no caching and doesn't re-run upstream models (b, d are wrong); it isn't cosmetic (c). It compiles to a fully-qualified relation name, but the value is the dependency graph and environment-aware resolution behind it.

A view materialization issues a CREATE VIEW — it stores only the query definition, not the result set. Building it is near-instant and uses no storage, and it always reflects current upstream data, but every downstream select re-executes the underlying SQL, so reads of an expensive transformation are slow and repeated. A table materialization runs CREATE TABLE AS SELECT, persisting the computed rows: builds cost compute and storage and the data is only as fresh as the last dbt run, but reads are a cheap scan of stored results. The rule of thumb: views for light staging/transient models that are read infrequently, tables for expensive logic read by many downstream consumers or BI tools. Option b mislabels freshness (views are more live, not stale); c and d are simply false.

is_incremental() returns true only when the target table already exists and the run is not a --full-refresh. On such runs dbt compiles the where clause so the model selects only source rows newer than the max timestamp already persisted ({{ this }} refers to the model's own existing relation), then applies the configured incremental_strategy (append, merge, delete+insert, etc.) to fold just those new rows into the existing table. That is the entire point of incremental models: process a small recent slice instead of re-transforming the full history every run, which is essential for large event/fact tables. On the first run (or under --full-refresh) is_incremental() is false, the filter is omitted, and the table is built from all rows. Options b and c describe a table materialization or a destructive rebuild; d is wrong because the function is true on subsequent runs.

An ephemeral model is never materialized as a view or table — dbt builds no relation for it at all. Instead, when a downstream model refs it, dbt interpolates the ephemeral model's compiled SQL as a common table expression (CTE) at the top of that downstream model's query. This keeps tidy, reusable logic out of the warehouse namespace and avoids cluttering it with trivial intermediate objects. The limitations follow directly: because there's no object, you can't query the ephemeral model directly (in a BI tool or ad-hoc), tests on it run only in the context of consumers, and deeply nested ephemeral chains can produce large, hard-to-debug compiled CTE stacks. Option b describes a temp table (not how ephemeral works), c invents a hidden view, and d is false — the logic does run, just inlined.

dbt build is a single DAG-aware command that executes seeds, snapshots, models, and tests together in dependency order, interleaving them node-by-node (a, c). Crucially, after building a node it runs the tests attached to that node before proceeding downstream, so a failing test causes dbt to skip the dependent models rather than build them on top of bad data (b). That early-stop behaviour is the main reason build is preferred in CI/production over a separate dbt run followed by dbt test — in the split workflow, every model is built first, so failing tests are discovered only after bad data has already been written and propagated downstream. Option d is wrong (build very much runs tests), and e is wrong (it strictly respects the DAG, never alphabetical order).

True. Source freshness is a property you declare on source definitions in a .yml file, not on models. dbt looks at a loaded_at_field column (or, on supported warehouses, table metadata) to find the most recent load time for each source table, then compares the gap to now against the warn_after and error_after thresholds you configure, emitting a warning or error when raw data has gone stale. This catches a broken or late upstream ingestion (an ETL job that stopped running) before you waste a build transforming stale data — a common pattern is to run dbt source freshness at the start of a pipeline and abort if it errors. It says nothing about whether your dbt models are up to date; that depends on when you last ran them. Because it targets sources, it has no notion of ref() — it is checking the inputs to your project, not its internal nodes.

dbt merges configuration from multiple layers with a clear precedence: a config() block inside the model itself is the most specific and wins over properties.yml configs, which win over dbt_project.yml directory-level configs, which win over project defaults. Here the model's own config(materialized='table') is more specific than the marts: +materialized: view set in dbt_project.yml, so the model is built as a table. dbt does not error on a 'conflict' (option c) — layered config with overrides is the intended design, letting you set a sensible default for a whole folder and override the exceptions per model. Options b and d invert or invent the rule. The practical takeaway: set broad defaults in dbt_project.yml, then reach for an in-model config() block only for the specific models that need to differ.

This is the canonical Jinja-in-dbt pattern: {% set %} defines a list, and the {% for %} loop fans it out into one aggregate column per element. Each iteration renders sum(case when method = '<pm>' then amount end) as <pm>_amount, substituting the loop value. The {% if not loop.last %},{% endif %} guard emits a comma after every column except the final one — loop.last is true only on the last iteration — so you get a clean comma-separated list with no dangling trailing comma before from (which would be a SQL syntax error, the trap in option b). The result is two pivot-style columns, card_amount and cash_amount. Option c misunderstands that Jinja renders the loop variable; d is wrong because {% set %} lists are fully supported. This loop.last comma trick is one of the most common reasons people use Jinja in dbt at all.

unique and not_null are built-in generic tests. dbt compiles each into a select that returns the failing rows — not_null returns rows where id is null, unique returns values appearing more than once via a group by ... having count(*) > 1. A generic test passes when that query returns zero rows and fails when it returns one or more. With both a NULL and a duplicate present, the two tests run independently and each fails, reporting its own offending rows; dbt does not stop after the first failure (b) — every selected test runs so you see the full picture. The tests check values, not just types (c is wrong), and a column may carry any number of tests (d is wrong). Generic tests are the cheapest, most common data-quality guardrail in dbt; dbt build would additionally use these failures to skip downstream models.

The merge (and delete+insert) strategy needs a unique_key to know which existing rows a new batch should update. Without it, dbt has no match condition, so merge degenerates into an insert-only operation: when a previously-seen order_id arrives with a newer updated_at, it passes the updated_at > max filter and is appended as a brand-new row instead of overwriting the old one — hence the duplicates. The fix is to add unique_key='order_id' to the config so the merge updates the matching row in place (and inserts genuinely new orders). Option b is wrong — is_incremental() is correct here, gating the filter so it only applies on incremental runs. Option c is false: {{ this }} is the standard self-reference to the model's existing relation. Option d is false: merge is a first-class incremental strategy on warehouses that support it. The general lesson: an incremental model that must keep one row per entity is incomplete without a unique_key.

Snapshots exist because source systems mutate rows in place — an order's status flips from 'pending' to 'shipped' and the old value is lost. A snapshot persists each version as a row with validity columns (dbt_valid_from/dbt_valid_to), giving you SCD type 2 history you can query as-of any point in time. The timestamp strategy detects change by watching a reliable updated_at column — newer timestamp means a new version — and is cheap and preferred. The check strategy diffs the values of named (or all) columns between source and the latest snapshot row, opening a new version on any difference; it's the fallback when no trustworthy update timestamp exists. A strong answer names SCD type 2, the validity columns, and the timestamp-vs-check distinction.

Slim CI rests on two features. State comparison: dbt's manifest.json is a full description of the project, so saving the production manifest as a baseline lets --select state:modified --state <prod-manifest> pick out only nodes whose definition changed (and, with +, their downstream dependents) — you build the diff, not the whole project. Deferral: a changed model still refs unchanged upstream models that were never built in the ephemeral CI schema; --defer makes those refs resolve to the production relations instead, so the modified models can read real upstream data without rebuilding it. Together they cut CI time and warehouse cost dramatically. A strong answer names the manifest baseline, state:modified, and how --defer redirects unbuilt refs to prod.

Seeds turn small, static CSVs in seeds/ into warehouse tables via dbt seed, version-controlled and usable downstream with ref(). The interview point is knowing the fit: good for small business-owned lookup/mapping tables (country codes, exclusion lists, category mappings), bad for large or raw transactional data, which belongs in source()-declared tables loaded by an EL tool. A common follow-up contrasts seeds (ref) with sources (source): both are inputs, but seeds are tiny git-tracked CSVs you own, while sources are externally-loaded raw tables you only point at. The 'would a human edit this in a spreadsheet?' heuristic is the tell of real understanding.

A dbt invocation is a pipeline. First dbt parses the whole project — reading every model, macro, and yml file and rendering Jinja enough to discover each ref()/source() call (this is where the manifest of nodes is assembled). It then builds the dependency graph (DAG) from those resolved references and topologically sorts it to decide execution order. Next it compiles the selected models, fully rendering their Jinja into plain SQL written under target/compiled/ — at this point there is concrete SQL but nothing has touched the warehouse yet. Only then does it execute that SQL against the database, materializing models in dependency order so upstream tables exist before downstream ones run. Finally it writes artifacts (manifest.json, run_results.json) capturing the graph and per-node timing/status, which power docs, state comparison, and slim CI. Mixing up parse-before-compile or execute-before-compile is the common mistake; SQL must be compiled before it can run.

ref() and source() are the two ways a dbt model declares an input, and both add the dependency to the DAG. ref('model_name') points at another model in your project — dbt builds it first and resolves it to the right schema/database per target. source('source_name', 'table_name') points at a raw, externally-loaded table that you've declared under a sources: block in a .yml file; the two arguments are the source group and the table within it. The discipline is to wrap every raw table in a source() (giving you a single place to document it, test it, and check freshness) and to chain all model-to-model dependencies through ref() — never hard-code a database table name in a model, or you lose dependency tracking and environment-aware resolution.