ETL / ELT interview questions
ETL / ELT questions a Data engineer can be asked, from first job to senior. Open one to see the strongest answer and why it works.
When do you load first and transform in the warehouse (ELT) instead of a heavy ETL box?
The strongest answer: When the warehouse can do the transforms cheaper and we want raw landings we can replay — I do not ETL by habit because “that is what the old tool did”
Why it works: Exactly. ELT is a replay and cost story. Free is not a reason; skipping checks is a red flag.
- ELT when you want replayable raw.
- Use the warehouse if it is the cheaper computer.
- Not because it is “free.”
- Do not skip checks.
A daily load ran twice. What must still be true?
The strongest answer: The table matches one run — merge, replace a partition, or exactly-once keys — I do not append blindly and call it “more complete”
Why it works: Exactly. Idempotent loads. More rows are not more truth. Disabling retries just fails the day.
- One run or two = same table.
- Replace a partition or merge on keys.
- Do not blind-append.
- Do not turn off retries instead.
Events from Sunday arrive Tuesday. What do you not do?
The strongest answer: I do not publish Sunday as final on Monday and walk away — I restate or I label as-of
Why it works: Exactly. Late is normal. Dropping and pretending are how numbers lie. Asking the source is a wish, not a pipeline.
- Restate or label as-of.
- Do not drop late as policy.
- Event time ≠ ingest time.
- Do not wish lateness away.
A load of a million rows has forty rows whose date does not parse. What do you do with them?
The strongest answer: Route the forty to a quarantine table with the reason, load the rest and alert — unless the contract says any bad row fails the load. I never silently drop or coerce them
Why it works: Exactly — bad rows are data about the source; keep them visible, keep the good rows moving, and make the choice explicit in the contract.
- Quarantine, do not drop.
- Keep bad rows with the reason.
- Alert on the quarantine count.
- Fail-or-quarantine is a contract decision.
When is MERGE / upsert the right load?
The strongest answer: When the same business key can arrive again — I do not append a second truth for the same order
Why it works: Exactly. Keys that retry need a merge. Always/never is ideology. Hiding deletes is a different (named) policy.
- Upsert on retryable keys.
- Append immutable events.
- Do not ideology either verb.
- Name how deletes appear.
What is CDC for, versus a nightly dump?
The strongest answer: To land changes as they happen, with less load on the source — it is not “dump but streaming” if you still full-scan every hour
Why it works: Exactly. Change events, lighter source. CDC still has late and out-of-order. It is not a backup product.
- CDC = changes, lighter source.
- Not a backup.
- Not a skip-the-key card.
- Still handle late/out-of-order.
You need to reload 90 days. What do you not do?
The strongest answer: I do not run the daily DAG 90 times by hand in the UI without a backfill tool and a note to consumers
Why it works: Exactly. Backfills are launches. Hands, hope, rush-hour, and skip-tests are how you page the company.
- Use a real backfill path.
- Tell consumers.
- Do not drop-and-hope.
- Do not skip tests on history.
An incremental load pulls rows where updated_at is greater than the last run’s maximum. Where does it lose data?
The strongest answer: Rows committed with an updated_at earlier than the watermark — long transactions, clock skew, backdated updates — and rows sharing the exact watermark value; I overlap the window and de-duplicate on the key
Why it works: Right — a watermark is only as trustworthy as the source’s clock and commit order; a lookback window plus MERGE on the key is the standard cure.
- Watermarks miss late commits.
- Overlap the window; de-duplicate on key.
- Deletes need CDC or flags.
- Do not rely on a later refresh.
A backfill and the daily job write the same partition. What do you require?
The strongest answer: Isolation — separate slots or a lock on the partition, and a single writer story — I do not let two merges race
Why it works: Exactly. One writer, or a designed merge. Last-write-wins folklore and forgotten disables are outages.
- One writer per partition.
- Lock or separate targets.
- No silent last-wins.
- Do not rely on memory to pause daily.
A new optional column appears in JSON events. How do you evolve the table?
The strongest answer: Additive, documented, with a default or NULL and a contract note — I do not auto-widen into PII and I do not break readers for a rename without a version
Why it works: Exactly. Additive is cheap; rename is a version. Auto-widen is how secrets land. Fail-forever with no path is how people fork.
- Additive + documented.
- Watch PII on auto-widen.
- Renames are versions.
- Give a path to add columns.
Log-based CDC (reading the database’s transaction log) versus polling a table for changes — what does each cost you?
The strongest answer: Log-based sees every change, including deletes and intermediate states, with little load on the source, but needs log access and connector operations; polling is simple but misses deletes and twice-changed rows and taxes the source
Why it works: Exactly — completeness and source load are the two axes; freshness is a consequence, not the whole story.
- Log CDC: complete, light on source.
- Polling: simple, misses deletes.
- Twice-changed rows collapse under polling.
- Never tax the primary without the DBA.
When is an incremental model wrong?
The strongest answer: When the logic must restated over a moving window you cannot bound, or when late keys mutate too far back — I do not incremental a lie to save a slot
Why it works: Exactly. Incremental is a contract on what can change. Fashion on a tiny table is complexity. No unique key is a yes-it-is-wrong.
- Bound what can change.
- Full-refresh when restatement is wide.
- Do not incremental a tiny table for fashion.
- Need a unique key.
What does a late-data policy contain?
The strongest answer: How long we restated, what we drop after, and who we tell — a vibe that “we handle late” is not a policy
Why it works: Exactly. Window, drop, notify. Never and Slack are not policies.
- Restate window.
- Drop rule after.
- Notify consumers.
- Write it down.
Your CDC feed lands insert, update and delete events in the lake. A customer deleted in the source is still present downstream, and another shows an older address than the source.
What did the apply step get wrong?
The strongest answer: Events were applied by arrival, not source order, and deletes were ignored: apply per key in log-sequence order and treat a delete as a tombstone that removes or flags the row
Why it works: Right — ordering by the source’s sequence and honouring tombstones is what makes a change feed reproduce the source.
- Apply in source order; honour deletes.
- Order by log sequence, not arrival.
- Deletes are tombstones.
- Never de-duplicate on arrival time.
A mapping table lives in a CSV in the repo. When is that right?
The strongest answer: When it is small, owned, and versioned with the model — I do not seed a million-row dimension or a secret
Why it works: Exactly. Seeds are code-sized. Git-as-warehouse, never-seeds, and force-pushes are the failure modes.
- Small, owned, versioned seeds.
- No million-row CSVs.
- No secrets in seeds.
- No force-push to hide.
What do you listen for when hiring a senior data engineer?
The strongest answer: How they made a load idempotent and how they said no to a toy stream — not a trivia list of Spark configs
Why it works: Exactly. Judgement. Volume, vendors, and night-heroics are smells.
- Listen for idempotence and a grown-up no.
- Skip config trivia.
- Do not hire a vendor.
- Do not hire a night martyr.
A Sev-1 used a doubled partition. How do you learn?
The strongest answer: Which idempotence test would have failed, and one road change — no hunt for who clicked Run
Why it works: Exactly. Missing test, one change. Hunts and oceans teach fear.
- Fail the idempotence test next time.
- One owned road change.
- No last-clicker hunt.
- No Friday ocean.
Two hundred ingestion pipelines: a managed ingestion product for all of them, or connectors your team writes?
The strongest answer: Buy the long tail of standard sources and own the few high-volume or unusual ones; decide on engineering time, per-row pricing at our volumes, and whether the tool meets our freshness and schema-change needs
Why it works: Exactly — a portfolio decision priced against volume and constraints, not a slogan either way.
- Buy the tail, own the peaks.
- Price the tool at your volumes.
- Check freshness and schema-change handling.
- A bought tool still needs an owner.
What does day-one ingest look like on the paved road?
The strongest answer: A template: landing, schema, PII class, tests, and a DAG — the squad adds the source, they do not invent a fourth loader
Why it works: Exactly. New sources start on the road. Blank buckets and 2019 copies are how dialects multiply.
- Ship an ingest template.
- Squads add sources, not loaders.
- Do not copy 2019 dumps.
- A vendor login is not enough.