Replicate Elasticsearch Indices to BigQuery

What you’ll build

A live Elasticsearch → BigQuery pipeline that backfills existing documents using sliced Point-in-Time (PIT) snapshots, then continuously ingests new and updated documents by polling a monotonic watermark field. New writes in Elasticsearch appear in BigQuery within cdc_poll_interval + cdc_safety_lag (≈ 30 s by default).

Prerequisites

Before starting, complete the setup steps in Elasticsearch source prerequisites and BigQuery IAM setup.

You’ll also need:

• Elasticsearch 7.10+ with _source enabled on every replicated index
• A monotonic field on every replicated index — default @timestamp (configurable)
• An API key or user with monitor cluster privilege and read, view_index_metadata on the target indices

---

Step 1 — Set up BigQuery

# Enable the API
gcloud services enable bigquery.googleapis.com --project=my-project

# Create the destination dataset
bq mk --dataset --location=US my-project:replication

# Create a service account and grant access
gcloud iam service-accounts create nanosync --project=my-project

gcloud projects add-iam-policy-binding my-project \
  --member="serviceAccount:nanosync@my-project.iam.gserviceaccount.com" \
  --role="roles/bigquery.dataEditor"

gcloud projects add-iam-policy-binding my-project \
  --member="serviceAccount:nanosync@my-project.iam.gserviceaccount.com" \
  --role="roles/bigquery.jobUser"

# Download the key
gcloud iam service-accounts keys create nanosync-bq-key.json \
  --iam-account=nanosync@my-project.iam.gserviceaccount.com

---

Step 2 — Create the Elasticsearch API key

POST /_security/api_key
{
  "name": "nanosync",
  "role_descriptors": {
    "nanosync_reader": {
      "cluster": ["monitor"],
      "indices": [
        {
          "names": ["orders-*"],
          "privileges": ["read", "view_index_metadata"]
        }
      ]
    }
  }
}

Copy the encoded field from the response — that’s the value you’ll pass as api_key.

Verify the configured indices are readable and the watermark field exists:

curl -H "Authorization: ApiKey $ES_API_KEY" \
  "https://es-1.prod:9200/orders-*/_mapping?filter_path=*.mappings.properties.@timestamp"

---

Step 3 — Create the config files

Nanosync uses two separate YAML files: one for the server (database, notifications) and one for pipeline definitions (applied separately).

Create server.yaml:

# Server config — passed to nanosync start
# No pipeline definitions here.

Create pipelines.yaml:

connections:
  - name: my-elasticsearch
    type: elasticsearch
    dsn: "https://es-1.prod:9200"
    properties:
      api_key: "${env:ES_API_KEY}"
      ca_cert: "/etc/ssl/es-ca.pem"

  - name: my-bigquery
    type: bigquery
    properties:
      project_id:       my-project
      dataset_id:       replication
      credentials_file: /path/to/nanosync-bq-key.json

pipelines:
  - name: orders-to-bigquery
    replication_type: cdc_backfill
    source:
      connection: my-elasticsearch
      properties:
        index_pattern:       "orders-*"
        exclude_indices:     "orders-archive-*"
        snapshot_slices:     "0"           # 0 = auto (primary shard count)
        page_size:           "1000"
        cdc_watermark_field: "@timestamp"
        cdc_poll_interval:   "2s"
        cdc_safety_lag:      "30s"
    sink:
      connection: my-bigquery
      properties:
        table_id: orders

---

Step 4 — Test your connections

export ES_API_KEY=...

nanosync test connection my-elasticsearch
# ✓  my-elasticsearch connected in 41 ms

nanosync test connection my-bigquery
# ✓  my-bigquery connected in 34 ms

If either fails, the error tells you exactly what’s wrong:

✗  my-elasticsearch: preflight: index "orders-2026.06" has _source disabled

Fix the issue before continuing.

---

Step 5 — Start the server and apply pipelines

# Start the server (handles state store, notifications, etc.)
nanosync start dev --config server.yaml

nanosync serving — Ctrl-C to stop
  REST API: http://localhost:7600/v1
  Web UI:   http://localhost:7600/app

Then apply your pipeline definitions in a separate terminal:

export ES_API_KEY=...

nanosync apply --file pipelines.yaml

The pipeline starts immediately. Nanosync opens a PIT per primary shard and streams the snapshot in parallel, then switches to watermark polling once every partition reports complete.

---

Step 6 — Watch it work

nanosync monitor

NAME                 SOURCE              TARGET     STATUS          LAG    EV/S
orders-to-bigquery   my-elasticsearch    bigquery   ● snapshotting  —      —
                                                    [████████░░░░]  67%  1,800,000 docs

orders-to-bigquery   my-elasticsearch    bigquery   ● live CDC      28s    1,250

The CDC LAG in steady state hovers around your configured cdc_safety_lag — that is the trailing-edge offset of the polling window, not a sign of trouble.

---

Step 7 — Kill it and restart

Press Ctrl-C to stop the server, then restart it:

nanosync start dev --config server.yaml

INF pipeline resuming  name=orders-to-bigquery  watermark=2026-06-06T18:14:22.117Z
INF cdc polling        index=orders-2026.06 last_seen=2026-06-06T18:14:22.117Z

No re-snapshot. PIT IDs are not persisted (they expire server-side), so nanosync opens a fresh PIT and resumes polling from the last watermark saved to the checkpoint store. Snapshot partitions that completed before the restart are skipped; only partitions that were running re-run.

---

Production considerations

Watermark lag monitoring

Watch nanosync_source_elasticsearch_watermark_lag_seconds{index} — in steady state it should hover around cdc_safety_lag. If it grows without bound, either:

• The poll cycle can’t keep up with write volume — decrease cdc_poll_interval or increase page_size.
• The configured watermark field stopped advancing on the source — confirm new documents include @timestamp (or your override).

Schema drift

If you add a field to source documents that isn’t in the cached Elasticsearch mapping, nanosync emits a SchemaDelta event and adds the column to BigQuery on the next batch. Type changes pause the pipeline at pending_schema_approval — manual resolution is required. See Configuration reference for schema_drift_mode.

Deletes

Because polling can’t observe document removals, deletes in Elasticsearch leave dangling rows in BigQuery. Options:

• Tolerate it — most analytical workloads are append-mostly; row counts diverge slowly.
• Schedule a reconciliation job — periodically diff _id sets and issue DELETE statements in BigQuery yourself.
• Wait for delete_detection: full_diff — planned post-v1.

_source discipline

The connector cannot reconstruct documents from doc-values alone. Any index added to the pipeline must have _source enabled in its mapping. The preflight check catches this on nanosync apply — fix it at the index template level so future indices inherit it.

How to know it’s working

# Check lag is in the right ballpark (≈ cdc_safety_lag, not growing)
nanosync monitor --pipeline orders-to-bigquery

# Verify rows in BigQuery
bq query --use_legacy_sql=false \
  'SELECT COUNT(*) FROM `my-project.replication.orders`'

# Compare with source
curl -H "Authorization: ApiKey $ES_API_KEY" \
  "https://es-1.prod:9200/orders-*/_count"

Source and destination counts should match modulo the cdc_safety_lag window and any deletes that occurred since the snapshot.

---

What’s next

• Configuration reference — all YAML pipeline options
• CLI reference — full command listing
• Elasticsearch source docs — PIT, watermark polling, schema mapping, limitations
• BigQuery sink docs — IAM, partitioning, DELETE handling