ETL & ELT Interview Questions cheat sheet
Batch versus streaming, incremental loads, idempotency, data quality, and pipeline design fundamentals every data interview covers.
ETL versus ELT fundamentals
What is the difference between ETL and ELT?- ETL transforms data on a separate processing tier before loading, so only modelled data lands in the target. ELT loads raw data first and transforms inside the warehouse using its own compute. ELT became the default because cloud warehouse compute is elastic and cheap, and keeping raw data means you can rebuild a transformation without re-extracting from the source.
When is ETL still the right choice?- When data must be masked or tokenized before it can legally land in the warehouse, when the target cannot handle the transformation cost, when you are integrating many small sources into a fixed API contract, or when the source is a file format the warehouse cannot read. Compliance is the most common honest answer.
What are the stages of a typical data pipeline?- Extract from sources, land raw data in durable storage, validate and clean, transform into conformed models, serve to consumers, and monitor throughout. The layering convention many teams use is bronze for raw, silver for cleaned and conformed, gold for business-facing. Keeping the raw landing zone immutable is what makes every later stage replayable.
What is the difference between a data lake, a warehouse, and a lakehouse?- A lake stores raw files of any format cheaply with schema applied on read; a warehouse stores modelled, typed tables optimized for SQL analytics; a lakehouse puts a transactional metadata layer such as Iceberg or Delta over lake files to get warehouse guarantees on open storage. The lakehouse pitch is one copy of data instead of a lake that feeds a warehouse.
What does schema-on-read versus schema-on-write mean?- Schema-on-write validates and rejects bad data at load time, guaranteeing that everything in the table conforms. Schema-on-read accepts anything and interprets it at query time, which never blocks ingestion but pushes surprises onto consumers. Most modern pipelines land schema-on-read in bronze and enforce schema-on-write at the silver boundary.
What is reverse ETL?- Pushing modelled data from the warehouse back into operational tools such as a CRM, ad platform, or support system, so the business logic defined once in the warehouse drives operational systems. The engineering concerns are API rate limits, per-record error handling, and idempotency, because these targets rarely support transactional bulk loads.
Extraction and ingestion
What is the difference between a full load and an incremental load?- A full load replaces the entire dataset each run — simple, self-correcting, and viable while the table is small. An incremental load moves only new or changed rows using a watermark or change feed, which is far cheaper but accumulates drift and can miss late or deleted records. Many teams run incremental daily with a periodic full reconciliation.
What is change data capture and why prefer it over polling?- CDC reads the database transaction log to emit every insert, update, and delete in order, with minimal load on the source. Polling with a query on updated_at misses hard deletes, misses intermediate values between polls, and puts scan load on a production database. The trade-off is operational complexity — CDC needs log access, schema change handling, and snapshot bootstrapping.
How do you pick a watermark column?- It must be monotonically non-decreasing, set by the database rather than the application clock, and updated on every change including soft deletes. A created_at column misses updates; an application-set updated_at drifts across servers. Whatever you pick, add a lookback overlap so rows committed slightly out of order are still captured.
How do you extract from an API with pagination and rate limits?- Follow cursor-based pagination rather than offsets, since offsets shift as data changes; respect the Retry-After header with exponential backoff and jitter; checkpoint the cursor so a failure resumes rather than restarting; and cap concurrency to the documented quota. Also record the raw response payloads, so a parsing bug is fixable without re-calling the API.
What is the difference between batch, micro-batch, and streaming?- Batch processes a bounded set on a schedule, typically hourly or daily. Micro-batch runs the same model on small frequent intervals of seconds to minutes, which is how Spark Structured Streaming works. True streaming processes each event as it arrives with sub-second latency. Cost and operational complexity rise sharply as latency falls, so the requirement should justify it.
Loading and idempotency
What does idempotency mean for a pipeline and how do you achieve it?- Running the same task for the same input window twice produces the same result as running it once. Achieve it by writing to a deterministic destination — overwrite a specific partition, or MERGE on a business key — rather than appending. Every retry, backfill, and manual rerun depends on this, which is why it is the most asked design question.
Explain at-most-once, at-least-once, and exactly-once.- At-most-once may lose data and never duplicates; at-least-once never loses and may duplicate; exactly-once is the guarantee that each record affects the result once. In practice exactly-once is achieved as at-least-once delivery plus an idempotent sink, not as magic in the transport, and a strong candidate says so.
What is an upsert and how does MERGE implement it?- An upsert updates a row if the key exists and inserts it otherwise. MERGE does this in one statement with matched and not-matched branches, and can also delete. The trap is a source with duplicate keys, which most engines reject or resolve nondeterministically, so deduplicate to one row per key by recency before merging.
How do you handle deletes from a source system?- Hard deletes are invisible to timestamp-based incremental loads. Capture them with CDC delete events and apply them as soft deletes with a deleted_at column so history survives, or, if the source only offers snapshots, diff the full key set per load. Deciding whether downstream models filter deleted rows is a modelling decision you should raise explicitly.
How do you backfill a pipeline without breaking production?- Confirm idempotency, run into a separate target or partition set first and validate row counts and key metrics against a known-good period, throttle concurrency so the backfill does not starve scheduled runs, and process in chunks so a failure loses one chunk. Then swap or publish atomically rather than trickling rows into the live table.
Data quality and reliability
What checks belong in every pipeline?- Row count within an expected band, uniqueness on the primary key, not-null on required columns, referential integrity to parent tables, freshness against an expected arrival time, and a reconciliation of a key total against the source. Zero rows should fail loudly, because an empty successful load is the failure that gets noticed a week later.
What is the difference between data quality testing and observability?- Testing asserts known rules you wrote in advance and fails the run. Observability monitors metrics such as volume, freshness, schema, and distribution, and alerts on anomalies you did not anticipate. You need both: tests catch the errors you predicted, observability catches the ones you did not, such as a source silently changing units.
How do you handle bad records without stopping the pipeline?- Route them to a quarantine table or dead letter location with the raw payload and the reason, let good records proceed, and alert on quarantine volume crossing a threshold. Failing the entire load on one malformed row blocks good data; silently dropping it loses data. Quarantine is the answer that shows you have run this in production.
What are the data quality dimensions?- Completeness, accuracy, consistency, timeliness, validity, and uniqueness. The useful interview move is to tie each to a concrete check: completeness to row counts and nulls, timeliness to freshness SLAs, validity to type and range constraints, consistency to cross-system reconciliation. Reciting the list without checks reads as textbook knowledge.
What is a data contract?- An explicit agreement between a producer and consumer covering schema, semantics, freshness, and change policy, ideally enforced in the producer's CI so a breaking change fails before deployment. It shifts breakage detection left, from the analyst noticing a broken dashboard to the engineer failing a build. It only works with real ownership behind it.
What are SLAs, SLOs, and SLIs for data?- An SLI is the measured indicator, such as the hour a table is refreshed by. An SLO is the internal target, for example 99% of days by 07:00. An SLA is the commitment to a consumer, usually with consequences. Defining freshness and completeness targets per table is what makes on-call decisions objective instead of a judgment call at 3 a.m.
Pipeline design and operations
How do you decide the schedule for a pipeline?- Work backwards from the consumer's decision cadence and the source's availability. A dashboard reviewed each morning needs an overnight run finishing before people arrive, not hourly refreshes. Then account for upstream arrival variance and add a buffer, since a job scheduled at the source's median arrival time fails roughly half the time.
What is orchestration and why not just use cron?- Orchestration adds dependency graphs, retries with backoff, backfills over historical intervals, observability, and parameter passing. Cron gives none of that, so a failed upstream job leads downstream ones to run on stale data and succeed. Cron is fine for one independent script; the moment there are two dependent steps, you need real orchestration.
How do you make a pipeline observable?- Emit structured logs with the run ID and partition, record row counts in and out at every stage into a metadata table, expose duration and freshness as metrics, and alert on both failure and abnormal success — a run that finished in 10 seconds instead of 10 minutes moved no data. Lineage ties an incident to affected downstream assets.
What causes pipeline failures most often in practice?- Upstream schema changes, late or missing source data, credential and token expiry, resource limits such as memory or quota, and duplicate or malformed records. Almost none are transformation logic bugs, which is why experienced engineers spend defensive effort on the boundaries — validation on ingest and contracts with producers — rather than on the SQL.
How would you migrate a legacy ETL system to a modern stack?- Inventory jobs and their consumers, run the new pipeline in parallel writing to a shadow target, reconcile outputs row by row and metric by metric until they match for a full cycle, then cut consumers over one at a time and decommission. Big-bang cutovers fail because undocumented behavior in the old system is only discovered by comparison.
What would you do if a stakeholder says the numbers look wrong?- Reproduce their exact query and time window first, then check freshness and the last successful run, then reconcile against the source for a small sample rather than debating aggregates. Communicate a status early even without a root cause. Most of these turn out to be a definition mismatch rather than a pipeline bug, which is an argument for a semantic layer.
From DataLane — tutorials at/blog, practice SQL live in theplayground.