Data Analyst & Analytics Interview · Data Modeling & the Modern Stack · Lesson 2 of 2
dbt, data quality & the semantic layer
Modern analytics teams transform data inside the warehouse with version-controlled SQL — the analytics engineering workflow, with dbt as its standard tool. 2026 loops increasingly assume you can talk about models, tests, and where a metric's definition lives.
- A dbt model is just a SELECT in a file; dbt materializes it as a view or table, and
ref()wires models into a dependency DAG that builds in order. - Layering: staging (clean/rename raw sources) → intermediate (business logic) → marts (the facts and dims analysts query).
- Tests: declare
unique,not_null,accepted_values,relationshipson columns — the build fails before bad data reaches a dashboard. - Docs & lineage: every model documented, lineage traced from source to dashboard — the working answer to 'can I trust this number?'
The semantic layer — and why AI made it matter
- A semantic layer defines each metric once, in code (revenue, active user), so every dashboard — and every AI agent — computes the same number.
- The 2026 twist: text-to-SQL AI grounded in a well-modeled semantic layer is far more accurate than AI pointed at raw tables. Clean modeling became AI infrastructure.
- Your role shifts up a level: define the metrics, model the data, verify AI-generated output — not hand-write every query.
Metrics defined onceIf 'revenue' is re-derived in five dashboards, it drifts five ways (refunds in or out? tax? currency?). Defining it once in a mart or semantic layer is the structural fix — and the answer interviewers want to 'two dashboards disagree'.
AI-assisted roundsMany 2026 loops let (or expect) you to use an AI assistant for SQL. They are grading verification: do you check the join grain, row counts, and NULL handling before trusting the output? Treat AI SQL as a fast draft from a junior analyst — and review it like one.
◆ Lock it in
- dbt = version-controlled SQL models in layers (staging → marts) with a dependency DAG.
- Tests (
unique,not_null…) fail the build before bad data hits dashboards. - A semantic layer defines each metric once — for humans and AI agents alike; your job now includes verifying AI-generated SQL.
Feynman drill — say it out loudExplain to a stakeholder why two dashboards can show different 'revenue', and how dbt tests plus a semantic layer prevent it.
Step 1 rate your confidence · Step 2 pick your answer
A dbt unique test on orders.order_id fails after a new source lands. What did it just save you from?
Step 1 — how sure are you?