Backfill existing data in bounded batches

In this lesson you will backfill existing data in bounded batches. Work with the Equipment Rental API in the downloadable lab. The reference is a complete solution with separate lesson checks, so you can inspect the answer, make a deliberate local change and verify its behavior.

A backfill evolves data that already exists. During an expand stage, old equipment rows may have condition=NULL while new writes already supply a valid value. backfill_condition selects a bounded batch of missing ids, updates only rows still missing the value, and commits that batch through engine.begin. Rerunning after interruption skips filled rows, so the job is idempotent.

Batching limits lock duration and transaction size. It does not make every large migration risk-free: indexing, application compatibility and concurrent writers still need review. First deploy code that tolerates the expanded shape, run and observe the backfill, verify no missing values remain, and then add the final constraint in a later migration. The bundled 0002 migration is a small-data example in one transaction. The separate helper demonstrates independent batch commits; using it inside an existing migration transaction would be a different behavior. Count processed batches or rows without logging learner records or credentials. Do not overwrite non-null values merely to make every row look identical.

Worked source: equipment/services.py, backfill_condition.

The lesson11 fixture temporarily makes condition nullable only in its disposable database. It creates three missing values, uses a batch size of two, expects two committed batches, reruns expecting zero, and restores NOT NULL. This intentionally simulates an intermediate schema rather than weakening the application's final schema.

def backfill_condition(engine, batch_size=100):
    if batch_size < 1:
        raise ValueError("Batch size must be positive")
    batches = 0
    while True:
        with engine.begin() as connection:
            identifiers = (
                connection.execute(
                    select(Equipment.id)
                    .where(Equipment.condition.is_(None))
                    .order_by(Equipment.id)
                    .limit(batch_size)
                )
                .scalars()
                .all()
            )
            if not identifiers:
                break
            connection.execute(
                update(Equipment)
                .where(Equipment.id.in_(identifiers), Equipment.condition.is_(None))
                .values(condition="ready")
            )
        batches += 1  # each batch commits; rerunning ignores already-filled rows
    return batches

python run_checks.py -k lesson11