Metadata-Driven ETL & Power BI
How we replaced 200+ hand-built pipelines with one configuration-driven framework, and put near-expiry stock in front of store managers while it could still be priced.
What was breaking
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.
The platform we built
How the framework works
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.
Driven by config, not code
The entire pipeline is driven by deterministic JSON configurations. Onboarding a new global supplier data source requires exactly zero code compilation.
- Schema drift handled in config
- Quality rules enforced per source
What the store manager sees
Real-time spoilage tracking telemetry enabling immediate interventions at the extreme perimeter of the retail floor. Powered by Power BI streaming primitives.
What it produced
- 01
Reduced supplier onboarding time from 2 weeks to 4 hours.
- 02
Enabled dynamic pricing on near-expiry stock, turning spoilage into recoverable margin.
- 03
Decommissioned 200+ legacy SSIS packages, reducing maintenance costs by 60%.
- 04
Empowered non-technical users to access clean data via Power BI Datasets.