Sliding-Window Date Reconciliation for ACH & Wire Settlement

Fixed-date reconciliation breaks the moment a payment's posting date and its value date disagree — and in production ACH and wire operations they disagree constantly. Batch processing lag, weekend and holiday settlement shifts, Fedwire cutoff variances, same-day versus next-day ACH windows, and cross-border timezone offsets all decouple the calendar day a transaction lands from the day it was dated. Join two settlement streams on an equality predicate over the date field and every one of those legitimate lags surfaces as a false break, flooding the exception queue with items that were never actually broken. Sliding-window date reconciliation fixes this by evaluating candidate pairs across a bounded, rolling temporal range instead of a single rigid calendar boundary. This page sits within the broader transaction matching and reconciliation algorithms framework, and it is the stage that expands the date dimension — and only the date dimension — so that settlement lag stops manufacturing exceptions.

The discipline here is restraint. A date window is the one relaxation in the pipeline that engineers reach for reflexively and calibrate carelessly, because widening it is a one-character change that silently multiplies the candidate set every downstream comparison must scan. Done right, the window is the narrowest span that absorbs real settlement lag while still letting a genuine finality failure surface as an exception; done wrong, it becomes a place where late, unrelated transactions get auto-matched and real breaks disappear. This guide defines the window at the field level, shows where it belongs relative to deterministic and fuzzy matching and tolerance threshold configuration, gives a memory-safe Python implementation, tabulates the failure modes that corrupt payment pipelines, and ties each design choice to the NACHA and Reg E rules that make date flexibility a governed control rather than a convenience.

Concept Definition: A Bounded Rolling Interval Over Normalized Dates

A sliding window is a half-open date interval [processing_date - lookback, processing_date + lookahead] that advances by one business day each cycle. On every run it retains still-unpaired transactions whose normalized date remains inside the interval, ingests the new inbound file, attempts to pair the residual against the incoming records, and expires anything that has aged past the trailing boundary. Unlike a fixed settlement-date join, the interval is a range predicate, so a transaction dated Friday can legitimately match a settlement posted the following Monday without either side being flagged.

The window operates on normalized dates, and normalization is non-negotiable. Inbound files carry heterogeneous temporal encodings that must be reconciled to a single canonical form before any interval test runs:

  • NACHA effective entry dates are YYMMDD in the Batch Header (Company/Batch Header record positions 70–75) and carry no time or zone; the settlement date is Fed-assigned and can differ from the effective date across cutoffs and holidays.
  • Fedwire carries date and time in the IMAD (Input Message Accountability Data — the YYYYMMDD cycle date is the leading 8 bytes of the IMAD) with an implicit Eastern-time settlement clock.
  • ISO 20022 pacs.008 messages carry IntrBkSttlmDt as an ISO 8601 YYYY-MM-DD date and CreDtTm as a full timezone-aware timestamp.
  • Internal core ledgers typically store UTC or local-business-day timestamps that may already have been shifted by a posting engine.

Every inbound record must be parsed into a timezone-aware value and reduced to a single canonical settlement business day before the window sees it. Cross-border wires and multi-region ACH batches require strict UTC normalization first, then conversion to the settlement calendar of the rail. Skipping normalization introduces phantom drift: a wire stamped 23:30 America/New_York and a ledger entry stamped 03:30Z are the same business day, but a naive comparison treats them as two, and the window then has to be widened to paper over a bug that normalization should have removed. Parse with the standard library per the datetime and zoneinfo modules, and reject rather than guess when an offset is absent.

Window sizing is rail-specific and is the calibration exercise this whole page turns on. Same-day wires demand a zero-day window because any drift is a genuine anomaly; standard ACH tolerates roughly ±1 business day for effective-versus-settlement skew; cross-border SWIFT gpi corridors may need ±2 days to absorb intermediary-bank hops and timezone rollovers. The most common production pattern — a rolling three-day interval that advances with the processing date and expires stale candidates — is worked through end to end in configuring rolling 3-day reconciliation windows.

Architecture: Where the Window Sits in the Pipeline

Stage ordering is the load-bearing design decision. Date-window matching must run after deterministic keying but before tolerance relaxation and probabilistic scoring. Deterministic exact-key matches (trace number, IMAD/OMAD, EndToEndId) clear first in expected per record and remove settled items from the pool; only the residual — the transactions that failed an exact key — is expanded across the date interval. Expanding the date range before deterministic keying would inflate the candidate set for matches the hash map could have resolved for free, and running the window after tolerance and fuzzy scoring would let those expensive stages compare against dates that were never valid candidates in the first place.

The engine therefore behaves as a funnel where the window is a bounded amplifier on exactly one axis: exact keys clear, the residual is fanned out over lookback + lookahead + 1 candidate days, each candidate day is probed by a cheap indexed lookup, and survivors carry forward into tolerance threshold configuration and the multi-field fallback chain. Because the window multiplies work by the span , it must be the smallest value that clears legitimate lag — a design constraint, not a tuning afterthought.

Where the sliding window sits, and how it fans the residual over a business-day range A pipeline chain runs left to right: a deterministic exact-key stage that clears matches in O(1) per record passes only its unmatched residual into the sliding-window stage, which costs O(n times w); survivors then flow into the tolerance stage and the multi-field fallback chain. Below, the window stage is zoomed into a business-day axis with five day buckets from May 10 to May 14. A shaded band marks the rolling window covering May 11 (trailing edge, processing_day minus lookback), May 12 (processing_day), and May 13 (leading edge, processing_day plus lookahead). May 10 sits outside the trailing edge: its retained ledger record has aged out and is routed to a dead-letter queue with code ERR_WINDOW_EXPIRED. May 14 is outside the leading edge and not yet ingested. An inbound file record, TRACE-88213 for 123.45 dollars, probes candidate days nearest-first: probe one at offset zero (May 12) misses, probe two at offset plus one (May 13) misses, probe three at offset minus one (May 11) is the first amount-exact hit and matches ledger record LEDGER-4471 with day_offset plus one. Each cycle the whole window advances one business day and records past the trailing edge expire. Where the window sits — and how it fans one axis over a business-day range Deterministic exact-key · O(1)/rec settled items removed O(n · w) Sliding Window date-range probe · residual only Tolerance amount bands Multi-Field fallback chain residual survivors expand exactly one axis — the date Inside the sliding window — one cycle over the day axis Inbound file record TRACE-88213 · $123.45 probe nearest-day-first — first amount-exact hit wins May 10 outside window LEDGER-40 aged past trailing edge May 11 retained LEDGER-4471 $123.45 · ✓ offset +1 May 12 processing_day residual no pair yet May 13 retained LEDGER-52 $80.10 May 14 outside window not yet ingested 1 2 3 trailing edge = D − lookback leading edge = D + lookahead EXPIRED → dead-letter queue ERR_WINDOW_EXPIRED Each cycle the window advances one business day → records past the trailing edge expire; they are never dropped

Phase-by-Phase Implementation

The engine runs as four ordered phases. Each consumes a stream and yields a stream, so no phase materializes the full batch in memory — a hard requirement when a single Fedwire end-of-day sweep can exceed a worker's resident memory. Amounts are held as integer cents throughout so an IEEE 754 rounding artefact can never be mistaken for a real variance, and unpaired records are persisted to disk-backed state so the window survives across processing cycles.

Phase 1 — Restore prior state and prune the trailing edge

The window is stateful: unmatched records from earlier cycles must survive into today's run, and anything older than the trailing boundary must expire deterministically rather than lingering forever. Restore from a JSONL buffer and prune before ingesting anything new.

python
from __future__ import annotations

import json
import logging
from collections import deque
from dataclasses import dataclass
from datetime import date, timedelta
from pathlib import Path
from typing import Iterator

logger = logging.getLogger("reconciliation.sliding_window")

@dataclass(frozen=True, slots=True)
class WindowRecord:
    trace_id: str
    amount_cents: int          # money as integer cents — never float
    settlement_day: date       # normalized canonical business day
    routing_number: str
    reference: str

class SlidingWindowMatcher:
    def __init__(
        self,
        lookback_days: int = 1,
        lookahead_days: int = 1,
        state_path: Path = Path("/var/lib/recon/state"),
    ) -> None:
        self.lookback = timedelta(days=lookback_days)
        self.lookahead = timedelta(days=lookahead_days)
        self.state_path = state_path
        self.state_path.mkdir(parents=True, exist_ok=True)
        self.unmatched: deque[WindowRecord] = deque()

    def restore(self) -> None:
        """Reload unpaired records from the previous cycle's buffer."""
        buf = self.state_path / "unmatched_buffer.jsonl"
        if not buf.exists():
            return
        with buf.open("r", encoding="utf-8") as fh:
            for line in fh:                       # generator I/O — never read().splitlines()
                raw = json.loads(line)
                self.unmatched.append(
                    WindowRecord(
                        trace_id=raw["trace_id"],
                        amount_cents=int(raw["amount_cents"]),
                        settlement_day=date.fromisoformat(raw["settlement_day"]),
                        routing_number=raw["routing_number"],
                        reference=raw["reference"],
                    )
                )

    def prune_expired(self, processing_day: date) -> None:
        """Drop records that have aged past the trailing edge of the window.

        The buffer is kept in settlement-day order, so expiry is a cheap
        left-pop rather than a full scan — O(k) in expired records only.
        """
        trailing_edge = processing_day - self.lookback
        while self.unmatched and self.unmatched[0].settlement_day < trailing_edge:
            expired = self.unmatched.popleft()
            logger.warning(
                "WINDOW_EXPIRED trace=%s day=%s edge=%s",
                expired.trace_id, expired.settlement_day.isoformat(),
                trailing_edge.isoformat(),
            )

Phase 2 — Index the retained buffer by candidate day

To keep the per-record probe cheap, bucket the retained residual by (routing_number, settlement_day). An inbound record then probes only the handful of buckets its own window spans, turning what would be an all-pairs scan into a small local lookup.

python
from collections import defaultdict

WindowIndex = dict[tuple[str, date], list[WindowRecord]]

def index_by_day(records: deque[WindowRecord]) -> WindowIndex:
    """Bucket retained residual by (routing, settlement_day) so each inbound
    probe is O(w) lookups of small buckets, not an O(n*m) all-pairs scan."""
    index: WindowIndex = defaultdict(list)
    for rec in records:
        index[(rec.routing_number, rec.settlement_day)].append(rec)
    return index

Phase 3 — Stream inbound records and probe the window

Each inbound record is probed against every candidate day in its interval. The first amount-exact hit within the window resolves the match; a miss retains the inbound record in the buffer for a future cycle. Emitting via yield keeps the whole pass constant-memory regardless of daily volume.

python
from enum import Enum

class WindowOutcome(str, Enum):
    MATCHED_IN_WINDOW = "MATCHED_IN_WINDOW"
    RETAINED = "RETAINED"

@dataclass(frozen=True, slots=True)
class WindowResult:
    inbound_trace: str
    matched_trace: str | None
    outcome: WindowOutcome
    day_offset: int | None      # signed drift, in days, that the match required

def candidate_days(centre: date, lookback: timedelta, lookahead: timedelta) -> Iterator[date]:
    """Yield every settlement day inside the window, nearest-first, so the
    smallest-drift match wins and large drifts are only reached on a miss."""
    yield centre
    for delta in range(1, (lookback.days + lookahead.days) + 1):
        if delta <= lookahead.days:
            yield centre + timedelta(days=delta)
        if delta <= lookback.days:
            yield centre - timedelta(days=delta)

def match_window(
    self: SlidingWindowMatcher,
    inbound: Iterator[WindowRecord],
    index: WindowIndex,
) -> Iterator[WindowResult]:
    """Stream-match inbound records against the day-bucketed window index."""
    consumed: set[str] = set()
    for msg in inbound:
        hit: WindowRecord | None = None
        for cand_day in candidate_days(msg.settlement_day, self.lookback, self.lookahead):
            bucket = index.get((msg.routing_number, cand_day), [])
            hit = next(
                (r for r in bucket
                 if r.amount_cents == msg.amount_cents and r.trace_id not in consumed),
                None,
            )
            if hit is not None:
                consumed.add(hit.trace_id)
                yield WindowResult(
                    inbound_trace=msg.trace_id,
                    matched_trace=hit.trace_id,
                    outcome=WindowOutcome.MATCHED_IN_WINDOW,
                    day_offset=(msg.settlement_day - hit.settlement_day).days,
                )
                break
        if hit is None:
            self.unmatched.append(msg)          # carry forward to a future cycle
            yield WindowResult(msg.trace_id, None, WindowOutcome.RETAINED, None)

Phase 4 — Persist the surviving buffer

At the end of the cycle, write the still-unmatched buffer back to disk so the next run restores it. Persist derived reconciliation state only — never mutate or rewrite the original inbound payloads, which belong in WORM storage.

python
def persist(self: SlidingWindowMatcher) -> None:
    buf = self.state_path / "unmatched_buffer.jsonl"
    tmp = buf.with_suffix(".jsonl.tmp")
    with tmp.open("w", encoding="utf-8") as fh:
        for rec in self.unmatched:              # generator write — bounded memory
            fh.write(json.dumps({
                "trace_id": rec.trace_id,
                "amount_cents": rec.amount_cents,
                "settlement_day": rec.settlement_day.isoformat(),
                "routing_number": rec.routing_number,
                "reference": rec.reference,
            }) + "\n")
    tmp.replace(buf)                            # atomic swap — no torn state on crash
    logger.info("WINDOW_PERSISTED retained=%d", len(self.unmatched))

The deque with left-edge expiry, day-bucketed index, and atomic JSONL swap together give a constant memory footprint and crash-safe state — the window never balloons a DataFrame and never loses a retained record mid-cycle.

Edge Cases & Known Failure Modes

Most window incidents are not algorithm bugs; they are silent data conditions that make a correct interval test return a wrong answer. These are the recurring ones in ACH/wire pipelines and their mitigations.

Failure mode Root cause Mitigation
Timezone-induced value-date drift A wire stamped 23:30 ET and a ledger entry stamped 03:30Z are the same business day but compare as two Normalize every timestamp to UTC, then to the rail's settlement calendar, before the interval test; never widen the window to hide it
DST midnight rollover A daylight-saving transition shifts a timestamp across midnight, moving the day by one Use zoneinfo (not fixed UTC offsets) so the wall clock resolves correctly through the transition
Effective vs settlement skew NACHA effective entry date differs from the Fed-assigned settlement date across cutoffs/holidays Key the match on the settlement day, retain the effective day as metadata, size the window to the rail's known skew (±1 ACH)
Weekend/holiday gap A Friday-dated item settles Monday; a ±1 calendar window misses it Count in business days against a Fed holiday calendar, not calendar days
Window too wide Lookahead set generously "to be safe" auto-matches a later, unrelated same-amount transaction Keep the window the narrowest span that clears real lag; probe nearest-day-first so smallest drift wins
Expired match after cutoff A legitimate item arrives after its window has already pruned the counterpart Escalate expired records to manual review with full window metadata; do not silently drop
Duplicate same-amount candidates Two retained records share routing + amount + day; the wrong one is consumed Track consumed trace IDs per cycle; surface true duplicate settlement as an exception, never overwrite
float cent drift Amounts parsed as float accumulate IEEE 754 error and fail the amount-exact probe inside the window Store integer cents (or decimal.Decimal); compare in cents
Missing timezone offset A naive inbound timestamp with no zone is assumed local and shifts a day Reject records with absent offsets at ingestion rather than guessing

Compliance & Auditability

A date window is a governed control surface, not a convenience knob, because it directly changes whether a settlement break surfaces. Widen it and a genuine finality failure can be silently auto-matched to a lagging record; every window decision therefore has to be logged and defensible.

  • The window is bounded by settlement finality. Under the NACHA Operating Rules, same-day and next-day ACH settlement windows constrain how long an item may legitimately lag; the window must never extend past the rail's finality boundary, and an item that ages past it is isolated with the appropriate return reason code (for example R03 no account, R23 credit refused) rather than held open indefinitely.
  • Every retained, matched, and expired record is auditable. Regulation E (12 CFR 1005.11) requires an institution to investigate and resolve an alleged error on a consumer electronic transfer within defined timelines (generally 10 business days, extendable to 45). When a disputed item was paired across a date offset, you must be able to reconstruct the window boundaries in force, the drift the match required, and the retention history — a bare MATCHED flag cannot support that investigation.
  • State transitions are immutable and attributable. Every ingestion, retention, in-window match, and expiry is written to an append-only log, and original inbound files are held in WORM-compliant storage. SOX §404 and FFIEC examination guidance treat this trail as the internal-control evidence for the reconciliation process.

Because widening a window can conceal a break, the engine never discards a transaction. An item that ages out lands in an EXPIRED state carrying an explicit code (ERR_WINDOW_EXPIRED) and enters a dead-letter queue for replay during a dispute. A single window event serializes to a schema-validated JSON record:

json
{
  "event_ts": "2026-05-12T22:07:14.882Z",
  "pipeline_stage": "sliding_window",
  "inbound_trace": "TRACE-88213",
  "matched_trace": "LEDGER-CORE-4471",
  "outcome": "MATCHED_IN_WINDOW",
  "processing_day": "2026-05-12",
  "window_start": "2026-05-11",
  "window_end": "2026-05-13",
  "day_offset": 1,
  "rail": "ACH",
  "exception_code": null,
  "reg_e_eligible": true
}

Testing & Verification

Window logic is high-blast-radius code: an off-by-one on the boundary either resurrects false breaks or auto-matches unrelated items. Anchor the suite on the exact drift conditions above and assert the boundary behaviour explicitly.

python
import pytest
from datetime import date, timedelta

def rec(**kw) -> WindowRecord:
    base = dict(trace_id="T1", amount_cents=12345,
                settlement_day=date(2026, 5, 12),
                routing_number="021000021", reference="INV-88213")
    return WindowRecord(**{**base, **kw})

def matcher() -> SlidingWindowMatcher:
    m = SlidingWindowMatcher(lookback_days=1, lookahead_days=1)
    return m

def test_one_day_drift_matches_inside_window() -> None:
    m = matcher()
    m.unmatched.append(rec(trace_id="LEDGER", settlement_day=date(2026, 5, 11)))
    index = index_by_day(m.unmatched)
    results = list(match_window(m, iter([rec(settlement_day=date(2026, 5, 12))]), index))
    assert results[0].outcome is WindowOutcome.MATCHED_IN_WINDOW
    assert results[0].day_offset == 1

def test_drift_beyond_window_is_retained_not_matched() -> None:
    m = matcher()
    m.unmatched.append(rec(trace_id="LEDGER", settlement_day=date(2026, 5, 8)))
    index = index_by_day(m.unmatched)
    results = list(match_window(m, iter([rec(settlement_day=date(2026, 5, 12))]), index))
    assert results[0].outcome is WindowOutcome.RETAINED   # 4 days out, window is +/-1

def test_expiry_drops_only_records_past_the_trailing_edge() -> None:
    m = matcher()
    m.unmatched.append(rec(trace_id="OLD", settlement_day=date(2026, 5, 8)))
    m.unmatched.append(rec(trace_id="FRESH", settlement_day=date(2026, 5, 12)))
    m.prune_expired(processing_day=date(2026, 5, 12))     # trailing edge = 05-11
    assert [r.trace_id for r in m.unmatched] == ["FRESH"]

def test_amount_mismatch_never_matches_even_on_the_same_day() -> None:
    m = matcher()
    m.unmatched.append(rec(trace_id="LEDGER", amount_cents=12345))
    index = index_by_day(m.unmatched)
    results = list(match_window(m, iter([rec(amount_cents=12346)]), index))
    assert results[0].outcome is WindowOutcome.RETAINED   # 1c off falls through to tolerance

Frequently Asked Questions

How wide should the date window be for each rail?

Size it to the rail's known settlement skew and nothing more. Same-day wires get a zero-day window because any drift is a genuine anomaly that should surface as an exception. Standard ACH tolerates roughly ±1 business day for the effective-versus-settlement gap. Cross-border SWIFT gpi corridors may need ±2 business days to absorb intermediary-bank hops and timezone rollovers. Never set one global window across rails — it will either resurrect false breaks on the tight rails or auto-match unrelated items on the loose ones. The configuring rolling 3-day reconciliation windows walkthrough covers calibrating and expiring a concrete window.

A legitimate payment keeps landing in the exception queue on the date field. What is wrong?

Almost always value-date drift the window is not absorbing: the ACH effective date differs from the settlement/posting date, a Fed cutoff pushed the item to the next processing day, or a daylight-saving transition shifted a timestamp across midnight. First confirm your normalization is correct — a wire at 23:30 ET and a ledger entry at 03:30Z are the same business day, and if they compare as two the real fix is normalization, not a wider window. Only after normalization is sound should you size the window to the rail's genuine lag, and keep the exact key on amount, routing, and trace so you expand only the date dimension.

Why must the date window run after deterministic matching but before tolerance?

Because ordering controls both cost and correctness. Deterministic exact-key matching is per record and removes settled items from the pool, so running the window first would fan out candidates for matches the hash map could have cleared for free. Running the window after tolerance and fuzzy scoring would let those expensive stages compare against dates that were never valid candidates. The window belongs in the middle: it expands one axis — the date — on the residual only, then hands survivors to tolerance threshold configuration and the multi-field fallback chain.

How do I keep the window from matching a later, unrelated same-amount transaction?

Two guards. First, keep the window as narrow as the rail's real lag allows — most false in-window matches come from a lookahead set generously "to be safe." Second, probe candidate days nearest-first so the smallest-drift match wins, and track consumed trace IDs within a cycle so one retained record cannot be matched twice. If two retained records genuinely share routing, amount, and day, that is duplicate settlement (a re-presented ACH or a retransmitted file) and must be surfaced as its own exception, not silently consumed.

What happens to a transaction whose counterpart never arrives inside the window?

It expires deterministically. When a retained record ages past the trailing edge, it is popped from the buffer, written to an EXPIRED state with an explicit code (ERR_WINDOW_EXPIRED), and pushed to a dead-letter queue with its full retention history and window boundaries attached. It is never dropped — a silent drop is the one failure mode that is unrecoverable in a regulated pipeline, because the missing item only resurfaces later as an unexplained settlement break. Expired items route to manual review, where an operator can confirm a genuine finality failure or replay the record.

Do I count the window in calendar days or business days?

Business days, against a Fed holiday calendar. A ±1 calendar-day window will miss a Friday-dated item that settles the following Monday, because the counterpart is three calendar days away but only one business day away. Counting in business days — and normalizing every timestamp to the settlement calendar first — is what makes the window absorb weekend and holiday gaps without being widened to a value that would also swallow real drift.

Can I use a float for the amount check inside the window?

No. The in-window probe still tests amount equality, and amounts parsed as float accumulate IEEE 754 rounding error that is indistinguishable from a real one-cent variance to the comparison. Store money as integer cents or decimal.Decimal end to end. A cent that is genuinely off should fall through the window into tolerance threshold configuration, where a governed band decides whether it is an explainable rounding delta — not be masked by float drift inside the date stage.