9-week build · P1 foundation now structurally complete

The replication pipeline from W2 keeps running every night. This week the dbt project layer was restructured to the receiving team's vanilla conventions, the CI route was fixed, and the Snowflake-side schemas + grants for the new layout were provisioned live on HH82036. Phase 1 build (real model logic in #158, then #175 canonical) opens Monday as soon as the bootstrap PR merges.

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

Tue 13 May · Allan + Adrien sync · 3 structural decisions

Tuesday's audit deck identified 18 layout items vs the dbt Labs Best Practices Guide + the eftours/de-dbt monorepo conventions. 8 items already aligned, 7 quick wins land before model logic, and 3 substantive decisions needed sign-off. All three settled in the same call:

What landed in W3

1. 1
   Spec #143 rewritten for the vanilla-audit layout. Pass 1 + 2 marked done with acceptance criteria checked. New Pass 3 split into three sub-passes: 3a SYSADMIN (create 4 new project-prefixed schemas), 3b ACCOUNTADMIN (grants on the new schemas), 3c SYSADMIN (drop the 8 obsolete unprefixed schemas after PR merge). Idempotent DDL inline + companion scripts on disk.

   Spec: specs/setup/143-ch-dbt2-home.md · DDL: scripts/setup/143-ch-dbt-home-pass3.sql + ...-pass3ab-only.sql
2. 2
   Pass 3a + 3b DDL executed live on HH82036. 12 new schemas (EF_DATA_HUB_CH_STAGING / _INTERMEDIATE / _CORE / _MARTS × DEV / QA / PROD) + full grant matrix on each: USAGE/MONITOR/MODIFY + 10 CREATE privileges to the owning RW role, USAGE cross-env to the other two RW roles, USAGE to MCP_READER on all 12. ACCOUNTADMIN granted temporarily to EF_MCP_SERVICE_USER_CH for the grant block and revoked immediately after — least-privilege restored.

   Pass 3c (drop the 8 obsolete staging/normalize/sfmc_engagement/canonical/ir/cis/segments/marts schemas) queued for after PR #3868 merges
3. 3
   Cross-repo PR eftours/de-dbt#3868 — restructured end-to-end to the vanilla layout. dbt_project.yml collapsed 8 layer configs → 4 (UPPERCASE schema directives). Sources moved to top-level sources/ outside models/. 7 quick wins from the audit deck landed: dbt-cloud: project-id: 17231 block, +group: ef_data_hub_ch + models/_groups.yml, +access: public on marts, +meta.required_tests on persistent layers, +transient: false + +materialized: table on core + marts, medallion tags dropped. Custom generate_schema_name macro replicated from the EF monorepo standard pattern. .dbt_cloud.txt with project 17231's job IDs routes the workflow to the dbt-cloud CI path. _tours_utilities wired as local package; migrated from calogica/dbt_expectations → metaplane/dbt_expectations to match transitive deps. Phase A placeholder macro call replaced with cast(null as timestamp) across all 56 staging skeletons (the original macro hardcoded a per-source column name that didn't exist on every source table). CI is 14/14 green; PR is mergeable.

   Adrien tagged for code-owner review · 6 commits pushed this week to feat/ef_data_hub_ch-bootstrap
4. 4
   CI diagnose-and-fix marathon — 5 distinct failure modes resolved end-to-end. (1) Stale base vs main caused a "missing script" false alarm — fixed by merging origin/main into the PR branch. (2) KeyError: 'javascript' from dbt-snowflake 1.11.5 when CI fell to the dbt-core path → root-caused as a routing issue, not the regression itself. (3) Routing fix: shipped .dbt_cloud.txt with the project's job IDs so the workflow uses the dbt-cloud build path. (4) Defer chicken-and-egg: defer-based CI jobs cancel BEFORE running steps if the env has no baseline manifest. Solved by triggering deploy-type runs (compile-only, no DML) to seed PROD env (job 611029 PATCH-trigger-restore) and QA env (created temp job 622417, ran it, deleted). (5) Macro/package gaps: identified tnightengale/dbt_meta_testing as the missing source for required_tests / required_docs; pulled in transitively via _tours_utilities. Final placeholder macro fix landed the SQL-validity errors on staging models.

   Lesson captured: state:modified defer is sticky — steps_override can't bypass the pre-check, only a deploy-type job seeds the env baseline
5. 5
   Allan's Terraform PR eftours/de-dbt-cloud-terraform#119 applied. Connection 17238 in dbt Cloud account 338 flipped from the leftover EF_DBT_SANDBOX/EF_DBT_SANDBOX_RW to EF_DBT_DEV/EF_DBT_DEV_RW. Same PR reconciled Terraform drift on the QA/PROD env credentials (added EF_DBT_PROD as the explicit database for PROD; aligned QA role + database to EF_DBT_QA_RW / EF_DBT_QA). Future terraform apply won't revert the manual cred PATCHes from W3 Day 1.
6. 6
   Ticket housekeeping. #143 spec rewrite landed via PR #274. Pass 3 scripts added via PR #283 + #284. Session log + decision capture via PR #273. Several mid-week correction PRs (#272, #278, #279, #280, #281) keeping the local source-of-truth in sync with the cross-repo state. Once PR #3868 merges, #143 + #155 close.

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.

New schema surface on HH82036 (post Pass 3a/3b)

Where each in-flight ticket landed

Ready to close once PR #3868 merges

Now unblocked for the real build

Still pending or stakeholder-blocked

• #3868 PR review — Adrien (code-owner approval needed; CI is 14/14 green)
• #225 — Confluence reconciliation-rules CSV validation (Rich; 150-row inventory pending since 8 May, gentle nudge planned for W4)
• #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)
• #224 — Marts sublayer (Cortex / cross-regional / BI consumers); spec write pending after #158 Phase B settles patterns
• #136-#140 — RLT-* normalize tickets: ready to move to In Review on Board #7 once #158 Phase B resolves the underlying scope

$25 k NTE · spent vs remaining

NTE consumption

42.4% consumed end of W3. Trajectory healthy: P0 done + structural P1 foundation complete in 3 active weeks, with ~58% 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: 74 h · $10,600. Detailed daily breakdown lives in sprint-log.md.

Phase status

P1 burn at 60 h vs the 25-h estimate is a clear over-run on the phase tracker, but the phase scope expanded materially: it now bundles #143 + #159 (W2), then the entire #155 bootstrap + vanilla-audit restructure + CI debug + Pass 3 DDL (W3) — all of which are P1 foundation prereqs that the SOW didn't separately budget. Cumulative NTE at 42.4% leaves ~$14.4 k for P2 → P6. No escalation; tracking the over-phase burn as a flag to pace P2 onward more strictly.

W4 (19 May – 25 May) · real-SQL build opens once PR merges

P1 Phase B — port-and-evolve from ANALYTICS

With structural P1 foundation done (12 schemas + grants + project layout + CI green), the bottleneck moves to real model SQL. Plan: pilot stg_ccap__contact from the legacy EF_DATA_HUB.ANALYTICS.CCAP.CONTACT_V view (CCAP fully audited, smallest controlled scope) to validate the port-and-evolve pattern + macros + parity tests end-to-end. Then fan out by product. Splink-IR (#161) and canonical model (#175a) build on staging once Phase B has 2-3 products materializing real data.

Looking past W4 — phase trajectory

Critical path now: PR #3868 merge → #158 Phase B real SQL → #161 Splink IR → #175a canonical → #166 CIs → #142 segments → cutover. Replication pipeline and dbt project surface are both autonomous now — not on the critical path.

Where to dig deeper