Data Engineering / Intermediate: change, time and coordination / Lesson 7

Incremental cursors and checkpoints

Extract bounded changes without skipping equal timestamps or advancing progress before accepted effects. Define the cursor’s source semantics and recovery boundary.

About 4 min readContent reviewed 2026-10-26

On this page

Lesson goals

Build complete stable cursors and explicit overlap policies.
Coordinate target state and progress within a supported boundary.

A high watermark is a claim about source order

An incremental extractor needs an ordering field whose meaning fits the source change model. Event time may precede ingestion and can be corrected or backdated; it is not automatically a complete change cursor. A timestamp alone can also tie across rows. Define a stable composite boundary, source snapshot or log position, and which inserts, updates and deletes it can detect. Document late-change assumptions and a reconciliation path. Calling a field updated_at does not prove every writer advances it consistently or that a deleted row leaves evidence.

Keep every ordering component in the cursor

The offline fixture contains two rows with timestamp ten and one later row. A cursor of (10,1) must retain row two even though its timestamp equals the prior row’s timestamp. Lexicographic comparison matches the stated ascending order. This is a controlled source model, not proof that a real database query is snapshot-stable while rows change. Use the corresponding complete ORDER BY and predicate in the actual engine, with documented null handling and unique tie-breaker semantics. A cursor encoding must preserve types and ordering rather than compare arbitrary printed strings.

python Offline complete-cursor boundary

rows = [(10, 1, 'a'), (10, 2, 'b'), (11, 3, 'c')]
cursor = (10, 1)
page = [row for row in rows if row[:2] > cursor]
assert page == [(10, 2, 'b'), (11, 3, 'c')]
assert [row[1] for row in page] == [2, 3]

The equal-timestamp row remains eligible because its ID is greater. A timestamp-only greater-than filter would miss it.

Overlap requires identity and bounded lateness assumptions

Re-reading a recent interval can recover some late arrivals, but overlapping extraction deliberately repeats records. Deduplicate or apply version-aware updates under an explicit source identity contract. A fixed overlap cannot recover arbitrarily old corrections or hard deletes that no longer exist in the source. Choose the window from measured lateness and business requirements, then retain a deeper repair or change-log path. Distinguish an empty valid interval from a failed fetch; advancing the checkpoint after a source error can permanently skip data while the scheduler reports progress.

Commit the target and checkpoint together when possible

This isolated SQLite program puts one target effect and its checkpoint update in the same explicit transaction, then rolls both back as a failure rehearsal. The unchanged state proves the demonstrated local rollback boundary. It does not establish a cross-system transaction between an external source and warehouse. The connection uses autocommit-style isolation_level=None so the explicit BEGIN is unambiguous. A real distributed design must handle replay and uncertain responses with source retention, idempotency and reconciliation. Never infer end-to-end exactly-once delivery merely from one local transactional example.

python Target/checkpoint rollback in SQLite memory

import sqlite3

db = sqlite3.connect(':memory:', isolation_level=None)
db.executescript('CREATE TABLE target(id INTEGER PRIMARY KEY); CREATE TABLE progress(id INTEGER PRIMARY KEY,pos INTEGER); INSERT INTO progress VALUES(1,0);')
db.execute('BEGIN')
db.execute('INSERT INTO target VALUES(1)')
db.execute('UPDATE progress SET pos=1 WHERE id=1')
db.execute('ROLLBACK')
assert db.execute('SELECT COUNT(*) FROM target').fetchone()[0] == 0
assert db.execute('SELECT pos FROM progress').fetchone()[0] == 0
db.close()

Both target and progress remain at the initial state. The program creates no persistent file or external connection.

Record accepted progress rather than attempted progress

Keep input interval, cursor before/after, target version, accepted/rejected counts and run attempt identity with the checkpoint decision. A checkpoint should describe the data boundary actually accepted, not merely the largest key observed during an incomplete scan. Bound retries and verify connection cleanup after failure. Rehearse equal timestamps, page interruption, out-of-order updates, deletes and source unavailability. Tool-specific APIs can help, but correctness still depends on whether their checkpoint and sink semantics match the promised consumer outcome and the available replay history.

Common errors and fixes

Rows sharing the last timestamp are skipped

Why it happens: The checkpoint omitted a stable tie-breaker.
How to fix it: Use a complete ordered cursor or supported log/snapshot boundary.
Prevent it next time: Test multiple rows at the page boundary.

Progress advances after a failed source read

Why it happens: Failure was treated as an empty completed interval.
How to fix it: Preserve the failure and retain the last accepted checkpoint.
Prevent it next time: Separate no-data and source-error outcomes.

A local commit is called end-to-end exactly once

Why it happens: External source, sink and replay boundaries were ignored.
How to fix it: State the exact atomic boundary and recovery protocol.
Prevent it next time: Test lost responses and replay after partial progress.

Check your understanding

1. Will a two-day overlap recover every correction in a year of data?

Show worked solution

No. It covers only the assumed lateness interval and requires duplicate-safe application. Older corrections and hard deletes need another change-log or reconciliation mechanism. State the source’s guarantees and the maximum repair horizon rather than presenting overlap as universal completeness.

2. Why must a checkpoint describe accepted data rather than the largest observed cursor?