ADR-054: Snowflake DDL management via DCM Projects

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

---

Status: Accepted — 2026-05-03

Supersedes ADR-035 (Snowflake DDL management via versioned SQL scripts).

Context

bank-risk-platform (SD06) manages all non-dbt Snowflake objects — schemas, tables, dynamic tables, tasks, alerts, data metric functions (DMFs), and grants — using an imperative script runner: apply-snowflake-ddl.ts. This script walks a directory tree of SQL files and executes each file against the target Snowflake environment. It was the correct pragmatic choice when SD06 was first built, but it has accumulated three compounding problems:

1. No version tracking. There is no record of which DDL files have been applied to a given Snowflake environment. Re-running apply-snowflake-ddl.ts against an existing environment attempts to re-execute every file. Some files are idempotent (CREATE SCHEMA IF NOT EXISTS), others are not (ADD DATA METRIC FUNCTION fails if the DMF is already attached). The script handles this with per-file try/catch, silently continuing past errors. The result is an environment where the actual applied state may differ from the declared state with no observable signal.

2. Two-phase deploy complexity. Snowflake validates Alert bodies at CREATE ALERT time. Alerts in MOD-038 reference views built by dbt (v_quality_scores, v_open_breaks). Those views do not exist until after dbt build completes. This forces a two-phase deploy: apply-snowflake-ddl.ts --phase=pre-dbt (schemas, tables, DMFs, tasks, grants) then dbt build then apply-snowflake-ddl.ts --phase=post-dbt (alerts). Every new module that introduces Alerts or Tasks referencing dbt views inherits this complexity.

3. No promotion model. There is no clean way to promote a tested DDL state from dev to UAT. Re-applying all scripts against a fresh UAT environment would attempt every file, silently skip idempotent ones, and fail on non-idempotent ones that have already been partially applied, requiring manual intervention to determine the current UAT state.

Snowflake DCM Projects

Snowflake DCM Projects entered Public Preview in March 2026. DCM (Database Change Management) is a native Snowflake declarative state management system for database objects. Key properties:

• Declarative: You define the desired state of objects (schemas, tables, dynamic tables, tasks, alerts, DMFs, roles, grants) in SQL definition files. DCM diffs current environment state against the declared target and produces a typed change plan.
• Plan before deploy: snow dcm plan --project ./dcm --target dev produces an immutable plan artefact showing exactly what will be created, altered, or dropped. snow dcm deploy {plan-id} applies exactly that plan — idempotent, replayable.
• Environment-aware: DCM projects use Jinja2 templating. A single set of object definitions promotes across dev, uat, and prod by substituting environment-specific values (warehouse names, database names, role names, alert thresholds) at plan time. No separate DDL files per environment.
• Audit trail: DCM stores an immutable record of every plan and every deployment, per environment, with timestamps and plan contents.
• Dependency ordering: Object dependencies are declared explicitly. DCM respects creation order. Alerts that reference dbt views can be declared as dependent on the dbt step — the two-phase deploy complexity is replaced by declared dependencies managed natively.
• Aligned with ADR-049: ADR-049 established Snowflake-native compute as the SD06 architectural preference. DCM Projects is the natural extension of this principle from compute to schema and object lifecycle management.

Decision

1. DCM Projects replaces apply-snowflake-ddl.ts

apply-snowflake-ddl.ts is deprecated as the Snowflake DDL management mechanism for SD06. All new Snowflake DDL in bank-risk-platform is declared in a DCM project. Existing DDL managed by apply-snowflake-ddl.ts is migrated to DCM project format as each module is next deployed (not a forced immediate migration — see migration path below).

2. DCM project structure in bank-risk-platform

Each module owns a DCM project definition within its directory:

MOD-NNN-module-name/
  dcm/
    project.yml                    DCM project manifest (name, target, vars)
    schemas/
      governance_meta.sql          CREATE SCHEMA IF NOT EXISTS ...
    tables/
      config.sql                   CREATE OR REPLACE TABLE ...
      reconciliation_breaks.sql
    dynamic_tables/
      v_quality_scores.sql         CREATE OR REPLACE DYNAMIC TABLE ...
    dmfs/
      dmf_count_negative.sql
    dmf_attach/
      market_fx_spot_current.sql
    alerts/
      a_dq_breach.sql              Depends on dbt step (declared in project.yml)
    grants/
      dbt_role_grants.sql

The project.yml manifest declares: - Target environment variables (resolved from SSM at plan time via snow dcm plan --var) - Object dependency graph (alerts after dbt, DMF attachments after table creation) - Warehouse and role context for the deploy role

3. CI step replacement

The CI workflow step changes from:

- name: Apply Snowflake DDL (pre-dbt)
  run: pnpm apply-ddl --phase=pre-dbt

- name: dbt build
  run: dbt build --select tag:mod-NNN

- name: Apply Snowflake DDL (post-dbt)
  run: pnpm apply-ddl --phase=post-dbt

To:

- name: DCM plan
  run: snow dcm plan --project ./MOD-NNN/dcm --target dev --output plan.json

- name: Upload DCM plan to S3
  run: aws s3 cp plan.json s3://bank-artefacts/${REPO}/${SHA}/modules/MOD-NNN/dcm-plan.json

- name: dbt build (data models)
  run: dbt build --target dev --select tag:mod-NNN

- name: DCM deploy (includes post-dbt objects — alerts, tasks referencing dbt views)
  run: snow dcm deploy --plan plan.json

DCM's dependency ordering handles the sequencing of objects that depend on dbt output. The two-phase deploy is eliminated.

4. Stage promotion

Promotion from dev to UAT uses DCM's native environment targeting:

snow dcm plan --project ./MOD-NNN/dcm --target uat --output plan-uat.json
# inspect plan — shows only delta against current UAT state
snow dcm deploy --plan plan-uat.json

DCM diffs the current UAT Snowflake environment state against the declared target and applies only what is missing or different. There is no risk of re-applying already-applied objects, no try/catch error swallowing, and no manual state inspection required.

5. MOD-102 (Snowflake account configuration) — separate evaluation

MOD-102 in bank-platform manages account-level Snowflake objects: databases, warehouses, account-level roles, and the BANK_SNS_INTEGRATION notification integration. These are account-scoped resources provisioned once, not per-module resources. DCM Projects targets database-scoped objects; account-level provisioning is better suited to the Terraform Snowflake provider, which has full coverage of account-level objects and mature state management.

The decision on MOD-102 is deferred to a follow-up ADR. MOD-102 continues to use its current Pulumi-based approach until that decision is made.

6. Preview status and production gate

DCM Projects is in Public Preview as of March 2026. GA is required before production deployment.

Risk profile: - dev environment: DCM Projects is adopted immediately. All new SD06 module development uses DCM from this ADR forward. This is a controlled environment; preview instability is recoverable. - UAT environment: DCM Projects is adopted at the point of first UAT deploy, expected after GA. - Production: DCM Projects is used only after Snowflake declares GA. If GA is delayed, the fallback for the first production deploy is apply-snowflake-ddl.ts with a full idempotency audit pass — this is a documented contingency, not the default path.

7. Migration path for existing modules

MOD-038, MOD-085, and MOD-098 currently use apply-snowflake-ddl.ts. Migration to DCM is triggered at next deploy of each module:

1. Translate existing DDL scripts to DCM project format
2. Run snow dcm plan --target dev — the plan should show zero changes if the current dev Snowflake state already matches the declared target
3. If the plan shows drift (i.e. the current state diverges from what the scripts would declare), resolve manually, then re-plan to confirm zero-change
4. On next CI run, the DCM plan step replaces the apply-snowflake-ddl.ts step
5. Remove apply-snowflake-ddl.ts and the two-phase deploy scripts from the module

Consequences

Positive: - Eliminates the idempotency class of bugs (DMF re-attach failures, etc.) — DCM diffs state and never re-applies an already-correct object. - Eliminates the two-phase deploy complexity — dependency ordering is declared, not hacked via split phases. - Provides an immutable DCM audit trail of every Snowflake environment's deployment history — directly satisfies GOV-006 (internal audit access to configuration change history) for Snowflake objects. - Clean multi-environment promotion with full environment-state diffing — no manual inspection of Snowflake to determine what has and has not been applied. - Aligned with ADR-049: Snowflake-native tooling for Snowflake concerns.

Negative: - DCM Projects is in Public Preview — production use gated on GA. - Migration effort: MOD-038, MOD-085, MOD-098 each require a DDL translation pass to convert from imperative scripts to DCM project format. Estimated at 0.5 days per module. - Team must learn snow dcm CLI and DCM project YAML syntax — modest learning curve. - DCM's dependency declaration syntax must be understood for any object that references a dbt-built view — new pattern for the team but simpler than the current two-phase workaround.

---

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