9-week build · P1 close-out + P3 design lock

The replication pipeline keeps running every night. P1 (bronze staging) is closing out: bootstrap PR merged Monday, Phase B seeds + macros landed, and all 8 BU contacts staging models materialize in DEV against real data. Rich's reconciliation review closed Friday morning — the canonical model field list is now locked at 85 fields, and #175a (P3a canonical) is ready to start once the 3 staging PRs merge. Splink IR (P2) build window is the next workstream, in parallel to the canonical build.

Receiving team unchanged: Mike Grabbe + Adrian LeDoux (ET Data Engineering). Rich transitioning out — weekly standups continue; this report keeps you in the loop.

W4 cleared the critical-path bottleneck

Two long-running blockers cleared this week — Rich's reconciliation review came back, and Adrien approved the first two of three staging PRs. Plus the structural verification of the CH ↔ US replication confirmed the pipeline is healthy across all 49 source tables (rows + columns + bytes identical). Net: canonical build (#175a) can start, blocked only on the third PR review.

What landed in W4

1. 1
   Bootstrap PR eftours/de-dbt#3868 merged Monday (W4 D1). The vanilla-audit restructured project (8 → 4 vanilla schemas, UPPERCASE prefixed, sources outside models/) is now on main. Pass 3c DDL executed on HH82036 — dropped the 8 obsolete unprefixed schemas across DEV/QA/PROD (24 schemas total). The cross-repo project surface is now clean and Phase B work moves forward on top of the merged base.

   Closes #143 + #155 + #158 Phase A · PR #3868 also brought Phase B seeds (16-cat lead_source_mappings · 82-row language_iso_mappings) and 8 normalize macros (lead_source, language_iso, gender, geo, age, contactable, etc.) live in PROD
2. 2
   Phase C pilot — stg_ccap__contacts ported end-to-end (W4 D3). First real per-BU staging model. UNION of Contact (host-family record type, non-au-pair, US-only) + Lead (US, non-deleted, not-yet-converted), LEFT JOIN to Account for is_primary_contact, full utm_source normalization (~20 mappings preserved from legacy), lead_source_normalized via seed JOIN, survivorship_ts macro wired. Materializes 878,903 rows in DEV (346,778 Contact + 532,125 Lead) with 81.2% seed coverage on lead_source_normalized.

   PR eftours/de-dbt#4027 · CI green · Adrien-approved Friday
3. 3
   7 BU staging contacts extended (W4 D4) — same pattern applied to the other 7 BUs. Real ports for academy (PersonAccount + Contact-Guardian UNION, 2.77 M rows), hsey (Contact + Lead UNION, 1.59 M), language (Juno Kafka source, 33.4 M), student_tours (data_hub_share, 5.79 M), study_abroad (PLB share, 16.1 k), gap_year (PLB share, 132 k), world_journeys (multi-tenant via BUSINESS_CODE, 8.69 M). Each materializes ≤ 2.4 s in DEV. Row counts reconcile to filter logic to the exact row — verified independently via raw-source count queries.

   PR eftours/de-dbt#4059 · Ready for Review since Thursday EOD · awaiting Adrien (was draft when he reviewed the others on Friday AM)
4. 4
   Sources refactor PR (#3989) Adrien-approved. Splits the monolithic sources/sources.yml (~181 lines, 9 sources in one file) into per-product _<product>__sources.yml files inside each models/staging/<product>/ folder, aligning with the dbt Labs 1.12 best practice Adrien flagged on May 13. Bonus fix on a missing Fivetran freshness config for salesforce_ccap. No new logic — just organization.

   Approved Friday AM (post Friday morning re-nudge) · awaits merge
5. 5
   Rich's reconciliation review closed Friday morning — canonical at 78 fields. Rich returned the DC-direct verification XLS with his decisions on all 17 ambiguous fields: 6 Drop · 1 Keep · 2 Keep-on-Source-only · 1 Use-for-CI · 7 ghost fields Derive-in-DBT (rather than deploy as DMO columns). No DC metadata changes needed. Canonical model #175a active field count: 89 → 78. Design-locked.

   Reply saved: docs/sf-analysis/unifiedindividual-fillrate-rich-final-decisions-2026-05-22.csv · Summary: docs/sf-analysis/unifiedindividual-rich-final-decisions-summary-2026-05-22.md
6. 6
   DC-direct verification + 49-table CH ↔ US audit. Earlier in the week (W4 D3-D4), did a full DC-direct verification of the 17 ambiguous fields against UnifiedIndividual__dlm in datahub_prod (15.09 M rows) — including the surprise finding that the 7 "ghost" fields don't actually exist on the DMO (PROPOSED in dataCloudMappings/ + recon rules but never deployed). Then verified the CH-side replica matches US consolidation 100% on row count, column count, AND storage bytes across all 49 source tables (285 M+ rows total). Original Fivetran sources sit ~0.01-0.02% above CH due to nightly task lag — expected.

   PR #317 (Rich XLS) merged W4 D4 · RLT-3812 commented with the per-BU replication match table

What's running every night + what's now built on top

Nightly schedule (UTC)

Schemas + sizes (CH side)

EVALUATION_QUESTION_RESPONSES_V from W2 has been on CH for a week now — included in the 49 source count above.

Schema surface on HH82036 (post Pass 3c · obsolete schemas dropped W4 D3)

Where each in-flight ticket landed

Closed this week

Now ready for the build · pending PR merges only

Still pending or stakeholder-blocked

• PR #4059 — Adrien review (was draft when he reviewed the other two on Friday AM; gentle re-nudge sent, expecting Monday)
• #142 — Segments reframed: MC primary / Snowflake Plan B; Vivek call gates the methodology
• #161 + #162 — Splink IR + parity QA (incorporate Amon's modified 10-rule design from the parallel CH workstream's SF PS session); next workstream after canonical build kickoff
• #224 — Marts sublayer (Cortex / cross-regional / BI consumers); spec write pending after #158 Phase C fully merges
• #136-#140 — RLT-* normalize tickets: tracked as follow-up enrichments on the per-BU staging models once #4059 merges (nationality maps, host family aggregation, contact_origin_bu split, etc.)
• RLT-3991 — 5 HSEY closed-lost codes (CN/CY/EF/APP/INV → "Other"): Rich to confirm

$25 k NTE · spent vs remaining

NTE consumption

54.6% consumed end of W4. Trajectory still healthy: P0 + P1 done in 4 active weeks with ~45% of the cap reserved for P2 (Splink IR) through P6 (handoff). EF-engagement work only — the parallel ohanacloud-CH workstream (IR design with SF PS) is tracked separately and not billed against this NTE.

How this is calculated

Three rate tiers per SOW Amendment §4 (depending on day intensity):

Total: 95 h · $13,640. Detailed daily breakdown lives in sprint-log.md.

Phase status

P1 closed at 78 h vs 25-h SOW estimate (over-run absorbed). Phase scope materially expanded over W2-W4: #143 + #159 + #155 bootstrap + vanilla-audit restructure + CI debug + Pass 3 DDL + Phase B seeds + Phase C real ports for all 8 BUs + CH↔US 49-table audit. Cumulative NTE at 54.6% leaves ~$11.4 k for P2 → P6. Pacing P5/P6 conservatively; no escalation needed.

W5 (26 May – 1 Jun) · canonical build kickoff · IR design intake

P3a · canonical_individual model build

With the design locked at 78 fields and 8 BU staging contacts views materialized in DEV, the canonical model build is the natural next bite. canonical_individual sits in EF_DATA_HUB_CH_INTERMEDIATE and references the 8 per-BU staging views through the survivorship pattern. Splink IR (#161) work runs in parallel — the rule design from the parallel CH workstream's SF PS sessions can start integrating here.

Looking past W5 — phase trajectory

Critical path now: PR #4059 merge → #175a canonical_individual → #161 Splink IR (parallel) → #166 CIs → #142 segments → cutover. The 3 open PRs are the last bronze-layer gate.

Where to dig deeper