Stop ETL'ing Elasticsearch Into Your Warehouse Just to JOIN It

Announcing cross-cluster JOIN on Elasticsearch. This is the launch post for this release — query-time cross-index and cross-cluster JOIN on Elasticsearch, plus persisted Materialized Views, on every surface you already use: the REPL, the JDBC driver, ADBC, Arrow Flight SQL, and Federation.

The Tax Nobody Budgeted For

Here is a pattern that plays out in almost every company that runs Elasticsearch at scale.

You have orders in one index. Customers in another. Your dashboard needs order amount, customer name, and customer city on the same row. In PostgreSQL this is a five-second JOIN. In Elasticsearch it is a project.

Because Elasticsearch has no native cross-index JOIN. It never has. So a team that needs cross-index data does one of two things, and both of them are expensive:

1. They build a denormalization pipeline. A Kafka consumer listens for order events, looks up the customer in another index, writes an enriched document to a third index, and then a second consumer re-enriches everything when a customer changes. Six months later it’s 2,000 lines of code, a handful of Kafka topics, a race condition that produces stale customer names one percent of the time, and an on-call rotation that exists purely to babysit the pipeline.
2. They ETL the whole thing into a warehouse. Stand up a nightly export. Copy Elasticsearch into Snowflake or BigQuery or Postgres. JOIN it there. Now you maintain a second copy of your data, a sync job, a schema-drift monitor, and a 24-hour staleness window — all so an analyst can write one JOIN that the source system refused to run.

The second option is so common it has stopped feeling like a workaround. It feels like architecture. It isn’t. It’s a tax you pay because the query engine couldn’t do the one thing you asked.

SoftClient4ES removes the tax. You write the JOIN. We run it at query time. Your data stays in Elasticsearch.

Two Superpowers Elasticsearch Doesn’t Have

This release is built around two capabilities that Elasticsearch — and a stock JDBC driver in front of it — simply cannot provide.

Superpower 1: Query-time cross-index JOIN, on every surface

This is the headline. You can write a real SQL JOIN across two (or more) Elasticsearch indices and get rows back, right now, without precomputing anything.

SELECT  e.name,
        e.salary,
        d.dept_name
FROM    jdbc_join_emp  AS e
JOIN    jdbc_join_dept AS d
        ON e.dept_id = d.dept_id
WHERE   e.salary > 5000
ORDER BY e.salary DESC;

Elasticsearch can’t do that. A plain JDBC driver that just forwards SQL to Elasticsearch can’t either — it has no engine to join with. SoftClient4ES does, because the same SQL works identically across five surfaces:

• the REPL (the interactive SQL terminal you already use for incident triage),
• the JDBC driver (DBeaver, DataGrip, Superset, your Java/Scala app),
• ADBC (in-process, Arrow-native, for JVM analytics),
• Arrow Flight SQL (columnar over gRPC for Python, DuckDB, Superset),
• and Federation (the multi-cluster coordinator).

The drivers are the free delivery channel for cross-index JOIN. Pick the surface that fits your tool; the SQL doesn’t change.

Superpower 2: Persisted Materialized Views

The second superpower is the one we shipped in the previous series: SQL-defined, continuously refreshed Materialized Views. One CREATE MATERIALIZED VIEW … AS SELECT … JOIN … statement replaces the entire Kafka denormalization pipeline, and the result is a regular Elasticsearch index you query at sub-millisecond latency.

JOIN is the query-time answer (“I need this correlation once, now”). Materialized Views are the persisted answer (“I need this correlation continuously, pre-computed, for a dashboard”). This release gives you both, from the same SQL dialect.

The JOIN Ladder: Three Rows

Not every JOIN is the same shape. This release organizes cross-index JOIN into a three-row ladder, from simplest to most powerful. The deep walkthrough lives in the next post; here is the map.

Row 1 — Same-cluster passthrough. Both tables live in the same Elasticsearch cluster. Each table becomes an Elasticsearch sub-query; the rows are joined inside an embedded DuckDB engine that ships with the driver. This is the jdbc_join_emp JOIN jdbc_join_dept example above. No coordinator, no extra infrastructure — the JOIN happens right where the SQL is parsed.

Row 2 — Cross-cluster conveyor. The source SELECT runs on one cluster, flows through a coordinator, and is bulk-loaded into a target cluster. This is the conveyor belt that lets you move and reshape data between clusters with INSERT … SELECT or CREATE TABLE AS SELECT — across cluster boundaries, in SQL.

Row 3 — Multi-source coordinator. A single JOIN spans two or more source clusters. Each leg is staged to a Parquet scratch area and exposed as a per-query DuckDB view, then joined coordinator-local. Today the sources are all Elasticsearch clusters; heterogeneous sources (Postgres, MySQL, Snowflake via attach) arrive in the upcoming release (Quarter 1 2027).

-- Row 3: one query, three regional clusters
SELECT  o.order_id, o.amount, c.customer_name, c.region
FROM    `prod_us`.orders   AS o
JOIN    `prod_eu`.customers AS c ON o.customer_id = c.id
JOIN    `prod_ap`.fx_rates  AS f ON o.currency    = f.currency;

You climb the ladder only as far as your problem needs. A team with one cluster never leaves Row 1 — and Row 1 is free for up to two JOINs per query.

Two Deployment Shapes

This release has exactly two ways to run, and which one you need depends on a single question: one cluster or many?

Shape A — Single cluster, free driver. Download the JDBC (or ADBC, or Arrow Flight SQL) artifact for your Elasticsearch version, point it at your cluster, and write cross-index JOINs. No coordinator to deploy. This is Row 1, and the Community tier gives you two JOINs per query at no cost. For a huge number of teams, this is the whole story — and it costs nothing.

Shape B — Multiple clusters, Federation. When the JOIN has to cross cluster boundaries (Row 2 and Row 3), you deploy the Federation coordinator. The public softclient4es-helm chart ships ready-made topology examples — single-cluster, three-region, and heterogeneous-ready. Community has Federation, capped at one cluster; multi-cluster federation is a Pro tier capability.

Honest Scope: What’s Shipping Now and What’s Next

We would rather you find the edges from us than from a failed query.

• Subqueries and CTEs are not in this release. If your JOIN logic needs a nested SELECT or a WITH clause, that is planned for Quarter 4 2026. For now, express the correlation as a direct JOIN.
• Heterogeneous sources arrive in the upcoming release (Quarter 1 2027). Row 3 today joins Elasticsearch clusters to other Elasticsearch clusters. Attaching Postgres / MySQL / Snowflake as JOIN sources is on the roadmap, wired through the same DuckDB engine, but it is not in this release.
• No benchmark numbers here, on purpose. The Arrow zero-copy performance story is a release of its own (a follow-up release). We are not going to wave around a “3× faster” chart we haven’t earned in public yet. What we will say is architectural: the columnar path skips the JSON serialize/deserialize round-trip entirely. The numbers come later, measured, in their own post.

Pricing: Quota-as-Meter

This release uses one pricing idea, consistently: every tier has every feature; the meter gates scale. You never hit a wall that says “this feature is Enterprise-only.” You hit a wall that says “you’ve used your quota of this feature.” Then you turn the dial up.

Pro is €119/mo or €1,190/yr (USD secondary: $129/mo or $1,290/yr). Enterprise starts from €12,000/year for unlimited everything. Start the 30-day Pro trial at portal.softclient4es.com/signup.

A few things worth being explicit about:

• Community has Federation. It is not a Pro-only feature. Community is simply metered to one cluster, which means single-cluster federation works for free and the second cluster is what crosses into Pro.
• When you exceed a meter, it tells you clearly. Each cap fails its own way, on purpose. The JOIN cap is enforced by the JOIN planner before the query runs: the plan is rejected with an error that names the JOIN count, your tier’s limit, the next tier up, and the upgrade URL — so you never wait on a query that was never going to be allowed. The result-row cap either truncates with a warning or, on an explicit LIMIT over the cap, returns an HTTP 402 — depending on configuration. The Federation cluster cap is enforced at deploy time — an over-quota federation sidecar simply refuses to start, by design, rather than silently degrading. (402 is reserved for the metered surfaces — Materialized Views and an explicit over-cap LIMIT; the JOIN cap is a planner rejection, not a 402.)
• The free-to-use extensions are exactly that — free to use. The licensed extensions are released under ELv2. You can run them in production at no cost within the Community meter.

Quickstart

Single cluster, JDBC, today:

# Maven coordinate (one fat JAR per ES version, no Scala suffix)
# app.softnetwork.elastic:softclient4es8-jdbc-driver
# JDBC URL
jdbc:elastic://localhost:9200
# Driver class
app.softnetwork.elastic.jdbc.ElasticDriver

Then, in DBeaver or your app:

SELECT e.name, d.dept_name
FROM   jdbc_join_emp  e
JOIN   jdbc_join_dept d ON e.dept_id = d.dept_id
LIMIT 100;

Prefer the terminal? The REPL installs in ten seconds and speaks the same SQL:

curl -fsSL https://raw.githubusercontent.com/SOFTNETWORK-APP/SoftClient4ES/main/install.sh | bash
softclient4es --host localhost --port 9200

Multi-cluster? Deploy the Federation coordinator with the public Helm chart and one of its topology examples (start with single-cluster, graduate to three-region).

Where to Go Next

• The deep technical walkthrough of all three JOIN rows — INNER/LEFT, predicate pushdown, GROUP BY/HAVING, INSERT…SELECT…JOIN, CTAS, ON CONFLICT upsert, prepared statements, and the cross-cluster conveyor — is the next post in this series.
• The SRE wedge story — correlating logs, metrics, and traces across three regional clusters in one query instead of tab-hopping Kibana — is the post after that.
• The measured extraction benchmark — ten million rows out of Elasticsearch against Trino, with the methodology, the raw runs, and the cells where Trino wins — is the numbers post promised above.

Resources

• GitHub: SoftClient4ES Repository
• Helm chart (Federation): softclient4es-helm
• SQL Reference: Full SQL Documentation
• Discussions: Ask Questions
• LinkedIn: SoftNetwork

How many copies of your Elasticsearch data exist today purely so something can JOIN them? If the answer is “at least one,” this release is for you.

P.S. — Cross-index JOIN ships on the REPL, JDBC, ADBC, Arrow Flight SQL, and Federation. The driver is free, two JOINs per query are free, and your data never leaves Elasticsearch.