ADR-049: Snowflake-native compute — Alerts and DMFs preferred over Lambda monitors within SD06¶
| Status | Accepted |
| Date | 2026-05-01 |
| Deciders | CTO, Head of Data, Head of Risk Engineering |
| Affects repos | bank-risk-platform, bank-platform |
Status: Accepted — 2026-05-01
Context¶
ADR-046 §6 permits Lambda for "publishing a single EventBridge event to an external subscriber when a Snowflake Task completes or fails." When ADR-046 was written this was the lowest-friction way to bridge a Snowflake threshold check to an external notification. Two Snowflake-native capabilities have since matured to the point where the Lambda is unnecessary for the specific ops-alerting and continuous data-quality use cases:
Snowflake Alerts (GA) allow a scheduled SQL condition check to emit a notification via a Notification Integration directly — no Lambda, no CloudWatch cron, no EventBridge rule. The alert object itself holds the schedule, the condition, and the notification target. Results are recorded in SNOWFLAKE.ACCOUNT_USAGE.ALERT_HISTORY, providing the same operational visibility as CloudWatch Logs.
Snowflake Data Metric Functions (DMFs, Enterprise+ tier, GA 2024) attach named metric functions directly to tables and run them on a configured schedule. Results land in SNOWFLAKE.LOCAL.DATA_QUALITY_MONITORING_RESULTS automatically — no custom result table, no on_run_end macro, no orchestration. System DMFs cover completeness, row count, freshness, uniqueness, and duplicate checks. Custom DMFs (3–10 lines of SQL) cover referential integrity, value ranges, and format conformance.
The pattern identified in MOD-038 (data quality monitor), and verified for retrofit in MOD-085 (carry-forward-monitor Lambda) and MOD-098 (unattributed-monitor Lambda), is: a Lambda opens a JDBC connection to Snowflake, runs a SELECT against a threshold view, and publishes an SNS/EventBridge notification if the condition is true. This work is entirely within Snowflake's compute boundary and is better expressed as a Snowflake Alert.
The Snowflake account is provisioned at Enterprise+ tier (confirmed 2026-05-01), satisfying the tier requirement for DMFs.
Decision¶
1. Snowflake Alerts replace Lambda monitor functions¶
When an automated check satisfies all of the following, it must be implemented as a Snowflake Alert, not a Lambda:
- The condition check is a SQL query against Snowflake objects.
- No external API call is required to evaluate the condition.
- No write to a non-Snowflake system (Neon Postgres, S3, etc.) is required as part of the check.
- The notification target is reachable via a Snowflake Notification Integration (SNS, email, webhook).
The Notification Integration BANK_SNS_INTEGRATION provisioned by MOD-102 migration 10 is the standard routing path. Alerts emit via SYSTEM$SEND_SNOWFLAKE_NOTIFICATION(SNOWFLAKE.NOTIFICATION.INTEGRATION('BANK_SNS_INTEGRATION')). The SNS topic routes to EventBridge and from there to MOD-076 for operational alerting — no Lambda in the path.
ADR-046 §6's allowance for "Lambda for publishing a single EventBridge event when a Snowflake Task completes" is superseded within SD06 by this decision for the ops-alerting use case. Lambda is still correct for EventBridge events directed at external subscribers outside SD06 that require structured JSON payloads beyond what SYSTEM$SEND_SNOWFLAKE_NOTIFICATION supports.
2. Snowflake DMFs for continuous runtime data quality¶
All SD06 modules with runtime data quality requirements must attach Snowflake DMFs to their source and staging tables rather than using dbt store_results: true as a runtime DQ mechanism.
- System DMFs (
SNOWFLAKE.CORE.NULL_COUNT,SNOWFLAKE.CORE.ROW_COUNT,SNOWFLAKE.CORE.FRESHNESS,SNOWFLAKE.CORE.UNIQUE_COUNT,SNOWFLAKE.CORE.DUPLICATE_COUNT) are used for standard completeness, freshness, and uniqueness checks. No custom implementation required. - Custom DMFs are written as 3–10 line SQL functions in the owning module's schema for referential integrity, value range, and format conformance checks not covered by system DMFs.
- DMF results are read from
SNOWFLAKE.LOCAL.DATA_QUALITY_MONITORING_RESULTS. The module's DQ gate view (v_dq_gate_status) joins this native table to the module's threshold configuration — no custom result table or macro is required.
dbt tests remain the build-time data quality gate: they run on dbt build during CI and catch model bugs in PR review. DMFs are the runtime (continuous) gate. Both layers are required. They are complementary, not alternatives.
3. Decision table — Lambda vs Snowflake-native¶
| Work type | Tool | Justification |
|---|---|---|
| Scheduled threshold check on Snowflake data → SNS/email notification | Snowflake Alert | This ADR §1 |
| Continuous per-table data quality metrics | Snowflake DMF | This ADR §2 |
| Scheduled data transformation / model refresh | Snowflake Task | ADR-046 §1 (unchanged) |
| Runtime DQ gate view built from DMF results | Snowflake View | This ADR §2 |
| Build-time data quality assertion | dbt test | ADR-046 §2 (unchanged) |
| External API read (no Snowflake-native connector) | Lambda | ADR-046 §6 (unchanged) |
| Write-back to application Postgres | Lambda | ADR-047 (unchanged) |
| EventBridge signal to external subscriber requiring structured JSON payload | Lambda | ADR-046 §5a (unchanged) |
4. MOD-102 enablement (one-time)¶
Snowflake Alerts and DMFs require two additional account-level grants provisioned by MOD-102:
EXECUTE ALERT ON ACCOUNT→BANK_NONPROD_RISK_ROLE,BANK_PROD_RISK_ROLE(andBANK_DBT_ROLEwhere dbt pipelines own Alerts).APPLY METRIC ON ACCOUNT→ same roles. Required to attach DMFs to tables.
These are added as migrations ~021 and ~022 in MOD-102. All existing SD06 deploy roles inherit these privileges at next run.
5. Retrofit scope¶
Two deployed modules carry Lambda monitor functions that this ADR supersedes. They will be retrofitted in a dedicated pass (not blocking new module development):
- MOD-085 —
carry-forward-monitorLambda →MARKET.A_BKBM_CARRY_FORWARDSnowflake Alert. - MOD-098 —
unattributed-monitorLambda →METERING.A_UNATTRIBUTED_BREACHSnowflake Alert.
New modules (MOD-038 and onwards) build with the native pattern from V1. MOD-085 and MOD-098 Lambda monitors remain in service until the retrofit pass completes.
Consequences¶
Positive:
- Eliminates a class of Lambda functions whose only job is to open a Snowflake connection, run a SELECT, and publish a notification. One Snowflake object replaces: Lambda handler + CloudWatch cron rule + EventBridge rule + Pulumi stack entries + IAM surface + TypeScript tests.
- Alert history and DMF run history are natively visible in Snowflake Studio and ACCOUNT_USAGE — no separate CloudWatch log query required for operational debugging.
- DMF results land in a native system table with account-level visibility across all monitored tables in Snowsight's "Data Quality" pane.
- Eliminates cold-start latency on scheduled monitor Lambdas (2–5 s per fire, compounded across modules).
Negative: - Snowflake Alerts offer less payload control than a Lambda publishing a structured EventBridge JSON event. Acceptable for ops-alert use cases; Lambda remains available when rich payloads are required. - DMF testability is Snowflake-side only — structural assertions on DDL + a manual integration test that verifies the DMF has run. Lambda unit tests for the equivalent logic are not available. - Cross-table DMF checks are limited (DMF takes a single TABLE input). dbt tests at build time remain the appropriate tool for multi-table assertions. - Enterprise+ tier required for DMFs. Downgrading the Snowflake account tier would disable attached DMFs without warning.
Guidance for existing modules¶
MOD-085: carry-forward-monitor Lambda (CloudWatch cron + JDBC check + SNS publish) is scheduled for retrofit to a Snowflake Alert reading MARKET.V_BKBM_CARRY_FORWARD_STATUS. The NZFMA-fetcher Lambda and FX-writeback Lambda are unchanged — both perform external API calls or write to Postgres, which are outside this ADR's scope.
MOD-098: unattributed-monitor Lambda (CloudWatch cron + JDBC check + EventBridge publish) is scheduled for retrofit to a Snowflake Alert reading METERING.V_UNATTRIBUTED_COSTS. The cost-explorer-fetcher Lambda is unchanged — it reads the AWS Cost Explorer API.
MOD-038 and future SD06 monitoring modules: Build with Snowflake Alerts and DMFs from V1. No Lambda monitor functions.
All ADRs
Compiled 2026-05-22 from source/entities/adrs/ADR-049.yaml