Three Ways Teams Are Putting Cross-Cluster JOIN to Work

Part of the SoftClient4ES launch series. Previously: the launch overview, the JOIN matrix walkthrough, and the SRE cross-cluster triage story. This post is about shapes of usage, not names.

A Note on These Stories

The three teams below are illustrative composites, not customers we can name. We have no logos to drop and no quotes to attribute, so we’re not going to pretend otherwise. What’s real is the shape of each story — each maps cleanly onto one of this release’s deployment patterns and one of its pricing tiers, and each is a problem we built this release to solve. Read them as “here is who each shape is for,” not “here is a case study.”

The three shapes, from the JOIN ladder:

• Shape A — single cluster, free driver (Row 1: same-cluster cross-index JOIN)
• Shape B — cross-cluster conveyor (Row 2: move/reshape data between clusters)
• Shape C — multi-cluster federation (Row 3: one JOIN across many clusters)

Vignette 1 — The Fintech Platform Team (Shape A, Community)

Picture a small platform team at a fintech company. They run a single, well-tuned Elasticsearch cluster. Transactions live in one index; merchants live in another; risk-flags live in a third. Their analysts are fluent in SQL and allergic to Kibana’s visual query builder.

For two years, every “join transactions to merchants” request became a ticket: a developer hand-wrote a denormalization step, or worse, exported a CSV from each index and VLOOKUP’d them together in a spreadsheet. The analysts had the SQL in their heads; the gap was a query engine that would run it.

With this release they install the free JDBC driver, point DBeaver at the cluster, and write the join they always wanted:

SELECT  t.txn_id, t.amount, m.merchant_name, r.flag
FROM    transactions t
JOIN    merchants    m ON t.merchant_id = m.id
JOIN    risk_flags   r ON t.txn_id      = r.txn_id
WHERE   t.amount > 10000
  AND   r.flag = 'review';

Two JOIN keywords — exactly the Community limit. No coordinator, no second copy of the data, no ticket. The analysts iterate on their own.

Where they landed: Community (free). One cluster, two JOINs per query, well under the 10k-result cap for their ad-hoc triage queries. They’re not paying us anything, and for this shape they shouldn’t have to — the free driver is the product for a single-cluster team. (If their reporting grows past two JOINs or 10k rows per query, Pro is the dial they’d turn, not a wall they’d hit.)

Vignette 2 — The E-Commerce Analytics Team (Shape B, Pro)

Now a mid-size e-commerce company. They run a hot operational Elasticsearch cluster that serves the storefront, and a separate analytics cluster that the data team is allowed to hammer without endangering checkout. Today, getting yesterday’s completed orders from operational to analytics is a nightly Python job: scroll the orders index, look up customers, write enriched documents to the analytics cluster, handle the pagination, handle the retries, handle the schema drift when someone adds a field.

It’s two hundred lines of glue that breaks roughly once a quarter, always on a weekend.

The cross-cluster conveyor (Row 2) collapses the whole job into one statement they schedule with their existing cron:

INSERT INTO `analytics`.orders_enriched (order_id, amount, customer_name, segment)
SELECT  o.order_id, o.amount, c.customer_name, c.segment
FROM    `operational`.orders    AS o
JOIN    `operational`.customers AS c ON o.customer_id = c.id
WHERE   o.status = 'completed'
  AND   o.created_at >= CURRENT_DATE - INTERVAL 1 DAY;

The join legs are fetched from the operational cluster, the joined result is bulk-loaded into the analytics cluster, and the two-hundred-line Python job becomes a one-line SQL statement in a CronJob. They also pin a couple of dashboard rollups as Materialized Views on the analytics side, so the BI layer hits pre-computed indices.

Where they landed: Pro (€119/mo). The second cluster crosses the Community single-cluster meter, so federation moves them to Pro. Note what Pro actually buys: coverage up to five clusters — two is where this team starts, not where Pro tops out — plus the headroom on Materialized Views (50 vs 1) and result rows (1M vs 10k) that their nightly jobs need. They could add a staging cluster and a second analytics cluster tomorrow without touching their bill. The 30-day Pro trial is enough runway to retire the Python job before you have to decide anything.

Vignette 3 — The Observability Vendor (Shape C, Enterprise)

Finally, an observability vendor — a company whose product is dashboards over telemetry. They operate six regional Elasticsearch clusters for data-residency reasons — eu, us, ap, uk, ca, in — EU customer telemetry stays in EU, US in US, and so on for each region. Their internal SREs and their customer-success engineers constantly need to correlate across those regions — “show me error rates for tenant X across every region they’re deployed in” — and until now that meant a bespoke aggregation service the platform team maintained by hand.

The multi-cluster federation capability (Row 3) lets them write the correlation as SQL across the regional clusters at once — here across three of the six:

-- One correlation across three of the six regional clusters, as a catalog-qualified JOIN.
-- Each region keeps a per-tenant health rollup, so tenant_id is unique on every leg.
SELECT  eu.tenant_id,
        eu.error_count     AS eu_errors,
        us.avg_latency_ms  AS us_avg_latency,
        ap.avg_latency_ms  AS ap_avg_latency
FROM    `eu`.tenant_health AS eu
JOIN    `us`.tenant_health AS us ON eu.tenant_id = us.tenant_id
JOIN    `ap`.tenant_health AS ap ON eu.tenant_id = ap.tenant_id;

Each leg is a catalog-qualified table on its own regional cluster — `eu`, `us`, and `ap` — and the coordinator stages and joins the legs coordinator-local. (Nested SELECT and CTEs aren’t in this release, so the correlation is expressed as a direct table JOIN, not a subquery — exactly what the engine supports today.) The bespoke aggregation service gets deleted. The correlation becomes a query their support engineers can run themselves.

Two details worth stating, because the obvious version of this query is wrong. Join the rollups, not the raw event streams. Each region maintains tenant_health as one row per tenant — a Materialized View is the natural way to keep it — so tenant_id is unique on every leg. Join three raw events indices on tenant_id instead and you get a cartesian fan-out: COUNT(*) counts joined triples, not errors, and it will happily report six times the real number while the averages still look plausible. And because these are inner joins, the result covers tenants present in all three regions; a tenant deployed in EU and US only won’t appear.

Where they landed: Enterprise (from €12,000/year). Six regional clusters put them past Pro’s five-cluster meter, they need unlimited JOIN depth for their gnarlier correlations, and they want the result caps lifted entirely. Unlimited everything is exactly the Enterprise meter. The deciding factor wasn’t a feature Pro lacked — every tier has every feature — it was that their scale lives past Pro’s dials.

The Pattern Across All Three

Notice what didn’t decide any of these: nobody upgraded to unlock a feature. Every tier has cross-index JOIN, Federation, and Materialized Views. What changed tier was always scale — number of clusters, JOINs per query, result rows. That’s the quota-as-meter model working as intended: you start where you fit, free if you’re small, and you turn the dial only when your problem outgrows it.

Find Your Shape

• One cluster? Download the free driver, write your first two-JOIN query today. You’re Shape A, and you’re free.
• Moving data between two clusters? That’s Shape B — the cross-cluster conveyor — and the 30-day Pro trial covers the migration off your glue code.
• Correlating across regions? That’s Shape C — multi-cluster federation. The three-region Helm example is your starting template whatever your region count — up to five clusters you’re still in Pro territory; past five is when it becomes an Enterprise conversation.

Resources

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

Which of these three shapes is closest to your stack? Tell us in GitHub Discussions — we read them.

P.S. — The teams above are illustrative composites, not named references. The shapes, the queries, and the tiers are real; the logos are deliberately absent.