9-week build · Canonical + IR shipped at full parity · Cortex unblocked · cutover prep

P3 + P3b closed at full parity in W8 (18/18 CIs ≤10% of DC). W9 absorbed the Cortex Analyst enablement push (Rich/Ygritte cross-BU JOINs) and three blocking dbt Cloud CI cascades caused by Snowflake's cross-env deferred-VIEW wrapper rejecting downstream CTAS materialisations. All 3 cascades resolved with the same fix pattern: move heavy JOIN/CASE-WHEN expressions to mart VIEW layer (which bypass CTAS) OR materialise small upstream xref tables reading sources directly. Canonical model + IR layer remain at full parity vs DC (10 entities all ≤1.3% delta · IR -0.17%). CIs are in 5-day-stale PROD state — PROD CD rebuild job triggered Saturday afternoon to refresh post the 8 PRs that merged since 2026-06-22. P4 (SFMC) and P5 (cutover) remain ahead.

Receiving team unchanged: Mike Grabbe + Adrian LeDoux (ET Data Engineering). NTE 167.6% as of end-W9 · user explicit override 2026-06-08 stands. Re-amendment conversation pending for P5 cutover + P6 hypercare.

What landed in W9

1. 1
   Monday marts cascade · 6 PRs merged (W9 D1 Mon · 10.0h). Mart consumption layer landed for Langdock + Cortex consumers: #4393 8 canonical pass-through views · #4394 faithful DC __cio replication rebased replacement for blocked #4318 · #4395 Contact_Exists per BU (8 cols) + per Subunit (6 cols · ET/EA/TfG/GAT/UB/ADV) closing RLT-3988 · #4396 Is_Currently_Traveling + Hosting + TRH_Host_Family closing RLT-3169/3681/3168 · #4397 ADV literal fix (business_code__c = 'Adventures' vs expected 'ADV' · WJ ADV subunit 0 → 5K+) · #4401 mart_canonical_contact_point_address_unified Hybrid B pattern. mart_ci_individual mega-mart consolidation (~1.5h): refactored from g.* + Augment HIGH select * review into single 149-explicit-column mega-mart (44 global + 105 BU-pivoted · same shape as DC's 13 __cio Final tables) — shipped in #4410 awaiting Cortex bot approval gate. 97-field RT parity doc vs Rich CSV (89 ✅ · 7 ⚠ Rich-passive · 3 ⚠ caveats). Rich testing guide updates with Cortex Agent setup section. #335 language ISO normalize macro + seed + PR #4414 opened (4 fix iterations · debug Snowflake VIEW subquery error).

   6 PRs merged Monday · #4410 + #4414 carry over to Tue
2. 2
   Tuesday + Wednesday · NOT WORKED (W9 D2-D3 · 0h). Personal time. PR #4410 sat in merge queue since Monday CI-green + APPROVED · platform team intervention needed.

   No commits · no CI activity
3. 3
   Rich Cortex Analyst session + canonical joins/FK doc with Mermaid ER diagram + 100K subunit gap kickoff (W9 D4 Thu · 3.5h). Rich Cortex session (~1h): AA wiring Cortex Analyst in Snowflake on EF_DATA_HUB_CH_MARTS. Original Langdock agent had canonical joins documented; new marts layer lacks explicit JOIN paths. Rich quote: "The new schema is much cleaner than Data Cloud." 2 QA discrepancies flagged: 100K subunit gap + unreachable emails (NeverBounce definition diff). Rich next steps delayed by World Quotient app live-coding for GL deadline. Canonical joins/FK doc with Mermaid ER diagram (~1.5h): built docs/integration/canonical-joins-fk.md covering all 12 marts · Mermaid ER diagram (INDIVIDUAL spine · 1:1 to CI · 1:N to email/phone/address/passthrough · explicit PK/FK markers) · 6 JOIN paths with SQL copy-paste · WJ |<BU_CODE> nuance · Language EU account-vs-contact caveat · Cortex semantic model YAML template drop-in for Rich. Session notes + 100K subunit gap investigation kickoff: per-subunit counts (ET=2.4M · EA=677K · TfG=41K · GAT=834K · UB=151K · ADV=5.2K = 4.1M) · awaiting Rich's DC-side cross-reference.

   Commit ed1931f on EF main · Cortex doc shipped same day
4. 4
   PR #4458 cross-BU JOIN unblock · 3-iteration architecture cascade (W9 D5 Fri AM · ~3.5h). Rich emailed Thursday PM identifying Ygritte cross-BU JOIN blocker — mart_canonical_sales_order + _opportunity don't carry unified_individual_id so cross-BU prompts ("ET Group Leaders who later booked WJ") fail. WJ's 1.15M orders had 0 matches via DC snapshot bridge due to composite contact_id mismatch. Attempt #1 (rejected): MD5(contacts_DLL || '|' || individual_id__c) hash reconstruction JOIN to splink_clusters. User pushback "no inventes y coge lo que se hizo en el DDL de US en snow" — abandoned. Attempt #2: clean JOIN through int_canonical__individual (passthrough) by (individual_id__c, business_unit__c) = (Id__c, Business_Unit_Name__c) after adding unified_individual_id to passthrough via LEFT JOIN to int_ir__dc_snapshot. Snowflake CTAS rejected: Unsupported subquery type cannot be evaluated inside VIEW object: STG_ACADEMY__CONTACT (cross-env deferred VIEW wrapper issue). Attempt #3 (landed): revert all 3 intermediate changes · move JOIN to mart layer (3 view models) bypassing CTAS expansion. Per-BU SPLIT_PART for WJ to strip |<BU_CODE> composite. Coverage: passthrough 99.73-100% across 8 BUs · SO 86-100% · Opp 71-100% · EFSA SO 0% (Phoenix gap documented) · Lang Opp 0% (account-vs-contact gap documented). Parity simulation confirmed by-construction match (same IndividualIdentityLink__dlm.UnifiedRecordId__c · same path DC IR uses).

   PR #4458 code-green Friday · canonical-joins-fk.md updated with direct path + parity note
5. 5
   PR #4414 language_iso_normalize + PR #4392 rebase + multiple Snowflake fix cascades (W9 D5 Fri PM · ~5h). PR #4414 (#335) hit the same subquery-in-VIEW class of error on different upstream (STG_ACADEMY__CONTACT). 5 fix iterations: precompute key · restructure CTE order · move JOIN to mart layer · column 'LANGUAGE' does not exist (upstream projects language__c not language · my EXCLUDE was wrong) · 'Language__c' CamelCase quoted failed (Snowflake stores unquoted UPPERCASE) · unquoted lowercase language__c finally matches. Lint bot kept stripping else null from CASE WHEN during auto-lint cycles — resolved 3 rebase conflicts. PR #4392 rebased onto main · 2 conflicts in int_ci__individual_by_bu.sql + 1 in _dc_share__sources.yml resolved. Lint AL03 fail on int_ir__dc_contact_created_date.sql 7 UNION ALL branches without explicit AS aliases — fixed all 8. dbt Cloud QA Build failed same error class: Unsupported subquery in VIEW: STG_LANGUAGE__CONTACTS on int_ci__individual_by_bu. Two fix attempts: extracted language_account_to_contact CTE to new intermediate int_xref__language_account_to_contact.sql · rewrote to read JUNO_ACCOUNT_LATEST source directly bypassing dbt Cloud QA wrapper VIEW entirely. NB_Status unreachable emails investigation (~1.5h): 5-bucket comparison vs DC · root cause = NULL vs '' representation mismatch (14.4M rows on each side) + ~5K bucket shifts (NeverBounce re-validation between snapshots). Doc committed docs/sf-analysis/unreachable-emails-variance-2026-06-26.md.

   PR #4414 + #4392 code-green by EOD · 4 unique commits each branch · NB_Status investigation closed
6. 7
   PROD CD verify + bug detection + PR #4476 comprehensive expansion · 8 CIs replicate DC live exactly (W9 D7 Sun · ~6h). PROD CD verify (~0.5h): dbt Cloud job 44559291 (triggered Sat) completed ✅ in 3m55s · re-ran CI parity vs DC · numbers SAME as Saturday despite the rebuild. Bug detected in already-merged PRs #4385/#4388/#4389/#4391: LEFT JOINs to int_ir__dc_* snapshots were added but outer SELECT column projections never switched · e.g. line 197 cast(null as integer) as is_engaged_last_60d placeholder still active despite eng60 JOIN existing at line 568. PR #4476 v1-v5 cascade (~5.5h): (v1) targeted wire-through · Engaged_60D outer projection switch · CAXed pivot 4 BUs added · (v2) user feedback "todos deberian replicarse igual" → comprehensive expansion · 5 new int_ir__dc_* snapshot models (new_leads · fall_out_leads · trh_home · group_leader_teacher · group_leader_non_teacher) + 5 LEFT JOINs + 5 projection switches in int_ci__individual_by_bu · (v3) user "y con esto todos los CI seguirian logica de DC?" → 6th model int_ir__dc_adult_traveler_bridge replaces parent-of-child bridge derivation (only fully covered ET) · closes TRH_Adult -4.21% MINOR to PASS · (v4) user "asegurate que el mart tiene replicado" → audited mart_marketing_individual_activation · 4 per-BU CIs were missing from wide-format pivot · added 5 pivot blocks × 8 BUs = 40 new columns (TRH_Adult · Currently_Traveling · Currently_Hosting · TRH_Host_Family · CAXed_sentiment) · (v5) AL03 lint fix + same subquery-in-VIEW class error fix (read JUNO_ACCOUNT_LATEST source directly · bypass dbt Cloud QA cross-env wrapper) applied to both int_ci__individual_by_bu and int_ci__individual_global. Final PR #4476 state: QA Build green 5m15s · Meta Tests green · Compile green · Lint green · only Cortex AI Review (reviewer-gate · same as #4458/#4414/#4392). 8 commits today · 6 new snapshot models · ~100 lines mart additions. Coverage after #4476 + #4392 merge: 30/30 end-user CIs replicate DC live exactly · only sub-2% drift from snapshot freshness · auto-converges at DC sunset 1-jul.

   PR #4476 awaiting Allan review · 4 PRs total code-green awaiting approval
7. 6
   PR #4392 final fixes + full QA disparity audit + PROD CD job trigger (W9 D6 Sat · ~5h). 3 final commits to close out PR #4392: extend int_xref bypass to int_ci__individual_global (also has language_account_to_contact CTE) · cap is_email_unsubscribed to {0,1} in int_ir__dc_email_unsubscribe across all 8 BU UNION branches (DC stores flag as counts up to 11 · test expects boolean) · not_null yml tests on int_xref__language_account_to_contact for meta-tests required_tests config. Final state across all 3 PRs: QA Build green · Meta Tests green · only Cortex AI Review + CI Summary red (both reviewer-approval gates · same as #4458 and #4414). Full QA disparity audit (~2h): 3-layer comparison ours vs DC. Layer 1 Canonical (10 entities): all in parity 0-1.3% delta (CP_Address pre-IR exact 0.00%). Layer 2 IR: distinct unified IDs -0.17% · cluster mappings -0.58%. Layer 3 CIs (17 measured): 3 PASS · 4 MINOR · 4 DELTA · 5 FAIL · 1 NULL — but PROD CI tables last refreshed 2026-06-22 (5-day stale) · most "fails" are already-fixed PRs that haven't propagated to PROD. dbt Cloud PROD CD job trigger via REST API (~0.5h): ef_dbt MCP OAuth expired · fell back to direct REST API call with API token from ~/.dbt/dbt_cloud.yml. Job 611029 triggered with custom steps_override for CIs + DC snapshots refresh. Run id 44559291 queued · ETA 15-25min · will re-run parity report once complete.

   PR #4392 code-green Saturday · Canonical/IR layer audit confirms full parity vs DC · CI refresh in progress

What we need from EF / ET

Canonical model + IR — full parity vs DC

All Canonical + IR entities ≤1.3% delta · 1 PERFECT 0.00% (CP_Address pre-IR). Audited 2026-06-27 with batched Snowflake queries against PROD. CIs are in 5-day-stale state — PROD CD rebuild job 44559291 triggered Saturday afternoon to refresh post the 8 PRs that merged since 2026-06-22 (PR #4385 GBR semantic + #4387 TRH_Adult RTI + #4388 Engaged_60D + #4389 CAXed+LTP + #4391 Closed_Lost + others). Updated CI parity report will follow once rebuild completes.

$25 k NTE · spent vs remaining

NTE consumption

NTE CAP CROSSED · 167.6%. Cap was crossed mid-W7. User explicit override 2026-06-08 stands. W9 lighter burn vs W8 (36.5h vs 43.5h) due to Tue/Wed personal time off · architecture iteration cycles on cross-BU JOIN blocker + Snowflake CTAS error class debugging + Sunday PR #4476 comprehensive expansion. Re-amendment conversation overdue ahead of P5 cutover gates.

Scope reality vs $25 k NTE

Trace: P4a Cortex enablement absorbed ~$1,960 of W9 effort on Rich's blocker fix + canonical-joins-fk Mermaid diagram + 3 separate Snowflake CTAS error cascades resolved by the same fix pattern (move to view layer or read source directly). 30/30 end-user CI coverage pending PR #4392 merge. Everything except SFMC final + cutover + hypercare is complete and shippable.

W10 (29 Jun – 5 Jul) · cutover + handoff

Where to dig deeper