How to create Data Masking in a Lakehouse on Microsoft Fabric

João Barros 01 de November de 2026 4 min read

This tutorial shows how to apply Data Masking to tables in a Lakehouse on Microsoft Fabric to protect sensitive columns (e.g., card numbers, NIF, emails) without changing the source. It is useful to meet security requirements, enable safe reporting, and reduce data exposure in development and production environments.

Prerequisites

An account with access to a workspace in Microsoft Fabric and permissions to manage Lakehouse.
A Lakehouse with at least one Delta Table containing sensitive columns.
Permissions to create Views and manage permissions (GRANT/REVOKE) in the Lakehouse.
Basic knowledge of SQL/Spark SQL and data security concepts.

Step 1: Identify sensitive columns and create a sample table

Start by identifying which columns need masking. To test, create a sample table in the Lakehouse with sensitive data. Here we use Spark SQL in a Notebook or in the Lakehouse Query Editor.

CREATE OR REPLACE TABLE default.customers_mask_test (
  id INT,
  name STRING,
  email STRING,
  nif STRING,
  card_number STRING
);

INSERT INTO default.customers_mask_test VALUES
(1, 'Ana Silva', 'ana.silva@example.com', '123456789', '4111111111111111'),
(2, 'Carlos Pereira', 'carlos.p@example.com', '987654321', '5500000000000004');

Step 2: Concept — why use Views for Data Masking

A common approach in Fabric is to create Views that apply masking functions (substring, regexp_replace, CASE) to sensitive columns. It keeps the original table intact and allows granting different permissions to the View and to the table. This technique is simple and compatible with tools like Power BI and Warehouse.

Step 3: Create a View with Masking rules

Create a View that replaces sensitive parts with masked characters. Example: show only the last 4 digits of the card, hide part of the NIF and the email.

CREATE OR REPLACE VIEW default.vw_customers_masked AS
SELECT
  id,
  name,
  -- email: show only the domain and first letter
  CONCAT(SUBSTRING(email, 1, 1), '***@', SPLIT(email, '@')[1]) AS email,
  -- nif: keep last 2 digits
  CONCAT(REPEAT('*', LENGTH(nif)-2), SUBSTRING(nif, -2)) AS nif,
  -- card_number: show only last 4 digits
  CONCAT(REPEAT('*', LENGTH(card_number)-4), SUBSTRING(card_number, -4)) AS card_number
FROM default.customers_mask_test;

Step 4: Test the View and compare with the original table

Run queries on the View and on the original table to confirm the masking. Verify that the View returns masked data and that the original table remains complete (only for administrators).

-- See original data (only for those with permission)
SELECT * FROM default.customers_mask_test;

-- See masked data (for end users)
SELECT * FROM default.vw_customers_masked;

Step 5: Manage permissions — expose only the View to users

To ensure end users only see masked data, revoke direct access to the table and grant SELECT only on the View. Use Lakehouse access control SQL commands.

-- Revoke direct access to the table
REVOKE SELECT ON default.customers_mask_test FROM public;

-- Grant only View access to a group/role
GRANT SELECT ON default.vw_customers_masked TO analytics_readers;

Step 6: Automate rules with reusable functions (optional)

If you have multiple tables, create SQL functions to reuse the masking logic and maintain consistency.

CREATE OR REPLACE FUNCTION default.mask_email(e STRING) RETURNS STRING
RETURN CONCAT(SUBSTRING(e,1,1),'***@', SPLIT(e,'@')[1]);

CREATE OR REPLACE VIEW default.vw_customers_masked2 AS
SELECT id, name, default.mask_email(email) AS email,
       CONCAT(REPEAT('*', LENGTH(nif)-2), SUBSTRING(nif,-2)) AS nif,
       CONCAT(REPEAT('*', LENGTH(card_number)-4), SUBSTRING(card_number,-4)) AS card_number
FROM default.customers_mask_test;

Verify the outcome

Confirm that users in the analytics_readers role see only the View and that the data appears masked. Administrators with access to the original table should confirm that full data remains in the base table. Also test integration: connect a Power BI report to the Lakehouse using the View and verify that reports display masked data.

Conclusion

Applying Data Masking via Views in the Microsoft Fabric Lakehouse is a simple and effective solution to protect sensitive columns without duplicating data or altering the source. Next steps: implement role-based dynamic masking, integrate with Azure AD groups, and audit access. Tip: test masking in development environments before applying to production and ask: which columns in your organization need masking first?