Elasticsearch: Sync Documents, Stop Treating Aggregations as Facts

The support search index started returning nothing for amounts over 100. Mapping had amount as text because the first document that week arrived with "amount": "pending". Dynamic mapping believed it. Later numeric values were analyzed as strings. A terms aggregation on that field became the Friday ops review number. It did not match the warehouse by 18 percent.

We had also been appending documents with autogenerated ids from a Connect sink. A connector restart replayed a day of CDC and doubled the index. Search felt “busy.” Counts were fiction.

I run Elasticsearch as a sync target for findability. I do not run it as the system that defines revenue. Mapping is declared. Document ids are stable. Reindex from silver is how we recover, not a shameful batch job.

Declare the mapping or inherit the first row

Dynamic mapping is convenient in a demo. In production the first dirty document wins the type. I put an index template on anything a pipeline writes.

from elasticsearch import Elasticsearch

es = Elasticsearch("https://search.internal:9200")

INDEX = "orders-search"
TEMPLATE = {
    "index_patterns": ["orders-search*"],
    "template": {
        "mappings": {
            "dynamic": "strict",
            "properties": {
                "order_id": {"type": "keyword"},
                "amount_cents": {"type": "long"},
                "status": {"type": "keyword"},
                "body": {"type": "text"},
            }
        }
    },
}

def ensure_template() -> None:
    es.indices.put_index_template(name="orders-search", body=TEMPLATE)

strict fails a new field instead of inventing a type. That failure is a pipeline incident, which is better than a silent text mapping. Additive fields go through a template change you review.

flowchart TD
  src["OLTP / CDC"] --> id["Stable document id"]
  id --> es["Elasticsearch"]
  silver["Silver tables"] --> re["Nightly reindex"]
  re --> es
  es --> search["Search and filters"]
  es -->|"terms agg as truth"| lie["Wrong exec number"]
  silver --> wh["Warehouse metrics"]

Search reads the index. Facts read silver. A reindex is how the index catches up after a mapping mistake.

Idempotent ids, CDC, reindex

If the source is CDC, the document id is the primary key (and maybe an index name that includes a version). Replay then overwrites. Autogenerated ids make at-least-once delivery into duplicate hits. The same apply rule as Kafka Connect in production and Debezium on Postgres: assume duplicates, make the sink a upsert on a key.

The nightly reindex is not an admission that streaming failed. Streaming drifts (mapping, a bad transform, a deleted connector). Reindex from the warehouse tables you already trust is the rebuild. I keep it cheap: filter to the fields search needs, not SELECT * of gold.

Aggregations are not the warehouse

I will use a date histogram in Kibana to see whether ingest is alive. I will not let an exec scorecard be an ES sum. You lack grain tests, slowly changing dimensions, and a semantic definition anyone else can review. Put the metric in SQL, or in the semantic layer, and let ES stay a query index.

OpenSearch vs Elasticsearch is which contract you signed and which plugins you are allowed to run. It is not a modeling difference. I do not pick one in a blog post to sell a license. I pick the one the platform team can patch, and I apply the same mapping and id rules.

Pitfalls

Dynamic templates on a mixed-type field. The first writer wins. Strict mappings plus a DLQ for rejects.

Autogenerated _id on a CDC topic. Replay doubles the corpus.

Using ES as the only copy of a document. A bad reindex should be recoverable from silver, not from hope.

Heavy aggregations on hot search nodes during business hours. You will steal query latency from the app. Use the warehouse.

Treating the fork debate as architecture. Mapping and sync do not care which trademark is on the cluster.

What this means for your pipelines

Elasticsearch is a serving index I can rebuild. The warehouse is where I will argue about amounts. I pin mappings, I upsert on a real id, and I keep a reindex from silver for the week dynamic mapping or a poison payload wins.

The 18 percent gap was not a search relevance problem. It was a number that should never have left a terms aggregation. Sync the document. Measure the business in SQL.