9-week build · canonical bodies shipped + CIs end-to-end DAG

Canonical scaffolding (P3) closed Monday: 7 entity bodies (account / opportunity / sales_order / 3 contact_points / individual_unified + individual passthrough split) landed in PR #4239 with row-count parity all PASS ±0.2% vs DC. CI consolidation (#166) Phase A-C shipped Thursday on PR #4282: 17 of 27 CI columns active (62% of mapped 48 DC CIs), collapsing DC's Helper→Base→Rollup→Final 4-layer arch into single CTEs. Remaining 10 CIs unlock on RT1 stg enrichment (4) + G1/G2/G3 follow-on tickets (6). Cutover harness scaffolding (#170) closed Tuesday. NTE cap crossed this week ($28,635 vs $25k = 114.5%) — user explicit override 2026-06-08 authorises continued work through cutover.

Receiving team unchanged: Mike Grabbe + Adrian LeDoux (ET Data Engineering). Splink real-data unblock: Mike approved provisioning the SPLINK_RUNNER_SKU runner label via Rob Edwards (GitHub org owner) — secrets in place since 2026-06-10, awaiting Mike's runner label provisioning. Re-amendment conversation pending end of W7.

What landed in W7

1. 1
   7 canonical bodies landed in PR #4239 (W7 D1 Mon · 8.0h). Entity-1:1 canonical bodies via parallel subagents: int_canonical__account (52 cols · UNION ALL 7 BUs · WJ has no Account model · 447,773 rows = +0.12% vs DC) · int_canonical__opportunity (96 cols · 8 BU UNION ALL · 4,163,583 rows = +0.16%) · int_canonical__sales_order (101 cols · 8 BU UNION ALL · 6,181,811 rows = +0.06%). Then 3 ContactPoint canonicals (email / phone / address) as pre-IR passthrough matching DC's *_Data_Share__dlm views. Plus individual passthrough/unified split per user clarification: renamed prior int_canonical__individual → int_canonical__individual_unified (post-IR · 15.43M · +2.13% vs DC) + created new int_canonical__individual targeting Individual_Data_Share__dlm (pre-IR passthrough · 96 cols · 16.69M vs DC 16.66M = +0.17% AC7 PASS).

   PR #4239 · 12+ commits on feat/175a-canonical-bodies · all 7 bodies row-count PASS ±0.2%
2. 2
   Cutover reconciliation harness PR #4259 + operational runbook (W7 D2 Tue · 13.25h). 1,074 LOC across scripts/parity/ (compare.py · snapshots.py · connections.py · run_parity.py · cutover_reconciliation.py · ir_reconciliation.sql · report.py) + 8 canonical YAML configs + tri-state report renderer. Pre-staged for AC4-AC10 reconciliation when secrets land. Closed JonatanFG/EF #172 (operational runbook · 798 lines · 11 sections + 2 appendices: architecture, monitoring/alerting, failure scenarios, escalation path, dbt #1+#2 lineage, Prefect docs). 9 augmentcode review findings resolved (row-count masking · INFORMATION_SCHEMA unquoted · composite-PK NULL sentinel · MCP_READER → writable role · try/finally cleanup · FQTN routing · div-by-zero · YELLOW/RED rendering). EOD Jira backlog cleanup: closed 7 of 8 RLT tickets sitting in Under Review (3812 · 3969 · 3970 · 3972 · 3973 · 3975 · 3976 · 3990) with English QA-closure comments + GH mirror links + transitions to Finalizada.

   PR #4259 closes #170 · #172 operational runbook · 7 RLT closed in batch
3. 3
   Splink runner RSA key-pair auth migration + 100k smoke test PASSED (W7 D3 Wed · 12.5h). Allan provisioned EF_SPLINK_RUNNER service account 2026-06-10 with 3 repo secrets (SNOWFLAKE_ACCOUNT_CH · SNOWFLAKE_CH_SPLINK_USERNAME · SNOWFLAKE_CH_SPLINK_PRIVATE_KEY) + RSA key-pair auth (no legacy password). Adapted scripts/splink-runner/run_splink_ir.py.connect_snowflake() to load PEM → PKCS8 DER via cryptography.hazmat. Initial smoke at limit=1000 dry_run=true failed grants; Allan fixed in real-time (GRANT ROLE EF_DBT_PROD_RW TO USER EF_SPLINK_RUNNER). Re-run PASSED in 2m 37s on ubuntu-latest free runner. 100k scale-up PASSED in 47s total · Splink compute trivial (predict() done in 0.15s · clustering done in 0.4s). Only blocker for full 16.3M Splink real: memory (SPLINK_RUNNER_SKU = ubuntu-22.04-large 16-core/64GB pay-per-min). Crafted Mike-facing cost summary correcting earlier r6i.4xlarge $50k/yr misunderstanding.

   Runs 27332101766 + 27332314774 · 100k smoke 47s end-to-end · runner SKU label awaiting Mike via Rob Edwards
4. 4
   #166 CIs Phase A-C build + PR #4282 opened (W7 D3 Wed · ~6h). Re-scoped GH #166 from "single layer ~37 models" to 2 tables + 1 view (94% reduction · DC's Helpers→Base→Rollup→Final is a platform workaround that Snowflake+dbt obviates). Phase A scaffold: 3 model files (int_ci__individual_by_bu LONG · int_ci__individual_global per-individual · mart_marketing_individual_activation view) + YAML schemas. Phase B parallel CTE translation via 3 subagents (opp-side 5 chains · individual-side · so-side). Phase B/C application: applied 7 HIGH-confidence chains in int_ci__individual_by_bu (Active_Opp · Closed_Lost · New_Leads · Contact_Exists · Contact_Created_Date · Email_Unsub · TRH_Home) + 4 RT1-blocked structural-correct + 3 MEDIUM. Phase C global: 7 HIGH/MEDIUM cross-BU CIs in int_ci__individual_global + Global_Business_Rule orchestrator. 17 of 27 CI columns active today (62% of mapped 48 DC CIs).

   PR #4282 · 1,373 LOC · 8 files · all dbt Quality Gate green · cc'd @mbgrabbe @aledoux
5. 5
   4 augmentcode review fixes on PR #4282 + QA materialisation + 3 spotcheck findings (W7 D3 Wed). (1) HIGH · is_new_lead gated on contact_exists = 1 to prevent cross-join individual_bu_grid over-firing. (2) MEDIUM · per-BU date column split (ET → tour_*, others → service_*) for latest_tour_* global cols. (3) MEDIUM · added ccap_traveled_returned_home to mart view. (4) MEDIUM · PEM normalize helper before load_pem_private_key(). QA spotcheck on dbt Cloud run 43045206 revealed 3 distinct problems: WJ JOIN broken (99% of WJ carries |<BUSINESS_CODE> suffix that bridge strips → 0 matches; fix via split_part(individual_id__c, '|', 1) ships 25ad08b8a, WJ now 933k) · Language ID type mismatch (accountid vs individual_id) · Active_Opportunities semantic gap (DC Helper SQL emits 1 when individual has NO opp in BU, our model ships displayName-intent · numbers diverge dramatically). Documented + posted to PR #4282 + Rich/Adrien.

   Augmentcode fixes commit 0ba4586d4 · WJ fix 25ad08b8a · 3 spotcheck findings flagged
6. 6
   2 EF Confluence pages on Snowflake stack + 4 lessons (W7 D2 + ad-hoc throughout the week). Authored architecture pages for ET Data Engineering handoff: dbt #1 (US normalize layer · 57 views in EF_DATA_HUB.ANALYTICS port-and-evolve approach) and dbt #2 (CH harmonisation / IR / CIs / segments). 4 memory-system lessons logged: (a) Adrien's "vanilla dbt structure" preference (W3 carry-over · validates 4-layer schema split decision) · (b) Splink RSA-key-pair auth migration path · (c) PR #4239 individual passthrough/unified split rationale · (d) GH org-owner permissions required for runner labels.

   Confluence pages on EF site (read-only via ef_jira MCP) · 4 lessons under ~/.claude/projects/-Users-jonathanfrailegutierrez-code-EF/memory/
7. 7
   Tickets closed across the week (Jira + GH) — total 9. Jira (W7 D2 EOD): RLT-3812 · RLT-3969 · RLT-3970 · RLT-3972 · RLT-3973 · RLT-3975 · RLT-3976 · RLT-3990 (7 of 8 transitioned to Finalizada with QA-closure comments + GH mirror links · the 8th left in Under Review because GH #175 still open / PR #4239 pre-merge). GitHub: #170 cutover harness Done (PR #4259) · #160 EF_SPLINK_WH grants Done. PR #4259 also unblocks #169 (cutover hour-of-cutover commands ready) and #171 (cutover runbook drafts under docs/runbooks/cutover-procedure.md).

   7 RLT closed in batch · 2 GH issues Done · 2 more cutover tickets pre-staged

Blocked-on-others

$25 k NTE · spent vs remaining

NTE consumption

NTE CAP CROSSED · 114.5%. Per CLAUDE.md rule, the 95% threshold was crossed end of W6 and 100% crossed mid-W7. User explicit override 2026-06-08 ("olvidate del presupuesto, no estamos cumpliendo con lo que pidieron") authorises continued work through cutover. Realistic landing: P4 (CI parity finalisation + SFMC) + P5 (cutover) + P6 (hypercare) all still ahead. Re-amendment conversation recommended before P5 cutover gates.

Scope reality vs $25 k NTE

Trace: NTE cap crossed mid-W7. All 6 SOW workstreams now have some W7 burn. P3b (CIs consolidation) is new work that was originally out-of-SOW; it absorbed ~$1,400 of the overrun. Options going into W8: (a) Amendment #3 for cutover + hypercare funding · (b) descope P6 hypercare to one cycle · (c) request EF-side ownership of cutover with Ohana acting in advisory-only mode.

W8 (15 Jun – 19 Jun) · CI parity sprint + cutover prep

Where to dig deeper