The Modern Data Stack in 2026, Explained Simply

The modern data stack is the name for how a data team in 2026 actually moves numbers from the apps that create them to the dashboards, models, and downstream tools that consume them — a set of managed, cloud-native services wired together in a predictable order rather than one monolithic ETL server trying to do everything at once. It starts where data is born (a production database, a payment processor, an ad platform), pulls those records through a data ingestion layer, lands them in a cloud warehouse or lakehouse, reshapes them in place, and finally pushes the clean, modeled result back out to the people and systems that need it. Every arrow in that sentence is a layer you can buy, swap, or replace on its own, which is the whole point: the stack is modular, and each piece is built to do one job well.

This guide is the plain-English walkthrough you wished existed the first time someone drew five boxes on a whiteboard and expected you to know what each one was for. It opens the stack layer by layer: the ingestion connectors that land raw source data, the warehouse-versus-lakehouse decision at the storage layer, the dbt transformation step that turns raw tables into trustworthy models, the orchestration and observability that keep the whole pipeline running on schedule, and the activation layer — BI dashboards and reverse ETL — that puts modeled data back into the hands of the business. Along the way it explains the one architectural shift that defines the modern era, ELT replacing ETL, and where data governance lives once your data is spread across a dozen managed tools. Each section pairs a teaching block with a worked interview answer — code, a step-by-step trace, an output table, then a concept-by-concept breakdown of why it works.

When you want hands-on reps immediately after reading, drill the ETL practice library →, work through the data-transformation practice library →, and sharpen the fundamentals on the database practice library →.

---

On this page

• What the modern data stack actually is
• The ingestion and storage layer — warehouse and lakehouse
• The transformation layer — dbt and ELT
• Orchestration and observability
• Activation — BI, reverse ETL, and the 2026 shifts
• Cheat sheet — modern data stack recipes
• Frequently asked questions
• Practice on PipeCode

---

1. What the modern data stack actually is

Five modular layers connected by ELT — load raw data first, transform it in the warehouse

The one-sentence invariant: the modern data stack is a modular assembly of managed, cloud-native tools — ingestion, a cloud warehouse or lakehouse, in-warehouse transformation, orchestration, and activation — connected by the ELT pattern (load raw first, transform in place) rather than the older ETL pattern (transform before load), so each layer can be swapped independently and the warehouse's elastic compute does the heavy lifting instead of a hand-rolled server. The word "stack" is doing real work here: it is not one product, it is a sequence of specialized products, each responsible for exactly one hand-off, and the reason the industry converged on this shape is that cloud storage got cheap and cloud compute got elastic at the same time — which quietly rewrote every assumption the previous generation of tools was built on.

The five layers that matter.

• Ingestion. Pulls raw records out of source systems — production databases, SaaS APIs, event streams, files — and lands them, untransformed, in your storage layer. Tools: managed connectors (Fivetran, Airbyte), CDC readers (Debezium), and event pipelines.
• Storage. A cloud warehouse (Snowflake, BigQuery, Redshift, Databricks SQL) or a lakehouse (Databricks, an open Iceberg/Delta table over object storage) that holds both the raw landing zone and the modeled output, separating storage from compute so you pay for each independently.
• Transformation. In-warehouse modeling — almost always dbt — that turns raw tables into clean, tested, documented models using version-controlled SQL. This is the layer that produces the tables analysts and models actually trust.
• Orchestration and observability. The scheduler (Airflow, Dagster, Prefect, dbt Cloud) that runs every step in the right order with retries and backfills, plus the observability that tells you when freshness, volume, or schema drifted.
• Activation. The last mile that puts modeled data to work — BI dashboards and a semantic layer for people, reverse ETL for machines (syncing modeled tables back into the SaaS tools that operate the business).

The ELT-vs-ETL shift — the one idea that defines the era.

• Old ETL. Extract from the source, Transform on a separate server (Informatica, a Python box, an on-prem cluster), then Load the finished tables into a warehouse. Transformation happened before the data landed because warehouse storage and compute were expensive and coupled — you could not afford to store messy raw data or to run heavy SQL on the warehouse.
• New ELT. Extract, Load the raw data straight into the warehouse first, then Transform it in place with SQL. Cheap object storage means you can afford to keep every raw row; elastic, separated compute means you can run enormous transformations on demand and turn the compute off when you are done.
• Why the flip matters. ELT makes the raw data a permanent, replayable asset — if a transformation is wrong, you fix the SQL and re-run against raw, no re-extraction needed. It also moves transformation logic out of bespoke code and into version-controlled SQL that analysts can own.
• The trade-off. ELT concentrates cost and governance in the warehouse. You now store more data and run more compute there, so cost control (FinOps) and access control become first-class concerns rather than afterthoughts.

The 2026 reality — consolidation, AI, and convergence.

• Consolidation. The "one tool per box" sprawl of 2020 is collapsing. Platforms now bundle ingestion + storage + transformation, and teams deliberately trim the number of vendors to cut cost and integration overhead.
• AI in every layer. Text-to-SQL copilots, automated documentation, anomaly detection, and pipeline-authoring assistants are now standard features, not experiments.
• Zero-ETL and convergence. Cloud vendors ship "zero-ETL" links that replicate an operational database into the warehouse with no connector to manage, and the warehouse and lakehouse are converging into one queryable surface over open table formats.
• Governance moves left. Data contracts, catalogs, and lineage are increasingly enforced at ingestion and transformation time rather than bolted on at the end.

What interviewers listen for.

• Do you name the five layers without prompting, in order? — baseline signal.
• Do you explain ELT vs ETL as "load raw then transform in the warehouse" and say why (cheap storage, elastic separated compute)? — required answer.
• Do you describe transformations as idempotent and reproducible (re-runnable against raw) rather than "we clean the data"? — senior signal.
• Do you place governance as a cross-cutting concern (catalog, access, lineage) rather than a single box? — senior signal.

Worked example — mapping a business need to the five layers

Detailed explanation. The fastest way to prove you understand the stack is to take a concrete business request and route it through all five layers, naming the tool category and the artifact each layer produces. Walk through "the growth team wants a daily active-customers dashboard, and wants churned customers pushed back into the CRM."

• The source. A production Postgres orders table plus a Stripe billing feed.
• The destination. A BI dashboard for people and a CRM audience for the growth team's playbooks.
• The constraint. Daily freshness is fine; no sub-minute latency needed.

Question. Map the request to each of the five layers and name the concrete artifact each layer produces.

Input.

Layer	Tool category	Artifact it produces
Ingestion	managed connector / CDC	raw.orders, raw.stripe_invoices landed daily
Storage	cloud warehouse	schemas: raw, staging, marts
Transformation	dbt	stg_orders, fct_active_customers
Orchestration	Dagster / Airflow	a scheduled DAG: ingest → dbt → test
Activation	BI + reverse ETL	a dashboard + a churned_customers CRM sync

Code.

Request:  "daily active-customers dashboard + churned customers into the CRM"

sources ─▶ INGESTION ─▶ STORAGE (raw) ─▶ TRANSFORM ─▶ STORAGE (marts) ─┬─▶ BI dashboard
 (Postgres,   (connector    (warehouse    (dbt: stg_    (fct_active_     │
  Stripe)      + CDC)         raw schema)   → fct_)       customers)      └─▶ REVERSE ETL ─▶ CRM
                                                                              (churned_customers)
        ▲───────────────────────  ORCHESTRATION coordinates every arrow  ──────────────────────▲
        └───────────────────────  GOVERNANCE (catalog, access, lineage) spans every box  ──────┘

Step-by-step explanation.

1. Ingestion is responsible only for landing — it never cleans. A connector pulls orders and stripe_invoices into a raw schema on a daily schedule, preserving the source shape exactly so the raw zone stays a faithful, replayable copy.
2. Storage holds three logical zones in one warehouse: raw (untouched landings), staging (lightly cleaned one-to-one models), and marts (business-ready tables). Separating storage from compute means these zones cost storage dollars whether or not anyone queries them, and compute dollars only when a transformation or query runs.
3. Transformation is where dbt turns raw.orders into stg_orders (renamed, typed, deduped) and then into fct_active_customers (the business definition of "active"). The logic lives in Git, is tested, and can be re-run end-to-end against raw at any time.
4. Orchestration schedules the whole chain — ingest first, then dbt run, then dbt test — with retries on failure and a daily cadence. If ingestion is late, the transform waits; the scheduler encodes those dependencies.
5. Activation forks: the same fct_active_customers mart feeds a BI dashboard for humans, while a reverse-ETL sync reads a churned_customers mart and upserts those rows into the CRM so the growth team can act on them inside their own tool.

Output.

Consumer	Layer that serves it	Freshness
Growth team dashboard	Activation (BI)	daily
CRM churn audience	Activation (reverse ETL)	daily
Ad-hoc analyst query	Storage + Transformation	on demand
Audit / lineage question	Governance (cross-cut)	always

Rule of thumb. For any request, answer in layers: where does the data come in, where does it live, how is it modeled, what runs it, and how does it get used? If you can name the artifact each layer produces, you understand the stack.

Worked example — the same transformation, ETL vs ELT

Detailed explanation. The clearest way to feel the ETL→ELT shift is to write the same transformation both ways: once as pre-load Python (ETL) and once as in-warehouse SQL (ELT). The business logic — "daily revenue per customer, in dollars" — is identical; only where it runs changes.

• ETL version. A Python job extracts rows, transforms them in memory, and loads only the finished aggregate. The raw rows never land in the warehouse.
• ELT version. A connector loads raw rows as-is; a dbt/SQL model aggregates them in the warehouse afterward.
• The tell. In ELT, raw.orders still exists after the model runs — so you can fix and re-run without re-extracting.

Question. Implement the "daily revenue per customer" logic as ETL (pre-load Python) and as ELT (in-warehouse SQL), and explain what each leaves behind.

Input.

Aspect	ETL (transform-then-load)	ELT (load-then-transform)
Where transform runs	app server / Python box	the warehouse
What lands in warehouse	finished aggregate only	raw rows + aggregate
Re-run after a logic bug	re-extract from source	re-run SQL against raw
Who owns the logic	data engineer (code)	analytics engineer (SQL)

Code.

# ETL — transform BEFORE load; only the aggregate reaches the warehouse
import psycopg2, snowflake.connector
from collections import defaultdict

src = psycopg2.connect("host=oltp dbname=production user=reader")
rows = src.cursor()
rows.execute("SELECT customer_id, total_cents, created_at::date AS d FROM orders")

agg = defaultdict(int)
for customer_id, total_cents, d in rows:          # transform in Python memory
    agg[(d, customer_id)] += total_cents

wh = snowflake.connector.connect(account="acct", user="loader")
cur = wh.cursor()
for (d, customer_id), cents in agg.items():        # load ONLY the result
    cur.execute(
        "INSERT INTO analytics.daily_customer_revenue(day, customer_id, revenue_usd) "
        "VALUES (%s, %s, %s)", (d, customer_id, cents / 100.0))
# raw.orders never exists in the warehouse; a logic change forces a full re-extract

-- ELT — load raw first (a connector did that), then transform IN the warehouse
-- raw.orders is a faithful copy of the source; this model is re-runnable anytime
CREATE OR REPLACE TABLE analytics.daily_customer_revenue AS
SELECT
    created_at::date        AS day,
    customer_id,
    SUM(total_cents) / 100.0 AS revenue_usd
FROM raw.orders                    -- the raw landing zone stays put
GROUP BY 1, 2;

Step-by-step explanation.

1. The ETL job does the aggregation in Python before anything reaches the warehouse. It is memory-bound (it holds agg for the whole table) and the only thing it persists downstream is the finished daily_customer_revenue table.
2. Because ETL discards the raw rows, a bug in the aggregation ("we should have excluded refunds") means re-extracting from the source database — extra load on the OLTP system and a slower fix.
3. The ELT version assumes a connector already loaded raw.orders verbatim. The transformation is a single SQL statement that the warehouse executes with elastic compute, then releases that compute.
4. Because raw.orders still exists, the same bug is a one-line SQL fix plus a re-run — no source re-extraction, no OLTP load, and the change is a reviewable diff in Git.
5. Ownership shifts too: the ETL logic is Python that a data engineer maintains; the ELT logic is SQL that an analytics engineer can own, review, and test with the rest of the model layer.

Output.

After the run	ETL leaves behind	ELT leaves behind
raw.orders in warehouse	absent	present (replayable)
Finished aggregate	present	present
Cost of fixing a logic bug	re-extract from OLTP	re-run SQL against raw
Compute location	app server	warehouse (elastic)

Rule of thumb. If your transformation runs before the data lands, you are doing ETL and throwing away replayability. Load raw first, transform in the warehouse, and keep the raw zone — that is ELT, and it is why the modern stack looks the way it does.

Worked example — the "draw the stack" whiteboard answer

Detailed explanation. A common opener is "draw us the data stack you'd stand up for a Series-B SaaS company." The strong answer is a labeled five-box diagram with the cross-cuts (orchestration, governance) drawn around the boxes, not inside them. Walk through the canonical layout.

• Left to right. Sources → ingestion → storage → transformation → activation.
• Across the top. Orchestration, coordinating every hand-off.
• Underneath. Governance — catalog, access, lineage — spanning all boxes.

Question. Produce the labeled reference diagram and name a representative tool category for each box.

Input.

Position	Box	Representative category
Source	app DB, SaaS, events	Postgres, Stripe, Segment
1	Ingestion	managed connector + CDC
2	Storage	warehouse or lakehouse
3	Transformation	dbt
4	Activation	BI + reverse ETL

Code.

                 ┌──────────────── ORCHESTRATION (schedule · retries · backfills) ────────────────┐
                 │                                                                                 │
  SOURCES ──▶ 1. INGESTION ──▶ 2. STORAGE ──▶ 3. TRANSFORMATION ──▶ 2. STORAGE ──▶ 4. ACTIVATION
 (Postgres,     (connector       (warehouse /     (dbt: raw →           (marts)        (BI dashboards
  Stripe,        + CDC)           lakehouse:        staging → marts)                     + reverse ETL)
  events)                         raw zone)
                 │                                                                                 │
                 └──────────────── DATA GOVERNANCE (catalog · access control · lineage) ───────────┘

Step-by-step explanation.

1. The horizontal spine is the data's journey: it enters at ingestion, rests in storage, is reshaped in transformation, returns to storage as marts, and exits through activation. Drawing it left-to-right shows you understand data flows.
2. Storage appears twice on purpose — once as the raw landing zone (input to transformation) and once as the marts zone (output of transformation). Both live in the same warehouse; the distinction is logical, not physical.
3. Orchestration is drawn as a bar across the top because it is not a stage the data passes through — it is the controller that decides when each stage runs and in what order.
4. Governance is drawn as a bar underneath every box because a catalog entry, an access policy, and a lineage edge exist for artifacts in every layer, from raw landings to CRM syncs.
5. Naming one representative tool category per box (not a specific vendor) signals that you understand the roles and can swap vendors without redrawing the architecture — the defining property of a modular stack.

Output.

Element	Where it is drawn	Why
Five stage boxes	left-to-right spine	data flows in one direction
Storage	twice (raw + marts)	one warehouse, two logical zones
Orchestration	bar across the top	it controls, it is not a stage
Governance	bar underneath	it spans every artifact

Rule of thumb. Draw the stack as a spine with two cross-cutting bars. If orchestration or governance ends up as a single inline box, you have mislabeled a cross-cutting concern as a stage — the classic junior mistake.

Data engineering interview question on the modern data stack

A senior interviewer often opens with: "A Series-B SaaS company runs everything on a single Postgres with a pile of cron-driven Python scripts. They want trustworthy dashboards and the ability to push segments back into their CRM. Design the modern data stack you'd migrate them to — name each layer, justify ELT over ETL, and say where governance lives."

Solution Using a layered ELT reference architecture with governance as a cross-cut

# Reference stack (vendor-neutral roles)

1. INGESTION      managed connectors for SaaS APIs (Stripe, HubSpot);
                  log-based CDC for the Postgres OLTP → land into raw.*
2. STORAGE        one cloud warehouse; schemas raw / staging / marts;
                  storage and compute billed separately
3. TRANSFORM      dbt project: raw → staging (1:1 clean) → marts (business models);
                  version-controlled SQL, tested, documented
4. ORCHESTRATION  scheduled DAG: (a) run connectors, (b) dbt run, (c) dbt test;
                  retries + daily backfill window
5. ACTIVATION     BI + semantic layer for humans; reverse ETL for the CRM
   GOVERNANCE     catalog every model, role-based access on marts,
                  column masking on PII, lineage from source to CRM

-- The three-zone contract inside the one warehouse
CREATE SCHEMA IF NOT EXISTS raw;       -- connector landing zone (never edited by hand)
CREATE SCHEMA IF NOT EXISTS staging;   -- 1:1 cleaned models (types, names, dedupe)
CREATE SCHEMA IF NOT EXISTS marts;     -- business-ready facts and dimensions

-- Governance: analysts read marts, never raw; PII stays masked
GRANT USAGE  ON SCHEMA marts   TO ROLE analyst;
GRANT SELECT ON ALL TABLES IN SCHEMA marts TO ROLE analyst;
REVOKE ALL   ON SCHEMA raw     FROM ROLE analyst;   -- raw is engineer-only

Step-by-step trace.

Step	Before (Postgres + cron)	After (modern stack)
Ingestion	ad-hoc Python SELECT scripts	managed connectors + CDC into raw
Storage	one OLTP database	warehouse with raw/staging/marts zones
Transformation	inline SQL scattered in scripts	dbt DAG, versioned + tested
Orchestration	crontab, no dependencies	scheduled DAG with retries + backfills
Activation	manual CSV exports	BI dashboards + reverse ETL to CRM
Governance	none (everyone hits prod)	catalog + role access + PII masking + lineage

After the migration the OLTP database is no longer the analytics database — CDC feeds a warehouse instead, so heavy queries never touch production. Transformations are reviewable Git diffs, the whole pipeline runs on a schedule with retries, and the growth team gets both a dashboard and a CRM sync from the same governed marts.

Output:

Metric	Before	After
Analytics load on production OLTP	heavy (direct queries)	~zero (CDC only)
Transformation reproducibility	none (imperative scripts)	full (re-run dbt vs raw)
Pipeline failure handling	silent cron failures	retries + alerts
Access control on PII	none	role-based + column masking
Time to add a new source	days of scripting	hours (add a connector)

Why this works — concept by concept:

• ELT over ETL — loading raw into the warehouse first makes every transformation replayable and moves logic into version-controlled SQL. A logic change is a Git diff plus a re-run, not a re-extraction from the source database.
• Three-zone storage contract — raw / staging / marts gives every table a known trust level. Analysts read only marts; engineers own raw; staging is the cleaning boundary between them.
• CDC-fed ingestion — log-based change data capture moves analytics load off the OLTP database entirely, so dashboards can never slow down the app that pays the bills.
• Governance as a cross-cut — catalog, role-based access, column masking, and lineage apply to artifacts in every layer, so they are designed in from day one rather than retrofitted after a breach.
• Cost — one warehouse (storage + elastic compute, billed separately), a handful of managed connectors, and a dbt project. Compared to a growing pile of cron scripts, the marginal cost of a new source drops from O(days of code) to O(hours of config), and analytics never competes with production for CPU.

ETL
Topic — etl
ETL and ELT pipeline design problems

Practice →

Data Topic — data-transformation Data-transformation pipeline problems

Practice →

---

2. The ingestion and storage layer — warehouse and lakehouse

Connectors land raw source data; a warehouse or lakehouse holds it with storage separated from compute

The mental model in one line: the ingestion layer's only job is to land raw source records — via managed connectors, log-based CDC, batch API pulls, or event streams — into a storage layer that is either a cloud warehouse (a managed columnar SQL engine like Snowflake, BigQuery, or Redshift) or a lakehouse (open Iceberg/Delta tables over object storage), both of which separate storage from compute so you can keep every raw row cheaply and burst compute only when you transform or query. Ingestion never cleans and never aggregates; the moment it starts transforming, you have slid back into ETL and lost the replayable raw zone that makes everything else in the stack safe.

The ingestion patterns.

• Managed connectors. Hosted extractors (Fivetran, Airbyte) that know the schema and pagination of hundreds of SaaS APIs and databases. You configure credentials and a schedule; they land raw tables and handle schema drift. This is the default for SaaS sources.
• Log-based CDC. For operational databases, a change-data-capture reader tails the write-ahead log and streams every insert/update/delete into the warehouse with near-zero load on the source. This is the default for your own OLTP.
• Batch API pulls and files. Custom pulls for long-tail sources — a partner's REST API, a nightly S3 drop of CSVs — usually written as small extract tasks.
• Full vs incremental. A full load re-reads the entire source each run (simple, expensive); an incremental load reads only rows changed since a high-watermark (cheap, the production default). Incremental needs a reliable cursor: an updated_at column, a monotonic id, or a CDC log position.

Warehouse vs lakehouse.

• Cloud warehouse. A managed, columnar, ACID SQL engine. You load tables and query them with SQL; the vendor manages files, indexing, and clustering under the hood. Strengths: dead-simple SQL, strong performance out of the box, mature governance. Examples: Snowflake, BigQuery, Redshift.
• Lakehouse. Open table formats (Apache Iceberg, Delta Lake, Apache Hudi) layered over cheap object storage (S3, GCS, ADLS), giving warehouse-like ACID transactions, schema evolution, and time travel on top of plain Parquet files. Strengths: open format (no lock-in), one copy of data queryable by many engines, cheap storage. Examples: Databricks, Iceberg + a query engine.
• The convergence. In 2026 the two are blurring: warehouses can query external Iceberg tables, and lakehouses ship SQL warehouses on top. The practical decision is about lock-in tolerance, existing tooling, and workload mix, not a religious war.

The raw landing zone — schema-on-write vs schema-on-read.

• Schema-on-write (warehouse default). The table's columns and types are fixed when you load; the connector maps source fields to typed columns. Clean and fast to query, but schema drift requires the connector to add columns.
• Schema-on-read (lakehouse-friendly). Land semi-structured data (JSON, Parquet) and impose structure at query time. Flexible for messy or evolving sources; you pay a parsing cost on read.
• The raw contract. Whatever the mechanism, the raw zone should be an append-mostly, faithful copy of the source. Do not edit it by hand; treat it as immutable input to transformation.

What interviewers listen for.

• Do you make loads idempotent — re-running a load yields the same table, no duplicates? — required answer.
• Do you name an incremental cursor (updated_at, monotonic id, CDC LSN) rather than "we just reload it"? — senior signal.
• Do you say separation of storage and compute when asked why the cloud warehouse changed everything? — required answer.
• Do you know why Parquet + an open table format (Iceberg/Delta) matters for a lakehouse (columnar + ACID + no lock-in)? — senior signal.

Worked example — an idempotent incremental connector load

Detailed explanation. The canonical ingestion task: pull rows changed since the last high-watermark, stage them, then MERGE them into the raw table so re-running never creates duplicates. Walk through a daily incremental load of orders into raw.orders.

• Cursor. updated_at on the source, plus a stored watermark.
• Staging. Land the delta into a scratch raw._orders_delta first.
• Merge. Upsert by primary key so re-runs are idempotent.

Question. Implement an idempotent incremental load that merges the day's changed orders into raw.orders without duplicating rows on re-run.

Input.

Parameter	Value
Source	Postgres public.orders
Cursor column	updated_at
Target	raw.orders (warehouse)
Merge key	id
Load mode	incremental (delta by watermark)

Code.

-- 1. Land only the delta into a scratch table (idempotent stage: truncate first)
TRUNCATE TABLE raw._orders_delta;

INSERT INTO raw._orders_delta
SELECT id, customer_id, total_cents, status, created_at, updated_at
FROM   source.public.orders              -- e.g. via a federated/external read or connector stage
WHERE  updated_at > (SELECT COALESCE(MAX(loaded_watermark), '1970-01-01')
                     FROM raw._load_state WHERE table_name = 'orders');

-- 2. MERGE the delta into the raw table — upsert by primary key (idempotent)
MERGE INTO raw.orders AS tgt
USING raw._orders_delta AS src
   ON tgt.id = src.id
WHEN MATCHED THEN UPDATE SET
     customer_id = src.customer_id,
     total_cents = src.total_cents,
     status      = src.status,
     updated_at  = src.updated_at
WHEN NOT MATCHED THEN
     INSERT (id, customer_id, total_cents, status, created_at, updated_at)
     VALUES (src.id, src.customer_id, src.total_cents, src.status,
             src.created_at, src.updated_at);

-- 3. Advance the watermark to the max actually observed (not to "now")
MERGE INTO raw._load_state AS s
USING (SELECT 'orders' AS table_name, MAX(updated_at) AS wm FROM raw._orders_delta) d
   ON s.table_name = d.table_name
WHEN MATCHED     THEN UPDATE SET loaded_watermark = COALESCE(d.wm, s.loaded_watermark)
WHEN NOT MATCHED THEN INSERT (table_name, loaded_watermark) VALUES (d.table_name, d.wm);

Step-by-step explanation.

1. Step 1 truncates the scratch delta table before filling it, so the stage is itself idempotent — a retried run starts from an empty delta rather than appending to a half-filled one.
2. The delta query reads only rows with updated_at greater than the stored watermark. On the first run the watermark defaults to the epoch, so the initial load captures the entire table; every run after that is a cheap incremental slice.
3. Step 2 is the idempotency core: MERGE ... ON tgt.id = src.id updates rows that already exist and inserts rows that do not. Running the same delta twice produces the identical target table — no duplicate ids, ever.
4. Step 3 advances the watermark to MAX(updated_at) from the observed delta, not to wall-clock now(). Advancing to observed values avoids skipping rows whose updated_at sits between the last seen value and the current time.
5. Because the whole task is wrapped by the orchestrator with retries, a mid-run failure simply re-runs: the truncate-stage-merge sequence converges to the same state regardless of how many times it fires.

Output.

Run	Watermark before	Rows in delta	raw.orders row count	Duplicates
1 (bootstrap)	1970-01-01	1,000,000	1,000,000	0
2	2026-09-04 08:00	420	1,000,180	0
2 (retried)	2026-09-04 08:00	420	1,000,180	0
3	2026-09-05 08:00	305	1,000,431	0

Rule of thumb. Make every load idempotent with a truncate-stage-then-MERGE-by-key pattern and advance the watermark to the observed max. If re-running a load can create a duplicate, the load is broken — fix it before you build anything on top.

Worked example — an open lakehouse table over Parquet

Detailed explanation. On the lakehouse side, the same raw landing is a set of Parquet files registered as an open table (Iceberg or Delta) so any engine can query it with ACID guarantees and time travel. Walk through creating an Iceberg table and querying a historical snapshot.

• Files. Columnar Parquet in object storage.
• Table format. Iceberg metadata gives ACID commits, schema evolution, and snapshots.
• Payoff. One copy of data, many engines; roll back or query "as of" a past snapshot.

Question. Register the raw orders as an Iceberg table and show a schema-evolution add plus a time-travel read.

Input.

Component	Value
Storage	s3://lake/raw/orders/ (Parquet)
Table format	Apache Iceberg
Feature 1	schema evolution (add a column)
Feature 2	time travel (query a past snapshot)

Code.

-- 1. Create an Iceberg table over object storage (open format, ACID)
CREATE TABLE lake.raw.orders (
    id            BIGINT,
    customer_id   BIGINT,
    total_cents   BIGINT,
    status        STRING,
    created_at    TIMESTAMP,
    updated_at    TIMESTAMP
)
USING iceberg
LOCATION 's3://lake/raw/orders/'
PARTITIONED BY (days(created_at));       -- partition pruning on date

-- 2. Schema evolution — add a column with no rewrite of existing files
ALTER TABLE lake.raw.orders ADD COLUMN currency STRING;

-- 3. Idempotent MERGE (Iceberg supports row-level MERGE just like a warehouse)
MERGE INTO lake.raw.orders AS t
USING tmp.orders_delta AS s
   ON t.id = s.id
WHEN MATCHED     THEN UPDATE SET *
WHEN NOT MATCHED THEN INSERT *;

-- 4. Time travel — read the table as it existed at a past snapshot
SELECT COUNT(*) FROM lake.raw.orders
FOR SYSTEM_VERSION AS OF 3921083612030982;   -- a snapshot id from the table history

Step-by-step explanation.

1. The USING iceberg table is metadata over plain Parquet files in S3. The data is columnar (cheap scans of a few columns) and the Iceberg layer adds ACID commits so concurrent writers do not corrupt each other.
2. Partitioning by days(created_at) lets the engine prune whole days of files when a query filters on date — the lakehouse equivalent of warehouse clustering, expressed in the open table format.
3. ADD COLUMN currency is a metadata-only change: existing Parquet files are not rewritten, and old rows simply read NULL for the new column. This is safe schema evolution, the thing hand-rolled Parquet-on-a-prefix cannot do.
4. Iceberg supports the same MERGE idempotency pattern as the warehouse, so the incremental-load discipline from the previous example carries over unchanged — the storage engine differs, the load contract does not.
5. FOR SYSTEM_VERSION AS OF reads a prior snapshot. Every commit creates a new snapshot, so you can audit, reproduce a report, or roll back a bad load by pointing at an earlier snapshot id — a governance and debugging superpower.

Output.

Capability	Plain Parquet on a prefix	Iceberg table
ACID commits	no	yes
Add a column safely	rewrite files	metadata-only
Row-level MERGE	no	yes
Time travel / rollback	no	yes (snapshots)
Engines that can query it	one at a time, carefully	many, concurrently

Rule of thumb. A lakehouse is not "Parquet in a bucket" — it is Parquet plus an open table format (Iceberg/Delta) that adds ACID, schema evolution, and snapshots. If you skip the table format, you have a data swamp, not a lakehouse.

Worked example — choosing warehouse vs lakehouse

Detailed explanation. Interviewers love a decision framework. Rather than declaring a winner, score the choice on lock-in tolerance, workload mix, team skills, and cost shape. Walk the framework across two teams.

• Team A. SQL-first analytics team, mostly BI and dbt, small ML footprint, wants zero infra.
• Team B. Heavy ML/Spark team, multi-engine, petabyte scale, cares about open formats.

Question. Score the warehouse-vs-lakehouse decision for both teams and recommend one each.

Input.

Factor	Warehouse leans	Lakehouse leans
Primary workload	SQL / BI / dbt	Spark / ML / multi-engine
Lock-in tolerance	higher (managed)	lower (open format)
Ops appetite	minimal	comfortable with more knobs
Scale / cost shape	small–large, simple pricing	very large, cheap object storage

Code.

# A tiny scoring helper (illustrative; weights are the interesting part)
def recommend(sql_heavy, wants_open_format, ml_heavy, wants_min_ops):
    score = 0
    score += 2 if sql_heavy       else -1   # SQL/BI favors warehouse
    score -= 2 if wants_open_format else 0   # open format favors lakehouse
    score -= 2 if ml_heavy        else 0     # Spark/ML favors lakehouse
    score += 2 if wants_min_ops   else -1    # low ops favors warehouse
    return "warehouse" if score >= 0 else "lakehouse"

print(recommend(sql_heavy=True,  wants_open_format=False, ml_heavy=False, wants_min_ops=True))
# → warehouse   (Team A)

print(recommend(sql_heavy=False, wants_open_format=True,  ml_heavy=True,  wants_min_ops=False))
# → lakehouse   (Team B)

Step-by-step explanation.

1. Team A is SQL-first with a small ML footprint and no appetite for infrastructure. Every factor pushes toward a managed warehouse: SQL/BI is native, pricing is simple, and there are almost no knobs to turn.
2. Team B runs heavy Spark and ML across multiple engines at petabyte scale and explicitly values open formats to avoid lock-in. Every factor pushes toward a lakehouse: one open copy of data queryable by Spark, SQL, and ML frameworks alike.
3. The scoring is deliberately crude — the point is that the decision is multi-factor, not "which is newer." Lock-in tolerance and workload mix dominate; scale and cost shape break ties.
4. In 2026 the honest answer is often "both surfaces, one storage": a warehouse that can also query external Iceberg tables, or a lakehouse with a SQL warehouse on top. The convergence means you rarely have to commit forever.
5. The interview signal is that you refuse to declare a universal winner and instead tie the recommendation to the team's workload, skills, and lock-in posture.

Output.

Team	Recommendation	Deciding factors
A (SQL/BI, min ops)	warehouse	native SQL, simple pricing, no infra
B (Spark/ML, open)	lakehouse	open format, multi-engine, cheap scale
Mixed / uncertain	converged (warehouse + external Iceberg)	keep optionality

Rule of thumb. Choose the warehouse when SQL and low ops dominate; choose the lakehouse when open formats, multi-engine ML, and cheap petabyte storage dominate. When in doubt, pick the surface that queries an open table format so you keep the option to change your mind.

Data engineering interview question on ingestion and storage

A senior interviewer might ask: "You need to ingest a 2-billion-row events table plus a dozen SaaS sources into a cloud warehouse with daily freshness. Walk me through the ingestion pattern per source, the raw landing contract, how you keep loads idempotent, and how you'd decide whether the events firehose belongs in the warehouse or a lakehouse."

Solution Using CDC + managed connectors landing into an idempotent raw zone

-- Raw zone contract: one schema, one table per source stream, append/upsert only
CREATE SCHEMA IF NOT EXISTS raw;
CREATE TABLE IF NOT EXISTS raw._load_state (
    table_name        TEXT PRIMARY KEY,
    loaded_watermark  TIMESTAMPTZ NOT NULL,
    last_loaded_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- High-volume events: land via CDC/streaming into a partitioned raw table
CREATE TABLE raw.events (
    event_id     BIGINT,
    user_id      BIGINT,
    event_type   TEXT,
    payload      VARIANT,             -- semi-structured; schema-on-read
    event_ts     TIMESTAMPTZ,
    _ingested_at TIMESTAMPTZ DEFAULT now()
)
CLUSTER BY (event_ts::date);          -- prune by day at query time

-- SaaS sources: managed connectors land one raw table each, MERGE-upserted by PK
--   raw.stripe_invoices, raw.hubspot_contacts, ... (idempotent MERGE per the load task)

# Per-source dispatch: pick the ingestion pattern by source shape
def ingestion_pattern(source: dict) -> str:
    if source["type"] == "oltp_db":
        return "log-based CDC"          # near-zero source load; captures deletes
    if source["type"] == "saas_api":
        return "managed connector (incremental by updated_at)"
    if source["type"] == "event_stream":
        return "streaming append into partitioned raw table"
    return "batch API pull (incremental cursor)"

for s in [{"type": "oltp_db"}, {"type": "saas_api"}, {"type": "event_stream"}]:
    print(s["type"], "->", ingestion_pattern(s))
# oltp_db      -> log-based CDC
# saas_api     -> managed connector (incremental by updated_at)
# event_stream -> streaming append into partitioned raw table

Step-by-step trace.

Source	Pattern	Idempotency mechanism	Storage choice
Postgres OLTP	log-based CDC	MERGE by PK on apply	warehouse raw zone
Stripe / HubSpot	managed connector	truncate-stage + MERGE by PK	warehouse raw zone
2B-row events	streaming append	dedupe by event_id on read	lakehouse or clustered raw
Partner REST API	batch pull	incremental cursor + MERGE	warehouse raw zone

After deployment, the twelve SaaS sources land as tidy MERGE-upserted raw tables, the OLTP database is mirrored by CDC with no analytics load on production, and the 2-billion-row event firehose lands append-only in a date-clustered (or open-format lakehouse) table where dedupe happens at read time. Every load is re-runnable; a retried task converges to the same raw state.

Output:

Metric	Value
SaaS sources	12, one raw table each (MERGE-upserted)
OLTP freshness	CDC, minutes; batch apply daily
Events volume	2B rows, append-only, day-partitioned
Duplicate rows after re-run	0 (idempotent MERGE / read-time dedupe)
Analytics load on production DB	~zero (CDC only)

Why this works — concept by concept:

• Pattern per source shape — CDC for your own OLTP, managed connectors for SaaS, streaming append for firehoses, batch pulls for the long tail. Matching the pattern to the source is what keeps ingestion cheap and reliable.
• Idempotent raw contract — every low-to-medium volume source is MERGE-upserted by primary key, so retries and backfills never duplicate rows. The raw zone stays a faithful, replayable copy of the source.
• Schema-on-read for events — the high-volume firehose lands as semi-structured VARIANT/Parquet and is structured at query time, so a changing event schema never blocks ingestion.
• Storage separated from compute — keeping 2B raw rows costs storage dollars only; you burst compute to transform or query and then release it, which is exactly why the warehouse/lakehouse model can afford to keep everything raw.
• Cost — one indexed/clustered scan per incremental load (O(delta), not O(table)) for the small sources, plus cheap append for the firehose. Compared to nightly full reloads, this is daily freshness at a fraction of the compute, and production never competes with analytics for CPU.

Data
Topic — data-transformation
Data-transformation and ingestion problems

Practice →

SQL Topic — database Database and warehouse modeling problems

Practice →

---

3. The transformation layer — dbt and ELT

dbt turns raw tables into tested, documented models — a DAG of ref()-linked SQL from staging to marts

The mental model in one line: the transformation layer is where raw landings become trustworthy business tables, and in the modern stack that means dbt — a framework that lets analytics engineers write each model as a SELECT statement, wires those models into a dependency DAG through the ref() function, materializes them as views or tables (or incrementally), and ships built-in testing, documentation, and lineage so the marts everyone queries are reproducible from raw with a single command. dbt did not invent SQL transformation; it made it software — version-controlled, tested, reviewed, and re-runnable — which is why it is the near-universal T in modern ELT.

The dbt model DAG — staging, intermediate, marts.

• Staging models (stg_). One model per source table, a light 1:1 clean-up: rename columns to a house style, cast types, trim whitespace, and de-duplicate. Staging is the boundary between "the source's shape" and "our shape."
• Intermediate models (int_). Optional building blocks that join or reshape staging models into reusable pieces — for example unnesting order line items — that several marts share.
• Marts (fct_ / dim_). Business-ready facts and dimensions, usually a star schema: fct_orders (one row per order, additive measures) joined to dim_customers (one row per customer, descriptive attributes).
• ref() builds the DAG. You never hard-code a table name; you write {{ ref('stg_orders') }}. dbt resolves it to the right schema and, crucially, infers the dependency graph from those ref() calls, so it always builds models in the correct order.

Materializations — how a model becomes a physical object.

• view. The model is a database view; cheap to build, recomputed on every read. Good for lightweight staging.
• table. The model is rebuilt as a full table each run; simple and fast to query, but rebuilds everything every time.
• incremental. Only new/changed rows are processed each run and merged into the existing table — essential for large fact tables where a full rebuild is too expensive.
• ephemeral. Not materialized at all; inlined as a CTE into downstream models. Good for small helper logic you do not want cluttering the warehouse.

Tests, docs, and lineage — why dbt is trustworthy.

• Generic tests. Declarative column tests in YAML: unique, not_null, accepted_values, relationships (foreign-key integrity). One line each; they run with dbt test.
• Singular tests. A hand-written SQL query that should return zero rows — for arbitrary business rules ("no order has a negative total").
• Docs and lineage. dbt docs generates a browsable site with every model's description, columns, and a full lineage graph derived from ref(), so anyone can trace a mart column back to its source.
• Contracts. Model contracts pin a model's column names and types so a breaking change is caught at build time, not by a downstream dashboard turning blank.

What interviewers listen for.

• Do you use ref() and explain that it builds the DAG and ordering for free? — required answer.
• Do you pick the right materialization (incremental for big facts, view for light staging)? — senior signal.
• Do you say transformations are idempotent — re-running produces the same marts? — required answer.
• Do you mention tests and lineage as first-class, not optional extras? — senior signal.

Worked example — a staging model plus a mart with ref()

Detailed explanation. The bread-and-butter of dbt: a stg_orders model that cleans the raw table, and a fct_orders mart that builds on it via ref(). Walk through both and the DAG they imply.

• Staging. Rename, cast, dedupe raw.orders.
• Mart. Aggregate to one row per order with a customer join.
• DAG. ref('stg_orders') makes dbt build staging first, automatically.

Question. Write stg_orders and fct_orders, and describe the build order dbt derives.

Input.

Model	Layer	Materialization	Depends on
stg_orders	staging	view	raw.orders (source)
stg_customers	staging	view	raw.customers (source)
fct_orders	mart	table	stg_orders, stg_customers

Code.

-- models/staging/stg_orders.sql   (materialized as a view)
with source as (
    select * from {{ source('raw', 'orders') }}
),
cleaned as (
    select
        id                       as order_id,
        customer_id,
        total_cents / 100.0      as order_total_usd,
        lower(status)            as status,
        created_at::timestamp    as created_at,
        updated_at::timestamp    as updated_at,
        row_number() over (
            partition by id order by updated_at desc
        ) as _rn
    from source
)
select order_id, customer_id, order_total_usd, status, created_at, updated_at
from cleaned
where _rn = 1        -- dedupe: keep the latest version of each order

-- models/marts/fct_orders.sql   (materialized as a table)
{{ config(materialized='table') }}

select
    o.order_id,
    o.customer_id,
    c.customer_name,
    c.customer_segment,
    o.order_total_usd,
    o.status,
    o.created_at
from {{ ref('stg_orders') }}    as o          -- ref() creates the dependency edge
left join {{ ref('stg_customers') }} as c
       on c.customer_id = o.customer_id

Step-by-step explanation.

1. stg_orders reads from {{ source('raw', 'orders') }} — the declared source — and does exactly three things: renames (id → order_id), casts and rescales (total_cents → order_total_usd), and dedupes with a row_number() window keeping the latest updated_at per order_id.
2. Deduping in staging means every downstream model can trust that stg_orders has one row per order. Pushing this invariant to the boundary is what keeps mart logic simple.
3. fct_orders never names a physical table; it uses {{ ref('stg_orders') }} and {{ ref('stg_customers') }}. dbt reads those ref() calls and infers that both staging models must build before fct_orders.
4. The {{ config(materialized='table') }} line tells dbt to persist fct_orders as a full table (fast to query for BI), while the staging models stay cheap views.
5. Running dbt run builds the DAG in topological order — sources, then the two staging views, then the mart — with no manual ordering. Re-running rebuilds the same objects idempotently.

Output.

Build step	Object	Type
1	stg_orders	view
1	stg_customers	view
2	fct_orders	table
Result	one row per order + customer attributes	queryable mart

Rule of thumb. Do the cleaning once, in staging, and build marts on ref()-linked staging models. Never ref() a raw source directly from a mart — the staging layer is where source shape becomes house shape.

Worked example — an incremental fact model

Detailed explanation. A fct_orders that reprocesses a billion rows every run is too expensive. The incremental materialization processes only new/changed rows and merges them, using is_incremental() to branch the SQL. Walk through it.

• Full on first run. With no existing table, the model builds everything.
• Incremental after. Subsequent runs filter to rows newer than what is already loaded.
• Merge key. unique_key deduplicates on merge so late updates replace old versions.

Question. Convert fct_orders to an incremental model that only processes recently changed orders.

Input.

Setting	Value
materialized	incremental
unique_key	order_id
Incremental filter	updated_at > max(updated_at) in this model
First run behavior	full build

Code.

-- models/marts/fct_orders.sql   (incremental)
{{ config(
    materialized='incremental',
    unique_key='order_id',
    incremental_strategy='merge'
) }}

select
    o.order_id,
    o.customer_id,
    o.order_total_usd,
    o.status,
    o.created_at,
    o.updated_at
from {{ ref('stg_orders') }} as o

{% if is_incremental() %}
  -- only rows changed since the newest updated_at already in this table
  where o.updated_at > (select coalesce(max(updated_at), '1900-01-01') from {{ this }})
{% endif %}

Step-by-step explanation.

1. The config block sets materialized='incremental' with unique_key='order_id' and the merge strategy, so dbt upserts by order_id rather than appending blindly.
2. On the first run, is_incremental() is false (the table does not exist yet), so the {% if %} block is skipped and the model builds the full history.
3. On every subsequent run, is_incremental() is true and the where filter kicks in: it reads max(updated_at) from {{ this }} (the existing table) and processes only orders changed after that point.
4. The merge strategy uses unique_key to replace existing rows with the same order_id, so a late update to an old order correctly overwrites the stale version instead of creating a duplicate.
5. The result is idempotent and cheap: re-running processes only the recent slice, and even a retried run converges because the merge is keyed on order_id.

Output.

Run	is_incremental()	Rows scanned from staging	Rows merged
1 (bootstrap)	false	1,000,000	1,000,000
2	true	420	420
2 (retried)	true	420	420
3	true	305	305

Rule of thumb. Make large fact tables incremental with a unique_key and an is_incremental() filter on an update cursor. Use merge so late-arriving updates replace old rows — an append-only incremental model silently duplicates on updates.

Worked example — tests that make marts trustworthy

Detailed explanation. A model is only trustworthy if it is tested. dbt tests come in two flavors: generic YAML tests on columns, and singular SQL tests for arbitrary rules. Walk through both for fct_orders.

• Generic. unique + not_null on the key, relationships to the customer dimension, accepted_values on status.
• Singular. A query that returns offending rows (should be empty) — "no order has a negative total."
• Gate. dbt test fails the build if any test finds bad rows.

Question. Write the schema tests for fct_orders and a singular test for the non-negative-total rule.

Input.

Test	Type	Column / rule
unique	generic	order_id
not_null	generic	order_id
relationships	generic	customer_id → dim_customers
accepted_values	generic	status in a known set
non-negative total	singular	order_total_usd >= 0

Code.

# models/marts/_marts.yml — generic tests live next to the models
version: 2
models:
  - name: fct_orders
    description: "One row per order with customer attributes and USD total."
    columns:
      - name: order_id
        description: "Primary key of the order."
        tests: [unique, not_null]
      - name: customer_id
        tests:
          - relationships:
              to: ref('dim_customers')
              field: customer_id
      - name: status
        tests:
          - accepted_values:
              values: ['pending', 'shipped', 'delivered', 'cancelled', 'refunded']

-- tests/assert_fct_orders_total_non_negative.sql  (a singular test)
-- Convention: a test PASSES when it returns ZERO rows.
select order_id, order_total_usd
from {{ ref('fct_orders') }}
where order_total_usd < 0

Step-by-step explanation.

1. The YAML declares four generic tests. unique and not_null on order_id guarantee a clean primary key; relationships enforces that every customer_id in the fact exists in dim_customers (referential integrity the warehouse itself does not enforce).
2. accepted_values on status catches an upstream source that starts emitting a new, unhandled status — a common silent-corruption vector that would otherwise reach dashboards unnoticed.
3. The singular test is plain SQL that selects the offending rows. dbt's convention is that a test passes only when the query returns zero rows, so any order with a negative total fails the build.
4. dbt test runs all of these after dbt run; the orchestrator treats a test failure as a pipeline failure, so bad data stops at the transformation layer instead of flowing to BI.
5. Because the tests live in Git next to the models, they are reviewed like code and evolve with the schema — the model and its guarantees change together.

Output.

Test	What it catches
unique / not_null on order_id	duplicate or missing keys
relationships on customer_id	orphan orders with no customer
accepted_values on status	new/unexpected status values
singular non-negative total	corrupt or refunded-as-negative amounts

Rule of thumb. Ship every model with at least a unique + not_null on its key and a relationships test on every foreign key. Untested marts are just opinions; tested marts are contracts.

Data engineering interview question on the transformation layer

A senior interviewer might ask: "You've loaded raw orders, customers, and line items into the warehouse. Design the dbt project that turns them into a trustworthy fct_orders star schema — the layering, the materializations, how you keep the big fact table cheap to rebuild, and the tests that stop bad data from reaching the dashboards."

Solution Using a layered dbt project with an incremental fact and tests

-- models/staging/stg_orders.sql   (view; 1:1 clean)
select
    id                    as order_id,
    customer_id,
    total_cents / 100.0   as order_total_usd,
    lower(status)         as status,
    created_at::timestamp as created_at,
    updated_at::timestamp as updated_at
from {{ source('raw', 'orders') }}
qualify row_number() over (partition by id order by updated_at desc) = 1

-- models/marts/fct_orders.sql   (incremental fact, merged by key)
{{ config(materialized='incremental', unique_key='order_id', incremental_strategy='merge') }}

select
    o.order_id,
    o.customer_id,
    c.customer_segment,
    o.order_total_usd,
    o.status,
    o.created_at,
    o.updated_at
from {{ ref('stg_orders') }}     as o
left join {{ ref('dim_customers') }} as c using (customer_id)
{% if is_incremental() %}
  where o.updated_at > (select coalesce(max(updated_at), '1900-01-01') from {{ this }})
{% endif %}

# models/marts/_marts.yml
version: 2
models:
  - name: fct_orders
    columns:
      - name: order_id
        tests: [unique, not_null]
      - name: customer_id
        tests:
          - relationships: { to: ref('dim_customers'), field: customer_id }
      - name: status
        tests:
          - accepted_values: { values: ['pending','shipped','delivered','cancelled','refunded'] }

Step-by-step trace.

Layer	Model	Materialization	Purpose
staging	stg_orders, stg_customers	view	1:1 clean, dedupe, retype
intermediate	int_order_items	ephemeral	unnest line items, reused by marts
dimension	dim_customers	table	one row per customer
fact	fct_orders	incremental (merge)	one row per order, cheap re-runs
tests	schema + singular	—	gate the build on data quality

After dbt run, dbt builds sources → staging views → dim_customers → fct_orders, in that order, entirely from the ref() graph. The fact table processes only recently changed orders on each run, and dbt test fails the pipeline if a key is duplicated, a customer is missing, or a status is unexpected — so the dashboards downstream only ever read data that passed every contract.

Output:

Metric	Value
Build order	derived automatically from ref()
fct_orders rows scanned per run	O(recent delta), not O(history)
Duplicate keys after re-run	0 (merge on order_id)
Bad data reaching BI	blocked by dbt test
Lineage from mart to source	auto-generated (dbt docs)

Why this works — concept by concept:

• ref()-derived DAG — because every model references its parents through ref(), dbt computes build order and lineage automatically. Adding a model never requires hand-editing a schedule.
• Layered staging → marts — cleaning happens once in staging, so marts stay simple and every downstream model inherits the same trusted, deduped inputs.
• Incremental merge fact — materialized='incremental' with unique_key processes only the recent slice and upserts by key, so a billion-row fact stays cheap to refresh and idempotent under retries.
• Tests as gates — generic and singular tests turn data-quality expectations into build-time contracts; a failure stops bad data at the transformation layer instead of surfacing as a wrong dashboard.
• Cost — staging views cost nothing to store and recompute on read; the incremental fact costs O(delta) compute per run instead of O(history). Compared to full table rebuilds, this is the difference between minutes and hours on large facts, and the tests add only a handful of cheap scans.

SQL
Topic — database
SQL modeling and star-schema problems

Practice →

ETL Topic — etl ELT transformation and incremental-model problems

Practice →

---

4. Orchestration and observability

The orchestrator runs every layer in order with retries and backfills; observability tells you when the data drifted

The mental model in one line: orchestration is the control plane of the modern data stack — a scheduler (Airflow, Dagster, Prefect, or dbt Cloud) that runs ingestion, transformation, tests, and activation in dependency order, retries failures, and backfills missed windows — while observability is the sensor layer that continuously checks the data itself for freshness, volume, schema, and distribution drift, so a broken pipeline or a silently-corrupted table pages a human instead of misleading a dashboard. Orchestration answers "did every step run, in the right order, on time?"; observability answers "and is the data it produced actually correct?" You need both, because a pipeline can succeed technically while producing garbage.

What orchestration actually does.

• Scheduling. Runs the pipeline on a cadence (cron-like) or on a trigger (a file lands, an event fires). Daily and hourly are the common warehouse cadences.
• Dependency management. Encodes "transform runs after ingestion, activation runs after tests pass" as a DAG, so nothing runs on stale or missing inputs.
• Retries and alerting. Transient failures (a connector blip, a warehouse timeout) retry with backoff; persistent failures alert on-call.
• Backfills. Re-run a range of past windows after a bug fix or a late source — only safe if every task is idempotent, which is why the whole stack insists on idempotency.

Orchestrators in 2026.

• Airflow. The incumbent; task-based DAGs written in Python. Huge ecosystem, battle-tested, verbose. Still the default in many shops.
• Dagster. Asset-based: you declare the data assets (tables) you want and their dependencies, and Dagster orchestrates to produce them. Lineage and observability are first-class, which fits the modern stack well.
• Prefect. Pythonic, dynamic flows with minimal boilerplate; strong for teams that want orchestration to feel like normal code.
• dbt Cloud / managed schedulers. For dbt-centric stacks, a managed scheduler runs dbt build on a cadence with logging and alerts, sometimes removing the need for a separate orchestrator entirely.

The five observability pillars.

• Freshness. Is the data recent enough? (When did the table last update versus its SLA?)
• Volume. Did roughly the expected number of rows arrive? (A 90% row-count drop is an incident even if the job "succeeded.")
• Schema. Did columns get added, removed, or retyped upstream without warning?
• Distribution. Did the values drift — a null rate spike, a sudden new category, an out-of-range metric?
• Lineage. When something breaks, what upstream source caused it and what downstream marts and dashboards are affected?

What interviewers listen for.

• Do you insist every task is idempotent so backfills are safe? — required answer.
• Do you separate "the job ran" from "the data is correct" (orchestration vs observability)? — senior signal.
• Do you name concrete freshness/volume/schema checks rather than "we monitor it"? — senior signal.
• Do you wire tests into the DAG so failures block activation? — required answer.

Worked example — an asset DAG wiring ingest → dbt → test

Detailed explanation. The canonical orchestration: a DAG that runs the connector, then dbt build (run + test together), and only marks the marts "ready" if tests pass. Walk through an asset-style definition.

• Task 1. Trigger the ingestion for the day's delta.
• Task 2. dbt build — runs models and tests.
• Gate. Activation depends on dbt build succeeding.

Question. Define the DAG so activation never runs on data that failed tests.

Input.

Task	Depends on	Retries
ingest_orders	—	3 (backoff)
dbt_build	ingest_orders	1
reverse_etl_sync	dbt_build (tests passed)	3

Code.

# Dagster-style asset graph: assets declare dependencies; the framework orders them
from dagster import asset, define_asset_job, RetryPolicy

@asset(retry_policy=RetryPolicy(max_retries=3))
def raw_orders() -> None:
    """Run the connector / CDC apply for the day's delta into raw.orders."""
    run_connector("orders")            # idempotent MERGE-based load

@asset(deps=[raw_orders])
def dbt_marts() -> None:
    """dbt build = run models AND run tests; raises if any test fails."""
    run_shell("dbt build --select staging+ marts+")   # non-zero exit on test failure

@asset(deps=[dbt_marts], retry_policy=RetryPolicy(max_retries=3))
def crm_sync() -> None:
    """Reverse ETL — only reached because dbt_marts (incl. tests) succeeded."""
    run_reverse_etl("marts.churned_customers", destination="crm")

daily_stack = define_asset_job("daily_stack", selection="*")

Step-by-step explanation.

1. Each @asset declares the data it produces and its upstream deps. Dagster derives the run order from those dependencies — the same idea as dbt's ref(), applied to the whole pipeline, not just transformation.
2. raw_orders carries a retry policy: a transient connector or warehouse hiccup retries up to three times with backoff before the run is considered failed. Because the load is an idempotent MERGE, retries are safe.
3. dbt_marts runs dbt build, which executes models and tests in one shot and exits non-zero if any test fails. A test failure therefore fails the asset, and Dagster will not consider it materialized.
4. crm_sync (reverse ETL) lists dbt_marts as a dependency, so it is only reachable if the marts built and every test passed. Bad data can never flow to the CRM, because the gate is the dependency edge itself.
5. Backfilling a past day re-runs the same assets for that partition; since every task is idempotent, the backfill converges to the correct state with no duplicates or double-syncs.

Output.

Scenario	dbt_marts result	crm_sync runs?
All tests pass	success	yes
A not_null test fails	failure	no (gated)
Connector blips once	retried, then success	yes
Backfill of 2026-09-01	idempotent re-run	yes, no double sync

Rule of thumb. Wire tests inside the pipeline (dbt build, not dbt run) and make activation depend on that step. If reverse ETL can run when tests failed, you will sync corrupt data into the tools that run the business.

Worked example — freshness and volume anomaly checks

Detailed explanation. Observability starts with two cheap, high-value checks: is the table fresh, and did roughly the right number of rows arrive? Both are SQL you can run right after the build. Walk through them for fct_orders.

• Freshness. Max timestamp should be within the SLA window.
• Volume. Today's row count should be within a band of the trailing average.
• Action. Either check failing raises an alert (and can gate downstream).

Question. Write a freshness check (SLA: updated within 26 hours) and a volume check (within ±40% of the 7-day average) for fct_orders.

Input.

Check	Rule	Threshold
freshness	now() - max(created_at)	< 26 hours
volume	today's count vs 7-day avg	within ±40%
on failure	raise alert / gate activation	page on-call

Code.

-- 1. Freshness: fail if the newest row is older than the SLA window
select
    max(created_at)                               as latest_row,
    now() - max(created_at)                       as staleness,
    (now() - max(created_at)) > interval '26 hours' as is_stale
from marts.fct_orders;

-- 2. Volume: compare today's row count to the trailing 7-day daily average
with daily as (
    select created_at::date as d, count(*) as n
    from marts.fct_orders
    where created_at >= current_date - interval '8 days'
    group by 1
),
stats as (
    select
        (select n from daily where d = current_date)                          as today_n,
        avg(n) filter (where d between current_date - 7 and current_date - 1)  as avg_prev_7
    from daily
)
select
    today_n,
    round(avg_prev_7)                                            as expected,
    today_n < 0.6 * avg_prev_7 or today_n > 1.4 * avg_prev_7     as is_anomaly
from stats;

Step-by-step explanation.

1. The freshness query takes max(created_at) and compares the gap to now(). If the newest order is more than 26 hours old, is_stale is true — the pipeline may have succeeded while a source silently stopped delivering.
2. Freshness catches the failure mode where "the job ran" but "no new data arrived," which orchestration alone cannot see because the task exited zero.
3. The volume query builds a per-day row count for the last eight days, isolates today's count, and computes the average of the previous seven days.
4. is_anomaly fires if today's count falls below 60% or above 140% of that trailing average — catching both a partial-load drop and a duplicate-driven spike, either of which is an incident even though no task errored.
5. Both checks are cheap scans you run as a final DAG step; a true result raises an alert and can gate activation, so a stale or half-loaded mart never reaches a dashboard or a CRM sync.

Output.

Day	today_n	7-day avg	is_anomaly	is_stale
Normal	12,430	12,100	false	false
Partial load	3,900	12,100	true	false
Source stalled	0	12,100	true	true
Dedup bug (spike)	24,800	12,100	true	false

Rule of thumb. Freshness and volume are the two checks with the best signal-per-line-of-SQL. Add them to every important mart; they catch the "job succeeded, data is wrong" failures that pure orchestration is blind to.

Worked example — an idempotent partitioned backfill

Detailed explanation. When a bug is fixed, you must re-run past windows. This is only safe if each partition's task overwrites just that partition idempotently. Walk through a date-partitioned backfill.

• Partition key. event_date.
• Idempotent write. Delete-then-insert (or MERGE) for the target partition only.
• Backfill. Loop the fixed task over a date range.

Question. Write a partition-scoped task that a backfill can re-run for any date without duplicating rows.

Input.

Parameter	Value
Target	marts.daily_metrics
Partition	metric_date
Idempotency	delete partition + insert
Backfill range	2026-08-01 .. 2026-08-07

Code.

# One idempotent task, parameterized by partition date; backfill = loop the range
from datetime import date, timedelta

def build_daily_metrics(run_date: date, wh) -> None:
    """Rebuild exactly ONE day's partition; safe to re-run any number of times."""
    with wh.begin() as tx:                      # single transaction = atomic swap
        tx.execute(
            "DELETE FROM marts.daily_metrics WHERE metric_date = %s", (run_date,))
        tx.execute("""
            INSERT INTO marts.daily_metrics (metric_date, orders, revenue_usd)
            SELECT created_at::date, count(*), sum(order_total_usd)
            FROM   marts.fct_orders
            WHERE  created_at::date = %s
            GROUP  BY 1
        """, (run_date,))

def backfill(start: date, end: date, wh) -> None:
    d = start
    while d <= end:                              # re-run each partition idempotently
        build_daily_metrics(d, wh)
        d += timedelta(days=1)

# backfill(date(2026, 8, 1), date(2026, 8, 7), wh)  # safe to run repeatedly

Step-by-step explanation.

1. build_daily_metrics is scoped to a single run_date: it deletes that day's partition and re-inserts it from the fact table, all inside one transaction so readers never see a half-empty partition.
2. Because the task touches only metric_date = run_date, re-running it recomputes just that day and leaves every other partition untouched — the definition of a partition-scoped idempotent write.
3. Running the same date twice yields the same partition contents: the DELETE clears the prior attempt, the INSERT rebuilds it. There is no path to duplicate rows.
4. backfill simply loops the fixed task over a date range. Because each day is independent and idempotent, the backfill can be interrupted and restarted with no cleanup.
5. This is why the whole stack insists on idempotency: orchestration's backfill feature is only trustworthy when every task can be re-run safely, so idempotency is a precondition for operability, not a nicety.

Output.

Action	Partitions affected	Duplicates
Normal daily run (2026-08-08)	just 2026-08-08	0
Backfill 08-01..08-07	those 7 days only	0
Re-run backfill (crashed midway)	recomputed cleanly	0
Other partitions	untouched	n/a

Rule of thumb. Scope every write to its partition and make it delete-then-insert (or MERGE) inside one transaction. Then a backfill is just a loop, and a crashed backfill is just "run it again."

Data engineering interview question on orchestration and observability

A senior interviewer might ask: "Design the orchestration and observability for a daily warehouse pipeline: ingestion, dbt transformation with tests, and a reverse-ETL sync. Cover the DAG dependencies, retry and backfill strategy, the data-quality checks that gate activation, and how on-call learns that a table went stale without any task failing."

Solution Using an asset-based DAG with test gating and freshness/volume checks

# The DAG: ingest → dbt build (models+tests) → freshness/volume checks → reverse ETL
from dagster import asset, RetryPolicy, AssetCheckResult, asset_check

@asset(retry_policy=RetryPolicy(max_retries=3, delay=60))
def raw_layer() -> None:
    run_all_connectors()               # idempotent incremental MERGE loads

@asset(deps=[raw_layer])
def dbt_marts() -> None:
    run_shell("dbt build")             # models + tests; non-zero exit fails the asset

@asset_check(asset=dbt_marts)
def freshness_and_volume() -> AssetCheckResult:
    stale   = query_scalar("select (now() - max(created_at)) > interval '26 hours' "
                           "from marts.fct_orders")
    anomaly = query_scalar(VOLUME_CHECK_SQL)   # ±40% band vs trailing 7-day avg
    return AssetCheckResult(passed=not (stale or anomaly),
                            metadata={"stale": stale, "volume_anomaly": anomaly})

@asset(deps=[dbt_marts], retry_policy=RetryPolicy(max_retries=3))
def reverse_etl() -> None:
    # reached only when dbt_marts built, tests passed, and the check is green
    run_reverse_etl("marts.churned_customers", destination="crm")

-- The freshness + volume checks as one gate query (raises → alert → block activation)
with f as (
  select (now() - max(created_at)) > interval '26 hours' as is_stale from marts.fct_orders
),
v as (
  select (count(*) < 0.6 * :avg7 or count(*) > 1.4 * :avg7) as is_anomaly
  from marts.fct_orders where created_at::date = current_date
)
select f.is_stale or v.is_anomaly as should_alert from f, v;

Step-by-step trace.

Stage	Mechanism	Failure behavior
Ingestion	idempotent MERGE loads, 3 retries	retry, then alert
Transformation	dbt build (models + tests)	test fail → asset fails → stop
Data-quality gate	freshness (26h) + volume (±40%) check	check fails → alert + block
Activation	reverse ETL, depends on green gate	never runs on bad data
Backfill	partition-scoped idempotent tasks	re-run any window safely

After deployment, the DAG runs top to bottom once a day: loads retry through transient blips, dbt build blocks the run if any model test fails, and an asset check verifies freshness and volume before activation is allowed. If a source silently stops delivering, no task errors — but the freshness check flips, pages on-call, and holds the reverse-ETL sync so the CRM is never fed a stale audience.

Output:

Metric	Value
Run order	derived from asset deps (no manual wiring)
Transient failure handling	3 retries with backoff
Bad data reaching activation	blocked by tests + checks
"Job ran but data is stale" detection	freshness check (26h SLA)
Backfill safety	idempotent partitioned tasks

Why this works — concept by concept:

• Asset-based dependencies — declaring assets and their deps lets the orchestrator derive run order and lineage automatically, the same principle dbt applies inside transformation, extended across the whole pipeline.
• dbt build as a gate — running models and tests together means a data-quality failure is a pipeline failure; activation is downstream of the gate, so corrupt data cannot pass.
• Freshness and volume checks — these catch the "task succeeded, data is wrong" class (a stalled source, a partial load) that orchestration alone is blind to, because they inspect the data, not the job status.
• Idempotent tasks enable backfills — every load and model is re-runnable, so backfilling a fixed window is a safe loop rather than a risky one-off with manual cleanup.
• Cost — retries and checks add a few cheap scans per run; the gating prevents the far larger cost of a corrupt CRM sync or a misleading executive dashboard. O(cheap check) upfront buys O(avoided incident) downstream.

Data
Topic — data-transformation
Orchestration and pipeline-reliability problems

Practice →

ETL Topic — etl Backfill and idempotency ETL problems

Practice →

---

5. Activation — BI, reverse ETL, and the 2026 shifts

Modeled data flows out two ways — to dashboards for people (BI) and back to SaaS for machines (reverse ETL)

The mental model in one line: activation is the last mile of the modern data stack, where the trusted marts finally get used — through a BI tool and semantic layer that let people explore metrics, and through reverse ETL that syncs those same modeled tables back into the operational SaaS systems (CRM, ad platforms, support tools) so software can act on them — all sitting on a governance foundation of catalog, access control, and lineage, and all being reshaped in 2026 by AI copilots, zero-ETL, and lakehouse convergence. The warehouse is not the destination; it is the staging ground for two very different consumers — humans who read charts and machines that run playbooks.

The activation layer — BI and the semantic layer.

• BI tools. Dashboards and self-serve exploration (Looker, Tableau, Power BI, Metabase) sitting on top of the marts. This is where analysts and executives read the numbers.
• The semantic / metrics layer. A single definition of each metric ("active customer," "MRR") that every tool shares, so a number means the same thing in every dashboard. It sits between the marts and the BI tools.
• Embedded analytics. The same marts powering customer-facing charts inside your own product, not just internal dashboards.

Reverse ETL — operational analytics.

• What it is. Syncing modeled tables from the warehouse back into operational SaaS tools — the opposite direction of ingestion. A churned_customers mart becomes a CRM audience; a product_qualified_leads mart becomes an ad-platform custom audience.
• Why it exists. The warehouse is where the complete, joined picture of a customer lives; the SaaS tools are where the business acts. Reverse ETL closes the loop so the sales rep sees the model's score inside their CRM, not in a dashboard they never open.
• How it works. A sync engine (Census, Hightouch, or a hand-rolled job) reads a mart, detects what changed since last sync, and upserts only the diff into the destination via its API — idempotently, respecting rate limits.

Data governance across the stack.

• Catalog. A searchable inventory of every table and column with owners and descriptions, so people can find and trust data.
• Access control. Role-based grants plus column masking (hide PII) and row-access policies (a regional analyst sees only their region).
• Lineage. The end-to-end graph from source column to CRM field, so you can answer "what feeds this?" and "what breaks if I change this?"
• Contracts. Agreements on schema and semantics enforced at ingestion and transformation time, so a producer cannot silently break a consumer.

The 2026 shifts.

• AI in the stack. Text-to-SQL and analytics copilots let people ask questions in English; AI assists documentation, anomaly detection, and even pipeline authoring. The semantic layer becomes the guardrail that keeps AI answers correct.
• Zero-ETL. Cloud vendors replicate an operational database straight into the warehouse with no connector to manage, collapsing part of the ingestion layer.
• Lakehouse convergence. Warehouse and lakehouse increasingly query the same open table format, so the storage choice stops being a fork in the road.
• Cost governance (FinOps). With compute concentrated in the warehouse, controlling and attributing spend becomes a named discipline with its own tooling.

What interviewers listen for.

• Do you distinguish BI (for people) from reverse ETL (for machines) as the two activation paths? — required answer.
• Do you make reverse ETL idempotent and change-detected (sync only the diff) rather than "push the whole table"? — senior signal.
• Do you place governance across every layer with concrete controls (masking, row access, contracts)? — senior signal.
• Can you name the 2026 shifts (AI, zero-ETL, convergence) and what each changes? — senior signal.

Worked example — a semantic-layer metric and a BI query

Detailed explanation. A metric defined once in the semantic layer is consumed identically by every BI tool. Walk through defining "active customer" and the query the BI layer generates from it.

• Definition. One YAML/SQL definition of the metric and its filters.
• Consumption. BI tools reference the metric name, not raw SQL.
• Payoff. Every dashboard agrees on the number.

Question. Define an active_customers metric in the semantic layer and show the query a BI tool compiles from it.

Input.

Element	Value
Metric	active_customers
Grain	per month
Definition	customers with ≥1 order in the last 30 days
Source mart	fct_orders

Code.

# semantic_layer/metrics.yml — one definition, shared by every BI tool
metrics:
  - name: active_customers
    label: "Active Customers"
    description: "Distinct customers with at least one order in the trailing 30 days."
    type: count_distinct
    expression: customer_id
    source: marts.fct_orders
    filters:
      - "created_at >= dateadd('day', -30, current_date)"
    dimensions: [order_month, customer_segment]

-- The query the BI tool compiles when someone charts active_customers by month
select
    date_trunc('month', created_at)              as order_month,
    count(distinct customer_id)                  as active_customers
from marts.fct_orders
where created_at >= dateadd('day', -30, current_date)
group by 1
order by 1;

Step-by-step explanation.

1. The metric is declared once: a count_distinct of customer_id over fct_orders, with the "last 30 days" filter baked into the definition. This is the single source of truth for what "active" means.
2. Because the filter and the aggregation live in the semantic layer, no analyst can accidentally redefine "active customer" as "last 28 days" in one dashboard and "last 31" in another — the definition is centralized.
3. When a user drags active_customers onto a chart broken out by month, the semantic layer compiles the SQL shown, substituting the dimension (order_month) and preserving the canonical filter and aggregation.
4. Every BI tool that speaks to the semantic layer emits the same compiled SQL, so the number matches across Looker, a notebook, and an executive PDF.
5. This centralization is also what makes AI text-to-SQL safe: the copilot answers by referencing governed metrics, not by inventing raw SQL that might use the wrong definition.

Output.

order_month	active_customers
2026-07	8,412
2026-08	9,105
2026-09	9,540

Rule of thumb. Define each metric once in the semantic layer and let every BI tool (and AI copilot) compile from it. Metrics defined ad-hoc in individual dashboards are how two executives end up quoting different "revenue" numbers in the same meeting.

Worked example — an idempotent reverse-ETL sync

Detailed explanation. Reverse ETL must sync only what changed and never create duplicates in the destination. The pattern: compute a row hash, compare to the last-synced hash, and upsert only the diff via the destination's API keyed on a stable external id. Walk through a CRM sync of churned customers.

• Change detection. Hash each row; sync only rows whose hash changed.
• Idempotent upsert. Key on the CRM's external id so re-running updates in place.
• Safety. Respect API rate limits; record what was synced.

Question. Implement a reverse-ETL sync that pushes only changed churned_customers rows into the CRM idempotently.

Input.

Parameter	Value
Source mart	marts.churned_customers
Destination	CRM contacts
External key	email
Change detection	row hash vs last sync

Code.

-- The mart carries a stable key and a content hash for change detection
create or replace table marts.churned_customers as
select
    customer_id,
    email,                                          -- stable external key for the CRM
    customer_segment,
    churn_risk_score,
    md5(concat_ws('|', email, customer_segment,
                  churn_risk_score::text))          as _row_hash
from marts.fct_customer_360
where churn_flag = true;

# Sync only rows whose hash changed since last run; upsert by email (idempotent)
def reverse_etl_sync(wh, crm, state) -> dict:
    rows = wh.query("select customer_id, email, customer_segment, "
                    "churn_risk_score, _row_hash from marts.churned_customers")
    last = state.load_hashes("churned_customers")     # {email: hash} from last run

    changed = [r for r in rows if last.get(r["email"]) != r["_row_hash"]]
    for batch in chunked(changed, 100):               # respect CRM API rate limits
        crm.upsert_contacts(                          # upsert = insert or update by key
            key="email",
            records=[{"email": r["email"],
                      "segment": r["customer_segment"],
                      "churn_risk": r["churn_risk_score"]} for r in batch])

    state.save_hashes("churned_customers", {r["email"]: r["_row_hash"] for r in rows})
    return {"scanned": len(rows), "synced": len(changed)}

Step-by-step explanation.

1. The mart computes a _row_hash over the fields that matter, alongside a stable external key (email). The hash is the fingerprint used to detect whether a contact actually changed.
2. Each run loads the previous run's {email: hash} map and keeps only rows whose current hash differs — so an unchanged customer is never re-sent, keeping API volume proportional to change, not table size.
3. The upsert is keyed on email, the CRM's external id, so pushing the same contact twice updates the existing record instead of creating a duplicate. This makes the sync idempotent under retries.
4. Records are pushed in batches of 100 to respect the destination's rate limits — reverse ETL lives or dies by treating the SaaS API's quotas as a first-class constraint.
5. After syncing, the run persists the full current hash map, so the next run has an accurate baseline. A retried run re-computes the same diff and upserts the same rows harmlessly.

Output.

Run	Rows scanned	Rows changed	API calls	Duplicates in CRM
1 (bootstrap)	5,000	5,000	50 batches	0
2	5,010	34	1 batch	0
2 (retried)	5,010	34	1 batch	0
3	5,010	0	0	0

Rule of thumb. Reverse ETL syncs diffs, not tables: hash rows, compare to last sync, upsert only changes keyed on a stable external id, and batch for rate limits. Pushing the whole table every run wastes API quota and risks duplicates.

Worked example — governance controls on a mart

Detailed explanation. Governance is concrete SQL, not a slogan: column masking hides PII from most roles, and a row-access policy restricts rows by attribute. Walk through both on fct_customer_360.

• Column masking. Analysts see a masked email; only a PII role sees the raw value.
• Row access. Regional analysts see only their region's rows.
• Enforced by the warehouse, so no query can bypass it.

Question. Apply a masking policy on email and a row-access policy by region to fct_customer_360.

Input.

Control	Column / scope	Effect
masking policy	email	masked unless role = pii_reader
row-access policy	region	analyst sees only their region
enforcement	warehouse engine	cannot be bypassed

Code.

-- 1. Column-level masking: most roles see a masked email, PII role sees raw
create masking policy mask_email as (val string) returns string ->
  case
    when current_role() in ('PII_READER', 'ACCOUNTADMIN') then val
    else regexp_replace(val, '^[^@]+', '****')       -- ****@domain.com
  end;

alter table marts.fct_customer_360
  modify column email set masking policy mask_email;

-- 2. Row-access policy: an analyst only sees rows for their mapped region
create row access policy region_rap as (region string) returns boolean ->
  exists (
    select 1 from governance.role_region_map m
    where m.role_name = current_role()
      and m.region    = region
  )
  or current_role() in ('ACCOUNTADMIN');

alter table marts.fct_customer_360
  add row access policy region_rap on (region);

Step-by-step explanation.

1. The masking policy is a function the warehouse applies at query time: if the querying role is PII_READER (or admin) it returns the raw email, otherwise it returns a masked form. Analysts building dashboards never see raw PII.
2. Because masking is attached to the column with modify column ... set masking policy, it applies to every query against email, including ad-hoc ones and BI extracts — there is no way to select around it.
3. The row-access policy is a boolean function evaluated per row: it checks a role_region_map table to see whether the current role is allowed to see that row's region, returning true only for permitted regions (admins bypass).
4. Attaching it with add row access policy ... on (region) means a SELECT * by a EU-region analyst silently returns only EU rows — the filter is enforced by the engine, not by the analyst remembering to add a WHERE.
5. Together these give PII protection and regional data isolation as table properties, so governance travels with the data into every downstream BI query and even reverse-ETL read, satisfying auditors without trusting every query author.

Output.

Role	Sees email	Sees rows
analyst_eu	****@acme.com	EU region only
analyst_us	****@acme.com	US region only
pii_reader	raw email	per their region map
accountadmin	raw email	all regions

Rule of thumb. Enforce PII and access rules as warehouse-level masking and row-access policies attached to the table, not as WHERE clauses in each report. Policy that lives on the data cannot be forgotten or bypassed by the next query someone writes.

Data engineering interview question on activation and governance

A senior interviewer might ask: "Marketing wants churned customers pushed into the CRM automatically, and the same customer table must feed executive dashboards — but the table contains PII and spans regions. Design the activation path: the governed mart, the BI/semantic definition, the reverse-ETL sync, and the governance controls that keep PII and regional access correct end to end."

Solution Using a governed mart feeding BI and an idempotent reverse-ETL sync

-- 1. Governed mart: stable keys, a content hash for change detection, PII protected
create or replace table marts.fct_customer_360 as
select
    customer_id,
    email,
    region,
    customer_segment,
    churn_flag,
    churn_risk_score,
    md5(concat_ws('|', email, customer_segment, churn_risk_score::text)) as _row_hash
from marts.int_customer_features;

-- 2. Governance travels with the table
alter table marts.fct_customer_360 modify column email set masking policy mask_email;
alter table marts.fct_customer_360 add row access policy region_rap on (region);

# 3. Activation forks from ONE governed mart: BI reads it; reverse ETL syncs the diff
def activate(wh, crm, state):
    # BI path: the semantic layer / dashboards query fct_customer_360 directly
    # (masking + row access apply automatically to every BI query)

    # Reverse ETL path: sync only churned customers whose hash changed
    rows = wh.query("select email, customer_segment, churn_risk_score, _row_hash "
                    "from marts.fct_customer_360 where churn_flag = true")
    last = state.load_hashes("churn")
    changed = [r for r in rows if last.get(r["email"]) != r["_row_hash"]]
    for batch in chunked(changed, 100):
        crm.upsert_contacts(key="email", records=batch)   # idempotent upsert by email
    state.save_hashes("churn", {r["email"]: r["_row_hash"] for r in rows})
    return {"synced": len(changed)}

Step-by-step trace.

Layer	Component	Guarantee
Mart	fct_customer_360 with _row_hash	one governed source for both paths
Governance	masking + row-access policies	PII masked, region isolated, engine-enforced
BI	semantic metrics over the mart	consistent numbers, policies auto-applied
Reverse ETL	hash-diff + upsert by email	idempotent, syncs only changes
State	last-synced hash map	change detection across runs

After deployment, one governed mart serves both consumers. Executives explore metrics in BI where masking and row-access policies apply automatically, so a US analyst never sees EU rows or raw emails. Marketing's CRM audience stays current through a reverse-ETL sync that pushes only changed churned customers, keyed idempotently on email — and because governance lives on the table, both the dashboard and the sync inherit the same PII and regional guarantees without any extra code.

Output:

Metric	Value
Sources of truth for the customer	1 (fct_customer_360)
PII exposure to analysts	masked (engine-enforced)
Regional data isolation	row-access policy
Reverse-ETL API calls	proportional to change, not table size
Duplicate CRM contacts	0 (upsert by email)

Why this works — concept by concept:

• One governed mart, two consumers — BI and reverse ETL both read the same fct_customer_360, so "the number" and "the CRM audience" can never diverge. Activation forks at the very end, not upstream.
• Governance on the table — masking and row-access policies are attached to the mart, so every downstream query and sync inherits PII masking and regional isolation with no per-consumer code and no way to bypass.
• Change-detected reverse ETL — hashing rows and syncing only diffs keyed on a stable external id makes the sync idempotent and keeps API usage proportional to actual change, respecting SaaS rate limits.
• Semantic layer for BI — metrics defined once compile to consistent SQL for every dashboard and every AI copilot, which is what makes 2026's text-to-SQL trustworthy rather than a source of contradictory answers.
• Cost — one mart build plus a diff-sized sync per run and a few cheap policy evaluations. Compared to maintaining separate PII logic per dashboard and pushing whole tables to the CRM, this is O(change) activation with governance amortized into the table definition, not re-implemented per consumer.

SQL
Topic — database
Governance, access-control, and SQL modeling problems

Practice →

Data
Topic — data-transformation
Reverse ETL and activation pipeline problems

Practice →

---

Cheat sheet — modern data stack recipes

• The five layers. Ingestion (land raw) → Storage (warehouse/lakehouse, raw+marts zones) → Transformation (dbt: staging→marts) → Orchestration + observability (schedule, retries, checks) → Activation (BI for people, reverse ETL for machines). Governance (catalog, access, lineage) is a cross-cut over all five, not a sixth box.
• ELT-vs-ETL rule. Load raw into the warehouse first, transform in place with SQL. Keep the raw zone immutable and replayable. If a transform runs before the data lands, it is ETL and you have thrown away replayability. Cheap object storage + elastic separated compute is why the flip happened.
• Idempotent incremental-load template. TRUNCATE _delta; INSERT _delta SELECT ... WHERE updated_at > watermark; MERGE INTO raw.tbl USING _delta ON id WHEN MATCHED UPDATE WHEN NOT MATCHED INSERT; advance watermark to MAX(observed updated_at). Re-running yields the same table, zero duplicates.
• Warehouse vs lakehouse. Warehouse = managed columnar SQL engine (Snowflake/BigQuery/Redshift): pick it for SQL/BI + low ops. Lakehouse = open table format (Iceberg/Delta) over object storage: pick it for open formats + multi-engine ML + cheap petabyte scale. When unsure, pick the surface that can query an open table format to keep optionality. A lakehouse is Parquet plus a table format — files alone are a swamp.
• dbt layering. stg_ (1:1 clean, dedupe, retype) → int_ (reusable joins) → fct_/dim_ (star schema). Always build marts on {{ ref('stg_...') }}, never on a raw source directly. ref() builds the DAG and the lineage for free.
• Materialization picker. view for light staging, table for small stable marts, incremental (with unique_key + is_incremental() + merge) for large facts, ephemeral for inlined helpers. Incremental with merge so late updates replace rows instead of duplicating.
• Test every model. At minimum unique + not_null on the key and relationships on every foreign key; add accepted_values for enums and singular SQL tests for business rules. Run dbt build (models + tests) so a failure blocks the pipeline.
• Orchestration rules. Encode dependencies as a DAG (ingest → dbt build → checks → activation), retry transient failures with backoff, and make every task idempotent so backfills are a safe loop. Gate activation on tests passing — reverse ETL must never run on failed data.
• Observability checks. Freshness (now() - max(ts) < SLA), volume (today within ±40% of trailing 7-day avg), schema (columns added/removed/retyped), distribution (null-rate / new-category drift), lineage (source→dashboard impact). Freshness + volume catch "job ran, data wrong" that orchestration cannot see.
• Reverse ETL template. Add a _row_hash and a stable external key to the mart; each run sync only rows whose hash changed; upsert by external key (idempotent); batch for API rate limits; persist the hash map for next-run change detection. Sync diffs, never whole tables.
• Governance controls. Catalog every model with an owner; enforce PII with column masking policies and access with row-access policies attached to the table (engine-enforced, not WHERE clauses); keep lineage source→CRM; enforce data contracts at ingestion/transformation so producers cannot silently break consumers.
• 2026 shifts to name. AI copilots / text-to-SQL guarded by the semantic layer; zero-ETL replication collapsing part of ingestion; warehouse–lakehouse convergence on open table formats; and cost governance (FinOps) as a named discipline now that compute is concentrated in the warehouse.

Frequently asked questions

What is the modern data stack in one sentence?

The modern data stack is a modular set of managed, cloud-native tools — data ingestion, a cloud warehouse or lakehouse, dbt transformation, orchestration with observability, and activation (BI plus reverse ETL) — wired together in a fixed order so that raw data flows from source systems into storage, gets modeled in place, and flows back out to dashboards and operational tools. Its defining trait is modularity: each layer is a swappable, best-of-breed component connected by the ELT pattern rather than one monolithic ETL server. It became the norm because cloud object storage got cheap and cloud compute got elastic at the same time, which made it affordable to keep every raw row and transform it inside the warehouse on demand.

ELT vs ETL — what changed and why?

Classic ETL extracts data, transforms it on a separate server, then loads only the finished tables into the warehouse — transformation happens before loading because warehouse storage and compute used to be expensive and coupled. ELT flips the last two steps: extract, load the raw data straight into the warehouse first, then transform it in place with SQL. The shift happened because cloud warehouses separated storage from compute and made both cheap and elastic — you can now afford to keep every raw row and burst compute for big transformations, then turn it off. ELT's payoff is replayability: because the raw zone persists, fixing a transformation bug is a SQL change plus a re-run, not a re-extraction from the source, and the logic lives in version-controlled, testable SQL instead of bespoke code.

Warehouse vs lakehouse — how do I choose?

A warehouse (Snowflake, BigQuery, Redshift) is a managed columnar SQL engine: you load tables and query them, and the vendor handles files and performance. A lakehouse (Databricks, or Iceberg/Delta over object storage) puts warehouse-like ACID transactions, schema evolution, and time travel on top of open Parquet files in cheap object storage, queryable by many engines. Choose the warehouse when your workload is SQL/BI-first, you want minimal operations, and simple pricing matters more than open formats. Choose the lakehouse when you run heavy Spark/ML across multiple engines, care about avoiding lock-in with an open table format, or operate at cheap petabyte scale. In 2026 the two are converging — warehouses query external Iceberg tables and lakehouses ship SQL engines — so picking a surface that speaks an open table format keeps your options open.

What does dbt actually do?

dbt is the transformation layer of the modern stack: it lets you write each data model as a SELECT statement, wires those models into a dependency DAG through the ref() function, and materializes them as views, tables, or incremental tables in your warehouse. Beyond running SQL, it makes transformation behave like software — models are version-controlled and code-reviewed, ref() auto-derives build order and lineage, and built-in tests (unique, not_null, relationships, accepted_values, plus custom SQL tests) turn data-quality expectations into build-time gates. It also generates documentation and a lineage graph automatically. The result is that the marts everyone queries are reproducible from raw with a single dbt build, and bad data is stopped at the transformation layer instead of reaching dashboards.

What is reverse ETL and when do I need it?

Reverse ETL syncs modeled tables from the warehouse back into operational SaaS tools — the opposite direction of ingestion. For example, a churned_customers mart becomes a CRM audience, or a lead-score mart becomes an ad-platform custom audience. You need it whenever the complete, joined view of a customer lives in the warehouse but the people (or software) who act on it work inside another tool — sales reps in a CRM, marketers in an ad platform, support agents in a helpdesk. This is "operational analytics": closing the loop so modeled data drives action, not just dashboards. A good reverse-ETL sync is idempotent and change-detected — it hashes rows, syncs only what changed, upserts by a stable external key, and batches for API rate limits — so it never creates duplicates or blows through quotas.

What are the biggest 2026 shifts in the stack?

Four shifts define 2026. First, AI in every layer: text-to-SQL copilots let people ask questions in English, and AI assists documentation, anomaly detection, and pipeline authoring — with the semantic layer acting as the guardrail that keeps answers correct. Second, zero-ETL: cloud vendors replicate an operational database straight into the warehouse with no connector to manage, collapsing part of the ingestion layer. Third, warehouse–lakehouse convergence: both increasingly query the same open table formats (Iceberg, Delta), so the storage choice stops being a permanent fork. Fourth, cost governance (FinOps): because ELT concentrates compute in the warehouse, controlling and attributing that spend has become a named discipline with its own tooling. Underlying all four, governance keeps shifting left — contracts, catalogs, and lineage enforced at ingestion and transformation time rather than bolted on at the end.

Practice on PipeCode

• Drill the ETL practice library → for the ELT-vs-ETL, incremental-load, and idempotent-pipeline problems that map directly onto the modern data stack's ingestion and transformation layers.
• Work through the data-transformation practice library → for orchestration, backfill, observability, and reverse-ETL activation scenarios.
• Sharpen the fundamentals on the database practice library → for the SQL modeling, star-schema, and warehouse-governance skills every layer depends on.
• Stack these against PipeCode's broader 450+ data-engineering catalogue to anchor the five-layer model against real graded inputs.

Turn the stack diagram into muscle memory

Reading the layers is easy; building them under interview pressure is the hard part — when ELT beats ETL, when a load must be idempotent, when a fact table should go incremental, when observability catches what orchestration misses, and when reverse ETL needs change detection. Pipecode.ai is Leetcode for Data Engineering — layer-by-layer practice tuned for the production trade-offs the modern data stack actually demands.

Practice ETL problems →
Practice data-transformation problems →