Key results
data refresh success rate post optimization with automated retries and monitoring
faster query performance using BigQuery materialized views, clustering, and table partitioning
standardized across business functions to ensure consistent, trusted decision-making
saved by automating Excel-based reporting through Tableau dashboards
About the Client
A mid-sized FMCG company offering a wide range of packaged food products, including ready-to-eat snacks, condiments, and beverages, sold through retail chains, distributors, eCommerce platforms, and D2C channels.
Key Challenges
Fragmented data across POS systems, ERP, WMS, eCommerce, and marketing platforms
Heavy reliance on spreadsheets for reporting, slowing down decisions and increasing errors
Lack of centralized, real-time access to product performance, inventory health, and marketing ROI
Difficulty in aligning cross-functional teams around a single source of truth
Our Approach
- Built a centralized data warehouse using Google BigQuery to bring together all business-critical data
- Designed modular ETL pipelines to enable rapid, scalable ingestion from diverse internal and external systems
- Created role-based data marts and semantic layers for Sales, Marketing, Inventory, and Fulfillment teams
- Ensured data quality and availability with rigorous validation, schema checks, and refresh monitoring
Implemented Solution
Cloud-Native Data Warehouse on GCP
- Google Cloud Storage used for staging and archival of raw and transformed data
- BigQuery configured for structured, scalable analytics with cost-efficient auto-scaling and performance tuning
- Access control set up by business function using Google IAM and dataset-level permissions
Streamlined Data Ingestion and ETL Pipelines
- DataChannel used for third-party data from Shopify, Amazon Seller Central, Meta Ads, and Google Ads
- Python ETL jobs built on Cloud Functions and scheduled using Cloud Composer for ERP, WMS, and distributor data
- Schema enforcement, deduplication, and automated error alerts ensured reliability and data trust
High-Performance Query Architecture
- Materialized views and incremental loading strategies implemented for heavy reporting tables
- Time-based partitioning and logical clustering improved dashboard responsiveness and query cost efficiency
- Near real-time pipeline execution for priority datasets enabled faster operational decisions
BI Enablement with Tableau Dashboards
- Sales & Channel - Sales performance by region, campaign ROI, and SKU-level growth
- Inventory Health - Aging inventory, low-fill-rate alerts, DC-level stock metrics
- Distributor Fulfillment - On-time delivery metrics, SLA breaches, delayed shipments
- SKU Profitability & Lifecycle - Gross margin by SKU, new launches vs. legacy product analysis
Technologies Used
