dbt Interview Questions

"dbt is the transform step in an ELT setup. The data is already loaded into the warehouse by some other tool, and dbt lets me write each transformation as a select statement in its own file. It adds Jinja on top, so I can reference other models, reuse logic in macros and change behaviour by environment. When I run it, dbt compiles everything to plain SQL, works out the order from the references, and runs it inside the warehouse, so the warehouse does the heavy lifting. Around that it gives me tests, documentation and lineage, all in version control. What it doesn't do is extract or load data, and it isn't a general scheduler for the whole pipeline. Something else moves the data in, and usually something else triggers the dbt run."

"dbt run builds models only. dbt test runs the tests against whatever is already built. dbt build does everything in one pass, in dependency order: seeds, models, snapshots and tests. The big difference is how failures flow. With build, each model's tests run right after that model, and if a test fails, dbt skips everything downstream of it, so bad data doesn't spread into the marts. If I schedule run and then test as two steps, every model has already been rebuilt by the time a test fails, and dashboards may already be showing bad numbers. So in production I schedule dbt build, often with a selector for one group of models. I use run on its own mostly in development, when I'm iterating quickly on a single model."

"Seeds are CSV files kept in the project's seeds folder. Running dbt seed loads them into the warehouse as tables, and models reference them with ref like any other model. They're for small, static reference data that belongs with the code and changes rarely: a mapping of country codes to regions, a list of test accounts to exclude, a lookup of product categories. Because they live in git, every change is reviewed and versioned. What shouldn't go in them: large data, because loading CSVs through dbt is slow and not what it's built for; data that changes often or comes from another system, which the ingestion tool should load as a source; and anything sensitive like personal data or passwords, because it ends up in the repository. I also set column types when needed, so a code like 007 isn't read as the number 7."

"I list the package in packages.yml with a version range, then run dbt deps, which downloads it into the project so its macros and tests are available. dbt_utils is the one almost every project has. I use generate_surrogate_key to build a hashed key from several columns, date_spine to make a continuous calendar, star to select all columns except a few, and extra tests like unique_combination_of_columns for tables whose key is more than one column. I always pin to a version range rather than taking whatever is latest, because an upgrade can change how a macro behaves, and I'd rather upgrade on purpose and test it in CI. I also keep the number of packages small. Every package is code I'm trusting inside my build, so I check it's maintained before adding it."

"The four I use most are view, table, incremental and ephemeral, and view is the default. A view is cheap to build and always fresh, but the query runs every time someone reads it, so I use views for light staging models. A table stores the result, so reads are fast, but each run rebuilds it fully. That suits marts people query a lot, as long as the rebuild is affordable. Incremental builds the table once and then only processes new or changed rows on later runs, which I reach for when the data is large and a full rebuild takes too long. Ephemeral isn't built in the warehouse at all; dbt pastes it into the models that use it as a CTE. I use that sparingly, because you can't query it directly when debugging."

"I use three layers. Staging has one model per source table, and it only does light cleanup: renaming columns to a consistent style, casting types, maybe converting units. No joins and no business logic, and it's the only layer that reads from source. Intermediate is where the harder logic lives: joins, deduplication and reshaping that several marts need but no business user should query directly. Marts are the finished tables people use, facts and dimensions or wide tables for a team like finance, named in business language. The payoff is that each layer has one job. When a number looks wrong I know where to look, and a renamed column in a raw table only touches one staging model. I usually make staging views and marts tables or incremental, set at folder level in the project file."

"In my last role I took over a project with around two hundred models, and most marts read straight from raw tables with hard-coded names. Nobody trusted the lineage, and one upstream rename broke a dozen models. I didn't try to rewrite it all. First I declared the raw tables as sources and added staging models for the ones used most, so each raw table was read in one place. Then I replaced hard-coded names with ref and source, which fixed the build order and gave us a real lineage graph. Next I added unique and not_null tests on every mart's key, and that immediately found two models with duplicate rows from a bad join. Only after that did I start merging models that repeated the same logic. I did it in small pull requests over a couple of months, comparing row counts and key totals before and after each change."

"ref does two jobs. First, it tells dbt that this model depends on another one, so dbt can build the dependency graph, run things in the right order and show lineage in the docs. If I hard-code a table name, dbt has no idea that dependency exists, so it might run my model before its parent is rebuilt. Second, ref resolves to the right database and schema for whichever environment I'm running in. In development it points at my personal schema, in production at the production schema, and I never change the code. It's also what makes selecting a model plus everything downstream work. The rule I follow is simple: raw tables come in through source, everything built by dbt goes through ref, and there are no hard-coded names in model SQL."

"I declare raw tables in a YAML file under a sources block: a source name, the schema where the loader writes, and the list of tables. Models then read them with the source function instead of a hard-coded name, so raw tables show up in the lineage graph and I can test them like any model. For staleness, I set a loaded_at_field, which is a timestamp column the loader fills, and freshness rules with a warn_after and an error_after. Running dbt source freshness compares the newest value in that column with those rules. I schedule that before the main build. So if the loader silently stopped overnight, we hear about it from a warning or a failed job, not from a stakeholder asking why yesterday's numbers are flat. I usually add not_null and unique on the source's key as well."

"A data test checks the real data after a model is built, like unique or not_null on a key. It tells me the data is wrong, but not whether my SQL is right. A unit test checks the logic. In YAML I give the model a few fixed input rows for the models it reads, and the exact rows I expect out. dbt runs the model's SQL on those rows and compares the result. With dbt build, a model's unit tests run before the model itself, so broken logic never reaches the table. I don't write them for simple renames. They're worth it where logic is easy to get wrong: a case statement with many rules, date math around month ends, a window function, or an edge case that bit us before, like a refunded order. Because the inputs are fixed, I run them in development and CI and usually leave them out of production runs."

"dbt ships four generic tests: unique, not_null, accepted_values and relationships. I add them in the model's YAML file under the column they check. unique and not_null together are how I state a primary key, and I put them on every model's key. accepted_values checks a column only holds a known list, like order statuses. relationships checks every value exists in another model, which is a foreign key check: for example, every customer_id on orders exists in the customers dimension. Behind the scenes, each test compiles to a query that selects the rows breaking the rule, and the test passes when that query returns nothing. So when a test fails, I can take the compiled query and see exactly which rows are the problem."

"There are two ways. A singular test is a SQL file in the tests folder that selects the bad rows for one specific check; if it returns rows, it fails. That's fine for a one-off rule. When the same rule applies to many columns, I write a custom generic test. It's a test block, a lot like a macro, that takes the model and the column name as arguments and returns the failing rows. Once it's in the project, I can attach not_negative in YAML to any column, exactly like unique or not_null. The thing to remember is that the logic is inverted: I write the query that finds violations, not the one that finds good rows. Before writing my own, I check packages like dbt_utils or dbt_expectations, because common rules are often already there."

"First I'd check whether the failures are real, because a test that fails daily and gets ignored is worse than no test. If the handful of bad rows is known and acceptable, I use the test config. Severity can be error or warn: a warning shows in the logs but doesn't fail the run or skip downstream models. There are also thresholds, warn_if and error_if, compared against the number of failing rows. So I can say warn above ten bad rows and error above five hundred, which lets small noise through but stops the build if something really breaks. I also turn on store_failures for tests like this, so the failing rows are saved to a table and whoever is on call can look at them without rerunning anything. And every warning still needs an owner, or it becomes background noise."

"I write descriptions in the same YAML files where the tests live, for each model and its important columns. For longer text, like how a revenue metric is defined, I use a doc block in a markdown file and reference it from the YAML, so one definition is reused everywhere. Then dbt docs generate builds the documentation from the project and from the warehouse's catalog, which adds the column types, and dbt docs serve lets me browse it locally, or we host the site. What people get is a searchable site with every model, its description, columns, tests, the SQL and the lineage graph showing what feeds it and what depends on it. The lineage is the part analysts use most. I also declare exposures for key dashboards, so the graph shows which dashboards a model change could hit."

"At my last company we had a relationships test from the orders fact to the customers dimension. One morning it failed on a few thousand rows. Because we ran dbt build, the marts downstream were skipped, so dashboards still showed yesterday's correct data instead of broken numbers. I pulled the failing rows with the compiled test query and saw they were all new customers from one region. The source team had started adding a prefix to IDs for new sign-ups, so our join no longer matched. I told the dashboard owners the data would be a few hours late and why, worked with the source team, and changed the staging model to handle both formats. Afterwards I added a pattern test on the raw ID column from a testing package, so a format change now fails at the source layer, where it's easiest to spot."

"First I'd check what the failure actually stopped. With dbt build, the orders model itself was already rebuilt with the duplicates, but everything downstream was skipped. If the dashboard reads a downstream mart, it still shows yesterday's data, stale but correct, which buys time. If it reads the orders model directly, it's already wrong and I warn people now. Then I'd pull the duplicate keys with the compiled test query and look for the cause. Often it's a source loaded twice or a join that fans out. If the fix is small and clear, I fix it, rebuild the model and everything downstream with the plus selector, and let the tests confirm it. If it isn't clear by around eight, I'd tell the dashboard owners which numbers to trust and when to expect an update, rather than letting them find out. What I wouldn't do is switch the test off to push the run through, because then leadership reads double-counted revenue."

"I set the materialization to incremental and give it a unique key. The first time it runs, the table doesn't exist yet, so is_incremental is false and dbt builds the whole table from the full select. On later runs is_incremental is true, so the filter kicks in: I only select events newer than the latest event_at already in the target, which I reach through this. dbt then loads those rows into the table, and because event_id is the unique key, a row that comes through twice replaces the old copy instead of duplicating. is_incremental is also false when I run with full-refresh, which is how I rebuild after a logic change. The weak spot is late data. Anything arriving with an older timestamp gets skipped, so in practice I'd add a lookback window of a few days."

"Which ones you get depends on the adapter, but the ideas are the same. Append just inserts the new rows. It's the cheapest, but with no unique key a rerun or an overlapping window creates duplicates, so I only use it for true append-only logs. Merge matches on the unique key, updates existing rows and inserts new ones. It's the usual choice when rows change, but on a huge target the merge can scan a lot unless I limit it. Delete+insert deletes target rows whose keys appear in the new batch, then inserts the batch, which can be faster than merge on some warehouses. Insert_overwrite replaces whole partitions on warehouses that support it, so I can reprocess the last few days completely, as long as each batch holds every row for those partitions. Newer versions also add microbatch, which processes the data in fixed time slices. I choose by how the data changes, the table size and what the warehouse does well."

"By default nothing obvious happens, which is the trap. on_schema_change defaults to ignore, so the incremental run doesn't add the new column to the existing table; it's just silently not there. I have a few options. append_new_columns adds new columns to the target. sync_all_columns adds new ones and also drops columns that were removed from the model. Or fail stops the run so someone decides on purpose. What none of them does is fill in the new column for rows that were loaded earlier. Those old rows just have nulls. So if history matters, I run the model with full-refresh, or a targeted backfill if the table is too big to rebuild. On models other teams depend on, I lean towards fail, because a silent schema change is how dashboards quietly break."

"First, size the damage. I'd compare the incremental table with a fresh query over the source for the affected weeks, day by day, so I know how many rows are missing and which reports are off. Then I fix the root cause in the model. The filter uses the max event timestamp, so anything that arrives late with an older timestamp gets skipped. I'd filter on the load timestamp instead, or add a lookback of a few days with a unique key so reprocessed rows merge instead of duplicating. Then the backfill. If the table is small enough, a full refresh is cleanest. If not, I'd reprocess only the affected date range, using a variable that widens the window for one run. Then I'd compare totals against the source again and send finance a short note: what was wrong, which days changed and what stops it happening again."

"A snapshot keeps the history of a table that only stores its current state, which is a type 2 slowly changing dimension. Each time dbt snapshot runs, it compares the source rows with what's already captured. If a row changed, dbt closes the old version by setting its dbt_valid_to and inserts a new version with a fresh dbt_valid_from. By default the current version is the one where dbt_valid_to is null. To detect changes there are two strategies. Timestamp uses an updated_at column: if it moved forward, the row changed. It's efficient and my first choice when the source keeps that column reliably. Check compares a list of columns, or all of them, which I use when there's no trustworthy updated_at. It's more work for the warehouse, and if I list columns, a change in a column I left out won't be caught."

"The biggest one is that a snapshot only sees the source as it looks when the snapshot runs. If a row changes three times between daily runs, I only capture the last state, so I'm honest with stakeholders about how fine the history is. Second, snapshots can't be rebuilt. Unlike a model, if the table is dropped the history is gone, so I keep it in its own schema, restrict who can drop it, and make sure it's covered by backups. Third, hard deletes. By default a row deleted in the source just stays current for ever, so I turn on the snapshot setting that closes out deleted rows when that matters. I also snapshot raw sources with as little logic as possible, because if I snapshot a transformed model and the logic changes later, the history ends up mixed. And I test that there's exactly one current row per key."

"Macros are Jinja functions that return SQL text. A common case is pivoting, like counting orders per customer for each status. Instead of writing a case expression per status in every model, I write a macro that takes the column and a list of values, loops over the list and emits one sum per value, adding a comma after every item except the last. In the model, I call it inside the select. When dbt compiles, the call is replaced by the generated SQL, so the warehouse only ever sees plain SQL, and I can check the result in the compiled folder under target. I keep macros for logic that's really repeated. If a macro is only used once, I'd rather keep the SQL in the model, because heavy Jinja makes models harder for the next person to read."

"That's dbt's default behaviour, not a bug. Schema names come from a macro called generate_schema_name. If a model has no custom schema, it goes into the target's schema. If it has one, dbt joins the two: target schema, an underscore, then the custom schema. The reason is safety. In development every developer has their own target schema, so their marketing models land in something like dbt_sam_marketing and never overwrite each other or production. To change it, I override that macro in my own project. The common pattern is: in production, use the custom schema on its own; everywhere else, keep the prefixed name. I decide what production is by checking target.name. After the change I compile and check where things will land before the first production run, because a wrong schema macro can write over tables people use."

"dbt works in two phases. First it parses the whole project to build the graph, and during that phase it renders the Jinja but doesn't actually run queries against the warehouse. A variable called execute is false while parsing and true when dbt is really running or compiling nodes. So if my macro calls run_query and immediately reads the results, like taking the first column's values, it fails at parse time, because there are no results yet. The fix is to wrap anything that reads the results in an if execute check, with a harmless default like an empty list. I'd also ask whether a runtime query is needed at all. It adds a warehouse round trip on every compile and makes the SQL depend on the data. A fixed list or a seed is often simpler, and dbt_utils has get_column_values if it must be dynamic."

"The idea is usually called slim CI. Every production run leaves a manifest.json, a full description of the project as deployed. In CI I fetch that production manifest and pass it with the state flag. Then selecting state:modified plus picks the models whose code or config changed in this pull request, plus everything downstream of them, and dbt build runs and tests just those. The defer flag handles the rest: when a changed model refs something that wasn't rebuilt, dbt points that ref at the production object instead of failing. CI writes into its own schema, usually named after the pull request, and I drop it when the PR closes. On top of that I'd run a SQL linter and require the CI job to pass before merging. It keeps CI fast and cheap while still catching a change that breaks a model three steps downstream."

"dbt connects through a profile, and each profile has targets, like dev, ci and prod, each with its own credentials, schema and number of threads. With dbt Core that lives in profiles.yml, outside the repo, so secrets aren't committed, and I read passwords with env_var. The trick for isolation is the target schema. Each developer's dev target has their own schema, like dbt_sam, so when I run a model it builds there and can't touch production or anyone else's work. CI gets its own schema per pull request. Only the production job, running with a service account, writes to the production schemas. Inside models I can check target.name when something has to differ, for example limiting dev to the last few days of data so builds are quick. I keep those branches rare, because code that behaves differently per environment is harder to trust."

"At my last company, analysts pushed dbt changes straight to the main branch, and production broke a few times a month. Rather than announcing new rules, I started by showing the cost: I listed the recent incidents and how long each took to find. Then I set up a CI job that built only the changed models into a temporary schema, and I kept it fast, a few minutes, because slow CI is the quickest way to lose people. I wrote a short guide with examples of key tests and paired with two analysts on their first pull requests. For the first month the CI result was advice, not a blocker, so people got used to it. Once they saw it catch real mistakes, turning on required checks was an easy conversation, and production breaks dropped to almost none that quarter."

"I'd start by finding who uses it. The lineage graph shows downstream models, and if we've declared exposures for dashboards they show up there too. For anything else, I'd search the BI tool or the warehouse's query history for the old column name. Then I'd avoid a hard cut-over. The simple safe route is to add the new column next to the old one, tell the owners, and give them a date to switch. After that date, and once query history shows nobody reads the old name, I drop it. For marts that are real interfaces for other teams, I'd use model contracts, so column names and types are enforced at build time and a change can't slip through, and model versions, so a new version can live beside the old one with a deprecation date. The rename is a one-line change; the work is the communication."

"First I confirm where the time goes. dbt records each model's run time in run_results.json and the logs, so I check it really is this model and when it started slowing down. Then I open the compiled SQL and the warehouse's query profile to see which step is expensive. The usual suspects are these. The table is rebuilt from all of history every day and simply grew, which is a case for making it incremental. It reads from a chain of views, so each run recomputes all the upstream logic, which I fix by materializing a heavy upstream model as a table. A join fans out and multiplies rows before a group by. Or a window function runs over the whole history. I also check that filters apply early, so the warehouse can skip data. Then I change one thing, rerun, and compare the run time and row counts."