Skip to content

ADR-008: Reconciliation and exception management
Status Accepted
Date 2026-04-10
Deciders CTO, CFO, Head of Payments, Head of Platform
Affects repos bank-core, bank-payments, bank-risk-platform

Status

Accepted — 2026-04-10

Context

A bank's reconciliation process is the mechanism by which it verifies that its internal records match external reality — nostro accounts, payment scheme settlement files, card network files, and correspondent bank statements. Breaks are inevitable. The question is how quickly they are detected, how they are investigated, and how they are resolved.

Exception management is the broader discipline: what happens when something goes wrong in a financial process — a failed payment, a suspense account entry, a duplicate transaction, an unmatched settlement — and how is it tracked, owned, and resolved within SLA.

This is a compliance obligation (REP-004, CAP-002) as well as an operational one. Unresolved breaks are a regulatory concern.

Requirements

  • Automated reconciliation runs for all payment rails (NPP, Payments NZ, FX nostros, card schemes) on settlement cycle
  • Breaks detected and classified automatically — not discovered by manual review
  • Exceptions routed to the correct owner with priority, SLA, and audit trail
  • Suspense account management — entries must not age beyond defined thresholds without escalation
  • Management reporting on break volumes, ageing, and resolution time
  • Regulatory evidence trail for all resolution actions

Options evaluated

Reconciliation logic as Snowflake dynamic tables and views. Settlement files ingested to Snowflake via S3 on each settlement cycle. Internal position from Postgres CDC. Matching logic in SQL. Breaks surfaced as a Snowflake table. Exceptions managed via a back office queue in the app (SD08).

Strengths: All data already in Snowflake; no additional platform; SQL matching logic is transparent and auditable; easy to adjust rules without deployment; Snowflake can handle the volume easily; breaks feed directly into operational dashboards; no additional licensing cost.

Weaknesses: Reconciliation logic in SQL requires discipline to maintain; complex multi-leg FX reconciliations need careful modelling; operational queue for exceptions needs to be built in the back office app.

Option B — Purpose-built reconciliation platform (AutoRek, SmartStream, Duco)

Specialist reconciliation platforms with pre-built connectivity, matching algorithms, and exception workflows.

Strengths: Pre-built connectors for common payment schemes; sophisticated matching algorithms; built-in exception workflow and SLA tracking; regulatory reporting built in.

Weaknesses: Significant licensing cost (six figures annually for enterprise platforms); heavy implementation; adds a new platform to operate and maintain; data must be moved to the platform; overkill for a startup bank's initial volume; KISS violation.

Option C — Hybrid — Snowflake for reconciliation, dedicated workflow tool for exceptions

Use Snowflake for the matching and break detection. Use a lightweight workflow tool (Linear, Jira, or a custom back office queue) for exception tracking and resolution. Integration between them via the write-back API.

Perspective evaluation

Perspective Snowflake-native Purpose-built platform Hybrid
Performance & Scalability ✓ ✓ ✓
Evolution ✓ SQL rules easy to change ~ Platform-dependent ✓
Strategy ✓ Consistent with data platform ~ New vendor dependency ✓
Integration ✓ Data already in Snowflake ~ New integrations required ✓
Support & Maintenance ✓ No new platform ~ Additional platform to maintain ~
Cost ✓ No additional licensing ✗ Six figures annually ✓
Regulatory ✓ Auditability via Snowflake ✓ Purpose-built ✓
Capability ~ Must build exception queue ✓ Pre-built ✓
Resource ✓ Snowflake skills reused ~ New platform skills ✓

See perspectives.md for how to use these evaluation lenses.

Principles alignment

Principle Assessment
AP-001 KISS Snowflake-native is the simplest approach that meets requirements
AP-002 Data governance Reconciliation data owned and lineage-tracked in Snowflake
AP-003 Compliance Automated detection + immutable exception trail satisfies REP-004
AP-008 Real time Reconciliation is end-of-day cycle; real-time not required here
AP-009 Robust and serviceable Breaks must alert automatically; no manual polling

Recommendation (pending decision)

Snowflake-native reconciliation with back office exception queue (Option A / C hybrid):

  • Matching logic in Snowflake dynamic tables — transparent, auditable, adjustable
  • Settlement files land in S3 → ingested to Snowflake on each settlement cycle (same pattern as ADR-003)
  • Internal position from Postgres CDC → Snowflake (already flowing)
  • Breaks written to a reconciliation_breaks table in Snowflake
  • Write-back API pushes open breaks to the back office exception queue (SD08)
  • Exception queue in back office app — owner assignment, priority, SLA countdown, resolution notes, audit trail
  • Management reporting from Snowflake — break volumes, ageing, resolution time by category

This approach adds no new platform, reuses existing data flows, and keeps the exception workflow visible to operations staff in the same app they use for everything else.

A purpose-built platform should be reconsidered if break volumes and complexity grow beyond what Snowflake SQL can handle cleanly — evaluate at 12 months.

Exception categories and SLA targets

Category Description Target resolution
Payment break Internal record doesn't match scheme settlement Same business day
Nostro break Internal FX position doesn't match correspondent 2 business days
Card scheme break Card network settlement file mismatch 5 business days
Suspense entry Unallocated amount in suspense account 5 business days
Duplicate transaction Suspected duplicate posting 2 business hours
Regulatory report break Reported amount differs from source Before next submission

Relevant viewpoints

  • Functional viewpoint — Reconciliation cycle flow; exception queue in back office
  • System viewpoint — Settlement file ingest; Snowflake matching; write-back to exception queue
  • Information viewpoint — reconciliation_breaks table structure; data lineage from settlement file to resolution
  • Operational viewpoint — Break alerting; SLA countdown; escalation path for aged breaks

See viewpoints.md for guidance on producing these viewpoints.

Signoff record

Date Name Role Status
2026-04-10 Ross Millen CTO Approved
2026-04-10 Ross Millen Head of Architecture Approved
2026-04-10 Ross Millen Head of Data Approved

Capabilities

Capability Description Relationship
CAP-025 Automated regulatory reporting pipeline governed — reconciliation breaks feed regulatory evidence trail via Snowflake
CAP-026 Exception-only case routing enabled — exception queue routes only unmatched breaks to agents
CAP-029 Immutable audit log governed — resolution actions written to immutable audit trail
CAP-094 Work queue management enabled — exception queue is a work queue with owner, priority, and SLA countdown
CAP-127 Automated payment reconciliation enabled — settlement-cycle reconciliation across all payment rails
ADR Title Relationship
ADR-002 Snowflake as the analytics and risk compute platform reconciliation matching logic lives in Snowflake Dynamic Tables
ADR-003 CDC pipeline — Neon Postgres to Snowflake via Firehose and Apache Iceberg internal position flows to Snowflake via CDC for matching

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