BigQuery Partition-Aware Incremental Refresh

Reprocessing an entire BigQuery table every run is slow and expensive when only the last few days of data actually change. This blueprint runs a partition-aware incremental refresh: it walks a small rolling window of days and, for each day, loads just that day's Google Cloud Storage files into the matching date partition using a partition decorator (table$YYYYMMDD) with WRITE_TRUNCATE. Each partition is replaced atomically, the load never rescans history, and reruns are safe because every load truncates only its own partition. The pattern covers late-arriving data while keeping scan cost proportional to the delta, not the table size.

How it works

1. The refresh_window task (io.kestra.plugin.core.flow.ForEach) iterates over the day_offsets input with concurrencyLimit: 1, processing one day at a time so partition loads never collide.
2. For each offset, load_partition (io.kestra.plugin.gcp.bigquery.LoadFromGcs) reads gs://.../events/dt=YYYY-MM-DD/*.parquet for that day and loads it into analytics.events$YYYYMMDD with format: PARQUET and writeDisposition: WRITE_TRUNCATE. The date is computed from now() and the offset via dateAdd.
3. allowFailure: true on the load lets a day with no matching files pass without failing the run.
4. The notify task (io.kestra.plugin.slack.notifications.SlackIncomingWebhook) posts a Slack confirmation summarizing how many days were refreshed.
5. A disabled daily Schedule trigger (cron: 0 5 * * *) is ready to enable, and an alert_on_failure error handler posts to Slack if the refresh fails.

What you get

• Cost and runtime that scale with the changed window, not the full table.
• Atomic, idempotent partition replacement: safe reruns with no duplicates.
• Coverage for late-arriving data via a configurable lookback window.
• Slack confirmation on success and an automatic alert on failure.

Who it's for

• Data and analytics engineers maintaining partitioned BigQuery tables.
• Platform teams optimizing warehouse spend on high-volume event tables.
• Anyone loading day-partitioned GCS exports (Parquet) into BigQuery.

Why orchestrate this with Kestra

BigQuery scheduled queries can run SQL on a cron, but they cannot orchestrate a per-day GCS-to-partition load loop, react to upstream events, retry a single failed day, or fan a result into Slack alerting and lineage. Kestra adds event and schedule triggers, per-task retries and error handlers, full execution lineage, and a declarative YAML definition you version in Git. You control exactly which partitions are rewritten and when, instead of rescanning the whole table on a fixed schedule.

Prerequisites

A day-partitioned BigQuery table (analytics.events) and GCS objects laid out under events/dt=YYYY-MM-DD/ as Parquet.

Secrets

• GCP_PROJECT_ID: GCP project hosting the dataset.
• GCP_SERVICE_ACCOUNT: service account key JSON with BigQuery job and GCS read access.
• GCS_BUCKET: bucket holding the day-partitioned source files.
• SLACK_WEBHOOK_URL: Slack incoming webhook URL for notifications and alerts.

Quick start

1. Set the four secrets above in your Kestra namespace.
2. Point the GCS from path and destinationTable at your partitioned data.
3. Set day_offsets to cover how far back late data can arrive (for example [0,-1,-2,-3,-4,-5,-6] for a week).
4. Run it, confirm the Slack message, then enable the daily trigger.

How to extend

• Widen or narrow day_offsets to trade freshness against cost.
• Swap format: PARQUET for CSV or AVRO to match your exports.
• Add a downstream transform or dbt run after refresh_window.
• Raise concurrencyLimit if your partitions are independent and you want faster backfills.

Links

• Google BigQuery plugin docs
• BigQuery orchestration with Kestra