Kafka to BigQuery: How to Move Your Data

Moving data from Kafka to BigQuery puts your event streams somewhere they can be queried with SQL and joined against everything else. It is a common and useful pipeline, and it is also the one where the mental model matters most, because Kafka is unbounded and an Airbyte sync is not.

The connector is a consumer that runs on a schedule and listens for a configured length of time, then stops. It is not a continuously running consumer. That single fact drives the polling time setting, the consumer group configuration and the risk of falling permanently behind, and this guide covers all three.

Kafka to BigQuery at a glance:

Why move data from Kafka to BigQuery?

Two situations account for most of these pipelines.

The first is permanence. Kafka topics have a retention window, usually days rather than years, because Kafka is a transport rather than an archive. The events flowing through it are frequently the most detailed record your organisation has of what users and systems actually did, and they are being deleted on a rolling basis. BigQuery is where that history can live indefinitely.

The second is joining. An event stream on its own answers narrow questions. The same events next to customer records, subscription data and support history answer commercial ones. If you need sub-second processing rather than analysis, a stream processor is the right tool and this pipeline is not competing with it.

What do you need before you start?

One of these can block the project outright, so check it first:

A broker security setup the connector supports. You choose a protocol covering how the connector talks to brokers and how it authenticates. Community reports indicate the Kafka connectors cannot authenticate against brokers secured with SASL_SSL and SCRAM, because that combination needs keystore configuration the connector does not expose. If that is your cluster, verify against current documentation before designing anything around this.

Topics that already exist, and read permission on them. The connector reads rather than creates, so the topics must be there first and the Airbyte user must be allowed to consume from them.

A decision on message format. JSON or Avro. Avro deserialises through the Confluent API and needs a schema registry URL, optional credentials and a subject name strategy. JSON does not support a schema registry at all. The Kafka source documentation covers both paths.

A dedicated consumer group ID. Kafka tracks read position per consumer group, so give Airbyte its own rather than sharing one with an existing application. Sharing means the two compete for partitions and neither sees everything.

On the BigQuery side you need a project, a dataset, and a service account with the BigQuery User and BigQuery Data Editor roles. Set the dataset location to match anything you plan to join against, since it cannot be changed later. If your brokers restrict inbound traffic by IP, add the Airbyte Cloud IP addresses to the allow list.

How do you build a Kafka to BigQuery pipeline in Airbyte?

Step 1: Measure your topic throughput

Before configuring anything, find out how many messages per minute your topics produce and what the retention window is. Those two numbers determine your polling time and your sync frequency, and getting them wrong is the failure mode that loses data rather than the one that throws an error. Ten minutes with whoever runs the cluster saves considerably more later.

Step 2: Configure the Kafka source

Click Sources in the left navigation, then New Source, and select Kafka, following adding a source. Supply the bootstrap servers, protocol and credentials, the message format, a topic pattern where each matching topic becomes a stream, and your group ID. Set a client ID as well, which is an arbitrary label that appears in broker-side request logging and makes it obvious which traffic is Airbyte's when someone is looking at cluster load.

Step 3: Configure the BigQuery destination

Click Destinations, then New Destination, and select BigQuery, following adding a destination. Supply the project, dataset and service account key, then pick a loading method. Batched standard inserts is the default and the simplest. Cloud Storage staging gives you control over where staging files live at the cost of a bucket and an HMAC key, and is worth considering on high-volume topics.

Step 4: Set polling time and frequency together

Click Connections, then New connection, select your topics and a sync mode. Polling time is how long, in milliseconds, the connector tries to poll for messages per sync. Treat it and the schedule as one decision rather than two: a short poll on a frequent schedule and a long poll on an infrequent one behave very differently against a busy topic.

Use the test topic field during setup to confirm the connector can actually consume before you point it at production topics. It exists precisely so that a broken protocol or permission configuration surfaces immediately rather than as an empty sync.

What happens if the connector cannot keep up?

This is the failure that matters, and it does not announce itself. Each sync consumes for the configured polling duration and then stops, committing its position in the consumer group. If messages arrive faster than your syncs consume them, the gap between your committed offset and the head of the topic grows on every run.

Consumer lag on its own is survivable, because Kafka retains messages and you can catch up. What is not survivable is lag exceeding retention. Once the broker deletes messages you have not read, they are gone, and nothing in the pipeline reports this: syncs keep succeeding, they simply resume from the oldest message still present. Your BigQuery table has a hole in it and nothing says so.

So monitor consumer lag for Airbyte's group ID using your normal Kafka tooling, and alert on it. That is the check that tells you this pipeline is healthy. Sync success in Airbyte does not. If lag is growing, lengthen polling time, sync more often, or both, and consider whether the highest-volume topics belong in a separate connection with their own schedule.

Should you use JSON or Avro?

Usually your producers have already decided, so this is a question of confirming rather than choosing. It still affects what you get in BigQuery.

Avro deserialises through the Confluent API against a schema registry, which means the connector knows the shape of your messages and a producer changing that shape is a visible, versioned event rather than a surprise. You configure the registry URL, optional credentials, and the subject name strategy used to select the schema. For a warehouse pipeline that structure is genuinely valuable.

JSON does not support a schema registry here. It works, and it means schema management becomes your problem: a producer adding or renaming a field is something you discover downstream. If your topics are JSON, plan on landing the payload and doing the field extraction in a BigQuery view, keeping the raw column so you can reprocess when the shape changes.

Frequently asked questions

Is this a real-time pipeline?

No. The connector polls for a configured duration on a schedule rather than consuming continuously. For sub-second processing use a stream processor; this pipeline is for analysis and permanence.

How do I know if I am losing messages?

Monitor consumer lag for Airbyte's group ID in your Kafka tooling. If lag exceeds topic retention, messages are deleted before being read and syncs still report success, so Airbyte alone will not tell you.

Can I use a schema registry with JSON messages?

No. Schema registry support applies to Avro, which deserialises through the Confluent API. JSON messages are read without registry support.

Should Airbyte share a consumer group with my application?

No. Kafka tracks read position per consumer group, so sharing means the two compete for partitions and neither receives every message. Give Airbyte its own group ID.

Can I do this without writing code?

Yes. The setup above is entirely UI-driven. If your topics carry JSON you will want a BigQuery view to extract fields from the payload, which is a short piece of SQL.

Get your Kafka data into BigQuery

Check your broker security setup first, because it can rule the pipeline out. Then measure throughput and retention, set polling time and schedule as a single decision, give Airbyte its own consumer group, and alert on consumer lag rather than trusting sync status.

Airbyte's connector catalog includes 600+ pre-built connectors, so the same warehouse can hold your event streams alongside your operational data. For Kafka as a destination rather than a source, see BigQuery to Kafka, and for another source where retention drives the design, Sentry to BigQuery.

Start syncing now →