Stream Pub/Sub Events into BigQuery with Micro-Batching

Stream Google Cloud Pub/Sub messages into BigQuery in near real time without writing a consumer service or fighting per-row streaming quotas. This blueprint uses a Pub/Sub trigger to micro-batch incoming events, converts them to newline-delimited JSON, and appends each batch to a raw BigQuery landing table in a single load job. It solves the classic event-ingestion problem: keeping analytics data fresh while staying well under BigQuery's streaming and DML limits and following the ELT pattern of capture first, parse downstream.

How it works

1. The io.kestra.plugin.gcp.pubsub.Trigger polls the kestra-events-sub subscription on a fixed interval (PT1M) and starts the flow with the batch of messages it consumed, up to maxRecords (1000) per run, serialized as JSON.
2. The to_json task (io.kestra.plugin.serdes.json.IonToJson) converts the consumed batch from Kestra's internal ION format into the newline-delimited JSON that BigQuery accepts.
3. The load_to_bigquery task (io.kestra.plugin.gcp.bigquery.Load) appends the batch to the analytics.pubsub_events_raw table with format: JSON, autodetect: true, createDisposition: CREATE_IF_NEEDED, and writeDisposition: WRITE_APPEND, so the table is created on first load and grows append-only.
4. If a load fails, the alert_on_failure error handler (io.kestra.plugin.slack.notifications.SlackIncomingWebhook) posts the failed execution to Slack.

What you get

• Near real-time ingestion of Pub/Sub events into BigQuery with sub-minute latency.
• One BigQuery load job per batch instead of per-row inserts, avoiding streaming and DML quota limits.
• A self-creating raw landing table with schema autodetection.
• Built-in failure alerting to Slack.

Who it's for

• Data engineers building event pipelines on Google Cloud.
• Analytics teams who want fresh clickstream, IoT, or application events in BigQuery.
• Platform teams replacing a hand-written Pub/Sub-to-BigQuery consumer with declarative YAML.

Why orchestrate this with Kestra

Pub/Sub can push or pull messages, but it has no scheduler that batches events, transforms them, loads them, and alerts on failure as one observable pipeline. Kestra adds an event-driven trigger that fires on new messages, retries on transient errors, full execution lineage and logs, and a declarative YAML definition you can version and review. The micro-batching cadence (interval plus maxRecords) is the specific gap Pub/Sub's own delivery cannot fill: it turns a continuous stream into clean, quota-friendly load jobs.

Prerequisites

An existing Pub/Sub topic (events) with a subscription (kestra-events-sub) and a BigQuery analytics dataset. The analytics.pubsub_events_raw table is created automatically on the first load.

Secrets

• GCP_PROJECT_ID: GCP project hosting the Pub/Sub topic and BigQuery dataset.
• GCP_SERVICE_ACCOUNT: service account key JSON with BigQuery and Pub/Sub access.
• SLACK_WEBHOOK_URL: Slack incoming webhook URL for failure alerts.

Quick start

1. Set the secrets above in your Kestra namespace.
2. Point the trigger topic and subscription at your own Pub/Sub resources.
3. Enable the trigger (it ships disabled: true so importing the blueprint does not connect with placeholder config).
4. Publish a few messages and confirm rows land in analytics.pubsub_events_raw.

How to extend

• Lower interval for fresher data or raise maxRecords for larger, cheaper batches.
• Add a downstream io.kestra.plugin.gcp.bigquery.Query or dbt task to parse the raw message envelope (data, attributes, messageId) into typed columns.
• Swap the destination table per environment or partition the raw table by ingestion time.

Links

• Plugin docs: https://kestra.io/plugins/plugin-gcp
• Orchestrate BigQuery with Kestra: https://kestra.io/orchestration/bigquery
• Orchestrate Pub/Sub with Kestra: https://kestra.io/orchestration/pubsub