Введение

Инцидент SwiftPay 2024 — $2.3M недоплат drivers в регионе <Term abbr="DACH" title="DACH region" definition="Германия, Австрия, Швейцария (Deutschland, Austria, Confoederatio Helvetica) — единый бизнес-регион из-за общего языка и связанных регуляций." /> из-за rounding error в commission engine. Root cause: PostgreSQL `swiftpay.payouts` использовал `NUMERIC(10,2)` (banker's rounding); Snowflake `fct_driver_earnings` использовал `DECIMAL(38,4)` (truncation). Daily 1.2M trips × малая per-trip delta =$2.3M накопленных за месяц. Каждый источник отдельно выглядел «accurate»; никакой single-system DQ check не ловил расхождение.

Cross-system reconciliation — это независимое сравнение между двумя authoritative источниками одних и тех же бизнес-фактов. Критично потому что: каждая система имеет свой data path, свои type promotions, свои edge cases. Reconciliation ловит drift, который single-system controls пропускают; это primary защита против material misstatement.

BCBS 239 Principle 3 явно: «reconciliation to source». PCAOB AS 1105 ¶.10 — IPE — аудитор обязан тестировать accuracy и completeness либо контроли над accuracy и completeness. Reconciliation log = primary evidence для контролей accuracy/completeness.

Почему reconciliation имеет значение

OLTP (PostgreSQL, CockroachDB, Aurora) и OLAP (Snowflake, BigQuery, Databricks) живут в разных мирах:

• Типы данных — PostgreSQL NUMERIC(10,2) против Snowflake DECIMAL(38,4); rounding отличается.
• Часовые пояса — Aurora UTC против настройки session timezone Snowflake; cut-off периода имеет значение.
• Replication lag — CDC (Debezium / Fivetran / Airbyte) — sub-second до минут.
• Late-arriving data — события задерживаются, retried, re-emitted.
• Schema evolution — column добавлен в OLTP не propagated идентично в OLAP.
• Hot-fixes — инженер чинит value напрямую в OLTP UI (anti-pattern, но случается); OLAP не synced.

Без reconciliation эти drifts накапливаются молча. Один tiny rounding bug × миллионы записей = material misstatement.

Паттерны reconciliation

Pattern 1 — Row count match

Самый базовый. Per period (day / hour) — count записей в OLTP source против OLAP target.

-- OLTP (PostgreSQL)
SELECT COUNT(*) AS oltp_count
FROM swiftpay.payouts
WHERE created_at::date = current_date - interval '1 day';

-- OLAP (Snowflake)
SELECT COUNT(*) AS olap_count
FROM swiftride_prod.fct_driver_earnings
WHERE payout_date = current_date - interval '1 day';

-- Compare
oltp_count - olap_count -- должно быть ≤ толеранс (типично ±2 для replication lag, late events)

Threshold: абсолютная разница ≤2-5 records для real-time CDC; 0 для batch loads.

Pattern 2 — Sum match

Sum-of-values check для financial values.

-- OLTP
SELECT SUM(amount_paid_cents)::DECIMAL / 100 AS oltp_sum
FROM swiftpay.payouts
WHERE created_at::date = current_date - interval '1 day';

-- OLAP
SELECT SUM(gross_earnings_usd) AS olap_sum
FROM swiftride_prod.fct_driver_earnings
WHERE payout_date = current_date - interval '1 day';

-- Compare
ABS(oltp_sum - olap_sum) / olap_sum AS delta_pct -- должно быть ≤ 0.05%

Threshold: delta_pct ≤0.05% типично для SOX-grade reconciliation. Tighter (≤0.01%) для core financial CDE.

Pattern 3 — Checksum / hash-сравнение

Per-record hash-сравнение — ловит value changes без count change.

-- OLTP: per-record hash
SELECT md5(
  payout_id::TEXT || '|' ||
  driver_id::TEXT || '|' ||
  amount_paid_cents::TEXT || '|' ||
  status::TEXT
) AS row_hash, payout_id
FROM swiftpay.payouts;

-- OLAP: та же структура hash
SELECT md5(
  payout_id || '|' ||
  driver_id || '|' ||
  CAST(gross_earnings_usd * 100 AS INTEGER)::TEXT || '|' ||
  status
) AS row_hash, payout_id
FROM swiftride_prod.fct_driver_earnings;

-- Compare per payout_id
SELECT oltp.payout_id
FROM oltp_hashes oltp
FULL OUTER JOIN olap_hashes olap USING (payout_id)
WHERE oltp.row_hash != olap.row_hash
   OR oltp.row_hash IS NULL
   OR olap.row_hash IS NULL;

Threshold: 0 mismatches для real-time CDC reconciliation; залогированные exceptions для известных late-arriving сценариев.

Компромисс: вычислительно дорого — full table scan + hash + сравнение; медленно на больших таблицах. Решение — sample-based hash-сравнение (каждая N-ная запись) + targeted full reconciliation на конкретных date ranges квартально.

Pattern 4 — Distribution match

Statistical distribution match вместо exact value — полезно для aggregate accuracy.

-- Per BU × country, percentile breakdown
SELECT
  business_unit_id, country_code,
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY gross_earnings_usd) AS p50,
  PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY gross_earnings_usd) AS p95,
  AVG(gross_earnings_usd) AS mean
FROM fct_driver_earnings
GROUP BY 1, 2;

OLTP equivalent вычисляется тем же способом; различия логируются + investigated.

Threshold: percentile delta в пределах ±5%; distribution shift триггерит расследование (может быть legitimate business change, может быть data corruption).

Real-time против batch reconciliation

Real-time / streaming reconciliation

Per event — пока CDC streams события, отдельный consumer подписывается на тот же Kafka topic и отслеживает state параллельно; периодически сравнивает.

Плюсы:

• Ловит drift в минутах.
• Нет накапливающегося gap.

Минусы:

• Операционная сложность (отдельный consumer, управление state).
• Более высокая стоимость инфры.
• Late events / retries вызывают noise; нужен windowing.

Применение SwiftRide — real-time reconciliation баланса кошелька SwiftPay между Aurora и Snowflake; per-driver balance сравнивается каждые 60s.

Batch reconciliation

Per day / hour — full set of records сравнивается. Самый распространённый паттерн.

Плюсы:

• Более простая реализация (один Airflow DAG).
• Более низкая стоимость инфры.
• Естественно толерантна к late events (close-out window).

Минусы:

• Latency window 1h-24h между событием и detection.
• Restatement если material error пойман после downstream consumption.

Применение SwiftRide — daily reconciliation fct_driver_earnings (Snowflake) против swiftpay.payouts (Aurora); запускается T+1 06:00 UTC.

Гибрид

Real-time для material CDE с high event volume; batch для остального. Цель SwiftRide T+9M — real-time для SwiftPay (wallet), batch для прочих.

Late-arriving data — close-out reconciliation

Реальный мир: события приходят поздно. Trip completed 23:55 UTC; OLTP records 23:56; CDC лагает 5 мин; OLAP получает 00:01 — другой день per настройка timezone OLAP. Наивный reconciliation помечает как «missing event».

Close-out window

Reconciliation запускается T+1 06:00 UTC, но закрывает period в T+1 04:00 UTC — 4 hours buffer для late events. События, приходящие после 04:00 — назначаются к T+1 reconciliation cycle (T+2 06:00 UTC).

Late event log

События, приходящие после close-out window — логируются отдельно:

-- Late event detection
SELECT event_id, event_timestamp, arrival_timestamp,
       arrival_timestamp - event_timestamp AS latency
FROM swiftride_prod.events_log
WHERE arrival_timestamp::date != event_timestamp::date
  AND arrival_timestamp::date = current_date;

Latency больше 24h → Sev-3 расследование; возможная проблема upstream-системы.

Adjustment journal

Late events, влияющие на prior-period reconciliation — записываются в adjustment journal с reference к original period.

Реализация SwiftRide: таблица swiftride_prod.recon_adjustments — period, original_value, adjusted_value, late_event_count, reason, signed_by. Аудитор делает review adjustments квартально.

Reconciliation evidence — audit-grade

Per PCAOB AS 1105 ¶.10 — аудитор обязан тестировать accuracy и completeness либо напрямую, либо через controls. Reconciliation log — primary evidence vehicle.

Обязательные поля:

{
  "reconciliation_run_id": "RECON-20260815-001",
  "timestamp": "2026-08-15T06:00:00Z",
  "control_id": "CTL-CDE-SWR-003-002",
  "period": "2026-08-14",
  "source_system": "aurora.swiftpay.payouts",
  "target_system": "snowflake.fct_driver_earnings",
  "metric_type": "sum_match",
  "source_value": 12450231.45,
  "target_value": 12450239.12,
  "delta_abs": 7.67,
  "delta_pct": 0.0000616,
  "threshold_pct": 0.0005,
  "status": "pass",
  "late_events_count": 12,
  "adjustment_journal_ref": null,
  "signed_by": "arn:aws:iam::123456789:role/AirflowReconciliationRunner",
  "signature_alg": "HMAC-SHA256",
  "signature": "a4f9d2e8c1b3..."
}

Хранится в S3 object lock compliance mode; retention 7y; queryable через Snowflake audit.evidence_index.

Failed reconciliation:

{
  ...
  "status": "fail",
  "delta_pct": 0.00832,
  "incident_id": "INC-2026-08-15-001",
  "servicenow_ticket": "CHG0098765",
  "pagerduty_incident": "P-2026081501",
  "investigation_outcome_ref": "/runbooks/recon-fail-INC-2026-08-15-001.md"
}

Каждый failed reconciliation → investigation runbook + ремедиация + post-incident review. Все связаны через incident_id.

Reconciliation кошелька SwiftPay в SwiftRide

Material для SOX (revenue + payout accuracy), PSD2/PSD3 (wallet balance integrity), AMLR (transaction monitoring).

{{ audit_helper.compare_relations(
    a_relation=ref('fct_swiftpay_payouts'),
    b_relation=source('aurora_swiftpay', 'payouts'),
    primary_key='payout_id',
    columns=['driver_id', 'amount_paid_cents', 'status', 'created_at'],
    summarize=true
) }}

Anti-patterns

Reconciliation в output пайплайна, не в независимом пути

Паттерн: «reconciliation» запускается на Snowflake, сравнивая две Snowflake tables (обе derived из того же источника).

Почему плохо: если upstream-пайплайн испортил обе tables, reconciliation passes (обе wrong идентично). Не независимый.

Fix: reconciliation должен сравнивать против truly независимого источника — OLTP source-of-truth, внешний провайдер, банковский statement.

Sum match без count match

Паттерн: «sum_match passes — reconciliation OK».

Почему плохо: 100 records × $10 = 1000; ИЛИ 1000 records ×$1 = 1000. Sum matches, но records очень разные. Missing records маскируются.

Fix: всегда pair sum match с count match (или hash compare).

Close-out window не задокументирован

Паттерн: reconciliation запускается T+1; late events обрабатываются «как-то».

Почему плохо: аудитор спрашивает «что происходит, если event прибудет на 25 часов позже?» — нет ответа; design deficiency control.

Fix: задокументированный close-out window (например, 4h); задокументированная обработка late-event (adjustment journal); задокументированный escalation для events больше 24h.

Reconciliation evidence в Snowflake table

Паттерн: «log entries сохраняются в Snowflake — retention 7y сконфигурирован».

Почему плохо: Snowflake mutable; DBA может modify; не SOX-grade primary evidence.

Fix: S3 object lock compliance mode для primary evidence; Snowflake table только index pointers к S3 keys.

Reconciliation за Q4 показывает 100% pass — без каких-либо fails

Паттерн: «control performing perfectly — zero exceptions».

Почему плохо: zero exceptions подозрительно. Это значит либо threshold too lenient (реальные exceptions пропущены), либо underlying process так refined, что control rendered superfluous (signal of low risk).

Fix: tuning threshold — анализировать trailing baseline; tighten threshold если 0% fail; задокументировать rationale.

Резюме

• Cross-system reconciliation — независимое сравнение между двумя authoritative источниками одних и тех же бизнес-фактов. Критично потому что OLTP против OLAP — разные data paths, разные types, разные timezones, разный rounding.
• 4 паттерна: count match, sum match, hash compare, distribution match. Каждый ловит разный failure mode; комбинируем при material CDE.
• Real-time против batch: real-time для material CDE (SwiftPay wallet — tracking каждые 60s); batch для остального (ежедневно). Гибридный подход типичен.
• Late-arriving data — close-out window (4h типично); adjustment journal для late events; задокументированный escalation для latency больше 24h.
• Reconciliation evidence — structured JSON с timestamp, control_id, source/target values, delta, threshold, status, signed_by, signature; S3 object lock 7y. Failed reconciliation → investigation_outcome_ref + ServiceNow + post-incident review.
• BCBS 239 Principle 3 — «reconciliation to source». Инцидент SwiftPay 2024 ($2.3M DACH) — конкретное следствие отсутствия reconciliation.
• Инструменты: dbt audit_helper для batch (workhorse); custom Python + Kafka consumer для real-time; Anomalo для distribution drift; Snowflake audit.evidence_index для query layer.

В M5.7 разберём SoD — segregation of duties в data world; 4-actor model (initiate, execute, approve, monitor); compensating controls когда SoD непрактичен.