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

> Backfill historical data in BigQuery efficiently using external tables, partitioning, and idempotent MERGE statements. Orchestrate with Kestra for cost savings.

- Repository: [DataTalksClub/data-engineering-zoomcamp](https://github.com/DataTalksClub/data-engineering-zoomcamp)
- Tags: how-to-guide
- Published: 2026-05-30

---

**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`](https://github.com/DataTalksClub/data-engineering-zoomcamp/blob/main/02-workflow-orchestration/flows/09_gcp_taxi_scheduled.yaml), the flow executes a task (`bq_*_table_ext`) that runs:

```sql
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:

```sql
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:

```sql
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`](https://github.com/DataTalksClub/data-engineering-zoomcamp/blob/main/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`](https://github.com/DataTalksClub/data-engineering-zoomcamp/blob/main/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:

```bash
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.