Housing CRM exports sometimes repeat the same household under new surrogate IDs. What should silver cleansing do?

Housing CRM exports that mint new surrogate IDs for the same household require silver deduplication onto a stable resident or household key. Without collapsing repeated entities, case counts, benefit rollups, and outreach lists treat one family as many. Silver cleansing should match on durable business attributes (address, member set, external household id) and resolve surrogates to one household entity for curated layers.

Applying SCD Type 2 versioning to resident keys so repeated households under new surrogates resolve to one entity fails by concept: it creates a new version row for each change rather than collapsing repeats to one stable household entity, so one family remains represented by multiple versioned keys and curated layers never get a single deduplicated record. Deduplicating resident keys on ingestion batch IDs so repeated households across extracts resolve to one entity does not catch this case—the same household arrives in different batches with different batch IDs, so batch-based grouping never links repeats across extracts and leaves one family represented by multiple household keys. Deferring resident key resolution to gold views so repeated households under new surrogates resolve to one entity breaks silver cleansing by leaving dedup at query time instead of resolving surrogates to one stable, materialized household key that curated layers can rely on.

Exam caveat: Surrogate keys from source systems are not automatically unique business entities. Deduplicate in silver using defined match rules; do not confuse bronze retention of raw surrogates with gold household grain. Document which attributes define "same household" for the housing program.

Operational check: Seed two CRM rows that share household identity but differ on surrogate_id, run silver cleansing, and confirm one household entity with linked surrogate history. Re-run the nightly job and verify keys remain stable (no random UUID churn). Compare pre- and post-dedupe household counts against a known housing caseload sample.