ETL Interview Questions: Concepts, Testing, and Scenarios

ETL transforms data on a separate engine before loading the warehouse (Informatica, SSIS, Spark); ELT loads raw data first and transforms inside the warehouse using its MPP engine — Snowflake, BigQuery, or Synapse, typically with dbt as the transform layer. ELT dominates cloud stacks because storage is cheap, warehouse compute scales, and raw data stays available for reprocessing. A sharp addition: ELT shifts data-quality and governance checks to after the load.

Full load truncates and reloads everything; incremental moves only new or changed rows using a watermark or CDC. Full load remains correct for small dimension tables, sources with no reliable change indicator, or when the reload window is trivial — simplicity beats maintaining change-detection logic. Incremental becomes mandatory when volume or load windows make full reloads impossible.

A staging layer holds raw extracted data before transformation — it decouples extraction from transformation, lets you reload and debug without hitting the source again, enables reconciliation against the source, and isolates source outages. In modern lakehouse terms this is the raw or bronze zone. Loading targets directly means any transform bug forces re-extraction, and you lose the audit trail.

The standard set: row-count and checksum reconciliation between source and target, not-null and uniqueness checks on keys, referential integrity between facts and dimensions, accepted-range and domain checks, and freshness checks confirming today's partition arrived. Make critical rules blocking (fail the pipeline) and the rest warning-level, implemented with dbt tests, Great Expectations, or plain SQL assertions.

It manages dependencies (task B only after A), retries with backoff, alerting, backfills, parameterized runs, and concurrency control, plus a monitoring surface for run history. Cron only answers "when"; an orchestrator answers "in what order, what happens on failure, and how do I rerun window X". That distinction is exactly what the interviewer is probing.

Timestamp/watermark: simplest, but misses hard deletes and rows updated without touching the column. Log-based (Debezium, SQL Server CDC, Oracle GoldenGate) reads the transaction log and captures inserts, updates, and deletes with minimal source impact — the production-grade answer. Triggers write change rows on DML — reliable but they add write latency and DBAs resist them. Snapshot diff (full extract plus hash compare) is the fallback when the source offers nothing: expensive but complete, including deletes.

Idempotent means rerunning the same run or window produces the same end state — no duplicates, no double counting. Standard techniques: delete-then-insert or overwrite scoped to the partition (usually a date), MERGE/upsert on business keys, or run-scoped keys with dedupe on load. It matters because retries and backfills are inevitable; a non-idempotent pipeline turns every recovery into a data-corruption incident.

Compare incoming rows to the current dimension version, typically via a hash of the tracked attributes. Unchanged rows are skipped; changed rows expire the current version (set end_date, is_current = false) and insert a new version with a fresh surrogate key and effective_date; new keys are plain inserts. In SQL this is a MERGE — often two-step UPDATE plus INSERT, since MERGE cannot perform both actions on one matched row; in Delta Lake it is MERGE with a union trick for the insert leg.

Source-to-target count and reconciliation testing (counts, sums, hash or checksum comparison), transformation-logic testing against mapping documents, duplicate and null checks on keys, incremental-load testing (only the delta moved, and reruns don't duplicate), SCD/history testing, regression testing after changes, and performance testing against the load window. Always name a method or tool: MINUS/EXCEPT queries, QuerySurge, dbt tests, or Great Expectations suites.

Classify errors first. Transient failures (network, deadlock, throttling) get automatic retry with capped exponential backoff. Data errors (bad rows, type violations) shouldn't fail the batch — route them to a reject or dead-letter table with reason codes and load the rest, with a threshold that fails the run if the reject percentage spikes. Structural errors (schema change, missing file) fail fast and alert, because retries cannot fix them; every failure path emits an alert with run context.

The prerequisite is idempotency — partition overwrite or MERGE. Parameterize the pipeline by date window rather than "now", run backfill windows with controlled parallelism so you don't crush the source or the warehouse, ideally via a separate backfill job so daily SLAs aren't blocked, and bypass or restore watermarks deliberately. Reconcile each window after it lands and tell consumers in advance, because historical numbers will visibly change.

Use a layered approach: row counts per partition, then SUM/MIN/MAX on numeric and date columns, then aggregate hash comparison — MD5 or HASHBYTES over concatenated normalized columns, grouped by key ranges to localize mismatches — and only then row-level EXCEPT/MINUS on the ranges that differ. Counts alone will pass even when rows are corrupted in place, which is exactly why interviewers push past them.

In order of preference: enable log-based CDC (SQL Server CDC or Change Tracking, Debezium) — the right fix, but it needs DBA buy-in; hash-diff — stage current keys plus a row-hash, compare against the previous snapshot's hashes to derive inserts, updates, and deletes, which reads everything but moves only the delta; or partition-scoped reloads if the data is append-mostly by date. State the tradeoff explicitly: hash-diff trades source read cost for full correctness, including deletes.

Most movement layers give at-least-once — retries can redeliver the same data. True exactly-once across heterogeneous systems is impractical, so you achieve effective exactly-once by pairing at-least-once delivery with an idempotent sink: MERGE on business keys, partition overwrite, or transactional writes that record offsets or run IDs (Kafka offsets committed atomically with the write, Delta transaction IDs). The pattern name is the interview answer: at-least-once delivery plus an idempotent consumer.

For aggregates, reprocess a sliding lookback window — recompute the last N days each run with partition overwrite so late rows land correctly — and watermark on event time, not load time. For SCD2, a late change dated before the current version means inserting a version into history and adjusting adjacent effective and end dates, which is expensive; many teams accept load-time ordering and document the limitation. In streaming terms: watermarks with allowed lateness, and truly late data routed to a correction path.

Work backwards through lineage: define what "wrong" means with a reconciliation query against source for the affected slice, then check the usual suspects — duplicate loads from a rerun without idempotency, timezone or partition-boundary bugs (IST vs UTC cutoffs are a classic in India-based teams), late data that arrived past the watermark, silently changed source semantics, and join fanout or filter changes in the transform. Fix it, then add the blocking data-quality check that would have caught it — interviewers listen for that closing move.