Lesson 1 of 4 · 25 min
Reconstruct latest state from a dirty change table
Write deterministic latest-state logic with deletions.
Mechanism and reasoning
A latest-state exercise tests more than a window-function syntax trick. Ask what identifies an entity, which field orders changes, how ties are resolved and how deletes appear. Without those rules, several SQL answers can be syntactically valid and semantically different.
Rank all changes before applying the final deletion filter. If you remove delete records first, the query can resurrect the most recent old value. A tombstoned entity should remain absent from the current view. Keep raw history separately if audit or replay requires it.
Use a deterministic tie-breaker only when it has an authorized meaning. A larger ingestion ID may make the query stable, but it does not automatically make the selected value correct. If the source contract says versions are unique per entity, conflicting equal versions should be flagged. Do not bury a producer defect inside arbitrary ordering.
SQL engines have different syntax conveniences. A portable approach uses a common table expression, ROW_NUMBER and an outer filter. Explain whether null keys or versions are allowed. If they are invalid, quarantine them before building current state and report the rejected count.
The interview deliverable is a small result table plus a query and edge-case tests. Run the reasoning on a late update, a repeated event and a delete. Then explain how it becomes incremental without changing semantics. An incremental filter based only on event time can miss a late correction, so ingestion position or a controlled lookback may be needed.
Produce an answer and a test fixture together
The interviewer should be able to check your result without running a large system. Write a compact fixture with the expected current view, then derive the query. Include one entity that is deleted, one with an out-of-order update, and one that is recreated after deletion. State whether recreation continues the same monotonic version sequence or starts a new incarnation identifier.
| Entity | Version | Operation | Name | Expected effect |
|---|---|---|---|---|
| a | 1 | upsert | Ada | Superseded |
| a | 2 | delete | null | Current absence |
| b | 3 | upsert | Bea | Current value |
| b | 2 | upsert | Ben | Stale arrival |
| c | 4 | delete | null | Superseded by recreation |
| c | 5 | upsert | Cy | Current value |
The expected current rows are b/Bea and c/Cy. This assumes that version five is a legitimate recreation under the same entity's authoritative sequence. If the source reuses IDs while restarting versions, entity ID plus version is insufficient. Introduce an incarnation or another source-defined ordering contract. A query cannot infer that missing business rule.
Validation precedes ranking. A row with a null version cannot be placed reliably in the authoritative sequence. Two different payloads for the same entity and version violate the stated unique-version contract. Return these conflicts rather than adding an arbitrary field to ORDER BY and calling the answer correct. A stable tie-breaker can make repeated executions agree while hiding contradictory source claims.
sql
1-- Teaching precondition check; exact duplicate ingestion is handled separately.2SELECT user_id, version, COUNT(*) AS copies3FROM deduplicated_changes4GROUP BY user_id, version5HAVING user_id IS NULL6 OR version IS NULL7 OR COUNT(*) > 1;This check assumes exact event redeliveries were removed by their stable event identity. If deduplicated_changes still contains equivalent source records with different event IDs, the producer contract must define whether they are valid or conflicting. The query deliberately exposes the ambiguity instead of choosing a winner from arrival order.
An incremental implementation must compare new state with existing state. Ranking only the latest input batch is not enough: a batch containing version two must not replace stored version three. For each entity, select the highest valid candidate in the new batch and update the sink only when that candidate is newer than the stored authoritative version. Equal versions require equivalence or conflict handling; lower versions can be retained in history without changing current state.
Deletes create a storage nuance. If the public current-state table omits deleted entities, a separate allowed tombstone or version ledger may be needed for incremental comparisons. Otherwise, a stale lower-version update can appear to be a brand-new entity after the current row is physically removed. Keep the minimum ordering metadata permitted by retention and privacy policy, or use a source/sink protocol that provides an equivalent guard.
Selecting new input by event time alone can miss late arrivals. A record that occurred last week may enter raw storage today. Track ingestion progress or source positions for incremental discovery, then use business versions for state selection. A bounded event-time lookback can be useful when its completeness assumptions are explicit, but it does not guarantee inclusion of arbitrarily late corrections.
Finally, verify a full recomputation against the incremental result on the same fixed input. Permute arrival order, repeat events and split batches at awkward boundaries such as immediately before a deletion. Both methods should produce the same current keys and versions under the contract. This comparison turns a SQL interview answer into an implementation argument: the candidate understands how the query behaves when the data no longer arrives as a neat static table.
Worked example
Teaching input has user u1 at version one active, version three deleted and version two active arriving last. User u2 has version one active. The current view must contain only u2.
sql
1WITH ranked AS (2 SELECT user_id, version, operation, name,3 ROW_NUMBER() OVER (4 PARTITION BY user_id ORDER BY version DESC5 ) AS rn6 FROM validated_changes7)8SELECT user_id, name9FROM ranked10WHERE rn = 1 AND operation <> 'delete';Assume validated_changes has unique entity/version pairs. The query ranks the delete before filtering it, so u1 does not reappear.
Exercise
Input: a 1 version one name Ada; a 1 version two delete; b1 version one name Bo; b1 version three name Bea; b1 version two name Ben. Give the current rows and explain why filtering deletes before ranking fails.
Model solution and rubric
Only b1 with name Bea remains. Version three is the highest version for b1 even though version two arrived later. If delete rows are removed before ranking, a 1 version one becomes the highest surviving row and Ada is wrongly restored. Test an entity whose only event is delete, an invalid null version and conflicting same-version rows. The query assumes validation resolves or rejects those cases first.
Score out of four: one point for the correct result, one for showing the intermediate reasoning, one for identifying the stated failure case, and one for a verification that could disprove the answer. Do not award the reasoning point for a tool name alone.
Failure modes and misconceptions
“MAX(name) belongs to MAX(version).” Independent aggregates can select values from different rows. Select the complete winning record.
“Ranking each new batch gives a correct incremental view.” A lower-version batch can regress stored state unless the update compares against the existing authoritative version.
Interview probe
Evidence class:
recommended. Original practice.How would you make this latest-state query safe for late-arriving changes?
Strong answer: Use source versions for state ordering and ingestion progress for selecting new work. A late lower version should not overwrite the current row. A late higher version should update it. Validate equal-version conflicts rather than choose arbitrarily.
Follow-up: How would you handle a delete followed by a legitimate recreate at a higher version?
Weak answer indicators: Filtering deletes before ranking; ordering by arrival without a contract; assuming MAX(name) matches MAX(version).
Sources
Technical references: Debezium PostgreSQL connector; Kafka 4.1 design. Sources support the documented mechanisms. The numbers, decisions, rubrics and interview prompts in this lesson are original teaching examples, not measurements or employer question claims.
docsDebezium PostgreSQL connectordebezium.iodocsKafka 4.1 designkafka.apache.orgCheckpoint
Why must a current-state query rank deletes before excluding deleted entities?
AIt makes every source update idempotent without a keyBIt guarantees the database chooses an index scanCRemoving the latest delete first can make an older active row become the winnerDIt resolves equal-version producer conflicts automatically
Sign up free to answer and see whyCheckpoint
Stored state is version 8; the new batch's maximum is version 6. Incremental action?
AReplace with 6 because the batch is newerBDelete the entityCKeep 8 under the authoritative version ruleDAccept version 6 only because it belongs to a later source partition
Sign up free to answer and see whyCheckpoint
Version 4 deletes an entity and version 5 legitimately recreates it. Current result?
AVersion 5 active under the stated monotonic sequenceBPermanently absent after any deleteCBoth versions currentDWhichever arrived last
Sign up free to answer and see whyCheckpoint
Two payloads share entity/version under a unique-version contract. What is appropriate?
AChoose MAX(name) so reruns agreeBChoose the last ingestion time as proof of source authorityCAccept both as current because event IDs differDReturn the contract conflict for defined reconciliation
Sign up free to answer and see whyCheckpoint
Why use ingestion progress to discover new work and source version to choose state?
AThey are interchangeable clocksBThey answer arrival completeness and business ordering separatelyCIt guarantees no source defectDIt eliminates deletion metadata
Sign up free to answer and see whyExplain how you would write deterministic latest-state logic with deletions without reading the solution. State one assumption that could change your answer, and one observation that would make you revise it.
Not yetGetting thereConfident
Wrap-up
- Show the expected rows before the query. Deletion, ordering and conflicts define correctness.
Sources
Free to read · better with Enzo
Learn it with Enzo
Save your progress, answer the checkpoints, and let Enzo quiz you on what you just read.