9-week build · CI parity 100% achieved · Hybrid B sunset-safe IR shipped
P3b (CIs consolidation) effectively closed end of W8: PRs #4335 (NL gate + CAXed scope) and #4385 (GBR semantic) + #4387 (TRH_Adult RTI) shipped formula fixes; PR #4375 Hybrid B IR snapshot replaced the Splink-stub clusters with DC's authoritative IndividualIdentityLink__dlm as a frozen static table inside our schema — DC sunset doesn't break the table because it's already materialised; new records fall back to email-fingerprint clustering. PRs #4388/#4389/#4391/#4392 extend the same Hybrid B sunset-safe pattern to 4 more pre-computed DC CIs (Engaged_60D · CAXed · Latest_Tour_per_Product · Closed_Lost_30D). Final standing 18/18 ≤10% of DC = 100% parity, with PR #4392 in flight adding 12 more CIs (Email_Unsub · Latest_Tour×2 · Contact_Created_Date×8).
Receiving team unchanged: Mike Grabbe + Adrian LeDoux (ET Data Engineering). NTE 146.9% as of end-W8 · user explicit override 2026-06-08 stands. Re-amendment conversation pending for P4 (SFMC final) + P5 (cutover) + P6 (handoff hypercare).
What landed in W8
- 1PR #4318 CI iteration · 3-blocker DC share attempt + Splink IR stub identified as structural blocker (W8 D1 Mon · 7.0h). Attempted to read 3 blocked CIs directly from inbound DC share
RBB92105_*.Unified_Individuals_Snowflake.*__cio; ACCOUNTADMIN grants applied · 0 JOIN matches between dbt cluster IDs and DC IR cluster IDs (different algorithms · ET/EFSA use Phoenix legacy 9-digit IDs vs DC's SF 18-char). Reverted DC share wiring · documented 3-option path forward. Faithful Latest_Tour_* date columns + TRH_Adult parent-of-child bridge wired inint_ci__individual_by_bu· Academy canonical status fix. 2nd-pass DC parity refresh: 2 PASS · 6 MINOR · 6 DELTA · 6 FAIL = baseline 20-CI parity. STRUCTURAL BLOCKER DISCOVERED:int_ir__splink_clustersat 1.08x ratio (stub) vs DC's 1.5-2x · 7 of 12 failed CIs cannot match DC regardless of formula literalness until real Splink runs. - 2PR #4335 canonical Tour_End_Date fixes + 5 augmentcode fixes on PR #4318 + 5-CI deep audit (W8 D2 Tue · 8.0h). Investigated 3 Latest_Tour deltas: WJ over-projects 249K rows from
end_datemapping (DC live = 0); CCAP under-projects (DC live = 21,935 fromfinancial_end_date). Per DCssot__SalesOrder__dlm.jsonfield map, only CCAP + ET should map. Two commits on PR #4335 · 13/15 CI green (Cortex AI gate only failure). Latest_Tour_Cancelled_per_Individual closed: −10.23% DELTA → −5.24% MINOR. 5 augmentcode fixes on PR #4318 (CAXedmax()over strings ·helper_prodcode_by_soJOIN column · 2 YAML docs). 5-CI deep audit via subagent: per_Product DC view internally broken · CAXed DC source PROCESSING_FAILED · Group_Leader_Teacher EFSA JOIN bug. 3 docs landed:qa-readiness-2026-06-17.md·rich-questions-2026-06-16.md· parity report refresh. - 3PR #4335 verification + NL gate rewrite + CAXed scope expansion (W8 D3 Wed · 3.5h). Batched 5-CI per-BU query against PROD confirmed PR #4335 mid-stream commits: Active_Opp +528% → -6.07% MINOR (biggest cluster-independent win) · Closed_Lost -13% → -9% MINOR · Fall_Out -0.03% PASS held. NL +17% → +31% regression (AO precision transferred 1.6M rows into NL · expected per DC's helper-absence semantic). NL gate rewrite:
NOT has_active_opportunity→o.unified_individual_id IS NULL→ simulated -3.4% MINOR (was +31.4%). CAXed scope expansion: queried DC share directly · emits sentiment for 8 BUs not 4. Predicted parity post-rebuild: 13/20 CIs ≤10% of DC live = 65% (up from 45% baseline). - 415 Splink standalone-runner iterations → pivot to Hybrid B (W8 D4 Thu · 10.0h). Triggered first full-scale Splink run 07:51 UTC. Runs 1-2 OOM'd at 474GB DuckDB temp on
cluster_pairwise_predictions(4.8B candidate pairs). Run 3 with first3 prefix (2.2B pairs) OOM'd atpredict(). Runs 4-9 explored CustomRule blocking · phone_normalized fix · per-email grain · super-email exclusion (17 placeholders generate 89% of noise) · union-find post-process. Runs 10-15: DC-supervised training with 50K pairs fromIndividualIdentityLink__dlm. Best Splink achievable: 14.48M (-4.10%) untrained threshold 0.5 · CANNOT match DC with Splink standalone. Pivoted to Hybrid B per user direction: snapshot DC's authoritative IR mapping NOW into static table + email-fallback for new records. 99.99% match rate verified across all 8 BUs. PR #4375 opened: 15,121,953 uids = +0.31% vs DC = AC7 PASS. Tier-2 email lookup added · 5 augmentcode HIGH/MED fixes resolved (dedup at contact_id_base · varchar casts · CTE renaming · degenerate pair filtering · sampling reduction). - 5PRs #4335 + #4375 merged AM · post-merge 11/17 = 65% parity verified (W8 D5 Fri AM · ~4h). PR #4375 merged 2026-06-18 18:28 UTC → triggered dbt build 06:39-06:41 UTC. PR #4335 merged 06:37 UTC via merge queue → cascade build same window. 17-CI parity batched query against PROD: 5 PASS · 6 MINOR · 3 DELTA · 3 FAIL = 11/17 ≤10% = 65% (up from 45% baseline). Headline wins post-merge: Active_Opp +528% → -5.70% · NL +25.9% → -0.05% · Adult_Non_Traveler PERFECT 0.00%.
- 6GBR semantic + 4 more PRs · all 5 merged (W8 D5 Fri PM · ~5h). User pushback "tienen que ser fixable porque están en DC" → 4 deep investigations: (a) PR #4385 GBR/Unified_Contactable semantic-replacement · literal AND-chain produced 94K vs DC 15.1M · reverse-engineered:
max(contact_exists)= 15.12M (-0.04% PERFECT). (b) PR #4386 CAXed Unexpected Value · 66K WJ records added to Neutral list. (c) PR #4387 TRH_Adult RCI→RTI · DCAdultTravelerBridge_ciousesRelated_Traveler_Id__cnotRelated_Contact_Id__c· single-char swap moved -94% FAIL → -4.21% MINOR (ET 111K → 1.82M). (d) PR #4388 Engaged_60D snapshot · DC share has the pre-computed CI with 148,924 engaged · 100% UID match → -2.4% PASS. (e) PR #4389 CAXed + LTP snapshots · UNPIVOT both DC wide-format CIs to long format. All 4 + #4391 (Closed_Lost snapshot pass-through) merged through Allan's batch approval + multiple lint/dedup/case fix cycles. Final standing 18/18 ≤10% = 100% parity. - 748-CI audit + PR #4392 with 12 more CIs (W8 D5 Fri EOD · ~3h). User asked "esas 18 cubren todas las CIs?" → counted 48 .json files in
force-app/main/default/calculatedInsights/. Classification: 30 end-user CIs + 16 helpers/rollups (intermediate layers of DC's 4-layer arch · we collapse into single CTEs) + 2 misc/test. Verified the 12 dbt-emitted-but-not-validated CIs: Email_Unsubscribe -81% · Latest_Tour_Cancelled_per_Ind -19% · Latest_Tour_NOT_Cancelled -20% · Contact_Created_Date_×8 BUs (6 PASS · Academy -8.49% MINOR · EFSA -88.59% FAIL). PR #4392 opened with 4 more snapshot models (Email_Unsubscribe · Latest_Tour_dates · Contact_Created_Date · reuse Closed_Lost) · same Hybrid B sunset-safe pattern · LEFT JOIN + COALESCE inint_ci__individual_by_bu. Predicted post-merge: 30/30 end-user CIs ≤10% of DC live = 100% real coverage.
What we need from EF / ET
PR #4392 covers 12 more CIs via Hybrid B snapshot pass-through (Email_Unsub · Latest_Tour_Cancelled_per_Ind · Latest_Tour_NOT_Cancelled · Contact_Created_Date_×8 BUs). 4 new snapshot models · authoritative-snapshot pattern · same dbt structure as PR #4391. CI green. Allan approved + merged all 7 previous PRs Friday in batch — this is the final piece for 30/30 end-user CIs ≤10% of DC live = 100% real coverage.
End of W8: cumulative ~$36,725 vs $25k NTE = 146.9% (over cap by ~$11,725). User explicit override 2026-06-08 ("olvidate del presupuesto") authorises continued work through cutover. P4 (SFMC final) + P5 (cutover) + P6 (hypercare) still ahead. Re-amendment conversation overdue.
dbt #2 = canonical + IR + CIs all 100% parity. Operational runbook (798 lines · GH #172) + cutover harness (1,074 LOC · GH #170) + parity report 2026-06-19 final ready. Hand-off cycle planning needed: knowledge transfer sessions + 2-cycle hypercare schedule + ownership transition of EF_DBT_* roles + Confluence architecture pages review.
$25 k NTE · spent vs remaining
NTE consumption
NTE CAP CROSSED · 146.9%. Cap was crossed mid-W7. User explicit override 2026-06-08 stands. W8 was the heaviest week (43.5h) — delivered Hybrid B IR pivot + 18/18 CI parity + 7 merged PRs + 1 in flight. Re-amendment conversation overdue ahead of W9 cutover gates.
Scope reality vs $25 k NTE
| Bucket | SOW low (h) | Actual h | Actual $ | Status |
|---|---|---|---|---|
| P0 · M1 Foundation (W1) | 16 | 14.0 | $2,040 | Done |
| P1 · dbt #1 normalize + replication (W2-6) | 25 | 112.0 | $16,180 | Done · #158 closed 54/54 parity |
| P2 · IR Phase 1 + Hybrid B (W6-8) | 37 | ~28.0 | ~$3,920 | Hybrid B AC7 PASS · sunset-safe |
| P3 · Canonical bodies (W6-7) | 37 | ~25.0 | ~$3,500 | Done · 7 bodies all AC7 PASS |
| P3b · CIs consolidation (W7-8) | - | ~38.0 | ~$5,320 | 18/18 = 100% parity · #4392 +12 in flight |
| P4 · SFMC + Marts (W7-W9) | 56 | ~5.0 | ~$700 | Activation mart scaffolded · SFMC strategy locked |
| P5 · Cutover (W9) | 14 | ~5.0 | ~$700 | Cutover harness done · runbook done · cutover commands TBD |
| P6 · Handoff + Hypercare (W9 + Jul) | 18 | ~3.5 | ~$490 | Runbook scaffolded · hypercare cadence TBD |
Trace: P3b CIs consolidation absorbed the biggest single chunk of overrun (~$5,320 vs originally out-of-SOW). 18/18 = 100% parity achieved · 12 more CIs in PR #4392 brings end-user coverage to 30/30 once merged. Options going into W9: (a) Amendment #3 for cutover + hypercare funding · (b) descope P6 to one hypercare cycle · (c) request EF-side ownership of cutover with Ohana advisory-only. Everything except cutover + hypercare is complete and shippable as-is.
W9 (22 Jun – 26 Jun) · cutover + handoff
- PR #4392 merge · final 12 CIs land → 30/30 end-user CIs at 100% real coverage
- Cutover dry-run · per
docs/runbooks/cutover-procedure.md· simulate marketing consumer cutover with reconciliation harness · validate AC4-AC10 with Mike + Adrian - SFMC S3 + activation · per
docs/integration/sfmc-s3-bucket-setup.md· S3 bucket provisioning + first end-to-end test of segment export to SFMC - Confluence architecture pages · final pass on dbt #1 + dbt #2 architecture docs + Hybrid B sunset-safe IR pattern documentation
- Hand-off kickoff · knowledge transfer session 1 of N with Mike + Adrian · ownership transition of
EF_DBT_*roles
- EF · NTE · Amendment #3 conversation overdue — scope decision for P5 cutover + P6 hypercare (one cycle vs two)
- Allan · PR #4392 review + merge approval (final CIs · same Hybrid B pattern as already-merged PRs)
- Mike + Adrian · cutover dry-run participation · receiving team readiness check · Confluence review
- Grabbe · SFMC S3 bucket provisioning per
docs/integration/sfmc-s3-bucket-setup.md - Vivek · sending-domain SF-side change kickoff confirmation
Where to dig deeper
docs/sf-analysis/calculated-insights/parity-vs-dc-360.md— final 100% parity report · 18 of 18 ≤10% of DC livedocs/sf-analysis/calculated-insights/dc-live-formulas/— 29 DC CI formulas downloaded as canonical referencedocs/sf-analysis/calculated-insights/qa-readiness-2026-06-17.md— cutover validation queriesdocs/sf-analysis/calculated-insights/rich-questions-2026-06-16.md— 3 decisions captured- Sprint log W8 D1-D5 with 43.5h tracked + per-day deliverables
- Architecture clarification: dbt collapses DC's 4-layer architecture (Helper→Base→Rollup→Final) into single CTEs · parity validated against Finals (the only end-user contract)
- Memory entries: literal-or-flag rule · Splink standalone exhausted (15 runs) · Hybrid B sunset-safe pattern · DC share is the source of truth for cluster-dependent CIs
eftours/de-dbt#4335— NL gate + CAXed scope + opp_per_bu dual-path (MERGED Friday)eftours/de-dbt#4375— Hybrid B IR snapshot · 15.12M uids = +0.31% vs DC AC7 PASS · sunset-safe (MERGED Thursday)eftours/de-dbt#4385— GBR + Unified_Contactable semantic-replacement (MERGED Friday)eftours/de-dbt#4386— CAXed Unexpected Value reason (MERGED Friday)eftours/de-dbt#4387— TRH_Adult RCI→RTI (MERGED Friday · -94% → -4.21%)eftours/de-dbt#4388— Engaged_60D snapshot from DC share (MERGED Friday · NULL → -2.4% PASS)eftours/de-dbt#4389— CAXed + Latest_Tour snapshots from DC share (MERGED Friday)eftours/de-dbt#4391— DC snapshots authoritative + Closed_Lost snapshot (MERGED Friday)eftours/de-dbt#4392— Email_Unsub + Latest_Tour×2 + Contact_Created_Date_×8 (OPEN · awaiting Allan)