Microsoft Fabric
Data Cleansing with the Medallion Method
Dirty data gives you reports nobody trusts. Learn how the medallion method cleans it in layers, and how Microsoft Fabric now automates much of the work with materialized lake views, lakehouse schemas and OneLake security.
Published · Updated
A week of washing lands in one basket: work shirts, gym kit, towels and a red sock that has been through the whites. Nobody would lay that out for an important meeting. Yet many businesses hand their board the data equivalent every month, reports built from mismatched, half-duplicated records, and then spend the meeting arguing about whose number is right.
The medallion method cleans data in stages, so every report draws on information that has been sorted and washed first. It has been popular in lakehouses for years. Microsoft Fabric now builds in much of what teams used to code by hand, and this article shows what has changed since we first covered the topic.
What is the medallion method for data cleansing?
The medallion method moves data through three layers in a fixed order: Bronze, Silver and Gold. Raw data enters at the start and business-ready data leaves at the end, and that final layer becomes your Single Source of Truth. In laundry terms, Bronze is the basket as it arrives, Silver is the washed and ironed pile, and Gold is the outfit laid out for the day. The method works in lakehouses, in data warehouses or in a mix of the two.
Bronze layer: keep the original receipt
Bronze holds raw data exactly as it arrived, dirty and all, with a full history of every import. Data engineers work in it and auditors sometimes ask to see it, but nobody reports from it. It is the record of what the source system said at the time, which lets you answer an auditor and reload Silver and Gold if something goes wrong. Because business applications keep adding and changing fields, Bronze has to accept new columns without losing data.
Silver layer: where the cleansing happens
Silver is where the cleansing happens. It holds validated, joined data that is ready for analysis but not yet shaped for everyday business users. Data engineers build it, and data scientists often work from it. The main jobs are:
- De-duplication. The same customer can sit in your CRM, order system and accounts package under slightly different spellings. Decide which records are true duplicates and which entities to merge.
- Error correction. Missing, wrong, late-arriving or inconsistently typed values, and mixed upper and lower case. Fix what you can in Silver and flag the rest so the source system can be corrected.
- Joining entities. Combine order headers with order lines, or CRM, order and finance records into one customer record. Keep each Silver record to one real-world item, such as a single order line, and leave monthly totals for Gold, with the occasional justified exception.
- Semi-structured data. JSON or XML from application interfaces needs a decision on what to keep, and may need splitting into separate tables.
- Schema enforcement. Convert generic text into proper dates, numbers and text so that everything downstream behaves predictably.
Our article What Is Dirty Data? covers the typical problems in more detail.
Gold layer: ready for the business
Gold holds curated data for business users, analysts, data scientists and machine learning engineers. It is organised around your business logic and tuned so that reports and models refresh quickly. You can publish high-level summaries without exposing sensitive detail, and keep the granular records for anyone who needs to dig deeper.
Why do the layers matter?
A single script that cleans, joins and reshapes everything in one go leaves you nowhere to look when a number seems wrong. Layers give you somewhere to look. If someone asks where a figure came from and what it looked like originally, you can follow it back through Gold and Silver to the raw record in Bronze.
The same trail answers a practical question: if you replace your CRM, which reports, dashboards and AI models will be affected? Bronze also keeps the history, so when a manager disputes a report you can see the data as it stood on the day.
Layers also let you control access. People who build pipelines need raw data, while most of the business should only ever see Gold. Confidential columns should reach only the people cleared for them.
How does Microsoft Fabric support the medallion method?
Fabric puts all three layers on one platform, and recent features take over a lot of work that teams used to build by hand. These are the ones that matter for data cleansing.
Lakehouse layout and schemas: separate cabinets, labelled drawers
Microsoft's medallion guidance is to keep each layer in its own lakehouse or warehouse, and to give each lakehouse its own workspace for better control and governance. Schemas are now generally available and are the default for new lakehouses. They group the tables inside a lakehouse by source system or business area, so a long list becomes something you can find your way around.
Some organisations prefer one lakehouse per business domain with a schema for each layer. That works, but you lose the workspace boundary between layers, so make it a deliberate design decision. If you organise by business domain, Microsoft suggests setting up the three layers within each domain. If you are also weighing a data warehouse against a medallion lakehouse, our article Data Warehouse or Medallion Architecture Lakehouse? covers the choice.
Materialized lake views: a washing machine with programmes
Until recently, a Fabric medallion pipeline meant hand-building notebooks, pipelines, schedules and quality checks, then hoping they ran in the right order. Materialized lake views are now generally available and handle much of that for you, like a washing machine with programmes. You describe each transformation as a SQL query, and Fabric stores the result as a table, refreshes it and works out the order.
For data cleansing, the useful parts are the built-in quality rules, the automatic ordering, the refresh handling and the monitoring. You can attach a check to a view, such as "sales amount must be above zero", and choose whether rows that break it are dropped or the refresh fails. Fabric tracks which views depend on which, refreshes them in the right sequence and shows the dependency chain, including across lakehouses.
Fabric also decides whether to rebuild a view in full, update it incrementally or skip the refresh when nothing has changed. Incremental refresh needs Delta change data feed switched on for the source tables. Run history, data quality metrics and violation counts sit in one place, and a view can read CSV or Parquet files from OneLake straight into a Bronze table, capturing malformed rows in a column for inspection.
There are limits. Logic that is not SQL, such as machine learning or calls to external services, still belongs in notebooks. Microsoft points high-frequency streaming to Real-Time Intelligence, and PySpark authoring was still in preview when we checked.
Getting data into Bronze: shortcuts and mirroring
A shortcut points at data where it already lives, so nothing is copied. Mirroring keeps a near real-time copy of an operational database in OneLake without a load process. Both populate Bronze without ingestion pipelines to write and maintain. Shortcut transformations, now generally available, can also convert files to Delta format as they arrive. We explain the thinking in A Toolbox, Not a Moving Van.
Securing every layer with OneLake security
OneLake security lets you define who can see which tables, rows and columns once, and the rules follow the data into Spark notebooks, SQL queries and Power BI reports. It is now generally available and also covers mirrored data. It works alongside the separate workspaces Microsoft recommends for each layer: workspace roles decide who can reach a layer, and OneLake security narrows what they see inside it.
Technology only enforces the rules, so someone has to decide them first: who owns each dataset, which columns are sensitive, who approves access and how often that access is reviewed. Our Data Governance consultancy helps you build a practical framework for those decisions, and our Microsoft Purview service puts supporting tools in place, such as data cataloguing, lineage, sensitivity labelling and data loss prevention. Our article OneLake Security: Set the Rules Once, Protect Every Report explains how the roles work in practice.
Real-time data and the medallion pattern
Streaming data can follow the same Bronze, Silver and Gold pattern in Fabric's Real-Time Intelligence, using Eventhouse update policies and materialized views. If your business reacts to events as they happen, the layered idea still applies.
AI-assisted cleansing
Data Wrangler, the point-and-click tool inside Fabric notebooks, now includes Copilot. You describe a change in plain English, such as "standardise these dates and remove blank rows", and it generates the code with a preview of the result. It also offers AI functions for classifying and summarising text, which are still in preview. An administrator needs to enable these features, and you need a paid Fabric capacity.
AI can tidy a column quickly, but it cannot tell you whether two customer records are the same company. That decision belongs to people who know the business. If you are weighing how far AI should go beyond tidying data, our article Why Microsoft Fabric Needs Agentic AI to Deliver Real Business Value looks at AI that acts on what the data shows, and why that needs trusted data, clear permissions, defined workflows and human approval when the stakes are high. For a step-by-step pilot on a single column, see AI-Assisted Data Cleansing in Microsoft Fabric.
What should you plan before you build a medallion solution?
The right design depends on your internal skills, your budget and timeline, the sources you need to connect, your governance and compliance requirements and the quality of your source data. A short discovery and data strategy exercise before you build saves you from choosing tools that turn out not to fit, and our article Planning and Executing Data Cleansing walks through the planning, discovery and profiling steps. Build governance and security into the design from day one, because adding them later is harder.
We have worked with Fabric since it launched in November 2023, including moving clients from Azure Synapse and SQL Database. When we do that, we start with a review of the current estate and break delivery into small milestones, so you see visible results early. Some clients move their existing lake and warehouse as they are and reshape into Bronze, Silver and Gold later. Others use the move to reshape the platform straight away, and we help you choose between the two.
Technology is rarely what holds a data project back. Our article The Real Reason Fabric Adoption Stalls explains how unclear ownership, skills gaps, governance nerves and change fatigue slow adoption even when the platform is ready, and why one small, visible win persuades a sceptical team better than a bigger rollout. Agree who owns each layer, and who has been trained to use it, before you build.
How can PTR help with data cleansing in Microsoft Fabric?
We blend consultancy with training, so your team stays in control of its own data. Our Data Analytics and Data Strategy consultancy services assess where your data sits today and design a layered solution that fits your skills, budget, timeline and governance needs. Our Microsoft Fabric consultancy and Data Engineering and Data Integration teams build the Bronze, Silver and Gold layers, including materialized lake views, schemas and OneLake security.
To build the skills in your own team, our Fabric training courses, tailored training and blended learning mentoring teach your engineers and analysts to run and extend the solution themselves. For a worked example, see our article on a medallion lakehouse for market research firms.
If you would like a second pair of eyes on your current data estate, get in touch about our free BI data strategy and platform review.
MD
Mandy Doward
Managing Director
PTR’s owner and Managing Director is a Microsoft certified Business Intelligence (BI) Consultant, with over 35 years of experience working with data analytics and BI.
Related Articles
Frequently Asked Questions
Couldn’t find the answer you were looking for? Feel free to reach out to us! Our team of experts is here to help.
Contact Us