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.

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.

Green only means the code ran without throwing, and row counts are the weakest check there is because they survive any corruption that does not change cardinality. A value-level gap on matching counts points at a handful of suspects: a join fanout later collapsed by a DISTINCT, a unit or currency mismatch (paise loaded as rupees, a new currency code defaulting to INR), rows landing in the wrong day because the source stamps IST and the pipeline buckets on UTC, a numeric cast losing precision, or a filter dropping NULLs silently. Debug by narrowing, not by reading code: compare source and target SUMs by day, then by region or channel, until the delta collapses into one slice, then diff those rows. Close by adding the sum and hash reconciliation test that counts could never give you.

Split drift into additive and breaking, and automate only the additive half. A new nullable column or a new enum value should be absorbed: land raw with a schema-evolving format (Delta or Iceberg mergeSchema, or raw JSON projected in a staging model), log the change, move on. Breaking changes — a dropped or renamed column, a narrowed type, a semantic change like an amount switching units — should fail loudly and quarantine the batch rather than write NULLs into a fact table. What breaks is a lineage question. Table-level lineage tells you which models and dashboards touch the object; column-level lineage tells you whether the changed column is actually used, which is the difference between notifying two teams and notifying forty. dbt's DAG plus exposures, OpenLineage, or Purview and Collibra in enterprises give you that map.

A data contract is a versioned, explicit agreement between the producing system and the consuming pipeline covering schema and semantics: field names and types, nullability, the primary key and grain, allowed enum values, freshness and volume SLA, PII classification, and a deprecation notice period. Enforcement is the whole point. The contract lives beside the producer's code — a schema file in their repo, or an Avro/Protobuf entry in a schema registry for Kafka topics — and their CI fails when a change violates it, backed by a runtime check that quarantines non-conforming payloads instead of loading them. The hard part is organisational: in most Indian setups the producer is another product team or a vendor with its own roadmap, so contracts only stick when one team owns the platform SLA and the contract is what they point at.

dbt is a transformation framework, not a data-movement tool: it takes SQL you already write and adds Jinja templating, dependency resolution through ref(), version control, tests, and documentation, then executes all of it inside the warehouse. It is the T in ELT — something else lands raw data (Fivetran, ADF, Airbyte, a Spark job), dbt builds staging, intermediate, and mart models on top, and an orchestrator triggers dbt run. What earns credit in interviews is the boundary: models are just SELECTs materialised as views, tables, incrementals, or ephemeral CTEs, snapshots give you SCD Type 2 without hand-written MERGE, and tests are assertions you can run in CI. Saying dbt processes data is the tell that you have only read about it — the warehouse does every bit of the compute.

Because the two stacks optimise for different things and the older estates still run the business. GUI tools give you drag-and-drop mappings, a wide connector library, a vendor support contract, and a large pool of trained developers — exactly what a bank or insurer with a decade of Informatica mappings and an audit history wants. Code-first stacks give you Git, code review, tests, CI/CD, metadata-driven pipelines generated in a loop, and no per-core licence — what a product company or GCC building something new wants. Services firms therefore staff both: legacy maintenance and migration on one side, greenfield Airflow, dbt, and Spark on the other. Say it plainly: the concepts do not change — CDC, SCD, idempotency, reconciliation are identical — so you pick the tool the team that gets paged at 2am can actually run.

ADF is Azure's orchestration and data-movement service — the cloud successor to SSIS for scheduling and copying, not an engine you write heavy transformation logic in. Five concepts carry most rounds. Linked services are the connection: endpoint plus credentials, ideally from Key Vault. Datasets describe the shape and location of data at that connection. Pipelines hold activities: Copy, Lookup, ForEach, Execute Pipeline, Stored Procedure. Integration runtimes are the compute doing the movement — Azure IR for cloud-to-cloud, self-hosted IR when the source sits behind an on-prem firewall, SSIS IR to lift existing packages. Triggers start it all: schedule, event-based on blob arrival, and tumbling window, which is the one that gives you per-window retry and clean backfills. Real transformation belongs in Databricks, Synapse, or SQL; mapping data flows run Spark underneath and are priced accordingly.

Default to a managed warehouse unless you have a reason not to: Snowflake, BigQuery, or Databricks SQL, a managed connector for standard SaaS sources, dbt for transforms, one orchestrator, one BI tool. A mid-size team's binding constraint is people, not technology — three engineers cannot operate self-hosted Kafka, Spark, and Airflow as well as they can operate SQL. Choose a lakehouse on Delta or Iceberg when you have high-volume semi-structured data, ML workloads reading the same tables, or a cost profile that hates warehouse compute. ETL then differs in the mechanics: an update is a MERGE that rewrites data files rather than rows, so you schedule compaction and vacuum or the small-files problem eats your read performance; schema evolution is a table property; and time travel makes verifying a backfill genuinely easy.

Treat it as entity resolution plus survivorship, and make both explicit and auditable. Standardise before matching — lowercase and trim emails, normalise Indian phone numbers to a +91 form with the leading zero stripped, uppercase and validate PAN or GSTIN, tokenise addresses — because a lot of apparent duplicates are only formatting. Then match in tiers: deterministic on strong identifiers first (GSTIN, PAN, verified email or phone), then fuzzy on name plus address with a scored threshold, and route the grey band to a human review queue instead of auto-merging. Survivorship is a documented rule per attribute, not per record: billing wins legal name and address, CRM wins owner and segment, support wins last contact channel, most-recent non-null otherwise. Keep a crosswalk of source keys to master ID so any merge can be explained and reversed.

Measure first — get per-stage timings, because the time is almost always concentrated in one stage and a candidate who opens with add more cluster fails this question. Then work in order of leverage. Stop moving data you do not need: incremental instead of full extract, column pruning, predicate pushdown to the source. Fix the shape of the work: file sizes and partitioning, skew on a hot key, a broadcast join instead of a shuffle, set-based SQL instead of row-by-row cursors or per-row API lookups. Parallelise branches that were serial for no reason. Only then buy compute for that window. Also challenge the requirement — usually one report needs the 2am number and the rest can wait, so splitting the DAG into a critical path and a long tail delivers the SLA without touching the slow part.

Design for the API being unavailable and the run being resumed, not for the happy path. Stay under the documented limit with a token bucket or a fixed delay, and honour the Retry-After header on a 429 instead of guessing. Retry with exponential backoff plus jitter on 429s, 5xx, and timeouts, never on 4xx client errors, with a capped attempt count. Set connect and read timeouts on every call — an un-timed-out socket is how a twenty-minute job becomes a job that hangs for days without anyone noticing. Page with a cursor and checkpoint that cursor after each successful page so a resume does not start from zero, and land raw responses to object storage before parsing so you can replay without re-calling. Finally MERGE on the provider's own record id, because retries will redeliver rows.

Run both stacks in parallel and cut over report by report — never big-bang. Inventory first: packages, their real sources, their actual schedules, and who consumes the output, because estates like this always contain packages feeding reports nobody opens, and deleting those is the cheapest migration you will ever do. Then decide lift-and-shift versus rewrite per package: SSIS integration runtime inside ADF moves you fast with the technical debt intact, while rewriting into ADF plus dbt or Databricks is slower but is the reason you are migrating. Dual-run next — old pipelines keep feeding today's reports while the new ones write to a parallel schema, reconciled daily on counts, sums, and hashes until they agree through a full cycle including month-end close. Repoint BI one report at a time, keep the old path warm for rollback, then decommission.

Two layers. Pipeline health: freshness of the newest row against its SLA, success rate, run duration versus its normal distribution, rows in versus rows out, retry counts, and cost per run — duration drift is the metric that warns you before an incident rather than after. Data quality: the standard dimensions expressed as tests — completeness (nulls, missing partitions), uniqueness on keys, validity (ranges, enums, formats), consistency (target totals reconcile to source), accuracy against a trusted reference, and timeliness. Fatigue is an ownership problem, not a tooling one. Give every check a severity, page a human only for blocking failures on tables that feed money or a regulator, send the rest to a channel or dashboard, alert on the SLA being missed rather than on every retry, and delete any alert nobody has acted on in a month.

Classify at ingestion and carry the classification along the lineage, because you cannot protect or erase what you cannot locate. Tag PII columns in the catalog, encrypt in transit and at rest, mask or tokenise in non-production instead of copying a raw prod dump into dev, and give analysts masked views rather than blanket table access. Minimise first — the safest PII is the field you never copied. Erasure is the hard engineering part, because warehouses are append-only by design: you need a person-to-key map, deletion propagated to every downstream copy including exports and backups, real removal from the files (a MERGE delete plus VACUUM on Delta or Iceberg, not a soft flag), and an audit record proving it happened inside the SLA. Crypto-shredding — tokenise identifiers, destroy the key — plus a documented retention window is the usual pattern.