From 7 Hours To 7 Mins: Lessons Learnt In Transforming Mission Critical Data Processing

Apex NZ provides investment administration services for dozens of investment product providers in Aotearoa. Upgrading to a modern data platform has supercharged their ability to handle complex tax reporting for 1 million investors. A painful process is now 50 times faster than a year ago, with room to scale another 10x, giving clients speed, accuracy and confidence.

Here’s what the company learnt…

#1 - ‘Fit for purpose’ isn’t static

A classic tale of ten years to achieve “overnight success”, Apex NZ has taken on more of the heavy lifting behind the scenes for more clients in Aotearoa.

“In recent years, we’ve grown at a phenomenal rate, placing higher demand on our in-house developed platform. We were straining to keep up with reporting and data extracts; and client demands for data that is both accurate and more real-time.

“What was fit for purpose in the company’s early days eventually became a bottleneck for future growth. Things that worked perfectly fine when we had 30,000 investors on the platform started to creak a bit when we reached 300,000. By the time we got to a million investors, things were really starting to hurt,” explains Joshua Arnold, Apex NZ’s Chief Product & Technology Officer.

Apex NZ knew it had outgrown its ‘heritage’ data architecture and needed a solution that was better suited to the massive scalability that can be achieved with cloud services.

#2 - Focus on where it hurts the most

One of the biggest ‘crunch’ processes for the whole business is the annual year-end tax reports. Tax reporting is complex, pulling in vast amounts of data and performing a massive number of complex calculations, each of which needs to be checked. Apex NZ produces reports on behalf of its more than 50 clients and this involves disseminating tax certificates to over a million investors.

That process relied on older SQL Server Reporting Services (SSRS) tooling, with the operational data store as the source. This meant the analytical needs like reporting and other data extracts were clashing with lots of daily operational processes and demands.

“To make this work in April-May of 2022 required a lot of manual work-arounds; cutting the batch size into small enough chunks to get the reports to run. The team worked long hours to get reports out in time. Doing this was tedious and risky, and we were particularly concerned about burning out key people. We got through the previous tax year end and everyone looked at each other and said, ‘let's not do that again’; it was just too painful,” explains Joshua.

Working backwards from where they wanted to be, the Apex NZ Product & Technology team had less than 12 months until the next reporting season to migrate a hugely complex process from SSRS to something more scalable and efficient.

#3 - Deliver a thin slice of value and focus on key risks early

To help speed up the mission, Apex NZ turned to Qrious (Spark Business Group’s AI and data innovation experts). Qrious was already a trusted partner, for advice and help to chart a way forward. Instead of sticking with the status quo, i.e. more of an existing technology or tweaking existing technology, Qrious proposed an entirely modern data platform using a Snowflake data warehouse solution, with DBT for modelling and transforms with FiveTran for ingesting data.

Tax reporting provided the perfect opportunity to demonstrate the immediate benefit of the new technology to the business. “The team built confidence by testing that it was possible to transition from T-SQL to ANSI-SQL. This could have been a blocker if we had been using a lot of proprietary T-SQL functionality. From testing a few more complex reports, we felt this risk was manageable. A partial rewrite was an opportunity to do some optimisation and improvements as part of the migration,” explains Joshua.

The other key risk to avoid was around Privacy and Security. The Apex NZ team prefers a “shift-left” approach to security, so needed to make sure the design and implementation would exceed the stringent needs of clients and investors. Investment Administration involves lots of highly sensitive data, so the team completed a data and privacy impact assessment up front to ensure that the Data Platform design, development and operation and improvement would address any security and privacy concerns.

#4 Iterate and improve; keep the focus on the outcomes

Over several intense months, Qrious worked closely with the Apex NZ’s in-house Data quad to procure and stand up the platform, including the work to ensure privacy and security was thought of and built in from day one, with modern Role-based Access Controls. Then they began the work of slicing up and translating the hugely complex SSRS tax report over to DBT Cloud, with Fivetran to extract and load the data into Snowflake. The finishing touch was PowerBI, which was used to analyse and validate the output, replacing a set of macros that the team used to use for this.

In time for the tax reporting season, Apex NZ was ready to effortlessly produce year-end tax reports and had a modern data platform foundation for their current and future data and analytics needs.

With the old system, it would take a staggering seven hours to run a tax year-end report for one of Apex NZ’s bigger clients. “And half the time it would fall over three quarters of the way through and we'd have to rerun it,” says Joshua. “It was a super painful process.”

On the modern data platform, Apex NZ can run an entire report for as many as three big clients and a handful of smaller clients in seven minutes. The new pace is a gamechanger.

Data protection and privacy has been addressed with the implementation of a robust security model, and the team are keen to start using Data Sharing with clients, to reduce the number of reports that are produced with copies of data sent daily when clients could just connect directly and read the data they need, when they need it.

The tax year-end report proved the power of the platform. Now Apex NZ has dozens more reports and other analytical needs lined up to migrate over to Snowflake/DBT.

“The mission now is to keep the momentum going, delivering value early and often for clients.Our clients have an insatiable appetite for data; they want more of it, more quickly, more real-time, while still being accurate and secure. Data is hugely valuable to their decision-making. The shift to a modern cloud-native Data Platform is a crucial building block for our future, and I’m excited to see what new capabilities we build for clients on top of this,” concludes Joshua.

© Scoop Media