The Challenge

Building a data integration system to launch the app in time.

Our client needed to launch their replacement app before the old app was shut down by their third-party vendor. A gap in digital service would have severely impacted client experience and created regulatory challenges. The data integration element of the project had fallen significantly behind schedule, leaving only three months for us to recover the plan and deliver a solution to avoid delaying the app launch and ultimately preventing existing clients from losing their digital access. The CRM and new app have entirely different data models, so we needed to build a bespoke method of repeatedly transforming millions of records from the source system and applying a complex logic workflow to load each record into the new app’s database. We also needed to design and build a stable, automated platform to run the synchronization process at least once a day. Before the app launch, we needed to migrate 18 months of historic transaction data for all 90,000 accounts. After launch, our solution needed to keep this data sychronized each day, be able to onboard new clients, and manage ongoing client permissions changes.

The Solution

Building an interim solution before focusing on long-term automation.

To meet the challenging deadline, we took over responsibility for building an initial on-premise solution to synchronize enough data to enable the app launch before working on an enduring, cloud-based automated solution. For the interim solution, we took daily extracts of millions of rows of data and applied a three-stage transformation process using an on-premise SQL database. We then wrote complex logic workflow in C# which used APIs to load data into the app’s database. We packaged the entire process into a Windows Service so it could be easily applied each day. Over 30 days, we gradually migrated 40k clients and 18 months of historic data to meet the app launch deadline. After launch, we switched to delivering a robust, fully automated, Azure-native solution. We recreated the data transformation process leveraging Azure Data Factory and migrated to using Azure Serverless SQL database as the intermediary. We rehosted and refactored the C# code as a service in Azure, making it more reusable and easier to maintain. We set up process health monitoring and guardrails so the system could be left to run automatically. Throughout, we enabled the synchronization of further data fields to support the app’s roadmap of new features. Once ready, we conducted a seamless switchover from the interim to automated solution before handing the entire system over to the client to maintain going forward.

The Results

Successful app launch and a low-maintenance, automated solution.

We succeeded in migrating all 40k clients and their data in time to enable the client to launch the app within their fixed deadline. The seamless transition between digital platforms maintained the expected client experience and ensured no high-net-worth clients were lost. Since launch, we have built additional synchronization features to support new features of the app and onboarded a further 1,700 clients.  We have also provided the client with a low-maintenance and automated data integration system which they are now further updating as the app’s features grow. The solution seamlessly maps the two systems to manage client access permissions, account ownership, and linking. The system is:

• Fully automated: The end-to-end process operates independently in the cloud with a dashboard available to monitor new client onboarding or data updates.

• Reliable and safe: We integrated guardrails and alerting to prevent errors and help troubleshoot problems that might impact client experience.

• Easily maintained: We ensure a seamless transition into live operation by the client, including a comprehensive library of technical documentation and one-on-one training. 

• Easily updated: We set up a full CI/CD pipeline and handed over a streamlined development process to the in-house engineering team.