EF
Status Report · Week 11 · Post-cutover GBR redesign

RLT-2023 GBR redesign deployed to PROD · 4,067,748 verified EXACT · Rich sign-off pending

Post-cutover consolidation week. Absorbed Rich's RLT-2023 5 spec ambiguities via audit + verified counts. Built and deployed PR #4579 (new int_ci__global_business_rule_redesigned parallel intermediate) after two-pass refactor: initial rebuild-from-scratch (1.44M · −63.6%) was corrected per user direction to delta approach + Opt 1 permissive, adding GLs to the eligible universe (+122K) + 3 new subtractive gates. Final PROD count 4,067,748 · EXACT match with the 2026-07-07 prediction · +2.80% vs current DC-replica. Rich confirmed WJ orphan mystery in email response (legacy UB Phoenix orders pre-ABE, customer_id → abe_id_c join already effective in our staging — no action required). RLT-3682 host-family CI ticket closed via 6 CIs already shipped. GH #224 (Phase 4 marts) closed. All legacy Jira tickets audited and categorized. Mart projection cutover pending Rich formal sign-off.

W11 · CLOSED (4/7 working days) PR #4579 merged · Redesigned GBR live in PROD 4,067,748 verified · EXACT prediction match ~315.25 h · ~$50,085 · ~200.3% NTE
Plan

Migration functionally complete · GBR redesign parallel-deployed · closeout in motion

W11 was the GBR redesign week: build the intermediate model that captures the RLT-2023 rule modifications (GL inclusion + currently_traveling/hosting exclusions + NB deliverability filter) as a parallel table (not switching the mart projection yet · Rich sign-off gates that). Two-pass refactor was needed because initial interpretation (rebuild-from-scratch AND-chain) produced a 1.44M count that felt wrong to the user · corrected to delta approach that inherits DC-replica baseline and only applies subtractive modifications + Opt 1 permissive GL uplift. Final PROD count 4,067,748 UIs · +2.80% vs current 3,956,948 · matches the 2026-07-07 batched-query prediction exactly to the row. Rich confirmed the WJ orphan investigation from RLT-4889 J.6 (legacy UB Phoenix orders that pre-date the ABE opportunity concept · travel.customer_id = contact.abe_id_c join is already effective at our staging layer · no code changes needed). ET unsub semantic still unclear pending Rich's clarification internally.

P0 · W1 Foundation
P1 · W2-6 Normalize + Replication
P2 · W6-8 IR Hybrid B
P3 · W6-7 Canonical bodies
P3b · W7-9 CIs · full DC parity
P4-P5 · W10-11 Cutover + GBR redesign
P6 · W12 Sign-off + Handoff

Receiving team unchanged: Mike Grabbe + Adrian LeDoux (ET Data Engineering). NTE 200.3% end-of-W12 D1 · user explicit override 2026-06-08 stands. Cutover PR + segments-sweep coordination locked behind Rich sign-off.

Done this week

What landed in W11 · GBR redesign shipped + PROD verified

  1. 1
    Monday · Rich session #3 closeout planning + W10 report + task cleanup + memory session log (W11 D1 Mon · 5.0h). Rich 30-min session confirmed all QA passed Friday 07-03 (automated agent + manual SQL + segment reproductions single-digit/dozens variance · complex multi-criteria queries pass). SFMC delay 4-6 weeks · handoff scope narrowed to setup + tagging only · admin account promised in Boston in-person · closeout meeting rescheduled Fri → Thu (backup Mon) due to travel · Rich signals future work at Cultural Care Au pair. Session log memory/2026-07-06-rich-session-closeout.md. W10 status report deployed to reports/w10-status-2026-07-04/ with 5 PRs merged + Rich sessions + budget snapshot 188% NTE. Landing page promoted W10 to "latest". Task list cleanup + Jira/GH triage.
    Session log + W10 report shipped · task list refreshed
  2. 2
    Tuesday · RLT-2023 audit + PR #4579 v1 opened (rebuild-from-scratch) + leave-one-out QA + user pushback + refactor to delta approach + 5-ticket update cascade (W11 D2 Tue · 7.0h). RLT-2023 audit (~2h): read ticket description · identified 5 ambiguities (GL inclusion contradiction · email >0 operator bug · TRH scope · WJ aggressive vs narrow · NB semantic) · resolved with user. PR #4579 v1 (~2h): initial int_ci__global_business_rule_redesigned as 11-gate AND-chain rebuild-from-scratch. Predicted 1,439,580 (−63.6% vs 3.96M). Schema.yml tests (not_null · unique · accepted_values). CI green · Cortex reviewer bot flagged path-based reviewer requirement — replied with reasoning + explicit Allan request. Leave-one-out QA (~1h): batched query showing marginal impact per gate. Discovered CAXed sentiment biggest driver (-780K), TRH -481K, New_Lead -162K, WJ aggressive only -5.9K, GL uplift +732. User pushback "no debería caer tanto" → refactored to DELTA approach (~1.5h): inherit DC-replica baseline · only apply new subtractive gates · force-pushed. Predicted 3,946,281 (-0.27%). Ticket updates (~0.5h): 3 comments on RLT-2023 + GH #135 with corrected counts + 12-rule trace + segments-sweep reminder. RLT-3682 host_family reply + close (~0.5h): verified 6 CIs (HSEY/CCAP TRH_host_family + currently_hosting) with counts · replied Rich + transitioned to Done. GH #337/#339/#340 comments (~0.5h): status updates + 8-BU source inventory correction (Gap Year included).
    PR #4579 open · 3 tickets commented · RLT-3682 closed · GH #340 confirmed no-recovery
  3. 3
    Wednesday · Off (W11 D3 Wed · 0h). Personal time.
    No commits · no CI activity
  4. 4
    Thursday · PR #4579 refactor Opt 1 permissive + force-push + count prediction 4,067,748 (W11 D4 Thu · 2.0h). User selected Opt 1 permissive (add ALL GLs to eligible universe). Refactored model to UNION (current_gbr = 1 OR is_gl_any_bu = 1) + apply subtractive gates. Schema.yml description updated with GL uplift semantic + segments-sweep reminder. Verified count predictions via batched Snowflake query: +122,439 GLs added · -1,258 currently_traveling · -4 currently_hosting · -10,382 NB → 4,067,748 final (+2.80% vs 3.96M current). Force-pushed to feat/135-gbr-redesigned. CI green.
    Commit c67e5b253 on branch · CI passing
  5. 5
    Friday · PR #4579 merged + PROD CD verify EXACT match + Rich WJ orphans response + RLT-2023 final QA table + RLT-4889 verified + GH #224 closed (W11 D5 Fri · 4.0h). AM merge queue processed PR #4579 at 13:56 UTC · commit 1cbfcfd9adfb on main. Redesigned model deployed. Discovered PR APPROVED by brunstein-ef but mergeStateStatus was UNKNOWN due to gh CLI cache stale · enabled auto-merge · queue picked it up + merged cleanly. PROD CD verify: EF_DBT_PROD.EF_DATA_HUB_CH_INTERMEDIATE.INT_CI__GLOBAL_BUSINESS_RULE_REDESIGNED · 4,079,388 total rows · 4,067,748 flagged · EXACT match with 2026-07-07 QA prediction (zero delta). RLT-2023 QA comment (~1h): comprehensive per-rule impact table (13 gates: inherited 1-10 · new 11-13) + count justification arithmetic + defensibility for Rich sign-off. Rich WJ orphans response (~1h): Rich confirmed legacy UB Phoenix orders pre-ABE · pointed at travel.customer_id = contact.abe_id_c join. Verified in canonical: 330,937 WJ SOs with NULL opp_id + 232,099 with orphan opp_id · ALL have valid individual_id__c → identity resolution unaffected · contact linkage works · no code changes. Confirmed PHOENIX_ID_C vs ABE_ID_C distinction (24-char hex). RLT-4887 Rich response: ET unsubs still unclear (Rich still trying) · WJ EMAIL_OPT_IN confirmed brand-level. Multi-ticket updates (~1h): comments on RLT-4887/4889 (Jira) + GH #337/#338/#340. Closed GH #224 Phase 4 marts as complete. GH #135 reopened → In Review with PROD-verify status.
    PR #4579 MERGED · PROD verified · GH #224 closed · 3 Jira comments · 3 GH comments
  6. 6
    Saturday · Sunday · Off (W11 D6-D7 · 0h). Personal time.
    No commits · no CI activity
RLT-2023 · deployed + verified

Redesigned GBR PROD verification — exact match with prediction

MetricPredicted (2026-07-07 QA)PROD verified (2026-07-10)Match
Total rows in INT_CI__GLOBAL_BUSINESS_RULE_REDESIGNED4,079,3884,079,388EXACT
Distinct UIDs4,079,3884,079,388EXACT · uniqueness test pass
SUM(global_business_rule_flag_redesigned)4,067,7484,067,748EXACT
Delta vs current DC-replica GBR (3,956,948)+110,800+110,800EXACT (+2.80%)

Model logic: (current_dc_gbr = 1 OR is_gl_any_bu = 1) AND is_currently_traveling = 0 AND is_currently_hosting = 0 AND NB_Status NOT IN ('invalid','disposable','bounced'). Rules 1-10 inherited from DC baseline · rules 11-13 new subtractive gates. Segments-sweep required in same release as cutover PR (add explicit NOT GL to any segment that shouldn't target GLs).

Pending external action

What we need from EF / ET

Rich · RLT-2023 sign-off
Redesigned GBR count validation + segments-sweep coordination

The redesigned model is live at EF_DBT_PROD.EF_DATA_HUB_CH_INTERMEDIATE.INT_CI__GLOBAL_BUSINESS_RULE_REDESIGNED · 4,067,748 UIs · +2.80% vs current mart. Rich needs to QA + sign off before we can: (a) trigger the mart projection cutover PR to switch mart_marketing_individual_activation.GLOBAL_BUSINESS_RULE_FLAG from DC-replica to redesigned; (b) enable scheduled automated refreshes for QA views. Rich flagged currently_hosting = 4 as a potential concern to double-check in QA.

Session 2026-07-13 · Rich has visibility on the count · will run own QA before sign-off.
Rich · SFMC admin alias
Still blocking S3 bidirectional + unsub refactor

SFMC admin account for me was promised in session #2 (2026-07-02) and reiterated 2026-07-13. Rich lacks admin rights and cannot configure S3 bucket connections. Still no update on the alias creation. Blocks JonatanFG/EF#167 (SFMC engagement S3 ingest) + #337 (Email_Unsubscribe SFMC-source refactor).

Latest update from Rich (2026-07-10): still unable to configure S3 bucket without admin.
EF · NTE re-amendment
$25k NTE crossed 200.3% · post-closeout scope conversation overdue

End-of-W12 D1: cumulative ~$50,085 vs $25k NTE = 200.3% (over cap by ~$25,085). User explicit override 2026-06-08 authorises continued work through closeout. Cutover PR + handover docs finalization + Andrew Tiemann unsub strategy consult + Jen consult on WJ/Academy pre-2018 orphans still ahead.

Options: (a) Amendment #3 for post-cutover work · (b) descope hypercare to advisory only · (c) close out with as-is scope covered.
Rich confirmations · WJ orphans + ET unsubs

Rich's email response validated the source-integrity story

WJ orphan SOs · confirmed legacy UB Phoenix orders pre-ABE. Rich pointed at prt_loyalty_travel.customer_id = prt_loyalty_contact.abe_id_c as the correct linkage path. Verified in canonical:

CategoryCountindividual_id__c populated?
WJ SOs total in canonical1,154,314—
With opportunity_id NOT NULL823,377—
With opportunity_id NULL (legacy UB Phoenix)330,937100%
Orphan opp (opp_id references missing)232,099100%

Both cohorts already have valid individual_id__c · identity resolution + contactability unaffected. The customer_id → abe_id_c join Rich proposed is already effective at our staging layer. Opportunity-dimension metrics (stage, close date, value) are legitimately NULL for legacy UB rows · semantically correct given they pre-date the ABE opportunity concept.

ET unsub semantic · still unclear. Rich quote: "I haven't found someone who has clarity on how unsubs work for ET, still trying to get an answer." Our post-#4525 US canonical implementation continues to give reasonable per-BU rates (WJ 30% · ET 4.2%). The full SFMC-source refactor stays deferred in JonatanFG/EF#337 until Rich has the answer AND SFMC access is unblocked.

WJ EMAIL_OPT_IN · brand-level confirmed. Rich: "EMAIL_OPT_IN does represent a marketing opt in to email. We keep this at a brand level." Matches our current stg_world_journeys__contact_email_variants.sql implementation (inverts to email_unsub at contact-level). No changes required.

Budget snapshot

$25 k NTE · spent vs remaining

NTE cap
$25,000
SOW Amendment #2 · CROSSED W7
Spent to date
$50,085
~315.25 hours · ~200.3% of NTE
Over cap by
$25,085
User override 2026-06-08 authorises continued work
W11 burn (D1-D5)
18.0 h
$2,680 · Mon (5.0) · Tue (7.0) · Thu (2.0) · Fri (4.0) · Wed/Sat/Sun off

NTE consumption

NTE CAP CROSSED · 200.3%. W11 burn 18.0h (Mon/Tue/Thu/Fri) reduced vs W10's 36h — mostly consolidation + tickets · single major code PR (#4579). User explicit override 2026-06-08 stands. Re-amendment conversation still pending ahead of closeout.

Scope reality vs $25 k NTE

BucketSOW low (h)Actual hActual $Status
P0 · M1 Foundation (W1)1614.0$2,040Done
P1 · dbt #1 normalize + replication (W2-6)25112.0$16,180Done · #158 closed 54/54 parity
P2 · IR Phase 1 + Hybrid B (W6-8)37~28.0~$3,920Hybrid B AC7 PASS · sunset-safe
P3 · Canonical bodies (W6-7)37~25.0~$3,500Done · 7 bodies all AC7 PASS
P3b · CIs consolidation (W7-9)-~52.0~$7,280Full DC parity · 12/12 Rich issues closed
P4a · Cortex enablement + CI completeness (W9-10)-~22.0~$3,080PR #4458 cross-BU JOINs · #4476 30/30 DC parity
P4b · SFMC + Marts (W7-12)56~5.0~$700Activation mart scaffolded · SFMC alias pending
P5 · Cutover (W10-12)14~15.0~$2,100DC sunset survived · handoff notes shipped · SFMC S3 last mile
P5b · RLT-2023 GBR redesign (W11-12)-~18.0~$2,680Parallel model deployed · Rich sign-off pending
P6 · Handoff + Hypercare (W12 + Jul-Aug)18~4.0~$620Runbook + handoff notes done · hypercare cadence TBD

Trace: P5b (RLT-2023 GBR redesign) is the new sub-bucket added in W11 · out of original SOW scope. All fixable code items closed; only signature-gated cutovers + SFMC-blocked follow-ups remain.

What's next

W12 (13 Jul – 19 Jul) · Rich sign-off + cutover + handoff

Build deliverables · W12
What lands
  • Rich RLT-2023 sign-off · Rich runs own QA on 4,067,748 count + segments-sweep coordination decision
  • Cutover PR (~10 min once signed) · swap mart_marketing_individual_activation.GLOBAL_BUSINESS_RULE_FLAG projection from DC snapshot → redesigned model
  • Segments-sweep coordinated · add explicit NOT GL clause to any segment currently downstream of GBR that shouldn't target GLs (marketing team ownership)
  • Automated refresh scheduling · enable dbt Cloud PROD CD scheduled refreshes for QA views post Rich sign-off
  • Wednesday 30min call with Rich · phone update (Rich travel · short format)
  • Handover docs finalization · docs/dc-sunset/handoff-notes.md polish + Confluence architecture pages (dbt #1 US + dbt #2 CH + Hybrid B sunset-safe IR pattern)
  • Handover kickoff session with Mike Grabbe + Adrian LeDoux (ET Data Engineering)
  • Invoice for W10-W11 work
Stakeholder asks · W12
What we need from EF / ET
  • Rich · QA + sign-off on 4,067,748 · SFMC admin alias creation · Jen consult on WJ/Academy pre-2018 orphans · previous invoice status
  • Marketing team · segments-sweep coordination decision (via Rich) · confirm segments that need explicit GL exclusion post-cutover
  • EF · NTE · Amendment #3 conversation — scope decision for P6 hypercare (one cycle vs two vs advisory-only)
  • Andrew Tiemann · 30-min meeting to finalise unsub strategy (central SFMC vs per-product) · required before writing marketing-lead proposal · deferred until Rich has ET unsub clarity
  • Mike + Adrian · handover kickoff session scheduling · walkthrough of docs/dc-sunset/handoff-notes.md
References

Where to dig deeper

This week's specs + docs
Landed this week
  • PROD table: EF_DBT_PROD.EF_DATA_HUB_CH_INTERMEDIATE.INT_CI__GLOBAL_BUSINESS_RULE_REDESIGNED · 4,067,748 flagged / 4,079,388 total
  • Model: ef_data_hub_ch/models/intermediate/calculated_insights/int_ci__global_business_rule_redesigned.sql
  • Memory: 2026-07-06-rich-session-closeout.md · 2026-07-13-rich-session-rlt-2023-review.md
  • Handoff docs: docs/dc-sunset/handoff-notes.md · docs/integration/canonical-joins-fk.md · docs/dc-sunset/dc_live_baseline_2026-06-30.md
  • Sprint log W11 D1-D5 + W12 D1 with 20.5h tracked + per-day deliverables
Cross-repo PRs + tickets
Deployed / commented this week
  • eftours/de-dbt#4579 — parallel int_ci__global_business_rule_redesigned model · Opt 1 permissive · MERGED 2026-07-10
  • JonatanFG/EF#135 — RLT-2023 GBR redesign · PROD-verify comment · In Review
  • JonatanFG/EF#337 — Email_Unsub SFMC-source · Rich ET unclear + WJ brand-level confirmed comment · OPEN
  • JonatanFG/EF#338 — Rich WJ orphans confirmation comment · CLOSED
  • JonatanFG/EF#340 — Rich confirmation on customer_id/abe_id_c path · CLOSED
  • JonatanFG/EF#224 — Phase 4 marts sublayer · CLOSED complete
  • Jira RLT-2023 · verified counts + 13-rule trace comment · Under Review
  • Jira RLT-3682 host_family · closed as Done · 6 CIs shipped
  • Jira RLT-4887 unsub rate · Rich ET clarification comment · Under Review
  • Jira RLT-4889 J.6 orphans · Rich confirmation comment · Closed