The fastest way to fail an analytics engineering screen is to write an incremental model that quietly drops rows. The candidate types out something that looks fine, runs it once, sees data, and moves on. The bug doesn’t surface until a Monday three weeks later, when finance notices the revenue table is short a few thousand orders.
This is what usually gets written under interview pressure:
{{ config(materialized='incremental') }}
select *
from {{ source('shop', 'orders') }}
{% if is_incremental() %}
where ordered_at > (select max(ordered_at) from {{ this }})
{% endif %}Two problems, and a good interviewer pokes at both. There’s no unique_key, so the incremental strategy is a plain append. Reprocess a day and you get duplicate orders. And the filter uses a strict > against the current max, so any row that arrives late, after that day’s run has already pushed the max forward, never matches the filter again. It’s gone. This is the late-arriving data problem, and it’s the most common trap in the whole interview.
The version that shows you’ve run this in production:
{{ config(
materialized='incremental',
unique_key='order_id',
incremental_strategy='merge'
) }}
select *
from {{ source('shop', 'orders') }}
{% if is_incremental() %}
where ordered_at >= (select dateadd(day, -3, max(ordered_at)) from {{ this }})
{% endif %}The three-day lookback reprocesses a rolling window instead of trusting a single high-water mark. The unique_key plus merge makes that reprocessing idempotent, so a row that shows up twice updates in place rather than duplicating. The follow-up is usually “what if your warehouse doesn’t support merge?” On older Redshift setups you fall back to delete+insert. And if they’re being thorough, they’ll ask what happens when you add a column: dbt’s on_schema_change defaults to ignore, so a new column silently won’t appear in the existing table until a full refresh.
ref() and source() are how dbt builds the graph
Models read from other models with {{ ref('stg_orders') }} and from raw tables with {{ source('shop', 'orders') }}. This isn’t a style preference. dbt parses those calls to build the dependency graph, works out the run order, and parallelizes whatever it can. Hardcode a schema-qualified table name instead of using ref and you’ve cut that node out of the graph. dbt will run your model before its upstream table has been rebuilt, and you’ll spend an hour chasing stale numbers that were correct yesterday.
The layering question follows almost every time. Most teams run staging, then intermediate, then marts. Staging models sit one-to-one with source tables: rename columns, cast types, maybe fix a timezone, no joins and no business logic. Intermediate models do the joins and the awkward reshaping you don’t want anyone reading twice. Marts are the tables analysts and BI tools actually query. When they ask where a piece of logic belongs, the answer they’re listening for is “as far upstream as it can live without being specific to one report.”
Picking a materialization, and when a view turns into a problem
Every model is a view, a table, incremental, or ephemeral. It sounds like trivia until they frame it as a scenario: your staging model is a view, it does three joins, and forty dashboards read from a mart built on top of it. Now every dashboard load recomputes those joins. Switch the mart to a table and the query cost drops, at the price of a rebuild each run. Get large enough and rebuilds hurt, so you go incremental. That progression is what they want reasoned out loud, not memorized as four definitions.
| Materialization | What dbt does each run | Where it fits | The tradeoff you accept |
|---|---|---|---|
| view | Creates or replaces a database view and stores no data | Staging models and light transforms with low query volume | Every downstream query recomputes the logic, so heavy models get slow |
| table | Drops and rebuilds the whole table from scratch | Marts queried often, with moderate row counts | A full rebuild every run wastes compute once the table is large |
| incremental | Inserts or merges only the rows matched by the incremental filter | Large append-heavy tables such as events, logs, clickstream, orders | You now manage unique_key, late data, and schema changes yourself |
| ephemeral | Inlines the model as a CTE in whatever references it and builds nothing | Small reusable logic you don’t want cluttering the warehouse | Can’t be queried directly, and errors surface in confusing places |
Snapshots and the “what did this look like on March 1st” question
Incremental models care about the latest state. Snapshots record how a row changed over time, which is dbt’s answer to slowly changing dimensions. Point a snapshot at a source table and dbt adds dbt_valid_from and dbt_valid_to columns, closing out the old version and opening a new one every time a tracked value changes. That’s how you answer “what plan was this customer on when they churned in March” months after the fact.
{% snapshot customer_plans %}
{{ config(
unique_key='customer_id',
strategy='timestamp',
updated_at='updated_at'
) }}
select customer_id, plan_name, updated_at
from {{ source('billing', 'customers') }}
{% endsnapshot %}The timestamp strategy trusts an updated_at column that reliably moves on every change. If the source has no such column you can trust, you switch to the check strategy and list the columns dbt should diff instead. The gotcha they like to surface is hard deletes. If a row disappears from the source, a snapshot leaves the last version marked current forever unless you set invalidate_hard_deletes. Miss that and your active-customer count never goes down.
The tests they expect to already be there
Nobody is impressed that you know unique and not_null exist. They’re checking whether you test the grain of the table. If a model is one row per order, a unique test on order_id and a not_null on it are the two lines that prove it, and their absence says you’ve never been paged for a fan-out bug. Past that: relationships tests for referential integrity between models, accepted_values for status columns, and singular tests (a plain SQL file that returns the rows breaking your assumption) for anything a generic test can’t express.
A quick tell they listen for is whether you say dbt run then dbt test, or just dbt build. build runs each model and its tests in dependency order, so a failing test stops bad data before the downstream models consume it. Running all models first and testing afterward means the broken data already propagated. The scenarios tend to sound like this:
- “This incremental model is short a few thousand rows some mornings. Walk me through what you’d check first.”
- “What’s the grain of this table, and which test proves it?”
- “When would you reach for a snapshot instead of an incremental model?”
- “There’s a left join in a staging model. Is that a problem?”
That last one is a trap worth naming. A left join in staging can fan out rows if the right side isn’t unique on the join key, which quietly breaks the one-row-per-entity grain the whole downstream tree assumes. The answer they want is to move the join into an intermediate model and test the grain right there.
The macro question has a trap in it
Jinja runs at compile time. dbt renders your macros into plain SQL before a single query reaches the warehouse, which makes a macro a code generator rather than a runtime function. You can loop over a known list of column names to generate SQL. You cannot loop over the rows in a table, because those rows don’t exist yet when the macro runs. Candidates who miss this write macros that try to iterate over the data, and it never works.
{% macro cents_to_dollars(column_name, precision=2) %}
round({{ column_name }} / 100.0, {{ precision }})
{% endmacro %}Called as {{ cents_to_dollars('amount') }}, it drops the same rounding logic into every model instead of being copy-pasted fifteen times. The package most teams reach for is dbt_utils, and the function that comes up by name is generate_surrogate_key, which hashes a set of columns into a stable key so you’re not leaning on a source system’s auto-increment id. If someone wants to test how deep you go, they’ll ask about run_query, the escape hatch that does let a macro hit the database at compile time to, say, fetch a list of distinct values to pivot on.
What the live round is really scoring
Once you’re past the take-home, the conversation usually turns to your own project or a repo they hand you. They’re watching for whether you think about CI. Slim CI, where dbt uses state:modified to build and test only the models that changed against a deferred production manifest, signals that you’ve felt the pain of a full-project run on every pull request. Deferral, grain, naming conventions, and where you draw the line between intermediate and mart all come up as you talk through the code.
The people who pass tend to describe their models the way they’d describe production code they’re on call for: what breaks it, how they’d find out, and what they’d check first. Recite the definition of an incremental model and you sound like the docs. Explain why yours dropped four thousand rows last quarter and how you caught it, and you sound like someone they can hand the warehouse to.
Drill the patterns next:
