The Client
An Indian logistics and delivery platform managing operational data across multiple systems needed a more unified foundation for reporting and analytics. With data distributed across PostgreSQL, MongoDB Atlas and ClickHouse, establishing consistent, reliable access to operational information required a modern data architecture that could support different ingestion frequencies, improve data quality and strengthen governance without disrupting existing reporting workflows.
The Challenge
-
Fragmented Operational Data
Operational data spread across three source systems made it necessary to establish a centralized, consistent foundation for reporting and analytics.
-
Data Quality and Consistency
Duplicate records and missing values in the in-scope data required systematic validation and cleansing to improve downstream data reliability.
-
Changing Source Schemas
Changes to source structures needed to be detected and managed to reduce the risk of disruption to data ingestion and downstream consumption.
-
Limited Operational Visibility
The platform needed better monitoring and timely alerts to help teams identify pipeline failures and follow up on issues.
-
Governance and Data Protection
Sensitive information required stronger protection through column-level masking and role-based access controls, alongside improved operational monitoring.
-
Different Data Freshness Requirements
The new platform needed to accommodate historical migration, near-real-time change-data capture and scheduled ingestion while keeping existing Metabase dashboards operational.
The Impact
- 3 source systems consolidated into a centralized BigQuery warehouse.
- 84 source tables and collections migrated into the modern data environment.
- 8 existing reports and dashboards repointed without redesigning their underlying business logic.
The Solution
Hoonartek consolidated operational data from PostgreSQL, MongoDB Atlas and ClickHouse into Google BigQuery using a Bronze–Silver–Gold architecture. Historical data was migrated from all three sources, while ongoing change-data capture and scheduled loads supported PostgreSQL and MongoDB.
Existing PySpark cleansing logic was moved into the BigQuery Silver layer. Null and duplicate checks, source-to-target reconciliation and curated Gold-layer objects established a structured approach to improving data consistency and preparing data for downstream reporting.
To preserve continuity for business users, eight existing Metabase reports and dashboards were repointed to BigQuery without redesigning the dashboards or changing their business logic.
Governance and operational controls were embedded into the solution. These included column-level masking for four identified sensitive columns, role-based access control, Cloud Logging, Cloud Monitoring and schema-change alerts. Automated pipeline-failure notifications through Slack and email improved issue visibility. Daily exports to Google Cloud Storage in Apache Iceberg format supported the platform’s data export requirements.
Key Benefits
-
A Unified Analytics Foundation:
Operational data from three source systems brought together in one BigQuery warehouse.
-
Improved Data Quality
Cleansing, null and duplicate checks, and source-to-target reconciliation introduced into the data pipeline.
-
Stronger Governance:
Sensitive data masking, role-based access controls and monitoring integrated into the platform.
-
Continuity for Reporting Teams:
Existing dashboards transitioned to BigQuery without a redesign.
-
Support for Multiple Ingestion Frequencies
Historical loads, near-real-time CDC, micro-batches and scheduled ingestion supported within the new architecture.
-
Better Operational Visibility:
Pipeline monitoring, schema-change alerts and automated notifications enabled more timely issue awareness and follow-up.