Singlestore to BigQuery: How to Move Your Data

Moving SingleStore into BigQuery is an unusual request on its face, because SingleStore already runs analytical queries quickly. The reasons people do it are cost and company: memory-optimised storage is expensive to fill with history, and your other sources are not in SingleStore.

This guide covers the managed path with Airbyte. One connector limitation shapes the whole design and matters more here than on most databases, because SingleStore is usually holding data that changes constantly rather than an append-only log.

SingleStore to BigQuery at a glance:

Why move data from SingleStore to BigQuery?

Two situations account for most of these pipelines, and neither is that BigQuery queries faster.

The first is tiering by cost. SingleStore earns its price by keeping working data in memory, which is exactly the wrong economics for three years of history nobody queries weekly. BigQuery's separation of storage from compute makes it a natural home for that cold tail.

The second is joining. Your CRM, your advertising platforms and your support system are not in SingleStore, and the questions people actually ask span all of them. A warehouse is where that meeting happens, and it is not a judgement on the database you are reading from.

What do you need before you start?

Short list, and the cursor question is the one that decides whether the result is trustworthy:

A dedicated read-only user. With access to every table you intend to replicate. Creating one for the pipeline rather than reusing an application account means its queries are identifiable when somebody asks what is consuming the cluster. The SingleStore source documentation lists the requirements.

A cursor column per table. There is no change data capture, so incremental sync depends on a column that only increases and is set on every write. An updated timestamp maintained by your application is the usual answer, and tables without one are limited to full refresh.

Network access, with SSL or an SSH tunnel. The connector supports both, which matters because a SingleStore cluster is usually inside a private network serving an application rather than exposed to the internet.

A BigQuery service account and a decided dataset location. With the BigQuery User and BigQuery Data Editor roles. Queries can only reference datasets in the same physical location and that cannot be changed after creation, so match it to whatever you intend to join against.

Note also that this is a Marketplace connector at an early version, so alert on sync failures rather than assuming silence means success.

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

Step 1: Find your cursors and decide about deletes

Go through the tables you intend to replicate and identify a suitable cursor column on each. At the same time, ask whether rows are ever deleted or hard-updated in those tables, because the answer determines how much work the section below creates for you.

Step 2: Configure the SingleStore source

Click Sources in the left navigation, then New Source, and select SingleStore, following adding a source. Supply the host, port, database and read-only credentials, then configure SSL mode or an SSH tunnel according to how your cluster is reachable.

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. Cloud Storage staging is worth considering if your initial load covers a large history, since that is the heaviest read this pipeline will do.

Step 4: Nominate cursors and schedule off-peak

Click Connections, then New connection, select your tables, nominate a cursor field for each and choose an incremental sync mode. Schedule it when the cluster is least busy, because this is another client competing with whatever application SingleStore is serving.

Split your heaviest tables into their own connection so they do not hold up everything else, and so you can give them a different schedule if they turn out to need one.

Why does the missing CDC matter here specifically?

Because of what SingleStore is usually doing. The connector reads with a cursor rather than from a log, so a query filtered on a timestamp cannot observe an absence, and deletions never reach BigQuery. That is true of many sources and it bites harder on this one.

A warehouse-to-warehouse pipeline usually reads tables that transformation jobs rebuild wholesale, where deletion barely applies. SingleStore is typically holding live operational state behind an application, where rows are updated and removed constantly, so the gap between source and destination is real rather than theoretical.

Two remedies, and the first is much better. Ask your application team to soft-delete, marking rows with a flag and an updated timestamp rather than removing them, which makes deletions visible to a cursor. Where that is not possible, schedule a periodic full refresh on those specific tables and accept the load it costs.

How do you keep the cold tier genuinely cheap?

By remembering that BigQuery bills for the bytes a query scans, which means the storage saving you came for can be undone by how people query the result. Moving history out of memory is only a win if the queries against it are disciplined.

Output tables are partitioned daily on the extraction timestamp and clustered on that column plus the primary keys, so filtering on it prunes partitions and reduces what a query costs. On tables holding years of operational history, the difference between a query that filters and one that does not is substantial and recurring.

Establish that convention while there is one query to change rather than a set of dashboards. It matters particularly on this pipeline, because the people querying the warehouse copy are used to SingleStore, where scanning a large table was fast and carried no per-query charge at all.

Frequently asked questions

Does the SingleStore source support change data capture?

No. Incremental sync is cursor-based and append only, so deletions are not captured and updates are only seen when they advance the cursor column.

How do I get deletions into BigQuery?

Ask your application team to soft-delete with a flag and an updated timestamp, which a cursor can see. Otherwise schedule a periodic full refresh on the affected tables.

How do I keep query costs down?

Filter on the extraction timestamp, which is the partitioning column. Tell analysts explicitly, since SingleStore charges nothing per query and the habit does not transfer.

Can I change the dataset location later?

No. It is fixed at creation, and queries can only reference datasets in the same physical location, so match it to whatever you intend to join against.

Can I do this without writing code?

Yes. The setup above is entirely UI-driven. The only code you might want is a soft-delete change in your application, which is a schema decision rather than pipeline work.

Get your SingleStore data into BigQuery

Find reliable cursor columns first, and settle the deletion question with your application team, because soft deletes are far cheaper than repeated full refreshes. Create the dataset in the right location since that is permanent, run the initial load when the cluster is quiet, and teach analysts to filter on the partitioning column.

Airbyte's connector catalog includes 700+ pre-built connectors, so operational data can sit beside the commercial sources that explain it. For the same source in another warehouse, see SingleStore to Snowflake, and for a similar relational source, MySQL to BigQuery.

Start syncing now →