Backfilling data safely: real-world lessons and examples

Introduction: Why backfilling matters

Every data team encounters the moment: a bug in the pipeline, a schema change, or a late-arriving source feed means you need to reprocess weeks or months of historical data. Backfilling data safely: real-world lessons and examples shows us that the difference between a smooth recovery and a production incident often comes down to careful planning and disciplined execution. In this guide, we'll walk through the techniques and patterns that help us reprocess history without chaos, drawing from experience across e-commerce, SaaS, and trading platforms.

Idempotent jobs by date

The foundation of safe backfilling is idempotency—the property that running your job multiple times with the same input produces the same output. Without it, each retry or re-run risks creating duplicate records, incorrect aggregations, or compounding errors.

In practice, this means your Spark job must be designed so that processing the same date range twice yields identical results. The safest approach is to make your job accept a date parameter and process only that specific day (or day range), with all transformations deterministic and repeatable.

from pyspark.sql import SparkSession
from datetime import datetime, timedelta

spark = SparkSession.builder.appName("BackfillOrders").getOrCreate()

def process_day(target_date: str):
    """
    Process a single day of orders.
    target_date: 'YYYY-MM-DD' format
    """
    # Read raw events for this day only
    df_raw = spark.read.parquet(
        f"s3://data-lake/raw/events/date={target_date}"
    )
    
    # Apply all transformations deterministically
    df_processed = (
        df_raw
        .filter(df_raw.event_type == "purchase")
        .withColumn("amount_cents", (df_raw.amount * 100).cast("long"))
        .withColumn("processed_at", F.current_timestamp())
    )
    
    # Overwrite partition for this date
    df_processed.write \
        .mode("overwrite") \
        .partitionBy("date") \
        .parquet(f"s3://data-lake/processed/orders/date={target_date}")
    
    return df_processed.count()

# Backfill 30 days
start_date = datetime(2024, 1, 1)
for i in range(30):
    current_date = (start_date + timedelta(days=i)).strftime("%Y-%m-%d")
    count = process_day(current_date)
    print(f"Day {current_date}: {count} records processed")

This pattern ensures that if the job fails mid-backfill on day 15, you can restart from day 15 without affecting days 1–14. Each date is independently reproducible. Use environment variables or command-line arguments to specify the date, never hardcode it.

Overwrite affected partitions only

Once your job is idempotent, the next critical step is to overwrite only the partitions you're backfilling, not the entire dataset. Writing to the entire table forces downstream consumers to wait, risks breaking their queries mid-read, and wastes I/O on partitions you haven't touched.

Spark's partitioned writes handle this elegantly. By using .mode("overwrite") with a filter or partition key, you replace only the affected data:

from pyspark.sql.functions import col, to_date

def backfill_range(start_date: str, end_date: str):
    """
    Backfill orders between start_date and end_date (inclusive).
    Overwrites only affected partitions.
    """
    # Read raw data for the range
    df_raw = spark.read.parquet("s3://data-lake/raw/events/")
    
    # Filter to date range
    df_filtered = df_raw.filter(
        (to_date(col("timestamp")) >= start_date) &
        (to_date(col("timestamp")) <= end_date)
    )
    
    # Transform
    df_processed = df_filtered.groupBy("date", "user_id").agg(
        F.sum("amount").alias("daily_spend"),
        F.count("*").alias("transaction_count")
    )
    
    # Overwrite only these date partitions
    df_processed.write \
        .mode("overwrite") \
        .partitionBy("date") \
        .parquet("s3://data-lake/warehouse/daily_spend/")
    
    print(f"Backfill complete: {start_date} to {end_date}")

# Usage
backfill_range("2024-01-01", "2024-01-31")

The key insight: Spark only rewrites the partition directories that contain data from your filtered date range. Existing partitions outside that range remain untouched, allowing concurrent reads and keeping overall write time minimal.

Throttle to protect resources

A backfill job processing months of data can consume all available cluster resources, starving production jobs and degrading the platform for users. Throttling—deliberately limiting parallelism and throughput—protects your infrastructure.

We typically implement throttling in two ways: partition-level throttling (process one date at a time) and cluster-level resource limits (lower executor memory or core count).

from datetime import datetime, timedelta
import time

def backfill_with_throttle(
    start_date: str, 
    end_date: str, 
    delay_seconds: float = 60,
    max_concurrent_jobs: int = 2
):
    """
    Backfill with inter-job delays to limit resource usage.
    Processes dates sequentially to avoid overwhelming the cluster.
    """
    current = datetime.strptime(start_date, "%Y-%m-%d")
    end = datetime.strptime(end_date, "%Y-%m-%d")
    
    job_count = 0
    
    while current <= end:
        date_str = current.strftime("%Y-%m-%d")
        
        try:
            print(f"[{datetime.now()}] Starting backfill for {date_str}")
            process_day(date_str)
            job_count += 1
            
            # Throttle: pause between jobs
            if current < end:
                print(f"  Waiting {delay_seconds}s before next job...")
                time.sleep(delay_seconds)
        
        except Exception as e:
            print(f"Error on {date_str}: {e}")
            # Decide: retry, skip, or fail
            # For critical backfills, fail fast and investigate
            raise
        
        current += timedelta(days=1)
    
    print(f"Backfill complete: {job_count} days processed")

# Backfill 90 days with 60-second gaps between jobs
backfill_with_throttle("2023-10-01", "2023-12-29", delay_seconds=60)

Alternatively, configure your Spark session to use fewer resources during backfill:

spark = SparkSession.builder \
    .appName("BackfillLowResource") \
    .config("spark.executor.instances", "4") \
    .config("spark.executor.cores", "2") \
    .config("spark.executor.memory", "4g") \
    .getOrCreate()

The balance here is practical: backfill slowly enough that production jobs meet their SLAs, but fast enough that the backfill completes before the next data refresh cycle. Communicate backfill schedules to the team so no one is surprised by elevated latency.

Validate counts after backfill

The backfill completed, the logs look good—but did it actually work? Validation catches silent failures where data silently diverges from expectations.

The most effective validation is row count reconciliation: compare the count of records in the source against the count in the target. A mismatch flags either data loss or duplication.

def validate_backfill(
    start_date: str, 
    end_date: str,
    source_path: str,
    target_path: str
):
    """
    Validate that backfilled data matches source counts.
    Raises AssertionError if counts diverge.
    """
    # Count source records in date range
    df_source = spark.read.parquet(source_path)
    source_count = df_source.filter(
        (to_date(col("timestamp")) >= start_date) &
        (to_date(col("timestamp")) <= end_date)
    ).count()
    
    # Count target records in date range
    df_target = spark.read.parquet(target_path)
    target_count = df_target.filter(
        (col("date") >= start_date) &
        (col("date") <= end_date)
    ).count()
    
    print(f"Source count: {source_count}")
    print(f"Target count: {target_count}")
    
    assert source_count == target_count, \
        f"Count mismatch! Source: {source_count}, Target: {target_count}"
    
    return True

# After backfill completes
try:
    validate_backfill(
        "2024-01-01",
        "2024-01-31",
        "s3://data-lake/raw/events/",
        "s3://data-lake/warehouse/daily_spend/"
    )
    print("✓ Validation passed")
except AssertionError as e:
    print(f"✗ Validation failed: {e}")
    # Trigger alert, rollback, investigate

Beyond row counts, run spot checks on specific metrics:

• Compare aggregated totals (sum of revenue, average order value) between old and new tables
• Sample 1,000 random records and manually verify a few key fields
• Run business logic tests (e.g., "daily revenue must be positive", "no order total exceeds 10,000")

For high-stakes backfills, we often run these validations in a pre-production environment first, then promote with confidence.

Common mistakes

• Non-idempotent jobs: Running the job twice produces different results. Fix this by ensuring all logic is deterministic and parameterized by date. Avoid random UUIDs, current timestamps, or non-deterministic aggregations.
• Overwriting the entire table: Writing with .mode("overwrite") without partitioning rewrites everything, blocking production reads and wasting resources. Always partition and filter to the specific date range.
• No throttling: A backfill job consuming 100% of cluster resources starves production pipelines. Implement delays between jobs or run backfills on a separate, resource-limited cluster.
• Skipping validation: Assuming the job succeeded because the Spark action completed. Always validate row counts and spot-check data quality; silent data loss is worse than a loud failure.
• Backfilling during peak hours: Scheduling a large backfill during business hours impacts real-time dashboards and APIs. Run backfills outside peak windows or communicate clearly with stakeholders.

Key takeaways

• Design jobs to be idempotent by date: processing the same day multiple times must yield identical results. Use date parameters, not hardcoded values.
• Overwrite partitions selectively: use partition-aware write modes to update only the affected date ranges, not the entire table.
• Throttle aggressively: add delays between jobs and reduce executor resources to protect production workloads during backfill windows.
• Validate ruthlessly: compare row counts between source and target, run business logic checks, and spot-sample data to catch silent failures early.
• Document backfill procedures and communicate schedules to the team; backfills are routine operational tasks, not surprises.