Building trade level reconciliation tool in 6 weeks
At ING WB Tech Financial Markets we deal with many interesting problems. Recently we were asked to build a trade reconciliation tool that would process two large datasets and identify differences between them. We only had 6 weeks to build it and that was definitely a big challenge!
What is trade reconciliation?
Reconciliation, a term originating from latin reconciliare (re- “back; again” + conciliō “unite, connect”), is an accounting process using two data sets to ensure that the financial figures are correct.
To understand it let’s imagine a trader performing a transaction on a financial instrument, for example buying a Dutch government bond. After closing the deal he books the details in the trade capture system. From there the data is pushed to a position keeping (PK) system for risk purposes as well as a financial ledger for accounting purposes. Both systems need to be aware that we have to send a cash transfer to the selling party and register a bond as an asset in the trading book. The goal of reconciliation is to make sure that the cash amount registered in the PK system is the same as the amount that was actually transferred. Also, we need to verify that we have booked the correct attributes of the bond (nominal, maturity date etc.) in both systems.
In a big institution like ING, with multiple trading locations around the globe, millions of trades in a portfolio and more than 100 possible attributes for each trade type, the process can be quite complex. Luckily, we already had all the data in our data hub. We also had the knowledge how to map attributes between the systems. We just had to find the right tools.
Proof of concept
We started by visiting the department that was supposed to deliver the report. They already started building a prototype using relational database, but they faced capacity limitations. Following this path would also mean being locked in a rigid structure, possibly for years. Adapting the system to changing requirements could become troublesome in the future.
Other solutions on the table suggested building an OLAP cube. We already had experience with such systems and they were great for use cases in which users had to perform real-time analysis on big datasets. Our use case was different — we had to generate high granularity data in batches and OLAP cubes were just not designed for that. Additionally, the costs of keeping all the data in memory was high and the development could potentially take a long time.
This sounded like a big data problem! We had to deal with high Volume of data (the whole global ING trades portfolio in scope) and high Variety (data coming from various systems in various formats, evolving over time). The Velocity was not an important factor, as the report needed to be generated only once per quarter. Nevertheless, we decided to look for solutions in the big data domain.
We started by downloading a virtual image of HDP (Hortonworks/Cloudera Data Platform) and in the first week we were able to develop a first proof of concept of the solution.
We decided to choose Apache Hive as an underlying technology because it perfectly fit our requirements. Hive is an open source project in the Hadoop ecosystem. It was initially developed at Facebook to facilitate processing big amounts of user data. Hive can be seen as an abstraction layer on top structured data (such as CSV files), giving developers and analysts the possibility to run queries through SQL-like interface.
We did not have a common unique identifier that would allow us to link the tables and compare the trades. The first challenge was to make sure that we can construct such key to perform joins between the datasets. That would give us confidence that the whole system might work.
After creating the Hive tables and loading source CSV files it took some trial and error to get it right. We constructed the key from multiple columns available in the source files. We had to perform mappings, sometimes using external lookup tables, but in the end the approach worked. Below you can see the fragments of the queries responsible for generating the keys.
Decision, planning, infrastructure
The second week was spent on discussions with the stakeholders, planning and setting up the cluster. We had to convince management and architects to our solution and that’s not always an easy task! We all know that once we introduce a new system to an IT landscape it’s likely to stay there for a long time. We had to prove that the solution is maintainable, secure and that it fits into long-term architectural vision.
Luckily, big data technologies are gaining popularity in many different departments of our bank. Management identified this trend and decided to facilitate the life of engineers by providing out-of-the box Hadoop infrastructure, based on Cloudera Data Platform solution. With just a few clicks we can request a cluster in ING’s private cloud, which is complaint with internal policies and comes with many enhancements, such as integration with corporate user access management, security event monitoring and extensive documentation.
Implementation
With the blessing from architects and special support from Big Data Cloud team we could start the real implementation. The team consisted of two developers, two analysts and two operations engineers, working from different locations (Amsterdam, Bucharest). Many other people were also involved.
In addition to Hive we decided to use two other open source systems: Apache Airflow for maintaining the data flows and transformations and Apache Superset, serving as an UI and allowing us to run ad-hoc queries.
With Airflow we could orchestrate the individual tasks into DAGs (directed acyclic graphs), which can be also seen as workflows. Looking at the tasks from the user perspective we divided them into three major parts:
- First one allowing us to load all the data to Hive cluster.
- Second one that would generate the reconciliation reports.
- Third one, producing the final report including the adjustments.
We could trigger execution of the whole workflow, or selected steps from the command line, which was very helpful during the testing phase. It allowed us to have frequent feedback loops with our analysts. While implementing the functionality we could make changes in the business logic, deploy the code to the cluster, start the workflow and within a few minutes we had the results ready.
Infrastructure
All of that would not be possible without the infrastructure provided by ING. We already had a central point for storing the data (Data Hub) with secure data flows from the sourcing systems. The cluster was deployed in ING Private Cloud (IPC) and using the NFS share we could just plug in and use data.
The 8-node cluster came with many pre-installed and pre-configured services. On top of them we installed Hive, Airflow and Superset and adjusted the configuration to make sure that the components can communicate with each other. Setting up security, user access management and other IT Risk related controls was a big struggle but in the end we managed to put all the pieces of the puzzle together.
Goal achieved
With a clear goal and good motivation we were able to progress quickly, setting up infrastructure, building required data transformation queries and orchestrating the software components.
In the end it all worked out well. Within 6 weeks we were able to build the fully functional solution that would process all the data and generate reports with reconciliation breaks. The breaks were then analysed by the market risk officers, who contacted other departments to clarify the inconsistencies on both sides of the process. In the end that allowed us to generate final report on time.
We learned a lot during this journey, but of course the project did not stop after six weeks. There is also a lot of work required to promote the system to fully automated production environment. Also, new requirements come in and we plan to extend the system with new functionality.
