Data Backfill

A data backfill populates or transforms existing database rows so they satisfy a newly introduced schema or application requirement. It is commonly performed alongside schema migrations when new fields, derived values, or changed representations must be applied to historical data.

A backfill operates on data that already exists, unlike a schema migration that primarily changes database structure. For example, after adding a non-null `full_name` column, an application may need to derive and populate values for every existing customer before the constraint can safely be enforced.

Backfills should usually be designed to be safe to rerun. A Java service or migration job can process rows in batches, update only records that still need work, and commit progress incrementally. Batching limits transaction size, lock duration, memory usage, and the impact of failures.

For large tables, avoid assuming that one transaction can update everything efficiently. Prefer a stable pagination strategy such as primary-key ranges, appropriate indexes, bounded batches, and observable progress. The job should also account for concurrent application writes so that newly created or modified records do not become inconsistent.

A backfill is often separated from the schema change itself. A common sequence is to add a nullable or otherwise compatible column, deploy code that can handle both old and new states, backfill historical rows, validate the result, and only then enforce stricter constraints if required.

Operational concerns matter as much as the SQL: measure throughput, monitor failures and database load, make the operation restartable, and define how conflicting or invalid historical data will be handled.

Key points

• Backfills modify existing rows to satisfy a new data requirement.
• Prefer idempotent, restartable operations over one-shot updates.
• Use batching and stable pagination for large datasets.
• Coordinate schema changes, application compatibility, and constraint enforcement.
• Plan for concurrent writes, failures, monitoring, and data validation.

Interview questions

What is a data backfill, and when would you use one?

A data backfill populates or transforms records that already exist. I would use it when a new field, derived value, or data representation needs to apply to historical rows. For example, after adding `full_name`, existing users may need values generated from their first and last names. I would normally make the operation restartable and avoid putting the entire table into one transaction.

How would you safely backfill a large production table?

I would process the table in bounded batches using an indexed, stable key such as a primary key. Each batch would commit independently, record progress, and be safe to retry. I would monitor query latency, lock contention, database load, errors, and throughput while running the job. I would also consider concurrent writes and verify that the final population matches the intended invariant.

How do you make a backfill idempotent?

An idempotent backfill produces the same intended final state when the same row is processed multiple times. One approach is to derive the target value deterministically and update only rows that are not already correct. Progress can be checkpointed, but correctness should not depend solely on the checkpoint because jobs can fail after a database update and before recording progress. Database constraints or conditional updates can provide additional protection.

How would you roll out a new non-null column that must be populated for existing records?

I would generally separate the structural change from enforcement of the final constraint. First, I would add the column in a production-compatible state and deploy application code that understands both old and new records. Then I would backfill historical rows in controlled batches, validate completeness and correctness, and account for records created or modified during the backfill. Once the invariant is demonstrably maintained, I would enforce the non-null constraint if the database and rollout strategy support doing so safely.

What failure modes would you consider before running a production backfill?

I would consider database load, long-running transactions, lock contention, deadlocks, replication lag, partial completion, malformed historical data, and concurrent updates. The job should have bounded work units, retry behavior, observability, and a clear stop or rollback strategy. I would also establish validation queries so success means more than simply reaching the end of the table. For destructive transformations, I would be especially cautious because rollback may require reconstructing the original values rather than simply rerunning the job.