9-week build · architecture pivoted W2 day 2

Architecture simplified Tuesday 6 May after a call with Adrien and Allan: single dbt project in CH (replacing the planned dbt-#1-US + dbt-#2-CH split). Raw US data lands in a consolidation DB (EF_DATA_HUB_RAW) and replicates nightly to CH. The 57 legacy ANALYTICS views stay alive in US as deprecating-legacy. Phase numbering unchanged; phase content adjusted accordingly.

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

Tue 6 May AM · architecture · Wed 7 May PM · refinements

What landed in W2

1. 1
   #143 — CH dbt home rewritten + executed (closed). Original 2-DB sandbox/prod from W1 evolved into the post-Rich layout: 3 environments (EF_DBT_DEV / EF_DBT_QA / EF_DBT_PROD) × 8 schemas each (staging, normalize, sfmc_engagement, canonical, ir, cis, segments, marts). 3 RW roles renamed/created with cross-env grants. ACCOUNTADMIN revoked post-verification (least-privilege restored).

   Spec: specs/setup/143-ch-dbt2-home.md · DDL: scripts/setup/143-ch-dbt-home-rewrite.sql · status: Done
2. 2
   #159 — Replication group US → CH executed end-to-end (closed). Three parts in one day:

   Part A — provisioning. Consolidation DB EF_DATA_HUB_RAW on TAC01291 with 9 data schemas + a PUBLIC orchestration schema. Dedicated copy-job role with 815 grants (sources via IMPORTED PRIVILEGES on the shared DBs, write privileges on the target schemas, EXECUTE TASK on account, USAGE on REPORTING_SALESFORCE_WH).

   Part B — copy tasks. 1 root task + 48 child tasks (one per source table) scheduled at 00:00 UTC nightly. Smoke test landed 48 tables / 283 M rows / 47 GB in EF_DATA_HUB_RAW on US — two Snowflake quirks fixed mid-flight (CREATE TASK clause order; predecessor-schema constraint forces children + root to share a schema).

   Part C — replication group. EF_DATA_HUB_RAW_TO_CH_RG on US side (CRON 0 1 * * * UTC) plus the replica on CH. Initial cross-region refresh complete with full byte-parity. Four read roles granted on the replicated DB: EF_DBT_DEV_RW, EF_DBT_QA_RW, EF_DBT_PROD_RW, MCP_READER.

   Spec: specs/setup/159-us-ch-replication-group.md · status: Done
3. 3
   Cross-repo PR eftours/de-dbt#3868 — bootstrap content under ef_data_hub_ch/. Drop-in artefacts ready for Allan: dbt_project.yml (3-env framework, 8 schemas, single CI layer, marts active), profiles.yml.template, sources.yml (49 source tables across 9 schemas), 6 reusable macros extracted from the 57 legacy views (bu_attribution, cancellation_reason_normalized, phone_e164, gender_canonical, country_code_normalize, traveler_age_3depth), 7 layer placeholders. CI runs green except 📦 Compile — expected, no models yet.

   Allan to review + finalize the dbt Cloud project connection · awaiting first review
4. 4
   Three decks refreshed for Monday's wider audience presentation. Architecture options · CH dbt project structure (decisions locked, 12 slides, +2 new for segment strategy + reconciliation rules) · migration plan deck. HTML edits + PPTX re-renders.

   Decks: docs/migration-plan/{architecture-options,dbt-project-structure,migration-plan-deck}-2026-05-05.html
5. 5
   Ticket housekeeping. 6 tickets closed (#143, #159, #163 + #164 + #165 CI layers collapsed, #215 structure proposal). 10 status-updated (#155, #158, #161, #162, #166 reframed + renamed to single CI layer, #167, #175, #224, #142, #225). Net board state cleaner: P0/P1 setup work largely done, P1 build is now actually unblocked.

What's running every night now

Nightly schedule (UTC)

Schemas + sizes (CH side)

Note: data_hub_share shows 7 tables once EVALUATION_QUESTION_RESPONSES_V (added today on US) lands on CH at the next 01:00 UTC refresh.

Where each in-flight ticket landed

Closed this week

Now unblocked for build

Still pending or scope-shifting

• #155 — dbt Cloud project bootstrap (Allan; cross-repo PR #3868 has the content ready, awaiting first review)
• #142 — Segments reframed: MC primary / Snowflake Plan B; Vivek call gates the methodology
• #166 — single CI layer build (renamed; replaces #163-#165); pending spec write
• #161 + #162 — Splink IR + parity QA, with the new survivorship rules
• #224 — Marts sublayer (NEW; Cortex / cross-regional / BI consumers)
• #225 — Confluence reconciliation-rules pre-spec cleanup pass

$25 k NTE · spent vs remaining

NTE consumption

23.8% consumed end of W2 day 4. Trajectory healthy: P0 + a chunk of P1 done in 4 active days, with ~76% of the cap reserved for P2 (Splink IR) through P6 (handoff).

How this is calculated

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

Total: 42 h · $5,960. The full-time intensity rate ($140) applies whenever a day exceeds 4 hours — that's been every active day except the initial 29 Apr walkthrough. Detailed daily breakdown lives in sprint-log.md.

Phase status

P1 burn: 28 h at $140/h = $3,920 vs $4,000 phase target (98%). Hours over the 25-h estimate (which was for normalize alone; we also executed the entire #143 + #159 stack this week). No NTE escalation needed — phase estimate was conservative on hours, dollar target intact.

W3 (12 May – 18 May) · build phase opens for real

P1 build kicks off — staging models

Now that the replication pipeline is live, the staging layer can read from EF_DATA_HUB_RAW on CH and produce stg_<product>__<entity> models in the staging schema. The plan is to land HSEY first (smallest product at 364 MB / 2.9 M rows), validate the pattern + macros + tests against legacy parity, then fan out to the other 7 products. Splink-IR (#161) and canonical model (#175) follow on top of staging in W3-4.

Looking past W3 — phase trajectory

Critical path now runs through #155 (dbt Cloud project) → #158 (staging) → #161 (IR) → #175 (canonical) → #166 (CIs). Replication pipeline is no longer on the critical path — it's autonomous.

Where to dig deeper