BigQuery to Elasticsearch: How to Move Your Data

Indexing BigQuery into Elasticsearch is how a computed result becomes something users can search. A warehouse can build a product catalogue, a customer directory or a knowledge index overnight, and it is a poor place to serve a search box from, because BigQuery is built for scanning rather than for sub-second lookups.

Two things decide this pipeline before you configure anything. The Elasticsearch destination runs on Airbyte Core and PyAirbyte only, and BigQuery bills for the bytes each sync scans, which makes what you select on the source side a recurring cost rather than a one-off.

BigQuery to Elasticsearch at a glance:

Why move data from BigQuery to Elasticsearch?

Two situations account for most of these pipelines, and both involve a warehouse computing something a warehouse then cannot serve.

The first is search over a derived dataset. You have assembled a unified customer or product view in BigQuery from half a dozen sources, and now an application needs to search it by name, description or address with typo tolerance and relevance. That is a search engine's job.

The second is latency and cost per query. A search box issuing a BigQuery query on every keystroke is both slow and billed, whereas an index answers in milliseconds for the cost of the hardware you already run. If your users are analysts writing SQL rather than people typing into a box, you do not need this.

What do you need before you start?

The first item can rule the pipeline out, so confirm it before designing anything:

A self-managed Airbyte deployment. The Elasticsearch destination is available on Airbyte Core and PyAirbyte, and not on the Standard, Plus, Pro or Enterprise Flex plans. No configuration works around this.

A narrow view in BigQuery. Because BigQuery charges for bytes scanned, syncing a wide table is a recurring bill rather than a one-off transfer. Build a view containing only the fields a search result needs, which is usually a fraction of what the underlying table holds.

A service account with read access. Scoped to the dataset you are reading. Scoping the source to a single dataset also keeps the catalogue comprehensible on a project holding many.

An index mapping you have designed. Deciding which fields are analysed for full-text search and which are keyword fields for filtering, before you index a few million documents under a mapping you then have to rebuild.

Finally, a maintained timestamp column on your view, because incremental sync here is cursor-based and without one every run is a full scan you pay for.

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

Step 1: Build the document as a view

Create a view producing one row per thing a user will search for, containing the searchable text and the filterable attributes and nothing else. This does double duty: it defines your document shape, and because BigQuery is columnar it is also what keeps the recurring scan cost small. Add a maintained timestamp so incremental sync is available.

Step 2: Configure the BigQuery source

Click Sources in the left navigation, then New Source, and select BigQuery, following adding a source. Supply the project identifier, the dataset you want to scope to, and a service account key with read access.

Step 3: Configure the Elasticsearch destination

Click Destinations, then New Destination, and select Elasticsearch, following adding a destination. Supply your endpoint and authentication, and create the index with the mapping from your design first, so the connector writes into a schema you chose rather than one dynamic mapping invented on the first document.

Step 4: Use incremental sync and schedule against the source

Click Connections, then New connection, select your view, nominate the cursor field and choose an incremental sync mode. Set the frequency against how often the underlying models rebuild rather than how fresh anyone would like search to be, because syncing hourly from a table rebuilt nightly is twenty-three scans a day you pay for and gain nothing from.

Schedule the connection to run shortly after your transformation jobs finish. A search index that updates just before the data behind it changes is the worst of both arrangements.

Why is the source the expensive end?

Because BigQuery bills for the bytes each query scans, and every sync is a query. That inverts the usual intuition, where the destination is the thing you pay for and the source is just a place data comes from.

Because BigQuery is columnar, the fix is mostly about columns. A view selecting the six fields a search result displays scans a small fraction of a table with two hundred, and that saving is multiplied by however often you sync. Filtering rows helps too where the source table is partitioned and your view filters on the partitioning column.

The other lever is sync mode. Incremental scans only what the cursor admits, which is why the maintained timestamp column is worth the effort of adding. A full refresh on a search index also has a second cost: the whole index is rewritten, which on a large corpus is work your cluster feels as well as your invoice.

What happens to deleted records?

They stay in the index, because the BigQuery source is cursor-based and a query filtered on a timestamp cannot observe an absence. That matters more on a search index than almost anywhere else, since a user clicks a result and lands on something that no longer exists.

The clean answer is to keep the removed rows in your view with a flag rather than dropping them, then filter on that flag when querying the index or handle removal in whatever layer sits between your application and Elasticsearch. Your transformation layer already controls the view, so this is a modelling decision rather than a pipeline one.

The blunt answer is a periodic full refresh, which rebuilds the index and therefore reflects removals. On a warehouse source that costs a full scan each time, so use it where the corpus is small or removals are rare, and prefer the flag where neither is true.

Frequently asked questions

Why can I not find the Elasticsearch destination in Airbyte Cloud?

It is available on Airbyte Core and PyAirbyte, and not on the Standard, Plus, Pro or Enterprise Flex plans. This pipeline needs a self-managed deployment.

Why is my BigQuery bill higher than expected?

Every sync is a query billed on bytes scanned. Index a narrow view rather than a wide table, use incremental sync, and match the schedule to how often the underlying models rebuild.

Do deleted rows disappear from the index?

No. The source is cursor-based and cannot observe deletions. Keep removed rows in your view with a flag and filter on it, or schedule a periodic full refresh.

Should I index a table or a view?

A view, almost always. It defines the document shape and keeps the recurring scan cost down, which on this source are the same decision.

Can I do this without writing code?

The Airbyte setup is entirely UI-driven. You will want the BigQuery view and the index mapping, both short and both determining whether the result is any good.

Get your BigQuery data into Elasticsearch

Check your Airbyte plan first. Then build the document as a narrow view, since that decides both your search quality and your ongoing bill, design the index mapping before indexing anything, use incremental sync, and handle deletions with a flag rather than hoping the cursor notices them.

Airbyte's connector catalog includes 600+ pre-built connectors, so a computed dataset can serve search and reporting at once. For the same destination fed by a relational source, see MySQL to Elasticsearch, and for the same source feeding another warehouse, BigQuery to Amazon Redshift.

Start syncing now →