ADR-047: Snowflake reference data write-back — dedicated Lambda pattern for operational delivery¶
| Status | Accepted |
| Date | 2026-05-01 |
| Deciders | CTO, Head of Architecture, Head of Data |
| Affects repos | bank-risk-platform, bank-platform, bank-core, bank-payments |
Status: Accepted — 2026-05-01
Context¶
ADR-039 established Snowflake as both an analytics platform and a source for normalised external reference data — market rates, benchmark curves, FX spot and forward rates, and similar time-series inputs ingested from third-party market data providers.
When that reference data has an operational use — i.e. it must be accessible to Neon Postgres services for sub-second reads during transaction processing — it needs to be written back from Snowflake to the appropriate operational database. ADR-036 defines one write-back path: Snowflake → decision_result_inbox → MOD-079 → Neon, for governance decisions (customer risk tier, ECL stage classifications, credit scores). That path requires a versioned decision payload, a decision_id, and a reason_code drawn from governance_meta.reason_code_catalog. None of that machinery is appropriate for reference data: market rates are deterministic outputs of market data ingestion, not governance decisions, and forcing them through MOD-079's inbox would make the reason code catalog meaningless.
The gap: no pattern has been defined for the delivery of Snowflake-computed reference data to operational Postgres. MOD-085 (market rates ingestion) correctly identified this and implemented a write-back Lambda that writes FX spot rates to SD04 Postgres. Without a governing ADR, that implementation is an undocumented exception and a potential future source of inconsistency.
This ADR fills that gap. It defines a second, distinct write-back path — complementary to ADR-036, not a replacement — and explicitly scopes each path so future modules do not misapply either.
Decision¶
1. Reference data uses a dedicated write-back Lambda, not MOD-079¶
Snowflake-computed reference data is written to operational Neon Postgres via a dedicated write-back Lambda. MOD-079's decision_result_inbox path is not used. The two paths are categorically distinct:
| ADR-036 (decision write-back) | ADR-047 (reference data write-back) | |
|---|---|---|
| Data type | Governance decisions — risk tier, ECL stage, credit score, sanctions disposition | Reference data — market rates, benchmark curves, TP grids, regulatory rate tables |
| Required payload fields | decision_id, reason_code, versioned decision payload |
(data_type, key_fields, valid_at) idempotency tuple |
| Router | MOD-079 | Dedicated Lambda per data domain |
| Reason code required | Yes — from governance_meta.reason_code_catalog |
No |
| Audit requirements | Full (policy traceability, reason catalog) | Ingestion log only (provider, timestamp, row count) |
2. Lambda contract — idempotent upsert keyed on (data_type, key_fields, valid_at)¶
The write-back Lambda must:
- Execute an idempotent
INSERT … ON CONFLICT DO NOTHING(or equivalent upsert) keyed on(data_type, key_fields, valid_at). Duplicate invocations must be safe. - Write a provenance row to the owning module's ingestion log table:
(run_id, data_type, valid_at, row_count, provider_id, written_at). - Return a structured result (run_id, rows_written, target_table) for the calling Snowflake Task to log.
- Not perform any schema migration. The target table DDL is owned by the consuming module's migration set.
3. Snowflake Task is the authoritative trigger¶
The write-back Lambda is invoked by a Snowflake Task as a Task Action (using the Snowflake Python connector's call_lambda() or equivalent). The Task sits at the end of the producing module's Task DAG — downstream of the dbt transform and dbt test steps. It runs only when the upstream dbt tests pass.
EventBridge is not used as the trigger for the write-back. The Task is the authoritative scheduler; Lambda is the delivery mechanism.
4. Confirmation event after successful write¶
After a successful write, the Lambda emits a domain event on the producing module's system bus (e.g. bank.risk-platform.market_rates_updated). This event:
- Signals to operational subscribers that fresh data is available in Postgres.
- Is the EventBridge boundary defined by ADR-046 for SD06 modules — it crosses from Snowflake-world into AWS-world at this single point.
- Must not be used as a gate by SD06-internal Snowflake modules (ADR-046 § 5 — no intra-SD06 EventBridge subscriptions). SD06 modules consume reference data via dbt
source()references to the Snowflake schema. - May be subscribed to by operational modules in other system domains (SD01, SD04, etc.) that need to invalidate caches or trigger downstream processing.
5. Scope: when this pattern applies¶
Use ADR-047 write-back when all of the following are true:
- The data originates from a Snowflake-computed or Marketplace-ingested source.
- The data is reference data — deterministic, provider-supplied, or derived by a repeatable transform — not a governance decision about a specific customer or counterparty.
- The data must be available in operational Neon Postgres for sub-millisecond reads during transaction processing or product pricing.
- The write frequency is at most hourly (end-of-day rate grids, intraday FX spot refreshes). High-frequency tick data must not use this pattern.
Do not use ADR-047 for:
- Customer-specific decisions (credit scores, risk tiers, ECL stages) → use ADR-036.
- SD06-internal inter-module data sharing → use dbt source() references (ADR-046).
- Data that is only ever read by Snowflake consumers → no write-back required.
Consequences¶
MOD-085 (market rates ingestion & normalisation) is the reference implementation. Its write-back Lambda, which writes FX spot rates to SD04 Postgres (payments.fx_rates) and emits bank.risk-platform.market_rates_updated, is fully compliant with this ADR. No refactor required.
MOD-086 (funds transfer pricing engine) writes the daily TP rate grid to SD01 Postgres (tp_rates) via a write-back Lambda. This also falls under ADR-047. The write is triggered by MOD-086's end-of-day Task DAG after dbt tests pass on the ftp.transfer_prices mart.
Future modules that need to deliver Snowflake-computed reference data to operational Postgres must follow this pattern. A new write-back Lambda (or a new handler in an existing Lambda if the data domain is closely related) is preferable to extending MOD-079's decision inbox.
MOD-079 scope is unchanged. It handles governance decisions only. Its contract (decision_id, reason_code, versioned payload) is not modified by this ADR.
ADR-036 scope is unchanged. The decision write-back path through decision_result_inbox is unaffected. This ADR does not supersede ADR-036; it defines the complementary pattern for a categorically different data type.
All ADRs
Compiled 2026-05-22 from source/entities/adrs/ADR-047.yaml