Reconcile PSP transactions against records from Snowflake

Purpose

To ensure PSP-reported transactions align with internal records held in Snowflake by identifying:

• Transactions that fully reconcile
• Discrepancies requiring investigation
• Missing or delayed postings on either side

The workflow supports a consistent period-end close and ongoing operational monitoring (e.g., daily reconciliation).

Data sources

1. PSP transactional data: The web agent automatically pulls deposits, withdrawals, and fees from the PSP back-office (e.g., Checkout or Stripe).
2. Internal back-office transactional data: Corresponding internal ledger or operational records from Snowflake.
3. Unmatched transactions from the previous period: Prior unmatched transactional data carried forward for matching at a later date.

Key processing steps

1. Clean transactional data

• Parse and normalize date formats
• Standardize and clean description text
• Categorize each transaction (deposit, withdrawal, fee)
• Ensure sign conventions (+ / –) are consistent across PSP and internal datasets from Snowflake

2. Prepare reconciliation sets

Two unified transaction sets are prepared for matching:

• PSP reconciliation set: new PSP transactions plus unmatched PSP transactions from the previous period
• Internal reconciliation set: new Snowflake internal transactions plus unmatched internal transactions from the previous period

Unmatched transactions persist until resolved, providing full continuity across periods.

3. Reconciliation

• Match PSP transactions with internal records using transaction ID
• Include unmatched transactions carried forward from the prior period
• Flag items that match, mismatch, or require manual review

4. Exception identification and classification

Reconciliation results are classified into clear operational queues:

• Fully matched transactions: No action required.
• Matched with discrepancies: Transactions linked across systems but with differences (e.g. amount, fee, status).
• Unmatched PSP transactions: Transactions reported by the PSP but not recorded internally.
• Unmatched internal transactions: Transactions recorded internally but missing from PSP data.

5. Export and reporting

Fully matched transactions are exported back to Snowflake as the reconciliation output. Unmatched PSP and internal transactions are stored separately for review and used as input in future reconciliation workflows.

Outputs

• Matching statistics: Match rates, exception counts, and high-level health indicators.
• Discrepancy report: Matched transactions requiring investigation, grouped by issue type.
• Unmatched transactions: PSP side and internal side.
• Reconciliation report: A complete, auditable transaction-level record supporting reviews, escalations, and close.