Medallion lakehouse for market research | PTR

Analysts at many market research firms spend more of the day preparing data than analysing it. PTR can build a medallion lakehouse in Microsoft Fabric that takes raw data in and cleans and shapes it once, automatically, so your teams start from data that is ready to use.

The Challenge: hours spent preparing data, not analysing it

Research data arrives from review platforms, internal business systems and external sources such as social media. When reporting depends on extracting and preparing that data by hand, the same cleaning work is repeated every day, and the time goes on preparation rather than analysis.

Teams need to be able to:

Bring data from many platforms into one place
Clean, deduplicate and validate it once, not every day
Keep raw data, and its history, as it arrived
Export prepared CSV datasets quickly
Serve reporting from one trusted copy of the data

What PTR can build: a medallion lakehouse in Microsoft Fabric

We can build three lakehouses, one for each layer of the medallion pattern. Bronze holds raw data as it arrived, silver holds it cleaned and validated, and gold holds it modelled for analysis. All three sit in OneLake, so the SQL endpoint and Power BI read the same data without making copies, and scheduled pipelines move data from one layer to the next.

Raw data lands in bronze, is cleaned in silver and modelled in gold, ready for extracts and reports.

How PTR builds it: from a pilot to a gold layer

A pilot on a trial capacity

We start with a pilot on a Fabric trial capacity, taking your data through all three layers to a first CSV extract. The pilot tests capacity, cost and the loading pattern on your own data, and it is built to go into production rather than be thrown away. Further extracts are added once it moves to a full capacity.

A bronze layer that keeps the raw data

Bronze takes data in from a wide range of sources, from operational databases to review platforms and files in cloud storage. It mirrors the structure of each source and can keep historical versions, so nothing is lost when a source changes. Where raw data already lands in Azure Data Lake Storage or Amazon S3, a shortcut can expose it to bronze without copying it, which can cost less than loading it again.

A silver layer that cleans and validates

Silver is where data is deduplicated, validated and prepared, so each cleaning rule is applied once and in one place. We build it with Spark or Python notebooks, or with Dataflows Gen2 for complex sources or where your team finds them more intuitive than SQL. The pattern is agreed with your team to suit their skills and your data.

A gold layer shaped for analysis

Gold holds data modelled and aggregated into a star schema that matches your semantic models, so reports can read it as it stands. Where a team prefers T-SQL, a Fabric Warehouse with stored procedures can take the place of the gold lakehouse.

What it gives your teams: data that is ready when they are

Preparation that runs on its own:
scheduled pipelines take data from bronze to gold, so analysts start from prepared data instead of building it again each day.
Raw data kept as it arrived:
bronze keeps the source data and its history, so the original is always there to go back to when a rule changes.
Extracts and reports from one copy:
paginated reports export prepared CSV datasets, and Power BI reads gold through Direct Lake, both from the same data.
A platform that grows by source:
new sources and extracts are added layer by layer, without re-engineering what is already built.

Our view: prepare data once, in layers. Keeping raw, cleaned and modelled data apart means each fix is made in one place, and everyone downstream works from the same prepared copy.

Prepare your research data once

Stop spending hours every day extracting and preparing data by hand. Talk to us about building a medallion lakehouse for your research data. PTR provides consultancy, training and support in Microsoft Fabric.

Call 0118 979 4000 or email info@ptr.co.uk.

Use Cases

Fabric medallion lakehouse for market research firms

The Challenge: hours spent preparing data, not analysing it

What PTR can build: a medallion lakehouse in Microsoft Fabric