Stream Kestra audit logs from Kafka to BigQuery

Move Kestra audit logs from the kestra_auditlogs Kafka topic into a Google BigQuery table so security, platform, and analytics teams can query workflow activity in one place. Raw audit events carry nested detail objects that are awkward to analyze, so this pipeline flattens them into typed columns and loads the result into BigQuery, turning a stream of operational events into a queryable warehouse table for dashboards, audits, and troubleshooting.

How it works

1. The schedule trigger (io.kestra.plugin.core.trigger.Schedule) runs the flow on a daily cron (0 10 * * *, 10 AM UTC).
2. The consume task (io.kestra.plugin.kafka.Consume) reads up to 500 records from the kestra_auditlogs topic, using auto.offset.reset: earliest and deserializing each value as JSON.
3. The transform task (io.kestra.plugin.graalvm.js.FileTransform) drops the Kafka headers, keeps the original payload as a serialized value string, and flattens nested detail fields into columns like detail_type, detail_cls, detail_permission, detail_namespace, detail_flowId, and detail_executionId.
4. The avro task (io.kestra.plugin.serdes.avro.IonToAvro) converts the transformed Ion file into Avro using an explicit nullable schema.
5. The load task (io.kestra.plugin.gcp.bigquery.Load) writes the Avro file into BigQuery with format: AVRO, useAvroLogicalTypes: true, and writeDisposition: WRITE_TRUNCATE.

What you get

• A flattened, typed BigQuery table of Kestra audit events ready for SQL.
• Nested detail fields promoted to first-class columns plus the full original payload preserved as a JSON string.
• Schema-safe loading through Avro instead of brittle CSV or JSON imports.
• A repeatable daily refresh you can extend to near-real-time.

Who it's for

• Platform and DevOps teams tracking who changed what across namespaces and flows.
• Security and compliance teams that need an auditable, queryable history.
• Data and analytics engineers building usage dashboards on top of Kestra activity.

Why orchestrate this with Kestra

Kafka and BigQuery each ship their own tooling, but neither stitches the end-to-end path together. Kestra connects the consume, transform, serialize, and load steps as one declarative YAML flow with built-in retries, full execution lineage, and event or schedule triggers. BigQuery's scheduled queries can only operate on data already inside BigQuery, they cannot pull from Kafka, reshape nested JSON, or convert to Avro first. Kestra fills exactly that ingestion and transformation gap.

Prerequisites

• A running Kafka broker with the kestra_auditlogs topic populated.
• A Google Cloud project with BigQuery enabled and a target dataset and table.
• A GCP service account with permission to load data into BigQuery.

Secrets

• GCP_CREDS: the GCP service account JSON used by the load task to authenticate with BigQuery.

Quick start

1. Add the GCP_CREDS secret with your service account JSON.
2. Point bootstrap.servers and topic at your Kafka cluster and audit log topic.
3. Set projectId and destinationTable to your real BigQuery project, dataset, and table.
4. Save and run the flow, then query the table in BigQuery.

How to extend

• Swap the Schedule trigger for a streaming or more frequent ingestion cadence.
• Change writeDisposition to WRITE_APPEND to accumulate history instead of truncating.
• Add columns to the Avro schema and the transform script to capture more audit detail.
• Fan out the loaded table into downstream dbt models or alerting flows.

Links

• Google Cloud plugin documentation
• BigQuery orchestration guide