Your SQLite backup restored. Does its checkpoint still match the data?

A backup can restore successfully and still leave your pipeline skipping data.

The awkward case is a checkpoint that says “batch 2 is done” while the rows from batch 2 never made it into the restored database. The next run trusts the checkpoint, skips the batch, and carries on with a hole.

Here is a small SQLite experiment that makes the boundary visible. It uses three ordered batches of synthetic events, all in one currency:

Batch	Event payloads	Total after commit
1	a: 100	100
2	a: 100, b: 50	150
3	b: 50, c: 25	175

The repeated IDs are deliberate. An exact repeat contributes nothing new. A different amount under an existing ID is a conflict.

Put progress in the same transaction as the data

The experiment fails after inserting rows for batch 2, just before updating the checkpoint. Both changes belong to the same transaction. After the exception, the database must still contain only event a and checkpoint 1.

Then it retries batch 2, commits batch 3, restores a backup taken after batch 1 into a separate database, and replays the source batches. The restored result must match the live result.

This is a single-writer teaching example. The batches are immutable, ordered, and complete; end_seq is a trusted batch boundary. It does not model gaps, repartitioning, corrections to already committed batches, or concurrent consumers. Those would need a different progress contract.

Run the experiment

Save this as checkpoint_demo.py and run it with Python. I checked the complete script on Python 3.14.3 using the standard-library sqlite3 module. It creates three separate in-memory databases and writes no database files.

import sqlite3


def state(db):
    seq = db.execute("SELECT seq FROM checkpoint").fetchone()[0]
    rows = db.execute("SELECT id, minor FROM events ORDER BY id").fetchall()
    return seq, rows, sum(amount for _, amount in rows)


def apply_batch(db, end_seq, events, fail=False):
    with db:
        checkpoint = db.execute("SELECT seq FROM checkpoint").fetchone()[0]
        if end_seq <= checkpoint:
            return
        for event_id, minor in events:
            previous = db.execute(
                "SELECT minor FROM events WHERE id = ?", (event_id,)
            ).fetchone()
            if previous is None:
                db.execute("INSERT INTO events VALUES (?, ?)", (event_id, minor))
            elif previous[0] != minor:
                raise ValueError("conflicting event")
        if fail:
            raise RuntimeError("injected failure before checkpoint")
        db.execute("UPDATE checkpoint SET seq = ?", (end_seq,))


live = sqlite3.connect(":memory:")
live.executescript("""
CREATE TABLE events(id TEXT PRIMARY KEY NOT NULL, minor INTEGER NOT NULL);
CREATE TABLE checkpoint(seq INTEGER NOT NULL);
INSERT INTO checkpoint VALUES (0);
""")
apply_batch(live, 1, [("a", 100)])
backup = sqlite3.connect(":memory:")
live.backup(backup)

try:
    apply_batch(live, 2, [("a", 100), ("b", 50)], fail=True)
except RuntimeError:
    pass
assert state(live) == (1, [("a", 100)], 100)
print("after failure:", state(live))
apply_batch(live, 2, [("a", 100), ("b", 50)])
apply_batch(live, 3, [("b", 50), ("c", 25)])

restored = sqlite3.connect(":memory:")
backup.backup(restored)
apply_batch(restored, 1, [("a", 100)])
apply_batch(restored, 2, [("a", 100), ("b", 50)])
apply_batch(restored, 3, [("b", 50), ("c", 25)])
assert state(live) == state(restored) == (3, [("a", 100), ("b", 50), ("c", 25)], 175)
print("live:", state(live))
print("restored:", state(restored))
before_conflict = state(restored)
try:
    apply_batch(restored, 4, [("d", 10), ("a", 999)])
except ValueError:
    pass
else:
    raise AssertionError("conflict was not rejected")
assert state(restored) == before_conflict
print("conflict rollback: passed")

Expected output:

after failure: (1, [('a', 100)], 100)
live: (3, [('a', 100), ('b', 50), ('c', 25)], 175)
restored: (3, [('a', 100), ('b', 50), ('c', 25)], 175)
conflict rollback: passed

What the assertions prove

The first assertion checks the failed attempt. The new b row must disappear, and progress must remain at batch 1. Checking only the final sum would miss that intermediate failure boundary.

The second compares checkpoint, event IDs, individual amounts, and total after restore and replay. A total alone is weak evidence: one missing event and one duplicate can cancel each other out.

The final check submits a batch containing a new d event followed by a conflicting version of a. Rejecting the conflict must also roll back d. Otherwise a rejected batch still changes the database.

The function skips a batch at or below the committed checkpoint. That is safe only under the immutable ordered-input contract above; it deliberately does not revalidate old payloads. The conflict test uses a new batch boundary so the payload is actually examined.

A few SQLite details worth making explicit

The script uses sqlite3's default legacy transaction mode. In that mode, the writes inside with db are committed on success and rolled back when an exception escapes. Do not copy this transaction wrapper into a connection configured with autocommit=True and expect the same behavior. The Python transaction documentation explains the modes.

The connection backup API copies one SQLite database into another. These calls happen after committed work, outside an active write transaction.

An in-memory backup is useful here because the experiment is about state consistency. It is not durable disaster recovery. A production restore drill also needs persistent backup storage, retention, encryption and access controls, recovery-time measurements, and a check that the source history needed for replay still exists.

This test also uses an injected Python exception, not a killed process or a power failure. It checks rollback behavior at a chosen boundary; it does not establish every crash-durability property of the storage stack.

Extend the exercise

I maintain a free fintech data engineering roadmap. C06: backup restore with an atomic checkpoint turns this idea into a scenario lab with a restoration trace, injected failure, replay, and conflict handling.

If you try it, add a failure case before reading the reference solution. A useful starting point is moving the checkpoint update into a helper that commits independently, then finding a test that exposes the broken transaction boundary.

The full repository also includes SQL and Python exercises using synthetic payment data.

A restore drill should answer more than “did the file open?” It should show which source position the restored data represents and whether replay can safely carry it forward.