suhuruli commented on code in PR #57149: URL: https://github.com/apache/spark/pull/57149#discussion_r3768812448
########## docs/declarative-pipelines-programming-guide.md: ########## @@ -517,6 +517,273 @@ AS INSERT INTO customers_us SELECT * FROM STREAM(customers_us_east); ``` +## Change Data Capture (CDC) with Auto CDC + +Many source systems emit a stream of *change events* rather than a snapshot of the current data: each record describes an insert, update, or delete to a row, identified by a key. Applying these events correctly to a target table by hand is tricky. You have to match events to existing rows, apply them in the right order, and handle out-of-order and duplicate events without corrupting the table. + +**Auto CDC** does this for you. You point it at a source of change events and tell it how to identify and order them, and SDP maintains a target streaming table that always reflects the latest state for each key. + +### What Auto CDC does + +Given an ordered stream of change events, Auto CDC keeps the target table in sync with the source: + +- **Inserts and updates** - For each key, the event with the highest sequence value wins. If no row exists for the key, it's inserted; if one exists, it's overwritten with the latest values. +- **Deletes** - Events that match a delete condition you supply remove the corresponding row from the target. +- **Out-of-order events** - Events don't have to arrive in order. Auto CDC uses the sequencing expression to determine the latest state per key, so a late-arriving event with a lower sequence value doesn't overwrite newer data. + +This behavior implements **Slowly Changing Dimensions (SCD) Type 1**: the target keeps only the current version of each row, with no history of prior values. SCD Type 1 is the only mode currently supported. + +For example, given these change events (ordered by `version`): + +| id | name | version | op | +|----|----------|---------|--------| +| 1 | alice | 1 | UPSERT | +| 2 | bob | 1 | UPSERT | +| 1 | alice_v2 | 2 | UPSERT | +| 2 | bob | 2 | DELETE | +| 3 | carol | 1 | UPSERT | Review Comment: Good catch @szehon-ho, and apologies @anew - a readability pass trimmed the out-of-order event out of the concept example, which is exactly what you had asked to see here. I have restored it, following the edits above: 1. Re-added the trailing `| 1 | alice | 1 | UPSERT |` row. 2. Restored the lead-in noting the events are not in `version` order (the last `id 1` event is a stale re-delivery of version 1 arriving after version 2). 3. Added the discard clause to the by-key walkthrough: the stale version 1 event is discarded because a higher sequence value has already been applied. The target table is unchanged, as you noted - the highest sequence value per key still wins. I also replayed this exact six-event batch through the merge engine to confirm (`id 1 -> alicia@2`, `id 2` gone, `id 3 -> carol@1`). Pushed in the latest revision. -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
