# KDS CDC Scaling And Incremental Curation

## Why this note exists

Current KDS CDC curated apply is correct and replay-safe for low to moderate throughput, but some targets still use slice-level refresh behavior.

Example observed at runtime:

- one `item_bump` message arrived,
- `staging.kds_item_bumps` loaded one row,
- `staging_to_curated` for `curated.kitchen_item_status_events` logged `deleted 187 rows and wrote 187 rows`.

This is expected with current SQL shape, but it is not good long-term if volume grows very large.

## Current behavior

Some KDS CDC curated targets rebuild the affected tenant/store projection from staging instead of applying only the keys changed by the current upstream batch.

For `curated.kitchen_item_status_events`, current pattern is effectively:

1. find all matching event rows for store,
2. delete matching curated rows,
3. reinsert the full current result set from staging joins.

This gives us:

- idempotent reruns,
- simple recovery semantics,
- deterministic final state,
- no need for fine-grained merge logic.

But it also gives us:

- large delete/insert churn,
- repeated scans of staging and curated tables,
- wider transaction conflict windows,
- more Redshift/Postgres serialization conflicts,
- worse latency for tiny changes,
- poor scaling at very high row counts.

## Why this does not scale to 200M rows

At very large scale, slice-level refresh becomes too expensive.

Main problems:

- one small CDC change can trigger large table rewrites,
- delete + reinsert patterns create heavy vacuum/churn pressure,
- long-running transactions increase conflict probability,
- write amplification becomes much larger than actual business change,
- per-message near-real-time processing degrades as slice size grows.

If tenant/store/day slices become large enough, this pattern becomes operationally noisy and cost-heavy even if correctness remains acceptable.

## Target direction

Move curated apply from full slice refresh to batch-keyed incremental apply.

Core idea:

- each upstream batch already knows which business keys changed,
- curated stages should only touch rows affected by those keys,
- delete/merge/upsert should be scoped to current changed keys, not the full store projection.

For KDS CDC this usually means driving curated work from current batch identities such as:

- `order.guid`
- `item.guid`
- `item.order_guid`
- `item_bump.guid`
- `item_bump.item_guid`

## Recommended model

### 1. Keep staging append-or-replace by batch

Staging can remain batch-oriented and retry-safe.

That means:

- current load stages still write normalized rows into staging,
- reruns remain safe,
- staging continues to be source for curated transforms.

### 2. Add changed-key scope per curated target

Each curated target should compute a small changed-key set from the current upstream batch.

Examples:

- `curated.kitchen_orders`: keys = changed `order.guid`
- `curated.kitchen_order_items`: keys = changed `item.guid` or changed `item.order_guid`
- `curated.kitchen_item_status_events`: keys = changed `item_bump.guid` and possibly changed parent `item.guid`

Then curated SQL should only delete/reinsert or merge rows for those keys.

### 3. Prefer key-scoped delete + insert or merge

Instead of deleting whole store slice, do one of these:

- delete curated rows where primary key is in current changed-key set, then insert recomputed rows for same keys,
- or use `MERGE` / upsert semantics when warehouse engine and target shape support it well.

This reduces work from:

- "rewrite everything for this store"

to:

- "rewrite only rows touched by this batch".

### 4. Separate current-state targets from history targets

Not every curated target has same shape.

- current-state tables should support key-scoped replacement/upsert,
- history/event tables may prefer append-only ingestion plus dedupe guards.

`kitchen_item_status_events` should be reviewed carefully here because it behaves more like event/state-transition data than a tiny mutable dimension.

### 5. Partition by natural boundaries

At larger scale, partitioning should align with common isolation boundaries such as:

- tenant,
- business date,
- maybe store,
- maybe event date for history-heavy targets.

This reduces scan size and conflict scope.

## Suggested implementation plan

### Phase 1: reduce churn without redesigning whole pipeline

Short-term improvement:

- keep current stage model,
- add changed-key extraction from current upstream batch details,
- update curated target SQL to delete/reinsert only affected keys.

This is the best next step because it preserves existing replay model and requires smaller code changes.

### Phase 2: add explicit curated apply strategy per target

Introduce target metadata such as:

- `FullSliceRefresh`
- `ChangedKeyReplace`
- `Merge`
- `AppendOnly`

This makes target behavior explicit instead of embedding all strategy decisions inside handwritten SQL only.

### Phase 3: optimize heavy targets independently

For highest-volume targets:

- review physical table design,
- add narrower indexes/partitioning,
- isolate event-history workloads from current-state workloads,
- consider larger batch windows for heavy event streams if near-real-time is not required.

## Operational guidance

Until incremental curated apply is implemented, expect these symptoms on some targets:

- row counts in logs much larger than incoming CDC count,
- occasional serializable conflicts under concurrent slice processing,
- successful retry/recovery even after one failed attempt,
- higher churn on event-like curated tables.

These symptoms do not necessarily mean data loss. They usually mean current SQL is doing more work than the incoming change size suggests.

## Recommendation

For KDS CDC go-forward work, preferred evolution is:

1. keep durable landing, replay, and stage orchestration as-is,
2. change `staging_to_curated` from slice-level refresh to changed-key incremental apply,
3. apply target-specific strategies for high-volume curated tables,
4. treat full-store refresh as fallback/recovery path, not default hot-path behavior.

That approach keeps correctness and recovery model, while making throughput and cost much more realistic for very large datasets.