dbt Interview Questions: Model Grain, Data Tests and Late-Arrival Reasoning

What is the grain of each model?

In the fictional dataset, raw_orders contains incoming versions of orders. stg_orders should contain one current row per valid order_id. customer_totals should contain one row per customer_id. Write these grains down before choosing tests or an incremental strategy.

The intended dependency chain is raw_orders → stg_orders → customer_totals. In a dbt project, the raw table would normally be declared as a source, and downstream models would use ref to refer to models. dbt’s ref both resolves a relation and records a dependency. Hardcoding a database table name does not express that model dependency in the same way.

Run the checked SQL reasoning exercise

Save the following as dbt_reasoning.py and run python dbt_reasoning.py. It uses only Python’s standard library. The exact code passed with Python 3.12.14 and SQLite 3.53.1. Integer amounts and integer update markers keep the example small; they are teaching values rather than a production money or timestamp design.

import sqlite3

con = sqlite3.connect(':memory:')
con.executescript('''
CREATE TABLE raw_orders (
  order_id INTEGER, customer_id TEXT, amount INTEGER, updated_at INTEGER
);
INSERT INTO raw_orders VALUES
  (1, 'A', 120, 1), (1, 'A', 150, 4),
  (2, 'A', 50, 3), (NULL, 'A', 30, 2);
CREATE VIEW stg_orders AS
WITH ranked AS (
  SELECT *, ROW_NUMBER() OVER (
    PARTITION BY order_id ORDER BY updated_at DESC
  ) AS version_rank
  FROM raw_orders WHERE order_id IS NOT NULL
)
SELECT order_id, customer_id, amount, updated_at
FROM ranked WHERE version_rank = 1;
CREATE VIEW customer_totals AS
SELECT customer_id, SUM(amount) AS total
FROM stg_orders GROUP BY customer_id;
''')
nulls = con.execute(
    'SELECT COUNT(*) FROM raw_orders WHERE order_id IS NULL'
).fetchone()[0]
duplicates = con.execute('''
SELECT order_id FROM raw_orders WHERE order_id IS NOT NULL
GROUP BY order_id HAVING COUNT(*) > 1
''').fetchall()
assert nulls == 1 and duplicates == [(1,)]
assert con.execute('SELECT * FROM customer_totals').fetchall() == [('A', 200)]
assert con.execute('''
SELECT order_id FROM stg_orders GROUP BY order_id HAVING COUNT(*) > 1
''').fetchall() == []
assert con.execute(
    'SELECT order_id FROM stg_orders WHERE order_id IS NULL'
).fetchall() == []
watermark = con.execute('SELECT MAX(updated_at) FROM stg_orders').fetchone()[0]
con.execute("INSERT INTO raw_orders VALUES (3, 'A', 40, 2)")
naive_increment = con.execute(
    'SELECT order_id FROM stg_orders WHERE updated_at > ?', (watermark,)
).fetchall()
assert naive_increment == []
assert con.execute('SELECT * FROM customer_totals').fetchall() == [('A', 240)]
print('raw null keys: 1; duplicate keys: [1]')
print('clean total: 200; after late arrival: 240; naive increment: []')
con.close()

Why do uniqueness and not-null checks belong together?

The raw input has one null key and two versions of order 1. The checks reveal both conditions. The staging view excludes the null-key row and keeps the latest version of each remaining order. In a real pipeline, the rejected row needs an explicit disposition such as a quarantine record and a follow-up owner; silently losing it would not be a complete data-quality strategy.

dbt data tests identify rows that violate an assertion. A uniqueness test and a not-null test answer different questions; declaring a key name is not a substitute for those checks. The script demonstrates their SQL logic, not an actual dbt test invocation.

Why can a watermark miss a late-arriving row?

The script captures a maximum update marker of 4, then receives order 3 with marker 2. Filtering for rows greater than 4 returns nothing even though a full evaluation now totals 240. The example isolates a mismatch between arrival time and the field used to detect changes.

A bounded lookback may reduce missed updates within its window, but it cannot guarantee recovery of arbitrarily late data. Discuss the source’s change guarantees, an ingestion marker, periodic reconciliation or a deliberate rebuild policy. The correct choice depends on the source and cost constraints, not on a universal number of days.

Does unique_key make an incremental model correct?

No. In supported incremental strategies, unique_key helps match incoming rows to existing rows; it does not prove that incoming keys are unique or that the selection filter included every change. Exact behavior depends on the adapter and strategy.

For the customer aggregate, simply appending the late order’s amount as a new customer row would violate the mart’s grain. Explain whether you would recompute affected customers or use another tested update strategy. The SQLite view always recomputes its result, so its correct total does not validate an incremental warehouse implementation.

What should you verify before presenting this as a dbt project?

Build a small project with the intended adapter, declare the source, create the two models and attach the relevant data tests. Check the compiled SQL and actual database results for an initial build, repeated input, changed orders, null keys, tied versions and late arrivals. Confirm the handling of deletes and schema changes.

This guide provides a checked reasoning exercise and the questions that expose its limits. It is not a ready-to-run dbt repository or a claim that one warehouse strategy works across all adapters.