A practical, end-to-end walkthrough of building the bronze, silver, and gold layers in Microsoft Fabric, including data masking and row-level security in the gold layer.
The goal of medallion architecture is to incrementally improve the structure and quality of data. Think of medallion architecture as a three-stage cleaning and organizing process for your data. Each layer makes your data more reliable and easier to use.
1. Bronze (Raw): Store everything exactly as it arrives. No changes are allowed.
2. Silver (Enriched): Fix errors, standardize formats, and remove duplicates.
3. Gold (Curated): Organize for reports and dashboards.
This architecture involves Extract, Transform, and Load of the source data. The source data can be both internal (One Lake or on-prem database) or external (Azure Data Lake Store Gen2, Amazon S3, Google Cloud Storage, Azure SQL database) storage systems.
– Extract data from the source side to the bronze layer Lakehouse using shortcuts or through pipelines using copy data activity and save it in delta format.
– In the bronze layer, you store data in its original format, which might be either tables or files. Tables will be used to store structured datasets (CSV, Parquet, or Delta), and files will be used to store semi-structured or unstructured datasets (JSONs, logs, PDFs, images, IOT readings).
– In the bronze layer, you can have smaller files because of the raw nature of the data, as long you focus data modification and preparation with Spark.
– Transform the data from Bronze lakehouse to Silver lakehouse, either using a notebook or Dataflow Gen2.
– In Silver layer, you store the data as delta tables after applying transformation rules such as data quality checks, null checks, removing duplicate records, appropriate aggregations as per the business requirements, etc.
– Delta tables use V-Order write-time optimization to the Parquet file format. That optimization enables fast reads by Fabric compute engines, such as Power BI, SQL, Apache Spark, and others.
– Once the data is transformed, we will load the data into the gold layer.
– This layer will be a warehouse, unlike bronze and silver layers, which were lakehouses.
– Data gets loaded to the gold layer by either using a pipeline or through dataflow Gen2.
– The gold layer will be the SQL analytics endpoint for the semantic models or the reports in our workspace.
– The gold layer will contain the Star schema fact and dimension tables and any relationships in between them.
– Technical guide on how to implement Medallion architecture in Microsoft Fabric.
– Technical guide on how to implement data masking and row-level security in the gold layer.
– Identification of the limitations in the previous architecture and the advantages of the current architecture.
– This layer contains a raw data or a copy of the data from the source side without any kind of transformations applied.
– Assuming we have already created a workspace, click on new item as shown below.
– Search for Lakehouse item and click on that item.
– Give the name as Bronze Layer, provide the location where you want to save the item in the workspace, check/uncheck Lakehouse Schemas (Schemas let you manage and reference objects in your lakehouse more easily. You can use a default schema to automatically fill in file paths when referencing a data object in code) depending upon the use case and click on Create. You will see a new item of Lakehouse being created.
– Go inside that item and click on the three dots beside the Files Folder. Click on New Shortcut.
– Go inside that item and click on the three dots beside the Files Folder. Click on New Shortcut.
– Since initially we won’t have an existing connection, we will create a new connection and provide the authentication kind as Organizational account. Click on the Next button.
– Select an appropriate directory of which you want to create a shortcut. Click on Next button.
– It will summarize with the location of the shortcut. Click on Create button.
– A new shortcut will be created with the same name as the directory name at the source side. In our case, we have already made a shortcut with a different directory as the source, hence it won’t be seen in the image below.
– Similarly, we can do for other data sources as well.
– This layer contains transformed data. This means it will have the data after implementing all kinds of transformations, such as data quality checks, aggregations, joins, unions, data type checks, null checks, etc.
– To transform the data, we can either use a Spark Notebook or a Dataflow Gen2. In our case, we are creating a PySpark Notebook.
– To create this layer, again click on new item as shown above in Bronze Layer.
– Search for the Lakehouse item and click on that item.
– Give the name as Silver Layer, provide the location where you want to save the item in the workspace, check/uncheck Lakehouse Schemas as per the use case, and click on Create. You will see a new item of Lakehouse being created.
– Now, again click on the new item as shown above in the Bronze Layer.
– Search for Notebook/Dataflow Gen2.
For Notebook:
– Enter the name of the item and the location where you want to save.
– Click on Create button.
For Dataflow Gen2:
– Enter the name of the dataflow and uncheck Git integration box.
– Click on Create button.
– You will see a new item of Notebook/Dataflow Gen2 being created.
– Open the item
For Notebook:
– Click on Data items
– Click on the OneLake Catalog
– Choose the Bronze and Silver Layer lakehouse as the data items to load to the Notebook. Click on the Add button.
– You will see the data items as shown below added. Then, click on connect tab and on New Standard Session to connect to processing engine where the script will run.
– Write the commands of loading the data from bronze layer, transforming the data on top of the loaded dataframe and writing the dataframe to Silver Layer in the notebook cells as shown.
For Dataflow Gen2:
– Click on Get data. Click on More.
– Click on OneLake catalog. Choose Bronze layer lakehouse item.
– Choose appropriate directory/files.Click on Create button.
– Write commands to do transformations as per business use case and click on ‘+’ sign besides data destination.
– Click on more and choose Lakehouse.
– Click on the Next button and choose existing lakehouse connection credentials.
– Go to the appropriate workspace and lakehouse location and choose Silver Layer as the destination.
– You can choose between a new table, an existing table, or a new file. In our case, we will be choosing a new table.
– Click on Next button.
– It will map the columns with appropriate column data types. You can change the data types according to the use case. Click on save settings.
– Click the publish button
– We will schedule and run the Notebook/Dataflow Gen2 using data pipelines. It is mentioned in detail under gold layer.
– This will be a warehouse. It will only contain the data that is for immediate use in the semantic model and reports. It will contain the data that is necessary for analysis purposes.
– This will get updated either daily, weekly, or monthly, depending on the use case.
– To create this layer, again click on new item as shown above in Bronze Layer. Search for a warehouse.
– Name the warehouse and click on the Create button.
– It will contain dbo as the default schema. Under this schema, we can create tables, views, functions, or stored procedures.
– Since we are not doing any type of transformations on top of data stored in the Silver layer, we will directly migrate the data from the Silver layer to the Gold layer using a copy data activity in data pipelines.
– In case one wants to do some kind of column selection or renaming or create additional columns on top of existing ones, they can do it using a Notebook/dataflows before moving the data to the Gold layer. Steps will remain the same as mentioned under the Silver layer.
– Now, again click on the new item as shown above in Bronze Layer. Search for pipeline.
– Name the pipeline and click on the Create button.
– We will be creating two pipelines here. One to schedule the notebook/dataflow and another to copy the data from the Silver layer to the Gold Layer.
– Go to the activities tab. Click on Notebook.
– Go to the Settings tab.
– Choose the workspace where your notebook/dataflow resides.
– Choose the notebook/dataflow that you want to schedule for run.
– Go to the Run tab. Click on ” Validate “ to check for any errors in your pipeline activities.
– If no errors, click on run.
– This will ask you to save and run your notebook/dataflow for the first time. Click on save and run.
– You can monitor the runs by going to View run history and then clicking Go to Monitor.
– Go to activities tab. Click on Invoke pipeline.
– Click on Settings. Type will be fabric.
– For Connection, click on Browse all.
– Click on Fabric data pipeline.
– Create a new connection. Click on Connect.
– Choose the appropriate workspace. Choose bronze_to_silver layer pipeline. Check mark wait on completion. This will invoke bronze to silver layer pipeline.
– Now, Click on copy data. Click on Add copy data activity.
– Click on Source.
– Connection will be Lakehouse and Lakehouse will be Silver Layer Lakehouse.
– We are saving our data as tables in silver layer. So, our root folder will be Tables, and the query will be Table.
– We will choose our praxedo table from the drop–down.
– Now, choose destination.
– Connection will be Gold layer. Table option will be Auto create table.
– Provide the schema name and the table name. Write behaviour will be insert.
– Click on Mapping. Click on import schemas. This will map the source and destination table columns automatically.
– Ensure that under Settings tab, Enable staging is ON and data store type is Workspace.
– Now create an arrow from Invoke pipeline to copy data activity. These activities will then run in sequence.
– Go to Run tab. Click on validate to check for any errors in your pipeline activities as shown in previous steps.
– If no errors, click on run. This will ask you to save and run your pipeline for the first time. Click on save and run. Bronze to Silver layer pipeline will also run and that will simply overwrite the data from Bronze to Silver layer.
– Once the run is successful, go back to Activities tab and click on copy data activity.
– Now, change the table option from Auto create table to Use existing and choose the appropriate destination table from the drop down.
– Also, change the write behaviour from insert to upsert. It will ask for Key columns which will help it to analyze which data is to insert and which data to update accordingly.
– Go back to Home tab.
– Click on Save button.
– This will save your pipeline.
– Click on add schedule. Based on your use case, you can schedule it by minute, hourly, daily, weekly or monthly.
– You can add the frequency. In our case, we need to run it on the daily basis. You can add attributes such as the time at which you want to schedule the pipeline, start date, end date and time zone. After this, click on save button.
– Also, you can add the email addresses at which the notification will be sent if the pipeline fails on a particular day.
– We have following 2 folders. Current architecture is under development folder.
– Under development folder, we will have following architecture. Our layers will be kept here. All the other items will be kept under respective folders.
Dataflow Gen2 pulls data directly from the source:
Dataflow → Source system → Transform → OneLake
Benefits:
Limitations:
Source → OneLake shortcut/landing → Dataflow → curated data
Benefits:
Limitations:
Hence second option will help in scalability, reusability, will be engine‑agnostic, useful for enterprise‑grade architecture.
You can use medallion architecture in following scenarios:
You cannot use medallion architecture in following scenarios:
OneLake shortcuts only work with file-based object storage (S3/GCS/ADLS-style endpoints).
SQL Server is not object storage, so it cannot be mapped as a shortcut.
Alternatives:
Mirroring creates a real-time or near–real-time replica of your SQL Server tables into OneLake using CDC.
We should use mirroring:
Tables excluded from mirroring:
Data masking in Microsoft Fabric is a security feature that protects sensitive data by obscuring it for unauthorized or low‑privileged users, while still enabling analytics, reporting, and development work.
Microsoft Fabric supports Dynamic Data Masking (DDM) natively in Fabric Data Warehouse and the SQL analytics endpoint. In our case, we are using Default mask (e.g., strings become XXXX, numeric become 0, dates become 1900-01-01).
Roles such as Admin, Member, or Contributor have CONTROL permission on the database by design and can view unmasked data by default.
Users without the Administrator, Member, or Contributor rights on the workspace, and without elevated permissions on the Warehouse, will see masked data.
– -Applying mask on a particular column – –
ALTER TABLE dbo.pxo_validated_tickets
ALTER COLUMN Id ADD MASKED WITH (FUNCTION = ‘default()’);
GO
– – Granting/Revoking permissions for a desired user on masked data column – –
GRANT UNMASK ON dbo.pxo_validated_tickets TO sabhye.singhal@synlab.com;
REVOKE UNMASK ON dbo.pxo_validated_tickets TO sabhye.singhal@synlab.com;
– – Revoke permissions on a specific group – –
ALTER ROLE <YourRoleName> REVOKE UNMASK;
Row-Level Security (RLS) in Microsoft Fabric allows you to restrict access to specific rows in a table based on who the user is or what role/group they belong to. This ensures each user sees only the data they’re permitted to see, even when everyone queries the same table.
According to Microsoft Learn, RLS applies to both the Fabric Warehouse and the SQL analytics endpoint.
RLS enables precise control by filtering rows at query time:
RLS is enforced inside the database tier, not in apps like Power BI, ensuring consistent security across all tools and reducing the risk of bypass.
Users with Admin, Member, or Contributor permissions at the workspace level inherently have high‑level control and can view unfiltered data. Also, if a table has no RLS policy, users see full unfiltered data.
– – Creating inline table-valued function – –
Alter FUNCTION Security.country_securitypredicate(Country AS nvarchar(50))
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS country_securitypredicate_result
WHERE @Country = ‘Germany’ AND USER_NAME() = ‘sabhye.singhal@synlab.com’;
GO
– – Using the function to create a Security Policy – –
CREATE SECURITY POLICY filter_based_on_country
ADD FILTER PREDICATE Security.country_securitypredicate(NOM_AGENCE)
ON dbo.pxo_validated_tickets;
GO
– – Revoking the security policy – –
Drop security policy filter_based_on_country
Navigation