Tracking payments at scale

This article was written by Ammar Chalifah, Senior Finance Data Engineer at Bolt.

TL;DR

Managing cash flow at scale is complex, especially when dealing with multiple payment service providers (PSPs), each with different merchant payout schedules, fees, and reporting formats. 

At Bolt, we've built a scalable financial data engineering system to track and reconcile cash movements efficiently. This system ingests PSP data, standardises it, performs reconciliation against internal records and bank statements, and applies accounting treatments to discrepancies. 

Introduction

When you take a ride with Bolt and pay through the app, the money you spend doesn’t instantly land in Bolt’s bank account. Instead, the money goes on a long and complicated journey across a network of financial institutions. From the customer’s issuing bank, flowing through the card scheme network, it lands into acquiring bank accounts and in Payment Service Providers’ (PSPs) merchant account balance, and finally reaches the merchant bank accounts in periodic batches. 

Usually, each component in this array of institutions takes a cut from the card scheme, banks, and payment service providers. The money is finally settled into Bolt’s bank account, and most of the time, it is already net of all the fees and deductions. PSP issues invoices and reports to disclose the details of cuts, along with the transactions that make up that one particular settlement.

As a global company operating in diverse markets, Bolt integrates with multiple PSPs, each with different currencies, time zones, settlement schedules, billing structures, reporting formats, and potential remittance delays. This complexity makes financial tracking and reconciliation challenging but crucial.

Why is this important?

Managing cash flow is fundamental to the company’s financial health, and companies need to measure it correctly to manage it well. Ensuring that these balances are accurately tracked and reconciled is essential for several reasons:

1. Financial сontrol: discrepancies between expected and actual balances could indicate missing funds, integration errors, or fraud.
2. Liquidity management: predicting cash inflows is crucial for managing working capital and ensuring smooth business operations.
3. Cost transparency: PSPs often apply fees, deductions, and other adjustments that may not always be explicitly detailed in invoices. Accurately categorising these transactions is essential for proper accounting treatment.
4. Cost allocation: the fee comes from PSP as a bulk amount. At the same time, a company needs to split these fees across different dimensions (regions, business units, etc.) to accurately allocate the cost into different categories, affecting each unit’s profitability.
5. Technical integrity: differences in reported amounts may signal data precision issues, technical failures, or missing transactions.

To address these challenges, Bolt has built a scalable data processing and reconciliation system that ensures financial accuracy while providing timely monitoring and control. This blog details the technical architecture behind this system.

Technical solution

Data ingestion

To enable accurate reconciliation, Bolt first needs to ingest data from multiple PSPs. Each PSP has different report offerings, but most of them have something called a “transaction reconciliation report” (it may be called differently by different PSP). This report contains all individual transactions that make up one settlement batch, all fees associated with that batch, and the information related to the total payout amount and adjustments. This batch is often attached to one merchant account, and the merchant account has a contractual relationship with a particular legal entity of the client. 

This report is the basis of reconciliation and tracking payments at scale, and PSPs deliver such reports either by SFTP or API methods. Some newer and smaller PSPs usually don’t have this functionality and instead offer to send such data manually in emails, but we won’t cover that case in this article. Instead, we’ll focus on the automated way of fetching this data.

• SFTP-based file transfers: some providers upload reports as a physical file to an SFTP server, commonly with CSV format (some providers have their own unique file formats).
• API-based data retrieval: other providers offer REST APIs for building and fetching settlement and transaction details.

At Bolt, both integration methods rely on batch jobs running on AWS Batch Job, scheduled by Apache Airflow. Each invocation of the batch job triggers the spin-up of a machine built using a Docker image stored in AWS ECR. This image already contains the code necessary to run the data ingestion. All ingested data and metadata related to this function are stored externally on cloud storage (AWS S3 in our case).

SFTP-based integration

For SFTP-based integration, the batch job loads metadata from S3 (internally, we call this metadata a crawler metastore) that contains the information of all files that the job has already processed in the past. Then, the job establishes a secure SFTP connection over a proxy to the PSP’s SFTP server. For every invocation, the job scans the content of the SFTP server to find new or modified files (by using the crawler metastore as a reference) and downloads all new or modified files to the ephemeral disk. The files downloaded from the SFTP server are downloaded as is, without any transformation or cleanups.

Then, the job stores these raw files in S3 and updates the crawler metastore. To avoid memory or storage problems in the batch job, the integration is being done in a way that file loading (from raw file download from SFTP server to raw file upload to S3) is done serially, followed by the cleanup of the files from the local disk. This setup enables a scalable workload, keeping the job size manageable. It uses external storage to meet the growing demand for storage without overloading the instance.

API-based integration

API-based integration is done in a very similar way, with exactly the same infrastructure. The only difference is that the instance makes a request to the PSP’s API endpoint instead of scanning the directory's file structure and loading the resulting report to S3 as-is.

The philosophy that guides the data integration is that the job never does any cleaning or transformation to keep all raw files intact. We allow the job to load duplicate data as all deduplication will happen at a later, downstream stage.

Data parsing to Delta format

Having raw files is great, but it's not enough. The files need to be parsed and then stored in a format accessible by query engines. Bolt’s Data Platform has chosen Delta as the columnar data storage format. Delta is a columnar format suitable for big data analytics, and it supports state-of-the-art technology for better data management, such as ACID-like transactions, data versioning, and schema evolution.

Parsing to Delta format is done using the same batch job. The instance downloads the files that aren’t parsed yet from S3 (using the crawler meta store as the reference), parses the data and holds it as an in-memory Dataframe, and then stores this data to S3 as Delta files. To make the data instantly queryable, the job also publishes this new data slice into Glue to a new partition in a table.

The only purpose of this step is to make the data accessible through query engines, so the job also doesn’t care about transformations or cleanups. One common problem in this step is data type mismatch, so as a mitigation, the parsing will force all columns to be stored as a string. All data type casting will be done in the data modelling stage.

Data modelling

As a result of the data integration step, now Bolt has the data available in the Data Lake, and analysts can already interact with the data with SQL. However, the data is still not ready to be served to the end users, as it still suffers from some data quality and experience issues: (a) duplicates, (b) wrong data types, and (c) varying schema for each data source. 

Cleanups

The first step in data modelling is cleaning up the data by removing duplicates, casting columns into the correct data types, and renaming columns. Typically, this step is implemented as a model defined in dbt, and executed on a distributed computing platform, Apache Spark.

The result of this step is a table queryable through query engines that already has deduplicated data, correct data types, and clean column names. This cleaned-up table is the input for the next step: unifying the raw data to align with a standardised schema.

Data modelling

Each PSP has a unique reporting structure, with different naming conventions, data granularity, and terminology. To reduce complexity in downstream processes, we standardise the data through a transformation pipeline built using dbt running on Apache Spark. 

The key transformations include:

1. Denormalisation: flattening hierarchical structures stored across different reports into standardised key staging tables.
2. Terminology mapping: aligning PSP-specific terms to Bolt’s internal terminology. For example, internally we refer to payment captures as “capture”, while PSP might use different terms such as “Payment”, “PaymentCapture”, or “CaptureSettled”. This step maps all PSPs’ terminologies to align with internal terminologies.
3. Legal entity mapping: ensuring that each PSP’s merchant account is attributed to the correct legal entity, as per contractual agreements.
4. Transactions breakdown: separating transactions into their most granular data points. For example, some PSPs provide one row that encompasses gross amount, interchange fee, scheme fee, and other fees. We want to split each transaction into separate rows, each row representing different financial actions.
5. Validation: implementing checks to ensure correctness, e.g., verifying that:
   Payout Amount = Gross Volume - Deductions

These transformations convert PSP raw data with different formats into data models with the exact same schema and identical interpretation. It allows all downstream processing, like reconciliation or fee tracking, to have the exact same approach, scalable for all PSPs. 

Reconciliation process

Once the data is modelled, Bolt performs reconciliation against internal records and bank statements. This process is crucial for identifying missing or incorrect transactions. 

The two main reconciliation steps are:

1. Transactional reconciliation: matching PSP-reported transactions with Bolt’s internal payment records.
2. Settlement reconciliation: ensuring PSP settlements align with bank deposits.

Technically, this reconciliation is implemented as a series of data models, written in SQL. The relationship between data models is defined in dbt, and the actual data processing happens in a distributed computing environment using Apache Spark. The outputs of this reconciliation are re-materialised as separate tables.

Reconciliation monitoring

To track reconciliation accuracy and detect issues, Bolt has implemented several monitoring mechanisms:

1. BI dashboard: a visualisation tool that tracks reconciliation rates over time.
2. Automated alerts: a scheduled job that triggers notifications to the operations team if reconciliation rates fall below a defined threshold.

A drop in reconciliation rates could indicate technical issues, incorrect reconciliation logic, or operational problems that require investigation. 

Accounting treatment

The final step in the process is accounting for extra components, such as non-fee deductions and reconciliation discrepancies. The system categorises reconciliation results into:

• Non-fee adjustments: unexpected deductions that impact revenue (e.g., one-off adjustments, reserve adjustments).
• Reconciliation differences: small variances that need to be adjusted as P&L items usually come from decimal precision differences.
• Clearing entries: transactions that involve movement across legal entities or currencies.
• Fee allocation: split the bulk amount from the invoice into each business line and region.

These adjustments ensure that Bolt’s financial books accurately reflect reality and comply with accounting standards.

Afterthoughts

Building a scalable and automated payment provider reconciliation system is critical for Bolt’s financial accuracy, cash flow management, and operational efficiency. 

The system is built by leveraging a modern data stack and improved by continuous feedback from all stakeholders, particularly Financial Controllers, Payment Engineering, and Payment Operations. This solution strengthens Bolt’s financial oversight and helps engineering teams detect and address potential payment integration issues, ultimately improving the overall payment infrastructure.

Join us!

Bolt is a place where you can grow professionally at lightning speed and create a real impact on a global scale.

Take a look at our careers page and browse through hundreds of open roles, each offering an exciting opportunity to contribute to making cities for people, not cars.

If you’re ready to work in an exciting, dynamic, fast-paced industry and are not afraid of a challenge, we’re waiting for you!