dbt Testing Interview Questions: Unit Tests, Data Tests, and CI

A good answer to dbt testing interview questions begins by naming the layer at risk. Use a unit test when you want to prove that SQL logic transforms a small, controlled input into an expected output. Use a data test when you want to query a built relation and find rows that violate an assertion. Passing both still does not prove that an incremental merge, warehouse-specific behavior, or production data delivery is correct.

This guide turns that distinction into an executable discount-model exercise. We ran it with dbt Core 1.12.4 and dbt-duckdb 1.11.0, deliberately broke one boundary, and preserved the failing diff before restoring the model.

When Should You Use a Unit Test Instead of a Data Test?

Official dbt distinction: dbt's unit-test documentation says unit tests validate SQL modeling logic against small static inputs before the model is materialized. Data tests are SQL queries over resources such as models, sources, snapshots, or seeds; failing rows are records that violate the assertion.

That gives you a practical interview rule:

Risk	Best first test	Evidence produced
CASE boundary, date math, regex, window, or join logic	Unit test	Actual rows compared with expected rows
Duplicate key, unexpected null, invalid domain, or orphan in built data	Data test	Rows or a count that violate the rule
Incremental merge/update behavior	Integration check on the materialized target	Final target state after realistic runs
Adapter-specific SQL or materialization	Targeted warehouse check	Behavior on the intended adapter and engine

The layers complement one another. A unit fixture can include a duplicate to test deduplication logic, but it does not show whether duplicates exist in today's source. A unique data test can reveal duplicate output keys, but it may not explain which branch of a complex model created them.

Interview inference: the strongest candidate does not answer “we add unique and not_null everywhere.” They connect each test to a failure mode, explain what a failure returns, and state which risks remain.

The Discount Model We Tested

Our original model reads customer lifetime value and assigns a discount tier. Values below 500 are bronze, 500 through 999.99 are silver, and values at or above 1,000 are gold. A missing lifetime value keeps the tier null. The model then left joins a tier lookup and defaults a missing discount rate to zero.

case
    when lifetime_value is null then null
    when lifetime_value >= 1000 then 'gold'
    when lifetime_value >= 500 then 'silver'
    else 'bronze'
end as tier

The critical rows are not a random sample. They sit immediately below and on each boundary: 499.99, 500.00, and 1000.00, plus NULL. A second fixture classifies a gold customer while deliberately omitting the gold lookup row. That row proves the left join and coalesce behavior.

We also materialized the model from two CSV seeds and attached three data tests: not_null and unique for customer_id, plus accepted values of 0.00, 0.05, and 0.10 for discount_rate. These tests answer different questions from the fixture even though they run in the same dbt test command.

How Do You Write the Smallest Useful Given-and-Expect Fixture?

Start from branches, boundaries, and nullable joins. Do not copy twenty production rows into YAML. Every row should force a distinct decision in the SQL.

unit_tests:
  - name: discount_boundaries_and_null
    model: customer_discounts
    given:
      - input: ref('raw_customers')
        rows:
          - {customer_id: 10, lifetime_value: 499.99}
          - {customer_id: 11, lifetime_value: 500.00}
          - {customer_id: 12, lifetime_value: 1000.00}
          - {customer_id: 13, lifetime_value: null}
      - input: ref('tier_discounts')
        rows:
          - {tier: bronze, discount_rate: 0.00}
          - {tier: silver, discount_rate: 0.05}
          - {tier: gold, discount_rate: 0.10}
    expect:
      rows:
        - {customer_id: 10, tier: bronze, discount_rate: 0.00}
        - {customer_id: 11, tier: silver, discount_rate: 0.05}
        - {customer_id: 12, tier: gold, discount_rate: 0.10}
        - {customer_id: 13, tier: null, discount_rate: 0.00}

Official constraints matter. dbt currently supports native unit tests for SQL models in the current project, and the YAML belongs under a configured model path. Declare every ref and source used by the model as an input or compilation can fail. Join table names must be aliased. Dict and CSV fixtures can specify only relevant columns, while an ephemeral-model input must use format: sql.

If the model uses database-specific structs, types, or functions, check the adapter caveats. A tidy fixture that compiles on DuckDB does not guarantee identical semantics on BigQuery, Snowflake, or another target.

Which Edge Cases Expose SQL Logic Bugs?

Change only one fact at a time. For the discount model, useful interview follow-ups include:

• Change >= 1000 to > 1000. Which row fails?
• Remove the gold lookup. Does the row disappear, become null, or default to zero?
• Put the lookup filter in WHERE after a left join. Does that accidentally create inner-join behavior?
• Add duplicate tier rows. Does the model duplicate a customer?
• Set lifetime value to null. Should the tier be unknown or bronze?

We executed the first mutation. The boundary unit test failed with one differing row: customer 12 changed from expected gold, 0.10 to actual silver, 0.05. Restoring >= produced a clean run: two unit tests and three data tests passed.

That diff is better interview evidence than “the test caught an off-by-one.” It names the input, expected branch, actual branch, and downstream discount consequence. A useful debugging answer preserves the diff before editing the SQL.

What Do Data-Test Failures Actually Mean?

A generic data test returns rows that fail its condition. For example, unique identifies duplicate values, while not_null identifies rows whose tested column is null. dbt reports the number of failing records and applies configured severity, warning, and error thresholds.

Do not confuse zero failing rows with complete correctness. A relation can contain the wrong discount for every customer while still having unique, nonnull IDs. It can also be empty and satisfy some universal constraints. Pair structural tests with business reconciliation such as expected population, totals, source-to-target coverage, freshness, and accepted relationships.

A singular data test is ordinary SQL whose selected rows represent failures. For this model, a useful custom test could select customers whose discount disagrees with the tier. Keep the failure query diagnostic: include the key, actual value, expected rule, and relevant source fields rather than returning only a total count.

What Can an Incremental Unit Test Not Prove?

dbt lets a unit test override is_incremental() and provide this as an input. That is useful for testing which rows the model SQL proposes to insert or merge. The official documentation makes a crucial limitation explicit: the expected result is the materialization input, not the final target after the merge, and native unit testing cannot currently prove that dbt performed the insert or merge correctly.

That boundary changes the test plan. Unit-test both SQL branches, then run a small integration sequence on the actual adapter: initial build, incremental change, replay, and targeted update or delete. Query the final table after each step. For late data, unique keys, and rebuild equivalence, use the separate dbt incremental model interview guide.

A similar distinction applies to snapshots. Unit-testing transformation logic does not prove that a snapshot observed every source state or handled hard deletes as intended. The dbt snapshot interview guide covers that history boundary.

How Should These Tests Run in CI?

The official dbt workflow recommends unit tests in development and CI because their inputs are static. A focused command can select unit tests with test_type:unit. The current docs also state that dbt build runs resources in dependency order: unit-test the model, materialize it, then run its data tests.

A practical pull-request pipeline might use:

1. Parse or compile the project and fail on configuration errors.
2. Build empty direct parents when the selected unit tests require their schemas.
3. Run unit tests for changed models and affected descendants.
4. Build the selected models in an isolated CI schema.
5. Run data tests on the built relations.
6. Preserve run_results.json, compiled SQL, and failing rows where policy permits.

Selection deserves care. Testing only the changed SQL file can miss downstream contracts; building the entire production DAG for every edit may be too slow or expensive. Use state-aware selection deliberately, define deferral behavior, and keep the CI target isolated from developer and production schemas.

How Do You Interpret a Green CI Run?

Say exactly what ran. In our local environment, dbt test discovered two seeds, one model, three data tests, and two unit tests. The final command completed PASS=5 with one DuckDB thread. It did not rebuild the model during that command because we had materialized it earlier.

A green result supports five claims: both controlled transformations matched expected rows, IDs in the current built relation were nonnull and unique, and discount values belonged to the configured set. It does not prove source freshness, warehouse equivalence, access grants, orchestration timing, incremental merge correctness, or business approval of the thresholds.

This precision helps during incident questions. If production is wrong after CI passed, do not conclude that dbt tests are useless. Compare the production input, target, adapter, variables, environment, selected resources, and execution order with what CI actually exercised.

dbt Testing Interview Questions to Rehearse

• When is a four-row unit fixture more valuable than a data test over millions of rows?
• Why must every referenced input appear in given?
• Which row catches > 1000 versus >= 1000?
• How can a filter after a left join invalidate the intended null behavior?
• What does a generic data test return when it fails?
• Why can unit and data tests pass while an incremental target is still wrong?
• Where should compiled SQL and failing-row evidence be retained in CI?

For each answer, name the risk, layer, fixture or dataset, oracle, and remaining uncertainty. That makes the response portable across adapters without pretending their behavior is identical.

Final Takeaway

The memorable dbt testing answer is not a list of commands. Unit tests prove SQL behavior for controlled given rows and expected output. Data tests find violations in built data. Integration checks prove materialization behavior, and targeted warehouse checks cover dialect or adapter assumptions.

Build one four-row fixture, introduce one deliberate boundary defect, and explain the diff before fixing it.

Sources and Further Reading

• dbt documentation: Unit tests
• dbt documentation: Data tests
• dbt command reference: dbt test
• dbt reference: Unit-test inputs
• dbt reference: Unit-test data formats
• GetTarmac dbt interview questions — search-intent context only.
• PythonDataBench dbt unit-testing guide — independent tutorial comparison.

Research note: Documentation and local behavior were checked on September 10, 2026. The local fixture used dbt Core 1.12.4 and dbt-duckdb 1.11.0; verify adapter-specific behavior in the interview's stated environment.