# Bean & Bark: fresh numbers from a stale replica

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

> **10:43am — seventeen minutes:** **Nadia:** *"board call at 11. I need orders and revenue by region including this morning — the anniversary promotion is the whole story and it launched at 9."*

## 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](https://dynostudio.dev/docs/dynostudio-analytics-replica/) 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:02am
SELECT Region, SUM(Total) AS revenue
FROM s3tables."BeanAndBarkSales"
GROUP BY Region;

-- this morning: the live table — fresh, but billed per item you read
SELECT * 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 revenue
FROM hybrid."BeanAndBarkSales"
GROUP BY Region
```

### One question over two planes

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 revenue
FROM hybrid."BeanAndBarkSales"
GROUP BY Region
```

_Executes as:_ Athena/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.

_Quote this example:_ https://dynostudio.dev/docs/dynostudio-bean-bark-board-hybrid/#board-numbers

`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.

> **Why hybrid demands append-only:** The split is only honest if a row lives on exactly one side of the watermark. `BeanAndBarkSales` qualifies because rows are written once and never edited. The main `BeanAndBark` table does not — a subscription whose `Status` changed after replication could exist on *both* sides and be double-counted. That's why `hybrid` needs a stage profile naming a write-monotonic, indexed split column and the current watermark; without one, the studio **refuses** and points you at explicit `dynamodb."T"` / `s3tables."T"` reads instead. A refusal here isn't a limitation — it's the tool declining to hand the board a wrong number.

**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 check**

**1. Why does `hybrid."T"` require the table to be append-only?**

- So every row lives on exactly one side of the watermark — an edited row could appear in both legs and be double-counted ✓ — the UNION ALL split is only correct if history and fresh rows are disjoint. Write-once rows guarantee it; mutable rows don't.
- Because S3 Tables can't store updated rows — the replica handles updates fine — the constraint is about the *split*: a mutable row can't be cleanly assigned to one side of the watermark.
- It's a billing optimization — the split does keep the live leg small, but that's a side benefit. The requirement exists for correctness — no double counting.

**2. A hybrid `AVG(Total)` runs. How is the answer produced?**

- Recomputed over the merged raw rows from both legs ✓ — averaging two partial averages weights them wrongly. The engine merges the rows first, then computes — one honest aggregate spanning both planes.
- Each plane returns its own AVG and the two are averaged — that's exactly the wrong-answer shape the recompute rule exists to prevent — an average of averages ignores how many rows each side held.
- Only the DynamoDB leg is used, since it's live — then history would vanish from the number. Both legs contribute rows; freshness is disclosed per leg, not used to discard one.

**Try it yourself**

**1. The 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.

_Hint:_ One branch per plane, each naming its side of 10:02am explicitly, composed with the cross-plane UNION ALL.

_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 ALL
SELECT SaleId, Region, Total FROM dynamodb."BeanAndBarkSales"
  WHERE SoldAt >= '2026-08-07T10:02:00Z'
```

**2. Retune 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.

_Hint:_ One ALTER, one setting. The studio warns if a settings change would force a full resync.

_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
```

> **Where next?:** The board saw one honest number at 11:00. The [hybrid & cross-plane reference](https://dynostudio.dev/docs/dynostudio-hybrid-queries/) has the full contract — bounded cross-plane joins, the composition boundaries, and the refusal rules — and the [replica reference](https://dynostudio.dev/docs/dynostudio-analytics-replica/) covers provisioning end to end. One chapter left in the story: a buyer walks in asking for a coffee no attribute can name. [Find the coffee they mean](https://dynostudio.dev/docs/dynostudio-bean-bark-taste-search/).

---
_Source: https://dynostudio.dev/docs/dynostudio-bean-bark-board-hybrid/ · DynoStudio Docs_