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.

Name one plane explicitly

SELECT region, SUM(total_amount) AS revenueFROM s3tables."Sales" GROUP BY region;​SELECT * FROM dynamodb."Sales"WHERE pk = 'STORE#SEA';​SELECT orderId, statusFROM 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

SELECT sale_id, total_amount FROM dynamodb."Sales"UNION ALLSELECT sale_id, total_amount FROM s3tables."Sales";​SELECT region, COUNT(*) AS orders, SUM(total_amount) AS revenueFROM hybrid."Sales"GROUP BY region;​SELECT c.customer_id, c.name, s.total_amountFROM dynamodb."Customers" cLEFT JOIN s3tables."Sales" s ON c.customer_id = s.customer_idWHERE 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.

Use cases

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

SELECT c.customer_id, c.name, s.total_amountFROM dynamodb."Customers" cLEFT JOIN s3tables."Sales" s ON c.customer_id = s.customer_idWHERE c.status = 'ACTIVE' AND s.total_amount >= 100

Related: OpenSearch indexes and queries · Analytics replicas · SELECT · Joins, subqueries & sets · keyword reference.