Building a Centralized Data Platform for a National / GOV Pension Organization

Executive Snapshot

The Maldives Pension Administration Office relied on multiple databases, spreadsheets, and external systems to manage critical pension information. Data was fragmented across several environments, reporting processes were largely manual, and business teams often waited nearly a full day before critical information became available.

Transcloud partnered with the organization to build a centralized data warehouse on Google Cloud that unified data sources, automated ingestion, and enabled near real-time reporting.

Key Outcomes

• Consolidated 18 databases and 188 source tables
• Processed and modeled over 225 million records
• Reduced data availability delays from 24 hours to less than 10 minutes
• Optimized complex datasets into 30 analytics-ready tables
• Established a centralized source of truth for reporting
• Eliminated manual data consolidation processes

The Challenge

The Maldives Pension Administration Office managed pension information across multiple systems. Data existed within two legacy MySQL environments, a newer operational database, spreadsheets uploaded by business teams, and external APIs that provided additional information.

Although these systems supported day-to-day operations, they created significant challenges for reporting and analytics. Teams were required to manually gather information from different sources before reports could be generated. This process introduced delays, inconsistencies, and substantial operational overhead.

As the organization grew, the complexity increased. The environment consisted of 18 databases, 188 tables, and more than 225 million records. Reporting teams often waited up to 24 hours before updated information became available, limiting visibility into operations and slowing decision-making.

The organization needed more than a reporting solution. It needed a modern data foundation capable of bringing together multiple systems, supporting large-scale analytics, and delivering trusted information faster.

The Solution

Transcloud designed and implemented a cloud-native data warehouse on Google Cloud using BigQuery as the central analytics platform. The objective was not simply to move data, but to establish a scalable architecture that could continuously collect, organize, and prepare information for business reporting.

The first challenge was data ingestion. Since information originated from several different systems, multiple ingestion methods were required.

For the operational MySQL environments, Transcloud implemented Change Data Capture (CDC) using Datastream. Instead of moving entire datasets repeatedly, only incremental changes were captured and synchronized, allowing information to remain continuously updated.

Business teams regularly uploaded Excel files containing operational information. To eliminate manual processing, Transcloud developed Python-based ingestion services that automatically detected new files in Cloud Storage and loaded them directly into BigQuery.

Additional business information was available through external APIs. Node.js integration services were developed and scheduled to retrieve this information automatically, ensuring external data sources became part of the centralized analytics environment.

Building the Data Foundation

Collecting data was only part of the solution. The larger challenge was making the information usable.

Transcloud designed the warehouse using a layered architecture consisting of a Raw Zone and a Curated Zone.

The Raw Zone preserved source information exactly as it arrived, creating a reliable historical record and simplifying future troubleshooting. Once data entered the platform, transformation processes applied business rules, standardization, and cleansing operations within the Curated Zone.

The team then performed extensive data modeling to simplify reporting.

Information spread across 188 source tables was transformed into 30 optimized analytical tables using fact and dimension models. This approach reduced complexity, improved query performance, and created reusable datasets that could support multiple reporting requirements simultaneously.

Rather than repeatedly rebuilding reports from raw operational systems, business users could now access pre-modeled data specifically designed for analytics.

Results & Business Impact

The new platform fundamentally changed how the organization accessed and used data.

18 databases and 188 source tables were consolidated into a centralized analytics platform, eliminating fragmented reporting processes and reducing operational complexity.

Over 225 million records were ingested, organized, and modeled within BigQuery, providing a scalable foundation for future growth.

Data availability improved dramatically. Reporting delays that previously reached 24 hours were reduced to less than 10 minutes, allowing teams to work with near real-time information.

The implementation of fact and dimension models reduced reporting complexity by transforming hundreds of source tables into 30 analytics-ready datasets, improving performance and simplifying report development.

Automated ingestion pipelines removed manual data collection efforts from spreadsheets, databases, and external APIs, while Looker Studio provided business users with centralized dashboards and reporting capabilities.

Today, the Maldives Pension Administration Office operates on a modern data platform that delivers trusted information faster, improves reporting efficiency, and provides a scalable foundation for future analytical and business intelligence initiatives.

Why Transcloud

Transcloud was selected not only for data engineering expertise but for the ability to design a long-term analytics foundation.

Our team combined data integration, ETL development, data modeling, and cloud-native architecture to build a solution that addressed both immediate reporting challenges and future business requirements.

By focusing on automation, scalability, and data quality, Transcloud helped establish a modern platform that enables faster decisions, better visibility, and improved access to critical pension information across the organization.