Data Engineering Interview Q&A: Idempotent Loads, Retries and Late Updates
Practise batch replay and late-update questions with a BigQuery MERGE pattern and clear data contracts.
1. What makes a batch load idempotent?
Repeating the same logical batch should leave the intended data state unchanged. A successful retry must not add the same business event twice. A job returning success is not sufficient evidence: compare keys, counts and values after replaying the batch.
Suppose a nightly sales file contains invoice I1 with value 120 and invoice I2 with value 80. If the job commits and then loses its acknowledgement, a scheduler may retry. An append-only implementation can produce four rows and total 400 instead of two rows and total 200. The interview answer should cover this failure point explicitly.
2. Is a batch ID enough?
A batch ledger helps track receipt and completion, but it cannot repair an inconsistent write by itself. Decide what identifies a business record, how updates are ordered, and whether the data write and completion record share a safe commit boundary. Also define whether a corrected file has a new version. Treating every file name as permanently unique can hide corrections.
3. What does MERGE solve in BigQuery?
MERGE can combine insert and update decisions in a single statement. It still needs an unambiguous source and a correct matching condition. The following is a teaching pattern for already-existing practice tables, not a command to run against production.
MERGE `training.curated.invoices` AS t
USING `training.staging.latest_invoices` AS s
ON t.invoice_id = s.invoice_id
WHEN MATCHED AND s.event_seq > t.event_seq THEN
UPDATE SET amount = s.amount, event_seq = s.event_seq
WHEN NOT MATCHED THEN
INSERT (invoice_id, amount, event_seq)
VALUES (s.invoice_id, s.amount, s.event_seq);The staging relation must contain at most one selected source row per invoice. The target must also follow the intended key rule. BigQuery does not magically deduplicate malformed staging data for this pattern. Validate both assumptions before relying on replay behaviour.
4. How would you select the latest input row?
Use an ordering value with a documented meaning, not simply the time a file arrived. For practice input I1 sequence 1 amount 100, I1 sequence 2 amount 120, and I2 sequence 1 amount 80, the selected rows are I1/2/120 and I2/1/80. Define how equal sequence values with conflicting amounts are rejected or resolved; arbitrary tie-breaking can silently change outcomes.
5. What happens when an older update arrives late?
The comparison in this pattern prevents a lower sequence from overwriting a newer target row. An equal sequence is left unchanged. That policy is correct only if sequence ordering is trustworthy and equal versions cannot legitimately contain corrections. If that assumption fails, redesign the version contract rather than adding blind retries.
Practice interview follow-up
Simulate target I1/2/120 and I2/1/80, then replay the same selected input: the total remains 200. Send I1/1/100 late: it remains 200. Send I1/3/130: it becomes 210. Describe what you would log when a source has two contradictory sequence-3 records. The state-transition arithmetic was checked locally; the BigQuery statement was reviewed against official documentation but not executed in a cloud account.
Prepared by AI Leaf Global Learn for learners in Mudichur/Tambaram, Chennai, and online across India. Verify current classroom and online batches.
GCP Data Engineering course.
Official BigQuery DML reference.