Repository navigation
Replace defensive final dedups with pre-deduped lookups in business facts #2382
Description
Description/Context
Systemic anti-pattern across the business fact tables: tfact_enrollment (lines 289-332), tfact_order (lines 272-307), tfact_grade (lines 165-204), and tfact_certificate (lines 313-358) all end with row_number() over (... order by updated_on desc nulls last) to repair dimension-join fan-out. Because the duplicates share an identical updated_on, the surviving row's FKs are nondeterministic.
Plan/Design
Adopt the tfact_studentmodule_problems.sql:94-115 pattern: pre-dedupe every lookup CTE to its join grain (platform-scoped, min(user_pk) style) before joining; then demote the trailing dedup to an error-detection uniqueness test.
Related witan pattern: pat-fact-table-joins-pre-deduped-lookups-not-defensi-77e58d.
Sequencing: do this after (or together with) the program_fk platform-scoping fix and the dim_discount grain fix, since those are the known fan-out sources. (Witan blockers: tk-fix-tfact-enrollment-program-fk-platform-scoping-5ceeb6, tk-fix-dim-discount-grain-mismatch-causing-tfact-or-fb07eb.)
P1. Part of #2375 (2026-07 dbt warehouse audit). Witan task: tk-replace-defensive-final-dedups-with-pre-deduped--088e12.
Activity
blarghmatey commentedon Aug 20, 2026
Triage pass 2026-08-20 - the two named fan-out sources are fixed, but the anti-pattern this issue targets is not.
Blockers cleared. #2378 (
tfact_enrollment.program_fkplatform-scoping) and #2381 (MicroMasters certificate dedup) both landed, adding upstream pre-dedupes attfact_enrollment.sql:38,220andtfact_certificate.sql:276. That was the prerequisite for removing the trailing guards.All four trailing defensive dedups survive, each still
row_number() over (partition by <x>_key order by ... updated_on desc nulls last):tfact_enrollment.sql:377tfact_order.sql:278tfact_grade.sql:183tfact_certificate.sql:368The comment at
tfact_certificate.sql:275still refers to "the certificate_key-based defensive dedup further below", so the layering is explicit in the code.Worth narrowing the body to say the blockers are resolved and the remaining work is removing the four guards, each behind a test proving the upstream grain holds without it.
tfact_order.sql:278should be sequenced after #2379 - thedim_discountgrain mismatch is what its guard is currently masking.