Elasticsearch to BigQuery: How to Move Your Data

Moving data from Elasticsearch to BigQuery converts search documents into warehouse rows, so that log, event, or catalogue data sitting in an index can be joined against everything else in your warehouse and queried with SQL. Airbyte handles the extraction and loading with no code. Before you schedule anything, though, one property of the Elasticsearch source governs the entire design: it has no incremental sync.

Every sync reads the whole index from scratch. That is workable for reference data and steadily painful for a growing log store, and it is the first thing to check your use case against.

Why is there no incremental sync?

The Elasticsearch source documents full refresh sync as supported and incremental sync as not supported. There is no cursor field to configure, because the connector does not offer one.

Three consequences follow, and they are worth thinking through before you build.

Sync duration and cost scale with the size of the index rather than with how much changed. An index that doubles in size doubles the work of every sync, forever. Schedule accordingly: hourly syncs on a large log index will cost far more than the data is likely worth, and daily or weekly is often the honest answer.

Elasticsearch calls may be rate limited by the underlying deployment, and this is specific to each cluster. A repeated full scan is exactly the workload most likely to hit those limits, so watch the effect on the cluster that is also serving production search traffic.

And because every sync carries the full index, full refresh overwrite is usually the right sync mode. Append would accumulate a fresh copy of everything on each run, which is rarely what anyone wants.

If you need genuinely incremental movement out of Elasticsearch, the practical routes are to write to both Elasticsearch and your warehouse from the application, or to select only time-bounded indices where your index naming scheme rolls daily or monthly.

How do Elasticsearch field types arrive in BigQuery?

This is the second thing that surprises people, and it comes from a genuine property of Elasticsearch rather than a connector shortcoming. Elasticsearch has no dedicated array type. Any field can hold zero or more values, provided they share a type, so every field is potentially an array.

The connector's type map reflects that. A long maps to integer or array, a double to number or array, and keyword and text to a union of string, array, number, and integer. Nothing arrives as a single unambiguous scalar type.

Two specific mappings deserve attention. Elasticsearch date fields map to string or array rather than to a temporal type, so verify what lands in BigQuery before writing date arithmetic against it. And nested fields map to object or string, so deeply structured documents will need parsing downstream.

The practical response is to build a typed model in BigQuery on top of the landed tables rather than pointing dashboards at them directly. Cast and flatten once, in one place, instead of asking every analyst to handle the ambiguity in each query.

Setting up the connection

The source needs very little: an Elasticsearch endpoint URL, and credentials, which are optional depending on how your cluster is secured. The connector then discovers every index in the domain automatically, and you choose which to sync.

That automatic discovery is convenient and worth a second look before you enable streams. System and internal indices will appear alongside your own. Select deliberately rather than enabling everything, because each selected index is a full scan on every run.

If you are on Airbyte Cloud and your cluster restricts access by IP, add the Airbyte Cloud IP addresses to your allow list. Note that this source is a Marketplace-tier connector with a sync success rate that Airbyte reports as low, though its usage rate is high, so it is well exercised even if it is not first-party maintained.

Loading into BigQuery

The BigQuery destination uses direct load, writing straight to final tables. Batched Standard Inserts is the default loading method and the simplest choice, converting inserts into managed file uploads that BigQuery cleans up for you. GCS staging gives you control over staging files at the cost of a bucket, an HMAC key, and a Storage Object Admin grant, and buckets using customer-managed encryption keys are not supported.

Given the repeated full scans, GCS staging is worth considering here more than on most pipelines, simply because you get visibility into the volume moving through on each run.

Your service account needs BigQuery User and BigQuery Data Editor. Set the dataset location to match the datasets you will join against, because BigQuery queries can only reference datasets in the same physical location and the location cannot be changed after creation. Tables are partitioned daily on the extraction timestamp and clustered on that column plus the primary keys.

Frequently asked questions

Can I sync only new documents from Elasticsearch?

No. The source supports full refresh only and offers no cursor field. If your index names roll by date, selecting only recent indices is the closest practical equivalent.

Why are my columns typed as unions rather than plain values?

Elasticsearch has no dedicated array type, so any field may hold multiple values and the connector maps types accordingly. Cast and flatten in a downstream model rather than in every query.

Which sync mode should I use?

Full refresh overwrite. Since every run carries the entire index, append would stack a complete duplicate copy on each sync.

Will syncing affect my production search cluster?

It can. Elasticsearch calls may be rate limited depending on your deployment, and a repeated full scan is a heavy read pattern. Test against a replica or off-peak window before scheduling frequently.

Get your Elasticsearch data into BigQuery

Choose a sync frequency that reflects full-scan economics, select indices deliberately, and build a typed model over the landed tables. For the same source into a different analytics store, see our guide to Elasticsearch to ClickHouse. For another route into the same warehouse, see ClickHouse to BigQuery.

Start syncing now →