Project overview
The challenge
- The retailer struggled with fragmented data silos across Azure and disparate operational databases, causing 24H+ latency in pricing analytics.
- Legacy ingestion scripts were brittle, failing silently and requiring manual intervention for data repairs.
- No centralized schema governance existed, leading to 'data swamps' where analyst queries frequently broke due to upstream changes.
- Infrastructure scaling was manual, unable to cope with holiday season traffic spikes.
A major European retailer ran pricing and inventory analytics on two clouds that could not talk to each other: petabytes of operational data sat in Azure while the modern analytics stack lived in GCP. Legacy ETL tooling was fragile, batch-oriented, and took days to propagate pricing changes — an unacceptable lag in a market where competitors updated prices hourly. The mandate was to build a production-grade pipeline that made same-day analytics the baseline, not the exception.
The ingestion layer runs as Google Cloud Functions (Python), pulling sanitised datasets from Azure and landing them in GCS before Dataform SQL transformations normalise schemas into BigQuery. The pipeline handles TB-scale daily loads with automated data-quality assertions that abort and alert rather than silently corrupt downstream reports. Credentials are isolated in Secret Manager; the CI/CD pipeline pushes transformations through three environments before touching production.
GKE infrastructure runs self-healing pod automation — a pod failure triggers an automatic restart with state reconciliation, contributing to the 99.9% availability SLA. The result: the analytics team went from multi-day pricing-lag to same-day insight, and infrastructure incidents that previously required manual on-call intervention now resolve without a human page.
Technical architecture
Ingestion Layer: Python-based Cloud Functions triggered by storage events to process data from Azure inputs.
Storage & Transformation: BigQuery serves as the Data Lakehouse, with Dataform managing complex SQL transformation pipelines and dependency graphs.
Operational Sync: Metadata and operational state synchronized with PostgreSQL for real-time application access.
Self-Healing Ops: Automated functions continually monitor and reset GKE pods and infrastructure components to maintain high availability.
Key challenges & solutions
Cross-Cloud Data Consistency
Solved data reliability issues during transfer between Azure and GCP by implementing atomic write operations and checksum validations in Cloud Functions.
Managing Complex SQL Dependencies
Migrated ad-hoc script transformations to Dataform, creating a dependency-aware execution architecture that prevents downstream failures when upstream data is missing.
Infrastructure Reliability at Scale
Developed 'Reset GKE Pods' automation functions to detect and resolve stuck operational pods without human intervention, reducing on-call alerts by 60%.
Impact & results
Same-day analytics unlocked for a major European retailer — pricing changes previously taking 2–3 days to propagate now visible in BigQuery within hours
TB-scale daily ingestion with 99.9% availability — self-healing GKE pod automation resolves infrastructure incidents without manual on-call intervention
80% faster deployments: three-environment CI/CD (TEST → UAT → PRD) with approval gates replaced a manual, error-prone release process
500+ Dataform SQL transformations versioned in Git, standardising data schemas across the organisation and reducing duplicate transformation logic by 70%
Key features
- Hybrid Cloud Data Sync (Azure to GCP) via Cloud Functions
- Serverless Data Ingestion pipeline (Python)
- SQL-based transformations using Dataform (BigQuery)
- Self-healing infrastructure (GKE Pod Reset automation)
- Automated Data Quality Assertions/Testing
- Secure credential management via Secret Manager
- CI/CD with automated testing and deployment
- PostgreSQL operational database synchronization
- Scalable execution handling TB+ daily data loads
