Practice Questions

In ETL you transform before loading, so the warehouse only ever sees cleaned, modeled data. In ELT you land raw source data first and transform inside the warehouse with SQL. ELT won because cloud warehouses and lakehouses made storage cheap and compute elastic, so keeping the raw layer is affordable and gives you two things ETL throws away: you can re-derive marts when logic changes without re-ingesting, and you have an immutable landing zone to reconcile against. The staging → intermediate → marts (bronze/silver/gold) layering falls straight out of ELT.

ETL still applies where you must transform before landing — PII redaction or tokenization for compliance, or heavy pre-aggregation of firehose volume that's too expensive to store raw.

Level up: Senior explains the trade-off and names where ETL survives. Staff frames it as a data-modeling and cost decision — raw retention policy, the reprocessing guarantee it buys, and when a lakehouse table format (Iceberg/Delta) makes the raw layer itself queryable and time-travelable rather than dead storage.

Idempotent means running the same task with the same inputs produces the same output no matter how many times you run it — a re-run doesn't double-count or corrupt. You get it by making writes partition-overwrite or merge-on-key rather than blind append, and by keying on natural/business keys so a repeated row lands in the same place. It matters first because everything downstream depends on it: retries, backfills, catchup, and late-data reprocessing all re-run tasks, and if a re-run isn't safe, none of those are safe either.

Level up: Senior designs merges keyed on order_id writing into the correct event_date partition. Staff extends idempotency to effectively-once delivery end to end — idempotency keys through the ingest layer, atomic partition swaps, dedup on at-least-once sources — so the guarantee holds across system boundaries, not just inside one SQL model.

Task-centric (classic Airflow) orchestrates operations: "run task A, then B." Asset-centric (Dagster, dbt's lineage, Airflow datasets) orchestrates the data: you declare the tables each model produces and consumes, and the system schedules from data dependencies. Asset-centric matters when you want data-aware scheduling (rebuild only what changed), lineage for free, and freshness/quality policies attached to the asset rather than the task. Task-centric is fine for simple, mostly-linear flows and for jobs that aren't really about materializing a dataset.

Level up: Senior can compare Airflow vs Dagster vs Prefect vs Temporal and pick appropriately. Staff talks about migration and standards — moving an org from time-based task DAGs to data-aware assets, and why software-defined assets reduce the "why did this table not update" class of incidents.

Dependency resolution (run B only after A actually succeeds, not "A probably finished by 2am"), retries with backoff, backfill and catchup over historical intervals, sensors/triggers to wait on upstream data, SLAs, parameterization, and observability into every run. Cron gives you a start time and nothing else — no dependency graph, no retry semantics, no visibility, and the classic failure mode of encoding dependencies as "schedule B late enough that A is probably done."

Level up: Senior names the interval/execution model and uses data-dependencies instead of time-dependencies. Staff notes the orchestrator's own failure domains — it schedules and monitors, it is not a compute engine — and pushes heavy work down to Spark/warehouse, keeps top-level DAG code cheap, and uses deferrable/async sensors so waiting tasks don't starve workers.

• Business logic inside operators. The transform lives in the DAG file, so it can't be tested or reused. Fix: operators call thin, tested transformation code (or delegate to dbt/Spark); the DAG only orchestrates.
• Heavy work at top-level (parse-time) DAG code. Every scheduler heartbeat runs it. Fix: keep imports and DB calls inside task bodies.
• Time-based dependencies instead of data-dependencies. "Schedule downstream 30 min later." Fix: sensors or dataset/asset triggers that fire on actual completion.

Honorable mentions: the giant monolithic DAG, non-idempotent tasks, and using the orchestrator as a compute engine (huge XComs, pandas in a task).

Level up: Staff connects these to blast radius — a monolithic DAG means one bad task blocks unrelated data; splitting by domain/asset shrinks the failure domain.

Event time is when the thing happened (the user tapped buy). Ingestion time is when it reached your system. Processing time is when your job ran on it. They diverge because of mobile offline buffers, network retries, upstream batch cutoffs, timezones, and out-of-order Kafka partitions. The distinction is the whole game because every correctness question — which day does this revenue belong to, is yesterday's total final — is answered in event time, while your pipeline is triggered in processing time. If you bucket by processing time you'll silently misattribute late events to the wrong day.

Level up: Senior always partitions and aggregates by event time and keeps ingestion time as an audit column. Staff ties it to an explicit completeness SLA — "event-day totals are final after N hours / N% watermark" — so consumers know when a number stops moving.

A watermark is the system's assertion that "I've now seen all events up to event-time T" — bounded out-of-orderness. It's the knob that lets you close a window and emit a result. The trade-off it encodes is completeness vs latency: a conservative watermark (wait longer) catches more stragglers but delays the answer; an aggressive one emits sooner but risks dropping or having to correct late events. Allowed-lateness lets you accept and re-emit for events that arrive after the watermark, at the cost of downstream restatement.

Level up: Senior sets a watermark plus allowed-lateness and reprocesses corrections. Staff makes the trade-off a product decision with a number attached, and designs downstream to absorb corrections (immutable partitions + reprocessing, or versioned snapshots) so a late correction doesn't break consumers.

This is the classic late-arriving dimension / early-arriving fact. Two workable patterns: (1) inferred/placeholder dimension member — insert a stub dimension row for the unknown key so the fact can load and join now, then enrich the stub when the real dimension arrives; (2) park-and-retry — route the orphan fact to a quarantine/pending table and re-drive it once the dimension shows up. I default to the inferred member because it keeps the fact queryable immediately and preserves referential integrity; the enrichment is just an idempotent update to the stub.

Level up: Senior implements the inferred member and back-fills attributes. Staff adds the reconciliation loop — a check that counts unresolved stubs and alerts if they age past an SLA — so "temporarily unknown" never silently becomes "permanently wrong."

SCD Type 2 with effective dating. Instead of overwriting the attribute, you close the current version (valid_to = change_date) and open a new version (valid_from = change_date, is_current = true), so historical facts still join to the attribute value that was true at their event time via an as-of join. A retroactive correction means inserting/adjusting a version with the right effective window rather than stamping "now."

Level up: Senior implements SCD2 and as-of joins. Staff reaches for bitemporal modeling — separating valid time (when it was true in the world) from system/transaction time (when we recorded it) — so you can answer both "what was the segment on the order date" and "what did our reports show last Tuesday," which is exactly what finance and audit ask during a restatement.

Lambda runs a fast streaming/speed layer for fresh-but-approximate results alongside a batch layer that recomputes the authoritative answer, and serves a merge of the two — late data is fixed by the batch recompute. Kappa drops the batch layer: there's one streaming pipeline, and you correct by replaying the log through the same code. Lambda's cost is maintaining two codebases that must agree; Kappa's cost is that replay-at-scale and long-window state are genuinely hard. I lean Kappa when the stream processor (Flink) can own state and replay cleanly, and keep a Lambda-style batch reconciliation when there's a hard correctness bar that must be independently verifiable.

Level up: Staff notes that a lakehouse table format collapses much of the distinction — immutable, replayable partitions with time-travel let one pipeline serve both fresh and restated results, so the "two layers" problem is really a storage-guarantees problem.

Each run reprocesses not just yesterday but the last N days of event-time partitions, merging (not appending) into the correct event-date partition so late arrivals land where they belong. N is set from the observed late-arrival distribution — the p99 delay, not a guess. Pitfalls: (1) appending instead of merging double-counts on every overlapping run; (2) N too small drops the long tail, N too large burns compute reprocessing settled data; (3) reconciliation must run per event-date, or a correction to day-5 gets masked; (4) the window must be idempotent so a re-run is a no-op on unchanged partitions.

Level up: Senior sizes N from the delay distribution and merges idempotently. Staff pairs the lookback with a completeness SLA and a reconciliation that flags any partition still moving after it was declared final, so the lookback isn't just hope with a number.

• Append-only — immutable event logs where rows never change. Fastest, but no updates and no dedup.
• Merge/upsert — mutable records keyed on a unique key; the general-purpose default when rows can change.
• Insert-overwrite (partition replace) — recompute and atomically swap whole partitions; ideal for event-date-partitioned data with a lookback, and naturally idempotent.
• Delete+insert — merge for engines without a good MERGE; delete the affected keys/partitions, then insert.
• Microbatch — process bounded time slices with an event-time column and lookback (dbt's microbatch model), which makes backfills a matter of replaying slices.

Level up: Senior maps each to a concrete case and knows the dbt is_incremental() / unique_key / strategy plumbing. Staff picks based on the storage engine's atomicity guarantees — insert-overwrite on a table format gives you snapshot isolation and cheap rollback that a plain warehouse MERGE doesn't.

You bookmark max(updated_at) from the last run and next run pull where updated_at > watermark. Two bugs: strict > drops any row sharing the exact boundary timestamp (ties, second-resolution clocks), and rows committed in a transaction that opened before your read but committed after can be missed entirely. Fix: pull >= watermark with an idempotent merge and dedup so re-reading the boundary is harmless, or use an overlap window (re-scan a small margin) plus merge. For real correctness use a monotonic source of truth — a sequence, or a CDC log LSN — rather than a wall-clock updated_at that can move backward.

Level up: Staff distrusts updated_at as a watermark entirely for anything financial and moves to log-based CDC where ordering and completeness are guaranteed by the log, not by clock hygiene.

Make the incremental backfill-safe by construction: partition by event date and use insert-overwrite/merge on the partition key so re-running any date range is idempotent — a backfill is then just "run these historical intervals," and the orchestrator's partitioned backfill handles it. Avoid double-counting by never appending; each partition is fully replaced, not added to. Run the backfill on its own resources/pool so it doesn't starve live runs, and reconcile after — row and aggregate counts per backfilled partition against source — before declaring it done. If logic changed, version the output or write to a shadow table and swap.

Level up: Staff plans the backfill as an operation with a blast radius: bounded concurrency, a kill switch, downstream consumers notified (contracts), and an audit record so the restatement is reproducible.

Query-based CDC polls where updated_at > watermark — simple, but it misses hard deletes (the row is just gone), misses intermediate states between polls, and leans on clock hygiene. Log-based CDC (Debezium reading the DB write-ahead log) streams every insert/update/delete in commit order, so it captures deletes and preserves ordering by design. Handle deletes with soft-delete tombstones or a merge that removes the key; preserve ordering by keying Kafka partitions on the primary key so all changes to one row stay ordered, and dedup on the log sequence (LSN) for exactly-once into the warehouse.

Level up: Senior knows log-based captures deletes and ordering. Staff designs the whole path — outbox on the producer to avoid dual-write, LSN-based dedup, and compaction/retention on the topic — so the CDC stream is itself a reliable, replayable source of truth.

Start with control totals by grain: for each event date, compare row counts and summed measures (e.g. sum(amount)) between source and target, and report the variance with a tolerance. Counts catch missing/duplicate rows; sums catch value corruption that counts miss. Where the correctness bar is hard (finance), add hash-diff / checksum reconciliation on business keys to find which rows differ, not just that a total is off. Reconcile on a cadence (per load, plus a daily sweep), set tolerance thresholds so rounding doesn't page anyone, and write results to an audit table with a break-investigation workflow.

Level up: Senior builds the count-and-sum recon with tolerances and blocks on breaks. Staff adds financial-grade invariants (debits=credits, sum(source)=sum(target) end to end), automated break triage that classifies the likely cause, and recon dashboards consumers can self-serve.

WAP writes the new data to a staging location, runs all validation/reconciliation checks against that staged copy (the audit), and only publishes — atomically swaps it into the location consumers read — if the checks pass. It beats post-hoc validation because bad data never becomes visible: consumers only ever see audited data. On a table format you implement it with branches/snapshots and an atomic swap; on a warehouse, staging tables and a swap or view repoint. Pair it with a circuit breaker that halts promotion on a failed critical check.

Level up: Senior implements WAP with a staging-then-swap and blocks on failure. Staff distinguishes blocking checks (fail the publish) from warning checks (page but publish), quarantines the offending rows rather than failing the whole batch where possible, and makes the swap the single choke point every dataset promotes through.

A data contract is an agreement between a producer and its consumers covering schema (columns, types, nullability), semantics (what a field means, its valid values), and SLA (freshness, completeness). It's enforced in two places: at CI on the producer — a schema/semantic change that breaks the contract fails the build before it ships — and at ingest — incoming data that violates the contract is rejected or quarantined rather than silently propagated. The point is to move breakage from "3am downstream page" to "the producer's PR."

Level up: Senior defines and enforces a contract for a critical table. Staff makes contracts an org standard with versioning and a deprecation process, so producers can evolve schemas without a coordinated flag-day across every consumer.

Six standard ones: completeness (not_null, row-count vs source), uniqueness (unique/primary-key tests), validity (accepted_values, range/format checks), consistency (relationships/referential integrity, cross-table invariants), accuracy (reconciliation against a source of truth), and timeliness (freshness checks). In dbt these are generic tests (unique, not_null, relationships, accepted_values) plus dbt_utils/dbt_expectations for the richer ones; Great Expectations/Soda/Deequ cover distributional and profiling checks. Accuracy is the one you can't test in isolation — it needs recon against source.

Level up: Staff distinguishes cheap column tests (run everywhere, always) from expensive distributional/anomaly checks (run where they earn it), and ties each dimension to whether a failure warns or blocks.

Block when a failed check means downstream would consume wrong data — broken primary key, referential integrity violation, reconciliation break beyond tolerance, freshness past a hard SLA. Warn when the signal is worth a human look but not worth halting everything — a distribution drifting, volume slightly off, a non-critical column newly null. The deciding question is blast radius: would shipping this corrupt a number someone makes a decision on? If yes, the circuit breaker fires and nothing publishes.

Level up: Senior classifies checks as blocking vs warning and wires the breaker. Staff adds tolerance bands and severity routing so alerts are actionable and deduped — the difference between a pager that people trust and one they mute.

Never silently drop. Route them to a quarantine / dead-letter table with the failure reason and the raw payload, so the good rows proceed and the bad ones are visible, countable, and re-drivable once fixed. This keeps one malformed record from failing an entire batch while guaranteeing nothing is lost. Alert on quarantine volume and on age — a growing or aging quarantine is a real incident even though the pipeline is "green."

Level up: Senior quarantines with reasons and re-drives. Staff closes the loop: quarantined rows count against the completeness SLA and reconciliation, so "we dropped 2% to the dead-letter queue" can't hide behind a passing pipeline.

At-most-once may drop (fire and forget). At-least-once may duplicate (retry until acked). True exactly-once across a distributed boundary is very hard and often impossible without coordination. The practical answer is effectively-once: accept at-least-once delivery — which is cheap and reliable — and make the consumer idempotent (idempotency keys + dedup, or a merge on business key) so duplicates are absorbed. The result is exactly-once semantics without needing exactly-once delivery.

Level up: Senior reaches for at-least-once + dedup by default. Staff names where genuine exactly-once exists (Kafka transactions, Flink's checkpointed exactly-once sinks) and where it's a mirage, and picks idempotency as the guarantee that survives any retry, replay, or backfill.

The dual-write problem: a service needs to update its database and publish an event, but those are two systems with no shared transaction — if it commits the DB row and then crashes before publishing (or vice versa), the two diverge and you get lost or phantom events. The transactional outbox fixes it by writing the event into an outbox table in the same DB transaction as the business change, so they commit atomically; a separate relay (often CDC on the outbox table) then reliably publishes from the outbox. One atomic write, no dual-write, and the relay is replayable.

Level up: Staff connects it to the CDC discussion — the outbox is what makes an event stream trustworthy as a source of truth, and pairs it with LSN-based dedup downstream for effectively-once end to end.

The observability pillars for data are freshness (is it up to date), volume (did the expected number of rows arrive), schema (did the shape change), and distribution (do the values look right) — plus lineage as the connective tissue. Lineage (ideally column-level, via OpenLineage/Marquez or dbt's graph and exposures) is what turns a freshness alert into a resolution: it tells you what upstream broke and, crucially, which downstream dashboards and consumers are affected so you know the blast radius before someone else finds out.

Level up: Senior monitors the four pillars and uses lineage to trace incidents. Staff instruments lineage as a first-class asset — column-level, wired to exposures — so impact analysis and "who do I tell" are automatic, and anomaly detection (control charts / observability tooling) catches the drift the static tests miss.

An SLI is the measured indicator (data is fresh as of 08:00; 99.8% of expected rows present). An SLO is your internal target for that indicator (freshness by 08:00 on 99% of days), with an error budget. An SLA is the externally-promised version, usually with consequences. An Airflow "SLA" is just a task-duration miss alert — it tells you a task ran long, not whether the data met a freshness or completeness objective. Real data SLOs are defined on the data's properties (freshness, completeness) and measured with SLIs, independent of how any one task performed.

Level up: Staff defines SLOs on consumer-facing data properties with error budgets that drive on-call and prioritization, and treats "data downtime" as an incident class with detect → contain → backfill/restate → prevent and a postmortem, not just a red task in a DAG.

Partitioning by event date lets the engine prune — skip partitions a query doesn't need — which is the biggest single win for large tables. But over-partitioning (e.g. by hour and a high-cardinality key) shatters data into millions of tiny files; every query pays per-file open overhead and the metadata layer chokes. That's the small-files problem, common with frequent streaming/micro-batch writes. Cures: partition at a grain that keeps files a sensible size (hundreds of MB), compact small files on a schedule, and use clustering/bucketing/Z-ordering for secondary access paths instead of more partition columns.

Level up: Staff frames it as a write-pattern vs read-pattern trade-off and reaches for a table format's compaction and hidden partitioning so the physical layout can evolve without rewriting queries.

Plain Parquet files have no transactions — concurrent writers, partial writes, and "which files are current" are all your problem. A table format adds a metadata layer that gives you ACID on object storage: atomic commits (so WAP swaps and insert-overwrite are safe), snapshots / time-travel (query or roll back to a prior state — gold for restatement and reconciliation), and schema and partition evolution without rewriting data. Iceberg leans hidden partitioning and engine-neutral metadata; Delta is tightly integrated with Spark; Hudi optimizes upserts/CDC with merge-on-read vs copy-on-write.

Level up: Staff connects it back to correctness — snapshots make late-data restatement reproducible and auditable, and atomic commits are what let WAP and effectively-once actually hold on a lake.

Drive it from the freshness SLA the consumer actually needs, then the correctness bar and cost. If the business decision is made daily, batch is right — simplest, cheapest, easiest to reconcile. If it needs minutes, micro-batch (Spark Structured Streaming, dbt microbatch) gets most of the freshness with batch-like operational simplicity. Reserve true streaming (Flink) for sub-second/continuous needs — fraud, live ops — where the latency genuinely changes the outcome. Streaming buys latency at the cost of complexity, harder reconciliation, and state management; you earn that complexity, you don't default to it.

Level up: Senior matches the tier to the SLA and names the streaming tax. Staff notes you can often serve a real-time view off a batch core (fresh speed layer + authoritative batch recon) rather than making the whole pipeline streaming — matching latency to the slice that needs it.

A shuffle is the all-to-all data movement a wide operation (join, group-by) triggers to co-locate keys — the expensive part of most jobs. Skew is when one key holds a disproportionate share of rows, so one task does most of the work and everyone waits on it (the straggler). Spill is when a task's data exceeds memory and Spark writes to disk, tanking performance. You broadcast a join when one side is small enough to ship to every executor, eliminating the shuffle entirely. Fixes for skew: salting the hot key, adaptive query execution's skew join handling, or filtering/pre-aggregating before the join.

Level up: Staff diagnoses from the Spark UI (stage/task duration, shuffle read/spill metrics) and treats partition sizing and skew handling as cost engineering, not just a correctness fix.

Have a real one, told as detect → contain → fix → prevent. Situation: what broke and how you found out (ideally your own monitoring caught it, not a stakeholder). Action: how you contained the blast radius (stopped promotion, held the last-good snapshot so consumers weren't served garbage), then diagnosed with lineage, then backfilled/restated idempotently. Result: the fix, and — most important — the prevention: the test, contract, or reconciliation you added so this class of break can't recur silently. The prevention beat is what separates a war story from a growth story.

Level up: Staff frames it as an incident with an error budget and a postmortem, and generalizes the fix into a standard other pipelines inherit.

Don't win the argument on architecture; win it on the requirement. Ask what decision the freshness enables and on what cadence it's actually made — "real-time" usually means "I don't want stale data," not "sub-second." Then present the trade-off honestly: streaming buys minutes-to-seconds of latency at a large cost in complexity, reconciliation difficulty, and on-call burden, while a batch or micro-batch pipeline meets a daily/hourly decision with far higher reliability and a much cleaner correctness story. Offer the middle: a fresh view where it's cheap, an authoritative batch recon underneath. Match the design to the requirement; earn complexity, don't gift it.

Level up: Staff quantifies the cost and reframes it as reliability and dollars, so the business chooses knowingly rather than the engineer over-building on a vibe.

Clarify before drawing. Two or three sharp questions: volume/throughput, freshness SLA, correctness bar (is this feeding finance?), and who consumes it. Then state the simple, correct version out loud — an idempotent batch pipeline: sources → land raw → transform (staging → marts) → serve, orchestrated and reconciled — and get it on the board fast. Then hand the interviewer the wheel: "there's real depth in late data, reconciliation, and failure handling here — where do you want to go first?" That sequence (clarify → simple correct baseline → invite the deep dive) shows requirements-first discipline and pacing, and sets you up to level up on exactly the thread they care about.

Level up: Senior runs clarify → baseline → deep dive cleanly. Staff proactively names the level-up on each lever — "the naive load is a full refresh; I'd build incremental with a lookback for late payments, add source-to-target reconciliation with a tolerance because finance consumes this, and WAP so a bad batch never publishes" — climbing two rungs unprompted. See Chapter 09 for the full worked walkthrough.