dbt

Your most-viewed Looker dashboard sits on fct_revenue_daily, a dbt model materialized as a view that sits on top of a 9-model join chain. Every dashboard load re-executes the whole chain; p95 load time is ~40s and the warehouse bill for that one tile is embarrassing. A teammate says "just make it a table." The interviewer wants you to actually reason about dbt's materializations — which one fits here, what each costs, and why "make everything a table" is the wrong reflex.

A daily fct_orders incremental model filters new rows with {% if is_incremental() %} where updated_at > (select max(updated_at) from {{ this }}) {% endif %}. Finance noticed the model is missing ~0.4% of orders every month — specifically orders whose updated_at is backdated (an upstream system corrects records hours-to-days late). They never appear because by the time they land, max(updated_at) has already moved past them. Walk through why the high-watermark filter loses late data, the incremental strategy you'd switch to, and how you'd reprocess correctly without rebuilding 3 years of history nightly.

dim_customers is rebuilt every run from the source and only ever holds each customer's CURRENT tier and region. Finance just asked: "what tier was customer #4012 on the day they placed order #88?" — and nobody can answer, because the day a customer upgrades, yesterday's tier is overwritten and gone. The source system has no history table; it only exposes current state. The interviewer wants you to design slowly-changing- dimension (SCD2) history capture with dbt snapshots, pick the right strategy, and be honest about what you can and can't recover.

Your dbt project has ~800 models. CI builds the ENTIRE DAG on every PR and takes ~90 minutes, so people merge on red "to save time." Last week a PR renamed a column in a stg_ model; CI passed, and 40 downstream models broke in production the next morning because nothing validated the contract between producer and consumer. The interviewer wants you to redesign CI so it's fast (build only what changed) AND actually catches breaking changes — and to be specific about the dbt features that do each job.

Two dbt marts disagree on Q1 revenue: fct_revenue says ₹4.20 Cr, fct_sales_summary says ₹4.71 Cr. Both are "blessed" models on exec dashboards, both written by different analysts, and the 12% gap has finance refusing to trust either. You trace it: one model joins orders to a one-to-many order_items (or a multi-row shipments) table BEFORE summing order_total, fanning out and double-counting revenue; and the two models reimplement revenue with subtly different CASE-WHEN logic. The interviewer wants the debugging arc AND the structural redesign so this can't recur.