Epic: dbt warehouse audit — semantic accuracy & consolidation #2375

Description/Context

Umbrella tracking issue for the 2026-07 dbt warehouse audit — a thorough review of the ol_dbt project (src/ol_dbt, ~650 models) evaluating the semantic accuracy of the dimensional, mart, and reporting layers against their documented contracts, using OpenMetadata lineage + profiling as ground truth.

The audit produced remediation work across five workstreams:

1. Dimensional layer correctness defects — identity collapse, unstable surrogate keys, SCD2 null-churn, platform-scoping violations, and systemic defensive-dedup that masks join fan-out.
2. Marts & reporting metric correctness — inverted-sign metrics, multiple competing definitions of the same concept (is_enrolled, problems-attempted, program-completion days), fan-out user joins feeding live Superset dashboards, and reporting→dimensional migration finishers.
3. Dead model & catalog cleanup — zero-consumer marts/reporting models, a dead dimensional "island" (unused bridges/dims), and catalog hygiene.
4. Shared macro extraction & adoption — ~54 near-identical per-platform models (staging clones + user_courseactivity family), macro-adoption sweeps for cross-db portability, and config cleanup.
5. StarRocks support for cross-database macros — 18 missing starrocks__ macro variants, 3 buggy variants, and live-cluster validation.

Highest-severity findings (P0): dim_user NULL-email identity collapse (#2376), un-deduped user joins fanning out engagement marts (#1957), and the inverted-sign program_completion_days metric (#2083).

Findings are tracked as witan tasks under project wp-dbt-warehouse-audit-semantic-accuracy-consolidat-b4244e; durable structural facts are stored as witan memories. Several workstreams overlap the in-flight mart→dimensional migration already tracked in #2072 (and #531) — pre-existing issues below are cross-linked and carry the new audit findings as comments, but remain under their existing epics.

Plan/Design

Sub-issues are grouped by workstream. The 24 newly-opened issues appear in the native sub-issue tracker above; pre-existing issues are cross-referenced here.

1. Dimensional layer correctness defects

• Fix dim_user NULL-email identity collapse #2376 — Fix dim_user NULL-email identity collapse P0
• Fix dim_instructor name-collision identity collapse #2377 — Fix dim_instructor name-collision identity collapse
• Fix tfact_enrollment.program_fk platform-scoping violation and doc contradiction #2378 — Fix tfact_enrollment.program_fk platform-scoping violation
• Fix dim_discount grain mismatch causing tfact_order discount_fk fan-out #2379 — Fix dim_discount grain mismatch causing tfact_order fan-out
• Fix NULL-unsafe SCD2 change detection in dim_course_run and dim_product #2380 — Fix NULL-unsafe SCD2 change detection (dim_course_run, dim_product)
• Dedupe MicroMasters certificates in tfact_certificate and fix email-case user resolution #2381 — Dedupe MicroMasters certificates in tfact_certificate
• Replace defensive final dedups with pre-deduped lookups in business facts #2382 — Replace defensive final dedups with pre-deduped lookups
• Design durable user surrogate key (replace email-based user_pk) #2383 — Design durable user surrogate key (replace email-based user_pk)
• Include platform in content_block_pk surrogate (dim_course_content and children) #2384 — Include platform in content_block_pk surrogate
• Make remaining tracking-log event facts incremental #2385 — Make remaining tracking-log event facts incremental
• Dimensional yml/doc alignment pass (OpenMetadata-verified drift) #2386 — Dimensional yml/doc alignment pass

2. Marts & reporting metric correctness

• Reconcile fifth competing metric definition: program_complete_days computed twice with opposite signs #2387 — Reconcile fifth competing metric (program_complete_days)
• Standardize the "is_enrolled" definition across activity reports #2388 — Standardize the is_enrolled definition
• Extend defensive-dedup fix to reporting-layer data_detail_* models #2389 — Extend defensive-dedup fix to reporting data_detail_* models
• Investigate conditional uniqueness logic in marts__combined_program_enrollment_detail and downstream reporting models #1957 — Un-deduped user joins fanning out engagement marts P0 (audit finding added as comment)
• Migrate marts__combined_program_enrollment_detail to use dimensional layer #2083 — Inverted sign of program_completion_days P0 (audit finding added as comment)
• Migrate engagement_problem_completion_summary to use dimensional layer #2104 — Consolidate four competing problems-attempted definitions (comment)
• Migrate organization_administration_report to use dimensional layer #2107 — Rebase organization_administration_report on engagement marts/facts (comment)
• Migrate combined_video_engagements_counts_report to use dimensional layer (blocked by mart migration) #2112 — Fix video "total count" semantics (comment)
• Migrate Enrollment_Activity_Counts_Dataset to use dimensional layer (blocked by mart migration) #2113 — Enrollment_Activity_Counts_Dataset snake_case + verified_count semantics (comment)
• Migrate enrollment_detail_report to use dimensional layer (blocked by mart migration) #2114 — Finish enrollment_detail_report cutover to dimensional (comment)
• [Epic] Complete pending dimensional warehouse model improvements (Phases B, C, G) #2073 — Add dimensional successors for exam grades & flexible-pricing income (comment)
• Migrate marts__combined__users to use dimensional layer #2077 — Rebase marts__combined__users on dim_user (comment)

3. Dead model & catalog cleanup

• Decide fate of dimensional dead island (catalog bridges, dim_topic/department, afact_video_engagement, dim_time/date) #2399 — Decide fate of the dimensional dead island
• Automate cleaning up the removed or renamed models  #531 — Catalog hygiene + automate cleanup of removed/renamed models (audit findings added as comment)
• [Epic] Migrate mart and reporting models to use the dimensional layer #2072 — Zero-consumer mart & reporting deletions (audit findings added as comment; several Migrate marts__combined_course_engagements to use dimensional layer #2074–Migrate video_engagement_report to use dimensional layer (blocked by mart migration) #2120 sub-issues should be closed/repurposed as deletions)

4. Shared macro extraction & adoption

• Macro-ize per-platform Open edX staging clones (~28 models, drift confirmed) #2390 — Macro-ize per-platform Open edX staging clones (~28 models)
• Macro-ize int__*__user_courseactivity_* family (~26 models differing by one ref) #2391 — Macro-ize int__*__user_courseactivity_* family (~26 models)
• Macro-ize additional per-platform intermediate families beyond user_courseactivity #2392 — Macro-ize additional per-platform intermediate families
• Extract tracking-log event-fact macro; stop dim_problem re-scanning raw logs #2393 — Extract tracking-log event-fact macro
• Macro adoption sweep: replace inline idioms with existing dispatch macros #2394 — Macro adoption sweep (cross-db dispatch macros)
• dbt config cleanup: redundant materializations, dead macro, duplicated value lists #2395 — dbt config cleanup (redundant materializations, dead macro, duplicated lists)
• [Epic] Migrate mart and reporting models to use the dimensional layer #2072 — Platform-union macro for combined models (cross-cuts the migration epic; comment)

5. StarRocks support for cross-database macros

• Implement 18 missing starrocks__ macro variants (doc-verified implementations ready) #2396 — Implement 18 missing starrocks__ macro variants
• Fix 3 buggy starrocks__ macro variants (json_extract_value, from_iso8601_timestamp, array_join) #2397 — Fix 3 buggy starrocks__ variants
• Validate StarRocks macro set on a live cluster (syntax gaps the docs don't cover) #2398 — Validate StarRocks macro set on a live cluster