The Last Message to Arrive May Be an Older Reading
Keeping a Copy Current When the Source Keeps Changing
BOTIE
The Delta Lake work described in the 2020 paper addressed dependable table updates over analytical files. Delta’s MERGE operation can match incoming rows to stored rows and apply declared insert or update rules. That gives us somewhere to express a correction, but we must still decide which source change is authoritative when messages arrive out of order.
Here the source gives each pump’s changes an increasing sequence. A later arrival can therefore be recognised as an older change. We also keep the version of a deletion behind the consumer’s view, so an old update cannot make a deleted pump reappear.
Sources for this history
Why Did a Deleted Pump Reappear?
Botie’s loader no longer repeats completed files. A new file contains a correction for P7, followed by a delayed older update. Later a deletion arrives. If we simply keep the last message in each file, delivery order chooses the current record. If we forget the deletion entirely, an old message can recreate it.
The source needs to tell us which change is newer for that pump. We will keep that sequence beside the entity and carry deletion evidence forward until it is safe to discard.
The source supplies a comparable sequence within each pump’s history. This local merge retains logical deletion records; connecting a real CDC source, committing Delta transactions and fulfilling physical-deletion requirements are separate work.
Step 1 of 1
Arrival order disagrees with source order
All three messages refer to the same entity.
| Arrival | pump | seq | deleted | value |
|---|---|---|---|---|
| First | P7 | 8 | false | 22 |
| Second | P7 | 5 | false | 17 |
| Third | P7 | 9 | true | null |
First
- pump
- P7
- seq
- 8
- deleted
- false
- value
- 22
Second
- pump
- P7
- seq
- 5
- deleted
- false
- value
- 17
Third
- pump
- P7
- seq
- 9
- deleted
- true
- value
- null
Give an entity and a change separate identifiers
The current sequence survives in internal state even when the visible row disappears.
Keep the version that actually came last at the source
A late message recreates a pump that a newer change deleted.
BOTIE
Visible current entity rows
[]
Follow the operations
1. Stored current state including tombstones
[ { "pump": "P7", "seq": 8, "deleted": false, "value": 22 } ]2. Arriving change records
[ { "pump": "P7", "seq": 9, "deleted": true, "value": null }, { "pump": "P7", "seq": 5, "deleted": false, "value": 17 } ]3. Entity key field
pump
4. Choose the last change per entity
[ { "pump": "P7", "seq": 9, "deleted": true, "value": null } ]5. Merge changes while retaining deletion versions
[ { "pump": "P7", "seq": 9, "deleted": true, "value": null } ]6. Hide deleted rows from the consumer
[]
7. Result
[]
The source supplies a comparable sequence within each pump’s history. This local merge retains logical deletion records; connecting a real CDC source, committing Delta transactions and fulfilling physical-deletion requirements are separate work.
1. Give an entity and a change separate identifiers
The pump field says which entity changed. The seq field says where that change sits in this pump’s source history. Think of the distinction as which pump and which revision of its record. Revisions for unrelated pumps do not need one shared numbering system.
Each change also carries deleted and value. deleted is a boolean. A deletion can have value=None and still be an important record: it tells us that this pump became absent at a particular source sequence.
File identity tells us whether an input file completed. The pump and sequence tell us which state its records describe.
2. Choose one unambiguous source change per key
A batch can contain several versions of the same pump. Select its highest source sequence before merging. An exact duplicate at that sequence is harmless; different contents at the same sequence are ambiguous and should stop the update.
Do not use whichever row happens to appear last after a distributed operation. An ordering must be declared. A tie-breaker that arbitrarily prefers one contradictory record hides the broken upstream contract.
This preprocessing also prevents an ambiguous collection of source rows from matching the same target row in a MERGE.
3. Compare the incoming change with the stored version
A newer source sequence can replace the stored state. An older one must leave it alone. An identical repeated current change is a no-op. Conflicting contents at the same sequence require investigation rather than an unconditional overwrite.
An absent entity can accept its first known change, including a deletion marker. Otherwise an earlier update arriving later would look like a new entity and resurrect it.
stored P7 sequence = 9, deleted = true
incoming P7 sequence = 8, deleted = false
8 < 9
keep stored deletion at sequence 9The current state follows source-version order across different deliveries.
Compare the source sequence before replacing state
A deleted record still has an entity identity and source sequence. Reading those fields does not require a numerical value to be present.
stored = {"pump":"A","seq":9,"deleted":True,"value":None}
incoming = {"pump":"A","seq":8,"deleted":False,"value":22}
print(incoming["seq"] > stored["seq"])FalseThe arriving active value is older. The fuller challenge handles batches, independent entities and contradictory ties.
Check yourself
Questions on Parts 1 to 3
Question 1 of 2
What should remain current?
Use the information below.
Stored P7: seq 8, value 22. Incoming P7: seq 5, value 17. The source sequence is authoritative.4. Keep deletion evidence behind the visible view
The stored state retains tombstones: records saying that an entity was deleted at a known sequence. A consumer view filters deleted rows out. That makes the current visible table empty for the entity without forgetting why an older update must be ignored.
Retention is a policy decision. Tombstones cannot be removed safely while older updates can still arrive under this contract. This is also not a claim that personal data has been erased from logs, snapshots or model weights. Logical visibility and deletion obligations need separate treatment.
The workshop output shows active rows. Inspect the merge component’s intermediate output to see retained deletion state.
5. Express the version rule in Delta MERGE
The reference assumes latest_changes already has one unambiguous source row per pump. It stores deleted as a column, so an unmatched deletion can also be retained. A separate view or filter supplies visible nondeleted records.
The source DataFrame must use the same declared columns and compatible types as the target. Reject contradictory equal-sequence rows before this step. The conditional update keeps older and identical-sequence source rows from overwriting stored state; it is not an upstream conflict detector.
Production CDC can have richer ordering and retention semantics. Inspect the source’s guarantees before replacing its ordering with an ingestion timestamp.
Run in your own environment
Merge only a newer entity version
This optional reference runs in your own Databricks environment. The browser exercise below uses ordinary Python and does not contact Databricks.
Use your own Delta table and a validated source DataFrame with pump STRING, seq BIGINT, deleted BOOLEAN and value DOUBLE. The target retains tombstones.
This reference modifies the target. Use an isolated approved table, preserve its earlier version, and validate source uniqueness and equal-sequence consistency before execution.
from delta.tables import DeltaTable
# latest_changes has pump, seq, deleted, value.
# It has one unambiguous row per pump, checked before this step.
target = DeltaTable.forName(spark, "lab.training.pump_state")
(target.alias("t")
.merge(latest_changes.alias("s"), "t.pump = s.pump")
.whenMatchedUpdateAll(condition="s.seq > t.seq")
.whenNotMatchedInsertAll()
.execute())
visible = spark.table("lab.training.pump_state").where("deleted = false")A physical delete would remove the sequence guard for an unmatched later insert. This example deliberately keeps tombstones in the state table.
Official interface documentation. Documentation checked .
6. Test sequences across more than one delivery
A first batch can delete a pump; a second can contain an older update. Testing only that each batch works independently misses the resurrection. Feed the first returned state into the second call and check the retained deletion.
Also test a genuinely newer update after deletion. Our declared source contract permits that to create a new visible state. If the business requires permanent deletion instead, its identity and retention rules must forbid that transition explicitly.
The same version logic will matter when a historical feature table is corrected. We must decide which changes a past prediction was allowed to know.
Check yourself
Questions on Parts 1 to 6
Question 1 of 4
Which retained evidence would prevent accidental resurrection?
Use the information below.
P7 was deleted at seq 9. A late seq 8 update arrives after its row was physically removed.Try it with Botie
Build with Botie
Write and run Python