A grocery estate ingested supplier and point-of-sale data through hand-written SSIS packages, one per feed, loaded overnight. We replaced the estate with one master pipeline that reads its instructions from a control table, curated the result in Synapse, and put a governed Power BI dataset in front of store managers.
A large estate of hand-written SSIS packages, each supplier and each point-of-sale feed with its own. A new supplier meant a new package, a deployment and one more thing to maintain. Loads ran overnight, so the stock reports store managers relied on described yesterday.
One reusable master pipeline in Azure Data Factory, told what to load by a control table. A new source is a row in that table, reviewed like any other change. Data lands append-only, is curated in Synapse, and is read by store managers through one governed Power BI dataset.
Azure SQL configuration directs one ADF master pipeline. Source records move through Bronze landing, cleansed Silver tables and a Gold star schema before Power BI.
One parameterised ADF pipeline reads the control table and loops through the configured sources. Copy activities land each feed in Bronze; the master pipeline coordinates validation, curation and per-source run logging.
Dashed routes carry configuration, orchestration and run logs. Solid routes carry business records: ADF Copy lands Bronze, then Synapse produces the Silver tables and Gold star schema.
One row per source in Azure SQL: system, connection, schedule, schema, landing path, quality rules. The table is the specification of the estate, versioned and reviewed; nothing about a source lives only inside a pipeline.
A single Azure Data Factory pipeline reads the control table and runs each source through the same steps: extract, land, validate, log. Parameterised activities and loops replace copies of the same logic; schema drift is handled in configuration, not in a hotfix.
Bronze retains every feed append-only in its original form. Synapse validates and conforms it into silver cleansed tables, then builds the gold star schema for the questions store teams actually ask: stock, sell-through, expiry. Each layer has a distinct responsibility before Power BI reads the reporting marts.
One governed Power BI dataset over the star schema, with the measures defined once. Store managers see current stock and near-expiry lines in the report they open every morning; head office sees the same numbers.
Every run is logged against the source that produced it, with alerts on failed validations. Onboarding, schedule changes and retirements are all changes to the control table, so the operations story is one table and one pipeline.
A row in the control table, reviewed and released, and the master pipeline loads it on its schedule. No new package, no new deployment.
The estate of packages became one pipeline and one table. Monitoring, alerting and change control apply once.
Loads are no longer one overnight batch; each source runs on the cadence its data needs, so store managers read current stock rather than yesterday’s.
Store teams and head office read the same Power BI dataset with the same measures; the spreadsheet copies that used to circulate went away.
Each feed was moved onto the framework, reconciled against its package, and the package switched off. Nothing was retired on faith.