SQL · hard

A daily batch job loads into a target table. Make re-running it for the same day safe, with no duplicates.

Asked in Data Engineer interviews, in the SQL round.

Short answer

MERGE on a stable business key, or delete-then-insert scoped to that day's partition inside one transaction. The job has to be idempotent rather than append-only: keyed on something like (entity_id, load_date) so a re-run replaces its own previous output instead of stacking on top of it. INSERT-only pipelines look correct until the first retry, and then every downstream metric double-counts.

How to answer it

Definition

Make the job idempotent: running it once and running it five times leave the target in the same state. This is a property of the load, not something added afterwards by cleaning up duplicates.

Two ways

MERGE on a stable business key:

MERGE INTO target t
USING staging s
   ON t.business_key = s.business_key
WHEN MATCHED THEN UPDATE SET amount = s.amount, updated_at = s.updated_at
WHEN NOT MATCHED THEN INSERT (business_key, amount, updated_at)
                     VALUES (s.business_key, s.amount, s.updated_at);

Delete then insert, scoped to the partition being rebuilt, in one transaction:

BEGIN;
DELETE FROM target WHERE load_date = DATE '2025-03-04';
INSERT INTO target SELECT * FROM staging WHERE load_date = DATE '2025-03-04';
COMMIT;

Which to choose

Delete-and-insert is simpler and usually faster on a partitioned warehouse table, because it rewrites one partition. MERGE is the right call when rows arrive late and belong to older partitions, or when the target holds state that must be updated rather than replaced.

Key points

• Idempotency comes from a stable business key. Without one, the job cannot tell a rerun from new data.
• Scope the delete to the partition. An unscoped delete is a very fast way to lose the table.
• Wrap it in one transaction, so a failure halfway leaves the old data rather than none.
• A surrogate key generated at load time is not stable. It changes on every run and defeats the whole thing.
• Prove it: run twice, assert the row count and a checksum are unchanged.

FAQ

• Why not INSERT ... ON CONFLICT DO NOTHING? It prevents duplicates but never corrects a row that was restated upstream.
• What about deduplicating afterwards instead? That is cleanup, not idempotency, and it hides the bug rather than fixing it.
• Does an append-only raw layer conflict with this? No. Raw stays append-only and immutable; the modeled table is the idempotent one.

The 30-second answer

I would make the load idempotent rather than cleaning up afterwards. Either a MERGE on a stable business key, so a rerun updates the row it already wrote, or a delete then insert scoped to that day's partition inside a single transaction.

The word I keep coming back to is stable. The key has to come from the source data, because a surrogate key generated at load time differs on every run and defeats the idea. I would scope the delete to the partition rather than the table, keep it in one transaction so a half-finished run does not leave the day empty, and prove it by running twice and checking the row count and a checksum have not moved.

In the interview

• What they are checking: whether you say the word idempotent, and whether the key you choose is stable across runs.
• Common mistake: INSERT only. It looks correct until the first retry, then every downstream metric double-counts that day.

Practice this for real

• Check your resume against a Data Engineer job description, free, no account needed.
• Every data interview question, by role and topic
• Scored AI mock interviews, two free, no card