How I builddata platforms.
The patterns I build with on Azure, Microsoft Fabric and Power BI, from the source to the report. Each one is a problem every data platform meets, the build that solves it and the services it runs on. Pick a line on the map, or read them all below.
3 patterns on Azure.
Pipelines on Azure that bring data together from every system it is made in, load only what changed and run every night with nobody watching.
- Azure · 3 services
One pipeline, every table
A control table lists each source table with its key and when it was last loaded. One parameterised pipeline reads that list and copies every table in turn, so a new table is a new row, not a new pipeline.
- A Lookup reads the control table.
- A ForEach runs one Copy per table, several at a time.
- Each copy lands in the lake in its own folder.
- The run writes its result back to the control table.
- Azure Data Factory
- Azure SQL Database
- Data Lake Storage Gen2
- Azure · 3 services
Load only what changed
A full copy every night gets slower as the data grows. A watermark, the latest change already loaded, lets each run ask the source for the newer rows only.
- Read the last watermark for the table.
- Copy only the rows changed after it.
- Merge them into the target by key.
- Move the watermark on only when the copy succeeded.
- Azure Data Factory
- Azure SQL Database
- Data Lake Storage Gen2
- Azure · 3 services
Pipelines released like code
Data Factory connected to Git: every change goes through a pull request, and the same release goes to test and then to production, each with its own settings.
- Work on a branch in the Data Factory studio.
- Review the change in a pull request.
- Release to test, then to production.
- Keep every secret in Key Vault, so no pipeline holds a password.
- Azure Data Factory
- Azure DevOps
- Key Vault
3 patterns on Fabric.
One lake for all of it: a lakehouse in bronze, silver and gold, notebooks and pipelines that move data between the layers, and checks that stop bad rows early.
- Fabric · 4 services
Bronze, silver and gold in one lakehouse
The model at the top of this site: data lands in bronze as it arrived, is cleaned and joined in silver and is shaped for reports in gold, all in OneLake as open Delta tables.
- Pipelines copy each source into bronze, untouched.
- Notebooks clean, type and join it into silver.
- Gold tables answer one subject each: sales, finance, customers.
- Every layer is a Delta table any Fabric engine can read.
- OneLake
- Lakehouse
- Data pipelines
- Notebooks
- Fabric · 3 services
Bad rows stop at bronze
Duplicates, missing keys and values that break a rule are set aside on the way to silver, with the reason, so no report counts them and anyone can see why they left.
- Each check is a rule with a name.
- Rows that fail go to a quarantine table with the rule they broke.
- Every run keeps a count of rows passed and set aside.
- A run with too many failures stops before silver changes.
- Notebooks
- Lakehouse
- Data pipelines
- Fabric · 3 services
Use data where it is
A shortcut shows data that already sits in Data Lake Storage or in another workspace inside OneLake without copying it, so there is one copy to keep up to date and one place to secure.
- Add a shortcut to the folder in the lakehouse.
- Read it like any other table or file.
- Grant access where the data lives, once.
- Copy only what has to change shape on the way.
- OneLake
- Shortcuts
- Data Lake Storage Gen2
3 patterns on Power BI.
Semantic models with every KPI defined once in DAX, and reports a manager reads at a glance, in colours that work for everyone.
- Power BI · 3 services
Every KPI defined once
Each KPI is one measure in the semantic model, with a description and a format, so every report shows the same number. Year to date and last year come from one calculation group instead of a copy per KPI.
- One measure per KPI, named the way the business says it.
- Year to date, last year and the change as one calculation group.
- A description on every measure, shown in the field list.
- Reports use the measures, never their own copies.
- Semantic models
- DAX
- Calculation groups
- Power BI · 3 services
Reports straight from the lake
A Direct Lake semantic model reads the gold Delta tables in OneLake, so a report shows the new data without an import refresh copying it first.
- Gold tables as a star schema: facts and dimensions.
- A Direct Lake semantic model on top, with its relationships and measures.
- Reports on the model as on any other.
- Watch for the queries that fall back to DirectQuery.
- Direct Lake
- Semantic models
- Lakehouse
- Power BI · 2 services
Reports read at a glance
A theme file sets the colours, fonts and visual styles once for every page, the layout puts the answer where the eye lands first, and every colour is checked for contrast and colour blindness.
- A report theme as one JSON file.
- A page grid: the key number top left, the detail below.
- One chart per question, picked from the question.
- Contrast and colour-blind checks before anyone opens it.
- Report themes
- Power BI Desktop
About the patterns.
Which platforms do the patterns cover?
Three: Microsoft Azure, Microsoft Fabric and Power BI. Data comes in through Azure, is made right in Fabric and is read in Power BI. Each pattern names the problem, the build that solves it and the services it runs on. So you can see where it fits in your own platform.
Can I learn a pattern step by step?
Yes, several patterns have an open course. The course walks through the build one lesson at a time, in your own workspace, and it is free. A pattern with a course links to it. For the others, the talks show the same builds live, with time for questions.
Can you show a pattern at my user group?
Yes. Each talk is built on these patterns and shows a working build, not slides alone. Talks are free, online or in Vienna, and run 30 to 45 minutes. Write to me with who comes and when. I suggest the talk that fits your audience best.
Seen enough? Say hello.
A question about any of it, an idea for a talk, or the course you want next: I read every message.