We use analytics cookies.We use cookies to analyze site traffic and improve your experience.
Loading...
System Design and AI Engineering, by Roni Das. 984 interactive lessons.
Start LearningAdvanced · Stream & Batch Processing
Retroactively populating a new data store, index, or column with historical data. Typically done as a batch job when adding a new feature that needs past data.
In short
A backfill is the process of retroactively loading historical data into a new table, column, index, or data store so it contains the past records it would otherwise be missing. It is usually run as a one-time batch job when you add a feature, fix a bug, or change a schema and need old data to look as if the new logic had always existed.
In short
A backfill is the process of retroactively loading historical data into a new table, column, index, or data store so it contains the past records it would otherwise be missing. It is usually run as a one-time batch job when you add a feature, fix a bug, or change a schema and need old data to look as if the new logic had always existed.
Learn it in the course
This page is the free part.
₹1,111 in India$25 everywhere elseonce, for the whole course
The System Design course covers Backfill across a run of lessons, not one page. These 4 alone are about 157 minutes of step-by-step reading, 1 of them with code you run in the browser, all with a quiz.
Process large volumes of data in scheduled jobs, the workhorse of data pipelines.
Two writers update the same Delta Lake table at once: who wins, and what does the loser see? I ran real writer processes against 3,475,226 NYC taxi trips, 5 runs per case. Two appends both committed every time. Two updates of different rows clashed every time, because they shared the same 9 files: one committed, the other got CommitFailedError. Splitting the table by the filter column let both commit. A check-then-append job started twice stored 317,150 trips for a day of 158,575 in 5 of 5 runs. In delta-rs 1.6.6, a WriteSerializable setting took effect only when spelled writeSerializable.
The data structure powering every search engine, mapping words to the documents that contain them.
When network calls fail, and they will, retries turn transient failures into invisible hiccups.
all part of
System Design Masterclass
770 lessons · about 287 hours · 18 free to read
₹1,111in India, by UPI
$25everywhere else, by PayPal
It is really nice and good Please join the course
You own this course
Continue with Batch Processingpay once,
yours for life
When you ship a new feature, the code only starts producing data from the moment it goes live. A backfill fills in everything before that point. If you add a 'country' column to a 50 million row users table, every existing row has NULL until you run a job that derives country from each user's IP or address and writes it back. That job is the backfill.
The same idea applies beyond a single column. Adding a new search index means you must index every document that already exists, not just new ones. Building a new analytics table means recomputing it from years of raw events. Fixing a bug that corrupted a value means recalculating it for every affected record. All of these are backfills.
The defining trait is that you are processing data that already happened. Normal application traffic handles new data going forward. A backfill is a separate, usually temporary, batch job aimed at the historical tail.
Almost every backfill is a loop over historical records in bounded chunks. You select a batch (say 5,000 rows or one day of events), transform each record with the new logic, write the result, record where you stopped, then move to the next batch. Chunking keeps memory flat and stops a single huge transaction from locking the table or blowing up the database.
Idempotency is the property you cannot skip. A backfill of millions of rows will get interrupted, retried, or run twice. Each write must produce the same end state no matter how many times it runs, which usually means UPSERT semantics or a 'process only rows where the new column is still NULL' filter. You also store a cursor or checkpoint (last id, last timestamp) so a restart resumes instead of starting over.
Throttling matters because the backfill shares the same database and message queue as live traffic. Teams cap the job's rate, run it during off-peak hours, target a read replica where possible, and sleep between batches so production latency does not spike. A common mistake is letting an unthrottled backfill saturate disk IO and take the live service down with it.
For very large jobs the work is fanned out across many workers using a framework like Apache Spark, a distributed queue, or a partitioned set of tasks, each owning a slice of the id or date range so they run in parallel without overlapping.
Reach for a backfill whenever new behavior needs to apply to old data: a new column or index, a new derived table, a corrected calculation, a migration to a new store, or onboarding a customer who expects their full history present on day one. If old data can stay missing or computed lazily on read, you may not need one at all.
The big trade-off is consistency during the run. While the backfill is in flight, some rows have the new value and some do not, so queries see a mixed state for hours or days. You handle this by making readers tolerant of NULLs, or by dual-writing new records with the new logic while the backfill catches up the old ones, then flipping reads over only once it completes.
Cost and time are the other constraints. Backfilling tens of billions of rows can run for days and burn real compute and database load. It is worth dry-running on a sample, measuring throughput, and estimating the full duration before you start, because a half-finished backfill that has to be aborted leaves you in the messy mixed state with no clean rollback.
Say a streaming service adds a 'watch_completion_percent' field to its viewing history so it can recommend shows people nearly finished. New plays record it immediately, but the table holds years of old plays with the field empty.
The team writes a Spark job that reads the raw playback event log partitioned by date, computes completion percent for each historical session, and writes it back to the viewing history table keyed by session id. It runs date range by date range, checkpoints after each day, and writes idempotently so a failed day can rerun safely.
They throttle it to a fraction of cluster capacity so live recommendation queries stay fast, and they keep the recommender tolerant of empty values until the backfill reaches the present. Once the last partition lands, every play old and new has the field, and the feature behaves as if it had existed all along.
In a scheduled pipeline, each run covers one slice of time. Apache Airflow calls this slice the data interval: for a daily pipeline, each run covers one day from midnight to midnight. Running the pipeline again for a past range of dates is what Airflow calls a backfill. In the current Airflow documentation you start one with a command such as airflow backfill create --dag-id DAG_ID --from-date START_DATE --to-date END_DATE.
Airflow also has catchup. With catchup=True, the scheduler creates a run for every interval that was missed since the last run, for example when a pipeline with a start date in the past is switched on. By default it is off (scheduler.catchup_by_default=False), so old intervals are only filled when you ask for a backfill. Because each run owns one interval, a good pipeline writes each run's output into its own date partition and overwrites it on a rerun. That keeps the backfill idempotent, so running a day twice gives one correct result.
Streaming pipelines backfill by replaying the log. If the input still sits in Kafka, you can move the consumer back and process old records again. The Kafka Streams application reset tool resets input offsets to the start of the topic by default, or to a date or duration you choose, which is useful after fixing a bug. It does not reset output topics, so downstream readers must cope with records produced a second time. The distributed job scheduler guide covers catch-up runs after an outage, and batch processing explains the jobs most backfills run as.
Apache Spark
The default engine for large backfills, reading historical data partitioned by date or id and recomputing derived tables in parallel across a cluster.
Airbnb
Uses Airflow DAGs with explicit start dates so a new pipeline can backfill every past day of data on its first run before processing current data.
Stripe
Built tooling around online schema migrations where adding a column triggers a chunked, throttled backfill across huge tables without locking live payment traffic.
GitHub
Runs background backfill jobs to populate new columns and rebuild search indexes across hundreds of millions of repositories and issues.
A migration changes the structure, like adding a column or table. A backfill populates that new structure with historical values. Adding the column is the migration; filling every existing row with the right data is the backfill. Many schema changes need both: first the structural migration, then the data backfill.
Long-running jobs over millions of rows will be interrupted and retried. If running the same batch twice produces a different result, you get double counting or corruption. Idempotent writes (UPSERT, or only touching rows that are still unprocessed) mean a retry safely lands the same end state, so you can resume after any failure.
Process in small chunks, throttle the rate with sleeps between batches, run during off-peak hours, read from a replica when possible, and avoid one giant transaction that locks the table. The goal is to spread the load thin enough that live query latency does not spike while the job runs.
Make readers tolerant of the missing value, treating a NULL as 'not yet computed' rather than an error. A common pattern is to dual-write new records with the new logic immediately while the backfill works through old data, then switch reads to depend on the new field only after the backfill finishes.
It depends entirely on row count, per-row work, and how hard you throttle. Always dry-run on a sample to measure rows per second, then multiply by the total. Backfills of tens of billions of rows can run for days, which is why estimating duration up front is part of planning one.
Backfill means filling in data for a past period that a table or pipeline is missing. You run the new or fixed logic over old records so history looks as if the logic had always existed. Typical triggers are a new column, a new derived table, or a bug fix in a calculation.
In Airflow, a backfill runs a DAG for a historical range of dates you choose. Each date range is split into the DAG's normal data intervals, and one run is created per interval. You start it from the command line with airflow backfill create, giving the DAG id and the from and to dates.
Catchup is automatic. When it is on, the scheduler creates runs for every interval missed since the last run. A backfill is something you start on purpose for a past date range you choose, and its reprocess setting decides whether intervals that already ran are run again. Catchup is off by default.
Yes, as long as the old input is still stored. With Kafka you reset the consumer's offsets to an earlier position or date and let the job read the records again. Plan for duplicates downstream, because outputs written the first time are still there.
The industry examples in “The next question”:
Feature Versioning and Backfill: The Drift Alarm Rang Without a Definition Change
What happens when a feature's definition changes? On real data from a UK online shop I changed two of lesson 1's features: money with returns left out, and frequency as days instead of invoices. I measured five ways the change can reach a model, 20 seeds each. Retrained on the new money, test AP fell by 0.0012 from 0.5452; on the new frequency it rose by 0.0040. Serving the new value to the old model moved it by -0.0005 and +0.0031, and could not be told apart from retraining. A half-done backfill behaved like a full one or a little worse; training only on the redone months cost up to 0.0395. A drift alarm against the training months fired in every test month with no definition change, on all six features, and less often on the real frequency change. Only a versioned name or a fingerprint of the definition caught every change.
Data Ingestion Pipelines: Getting Data Into the ML Platform
How data gets from databases, APIs, events and files into the place a model learns from: pull and push, batch, CDC and streaming, keyed idempotent writes, backpressure, late data, backfills and schema drift, every claim checked against the tools' own documentation. Then a lab on real electricity data sent one file a day: 66 of 756 files sent twice gave 39,417 rows for 36,249 records and no error, shuffled rows made 1,406 lag inputs wrong and scored below clean in every run, and six cheap table checks, each written for one of these faults, fired in all 64 runs while the model's accuracy moved both ways.
Data Lakes, Warehouses, and Lakehouses for ML
Data warehouse vs data lake vs lakehouse for ML, with every claim checked against the vendors' price lists, the Delta, Iceberg and Parquet docs and the 2020 and 2021 papers. Then a lab on a real table of 17,379 hours: the lake stored a file with a comma in every temperature while the lakehouse refused it; after a bad backfill only the pinned version gave back the exact training rows (0 of 8,734 guesses moved, against 8,677 from the lake); 731 tiny Parquet files took 64 times the bytes of one; and vacuum deleted the files the pinned version needed.
Batch Processing
Processing large volumes of data in scheduled chunks rather than in real time. Think nightly reports, ETL jobs, and data warehouse loads.
ETL
Extract, Transform, Load: a pipeline that extracts data from sources, transforms it into the desired format, and loads it into a destination like a data warehouse.
Change Data Capture
Capturing row-level changes in a database and streaming them to other systems in real time. Debezium reads the write-ahead log and publishes changes to Kafka.
Stream Processing
Processing data continuously as it arrives, rather than in batches. Powers real-time analytics, fraud detection, and live dashboards.
Exactly-Once Processing
A processing guarantee where each message is processed exactly one time, even in the face of failures. Achieved through idempotent consumers and transactional producers.
Checkpointing
Periodically saving the state of a stream processing job so it can recover from failures without reprocessing everything from the beginning. Flink and Spark use distributed checkpoints.
This page explains the idea. The course covers it step by step, with diagrams. It is part of the System Design Masterclass course, which has 770 lessons. 18 of them are free to read. You pay once and keep it for life, with no subscription.
See the System Design Masterclass coursecourse 1
From absolute beginner to principal engineer, drawn step by step.
Asked about Backfill in an interview?
Each question has why it is asked, a full answer, what the interviewer listens for, the follow-ups, the common mistakes and a diagram (268 diagrams in all). A PDF you keep, junior to staff level.
India: UPI, cards or netbanking through Razorpay. Elsewhere: card or PayPal. Download at once from My packs.
