The Challenge 01
The client relied on 200+ hardcoded SSIS packages to ingest data from suppliers and POS systems. Onboarding a new supplier took 2 weeks of developer time. Data latency was high (T+48 hours), meaning store managers received 'Spoilage Reports' after items had already expired. They needed a scalable, automated solution to democratize data access and reduce operational overhead.
Metadata-Driven Architecture
A unified platform powered by Azure Data Factory and Synapse Analytics.
The Solution 02
We implemented a 'Metadata-Driven Ingestion Framework'. Instead of creating a pipeline for each source, we built a reusable 'Master Pipeline' in ADF that reads configuration (Source System, Schedule, Schema) from a SQL Control Table. This dynamic approach allows the client to onboard new data sources by simply inserting a row into a table—zero code required. We modeled a Star Schema in Synapse and built a Power BI 'Store Manager Dashboard' for real-time inventory tracking.
Automated Ingestion Config
The entire pipeline is driven by simple JSON configurations. Adding a new supplier data source takes minutes, not weeks.
- Schema Drift Handling
- Auto-Quality Checks
Store Manager Interface
Real-time spoilage tracking enabling immediate interventions at the store level. Powered by Power BI streaming datasets.
Impact Delivered 03
- Reduced supplier onboarding time from 2 weeks to 4 hours.
- Saved $4M annually by enabling dynamic pricing on near-expiry items.
- Decommissioned 200+ legacy SSIS packages, reducing maintenance costs by 60%.
- empowered non-technical users to access clean data via Power BI Datasets.