A pragmatic Google Cloud approach to Value for Money

In our previous article, Value for Money is the first query, not the whole database, we established that the regulatory push for workplace pension transparency is fundamentally three distinct data problems wearing one name – investment performance, costs and charges, and service quality. Acknowledging this reality and carrying out a data readiness audit are the critical first steps for any digital pensions leader.

However, acknowledging the shape of the problem does not write code or deploy infrastructure. The immediate hurdle is execution amidst shifting goalposts. The joint DWP/FCA consultation paper (CP26/25) has proposed the phased rollout framework: larger schemes, including Master Trusts, large Single-Employer Trusts, and open multi-employer contract arrangements, must submit full assessments in 2028 (covering a shortened initial 6-month data collection window from 1 July to 31 December 2027). Full regulatory enforcement and transfer consequences take effect in 2029.

The clock is ticking. Pension providers need an architecture that bridges the gap between legacy operational systems and strict compliance mandates, without building a high-maintenance web of custom ETL scripts. Here is how we approach the Value for Money (VfM) data challenge pragmatically on Google Cloud, prioritising a vertical slice implementation that delivers a working MVP at pace.

Let’s dive in.

The messy centre

When architecting a solution for complex integrations, we frequently advocate for a ‘Data Onion’ model. The outer rings of this onion represent a curated, standardised logical data model designed specifically for regulatory VfM reporting. But the centre of that onion, your internal operational environment, is rarely clean.

The ‘messy centre’ extends far beyond internal SQL servers and finance team spreadsheets. The real engineering headache is the external data supply chain. Modern pension providers face the daunting task of continuously ingesting, cleaning, and normalising fragmented, asynchronous batch extracts from legacy Third-Party Administrators (TPAs) and external asset managers.

These external data feeds often arrive in inconsistent formats and on irregular schedules, creating integration challenges. Building bespoke, brittle integration pipelines for every TPA is a fast track to technical debt. Instead, your cloud architecture must decouple the ingestion of these third-party extracts from your core regulatory calculation engine.

Batch processing over streaming

Faced with an asynchronous, multi-party ingestion challenge, the engineering reflex is often to reach for tools like Apache Kafka to build a real-time streaming architecture. While stream processing is technologically compelling, it is an expensive overkill for VfM reporting.

VfM reporting is an annual regulatory requirement, complemented by internal monthly monitoring. By adopting a scheduled batching process, we reduce compute overhead and eliminate the operational friction of untangling state inconsistencies when a third-party administrator inevitably drops a corrupted file.

Executing the Vertical Slice on Google Cloud

Rather than an all-or-nothing legacy overhaul, we advocate for an iterative vertical slice approach. We start with accessible data sources to prove the end-to-end pipeline, then incrementally add complex legacy sets. By combining a “Data Onion” model with a Medallion Architecture, we can logically separate the three pillars of VfM (Investment Performance, Costs & Charges, and Quality of Service) as they move from raw data to assessable metrics.

Here is the Google Cloud toolchain and architectural flow that executes this strategy:

Orchestration & Ingestion – The flow begins with Cloud Composer triggering data orchestration. It prompts Cloud Functions to ingest disparate data sources (databases, FTPs holding fund values, units held, account valuations, and charges) and process that data into our initial landing zone, Cloud Storage. Cloud Dataflow picks up the batch extracts from Cloud Storage, performing the heavy-lifting ETL (Extract, Transform, Load) tasks to prepare the data for the data warehouse.

The Medallion Architecture (BigQuery)

The Bronze, Silver and Gold layer transformations are done entirely within BigQuery.

Ingest (The Bronze Layer) Raw data is staged here, categorised by pillar:

• Investment Performance: Ingesting daily fund prices, fund values and units held, and future return assumptions.
• Costs & Charges: Pulling in charge schedules, invoices and fees paid, broker charges, and market prices at trade time.
• Quality of Service: Capturing case handling events, complaints, member journeys, and UK bank holidays.

Arrange (The Silver Layer)

Data is cleansed, conformed and joined by common dimensions: how_many_members, which_cohort, which_scheme, and which_day.

• Investment Performance: Mapping value for every fund/every day, returns for each cohort, and versioned Capital Market Assumptions (CMAs).
• Costs & Charges: Calculating charges in force with a daily breakdown, cost of trades, and asset allocations.
• Quality of Service: Tracking every case end-to-end, measuring clock pauses and stops to capture true resolution times.

Score (The Gold Layer) 

The business-level aggregates required by the FCA’s specific arithmetic are calculated here, generating final metrics:

• Investment Performance: Geometric returns over 1, 3, 5, and 10 years, expected growth (Forward-Looking Metrics), and Annualised Standard Deviation to measure investment volatility.
• Costs & Charges: Maximum, minimum, and median costs across employer cohorts, ongoing charges, and trading costs.
• Quality of Service: Mean end-to-end transaction times, percentage of requests completed within regulatory day-ranges, and data accuracy.

Data Governance – Running natively alongside the BigQuery Medallion architecture, Dataplex ensures strict data governance, quality control, and unified security across all layers.

The Consumed Assessment Layer – The outermost ring connects Data Metrics Dashboards in Looker directly to the BigQuery Gold layer, allowing technical and governance teams to compare internal measurements against the commercial market comparator group.

Time-series, snapshotting, and 6-year retention

One of the most complex engineering challenges of VfM reporting is handling time-series, snapshotting, and point-in-time data access.

Instead of duplicating gigabytes of data every reporting run or building complex Slowly Changing Dimension (SCD Type 2) pipelines, BigQuery Table Snapshots (or BigLake with Apache Iceberg formats) handle point-in-time state capture natively. They preserve historical state without duplicating base data, charging storage only for differential changes as tables evolve.

This solves the FCA’s 6-year audit retention requirement without paying for duplicate storage. If data issues need to be corrected months later, we can update historical facts without destroying the record of exactly what was known at the time of submission.

Forecasting and event-driven telemetry

While historical batch data populates much of the Gold layer naturally, two specific CP26/25 requirements demand specialised cloud capabilities before they can be scored:

1. Forward-Looking Metrics (FLMs) via Vertex AI or BigQuery ML Schemes must disclose 10-year forward-looking return and risk projections. Because providers must define their own CMAs and retain an auditable record for at least 6 years, this is a statistical modelling challenge rather than simple SQL arithmetic. We leverage Vertex AI to build and version statistical projection models directly against Gold-tier BigQuery data. Vertex AI Model Registry provides an auditable, version-controlled lineage of your assumptions across asset classes, ensuring compliance when regulators inspect your methodologies.

2. Capturing event-triggered Service Quality Service Quality metrics, such as beneficiary nominations and complaint resolution, rely on operational event telemetry rather than periodic ledger balances. Because extracting this from legacy core admin platforms is notoriously difficult, the Google Cloud architecture captures user interactions directly at the digital product layer. These events are buffered via Pub/Sub and ingested directly into BigQuery Bronze storage, bypassing legacy databases entirely to accurately measure real member service experience.

Tracking RAGG tier drift

All of this data pipeline engineering converges at the outermost ring of the onion: the consumption layer. CP26/25 enforces a four-tier RAGG rating framework:

• Dark Green: Clearly outperforming the market consistently.
• Light Green: Delivering value, but with room for minor improvement.
• Amber: Not delivering value, but capable of being improved within 3 years.
• Red: Not delivering value, requiring member transfer or scheme restructuring.

By connecting a business intelligence tool like Looker directly to the BigQuery Gold aggregates, technical and governance teams gain an operational early-warning dashboard. Rather than waiting for the annual compliance reporting deadline, Looker allows IGCs and scheme trustees to compare their internal measurements against other providers in the commercial market, tracking monthly metric drift. This catches a fund sliding from Dark Green towards Light Green or Amber long before it triggers formal regulatory intervention.

The strategic advantage

Building a VfM solution on Google Cloud is not an exercise in regulatory box-checking. By decoupling messy legacy data sources from a standardised cloud calculation layer, you construct a resilient, modern data infrastructure.

The pension providers that succeed beyond the 2028 deadline will be those that leverage this regulatory mandate to establish a clean, unified member data platform, turning a compliance burden into a core digital capability.