Loading...
Loading...
Most reconciliation tools tell you something is wrong. Drift Recon tells you what broke, when it broke, and why — combining weighted matching with statistical drift detection on a rolling 30-day window.
Field Note — Drift Recon
---
Reconciliation is the boring part of fintech until it fails. Then it becomes the only part that matters.
Most reconciliation tools match transactions against bank statements and surface mismatches. This is useful but insufficient. When match rates degrade — when the percentage of successfully matched transactions drops from 98% to 87% — teams are left manually digging through thousands of records to figure out why. Is it a data quality issue? A timing shift? A genuine upstream break? Without a historical baseline, there's no way to tell normal noise from a real problem.
Drift Recon was built to answer not just "what's wrong" but "what broke, when, and why." It combines multi-factor weighted matching with statistical drift detection on a rolling 30-day baseline, and surfaces rule-based root-cause hypotheses for every drift event it flags.
---
A reconciliation mismatch is a symptom, not a diagnosis. Consider these scenarios:
All three show up as "match rate dropped." Only one is a genuine security incident. Without context — a historical baseline, a confidence score, a root-cause hypothesis — you can't tell which is which.
---
The architecture is deliberately simple:
Nginx (TLS termination) → FastAPI (ingestion, matching, drift analysis) → Streamlit (dashboard)
↓
PostgreSQL (transactions, statements, results, drift events)
↓
Redis (rate limiting, APScheduler job store)We chose FastAPI over Django because the domain is narrow — ingestion, matching, analysis — and FastAPI's async handling gives us better throughput for I/O-bound operations (parsing CSVs, querying PostgreSQL, computing statistics). The Streamlit dashboard is a separate process, not embedded in the API, so heavy dashboard queries don't block ingestion.
PostgreSQL stores everything:
The schema is normalized but pragmatic. Reconciliation results are denormalized with respect to transactions and statements because investigation queries need to be fast — "show me all unmatched transactions from yesterday" should be a single table scan, not a three-way join.
The matcher doesn't use a single key. It uses four factors, weighted by importance:
MATCH_WEIGHTS = {
"amount": 0.40, # 40% — exact amount match is strong signal
"date": 0.30, # 30% — transactions close in time are likely related
"reference": 0.20, # 20% — reference number match is specific but sometimes missing
"description": 0.10, # 10% — fuzzy text match on description
}Each factor contributes a score between 0 and 1. The composite score is the weighted sum. A perfect match (all factors = 1.0) scores 1.0. A partial match might score 0.85 — enough to be flagged for human review rather than auto-matched or unmatched.
The date factor uses a tolerance window. For most sources, transactions within 24 hours are considered "close." For sources with known delays, the tolerance is configurable per source. This prevents a timing shift from destroying match rates.
The reference factor handles missing or malformed references gracefully. If a transaction has no reference number, the reference score is 0, but the other factors can still produce a match. If a reference exists but is malformed (extra spaces, different case), we normalize before comparison.
Invalid rows are never silently dropped. If a CSV row fails validation — missing amount, unparseable date, negative value — it goes into a quarantine table with the full raw data, the validation error, and the ingestion batch ID:
class QuarantinedRecord(BaseModel):
raw_data: str # Original CSV row, untouched
error_message: str # Why it failed validation
batch_id: str # Which ingestion batch it came from
created_at: datetimeThis is critical for auditability. "We dropped 47 invalid rows" is a compliance nightmare. "We quarantined 47 invalid rows, here's why, and here's the raw data" is defensible. The quarantine table needs periodic triage — someone has to review and fix or reject these records — but nothing is ever lost.
The drift analyzer runs on a schedule (hourly, daily, or on-demand) and computes match-rate statistics against a 30-day rolling baseline:
def analyze_drift(source_id: str, window_days: int = 30):
snapshots = get_snapshots(source_id, days=window_days)
if len(snapshots) < 7:
return None # Not enough history for statistical significance
current = get_current_stats(source_id)
baseline_mean = statistics.mean(s.match_rate for s in snapshots)
baseline_std = statistics.stdev(s.match_rate for s in snapshots)
if baseline_std == 0:
z_score = 0 # No variance means no drift detectable
else:
z_score = (current.match_rate - baseline_mean) / baseline_std
# z-score bands: >2 is significant, >3 is critical
if abs(z_score) > 3:
return DriftEvent(
severity="CRITICAL",
z_score=z_score,
metric="match_rate",
hypothesis=generate_hypothesis(current, snapshots)
)
elif abs(z_score) > 2:
return DriftEvent(
severity="HIGH",
z_score=z_score,
metric="match_rate",
hypothesis=generate_hypothesis(current, snapshots)
)The z-score tells you how unusual the current match rate is compared to the source's own historical pattern. A source that normally matches at 95% ± 2% dropping to 90% is a 2.5-sigma event — unlikely to be random noise. A source that normally matches at 80% ± 10% dropping to 70% is a 1-sigma event — probably normal variance.
This per-source adaptation is the key insight. Global thresholds ("alert if match rate < 90%") are wrong because different sources have different normal ranges.
When drift is detected, the system generates a hypothesis based on the pattern of the current stats versus the baseline:
def generate_hypothesis(current: Stats, baseline: list[Stats]) -> str:
hypotheses = []
if current.unmatched_rate > baseline_mean_unmatched * 1.5:
hypotheses.append("Unmatched rate increased — possible data quality issue or upstream format change")
if current.avg_confidence < baseline_mean_confidence * 0.9:
hypotheses.append("Average match confidence dropped — possible reference field corruption or timing shift")
if current.date_delta_mean > baseline_date_delta_mean * 2:
hypotheses.append("Average date delta increased — possible statement delay or timezone misconfiguration")
if current.quarantine_rate > 0:
hypotheses.append(f"Quarantine rate is {current.quarantine_rate}% — invalid rows may be reducing effective match rate")
return "; ".join(hypotheses) if hypotheses else "No specific hypothesis — general drift detected"These hypotheses are not definitive diagnoses. They are starting points for human investigation. A hypothesis of "possible upstream format change" tells the engineer to check the latest CSV export from the payment processor, not to debug the matching algorithm.
Ingestion is idempotent via SHA-256 content hashing. When a file is uploaded, we compute a hash of its content and derive a deterministic batch ID:
def compute_batch_id(file_content: bytes) -> str:
content_hash = hashlib.sha256(file_content).hexdigest()
return f"batch_{content_hash[:16]}_{datetime.now().strftime('%Y%m%d')}"If the same file is uploaded twice — retry logic, user mistake, scheduled job overlap — the batch ID conflicts on the unique index and the second upload is rejected before any processing begins. This makes reruns and retries safe by construction.
The tradeoff is that near-duplicate files (same data, different formatting) produce different hashes. We mitigate this by normalizing the file content before hashing — stripping BOM, normalizing line endings, sorting columns — so that semantically identical files produce the same batch ID.
Scheduled drift analysis runs via APScheduler with a PostgreSQL-backed job store. This means scheduled jobs survive restarts — if the server goes down at 2am, the 3am drift analysis still runs when it comes back up. Redis is used for rate limiting and as a distributed lock to prevent multiple workers from running the same analysis simultaneously.
---
The confidence score has to weigh four different factors — amount, date, reference, description — in a way that feels fair across different data sources. A source with rich reference numbers should not be penalized because description matching is weak. A source with no references should still be matchable via amount and date.
Our solution was to normalize weights per source. During onboarding, the system analyzes the first 1,000 records to determine which fields are present and reliable. If references are present in 95% of records, the reference weight stays at 20%. If references are present in only 30% of records, the weight is redistributed to amount and date. This auto-calibration happens once per source and can be manually overridden.
Z-score thresholds of 2 and 3 are standard statistical practice, but they assume normal distribution. Match rates are not always normally distributed — they might be bimodal (high on weekdays, low on weekends) or have seasonal patterns.
We handle this by stratifying the baseline. Instead of one 30-day baseline, we maintain separate baselines for day-of-week and hour-of-day patterns. A Monday morning match rate is compared to previous Monday mornings, not to Sunday evenings. This reduces false positives from normal periodic variation.
---
A reconciliation tool is judged by what it does after it finds a mismatch, not by the matching itself. Matching is table stakes. The value is in diagnosis — telling the engineer where to look, not just that something is wrong.
Idempotency has to be the default behavior of every endpoint, not a special case for retries. If ingestion isn't idempotent by default, retries become dangerous. Every retry is a potential duplicate. Every duplicate is a potential reconciliation error.
A human review band between auto-matched and unmatched reduces false confidence in fully automated decisions. Auto-matching at 100% confidence is dangerous — it hides uncertainty. A band of 60-85% confidence flagged for human review catches edge cases that the algorithm can't resolve, while still automating the clear cases.
---
Drift Recon is open source at [github.com/Gwerdonatus/drift-recon](https://github.com/Gwerdonatus/drift-recon)