ADR-056: Snowflake-native dbt Projects for SD06 model execution

Status	Accepted
Date	2026-05-07
Deciders	CTO, Head of Platform Engineering, Head of Data Platform
Affects repos	bank-risk-platform, bank-platform

---

Status: Accepted — 2026-05-07

Context

SD06 (bank-risk-platform) has used dbt-core (Python CLI) inside GitHub Actions runners to compile and execute dbt models since the first modules shipped. The CI workflow runs dbt build --target ${STAGE} --select "tag:${module_tag}" which compiles model SQL on the runner, opens a Snowflake connection as BANK_DBT_ROLE, and issues CREATE OR REPLACE DYNAMIC TABLE / CREATE VIEW statements directly. The resulting Snowflake objects are real and correct — Dynamic Tables refresh, alerts fire, integration tests pass.

What is absent: Snowflake has no record of a dbt project as a managed object. SHOW DBT PROJECTS returns zero rows. The dbt project metadata (lineage graph, execution history, model inventory) lives in target/manifest.json uploaded to S3 artefacts (ADR-053). There is no Snowflake-side audit of which models ran, when, and with what inputs. You cannot see, schedule, or debug dbt models from inside Snowflake.

Snowflake-native dbt Projects (GA November 2025)

Snowflake-native dbt Projects entered GA on 6 November 2025. Key properties:

• First-class object: CREATE DBT PROJECT registers the project in Snowflake. Visible via SHOW DBT PROJECTS IN SCHEMA …. Managed under DCM's parent governance umbrella (though not a DCM-managed type — see §5 below).
• Server-side execution: EXECUTE DBT PROJECT (or snow dbt execute) runs genuine dbt Core with the dbt-snowflake adapter inside a Snowflake-managed runtime. No Python installation in CI runners is required for the execution step.
• Lineage in Snowflake: Model-level execution history, run timing, and result status are stored in Snowflake's account_usage.dbt_invocations view — surfaceable in dashboards, accessible to auditors, queryable alongside query history.
• Scheduling via Tasks: EXECUTE DBT PROJECT … AS TASK lets Snowflake Tasks trigger dbt execution directly. Relevant for future modules with batch-cadence requirements (replacing cron Lambda patterns for pure dbt-layer refreshes).
• GA status: No Preview gate. All three environments (dev, UAT, prod) are immediately eligible.

Research findings that shape this decision

Three technical questions were investigated before this ADR was drafted:

1. adapter.get_relation() in native execution

The Snowflake-native dbt runtime runs genuine dbt Core. The documented restriction list is narrow and specific: {{ env_var('...') }} Jinja calls are unsupported (use --vars instead); several CLI flags (--state, --target-path, --log-path, --profiles-dir, --project-dir) are unsupported. adapter.get_relation() — used throughout SD06 for bootstrap-resilient staging models — is not listed as restricted. Since it executes a standard INFORMATION_SCHEMA query via the dbt-Snowflake adapter Python layer, there is no architectural reason it would be unavailable.

Conclusion: expected to work. The bank-risk-platform team should run a smoke test against dev on the first migrated module before declaring the pattern confirmed (see §6 migration phasing).

2. DCM depends_on and dbt project execution

DBT PROJECT is not a DCM-managed object type. DCM Projects can declare dependencies only between DCM-managed objects (schemas, tables, dynamic tables, tasks, alerts, DMFs, grants). There is no cross-system dependency mechanism from DCM to dbt project execution completion.

Conclusion: the current CI sequencing approach is unchanged. The CI workflow runs snow dbt execute --wait, then snow dcm deploy. The --wait flag ensures DCM deploy only begins after all dbt models have been created/refreshed. This is structurally identical to the current dbt build → snow dcm deploy ordering.

3. dbt packages with snow dbt deploy

Two paths are supported and documented:

• Path A (server-side, recommended): Provide an EXTERNAL_ACCESS_INTEGRATION at project registration time permitting egress to hub.getdbt.com and codeload.github.com. Snowflake runs dbt deps server-side at compile time. No dbt_packages/ directory needed in the repo.
• Path B (pre-resolved): Run dbt deps in CI, bundle the dbt_packages/ directory, deploy with snow dbt deploy. No external access integration required.

This ADR adopts Path A for new modules and Path B as the fallback if network restrictions prevent the external access integration.

Critical: the dbt-snowflake adapter must be removed from packages.yml. It is bundled in the Snowflake-managed runtime. Re-declaring it causes a version conflict.

Decision

1. Snowflake-native dbt Projects replaces dbt-core CLI for SD06 model execution

dbt build in GitHub Actions runners is replaced by snow dbt deploy + snow dbt execute for all SD06 modules. The CI workflow step in reusable-risk-platform.yml changes from:

- name: dbt build
  run: dbt build --target "${STAGE}" --select "tag:${{ inputs.module_tag }}"

To:

- name: Register dbt project
  run: |
    snow dbt deploy \
      --project-path ./${{ inputs.module_dir }} \
      --name "${{ inputs.module_tag_upper }}" \
      --warehouse "BANK_${STAGE_UPPER}_DBT_WH" \
      --database "BANK_${STAGE_UPPER}_RISK" \
      --schema "DCM_PROJECTS" \
      --external-access-integration "BANK_DBT_HUB_EAI" \
      --overwrite

- name: Execute dbt project
  run: |
    snow dbt execute \
      --name "${{ inputs.module_tag_upper }}" \
      --select "tag:${{ inputs.module_tag }}" \
      --wait

The BANK_DBT_HUB_EAI external access integration (egress to hub.getdbt.com + codeload.github.com) is provisioned once per Snowflake account by MOD-102.

2. Each module registers a dbt project name

Each SD06 module's dcm/manifest.yml gains a dbt_project_name field. This is the name used in SHOW DBT PROJECTS and in snow dbt execute --name. Convention:

MOD_{NNN}_{SLUG_UPPER}

Examples: MOD_040_CHURN_HEALTH_SCORE, MOD_086_FUNDS_TRANSFER_PRICING.

3. profiles.yml is removed from module directories

Connection context is provided by the snow CLI (using the same BANK_DBT_ROLE credentials already available in the workflow). profiles.yml is removed from each module directory at migration time.

4. dbt-snowflake is removed from packages.yml

The dbt-snowflake adapter package declaration is removed from every module's packages.yml. Snowflake's managed runtime provides it. All other packages (dbt-labs/dbt_utils, etc.) remain and are resolved server-side via the external access integration.

5. CI sequencing: unchanged

DCM deploy continues to run after snow dbt execute --wait completes, for the same reason it runs after dbt build today: alerts and tasks that reference dbt-built views must be created after those views exist. The --wait flag is mandatory.

6. env_var() Jinja calls: prohibited

{{ env_var('...') }} is explicitly unsupported in Snowflake-native dbt execution. SD06 modules must not use it. The existing convention (all runtime config via SSM, not environment variables injected into the dbt Jinja context) already satisfies this constraint — this ADR formalises the prohibition.

7. MOD-102 provisions the external access integration

A new Flyway migration in MOD-102 (Snowflake account configuration) creates:

CREATE OR REPLACE NETWORK RULE bank_dbt_hub_network_rule
  TYPE = HOST_PORT
  MODE = EGRESS
  VALUE_LIST = ('hub.getdbt.com', 'codeload.github.com');

CREATE OR REPLACE EXTERNAL ACCESS INTEGRATION BANK_DBT_HUB_EAI
  ALLOWED_NETWORK_RULES = (bank_dbt_hub_network_rule)
  ENABLED = TRUE;

And grants USAGE ON INTEGRATION BANK_DBT_HUB_EAI TO ROLE BANK_DBT_ROLE.

MOD-102 must be deployed (or the migration applied manually in dev) before any module can use Path A package resolution.

Migration path

Existing modules (9 modules on current dbt-core CI pattern)

Module	System	Current status	Migration trigger
MOD-032	LCR/NSFR	Deployed	Next version bump
MOD-033	RWA/capital	Deployed	Next version bump
MOD-034	Stress testing	Not started	Greenfield — use new pattern
MOD-035	IRRBB	Deployed	Next version bump
MOD-038	Data quality	Deployed	Next version bump
MOD-039	Customer risk score	Deployed	Next version bump
MOD-040	Churn & health score	Deployed	Next version bump
MOD-085	Market data ingestion	Deployed	Next version bump
MOD-086	Funds transfer pricing	Deployed	Next version bump

Migration is not a forced immediate re-deploy. Each module migrates at its next substantive version bump. A pure migration (no functional change, just switching execution mode) is a valid reason for a version bump.

Per-module migration checklist: 1. Remove profiles.yml 2. Remove dbt-snowflake from packages.yml; confirm remaining packages resolve via hub.getdbt.com 3. Add dbt_project_name to dcm/manifest.yml 4. Update dcm/grants.sql to add GRANT USAGE ON INTEGRATION BANK_DBT_HUB_EAI TO ROLE BANK_DBT_ROLE (if not already granted account-wide by MOD-102) 5. Run smoke test: snow dbt deploy --project ./ --name MOD_NNN_... --overwrite against dev; verify SHOW DBT PROJECTS shows the new object 6. Run snow dbt execute --name MOD_NNN_... --select "tag:mod-NNN" --wait against dev; verify all models complete and Dynamic Tables resume 7. adapter.get_relation() confirmation: for modules that use the bootstrap-resilient staging pattern (MOD-035, MOD-040, MOD-086, MOD-085), confirm the staging model executes correctly against a cold schema on the first run — this is the definitive validation that adapter.get_relation() works in the native runtime 8. Update reusable-risk-platform.yml call in the module's .github/workflows/mod-NNN.yml to pass the new dbt_project_name input 9. Remove python-version, pip install dbt-snowflake, and dbt deps steps from the per-module CI workflow (now handled server-side)

New modules (all Phase-3 SD06 modules)

All new SD06 modules use Snowflake-native dbt Projects from day one. No profiles.yml. No dbt-snowflake in packages.yml. dbt_project_name in dcm/manifest.yml. The reusable workflow has_dbt_project: true input replaces has_dbt: true.

Consequences

Positive: - dbt project lineage, execution history, and model inventory are visible in Snowflake — auditable by regulators, accessible to the data platform team, queryable alongside account_usage.query_history. - SHOW DBT PROJECTS in each Snowflake environment gives an accurate picture of all registered dbt modules. - Python dbt-core is removed from CI runners — no more pip install dbt-snowflake==X.Y.Z pinning, no dbt version drift between modules. - EXECUTE DBT PROJECT … AS TASK opens the path to Snowflake-Task-driven batch scheduling for future modules (eliminating cron Lambda patterns for pure dbt refreshes). - dbt-snowflake adapter version is managed by Snowflake — modules no longer diverge on adapter version. - GA feature — no environment gate required.

Negative / risks: - adapter.get_relation() is not explicitly confirmed by Snowflake documentation — requires smoke-test validation on the first migrated module before the pattern is declared safe for all. - env_var() Jinja prohibition formalised — any future model that attempts to use it will fail at execution time with an opaque error. Linting rule recommended. - External access integration (MOD-102 prerequisite) must be in place before modules can migrate. If MOD-102 is delayed, Path B (pre-resolved dbt_packages/) is the fallback with no functional difference. - Migration effort: ~0.5 days per existing module (checklist above). Non-blocking for ongoing Phase-2 work.

---

As-built notes (2026-05-07)

MOD-102 migration 0025 — deferred shape

Migration 0025_dbt_hub_eai.sql shipped in deferred shape (schema only). Three delivery attempts surfaced a Snowflake product-tier limitation: errorCode 509009 External access is not supported for trial accounts. No SQL workaround exists — this is a Snowflake account-tier restriction.

Three attempts before reaching this conclusion:

1. Run 25467853162: Cannot perform CREATE NETWORK RULE. This session does not have a current database. — fixed by adding USE DATABASE/SCHEMA and FQN-qualifying the rule.
2. Run 25467979290: syntax error … unexpected 'EAIs' — fixed by removing ; from the schema's COMMENT string (the runner's splitSqlStatements is naive about string literals; documented in the migration header).
3. Run 25468266706: errorCode 509009 — account-tier block. Migration shrunk to deferred shape; full body deferred to follow-up.

Current live state (dev): - BANK_PLATFORM_SETUP.INTEGRATIONS schema exists and is pre-positioned. - BANK_DBT_HUB_EAI external access integration and bank_dbt_hub_network_rule are not yet created.

Follow-up path: When the Snowflake account exits trial tier, migration 0026_dbt_hub_eai_full.sql will add the NETWORK RULE + EAI + GRANT in a single incremental migration. The recipe-doc default for dbt_eai_name will change to BANK_DBT_HUB_EAI at that point, making Path A the automatic default for new modules.

Current active path: Path B

BANK_DBT_HUB_EAI is not yet available, so Path B is the active default for all modules. reusable-risk-platform.yml (commit fad61b7) makes dbt_eai_name optional with a default of "" — the --external-access-integration flag is conditional on dbt_eai_name being set:

uses: totara-bank/bank-platform/.github/workflows/reusable-risk-platform.yml@main
with:
  has_dbt_project: true
  dbt_project_name: MOD_040_CHURN_HEALTH_SCORE
  # dbt_eai_name omitted (defaults to "") → Path B
  # Module CI materialises dbt_packages/ before calling snow dbt deploy.

New Phase-3 modules (MOD-040 etc.) can adopt has_dbt_project: true immediately using Path B. No platform-side blocker remains for bank-risk-platform.

Stale integration-test count assertion (commit bb955d3)

reusable-risk-platform.yml integration tests included expect(rows.length).toBe(14) (a migration-count assertion) that had been latent-broken since migration 0015 was added. It had not been caught because every prior command=apply dispatch had failed earlier in the pipeline (auth, SSM, or migration error) before the integration tests ran — so the count was never verified against a successful migration sequence. The first end-to-end-clean dispatch (run 25468414117, v25) revealed the stale count. Commit bb955d3 replaces the bare literal with a named constant MIGRATION_COUNT_AFTER_APPLY = 25 and a comment requiring future migration adders to update it. Any author adding a new MOD-102 migration must bump the constant.

---

Implementation corrections (2026-05-07, bank-risk-platform 6/8 migration)

Three items in this ADR's spec required correction during the bank-risk-platform migration. The core decision (Snowflake-native dbt Projects) is unchanged. The corrections apply to the migration guide (§§3, 6, and the CI snippet) and should be followed for all future module migrations.

Correction 1: dbt_project_name cannot be a manifest.yml top-level key

Spec (§2): "Add dbt_project_name to dcm/manifest.yml as a top-level key."

Reality: Snowflake DCM v2 strict-validates manifest.yml top-level keys and rejects unknown properties:

001606 (55000): Error during DCM INIT_PLAN …
  [1] $.dbt_project_name: is not defined in the schema and
      the schema does not allow additional properties

Resolution: Demote dbt_project_name to a YAML comment block in each module's dcm/manifest.yml (human documentation only). The actual value is the dbt_project_name: caller input in the module's reusable-risk-platform.yml call — that is what snow dbt deploy reads.

Updated migration checklist step 3: Add dbt_project_name as a YAML comment in dcm/manifest.yml and as a dbt_project_name: caller input in the module's .github/workflows/mod-NNN.yml.

Correction 2: profiles.yml must remain present (not be deleted)

Spec (§3): "Delete profiles.yml from the module root. Connection context is provided by the snow CLI."

Reality: snow dbt deploy validates profiles.yml structurally before uploading. CI fails without it:

Error: profiles.yml does not exist in directory /home/runner/work/…/MOD-086-…

Furthermore, the Snowflake-native runtime DOES read warehouse:, database:, role:, and schema: from profiles.yml at execute time — these are not inferred from the snow CLI context alone.

Resolution: Restore a minimal placeholder profiles.yml in each module. The role: value must match the deploy role (BANK_NONPROD_RISK_ROLE for dev/uat, BANK_PROD_RISK_ROLE for prod). Using BANK_DBT_ROLE (the legacy default) caused DCM PLAN failures: the dbt-runtime role's objects were not visible to the DCM-deploy role during GRANT ON ALL expansions.

Updated migration checklist step 1: Keep profiles.yml. Ensure role: matches the env-appropriate deploy role, not BANK_DBT_ROLE.

Correction 3: env_var() is allowed in profiles.yml

Spec (§6): "{{ env_var('...') }} is explicitly unsupported in Snowflake-native dbt execution."

Reality: The restriction applies to models/ and macros/ (compiled server-side). profiles.yml is parsed client-side at deploy time and baked into the project before upload — env_var() there is compatible. The implementation took the conservative path of using static placeholders in profiles.yml anyway (simpler, no $BANK_DBT_ROLE env var injection required in CI), so this has no current impact on the running code.

Updated §6: The env_var() prohibition applies to models and macros only. Static values in profiles.yml are preferred for simplicity, but env_var() there would not cause a failure.

Actual snow dbt CLI signatures (snowflake-cli 3.16)

The CI snippet in the Decision section reflects the intended spec shape. The actual snowflake-cli 3.16 signatures differ materially. The working invocation shapes are:

- name: Register dbt project
  run: |
    snow dbt deploy "${{ inputs.dbt_project_name }}" \
      --source <staged-tempdir> \
      --force
    # Positional NAME; --project-path/--name/--overwrite do not exist.
    # No --warehouse/--database/--schema flags; those are read from profiles.yml.

- name: Execute dbt project
  run: |
    snow dbt execute "${{ inputs.dbt_project_name }}" \
      "build --select tag:${{ inputs.module_tag }} --target ${STAGE}"
    # Positional NAME + positional dbt command string; no --select flag directly
    # on snow dbt execute. The --wait equivalent is achieved via CI step sequencing.

--source scoping: --source . walked the entire module tree (including node_modules/, dist/, infra/) without honouring .gitignore. The working pattern stages only dbt-project files into a tempdir before calling snow dbt deploy.

Future snow CLI upgrades should be validated via the --help output dump pattern already present in reusable-risk-platform.yml.

packages.yml — dropped entirely

No SD06 module actually references dbt-labs/dbt_utils in any model or macro despite it being declared in every packages.yml. The migration dropped packages.yml entirely, making Path B a no-op (no dbt deps, no dbt_packages/ directory to materialise). When the EAI lands later, no per-module change is needed — the reusable workflow already gates --external-access-integration on the dbt_eai_name input.

Migration status: 6/8 modules complete

Module	dbt_project_name	Status
MOD-086 Funds Transfer Pricing	MOD_086_FUNDS_TRANSFER_PRICING	✅ Deployed
MOD-038 Data Quality Monitor	MOD_038_DATA_QUALITY_MONITOR	✅ Deployed
MOD-040 Churn & Health Score	MOD_040_CHURN_HEALTH_SCORE	✅ Deployed
MOD-085 Market Rates Ingestion	MOD_085_MARKET_RATES_INGESTION	✅ Deployed
MOD-039 Customer Risk Score	MOD_039_CUSTOMER_RISK_SCORE	✅ Deployed
MOD-098 Cost Attribution Engine	MOD_098_COST_ATTRIBUTION_ENGINE	✅ Deployed
MOD-032 LCR / NSFR Calculator	MOD_032_LCR_NSFR_CALCULATOR	⏸ Reverted to legacy dbt-core path — see follow-up note below
MOD-035 IRRBB Model	MOD_035_IRRBB_MODEL	⏸ Reverted to legacy dbt-core path — see follow-up note below

adapter.get_relation() confirmed working under native runtime (verified on MOD-040 cold start, MOD-085 cold start, MOD-086 cold start). The bootstrap-resilient staging pattern is safe.

MOD-032 and MOD-035 are back on has_dbt_project: false (legacy dbt build path) with their Snowflake objects continuing to refresh. See follow-up note below.

---

ADR-056 follow-up: MOD-032 + MOD-035 reverted (2026-05-07)

The follow-up recast handoffs (MOD-032-stg-recast-floats.handoff.md and MOD-035-stg-recast-floats-and-seed.handoff.md) were actioned in full across 4 iteration commits. The prescribed changes were applied:

MOD-032: stg_funding_book float columns (asf_factor, rsf_factor, runoff_rate_30d) recast to NUMBER(7,6); per-CTE CAST(SUM(...) AS NUMBER(22,4)) at every aggregation boundary; Basel (40.0/60.0) literal wrapped in CAST(... AS NUMBER(10,8)); CURRENT_DATE/CURRENT_TIMESTAMP replaced with run_started_at; CONFIG seed refactored from MERGE...VALUES to INSERT INTO...SELECT FROM.

MOD-035: stg_yield_curves.rate_decimal recast to NUMBER(10,8); bp-divisor literal swapped for CAST(10000 AS NUMBER(10,4)); POWER() output cast to NUMBER(22,10) per-CTE; IRRBB_SHOCK_SCENARIOS migrated to a dbt seed CSV (handoff Option A — no VALUES in lineage).

Outcome: 091905 (0A000): Change tracking is not supported on queries with 'VALUES' persists on both modules after all fixes. The error fires at identical line:position (line 9, position 0) on both compiled DT bodies, which strongly suggests it is not driven by model-content VALUES expressions but by something structural in the dbt-compiled SQL or by persisted Snowflake change-tracking metadata.

Most likely hypotheses (in order):

1. Snowflake internal change-tracking metadata persists across DROP+CREATE. The per-account change-tracking history may still classify the table-name as VALUES-derived from its prior INSERT history, regardless of the current seed method. Requires ACCOUNTADMIN inspection of SNOWFLAKE.ACCOUNT_USAGE.OBJECT_DEPENDENCIES to verify.

2. Upstream MOD-085 source views (v_swap_curve / v_ois_curve) have VALUES lineage. MOD-035's stg_yield_curves reads from these views; if MOD-085's Lambda ingest path uses INSERT...VALUES, the classification propagates through the lineage chain.

3. dbt-snowflake adapter's compiled DT output contains a structural VALUES form at line 9 (post_hook, dbt metadata column, or similar). Inspecting the compiled SQL on the CI runner would confirm or rule this out.

Current state: Both modules running on legacy dbt build path with the upstream SQL improvements in place (FLOAT staging recasts, INSERT...SELECT seeds, run_started_at substitutions, dbt-seed IRRBB_SHOCK_SCENARIOS). The 6 modules on has_dbt_project: true are unaffected.

Next steps to unblock:

• Capture the dbt-compiled SQL for a failing DT by adding a workflow step to upload target/run/.../liquidity_positions.sql on ADR-056 failure — line 9 will be self-evident.
• Inspect SNOWFLAKE.ACCOUNT_USAGE.OBJECT_DEPENDENCIES for CONFIG and IRRBB_SHOCK_SCENARIOS to see what Snowflake records as the dependency origin.
• If MOD-085's ingest path is confirmed as the source, scope a separate handoff to refactor that Lambda's ingest from INSERT...VALUES to INSERT...SELECT via internal stage.

If the 091905 error is ultimately intrinsic to incremental-mode DTs with certain upstream dependency patterns, this ADR may require an amendment carving out modules where refresh_mode='INCREMENTAL' is non-negotiable under CLQ-002 / FR-216 policy constraints.

---

All ADRs Compiled 2026-05-22 from source/entities/adrs/ADR-056.yaml