A construction data lakehouse in Microsoft Fabric
One governed home for construction data: raw sources landed, cleaned, mapped, checked, and published to Power BI and a SQL endpoint.
The problem
Each report pulls its own extracts, so every dashboard has its own version of the truth and a fix in one does not reach the others. When a number is questioned, there is no record of what the source actually said.
What we build
We build a medallion lakehouse in your Fabric workspace: bronze keeps raw source data as received, silver cleans and validates it, and gold holds the mapped business model every report reads from. A data-quality gate runs before semantic models refresh, and a failed run leaves the last good numbers in place.
How it works
- 1
Extract with notebooks
Fabric notebooks pull each source through its API or a data gateway, with endpoint registries and rate limits kept in versioned configuration.
- 2
Bronze, silver, gold
Raw payloads with load stamps, then typed and trimmed tables with a rejects table, then the mapped facts and dimensions with a shared date dimension.
- 3
Gate the publish
Automated data-quality rules run against gold; a blocking failure stops the semantic model refresh and the report shows its last refresh time.
- 4
Publish and expose
Semantic models feed Power BI, and the gold SQL endpoint lets finance query the exact rows behind any number.
- 5
Run it as code
Notebooks, pipelines, model definitions, and rules live in version control and deploy by script, with orchestration on a nightly or agreed schedule.
- Microsoft Fabric
- OneLake
- Fabric notebooks and pipelines
- Azure Key Vault
- Power BI
- GitHub
The value it creates
Collective intelligence
Finance, operations, and leadership read from the same gold tables and the same governed measures.
Better decisions
Numbers only publish after the quality gate passes, and a stale answer is labeled as stale rather than replaced by a wrong one.
Cost saved
A logic fix is a re-run from bronze, not a re-extract or a rebuilt report; tracked by time to correct a reported issue.
Visibility
Portfolio, project, and line-level detail come from the same model, with a SQL endpoint for anyone who needs the rows.
Proof
- BuildConstruction reporting: Procore, Sage 100 Contractor, Outbuild, and SharePoint (Production)
- BuildConnected financial reporting: Procore, QuickBooks Online, and HubSpot (Production)
- ArticleHow the Procore, Sage, and Outbuild platform was built in Microsoft Fabric
- ArticleMicrosoft Fabric for contractors
Related use cases
- Built and shown
Automated WIP schedule from Procore and accounting
A WIP schedule that builds itself from Procore and your accounting system every night, with month-end figures frozen as reported.
- Built and shown
Executive portfolio dashboard for contractors
A portfolio page and a one-page executive KPI view, with drill-through from any project to its detail.
- Built and shown
Job cost reconciliation between Procore and accounting
A nightly cross-check of Procore cost against accounting job cost, with every difference listed and traced to the system to fix.
- Built and shown
Monthly progress report automation for contractors
The monthly progress workbook rebuilt as a refreshing Power BI report, with every figure traced to a source system or a validated input.
- Built and shown
Structured manual inputs in SharePoint for data no system tracks
Wins, risks, safety, and QC registers move from loose spreadsheets into SharePoint lists with defined fields that flow through the same quality checks.
- Built and shown
Weighted project health scorecard for construction
A 0 to 1 project health index from weighted categories, with the arithmetic and data coverage shown so nobody has to take the score on faith.
Frequently asked questions
What is a medallion architecture?
A lakehouse pattern with three layers: bronze for raw data as received, silver for cleaned and validated data, and gold for the modeled business tables reports use. Each layer has one job, which makes problems easier to trace and fix.
Do we need Microsoft Fabric, or will Power BI alone do?
For one source and a simple report, Power BI alone can be enough. Once you join several systems, need a crosswalk, or want checks before publishing, a lakehouse keeps that logic in one governed place instead of inside each report.
What happens if a nightly run fails?
The reports keep the last good data, and each page shows when it last refreshed. Nothing is calculated against a half-loaded dataset.
Who owns the lakehouse after the build?
You do. It is built in your Fabric workspace and your repository, with credentials in your Key Vault, and handed over with documentation.
Next step
Which report or workflow would you like to improve?
Tell us what your team does today, which systems are involved, and what you want to change. We'll discuss whether there is a practical fit.
Prefer email? charley@buildflows.ai