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.