A year ago you explained a 12% dip with one GROUP BY over one table. The question is back — same shape, 40× the data — but the data now lives in two places: history in an analytics replica, the last hour in the live table. This chapter is about answering it honestly, without pretending either place holds the whole truth.
You'll learn
Know why reporting moved to an S3 Tables analytics replica — and what the replica's watermark means
Route a read to an explicit plane: s3tables."T", dynamodb."T", or the split view hybrid."T"
See how a hybrid aggregate is recomputed over merged rows — never stitched from two partial answers
Understand why hybrid demands an append-only table, and what the studio refuses when it can't be honest
The door, built last quarter
Somewhere between the loyalty import and today, reporting queries stopped hammering the live table. Sales events now also append to BeanAndBarkSales — a ledger where a row is written once and never edited — and that ledger has an analytics replica: a zero-ETL copy in Amazon S3 Tables that Athena can query with full SQL, refreshed on a cadence.
sql
-- last quarter: move reporting off the live table (the door)CREATE ANALYTICS REPLICA IF NOT EXISTS FOR TABLE "BeanAndBarkSales" WITH (refresh_interval = INTERVAL '1' HOUR, unnest = FULL);
Provisioning it was a chapter of its own, disclosed the way you'd now expect: the studio expanded that one statement into the ordered AWS plan — PITR checks, the S3 Tables bucket and catalog, Lake Formation and Glue wiring, the IAM role, the integration — with every native call and recurring cost line previewed before consent. The replica reference walks it end to end. What matters this morning is one number it left behind: the replica's write watermark — the moment up to which the copy is complete. Right now it reads 10:02am. The promotion launched at nine; the last forty-one minutes exist only in DynamoDB.
Two planes, neither one the answer
sql
-- history: Athena SQL over the Iceberg replica — complete until 10:02amSELECT Region, SUM(Total) AS revenueFROM s3tables."BeanAndBarkSales"GROUP BY Region;-- this morning: the live table — fresh, but billed per item you readSELECT * FROM dynamodb."BeanAndBarkSales"WHERE SaleDate = '2026-08-07';
The replica alone is stale. Athena over s3tables."BeanAndBarkSales" aggregates years of history in one cheap SQL pass — and silently stops at 10:02am. The board would see the promotion missing its last 41 minutes, which this morning is the story.
The live table alone is expensive.dynamodb."BeanAndBarkSales" is fresh to the second, but aggregating all history there is the whole-ledger read the replica exists to prevent — you'd bill your way through years of rows to recount what the replica already holds.
Waiting is a missed meeting. The next refresh lands after 11.
Naming a plane is always available and always explicit — an unqualified FROM "BeanAndBarkSales" still means plain DynamoDB, and enabling a replica never silently reroutes anything. But neither leg answers Nadia by itself. The honest answer is both, split exactly where the replica's knowledge ends.
Split at the watermark
sql
SELECT Region, COUNT(*) AS orders, SUM(Total) AS revenueFROM hybrid."BeanAndBarkSales"GROUP BY Region
One question over two planesLowered
Orders and revenue by region, spanning years of replicated history and the forty-one minutes the replica hasn't seen.
sql
SELECT Region, COUNT(*) AS orders, SUM(Total) AS revenueFROM hybrid."BeanAndBarkSales"GROUP BY Region
Executes asAthena/Iceberg leg before watermark 10:02am + DynamoDB leg at/after watermark → UNION ALL → COUNT/SUM per Region recomputed over merged rows
The receipt names both native legs and labels their freshness: the DynamoDB branch is live; the S3 Tables branch is complete to the watermark.
The DynamoDB leg reads only rows at or after 10:02am — the split is what keeps the fresh leg small and the bill sane.
hybrid."BeanAndBarkSales" is a virtual view of one append-only table: rows before the watermark come from S3 Tables, rows at or after it come from DynamoDB, and the two legs merge with UNION ALL. The aggregate is then recomputed across the merged rows — not assembled from two pre-rounded partial answers, which is the difference between one honest AVG and two averages duct-taped together. Every leg, the watermark itself, and each side's freshness appear in the run's receipt: eventual consistency stops being a footnote and becomes something you can read.
On screen at 10:51: the anniversary promotion is working everywhere — and West is leading it. The region whose 12% dip once taught you GROUP BY now tops the board slide, and the number includes the order that landed while you were typing.
Recap
An analytics replica moves reporting off the live table; its watermark is the moment up to which the copy is complete — and the receipt shows it.
Planes are explicit: s3tables."T" for replica history, dynamodb."T" for live rows, unqualified names unchanged. Nothing reroutes silently.
hybrid."T" splits an append-only table at the watermark, merges the legs with UNION ALL, and recomputes aggregates over the merged rows — one honest answer, per-leg freshness disclosed.
Mutable tables are refused the hybrid path because rows could straddle the watermark and double-count. The refusal is the feature.
Watermark check2
1Why does hybrid."T" require the table to be append-only?
2A hybrid AVG(Total) runs. How is the answer produced?
Try it yourself 2
1The manual split
The stage profile isn't configured yet, so hybrid."BeanAndBarkSales" refuses. Write the explicit two-leg fallback for total revenue that spans both planes without double-counting.
One branch per plane, each naming its side of 10:02am explicitly, composed with the cross-plane UNION ALL.
Show solution
This is what hybrid automates: each leg carries its own boundary predicate on the split column, so the planes stay disjoint. The hybrid view does the same split from the profile's watermark — without you hard-coding the timestamp.
sql
SELECT SaleId, Region, Total FROM s3tables."BeanAndBarkSales" WHERE SoldAt < '2026-08-07T10:02:00Z'UNION ALLSELECT SaleId, Region, Total FROM dynamodb."BeanAndBarkSales" WHERE SoldAt >= '2026-08-07T10:02:00Z'
2Retune the cadence
The board meets weekly, not hourly — and the hybrid path covers the gap on demand. Slow the replica's refresh so it stops doing work nobody reads.
One ALTER, one setting. The studio warns if a settings change would force a full resync.
Show solution
A slower refresh widens the watermark gap — and that's fine, because the hybrid split covers it live whenever someone actually asks.
sql
ALTER ANALYTICS REPLICA FOR TABLE "BeanAndBarkSales" SET refresh_interval = INTERVAL '12' HOUR