How to Backfill Historical Data in BigQuery Cost-Effectively: A Production Guide

Backfill historical data into BigQuery cost-effectively by combining external tables over Cloud Storage, partitioned destination tables, and idempotent MERGE statements orchestrated through Kestra with a backfill:true label.

Backfilling years of historical data into BigQuery can drain your cloud budget rapidly if you rely on expensive load jobs and full-table rewrites. The DataTalksClub data-engineering-zoomcamp repository demonstrates a battle-tested pattern for backfilling historical data in BigQuery cost-effectively that minimizes both compute and storage costs. This guide breaks down the exact architecture used to process large archives like the NYC Taxi dataset without incurring unnecessary BigQuery storage or ingestion charges.

Architecture: External Tables and Partitioned MERGE

The cost-effective backfill pattern relies on five core components that work together to avoid expensive data movement and redundant processing.

External Tables as a Cost-Optimized Data Lake

Instead of loading CSV files directly into BigQuery (which incurs load job costs and duplicates storage), the pipeline creates external tables that read directly from Google Cloud Storage (GCS).

In 02-workflow-orchestration/flows/09_gcp_taxi_scheduled.yaml, the flow executes a task (bq_*_table_ext) that runs:

CREATE OR REPLACE EXTERNAL TABLE `project.dataset.external_table`
OPTIONS (
  format = 'CSV',
  uris = ['gs://bucket/path/*.csv']
)

BigQuery reads these files on-demand during query execution, eliminating the $0.02/GB load job fee and avoiding the need to store the data twice.

Partitioned Tables for Query Pruning

The destination tables are explicitly partitioned by the pickup date using PARTITION BY DATE(lpep_pickup_datetime). This ensures that when the backfill runs, only the specific partitions receiving new data are touched, dramatically reducing write capacity costs and keeping subsequent queries fast and cheap through partition pruning.

Idempotent Processing with Deterministic Hashing

To prevent duplicate data during reruns, the pipeline creates a staging table (*_table_tmp) that adds a deterministic hash:

SELECT 
  MD5(CONCAT(...)) AS unique_row_id,
  "{{file}}" AS filename,
  *
FROM external_table

This unique_row_id enables the final MERGE statement to identify truly new rows.

MERGE for Append-Only Ingestion

The final step (*_merge) uses a BigQuery MERGE statement to insert only non-existent rows:

MERGE INTO dataset.destination_table T
USING staging_table S
ON T.unique_row_id = S.unique_row_id
WHEN NOT MATCHED THEN INSERT ...

Because the ON clause matches the deterministic hash, already-ingested rows are skipped, preventing duplicate storage charges.

Automatic Resource Cleanup

The flow includes a purge_files task that triggers PurgeCurrentExecutionFiles to delete temporary CSVs from local storage after upload. This prevents orphaned storage from accumulating in GCS, keeping storage costs minimal.

Step-by-Step Implementation

The complete pipeline is defined in 02-workflow-orchestration/flows/09_gcp_taxi_scheduled.yaml. Here is how the orchestration handles each stage:

1. Extract and Stream: The Kestra task downloads the CSV via wget and pipes it directly to gunzip, streaming the data without intermediate disk accumulation.
2. Upload to GCS: The upload_to_gcs task writes the file to a bucket path structured by date, creating the data lake layer.
3. Create External Table: The bq_*_table_ext task creates or replaces the external table pointing to the GCS URI.
4. Build Staging Table: The *_table_tmp task queries the external table to compute unique_row_id and select all columns into a temporary BigQuery table.
5. Execute MERGE: The *_merge task runs the MERGE statement against the partitioned destination table.
6. Clean Up: The flow purges local execution files to free temporary storage.

Executing the Backfill

To backfill historical data, you reuse the exact same production pipeline by triggering it with specific parameters.

Using the Kestra UI with Labels

The flow header in 09_gcp_taxi_scheduled.yaml (lines 4-6) specifies that you should add a label backfill:true when running historical dates. In the Kestra UI:

1. Select "Execute flow"
2. Add the label backfill:true
3. Set the input taxi: green (or yellow)
4. Set the trigger date (e.g., 2019-01-01)

You can use the "Run for date range" feature to execute the pipeline for every month in your historical window without modifying the underlying YAML.

Programmatic Execution via API

For automation, trigger the backfill via the Kestra REST API:

curl -X POST \
  -H "Authorization: Bearer <YOUR_TOKEN>" \
  -H "Content-Type: application/json" \
  -d '{
        "inputs": {"taxi":"green"},
        "labels": {"backfill":"true"},
        "trigger": {"date":"2019-01-01"}
      }' \
  https://kestra.example.com/api/v1/executions/zoomcamp/09_gcp_taxi_scheduled

Repeat this request for each historical date, or script a loop over your desired range. The pipeline will process each date independently, maintaining the same cost optimizations.

Why This Pattern Minimizes BigQuery Costs

Zero Load Job Fees: Reading CSVs via external tables eliminates the jobs.load charges associated with bq load commands or storage transfers.

Partition Pruning: By partitioning on lpep_pickup_datetime, the MERGE operation only writes to specific date partitions. BigQuery only scans and modifies the relevant shards, reducing slot time and write costs.

Deduplication at Compute Time: The unique_row_id hash ensures you never pay to store duplicate rows. The MERGE statement acts as a gatekeeper, only inserting truly new data.

Cheap Cold Storage: Raw CSVs live in GCS Nearline or Coldline tiers, which cost significantly less than BigQuery storage. You keep the source of truth cheap while querying it on-demand.

No Operational Debt: The PurgeCurrentExecutionFiles task ensures temporary execution artifacts do not accumulate as unmanaged storage costs.

Summary

• Use external tables over GCS to avoid BigQuery load job fees when backfilling historical data.
• Partition destination tables by date to ensure only target partitions are modified during the backfill.
• Generate deterministic hashes (unique_row_id) in staging tables to enable idempotent MERGE operations.
• Execute MERGE statements to append only new rows, preventing duplicate storage costs.
• Trigger backfills via Kestra using the backfill:true label to reuse production pipelines for historical dates without code changes.
• Purge temporary files automatically after each run to prevent GCS storage bloat.

Frequently Asked Questions

Why use external tables instead of loading directly into BigQuery?

External tables eliminate the load job cost ($0.02 per GB) and avoid duplicating data between GCS and BigQuery storage. BigQuery reads the CSVs directly from Cloud Storage on-demand, making this pattern ideal for one-time backfills where you want to keep raw archives in cheap object storage.

How does the MERGE statement prevent duplicate charges?

The MERGE statement joins the staging table against the destination table using the unique_row_id hash. The WHEN NOT MATCHED clause ensures that only rows not already present in the destination are inserted. This prevents paying for storage of duplicate records and avoids the write costs associated with reprocessing the same data.

What is the purpose of the unique_row_id hash?

The unique_row_id is a deterministic MD5 hash computed from the row's content in the staging table (*_table_tmp). It provides a reliable fingerprint for each record, enabling the MERGE operation to identify duplicates idempotently. Without this key, the backfill could insert redundant data if run multiple times for the same date.

Can I backfill multiple months at once with Kestra?

While the pipeline processes one date per execution, you can use the Kestra UI's "Run for date range" feature or script multiple API calls to iterate over a historical range. Each execution remains independent and cost-effective, processing only the specific partition for that date. The backfill:true label helps you identify these historical runs in the execution logs.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}