dbt interview questions for Data Engineer roles

ref() does 2 things a table name cannot. It builds the dependency graph: dbt reads every ref to learn that fct_orders needs stg_orders, so dbt run and dbt build run models in the right order, and selectors such as stg_orders+ know what is downstream. And it resolves the schema at run time, so the same code writes to a developer's schema, the CI schema and production without edits. A hard-coded analytics.stg_orders breaks both: dbt may build fct_orders before stg_orders, and a developer's run reads production data. source() does the same for raw tables loaded by other tools, and adds lineage, tests and freshness checks on them.

1. The difference in one model

-- breaks: no dependency, always reads production
select * from analytics.stg_orders

-- right: dbt knows the dependency and picks the schema of this run
select * from {{ ref('stg_orders') }}

In a developer's run ref('stg_orders') might compile to dev_ana.stg_orders; in production to analytics.stg_orders.

2. What the graph gives you

• Order: models build after everything they ref.
• Selection: dbt build --select stg_orders+ builds a model and everything that depends on it; +fct_orders builds what it depends on.
• Skipping on failure: in dbt build, a failing test on stg_orders skips the models downstream of it instead of building them on bad data.
• Lineage: the docs site draws the graph from the same references.

3. Sources for raw data

# models/staging/sources.yml
sources:
  - name: shop
    schema: raw_shop
    tables:
      - name: orders
        loaded_at_field: _loaded_at
        freshness:
          warn_after: {count: 12, period: hour}

select * from {{ source('shop', 'orders') }}

The raw table now appears in the lineage, can carry data tests such as unique on its id, and dbt source freshness warns when the loader has not delivered for 12 hours.

4. The rule

Inside a dbt project, a model never names a table directly: models are read with ref, raw tables with source. A reviewer can check it with a quick search for from followed by a schema name.

What interviewers want: the dependency graph and run order, the schema chosen per environment, selection with + and skipping after failed tests, source() for raw tables with lineage, tests and freshness.

Match the materialization to how each model is used. Views for light models that only rename and cast, since they cost nothing to build; tables for models that BI tools query or many models reuse; incremental for large event tables where rebuilding all of history each night is the waste; ephemeral for small reusable steps that need no object in the warehouse; a materialized view where the database can keep a result fresh on its own. Here, the 2-billion-row events model should be incremental, the staging models views, and the marts the dashboard reads tables.

1. The 5 built-in materializations

dbt's own advice is to start with views and change only when performance asks for it, and not to start with incremental.

2. Applying it to the 3-hour run

# dbt_project.yml
models:
  shop:
    staging:
      +materialized: view
    marts:
      +materialized: table

-- models/marts/fct_events.sql
{{ config(materialized='incremental', unique_key='event_id') }}
select * from {{ ref('stg_events') }}
{% if is_incremental() %}
  where event_time >= (select max(event_time) from {{ this }})
{% endif %}

• The staging models were rebuilt as tables each night but only rename columns: as views they cost nothing to build.
• The events model now processes a night's rows, not 2 billion.
• The marts stay tables, because the dashboard reads them all day.

3. How to decide on a model you have not seen

1. Who reads it? A person or a BI tool, often: table. Only other models: view or ephemeral.
2. How heavy is it? Renames and casts: view. Joins and aggregates over large data: table or incremental.
3. How big is it, and does it grow by appending? Large and append-mostly: incremental.
4. Does the platform refresh materialized views well? Then that is an option for a result that must stay fresh.

4. Measure, then change

Read the run timings in run_results.json or the dbt platform to find the slowest models before changing anything; usually a handful of models take most of the 3 hours.

What interviewers want: the 5 materializations and their trade-offs, views by default, tables for what BI reads, incremental for large append-mostly models and not by default, ephemeral as a CTE, and choosing by who reads a model and how heavy it is.

A data test checks the data a model produced, on every run, in production: is order_id unique, is status one of the expected values, does every customer_id exist in customers. A unit test checks a model's logic on a few rows you write by hand, before it is built, in development and CI. The duplicate order ids are a data problem, caught by a unique data test on the source or staging model. Refunds counted as sales is a logic error: the real data can look plausible, so a unit test with one sale and one refund, expecting revenue of the sale only, catches it.

1. Side by side

2. The data test for duplicates

models:
  - name: stg_orders
    columns:
      - name: order_id
        data_tests:
          - unique
          - not_null
      - name: status
        data_tests:
          - accepted_values:
              arguments:
                values: ['placed', 'shipped', 'completed', 'returned']
      - name: customer_id
        data_tests:
          - relationships:
              arguments:
                to: ref('stg_customers')
                field: customer_id

In dbt build a failing unique test stops the models downstream of stg_orders from building on duplicates. store_failures keeps the failing rows in a table to inspect.

3. The unit test for refunds

unit_tests:
  - name: refunds_do_not_count_as_revenue
    model: fct_revenue
    given:
      - input: ref('stg_payments')
        rows:
          - {order_id: 1, amount: 100, kind: sale}
          - {order_id: 1, amount: -100, kind: refund}
          - {order_id: 2, amount: 50, kind: sale}
    expect:
      rows:
        - {gross_sales: 150, refunds: 100}

It runs before fct_revenue is built and fails if the logic adds refunds to sales.

4. Where each goes

• Data tests on every primary key (unique, not_null) and every foreign key (relationships) of sources and models.
• Unit tests where the SQL is tricky: regex, date math, window functions, long case when statements, and any logic that has had a bug before. Not on functions the warehouse already tests, such as min().
• Severity: make a test warn when a failure should be looked at but not stop the build.

What interviewers want: data tests check produced data on every run and unit tests check logic on fixed inputs in CI, the 4 generic tests, a test failure skipping downstream models in dbt build, unit tests for complex logic, and matching each failure to the test that catches it.

A snapshot turns a table that is overwritten in place into a type 2 slowly changing dimension: each time the snapshot runs, dbt compares the source rows with the latest stored version of each key, closes a changed version by setting dbt_valid_to, and adds the new one with dbt_valid_from. The current version has dbt_valid_to null. Use the timestamp strategy on updated_at when the source keeps it reliably; use the check strategy on chosen columns when it does not. What can go wrong: a snapshot only sees the state at the moments it runs, so 2 changes between runs show as one, and deletes are ignored unless hard_deletes is set.

1. The source and the snapshot

The source keeps only the latest status:

The snapshot keeps each version:

Time in each status is dbt_valid_to - dbt_valid_from, with now in place of null for the current one.

2. The configuration (dbt 1.9 and later, in YAML)

# snapshots/orders_snapshot.yml
snapshots:
  - name: orders_snapshot
    relation: source('shop', 'orders')
    config:
      unique_key: id
      strategy: timestamp
      updated_at: updated_at
      hard_deletes: invalidate

With the check strategy instead:

      strategy: check
      check_cols: [status, amount]

3. Choosing the strategy

The check strategy compares columns on every run, which costs more on wide tables.

4. What can go wrong

• Changes between runs are lost. A snapshot taken once a day sees an order go from pending straight to shipped if it was packed for 3 hours. Run it more often, or capture changes from the database log (change data capture) when every step matters.
• Deletes. By default a deleted source row stays current forever. hard_deletes: invalidate closes it; new_record adds a row marking the delete.
• A unique key that is not unique creates bad versions. Test it.
• Snapshots are not rebuilt. History exists only from the first run on, and a dropped snapshot table cannot be regenerated, so treat it as data to protect, not a model to rebuild.

What interviewers want: snapshots as SCD type 2 with dbt_valid_from and dbt_valid_to, timestamp versus check, changes between runs being invisible, hard_deletes, testing the unique key, and snapshots as history that cannot be rebuilt.

The filter assumes rows arrive in time order and never change. A late event has an event_time older than the newest one already loaded, so the filter excludes it on every run after. An updated order is a new row for the same order, and with no unique_key dbt appends it next to the old one. Fix both: filter on when the row arrived or changed (updated_at or a load timestamp) with a lookback window, set unique_key='order_id' with the merge (or delete+insert) strategy so a changed order replaces its old row, and run a full refresh once to repair history. For large time-series data, the microbatch strategy with a lookback does the same per day.

1. Why late events vanish

{% if is_incremental() %}
  where event_time >= (select max(event_time) from {{ this }})
{% endif %}

On Monday night the newest event loaded is from Monday 23:59. On Tuesday an event from Sunday arrives. Its event_time is before Monday 23:59, so this filter skips it, and every later run skips it too.

2. Why updates duplicate

Without a unique_key, most adapters only append. The order placed on Monday (status placed) and the same order on Wednesday (status shipped) are 2 rows. dbt's docs say it directly: to capture updated records you need a unique key, or they come in as duplicates.

3. The rewrite

{{ config(
    materialized='incremental',
    unique_key='order_id',
    incremental_strategy='merge',
    on_schema_change='append_new_columns'
) }}

select order_id, status, amount, event_time, updated_at
from {{ ref('stg_orders') }}
{% if is_incremental() %}
  -- rows changed since the last load, with 3 days of overlap for late arrivals
  where updated_at >= (select dateadd(day, -3, max(updated_at)) from {{ this }})
{% endif %}

• updated_at moves when an order changes or arrives late, so both are picked up.
• The 3-day overlap catches rows whose timestamp lags the load. Overlap is safe because the merge on order_id replaces rows instead of adding them: the model is idempotent.
• unique_key must be unique in the new rows too; if stg_orders can hold 2 rows for one order, keep the latest first (qualify row_number() over (partition by order_id order by updated_at desc) = 1), or the merge can fail.

4. Repair history once

dbt build --select fct_orders+ --full-refresh

It drops and rebuilds the table, and the + rebuilds what depends on it.

5. The microbatch alternative

{{ config(materialized='incremental', incremental_strategy='microbatch',
          event_time='event_time', batch_size='day', lookback=3, begin='2025-01-01') }}

Each day is a batch that dbt replaces as a whole, and lookback=3 reprocesses the 3 days before the latest, so late events land in their own day. Upstream models need event_time configured, or each batch scans them in full.

What interviewers want: why max(event_time) drops late rows, why no unique_key duplicates updates, filtering on updated_at with an overlap, merge on a unique key making the overlap safe, deduplicating the new rows, a one-time full refresh, and microbatch with lookback.