CockroachDB to BigQuery: How to Move Your Data

Moving CockroachDB into BigQuery takes analytical load off a distributed database built for transactions. CockroachDB survives failures and scales writes admirably, and neither of those is what you want doing a three-year aggregation while the application is serving customers.

This guide covers the managed path with Airbyte. Two things shape the build: a source covers one schema, so a multi-schema database means several of them, and replication is cursor-based, which means deletions never reach your warehouse.

CockroachDB to BigQuery at a glance:

Why move data from CockroachDB to BigQuery?

Two situations account for most of these pipelines.

The first is separating analysis from transactions. Reports scanning years of records compete with the application doing today's work, and a distributed database designed for resilient writes is an expensive place to run them. A warehouse answers those questions without troubling the cluster.

The second is joining application data to everything else. The honest caveat is change capture: this connector is cursor based, so if your analysis depends on knowing what was deleted, a source with log-based replication behaves better and PostgreSQL to BigQuery shows what that looks like.

What do you need before you start?

Four things, and the first has a clause people forget:

A read-only user, granted on future tables too. Granting select on all current tables is the obvious half; altering default privileges so the user sees tables created later is the half that prevents a silent gap. The CockroachDB source documentation gives the statements.

A list of the schemas you need. A source connects to one schema, so replicating three means three sources pointing at the same database. That is more configuration rather than more difficulty, and worth counting before promising a date.

A cursor column per table you want incrementally. Only tables with a usable cursor can sync incrementally, so a table without a reliable updated timestamp is a full refresh whether you wanted one or not.

A BigQuery dataset in the right location. Location is fixed at creation and BigQuery will not join across locations, so put this where the data you intend to join already lives.

If your cluster restricts inbound traffic by IP, add the Airbyte Cloud IP addresses to the allow list before you begin.

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

Step 1: Grant for the tables that do not exist yet

Grant select across the schema, then alter default privileges so the same grant applies to tables created in future. Skipping the second statement produces a pipeline that works perfectly and quietly stops covering new tables the moment a developer adds one, which nobody notices because nothing fails. It is one extra line at setup and it removes an entire category of silent gap.

Step 2: Configure the CockroachDB source

Click Sources in the left navigation, then New Source, and select CockroachDB, following adding a source. Supply the host, port, database, schema and credentials. Repeat for each schema, naming the sources after the schema rather than the database so the list stays readable.

Step 3: Configure the BigQuery destination

Click Destinations, then New Destination, and select BigQuery, following adding a destination. Supply the project identifier, dataset and service account credentials. Array columns are supported by the source and may be de-nested depending on the destination, so check how yours landed before modelling on top of them.

Step 4: Create the connections and keep the connector current

Click Connections, then New connection, select your streams and a sync mode. Incremental needs a cursor that moves reliably. Staying on a current connector version matters here, since a past release fixed rows being missed when inserts landed at the same moment a sync started.

Then plan how you will handle deletions, because nothing in the pipeline will.

What does one source actually cover?

One schema within one database, which surprises teams who assumed a database connection meant a database. Replicating several schemas means configuring several sources against the same host and credentials, each scoped differently, and granting access to each schema separately.

The grant for future tables is the related trap and the more dangerous one. Without altering default privileges, a table a developer creates next month is invisible to the connector, and the symptom is not an error but an absence: the pipeline succeeds, the data is incomplete, and the discovery happens when somebody asks why a new feature's table is not in the warehouse.

Incremental has its own precondition, which is a usable cursor column. Only tables offering one can sync incrementally, so a table whose rows change without a reliable updated timestamp falls back to full refresh. On a large table that is a meaningful cost, and it is worth checking which of your tables qualify before assuming the whole schema will behave the same way.

How do you stop the copy drifting from the source?

By reconciling deliberately, because cursor-based replication never sees a deletion. A row removed from CockroachDB simply stops being returned, and nothing tells the warehouse to remove its copy, so your tables accumulate records the source no longer has. The gap widens quietly for as long as the pipeline runs.

For some datasets that is acceptable or even desirable, since an append-only history of everything that ever existed answers questions the live database cannot. For others it is a correctness problem, particularly where deletion is how your application expresses that something was cancelled, or where somebody exercised a deletion right.

The practical answer is a periodic full refresh alongside the incremental one, landing into its own dated partition so you can compare identifiers and see what has disappeared. Because tables arrive partitioned on the extraction timestamp, that comparison is cheap if your queries filter on it and expensive if they do not, so build the reconciliation as a view that filters properly and run it monthly rather than leaving the question open.

Frequently asked questions

Can one source cover several schemas?

No. A source connects to one schema, so several schemas means several sources against the same database, each granted access separately.

Why is a new table missing from my sync?

The read-only user probably lacks access to tables created after setup. Alter default privileges so future tables are covered, then refresh the schema.

Will deleted rows disappear from BigQuery?

No. Replication is cursor based and sees inserts and updates only, so reconcile periodically with a full refresh into a dated partition if deletions matter.

Why can a table not sync incrementally?

It has no column usable as a cursor. Tables are only offered for incremental sync when at least one column can serve that purpose.

Can I do this without writing code?

The pipeline, yes, though the grants are database administration. The reconciliation view that finds deleted rows is SQL worth writing once.

Get your CockroachDB data into BigQuery

Grant for future tables as well as current ones, because a missing table produces no error and nobody notices. Count your schemas, since each needs its own source. Check which tables have a usable cursor rather than assuming incremental everywhere. Then decide what deletions mean for your analysis, and if they matter, build a monthly reconciliation that filters on the partitioning column rather than scanning everything.

Airbyte's connector catalog includes 600+ pre-built connectors, so application data can be analysed without troubling the cluster serving it. For the same source into an operational database, see CockroachDB to PostgreSQL, and for the same source into a column store, CockroachDB to ClickHouse.

Start syncing now →