POM: A Daily Refresh in Minutes, Not Days

POM helps organisations get paid. Its platform creates and sends invoices, offers payment links, QR codes and payment plans, and runs the collection process, all under the client's own brand. More than 150 clients use it, from housing associations and insurers to energy suppliers, telecom providers, water companies and government bodies.

That adds up. According to POM, the platform sends more than 8.5 million payment requests a month, and every request, reminder, payment and plan becomes a row in a database. Month after month the database grows, by hundreds of gigabytes.

Our work started with a move. POM's database ran on premise, and we helped it make the transition to the AWS cloud. Around it we designed and delivered a modern data platform on Azure Databricks, integrating the different source systems into one central platform. That made the data more reliable, let it scale with POM's growth, and gave reporting a single place to draw from.

The simplest way to keep a data platform current is to copy everything again every night. With a small database that works. With hundreds of gigabytes it does not: a full reload takes most of a day, so a daily refresh is impossible, and every month of growth makes it worse. Move the sliders to see how quickly the full load runs out of night.

So we built a loading system that does as little as possible, in three steps. Where the source can tell which rows changed since the last load, the platform imports exactly those rows and nothing else.

Where that is unknown, it does not fall back to copying everything. It compares a fingerprint of each block of rows with the one it stored last time, and reads only the blocks that differ. And for calculations that need their data right now, it works on demand: before a calculation runs, it brings only the rows that calculation needs up to date. Try each strategy on the table below.

The result was a full refresh brought back from a whole day to several minutes. That made daily refreshes possible again, however much the database grows, and it gave the business reporting it could rely on every morning.

• The transition of POM's database from an on-premise server to the AWS cloud.
• A modern data platform on Azure Databricks, designed and delivered end to end.
• Multiple source systems integrated into one central platform, more reliable and ready to scale.
• A loading system that imports only changed rows, compares fingerprints where changes are unknown, and updates rows on demand for calculations.
• Better reporting, for decisions made on current data.