APPLYDJINN
← ETL and Data Engineering: reliable pipelinesProgress & review ↗

Typorithm · medium

CDC tombstones and stale resurrection

pythonetldata-engineeringindependent-practice
Saved on this device
Full lesson · 4 learning steps Open any exercise for focused practice
1

Step 1 of 4 · intro card

CDC tombstones and stale resurrection

0%

Concept and contract

Retain delete versions so an older replay cannot bring a deleted entity back to life.

Change data capture represents updates and deletions as ordered source events. Deleting the current row physically can discard the version needed to reject an older update. A tombstone retains the identity, authoritative delete version and deleted flag while excluding the entity from the visible current-state query.

The fixture uses a local SQLite table and a per-identity source version. Upsert and delete are the only supported operations. A deletion is therefore a state transition with ordering evidence, not merely the absence of a row. Retention of that evidence must match the source replay and recovery policy.

Mechanism and worked example

For each event the program forms a current-state payload and applies a parameterized UPSERT guarded by a strictly newer version.

For each event the program forms a current-state payload and applies a parameterized UPSERT guarded by a strictly newer version. A delete stores a tombstone; an upsert stores a visible amount. The report separately queries visible identities and retained ordering state so the distinction can be inspected.

Trace an upsert followed by a newer delete and then a stale upsert. The final entity remains invisible, yet its delete version is still present. A genuinely newer upsert can make it visible again under this declared source protocol. The empty input case has neither visible records nor invented tombstones.

Complete worked example

python

import sqlite3

def inspect_example(events):
    connection = sqlite3.connect('cdc.db', autocommit=False)
    try:
        connection.execute('CREATE TABLE state(id TEXT PRIMARY KEY, version INTEGER, amount_cents INTEGER, deleted INTEGER)')
        connection.commit()
        with connection:
            for event in events:
                if event['op'] not in ['upsert', 'delete']:
                    raise ValueError('unknown operation')
                connection.execute('INSERT INTO state VALUES(?,?,?,?) ON CONFLICT(id) DO UPDATE SET version=excluded.version,amount_cents=excluded.amount_cents,deleted=excluded.deleted WHERE excluded.version > state.version',
                    [event['id'], event['version'], event.get('amount_cents'), int(event['op'] == 'delete')])
        visible = [r[0] for r in connection.execute('SELECT id FROM state WHERE deleted=0 ORDER BY id')]
        retained = [list(r) for r in connection.execute('SELECT id,version,deleted FROM state ORDER BY id')]
        return {'visible': visible, 'retained': retained}
    finally:
        connection.close()
Reasoning
For each event the program forms a current-state payload and applies a parameterized UPSERT guarded by a strictly newer version. A delete stores a tombstone; an upsert stores a visible amount. The report separately queries visible identities and retained ordering state so the distinction can be inspected.
Download the ETL and Data Engineering computer lab ↗

Focused decision: use the complete example for context

python

connection.execute(
    "UPDATE state SET deleted=1,version=? WHERE id=? AND version < ?",
    [version, identity, version],
)
Decision
Count retained tombstones without letting an obsolete upsert resurrect a deleted identity.
Download the ETL and Data Engineering computer lab ↗

Counterexamples and common mistakes

The controlled bug reverses the version guard.

The controlled bug reverses the version guard. It lets stale data win and can resurrect an entity whose newer delete should remain authoritative. Merely filtering deleted rows in the final SELECT cannot repair a state table that has already accepted the wrong event version.

Physically purging tombstones too early creates a related risk. An old replay then appears to be a new insertion because the ordering evidence is gone. Choose tombstone cleanup only with a proven replay horizon, source generation boundary or reconciliation procedure. The small example does not implement general CDC connectors, schema changes or distributed transaction ordering.

Controlled bug: observe the rejected data contract

python

import sqlite3

def inspect_example(events):
    connection = sqlite3.connect('cdc.db', autocommit=False)
    try:
        connection.execute('CREATE TABLE state(id TEXT PRIMARY KEY, version INTEGER, amount_cents INTEGER, deleted INTEGER)')
        connection.commit()
        with connection:
            for event in events:
                if event['op'] not in ['upsert', 'delete']:
                    raise ValueError('unknown operation')
                connection.execute('INSERT INTO state VALUES(?,?,?,?) ON CONFLICT(id) DO UPDATE SET version=excluded.version,amount_cents=excluded.amount_cents,deleted=excluded.deleted WHERE excluded.version < state.version',
                    [event['id'], event['version'], event.get('amount_cents'), int(event['op'] == 'delete')])
        visible = [r[0] for r in connection.execute('SELECT id FROM state WHERE deleted=0 ORDER BY id')]
        retained = [list(r) for r in connection.execute('SELECT id,version,deleted FROM state ORDER BY id')]
        return {'visible': visible, 'retained': retained}
    finally:
        connection.close()
Reasoning
The controlled bug reverses the version guard. It lets stale data win and can resurrect an entity whose newer delete should remain authoritative. Merely filtering deleted rows in the final SELECT cannot repair a state table that has already accepted the wrong event version.
Download the ETL and Data Engineering computer lab ↗

Independent practice

Add tombstones to the report as a count measured from retained rows with deleted set.

Add tombstones to the report as a count measured from retained rows with deleted set. Preserve visible identities, retained versions and the strict update guard. The count describes stored deletion evidence, so it must not be inferred from how many delete operations appeared in the source delivery.

Query the database state after all events have been applied. A deleted entity that later receives a newer upsert should no longer count as a current tombstone. Use the stale-resurrection case, an empty stream and an ordinary update to distinguish final state from event history or a hardcoded delete counter.

Step 1: Count retained tombstones without letting an obsolete upsert resurrect a deleted identity. Step 2: Edit the owned exercise file and run verify.py --practice etl-cdc. Preserve caller inputs and the independently declared failure cases.

Practice starter: solve before reading the solution

python

import sqlite3

def inspect_example(events):
    connection = sqlite3.connect('cdc.db', autocommit=False)
    try:
        connection.execute('CREATE TABLE state(id TEXT PRIMARY KEY, version INTEGER, amount_cents INTEGER, deleted INTEGER)')
        connection.commit()
        with connection:
            for event in events:
                if event['op'] not in ['upsert', 'delete']:
                    raise ValueError('unknown operation')
                connection.execute('INSERT INTO state VALUES(?,?,?,?) ON CONFLICT(id) DO UPDATE SET version=excluded.version,amount_cents=excluded.amount_cents,deleted=excluded.deleted WHERE excluded.version > state.version',
                    [event['id'], event['version'], event.get('amount_cents'), int(event['op'] == 'delete')])
        visible = [r[0] for r in connection.execute('SELECT id FROM state WHERE deleted=0 ORDER BY id')]
        retained = [list(r) for r in connection.execute('SELECT id,version,deleted FROM state ORDER BY id')]
        return {'visible': visible, 'retained': retained}
    finally:
        connection.close()

# Independent task: Count retained tombstones without letting an obsolete upsert resurrect a deleted identity.
Reasoning
Count retained tombstones without letting an obsolete upsert resurrect a deleted identity.
Download the ETL and Data Engineering computer lab ↗
  • Hint 1: Add tombstones to the report as a count measured from retained rows with deleted set. Preserve visible identities, retained versions and the strict update guard. The count describes stored deletion evidence, so it must not be inferred from how many delete operations appeared in the source delivery.
  • Hint 2: Query the database state after all events have been applied. A deleted entity that later receives a newer upsert should no longer count as a current tombstone. Use the stale-resurrection case, an empty stream and an ordinary update to distinguish final state from event history or a hardcoded delete counter.

Solution, tests and trade-offs

The solution returns an aggregate over retained current state.

The solution returns an aggregate over retained current state. Acceptance verifies that stale replay leaves the authoritative delete visible in retained metadata while absent from the business-facing result. Exact observations cover both sides of that contract; testing only visible rows would leave tombstone retention unexamined.

A complete CDC pipeline also needs source offsets, transaction boundaries, duplicate handling and policies for equal-version contradictions. This lesson focuses on monotonic versioned state, while the capstone adds checkpoint atomicity and rejects contradictory payloads at one version. Do not extend this bounded mechanism into a claim that every real CDC product shares the same semantics.

Practice solution and acceptance checks

python

import sqlite3

def inspect_example(events):
    connection = sqlite3.connect('cdc.db', autocommit=False)
    try:
        connection.execute('CREATE TABLE state(id TEXT PRIMARY KEY, version INTEGER, amount_cents INTEGER, deleted INTEGER)')
        connection.commit()
        with connection:
            for event in events:
                if event['op'] not in ['upsert', 'delete']:
                    raise ValueError('unknown operation')
                connection.execute('INSERT INTO state VALUES(?,?,?,?) ON CONFLICT(id) DO UPDATE SET version=excluded.version,amount_cents=excluded.amount_cents,deleted=excluded.deleted WHERE excluded.version > state.version',
                    [event['id'], event['version'], event.get('amount_cents'), int(event['op'] == 'delete')])
        visible = [r[0] for r in connection.execute('SELECT id FROM state WHERE deleted=0 ORDER BY id')]
        retained = [list(r) for r in connection.execute('SELECT id,version,deleted FROM state ORDER BY id')]
        return {'visible': visible, 'retained': retained, 'tombstones': sum(r[2] for r in retained)}
    finally:
        connection.close()
Reasoning
The solution returns an aggregate over retained current state. Acceptance verifies that stale replay leaves the authoritative delete visible in retained metadata while absent from the business-facing result. Exact observations cover both sides of that contract; testing only visible rows would leave tombstone retention unexamined.
Download the ETL and Data Engineering computer lab ↗

Interview reasoning and next step

Explain why a tombstone remains useful after the visible entity disappears.

Explain why a tombstone remains useful after the visible entity disappears. Then describe which newer event is allowed to restore visibility and why an older event is not. Separate source version from delivery position: one orders entity state, while the other can identify how far extraction has committed.

For a stretch, write a retention decision table covering maximum replay age, source reset and a full reconciliation. Add an owned test that physically removes a tombstone and demonstrates the resulting risk. Treat that as a controlled counterexample, not as a recommended cleanup operation for a live database.

Optional stretch — after the core task works: Demonstrate tombstone purge risk in an owned fixture and define a reviewed retention decision table.

Computer practice: https://applydjinn.com/typorithm/etl-data-engineering-lab-20261107.zip. Use an isolated Python 3.13 environment, install requirements.txt and run verify.py. Edit exercises/etl-cdc.py and run --practice etl-cdc. Each case owns its files and database; Parquet uses the pinned DuckDB engine. The recovery probe terminates a separate interpreter and reopens the SQLite file. The app retains static examples, prepared results and questions without an editor or code execution. Use the course Download button once to save every lesson and illustration; opening an unsaved course does not automatically download it.

Retain delete versions so an older replay cannot bring a deleted entity back to life.