Change Data Capture
- Process of identifying changes made to data in the source and delivering those changes to the target
- row level changes (inserting new row/ update & delete existing records)
CDC Feed
- changes are logged at the source as events that contain both data of the records along with mmeta data (e.g. version number/ timestamp & operation)
DLT for CDC
APPLY CHANGES INTO LIVE.target_table
FROM STREAM(LIVE.cdc_feed_table) //streaming source for CDC feed
KEYS (key_field) //identify primary(ies) key fields for upsert
APPLY AS DELETE WHEN operation_field = "DELETE" //delete rows, optional
SEQUENCE BY sequence_field //how operation should be applied, ensure properly recomputed when any records arrive out of order
COLUMNS * EXCEPT (col1) //list of fields that should (not) add to target table
- it breaks the append-only requirements for streaming table source