The Backfill That Raced Against Live Traffic

We ran a data backfill on 140,000 subscription records while the app was live. Three weeks later, 1,800 customers had the wrong renewal date. The script looked fine. The bug was in the timing.

The script was 40 lines of Python. Read a subscription record, call an internal API to calculate the correct renewal date from payment history, write it back. Run it in batches of 500 with a 200ms sleep between batches to keep the database happy. The kind of thing you write on a Wednesday afternoon and forget about by Friday.

Except three weeks later, a customer support ticket came in: "I was charged on the 3rd but my renewal date is the 17th." Then another. Then four more in the same day.

The client was a B2B subscription platform — about 140,000 active subscriptions across 9,000 accounts. They'd added a renewal_date column to denormalize what had previously required a join through three tables. The column itself was fine. The backfill was the problem.

What the script did

Here's the core of it, simplified:

def backfill_renewal_dates(batch_size=500):
    offset = 0
    while True:
        subs = db.execute(
            "SELECT id, account_id FROM subscriptions "
            "WHERE renewal_date IS NULL "
            "ORDER BY id LIMIT %s OFFSET %s",
            (batch_size, offset)
        )
        if not subs:
            break
 
        for sub in subs:
            renewal = payments_api.get_next_renewal(sub["account_id"])
            db.execute(
                "UPDATE subscriptions SET renewal_date = %s WHERE id = %s",
                (renewal, sub["id"])
            )
 
        offset += batch_size
        time.sleep(0.2)

Nothing obviously wrong. It's readable, it's batched, it has a sleep. The engineer who wrote it ran it against a staging copy first and the numbers checked out.

But staging didn't have live traffic.

The race

In production, the subscription service was handling about 300 writes per minute during business hours. Plan changes, upgrades, downgrades, cancellations, renewals — all of them touching the subscriptions table. Some of those writes also set the renewal_date column because the application code had already been updated to populate it for new transactions.

Here's the timeline for a single bad record:

1. T+0ms: Backfill script reads subscription #82471. renewal_date is NULL.
2. T+50ms: A customer upgrades their plan through the UI. The app writes renewal_date = '2026-10-15' and updates the plan tier. This is the correct value.
3. T+120ms: The backfill script's API call returns. It writes renewal_date = '2026-10-03' — the old renewal date based on the previous plan's payment schedule.

The backfill's UPDATE wins because it runs after the application's UPDATE. But it's using stale data. The payments API returned the renewal date based on state that was already outdated by the time the write landed.

No constraint catches this. No error is thrown. The row has a renewal_date value, it's a valid date, and both writes succeeded. The data is just wrong.

Why we didn't notice for three weeks

The renewal_date column wasn't used for billing yet. It was powering a new dashboard widget that showed customers their next billing date. The billing system still calculated renewal dates the old way, through the join. But the product team had already shipped the dashboard widget to all users, so customers were seeing one date on their dashboard and getting charged on a different date.

Most of the affected records were off by a few days. Some were off by a full billing cycle. The ones that triggered support tickets were the obvious mismatches — a customer on a monthly plan seeing a renewal date three weeks in the future when they'd just been charged yesterday.

Finding the 1,800

Once we understood the race condition, finding the bad records was straightforward. Every subscription modification wrote to an events table with a timestamp. The backfill had its own log. We joined them:

SELECT s.id, s.renewal_date, e.created_at AS last_event_at, b.updated_at AS backfill_at
FROM subscriptions s
JOIN subscription_events e ON e.subscription_id = s.id
JOIN backfill_log b ON b.subscription_id = s.id
WHERE b.updated_at > e.created_at
  AND e.created_at > b.started_at
  AND e.event_type IN ('upgrade', 'downgrade', 'renewal', 'reactivation')
ORDER BY s.id;

1,823 records. About 1.3% of total subscriptions. Small enough that nobody caught it in aggregate metrics, large enough that real customers got wrong information.

We recalculated renewal dates for all of them using the current payment state and deployed the fix in a single transaction.

The pattern we use now

Every backfill script at that client now uses optimistic locking. The approach is simple: read the row's updated_at timestamp when you fetch it, and include it in the WHERE clause when you write back.

for sub in subs:
    renewal = payments_api.get_next_renewal(sub["account_id"])
    result = db.execute(
        "UPDATE subscriptions SET renewal_date = %s "
        "WHERE id = %s AND updated_at = %s",
        (renewal, sub["id"], sub["updated_at"])
    )
    if result.rowcount == 0:
        skipped.append(sub["id"])

If the row changed between the read and the write, the UPDATE matches zero rows and we skip it. The live application's write was more recent, so its data wins. We re-process skipped records in a second pass after the main backfill completes, when traffic is lower.

It's not the only approach. You could use SELECT ... FOR UPDATE to lock rows, but that stalls live traffic, which defeats the purpose of running the backfill online. You could use a queue-based approach where each record gets its own job with retry logic. For this team, the optimistic locking pattern hit the right balance between simplicity and safety.

The uncomfortable part

The engineer who wrote the original script wasn't junior. They'd been writing backfills for years. The problem is that the race condition is invisible in every environment that doesn't have realistic concurrent traffic. And most teams don't load-test their backfill scripts — they load-test their application.

I've since seen variations of this bug at two other clients. A loyalty points recalculation that overwrote points earned during the backfill window. An address normalization job that clobbered addresses customers were updating through a new UI. Same pattern every time: read, transform, write back, lose someone else's write.

The fix is always cheap. The damage from not having it ranges from annoying to catastrophic depending on what the column controls. In our case it was renewal dates — bad, but recoverable. If it had been the column that controlled whether an account gets billed at all, we'd be having a very different conversation.