dbt Interview and System Design Guide

You Have Completed All 19 Prior dbt Modules. Here Is How It Comes Together.

A Reusable Structure for Any dbt System-Design Prompt

Every worked example in this module, from Part 01 through Part 12, follows the same structure deliberately, because that structure is itself the thing interviewers are evaluating — more than any single correct answer. Internalizing this shape means a genuinely unfamiliar prompt on interview day is still approachable, because the process for attacking it is the same one practiced repeatedly below.

Notice this structure never starts with "here's the folder structure" — it starts with questions. Interviewers consistently report that the single most common mistake in a system-design interview, dbt-specific or otherwise, is a candidate leaping straight to a directory tree before establishing what problem that structure is actually meant to solve. Every worked example in this module opens with requirements gathering for exactly this reason, not as a formality.

Design a dbt Project Structure for a Company With 5 Source Systems and 3 Analytics Teams

Requirements gathering

Before touching a folder structure at all, a strong candidate asks clarifying questions, because the right layout depends entirely on the answers. For this prompt: what are the five source systems — a production application database, a payments processor, a marketing/ads platform, a support ticketing tool, and an HR system, say? Do the three analytics teams (finance, marketing, product) share any common entities, like a customer or an order? Does each team own and maintain its own marts, or does a central platform team own everything?

Reasonable answers for this exercise: the five sources are a production Postgres app database, a Stripe payments export, a Google/Meta ads export, Zendesk, and Workday. Finance, marketing, and product all need a shared notion of "customer" and "order," but each also has team-specific metrics (finance needs revenue recognition, marketing needs attribution, product needs feature usage). A small central platform team owns shared models; each analytics team owns its own team-specific marts on top of those shared models.

Layering — staging, intermediate, and marts, exactly as the project-structure module frames it

One staging model per source table, each doing only light cleanup — renaming, type casting, no joins — living under models/staging/<source_system>/. This one-to-one mapping between a staging model and a source table is what keeps the base of the DAG traceable back to exactly where each column originated, and it is the layer every other model in the project ultimately depends on through ref(), never a raw table name directly.

models/
  staging/
    stripe/
      stg_stripe__charges.sql
      stg_stripe__customers.sql
    postgres_app/
      stg_app__orders.sql
      stg_app__customers.sql
    zendesk/
      stg_zendesk__tickets.sql
    ads/
      stg_ads__campaign_spend.sql
    workday/
      stg_workday__employees.sql

  intermediate/
    int_customers_unified.sql      -- owned by the platform team
    int_orders_with_charges.sql    -- owned by the platform team

  marts/
    finance/
      fct_revenue_recognition.sql
      dim_customers_finance.sql
    marketing/
      fct_campaign_attribution.sql
    product/
      fct_feature_usage.sql
    shared/
      dim_customers.sql            -- the shared, cross-team customer dimension
      dim_orders.sql

The marts/shared/ subfolder is the key structural decision here: it holds the customer and order dimensions every team needs in common, owned by the platform team, whilemarts/finance/, marts/marketing/, and marts/product/ hold each team's own metrics built on top of the shared layer. This mirrors exactly the layered dependency direction the project-structure module establishes — staging feeds intermediate, which feeds shared marts, which feed team marts — with no team mart ever bypassing the shared layer to join two staging models directly, which would silently create three different, possibly inconsistent definitions of "customer."

Ownership and access control across teams

Trade-offs worth stating out loud in an interview

Centralizing the customer and order dimensions under platform-team ownership creates a single source of truth, but it also creates a bottleneck: a marketing analyst who needs one new column ondim_customers now depends on the platform team's review queue rather than shipping the change themselves. A strong answer names this trade-off explicitly and proposes a mitigation — a documented contribution process letting any team open a PR against the shared layer with mandatory platform review, rather than the platform team being the only one who can even propose a change.

How Would You Migrate a Legacy Stored-Procedure-Based Transformation Pipeline to dbt With Minimal Risk?

Requirements gathering — this prompt is about migration risk, not a clean-slate design

The critical difference from Part 01: this system already exists and already works, just as a tangle of nightly stored procedures nobody fully trusts to modify anymore. The interview is testing whether a candidate defaults to "rewrite everything in dbt at once" versus a lower-risk incremental migration. Key questions: how many stored procedures, and do they have clear boundaries or is business logic tangled across several of them? Who depends on the current output, and can that output be validated against a new pipeline before cutover?

Reasonable answers: roughly 60 stored procedures, several with genuinely tangled, undocumented logic that the original author has since left the company. A handful of downstream BI dashboards and one exported finance report depend on the final tables. Nobody wants a single big-bang cutover given how poorly understood some of the procedures are.

The migration approach — strangler-fig, one leaf table at a time, validated in parallel

The right first move is not rewriting all 60 procedures into dbt models simultaneously — that concentrates all the migration risk into one moment and makes rollback nearly impossible if something is subtly wrong. The right approach is the strangler-fig pattern: pick the stored procedures with the fewest downstream dependents (the leaves of the dependency graph) first, port each to a dbt model, run both the old procedure and the new dbt model in parallel for a validation window, and only decommission the old procedure once outputs match consistently.

Phase 1 (lowest-risk leaves first):
  Identify stored procedures with zero downstream dependents inside the
  legacy pipeline itself -- these are safest to port first because nothing
  else breaks if the port has a subtle bug, since nothing downstream
  consumes them within the legacy system.

Phase 2 (parallel run per ported model):
  Old stored procedure keeps running on its existing schedule, untouched.
  New dbt model (using ref()/source() from day one, not raw table names)
  builds into a separate validation schema on the same schedule.
  A validation query diffs row counts and key aggregate columns between
  the two outputs nightly.

Phase 3 (cutover, one model at a time):
  Once a specific ported model's outputs match consistently for an agreed
  window (two weeks is common), downstream consumers are repointed to the
  dbt-built table, and the corresponding stored procedure is disabled but
  not deleted yet.

Phase 4 (move up the dependency graph):
  Repeat for procedures whose only remaining dependents are already-
  migrated dbt models -- the dependency graph naturally determines the
  order, always migrating leaves before the procedures that feed them.

Phase 5 (decommission):
  Once every procedure has been ported and validated, the legacy
  scheduler and stored procedure code are removed entirely.

Migrating leaves first and moving up the dependency graph is the same underlying idea as the project-structure module's staging-before-marts layering principle, applied to a migration rather than a greenfield build: never migrate something before everything downstream of it (or, here, everything it depends on) is in a known state.

Trade-offs to state explicitly

Running two pipelines in parallel during validation roughly doubles compute cost for every model still mid-migration, and it takes real calendar time — pushing for a faster cutover trades away the exact safety net this approach is built around. A strong answer states this cost plainly rather than presenting the migration as free, and frames the validation window's length as a deliberate, negotiated risk tolerance rather than an arbitrary number.

Design a Testing and CI Strategy for a 300-Model dbt Project With a Small Platform Team

Requirements gathering

How many people maintain this project, and how many PRs merge per day? What is the cost of a silently wrong number reaching a dashboard versus the cost of tests being so slow or so noisy that engineers start ignoring failures? Are there specific tables (a revenue table, a customer PII table) where correctness matters more than others?

Reasonable answers: a platform team of three people supports roughly 300 models with a dozen PRs merging most days. A wrong number reaching the finance revenue dashboard is a genuinely costly incident; a flaky test that fails intermittently for unrelated reasons is actively eroding trust in the whole test suite, per the testing-strategy-at-scale module's framing of alert fatigue.

Layered testing strategy — not "test everything the same amount"

A common mistake a weaker answer makes here is proposing the exact same test depth uniformly across all 300 models — either far too little (missing the business-rule bugs that matter most) or far too much (a platform team of three cannot maintain hundreds of exhaustive singular tests, and test maintenance burden becomes its own operational problem). Tiering test depth by blast radius and business criticality, not by applying one policy everywhere, is the answer interviewers are listening for.

CI strategy for a three-person platform team supporting a dozen PRs a day

At this PR volume, a full-project build on every PR is immediately disqualifying on cost and speed grounds alone — exactly the problem the cicd-for-dbt module's Slim CI pattern solves. Every PR triggers dbt build --select state:modified+ --state ./prod-manifest --defer, building only the changed models and their downstream dependents against a manifest published after the most recent production deploy, with unbuilt upstream models resolved to production via--defer.

# Required status check, cannot be bypassed:
dbt build --select state:modified+ --state ./prod-manifest --defer --favor-state

# Additionally, for PRs touching marts/shared/ or marts/finance/ specifically,
# require an explicit platform-team review in addition to a green CI check --
# CI catches broken SQL and failing tests, but a second human reviewer is
# still the check for a business-logic error that compiles and passes tests
# while still being wrong.

With only three platform engineers, the highest-leverage additional investment is not more tests everywhere — it is making the required CI check fast and reliable enough that engineers trust it completely, and reserving scarce human review time specifically for the highest-blast-radius folders (marts/shared/) rather than spreading review attention evenly across all 300 models regardless of risk.

Walk Through Debugging Why an Incremental Model's Row Counts Don't Match a Full Refresh

Framing the problem before touching any SQL

This prompt tests methodical debugging more than any single piece of dbt trivia. The naive response is guessing at the SQL immediately; the strong response states a hypothesis-elimination process out loud first, because an incremental-versus-full-refresh row count mismatch has a small, well-known set of actual causes, and working through them systematically is faster than guessing.

The single fastest diagnostic — a full-refresh diff

Rather than reasoning abstractly about which hypothesis is correct, the fastest concrete step is running dbt run --select model_name --full-refresh into a separate comparison schema and directly diffing it against the existing incrementally-built table — exactly the diagnostic the incremental-models module recommends for exactly this situation.

-- Build a full-refresh copy into a scratch schema for comparison:
-- dbt run --select fct_orders --full-refresh --target scratch

SELECT
  incr.order_id,
  incr.total_amount AS incremental_amount,
  full.total_amount AS full_refresh_amount
FROM analytics.fct_orders incr
FULL OUTER JOIN scratch.fct_orders full
  ON incr.order_id = full.order_id
WHERE incr.order_id IS NULL          -- present in full refresh, missing incrementally
   OR full.order_id IS NULL          -- present incrementally, missing in full refresh (duplicate cause)
   OR incr.total_amount != full.total_amount   -- present in both, but stale in the incremental version

This single query answers which of the four hypotheses above is actually true: rows only missing from the incremental side point at the filter-window hypothesis; rows only present incrementally (duplicated) point at the merge-strategy hypothesis; rows present in both but with different values point at the late-arriving-update hypothesis. Rather than guessing, the diff tells you which direction to keep investigating.

Design a Slowly-Changing-Dimension Strategy for a Customer Table With Frequent Attribute Changes

Requirements gathering

What specifically changes on a customer record, and how often — an email address, a subscription tier, a billing address? Does any downstream analysis need to know what a customer's attributes were at a specific point in the past (e.g. "what tier was this customer on when they churned"), or is only the current value ever needed? What is the acceptable latency between a source change and that change being reflected?

Reasonable answers: subscription tier and billing address change several times a month per active customer; email changes rarely. Finance specifically needs historical accuracy — "what tier was this customer on in the month a given invoice was generated" is a real, recurring business question, not a hypothetical. A few hours of latency on capturing a change is acceptable.

Why this needs a Type 2 snapshot, not just an incremental model

An ordinary incremental model, even a well-built one, only ever represents the current state of a row per key — updating a customer's tier in place when the source changes. That is the wrong tool the moment finance needs to ask what a customer's tier was in the past, because an update-in-place model has already destroyed that history by the time anyone asks the question. This is exactly the distinction the snapshots module draws: a Type 2 snapshot instead inserts a new row with a new validity window every time a tracked column changes, preserving every historical value rather than overwriting it.

{% snapshot customers_snapshot %}

{{
    config(
      target_schema='snapshots',
      unique_key='customer_id',
      strategy='timestamp',
      updated_at='updated_at',
    )
}}

SELECT
  customer_id,
  email,
  subscription_tier,
  billing_address,
  updated_at
FROM {{ source('app', 'customers') }}

{% endsnapshot %}

Each snapshot run compares the current source row against the latest snapshotted version for thatcustomer_id. If subscription_tier or any other tracked column differs, dbt closes out the previous row (setting its dbt_valid_to) and inserts a new row withdbt_valid_from set to now — giving every historical tier change a precise, queryable validity window, exactly the point-in-time query finance needs.

SELECT s.subscription_tier
FROM snapshots.customers_snapshot s
WHERE s.customer_id = 4821
  AND '2026-06-15' >= s.dbt_valid_from
  AND ('2026-06-15' < s.dbt_valid_to OR s.dbt_valid_to IS NULL)

Snapshot cadence and the trade-off it implies

Snapshots only capture a change if they happen to run while that change is the current value at snapshot time — a customer whose tier changes twice between two snapshot runs will have the first change silently missed, with the snapshot only ever recording the value present at each actual run. Given the "a few hours of latency is acceptable" requirement established up front, an hourly snapshot run is a reasonable trade-off; a customer changing tier twice within the same hour is rare enough to accept the small risk of missing an intermediate value, but this trade-off should be stated explicitly rather than left implicit.

Design a dbt Project That Serves Both Fast-Moving Ad Hoc Analytics and a Stable, Audited Finance Reporting Layer

Requirements gathering

Do ad hoc analytics and finance reporting need the same underlying data, just consumed differently, or genuinely different data? How often does the ad hoc analytics team want to iterate on new metrics compared to how often the finance reporting layer can tolerate a definition changing? What auditability does finance actually require — a full change history of every model's definition, or just confidence that today's numbers are correct?

Reasonable answers: both consume the same underlying order and revenue data, but ad hoc analytics wants to experiment with new metrics weekly, while finance's reporting definitions must stay stable for an entire fiscal quarter at a time and need every change reviewed and traceable. Finance specifically needs to be able to say, months later, exactly what logic produced a given historical number.

Separating "exploration" from "the audited source of truth" structurally, not just by convention

The wrong answer here is one shared marts layer that both teams edit with the same review bar — a fast-iterating analytics team will inevitably want to change a metric definition in a way that, however reasonable in isolation, silently breaks a number finance already reported to auditors. The right answer is a structural split: a stable, tightly-reviewed marts/finance/ layer that only changes through a deliberate, documented process, and a separate, loosermarts/exploratory/ or similarly-named layer where ad hoc analytics can iterate freely.

marts/
  finance/                 -- stable, audited, quarter-locked definitions
    fct_revenue_recognized.sql
    dim_customers_finance.sql
  exploratory/              -- ad hoc analytics, iterates weekly, looser review
    metric_experiments/
      new_ltv_definition_v3.sql

Governance:
  marts/finance/* requires two reviewers, including one from the finance
  team itself, and any PR touching it must document exactly what changed
  and why in the PR description -- this becomes the audit trail finance needs.

  marts/exploratory/* requires one reviewer, any platform or analytics
  engineer, and is explicitly documented as "not guaranteed stable" in
  its own README so nobody builds a permanent dashboard on it by mistake.

Once a metric defined in marts/exploratory/ proves useful and stable enough that finance wants to adopt it, it gets deliberately "promoted" — rebuilt or moved intomarts/finance/ under finance's stricter review process, rather than the exploratory version silently becoming load-bearing for an audited report while still living under the loose governance it was built under.

Where documentation and version control do the audit work

Git history on marts/finance/ is itself the change-history audit trail finance needs — every change to a finance model is a reviewed, timestamped, attributed commit, exactly the kind of record an auditor asking "what logic produced Q2's revenue number" can be pointed at directly. Combining this with dbt's persisted column and model descriptions (per the documentation module) means finance's audit story is "read the git history and the generated docs for this specific model," not a separate audit system bolted on afterward.

A dbt Model Is Timing Out in Production but Runs Fine Locally — Diagnose and Fix It

Requirements gathering, even for a debugging prompt

What is different between the local run and the production run beyond just data volume — same warehouse size, same materialization, same data recency? Is this model incremental or a full table rebuild every run? Did this start happening suddenly, or has it been gradually getting slower?

Reasonable answers: the model is a table materialization (not incremental), local development uses a small sampled subset of data via a dev-target row limit, and production runs against the full, multi-billion-row source table. The slowdown has been gradual over several months as the source table has grown, not sudden.

Why "runs fine locally" is close to meaningless here

A local dev run against a small sampled dataset validates that the SQL is logically correct — it says almost nothing about whether that SQL scales to production data volume. This is the single most common false signal in exactly this kind of prompt: a candidate who treats "it works locally" as evidence the SQL itself is fine is missing that the entire problem is likely a volume-scaling issue the local environment was never capable of surfacing in the first place.

The fix — convert to incremental, and specifically diagnose why the full rebuild got slow

Given a full table rebuild every run against ever-growing data, per the performance-tuning-dbt module's guidance, the model is very likely a strong candidate for converting from atable materialization to incremental, so each run only processes new or changed rows instead of reprocessing the entire growing history every single time.

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

SELECT *
FROM {{ source('app', 'events') }}

{% if is_incremental() %}
  WHERE updated_at > (SELECT max(updated_at) FROM {{ this }})
{% endif %}

Before finalizing this as the answer, a strong candidate also checks whether the underlying query itself has a scaling problem independent of materialization — an unfiltered join fanning out unexpectedly at higher volume, a missing partition filter on the source table forcing a full table scan, or a window function computed over the entire table instead of a bounded window. Converting to incremental fixes "reprocessing everything every run"; it does not fix a query that is fundamentally inefficient per row it does process, and both should be checked rather than assuming incrementality alone is the complete fix.

Design a Macro and Package Strategy for a dbt Project Shared Across Three Business Units That Each Use a Different Warehouse

Requirements gathering

Do the three business units genuinely need different SQL per warehouse, or just the same logical transformation expressed portably? Is there existing duplicated logic across the three units' separate dbt projects today — the same surrogate-key generation, the same date-spine logic, each hand-rolled three separate times? Is the goal one unified project, or three still-separate projects that share a common macro library?

Reasonable answers: one business unit runs on Snowflake, one on BigQuery, one on Redshift, each with its own dbt project and its own team, following an acquisition that never unified the data platforms. All three have independently reinvented near-identical surrogate-key and date-spine macros, with subtle bugs in two of the three versions. The near-term goal is a shared macro library, not a single unified project — a full platform consolidation is out of scope for this design.

Why a shared package, not copy-pasted macros, is the right structural answer

The instinct to copy a working macro from one project into the other two is the wrong move for exactly the reason it was a problem in the first place — three independent copies of the same logic drift the moment any one of them gets a bug fix nobody backports to the other two. The right structural answer is extracting the shared macros into their own standalone dbt package — a separate git repository containing only macros, no models — installed into each business unit's project via packages.yml, exactly the mechanism the packages module covers.

# packages.yml, in each of the three business unit projects
packages:
  - git: "https://github.com/acme-corp/dbt-shared-macros.git"
    revision: v2.3.0

# Inside dbt-shared-macros itself:
# macros/generate_surrogate_key.sql
# macros/date_spine_portable.sql
# macros/tests/generic/test_positive_value.sql

Pinning a specific revision (a git tag, not a floating branch) in each project'spackages.yml is the detail that keeps this safe across three independently-scheduled teams: a bug fix or breaking change in the shared package does not silently propagate into all three projects on their next dbt deps run — each team upgrades to a new revision deliberately, on its own schedule, after reviewing what changed.

Writing macros that are genuinely portable across three different warehouses

A macro shared across Snowflake, BigQuery, and Redshift cannot lean on any one warehouse's proprietary SQL dialect without branching internally — exactly the problemdbt_utils and adapter dispatch solve for the community at large, and the same technique applies to an internal shared package. dbt's adapter dispatch mechanism lets a macro definition vary by the active database adapter while callers invoke one consistent macro name.

{% macro date_spine_portable(start_date, end_date) %}
  {{ return(adapter.dispatch('date_spine_portable', 'shared_macros')(start_date, end_date)) }}
{% endmacro %}

{% macro default__date_spine_portable(start_date, end_date) %}
  {{ dbt_utils.date_spine(datepart="day", start_date=start_date, end_date=end_date) }}
{% endmacro %}

{% macro bigquery__date_spine_portable(start_date, end_date) %}
  -- BigQuery-specific implementation if the default dbt_utils behavior
  -- needs an adapter-specific override for performance or dialect reasons
  {{ dbt_utils.date_spine(datepart="day", start_date=start_date, end_date=end_date) }}
{% endmacro %}

In practice, many macros need no adapter-specific override at all — leaning on dbt_utilsitself, which already handles the cross-warehouse dialect differences internally, covers the large majority of cases. Adapter dispatch matters most for the minority of genuinely warehouse-specific logic (a Snowflake-specific semi-structured column function, say) that has no single portable SQL expression across all three targets.

Testing the shared package independently of any consuming project

Because the shared package is its own repository, it should have its own CI pipeline, testing its macros against a minimal, synthetic dbt project built specifically to exercise them, rather than only being implicitly tested whenever one of the three business unit projects happens to use them. A regression in a shared macro should fail the package's own CI before it is ever tagged as a new revision, not get discovered by a business unit team days after upgrading.

A Stakeholder Asks Why Yesterday's Revenue Dashboard Number Changed After the Fact — Design a System That Prevents This

Requirements gathering

Is the number changing because of a genuine, correct late-arriving update (a refund processed a day after the original charge), or because of an unintended side effect of how the model rebuilds? Does the business actually want historical numbers to stay frozen once reported, or does it want them to reflect the most current, corrected truth even retroactively? Who needs to be notified when a previously reported number changes, and how quickly?

Reasonable answers: some of the change is genuine (late refunds, corrected charges) and some is an artifact of a table materialization being fully rebuilt nightly against a source system that itself allows backdated edits with no audit trail. Finance specifically wants a previously reported daily number to be immutable once published, with any subsequent correction shown as a separate, visible adjustment rather than silently overwriting history.

Why a plain nightly full-rebuild model cannot satisfy this requirement, structurally

A model that rebuilds from scratch every night, by definition, has no way to distinguish "today's rebuild produced a different number because new data legitimately arrived" from "today's rebuild produced a different number because a source row was silently edited in place." Both cases look identical to a full rebuild — it only ever sees the current source state, with no built-in memory of what a previous run computed. Satisfying finance's actual requirement needs an explicit historical record, not a smarter query against the same rebuild-from-scratch model.

The fix — a snapshot of the reported metric itself, not just the underlying source data

Part 05 of this module covers snapshotting a slowly-changing source table; this problem needs the same underlying idea applied one layer higher — snapshotting the daily reported output itself. A dedicated model, built once per day and never rebuilt afterward, freezes what was actually reported for that day. Any later change to the underlying source data flows into a new day's row, or an explicit adjustment row, rather than mutating a value finance already reported to stakeholders.

{{
  config(
    materialized='incremental',
    incremental_strategy='append',
    -- no unique_key here on purpose: append has no matching/dedup step,
    -- so a unique_key would be inert -- report_date + is_adjustment is
    -- just documentation of the natural grain, not enforced by dbt
  )
}}

SELECT
  current_date AS report_date,
  false AS is_adjustment,
  SUM(amount) AS reported_revenue
FROM {{ ref('fct_orders') }}
WHERE order_date = current_date

{% if is_incremental() %}
-- append-only: never overwrites a prior day's already-reported row,
-- deliberately the opposite of a merge/upsert incremental strategy
{% endif %}

Using incremental_strategy='append' rather than the more common merge is the deliberate, load-bearing decision here: a merge strategy would update an existing day's row in place the moment underlying source data changed, which is exactly the behavior finance does not want. Append-only guarantees a once-written row for a given report date is never silently touched again — any later-discovered correction becomes a new row, explicitly flagged as an adjustment via the is_adjustment column, rather than an invisible mutation of history.

Surfacing the adjustment, not hiding it

A separate, explicitly-scheduled reconciliation job compares each already-reported day's frozen number against what the current source data would produce for that same date, and inserts an adjustment row (with a clear reason code and a link back to what changed) whenever a material difference is found — rather than either silently ignoring the drift or silently overwriting the original number. The finance dashboard then sums a report date's original value plus any adjustment rows for it, showing both the original figure and a visible, explained correction rather than one quietly-changed final number.

How Would You Handle a Breaking Schema Change From an Upstream Source System With Zero Analyst-Facing Downtime?

Requirements gathering

What kind of breaking change specifically — a column renamed, a column dropped, a column's type or meaning silently changed (a currency field switching from dollars to cents, say)? Is there any advance notice from the upstream team, or does this show up as a surprise in a nightly run? Which downstream consumers would break, and how quickly does someone actually notice a broken dashboard versus a silently wrong number?

Reasonable answers: the upstream application team is renaming customer_status tolifecycle_stage on the core customers table, with two weeks' notice before the change ships, but no guarantee the exact ship date won't slip. A dozen staging and downstream models reference the old column name. Finance's revenue dashboard depends transitively on this table, and a silently broken build would not be noticed until someone opens the dashboard, possibly a full day later.

Why the fix belongs entirely inside the staging layer, not scattered across the DAG

This is exactly the scenario the project-structure module's one-staging-model-per-source-table principle exists to contain. Because every downstream model reads customer_status only through stg_app__customers, and never by querying the raw source table directly, the rename needs to be absorbed in exactly one place — the staging model aliases the new column name back to the old one downstream models already expect, and nothing outside staging/ needs to change at all.

-- models/staging/postgres_app/stg_app__customers.sql

SELECT
  customer_id,
  lifecycle_stage AS customer_status,   -- old name preserved for every downstream consumer
  updated_at
FROM {{ source('app', 'customers') }}

This buys time to plan a proper, deliberate rename across the dozen downstream models later, on the platform team's own schedule, rather than being forced into an emergency multi-model fix the moment the upstream change actually ships. The staging layer's whole purpose — insulating the rest of the DAG from exactly this kind of upstream volatility — is what makes a same-day, one-file fix possible instead of a scramble across every affected mart.

Detecting the change before it breaks anything, not after

Waiting for the rename to actually ship and break a nightly run is the reactive version of this problem; a stronger design catches it before it does. A source freshness-style contract test — checking that customer_status still exists with the expected type on every run, failing loudly the moment it does not — turns a silent schema drift into an immediate, actionable CI or nightly-run failure instead of a quietly wrong dashboard discovered a day later.

sources:
  - name: app
    tables:
      - name: customers
        columns:
          - name: customer_status
            data_tests:
              - not_null
          # a column-existence / type contract check, run every invocation,
          # fails fast the moment the upstream rename actually ships --
          # exactly when the two-week notice window runs out

Combining this with an explicit on-run-start hook or a scheduled, low-cost freshness check run independently of the main nightly job means the very first sign of the upstream change is a clear, attributable test failure with a specific column name in the error, not a vague "the finance number looks off" report from a stakeholder hours later.

Trade-offs worth stating explicitly

Aliasing the new column back to its old name at the staging boundary is a deliberate, temporary shim, not a permanent solution — leaving customer_status as the name used across a dozen downstream models forever means new team members keep learning a name the source system itself no longer uses, a small but real ongoing cost. A strong answer schedules the deliberate downstream rename as a tracked follow-up task with an owner and a rough timeline, rather than treating the staging-layer shim as the finished, permanent answer.

Design a dbt Project to Support Both a Nightly Batch Refresh and a Near-Real-Time Dashboard Requirement From the Same Underlying Data

Requirements gathering

What does "near-real-time" actually mean here — sub-minute, or is a 15-minute lag genuinely acceptable? Does every metric need the fast path, or only a specific subset (an operations dashboard watching order volume right now, say, while a monthly cohort-retention report is fine staying on the nightly cadence)? What is the cost tolerance — is running part of the pipeline every few minutes, all day, an acceptable warehouse spend increase, or does that need to be minimized?

Reasonable answers: an operations team wants order volume and fulfillment-delay metrics refreshed roughly every 10 minutes during business hours; every other metric in the project — finance, marketing attribution, cohort retention — is genuinely fine on the existing nightly cadence. Cost matters: the operations team's request should not multiply the compute bill for the entire 300-model project by running everything every 10 minutes.

Why this is a scoping problem before it is a scheduling problem

The naive answer is standing up a second, faster overall schedule for the whole project — this fails the stated cost constraint immediately, since the overwhelming majority of models genuinely do not need it, and it multiplies warehouse spend for zero benefit on the models that stay nightly. The right answer scopes the fast path down to exactly the handful of models operations actually needs fast, using dbt's node selection to run two schedules against the same project rather than maintaining two separate projects or duplicating models.

-- models/marts/ops/fct_order_volume_realtime.sql
{{ config(materialized='incremental', tags=['realtime']) }}
SELECT ...

-- models/marts/ops/fct_fulfillment_delay_realtime.sql
{{ config(materialized='incremental', tags=['realtime']) }}
SELECT ...

-- Two separate scheduled jobs against the same project:
-- Job A (every 10 min, business hours only):
--   dbt build --select tag:realtime+
-- Job B (nightly, full project):
--   dbt build --exclude tag:realtime

tag:realtime+ selects the two tagged models plus everything downstream of them, keeping the fast job scoped to exactly the small slice of the DAG operations actually depends on; the nightly job excludes those same models so they are not needlessly rebuilt twice on the same cadence they were already just refreshed on. This is the same node-selection mechanism Slim CI'sstate:modified+ already relies on, applied here to a scheduling problem instead of a change-detection one.

Where the two schedules can safely share upstream staging models, and where they cannot

Both the realtime and nightly paths ultimately read from the same stg_app__ordersstaging model, and that staging model itself needs to run on the faster cadence too — a realtime mart built on top of a staging model that only refreshes nightly is not actually realtime, it is nightly data wearing a realtime label. This is why the + in tag:realtime+ matters: it is deliberately the two realtime models plus their full upstream lineage, not just the two leaf models in isolation, ensuring every model actually feeding the fast dashboard runs on the fast schedule too.

Because stg_app__orders now runs on both schedules — once every 10 minutes for the realtime path and once nightly as part of the full project build — the nightly job's--exclude tag:realtime only needs to exclude the two realtime marts themselves, not the shared staging model beneath them; rebuilding that staging model once more as part of the full nightly run is cheap and harmless, and keeps the nightly build a complete, self-contained run that does not depend on the realtime job having succeeded earlier in the day.

Trade-offs worth stating explicitly

Running the shared staging model on two overlapping schedules means it is genuinely possible for the 10-minute job and the nightly job to overlap in the warehouse at the same time, which needs either a concurrency-safe incremental strategy (merge, not append, so a second concurrent run does not duplicate rows) or an explicit scheduling guard preventing true overlap. A strong answer names this concurrency risk directly rather than assuming two schedules against the same underlying models are automatically safe to run side by side.

A New Analyst Keeps Accidentally Breaking Production Models — Design a System That Makes That Structurally Hard to Do

Requirements gathering

Is this a process problem (no review happening before merge) or a knowledge problem (reviews happen, but reviewers themselves miss the same class of mistake repeatedly)? What specifically keeps breaking — a renamed column downstream models still reference, a materialization change that quietly triggers an expensive full rebuild, a test someone deletes because it was "in the way"? How large is the team, and how experienced are the people actually merging changes?

Reasonable answers: a team of two senior platform engineers and four newer analytics engineers who joined in the last six months. PRs are reviewed, but reviewers are themselves stretched thin across a 300-model project and occasionally approve a change that compiles and passes the tests it touches while still silently breaking an unrelated downstream model the reviewer did not think to check.

Why "review harder" is not a scalable answer

Asking two senior engineers to review every PR more carefully does not scale as the team and project grow, and it is exactly the kind of fix that works for a week and quietly erodes once review fatigue sets back in. The stronger answer makes the failure mode structurally harder to hit in the first place — shifting weight from "a human remembers to check this" to "the system itself catches this automatically," which is the same underlying philosophy Part 03's CI design and this module's interview-traps section both apply to testing.

None of these three fixes rely on a reviewer being more careful or more experienced — each one moves the check into the CI pipeline itself, so the newest analytics engineer on the team is protected by the exact same automated guardrail a ten-year veteran would be. This is the general shape of a strong answer to "how do you stop people from making the same mistake" in almost any system-design context, not just dbt specifically: prefer a structural, automatic check over asking people to individually remember more things.

What still genuinely requires human judgment, and should not be automated away

Not every failure mode reduces to an automatable check — whether a new metric's business logic is actually correct, for instance, is a judgment call no CI pipeline can make. The realistic answer is not "automate everything," it is drawing a clear line: mechanical, structural mistakes (a broken reference, an unreviewed cost spike, a silently deleted test) get an automated guardrail; business- logic correctness stays a human review responsibility, concentrated specifically on the highest-blast-radius folders per Part 03's tiered review policy, rather than spread thin evenly across every PR regardless of risk.

Trade-offs worth stating explicitly

Each new automated guardrail is itself a small piece of infrastructure that needs maintaining, and an overly aggressive one (flagging too many harmless changes) trains people to ignore its warnings entirely, which is worse than not having the check at all. A strong answer proposes starting with the highest-leverage, clearest-signal guardrail first — the state:modified+ downstream-breakage check, which has essentially no false positives — before adding the noisier, judgment-adjacent checks like the cost-threshold flag, rather than shipping all three simultaneously and risking alert fatigue on day one.

dbt Vocabulary Cheat Sheet

A dense, precise reference covering every major term used across the 20-module track — built for interview cramming, not first-time learning. If a definition here is unclear, the module it maps to covers it in full depth.

CLI Command Quick Reference

A companion to the vocabulary table above, scoped specifically to the commands and flags this module's worked examples actually invoke — useful for translating a design discussion directly into the command an interviewer might ask you to type or explain.

Wrong Answers Candidates Commonly Give, and Why They're Wrong

12 Quick-Recall Questions — Short, Complete Answers

What All Twelve Worked Examples Actually Have in Common

Read back over Parts 01 through 12 and a small set of recurring moves accounts for nearly all of them: push a boundary problem (an upstream rename, a legacy migration, a fast-versus-slow cadence split) down into the single layer built to absorb it, rather than letting it leak across the whole DAG; reach for a snapshot the instant a question needs point-in-time history a mutate-in-place model has already destroyed; scope any expensive operation — a CI build, a fast refresh schedule, a review — to the smallest correct blast radius instead of applying it uniformly; and state the resulting trade-off out loud instead of presenting a design as free. None of these are dbt-specific tricks — they are general systems-design instincts that happen to have a specific, idiomatic dbt expression in every one of these twelve prompts.

That is also why cramming the vocabulary table and rapid-fire section alone, without working through at least a few of the worked examples by hand, tends to fall apart under a genuinely novel prompt on interview day. Vocabulary answers "what is this called"; the worked examples build the actual reasoning habit of reaching for the right one of these recurring moves when a new, unfamiliar combination of requirements shows up — which is the harder and more valuable skill this module, and this entire track, was built to leave you with.