# Hybrid and cross-plane queries — compose DynamoDB, S3 Tables and OpenSearch

DynoStudio can route one read to **DynamoDB**, an **S3 Tables analytics replica**, or **OpenSearch**, then compose a small set of bounded cross-plane shapes. Every run names the native legs, the client-side merge, and the freshness of each source. An unqualified table name is unchanged and stays on the ordinary DynamoDB path.

> **Professional and read-only:** Provider-qualified, hybrid, and cross-plane execution is Professional. The hybrid engine accepts reads only: writes and DDL run against their dedicated control-plane paths, never through `dynamodb.`, `s3tables.`, `opensearch.`, or `hybrid.` query sources.

## Name one plane explicitly

```sql
SELECT region, SUM(total_amount) AS revenue
FROM s3tables."Sales" GROUP BY region;

SELECT * FROM dynamodb."Sales"
WHERE pk = 'STORE#SEA';

SELECT orderId, status
FROM opensearch."open-orders"
WHERE notes MATCH 'urgent refund' LIMIT 25;
```

- **`dynamodb."T"`** runs against the live base table through the DynamoDB provider.
- **`s3tables."T"`** runs as Athena SQL over the Iceberg analytics replica. The stage needs its Athena results S3 location configured.
- **`opensearch."index"`** runs as OpenSearch Query DSL against the derived search projection. `FROM OPENSEARCH "index"` is the dedicated shorthand.
- **Unqualified `FROM "T"`** keeps the existing DynamoDB behavior; enabling another plane never silently reroutes it.

## Supported compositions

```sql
SELECT sale_id, total_amount FROM dynamodb."Sales"
UNION ALL
SELECT sale_id, total_amount FROM s3tables."Sales";

SELECT region, COUNT(*) AS orders, SUM(total_amount) AS revenue
FROM hybrid."Sales"
GROUP BY region;

SELECT c.customer_id, c.name, s.total_amount
FROM dynamodb."Customers" c
LEFT JOIN s3tables."Sales" s ON c.customer_id = s.customer_id
WHERE c.status = 'ACTIVE' AND s.total_amount >= 100;
```

- **Cross-plane `UNION ALL`** — each branch must name one plane. DynoStudio executes the legs independently, unions the column set, reconciles types, keeps duplicates, and merges client-side.
- **Virtual `hybrid."T"`** — splits one append-only table at the replica write watermark: older rows go to S3 Tables, rows at or after the watermark go to DynamoDB, then the results merge.
- **Hybrid aggregates** — `COUNT`, `SUM`, `MIN`, `MAX`, and `AVG`, with optional `GROUP BY`, are recomputed over the merged raw rows so the answer spans both legs rather than adding two rounded partial answers.
- **Bounded cross-plane join** — one `INNER` or `LEFT` equi-join between two different provider-qualified planes. Projection columns must be alias-qualified; each `WHERE` conjunct must belong to exactly one side; each leg is capped at 5,000 rows before the client-side hash join.

## Hybrid table setup and freshness

A virtual hybrid table needs a stage profile naming a **write-monotonic, indexed split column** and the replica's current write watermark. The table must be append-only for the `UNION ALL` split: mutable rows could exist on both sides after replication and be double-counted. Without a profile or watermark, DynoStudio refuses and points to explicit `dynamodb."T"` / `s3tables."T"` reads instead.

The receipt identifies every native leg and labels its freshness: the DynamoDB branch is live, while S3 Tables and OpenSearch may lag. The merge never upgrades an eventually consistent result into an authoritative one.

## Boundaries that refuse before execution

- Plain `UNION`, `INTERSECT`, and `EXCEPT`; or a union-level `ORDER BY` / `LIMIT` across the combined result.
- A `UNION ALL` leg that itself spans planes or names `hybrid."T"`.
- `RIGHT`, `FULL`, or `CROSS` joins; more than one join; same-plane joins; non-equality or compound `ON`; bare `*`; unqualified projection columns; and predicates that reference both aliases.
- Holistic aggregates or aggregate shapes outside the supported `COUNT` / `SUM` / `MIN` / `MAX` / `AVG` plus `GROUP BY` contract.
- CTE envelopes and any non-`SELECT` statement.

> **Bound the legs, not only the final display:** Every provider executes before the client-side merge. Keep each branch selective; a cross-plane join or hybrid-split leg above 5,000 rows refuses. Moving a LIMIT only to a hypothetical combined result would not protect the native legs, which is why that shape is not accepted.

## Use cases

### Query recent writes and historical analytics as one table

The S3 Tables replica holds history but lags recent writes. A configured watermark splits the append-only Sales table without double-counting.

```sql
SELECT region, COUNT(*) AS orders, SUM(total_amount) AS revenue
FROM hybrid."Sales"
GROUP BY region
```

_Executes as:_ Athena/Iceberg leg before watermark + DynamoDB leg at/after watermark → UNION ALL → grouped aggregate recomputed client-side

- The receipt shows the watermark and the two freshness classes.

_Quote this example:_ https://dynostudio.dev/docs/dynostudio-hybrid-queries/#fresh-plus-history

### Join authoritative customers to bounded replica sales

Attach recent analytical sales values to active customer records without draining either source unbounded.

```sql
SELECT c.customer_id, c.name, s.total_amount
FROM dynamodb."Customers" c
LEFT JOIN s3tables."Sales" s ON c.customer_id = s.customer_id
WHERE c.status = 'ACTIVE' AND s.total_amount >= 100
```

_Executes as:_ DynamoDB SELECT leg + Athena SELECT leg → bounded client-side LEFT hash join

- Projection is alias-qualified and each WHERE condition belongs to one leg, so each provider receives only its own filter.

_Quote this example:_ https://dynostudio.dev/docs/dynostudio-hybrid-queries/#customers-with-replica-sales

Related: [OpenSearch indexes and queries](https://dynostudio.dev/docs/dynostudio-opensearch-index/) · [Analytics replicas](https://dynostudio.dev/docs/dynostudio-analytics-replica/) · [SELECT](https://dynostudio.dev/docs/dynostudio-select/#cross-plane-union-all) · [Joins, subqueries & sets](https://dynostudio.dev/docs/dynostudio-partiql-joins/) · [keyword reference](https://dynostudio.dev/docs/dynostudio-partiql-keywords/).

---
_Source: https://dynostudio.dev/docs/dynostudio-hybrid-queries/ · DynoStudio Docs_