9-week build closed · cutover week complete · handoff prep begins

The nine-week build shipped everything within scope plus the P4a Cortex enablement + P4b activation scaffolding + full DC parity coverage. W10 was the cutover / hardening week: absorb Rich's DC-live QA (5 issues expanded to 12), reconcile every reported gap against DC's own snapshot, and close every fixable delta before DC sunset on July 1st. All 5 PRs merged (including the ET J.6 orphan-recovery SALES_V UNION, which turned out to be a source-integrity fix Rich called "an excellent finding"). Two 30-minute Rich sessions this week locked (1) SFMC delay 4–6 weeks due to fixed-IP vs shared-IP-pool IP warming, (2) handoff scope narrowed to SF + Snowflake + dbt setup + tagging only (business will redefine segments in the new MC), (3) 200K residual orphans in WJ + Academy are pre-2018 legacy — Rich contacting Jen. P5 cutover (SFMC bidirectional S3) blocked on SFMC admin account provisioning (Rich promised). P6 handoff docs scaffolded.

Receiving team unchanged: Mike Grabbe + Adrian LeDoux (ET Data Engineering). NTE 188.0% end-of-W10 · user explicit override 2026-06-08 stands. Re-amendment conversation overdue ahead of P5 SFMC connection + P6 hypercare cycles.

What landed in W10 · 5 PRs merged · 7 tickets closed

1. 1
   Monday · Rich RLT-4816 (5 issues → 12) triage + GH #336 + PR #4493 scoping (W10 D1 Mon · 7.0h). Rich emailed 5 CI parity findings vs DC live. Ran fresh Snowflake queries against the DC live share to verify each. Result: 3 real bugs (mart CI 32K duplicate rows · UC/GBR emitted the SAME column with −44% delta · EFSA student_indie 54 vs DC 118K · CCAP active_opp +9,073%) · 2 DC-inherited semantics (GL teacher/non-teacher overlap 14,420 · WJ student_indie 100%). Opened GH #336 "Rich QA findings triage" with 5-issue table + evidence queries · cross-linked with RLT-4816. PM: Rich expanded ticket to 12 issues. Investigated all 12 · read DC live JSON formulas from docs/sf-analysis/calculated-insights/dc-live-formulas/ for each · confirmed which are DC-faithful vs real bugs. Started PR #4493 comprehensive-fix design.

   12-issue triage doc committed · issue table in GH #336
2. 2
   Tuesday · PR #4493 comprehensive fix (11 files · 6 new snapshots) + 7 RLT tickets closed + DC live baseline captured pre-sunset (W10 D2 Tue · 10.0h). PR #4493 landed on fix/336-rich-qa-comprehensive: 4 new snapshot models · int_ir__dc_active_opp closes CCAP +9,073% → exact · int_ir__dc_enroll_not_trav closes WJ −100% + EFSA/GY/CCAP gaps · int_ir__dc_student_indie closes EFSA −99.95% · int_ir__dc_adult_non_traveler fortifies GY/Academy. Plus 2 GBR/UC split snapshots: int_ir__dc_global_business_rule + int_ir__dc_unified_contactable — post-fix both = 3,956,949 exact DC match (was buggy 2,189,344 both same column). GL teacher branch adds Account_Id IS NOT NULL filter (Rich mut-excl). WJ Option B: wj_gc_only_contacts CTE excludes contacts whose every WJ SO has customer_type__c = 'GROUP_COORDINATOR' (−6,813 UIs · 8,640,619 vs DC 8,638,345). Mart adds 3 global passthroughs (currently_traveling / hosting / TRH_host_family). sed rename <bu>_closed_lost_30d → <bu>_closed_lost_last_30_days (Rich naming, 8 BU cols) + latest_tour_* → latest_program_* (RLT-3575 · 48 mart aliases · 8 BUs × 6 metrics). 6 commits · 4 augmentcode auto-fix rebase conflicts resolved · AL03 aliases added to 4 UNION ALL snapshot models. Merged 2026-07-01 06:57 UTC. DC live baseline written to docs/dc-sunset/dc_live_baseline_2026-06-30.md — immutable post-sunset regression reference · 24 metric snapshots. Closed 7 RLT tickets to Done via transitionJiraIssue: RLT-3169 (currently_traveling/hosting) · RLT-3168 (TRH_host_family) · RLT-3575 (Tour→Program) · RLT-3667 (CAXed naming) · RLT-3971 (Related_Contact) · RLT-3988 (Contact Exists BU/Subunit) · RLT-4816 (Rich closed). RLT-3974 (Referral CI) kept On Hold (Phase I deprioritised).

   PR #4493 merged · 7 tickets closed · pre-sunset DC baseline immutable
3. 3
   Wednesday · PR #4512 (GL mut-excl projection-level fix) + PR #4525 (Email_Unsub US canonical) + Rich session #1 (SFMC delay · non-boolean unsub) + RLT-4926 close + GBR reverse-engineering + GH #337/#338/#339 (W10 D3 Wed · 8.0h). Post-#4493 audit revealed the GL mut-excl fix was silently no-op — the final SELECT reads is_group_leader_teacher from dc_glt snapshot which discarded the Account_Id IS NOT NULL filter I had placed on the CTE. PR #4512 enforces mut-excl at projection level via case when non_teacher=1 then 0 else dc_glt.teacher end. Overlap 14,420 → 0. Merged 14:31 UTC. · PR #4525 email_unsub: DC's Email_Unsubscribe__cio inflates 95-99% per BU (WJ 98.9% · ET 95.4%) because ET's raw Email_Unsub__c is a counter (0-4) not boolean (values = number of products unsubscribed from · verified in Rich session). US canonical int_canonical__contact_point_email.Email_Unsub__c preserves the boolean at 16-33% per BU. Swap coalesce(dc_eu.is_email_unsubscribed, 0) → coalesce(i.is_email_unsubscribed, 0) reading from individual_per_bu CTE. Expected post-merge: WJ 8.6M → 2.6M · ET 5.4M → 241K. · Rich 30-min session #1 (see memory/2026-07-01-rich-session.md): new SFMC delayed 4-6 weeks (ET fixed IP vs shared IP pool · IP warming · Andrew Tiemann meeting needed for unsub strategy) · no segment pre-building (business will redefine in new MC) · handoff scope narrowed to SF + Snowflake + dbt setup + tagging only · IR will never exact-match DC (fuzzy match temperature differs). · RLT-4926 closed: currently_traveling gap −26% turned out to be time-dependency — Rich's baseline was 2026-06-29 · DC live TODAY = 14,518 (dropped 61% in 48h). Our dbt 30,787 is BETWEEN both · MORE inclusive than DC live. Rich accepted and closed. · GBR reverse-engineered: literal AND-chain in DC JSON gives 2,189,344 but DC snapshot output = 3,956,949 · confirmed DC uses proprietary formula ≠ published JSON. Best reverse-engineered guess: strong_identity + no_gl + no_active_opp + no_enrolled + (any_trh OR trh_adult) = 3,474,891 (-12% vs DC snapshot). Documented for post-cutover Hybrid COALESCE. · 3 GH tickets opened: #337 [REMEDIATION] Email_Unsubscribe SFMC-source refactor (blocked by #167) · #338 [AUDIT] W10 pre-cutover QA runner + Ygritte results (RLT-4889) · #339 [REMEDIATION] Hybrid COALESCE for unified_contactable post-sunset scalability.

   2 PRs merged · Rich session #1 recorded · 3 post-cutover follow-ups scoped
4. 4
   Thursday · PR #4534 (dbt tests + handoff notes) + PR #4537 (ET orphan opp recovery via SALES_V UNION · 100% of 35,740 orphans) + Rich session #2 + Academy investigation + GH #340 (W10 D4 Thu · 8.0h). PR #4534: 2 dbt tests + 9-section handoff doc. test_sales_order_orphan_opp_rate.sql per-BU (Academy excluded — 97.7% is persistent baseline not degradation; thresholds WJ<50% · ET<15% · others<20%) + test_splink_ir_tier1_hit_rate.sql monitors drift (zero-div guarded). docs/dc-sunset/handoff-notes.md covers Splink Hybrid B tiering + when to retrain · J.6 source integrity + evidence · data freshness advantage · Splink drift metrics · email_unsub PR #4525 rationale + source field map per BU · GH #339 Hybrid COALESCE plan · contact-point marts sibling refactor · references. · PR #4537 ET orphan opp recovery (RLT-4889 J.6): Rich flagged 675,657 orphan SOs · verified DC has same 670,591 rate (matches within 0.75%) — proves source-integrity issue not our regression. Investigated LANGUAGE_KAFKA.JUNO_OPPORTUNITY_LATEST (0 match) · WOJO.PRT_LOYALTY_OPPORTUNITY (0) · SALESFORCE_*.OPPORTUNITY (0). Discovered DATA_HUB_SHARE.SALES_V (4.4M rows) contains 100% of the 35,740 ET orphan opp IDs. Extended stg_student_tours__opportunities to UNION primary source + recovery from SALES_V (anti-join · sustainable for future data · minimal enrichment fields NULL). Expected: ET orphan_rate 10.4% → 0% · global J.6 1.07M → ~632K. · WJ + Academy triage: WJ orphan IDs are 18-char SF alphanumeric (006...) · not in Phoenix numeric sources · found WORLD_JOURNEYS.OPPORTUNITIES_V in US DataHub (1.87M rows · matches format) but NOT replicated to CH. Academy 100% NULL opportunityid__c AND order_id__c at source — canonical already correctly maps application_opportunity_id for 1,447 rows · remaining 60,559 are truly NULL pre-2018 legacy. GH #340 [REMEDIATION] opened + moved to In Progress: Replicate WORLD_JOURNEYS.OPPORTUNITIES_V to CH for WJ orphan recovery (blocker: data eng · same pattern as ET SALES_V access). · Rich 30-min session #2 (see memory/2026-07-02-rich-session.md): ET fix validated as "excellent finding" · SFMC unsub rates now reasonable (WJ 30% · ET 4.2%) · SFMC admin account promised to enable bidirectional S3 setup · Rich to contact Jen re pre-2018 WJ/Academy legacy · Rich off Fri holiday · target complete pending work by early next week.

   PR #4534 merged · #4537 shipped · #340 opened · Rich session #2 recorded
5. 5
   Friday · #4525 + #4534 merged · #4537 AL03 lint fix + rebase-conflict resolution + Allan reply (W10 D5 Fri · 3.0h). Verified merge queue processed #4525 (email_unsub US canonical) and #4534 (tests + handoff docs) · both landed successfully on main · PROD CD auto-triggered. · PR #4537: Allan approved but CI failed with AL03 (Column expression without alias · line 127) — the select 1 inside the NOT EXISTS anti-join probe needed an explicit AS. Fixed by adding select 1 as anti_join_probe. Rebase conflict with augmentcode bot's autofix (bot added as p on the source alias but kept the bare select 1 · lint still failed). Resolved conflict taking both fixes (my as anti_join_probe + bot's as p). Force-pushed 6dfbbc974. Full CI passed. Replied Allan confirming ready to merge.

   Merges verified · #4537 in queue awaiting Allan's final ✅
6. 6
   Saturday · Sunday · Off (W10 D6-D7 · 0h). Personal time. Rich also off Friday for holiday. Target for W11 D1 (Mon): confirm #4537 merged, run parity dashboard against fresh post-sunset PROD, kick off SFMC admin account handoff with Rich.

   No commits · no CI activity

What we need from EF / ET

RLT-4816 (5 → 12 issues) · every fixable gap closed

12 of 12 Rich QA issues closed in-repo · J.6 ET half shipped · WJ/Academy residuals moved to post-cutover follow-ups (#340). Rich session #2 explicitly validated the SALES_V approach as an "excellent finding" and confirmed WJ/Academy 200K residuals are likely pre-2018 legacy pending Jen consult. Rich closed RLT-4816 + RLT-4926 during the week.

$25 k NTE · spent vs remaining

NTE consumption

NTE CAP CROSSED · 188.0%. Cap was crossed mid-W7. User explicit override 2026-06-08 stands. W10 burn 36.0h (Mon-Fri) roughly matches W9's 36.5h — Tue (10.0h) was the heaviest day (PR #4493 comprehensive fix). Re-amendment conversation overdue ahead of P5 SFMC connection + P6 hypercare cycles.

Scope reality vs $25 k NTE

Trace: P3b + P5 absorbed the bulk of W10 (Rich's 12-issue QA closeout + DC live baseline capture + 2 Rich sessions + handoff notes). All fixable gaps closed; only source-integrity residuals (WJ replication + Academy pre-2018) and SFMC last-mile are outstanding.

W11 (6 Jul – 12 Jul) · SFMC last mile + handoff kickoff

Where to dig deeper