Housing needs silver case records to reflect inserts, updates, and deletes from the case-management OLTP system. Which ingest approach should they apply?

Housing silver case records that must reflect inserts, updates, and deletes from the case-management OLTP system need change data capture (CDC). CDC propagates the full change stream so silver stays aligned with live case state, including closures, corrections, and removals—not just history tracking that leaves deleted cases in place. Municipal housing analytics that mirror OLTP caseloads and eligibility require that complete change semantics in the silver layer.

An append load keyed on creation timestamp captures only brand-new cases and silently misses updates to existing cases and any deletions despite claiming to reflect inserts, updates, and deletes, so silver cannot reflect the full change stream. A merge keyed on modification timestamp is unreliable for deletes because removed cases have no modification event to merge despite claiming to reflect inserts, updates, and deletes, and timestamp-based keys can miss late-arriving corrections. A Type 2 dimension load of case history preserves historical versions but is a modeling pattern, not a change-capture ingest approach; it will not reliably propagate deletes from the OLTP into silver despite claiming to reflect inserts, updates, and deletes and can leave removed cases appearing active.

Exam caveat: CDC is the pattern for insert, update, and delete alignment; an append load keyed on creation timestamp can catch new rows but misses updates and removals despite claiming to reflect all three, so the exam answer for reflecting all three change types from OLTP is CDC.

Operational check: enable CDC from the case-management system into the lakehouse, apply changes to silver with MERGE or equivalent, test an insert, an update, and a delete end-to-end, and compare silver active-case counts to OLTP after the delete lands.