Implementing Column-Level Encryption and Dynamic Data Masking in a Fabric Warehouse: Protecting Sensitive Fields While Preserving Direct Lake and T-SQL Query Access

Introduction

Your data warehouse contains a Customers table with social security numbers, credit card digits, date of birth, and annual income. Downstream analysts need to query purchase patterns, segment customers, and build Power BI reports — but only your compliance team and a handful of DBAs should ever see a raw SSN or a full credit card number. The challenge isn't granting or denying access to tables wholesale; that's easy. The challenge is letting the same query hit the same table and return either real data or redacted data depending on who is asking. That's the problem column-level security and dynamic data masking solve — and it's one of the more nuanced security design problems you'll face in Microsoft Fabric.

This lesson covers the full implementation: how Dynamic Data Masking (DDM) works inside a Fabric Warehouse, how to combine it with column-level DENY permissions to create layered defenses, how to handle the specific wrinkle that Direct Lake semantic models introduce, and where Always Encrypted patterns fit when you need true cryptographic protection rather than presentation-layer redaction. By the end, you'll be able to design a coherent field-level security model that lets Power BI reports, T-SQL queries, and Direct Lake access coexist without creating gaps where sensitive data can leak through.

What you'll learn:

• How Dynamic Data Masking works inside a Fabric Warehouse and how masking rules are evaluated at query time
• How to combine DDM with column-level GRANT/DENY permissions for a layered security posture
• How Direct Lake mode interacts with column masking and what compensating controls you need
• How to implement deterministic and randomized masking functions for different data types
• How to test your masking and permission configuration as a non-privileged user to validate it before production

---

Prerequisites

You should be comfortable with:

• T-SQL fundamentals including views, permissions, and schema management
• The difference between a Fabric Warehouse and a Lakehouse SQL Analytics Endpoint — if you need a refresher, see Fabric Lakehouse vs Warehouse: Choosing the Right Store for Your Workload
• Workspace roles and item-level permissions in Fabric — covered thoroughly in Securing and Governing Microsoft Fabric: Workspace Roles, Item Permissions, and OneLake Data Access
• Basic Direct Lake concepts — the Direct Lake Mode in Power BI article explains the architecture

---

Understanding the Security Landscape: What DDM Actually Protects

Before writing a single line of SQL, you need a clear mental model of what Dynamic Data Masking is and — critically — what it is not.

DDM is a presentation-layer control. When you define a masking function on a column, the underlying data in storage is never modified. The raw values sit in the Delta Parquet files in OneLake exactly as they were written. What DDM does is intercept the result set at query execution time and substitute masked values for any principal who doesn't hold the UNMASK permission on that column or table. Think of it like a one-way mirror at the query layer: the data behind the glass is real, but most users see only the reflection the database decides to show them.

This distinction matters for two reasons. First, DDM alone does not protect against someone who has direct access to the underlying files in OneLake. If an analyst has OneLake storage reader access and knows the Delta table path, they can read the raw Parquet files entirely outside the Fabric Warehouse engine and see every field in plaintext. Second, DDM does not protect against users with elevated T-SQL permissions like CONTROL on the database or SELECT with UNMASK. A user who can grant themselves UNMASK can trivially bypass masking.

This is why the professional approach is always layered: DDM as a query-time convenience control, combined with column-level DENY permissions as an enforcement mechanism, combined with OneLake access control to restrict raw file access, and combined with Row-Level Security where row-scoped filtering is also needed. For a companion treatment of RLS design in Fabric, see Implementing Row-Level Security in a Fabric Warehouse and Lakehouse SQL Analytics Endpoint.

---

Setting Up the Schema: A Healthcare Payments Scenario

We'll work with a realistic scenario: a healthcare payment processing company that stores patient billing data in a Fabric Warehouse. The dbo.PatientBilling table contains fields at different sensitivity levels. Some fields (like PatientID, BillingAmount, ServiceDate) need to be visible to billing analysts for aggregation and reporting. Others (like SSN, DateOfBirth, CardLastFour) must be masked for most users. The raw FullCardNumber column should not be accessible at all to non-privileged users.

-- Create the billing table with realistic sensitive fields
CREATE TABLE dbo.PatientBilling (
    BillingID        INT            NOT NULL,
    PatientID        VARCHAR(20)    NOT NULL,
    PatientFirstName VARCHAR(100)   NOT NULL,
    PatientLastName  VARCHAR(100)   NOT NULL,
    DateOfBirth      DATE           NOT NULL,
    SSN              CHAR(11)       NOT NULL,   -- format: NNN-NN-NNNN
    FullCardNumber   VARCHAR(19)    NOT NULL,   -- format: NNNN-NNNN-NNNN-NNNN
    CardLastFour     CHAR(4)        NOT NULL,
    Email            VARCHAR(255)   NOT NULL,
    BillingAmount    DECIMAL(10,2)  NOT NULL,
    ServiceDate      DATE           NOT NULL,
    DiagnosisCode    VARCHAR(10)    NOT NULL,
    ProviderID       INT            NOT NULL
);

Insert some sample data so we can verify masking behavior throughout the lesson:

INSERT INTO dbo.PatientBilling VALUES
(1001, 'PAT-00123', 'Sarah',   'Okafor',    '1979-04-12', '412-77-9023',
 '4532-1234-5678-9012', '9012', 'sokafor@email.com',    1250.00, '2024-11-01', 'M54.5', 201),
(1002, 'PAT-00124', 'James',   'Whitfield', '1965-09-30', '301-55-8847',
 '4916-8765-4321-0987', '0987', 'jwhitfield@email.com', 3400.50, '2024-11-03', 'J18.9', 205),
(1003, 'PAT-00125', 'Priya',   'Rajan',     '1988-01-18', '219-44-6612',
 '5500-0023-5461-0001', '0001', 'prajan@email.com',      875.00, '2024-11-05', 'K29.7', 201);

---

Implementing Dynamic Data Masking Functions

Fabric Warehouse supports the same DDM masking functions available in Azure SQL Database. Let's apply them systematically, understanding why each masking function was chosen for each column type.

The `default()` Function

The default() function applies a type-appropriate mask: strings become XXXX, numeric types become 0, and date/time types become 1900-01-01 00:00:00.0000000. It's a blunt instrument — useful for columns where you want to signal to the user that a value exists but reveal nothing about its content.

The `partial()` Function

partial() is more surgical. It lets you expose a prefix and suffix of a string while masking the middle. The syntax is partial(prefixLength, paddingString, suffixLength). This is exactly what you want for SSNs (show last four), email addresses (show domain), or card numbers (show last four digits).

The `email()` Function

A convenience shorthand that exposes the first character of the email address and the domain suffix. It's roughly equivalent to partial(1, 'XXX@', 0) but cleaner to read.

The `random()` Function

Applies only to numeric columns. It returns a random integer within a range you specify rather than the actual value. Use this when you need to preserve approximate scale but not exact values — for instance, exposing that a billing amount is in the hundreds rather than thousands without revealing the precise figure.

Now let's apply masking to our table. Notice that you apply masking functions via ALTER TABLE ... ALTER COLUMN ... ADD MASKED WITH (FUNCTION = ...) on existing columns, or inline during CREATE TABLE:

-- Mask SSN: show nothing (full default mask for a sensitive identifier)
ALTER TABLE dbo.PatientBilling
ALTER COLUMN SSN ADD MASKED WITH (FUNCTION = 'default()');

-- Mask DateOfBirth: default mask - no information revealed
ALTER TABLE dbo.PatientBilling
ALTER COLUMN DateOfBirth ADD MASKED WITH (FUNCTION = 'default()');

-- Mask FullCardNumber: default mask - this column will also get DENY later
ALTER TABLE dbo.PatientBilling
ALTER COLUMN FullCardNumber ADD MASKED WITH (FUNCTION = 'default()');

-- Mask CardLastFour: partial mask - reveal only last 2 digits
-- For a 4-char field: show 0 prefix chars, 'XX' in the middle, 2 suffix chars
ALTER TABLE dbo.PatientBilling
ALTER COLUMN CardLastFour ADD MASKED WITH (FUNCTION = 'partial(0, "XX", 2)');

-- Mask Email: expose domain only using partial
ALTER TABLE dbo.PatientBilling
ALTER COLUMN Email ADD MASKED WITH (FUNCTION = 'email()');

-- Mask PatientFirstName: show first initial only
ALTER TABLE dbo.PatientBilling
ALTER COLUMN PatientFirstName ADD MASKED WITH (FUNCTION = 'partial(1, "XXXXXXXX", 0)');

You can verify masking is configured correctly by querying the system catalog:

SELECT
    c.name         AS column_name,
    c.is_masked,
    c.masking_function
FROM sys.masked_columns c
JOIN sys.tables t ON c.object_id = t.object_id
WHERE t.name = 'PatientBilling'
ORDER BY c.column_id;

---

Column-Level DENY: The Hard Enforcement Layer

DDM gracefully degrades what users see, but it doesn't prevent users with sufficient permissions from bypassing masking. The second layer of your defense is DENY SELECT at the column level, which prevents specific columns from appearing in query results at all — the query simply errors out for unauthorized users.

For our FullCardNumber column, we want zero tolerance: no masked view, no partial exposure, just a hard permission boundary. The pattern is:

-- Create a role for standard billing analysts
CREATE ROLE BillingAnalyst;

-- Grant SELECT on the full table
GRANT SELECT ON dbo.PatientBilling TO BillingAnalyst;

-- Then DENY SELECT on the columns that should never be visible to this role
DENY SELECT ON dbo.PatientBilling (FullCardNumber) TO BillingAnalyst;
DENY SELECT ON dbo.PatientBilling (SSN) TO BillingAnalyst;

With this configuration, a BillingAnalyst who runs SELECT * FROM dbo.PatientBilling will get a permission error — not because they can't access the table, but because SELECT * implicitly includes FullCardNumber and SSN, which are denied. They need to explicitly name the columns they're selecting:

-- This fails for BillingAnalyst (includes denied columns via *)
SELECT * FROM dbo.PatientBilling;

-- This succeeds, and DDM applies to the remaining sensitive columns
SELECT
    BillingID,
    PatientID,
    PatientFirstName,
    DateOfBirth,
    CardLastFour,
    Email,
    BillingAmount,
    ServiceDate,
    DiagnosisCode,
    ProviderID
FROM dbo.PatientBilling;

For the second query, what a BillingAnalyst sees after DDM applies looks like this:

BillingID	PatientID	PatientFirstName	DateOfBirth	CardLastFour	Email	BillingAmount
1001	PAT-00123	SXXXXXXXX	1900-01-01	XX12	aXXX@email.com	1250.00
1002	PAT-00124	JXXXXXXXX	1900-01-01	XX87	aXXX@email.com	3400.50

Granting UNMASK Permission

Privileged users who need to see real values — your compliance team, your security auditors, the DBA — need the UNMASK permission. In Fabric Warehouse, UNMASK can be granted at the database, schema, table, or column level:

-- Create a privileged role
CREATE ROLE ComplianceOfficer;
GRANT SELECT ON dbo.PatientBilling TO ComplianceOfficer;

-- Grant UNMASK at the table level (they see real values for all masked columns)
GRANT UNMASK ON dbo.PatientBilling TO ComplianceOfficer;

-- For SSN and FullCardNumber, also need to override the DENY
-- DENY takes precedence over GRANT, so we grant to ComplianceOfficer explicitly
GRANT SELECT ON dbo.PatientBilling (SSN) TO ComplianceOfficer;
GRANT SELECT ON dbo.PatientBilling (FullCardNumber) TO ComplianceOfficer;

---

Designing the Security View Layer

Rather than asking every downstream consumer to remember which columns they're allowed to select, the professional pattern is to create purpose-built views that pre-filter to the appropriate column set. This design has several benefits: it makes query authoring simpler, it moves the security logic into one maintainable place, and it gives Power BI a clean schema to point at.

-- View for billing analysts: no SSN, no FullCardNumber, DDM applies to remaining sensitive fields
CREATE VIEW dbo.vw_PatientBilling_Analyst AS
SELECT
    BillingID,
    PatientID,
    PatientFirstName,     -- DDM: partial masking applied
    PatientLastName,      -- No mask: last name considered non-PII in this policy
    DateOfBirth,          -- DDM: default mask applied
    CardLastFour,         -- DDM: partial mask applied
    Email,                -- DDM: email mask applied
    BillingAmount,
    ServiceDate,
    DiagnosisCode,
    ProviderID
FROM dbo.PatientBilling;

-- View for compliance officers: all columns visible (UNMASK grants still apply separately)
CREATE VIEW dbo.vw_PatientBilling_Full AS
SELECT * FROM dbo.PatientBilling;

Grant access to views rather than to the base table for most users:

-- Revoke direct table access for BillingAnalyst (they use the view instead)
REVOKE SELECT ON dbo.PatientBilling FROM BillingAnalyst;

-- Grant access to the analyst view only
GRANT SELECT ON dbo.vw_PatientBilling_Analyst TO BillingAnalyst;

This creates a clean, manageable security model: BillingAnalyst can only see what the view exposes, the view excludes the most sensitive columns entirely, and DDM handles graceful degradation of the remaining fields.

---

The Direct Lake Problem: Why Your Masking Won't Apply There

Here's where the architecture gets genuinely tricky, and where many implementations fail. If you're building Power BI reports using Direct Lake mode, you need to understand a fundamental constraint: Direct Lake reads Delta Parquet files directly from OneLake, bypassing the Fabric Warehouse SQL engine entirely.

That means your DDM configuration, your column-level DENY statements, and your T-SQL views have zero effect on a Direct Lake semantic model that points at the underlying Delta table. The semantic model's service identity reads the raw Parquet files, and masking is a SQL engine feature — it has nothing to enforce at the file layer.

This isn't a bug; it's a consequence of Direct Lake's architecture. Direct Lake's performance advantage comes precisely from skipping the SQL translation layer. That same skip also skips your SQL-layer security controls.

You have three architectural responses to this problem:

Option 1: Don't use Direct Lake for sensitive tables

If a table contains sensitive columns that require masking, serve it via DirectQuery against the Warehouse endpoint or via Import mode instead of Direct Lake. DirectQuery hits the SQL engine, so your DDM and column permissions apply. Import mode copies only what the semantic model's data refresh query retrieves, so you can restrict what gets imported by writing a restrictive refresh query.

The trade-off is performance. Direct Lake is significantly faster than DirectQuery for large datasets. For tables where most columns are non-sensitive and only a few need masking, this feels like overkill.

Option 2: Use Direct Lake against a masked/filtered view in the Lakehouse SQL Analytics Endpoint

Fabric Warehouse and the Lakehouse SQL Analytics Endpoint both expose Delta tables. If you create a view in the Lakehouse SQL Analytics Endpoint that projects only the non-sensitive columns, and then point your Direct Lake semantic model at that view, the semantic model never loads the sensitive columns.

However, as of current Fabric capabilities, Direct Lake works best against Delta tables directly, not views. Views in the SQL Analytics Endpoint are read through the SQL engine (effectively DirectQuery fallback), negating the performance benefit.

Option 3: Store a pre-masked version of the table in OneLake

This is the architecturally cleanest solution for high-performance reporting on large datasets. During your data transformation pipeline — in your gold layer — you create two versions of the sensitive table:

• PatientBilling_Full: the complete table with all sensitive fields, accessible only to privileged identities
• PatientBilling_Reporting: a version with sensitive columns permanently removed or pre-hashed, suitable for Direct Lake consumption by reporting users

This isn't DDM in the traditional sense — it's data-at-rest separation. The reporting table literally doesn't contain SSNs. No masking bypass is possible because the data isn't there. The cost is data duplication and the need to keep both tables in sync.

Here's a PySpark pattern for creating the pre-masked reporting table in your gold layer transformation pipeline:

from pyspark.sql import functions as F

# Read the full sensitive table
df_full = spark.read.format("delta").load(
    "abfss://workspace@onelake.dfs.fabric.microsoft.com/lakehouse.Lakehouse/Tables/PatientBilling_Full"
)

# Project only reporting-safe columns, applying hash to PatientID for linkage without exposure
df_reporting = df_full.select(
    "BillingID",
    F.sha2(F.col("PatientID"), 256).alias("PatientID_Hash"),  # pseudonymization
    "BillingAmount",
    "ServiceDate",
    "DiagnosisCode",
    "ProviderID"
    # SSN, FullCardNumber, DateOfBirth, Email, Name columns are intentionally omitted
)

# Write to a separate reporting table
df_reporting.write.format("delta").mode("overwrite").save(
    "abfss://workspace@onelake.dfs.fabric.microsoft.com/lakehouse.Lakehouse/Tables/PatientBilling_Reporting"
)

Then point your Direct Lake semantic model at PatientBilling_Reporting. Since the sensitive fields were never written there, no masking or access control at the SQL layer is needed to protect them — they simply don't exist in that table.

For more on building your gold layer transformation logic, see Implementing the Medallion Architecture in Microsoft Fabric and Building a Star Schema in a Fabric Lakehouse Gold Layer.

---

Column-Level Security for the SQL Analytics Endpoint

If your sensitive data lives in a Lakehouse rather than a Fabric Warehouse, the security model is different — and more limited. The SQL Analytics Endpoint supports views and RLS policies but as of current Fabric capabilities does not support DDM or column-level DENY in the same way a full Fabric Warehouse does.

For Lakehouse-based sensitive data, your primary options are:

1. Views in the SQL Analytics Endpoint: Create views that project only safe columns. Users who connect through the SQL endpoint see only what the view exposes. Users who can read OneLake files directly bypass this entirely.

2. OneLake access control: Restrict which identities can access the Lakehouse at the storage level. This is the only control that also protects against direct file access.

3. Workspace-level separation: Store sensitive data in a separate, restricted Lakehouse. This follows the principle of least privilege at the architecture level — sensitive data lives in an isolated workspace with tight access controls, while the reporting workspace contains only pre-processed, safe data.

The practical implication is that for column-level masking enforcement at the SQL query layer, the Fabric Warehouse is the right tool, not the Lakehouse SQL Analytics Endpoint. If you're currently using the Lakehouse endpoint for sensitive data, consider whether the warehouse architecture gives you the control surface you need.

---

Testing Your Masking Configuration

Never trust your security configuration until you've tested it as a non-privileged user. Fabric provides two mechanisms for this.

EXECUTE AS USER

In the Fabric Warehouse SQL editor, you can impersonate a user to verify what they see:

-- Verify what a BillingAnalyst member sees
EXECUTE AS USER = 'analyst.user@yourcompany.com';

SELECT
    BillingID,
    PatientID,
    PatientFirstName,
    DateOfBirth,
    CardLastFour,
    Email,
    BillingAmount
FROM dbo.vw_PatientBilling_Analyst;

REVERT;  -- Always REVERT after testing

You should see masked values for PatientFirstName, DateOfBirth, CardLastFour, and Email. If you see real values, the user has UNMASK permission or is in a role that grants it.

-- Verify that the denied columns cause an error when accessed directly
EXECUTE AS USER = 'analyst.user@yourcompany.com';

-- This should fail with a permission error
SELECT SSN FROM dbo.PatientBilling;

REVERT;

Testing Column-Level Permission Inheritance

A common gotcha: users who are members of the workspace Admin or Member role may have elevated permissions that override your DDM and DENY configurations. Test with accounts that have only Viewer workspace role access and have been granted database-level access through the SQL endpoint specifically.

-- Inspect effective permissions for a given user
SELECT
    entity_name,
    subentity_name,
    permission_name,
    state_desc
FROM sys.database_permissions p
JOIN sys.database_principals pr ON p.grantee_principal_id = pr.principal_id
WHERE pr.name = 'BillingAnalyst'
ORDER BY entity_name, subentity_name;

---

Advanced Pattern: Hash-Based Pseudonymization for Join Keys

There's a common scenario that neither DDM nor column-level DENY handles elegantly: a column like PatientID that isn't sensitive on its own but becomes a re-identification vector when combined with other data sources. If analysts can join your billing table to an external dataset using PatientID, they may be able to reconstruct identities even without seeing SSNs or names.

The solution is pseudonymization via keyed hashing. Instead of exposing the raw PatientID, you expose a deterministic hash that preserves joinability within your controlled data estate but is meaningless outside it.

This is best implemented at the view layer using a secret salt stored in a Fabric-managed variable or encoded into a view definition:

-- Pseudonymizing view using SHA2 hashing
-- The salt should be a fixed secret value managed by your security team
CREATE VIEW dbo.vw_PatientBilling_Pseudonymized AS
SELECT
    BillingID,
    -- Deterministic hash: same PatientID always produces the same hash
    -- Analysts can GROUP BY or JOIN on this without knowing the real ID
    CONVERT(VARCHAR(64),
        HASHBYTES('SHA2_256', CONCAT('YourSaltValue2024-', PatientID)), 2
    ) AS PatientID_Pseudonym,
    PatientFirstName,     -- DDM still applies
    DateOfBirth,          -- DDM still applies
    BillingAmount,
    ServiceDate,
    DiagnosisCode,
    ProviderID
FROM dbo.PatientBilling;

This pattern is particularly valuable in multi-team environments where your analytics team needs to build cohort models that join billing to clinical outcomes, but the clinical data team doesn't want billing analysts to hold real patient identifiers. Both teams expose pseudonymized IDs using the same salt and can join their datasets without either team exposing their raw identifiers to the other.

---

Hands-On Exercise

Work through this exercise to validate your understanding of the full implementation.

Setup: In a Fabric Warehouse (you'll need a workspace with at least a trial capacity — see Fabric Capacities and Workspaces if you need to set one up), create the PatientBilling table and insert the sample rows from earlier in this lesson.

Step 1: Apply DDM to all sensitive columns using the masking functions defined in this lesson. Verify via sys.masked_columns that all six target columns show is_masked = 1.

Step 2: Create the roles BillingAnalyst and ComplianceOfficer. Grant SELECT on the table to both roles. Apply DENY SELECT on SSN and FullCardNumber for BillingAnalyst. Grant UNMASK on the table for ComplianceOfficer.

Step 3: Create both views (vw_PatientBilling_Analyst and vw_PatientBilling_Full). Revoke direct table access from BillingAnalyst and grant them access only to vw_PatientBilling_Analyst.

Step 4: Test using EXECUTE AS. Run the analyst view query as a BillingAnalyst user (use your own email if you've added yourself to that role). Confirm you see masked values for PatientFirstName, DateOfBirth, CardLastFour, and Email. Confirm that attempting to SELECT SSN FROM dbo.PatientBilling raises a permission error.

Step 5: Test as ComplianceOfficer. Using EXECUTE AS, run SELECT * FROM dbo.vw_PatientBilling_Full. Confirm you see unmasked values for all columns including SSN and FullCardNumber.

Step 6 (Advanced): Create the pseudonymized view using HASHBYTES. Run it as a BillingAnalyst and verify that the PatientID_Pseudonym column returns a 64-character hex string rather than the raw PAT-XXXXX values. Verify that the same PatientID always produces the same hash (run the query twice and compare the pseudonym columns).

Expected outcome: A BillingAnalyst can query aggregate billing amounts, service dates, and diagnosis codes without ever seeing a real SSN, date of birth, or card number. A ComplianceOfficer running the full view sees everything unmasked.

---

Common Mistakes & Troubleshooting

Mistake 1: Applying DDM to a column but forgetting to restrict UNMASK

After you define a masking function, any user with the UNMASK permission on the table or database sees real values. This permission is granted to db_owner by default. If your data engineers hold db_owner on the warehouse database — which is common — they can bypass all masking. Audit who holds db_owner and whether they need it, or use granular schema-level permissions instead.

-- Identify who holds UNMASK permission
SELECT
    pr.name AS principal_name,
    p.state_desc,
    p.permission_name
FROM sys.database_permissions p
JOIN sys.database_principals pr ON p.grantee_principal_id = pr.principal_id
WHERE p.permission_name = 'UNMASK';

Mistake 2: Assuming DDM applies to Spark reads

A developer writes a PySpark notebook that reads the PatientBilling Delta table from OneLake. They're a member of BillingAnalyst. They expect masking to apply. It doesn't — not even slightly. Spark reads Parquet files directly, completely outside the SQL masking layer. If this developer's Spark identity has access to the OneLake path, they see raw data.

The fix: Restrict OneLake path access through workspace roles and OneLake RBAC so that only privileged identities can read the sensitive table's Delta files directly. Everyone else must go through the SQL endpoint. This requires careful workspace design — you may want to store highly sensitive tables in a dedicated restricted workspace rather than co-locating them with your general analytics lakehouse.

Mistake 3: Column-level DENY breaking existing views and stored procedures

If you apply DENY SELECT on SSN for the BillingAnalyst role, and you have an existing view that includes SSN in its definition, any BillingAnalyst trying to query that view will get a permission error — even if the view is intended to mask SSN. The DENY applies at the base table column level, so selecting from a view that references that column still triggers the DENY.

The resolution: revoke SELECT on the base table from the role and grant SELECT only on views that you've explicitly designed to exclude the denied columns. Never grant both base table access and view access to the same non-privileged role unless the base table permissions are carefully scoped.

Mistake 4: Trusting workspace role membership as a security boundary without SQL permission configuration

Fabric workspace roles (Admin, Member, Contributor, Viewer) control access to Fabric items and workspaces, but they don't directly translate to database-level permissions in the Warehouse SQL engine. A Viewer who is added as a SQL user and granted db_owner will see everything regardless of their workspace role. Treat SQL permissions and workspace roles as independent planes that both need to be configured correctly.

Mistake 5: Not accounting for EXECUTE AS and security context in service principals

If your data pipeline or Power BI semantic model connects to the Warehouse using a service principal, that service principal's SQL permissions determine what masking applies — not the end user's identity. A service principal with db_owner will see unmasked data even if the downstream report is consumed by a low-privileged user. For true end-user-identity-based masking in Power BI DirectQuery mode, you need to configure RLS in the Power BI semantic model or use per-user credentials through the gateway — not a shared service principal connection.

---

Understanding Performance Implications of DDM

DDM is not free at query time. When a masked column is included in a query, the SQL engine must:

1. Read the actual value from storage (the same read it would do unmasked)
2. Apply the masking function in the execution plan
3. Return the masked value

The masking computation itself is cheap — it's string manipulation or type substitution. But there's a subtler cost: masking prevents certain query optimizations. If a user queries WHERE SSN = '412-77-9023' and SSN is masked, the SQL engine must still evaluate the predicate against real data before returning a masked result. The user can't see the SSN, but they can infer from a non-empty result set that an SSN exists that matches their search string.

This is a known information leakage vector of DDM. If protecting the existence of a value (not just its content) is a requirement, you need column-level DENY rather than DDM. With DENY, the WHERE clause on a denied column generates a permission error — the user can't even form the predicate.

For tables where most queries are aggregation and grouping (which is most data warehouse usage), DDM overhead is negligible. For point-lookup workloads or high-cardinality column filtering, test query plans explicitly to ensure you're not inadvertently giving analysts a re-identification tool through targeted predicate queries.

---

Integrating with Fabric's Broader Governance Ecosystem

Your column-level security implementation sits within a broader governance picture. Microsoft Purview integrates with Microsoft Fabric to provide data classification, sensitivity label propagation, and audit logging. When you classify a column in Purview as "Highly Confidential - PII," that classification can drive:

• Sensitivity labels that follow the data as it moves through pipelines
• Access review alerts when unclassified users access labeled columns
• Audit log entries in Purview for every access to labeled data

This doesn't replace your DDM and DENY configuration — Purview classification is metadata, not enforcement. But it gives your compliance team visibility into who accessed what across your entire data estate, which is essential for GDPR, HIPAA, and similar regulatory frameworks.

Configure sensitivity labels at the column level in the Fabric portal by navigating to your Warehouse, opening the data management settings, and applying Purview labels to individual tables or columns. These labels propagate to Power BI reports that consume the labeled data, surfacing as report-level sensitivity banners for end users.

---

Summary & Next Steps

Implementing column-level security in a Fabric Warehouse requires understanding that you're operating across multiple layers: the SQL engine masking layer (where DDM and DENY live), the OneLake storage layer (where Direct Lake and Spark operate), and the semantic model layer (where Power BI applies its own security controls). Each layer needs deliberate configuration, and no single control is sufficient alone.

The architecture that works at scale is:

1. DDM for graceful degradation of sensitive columns for SQL-layer users who need to see the column but not the raw value
2. Column-level DENY for hard enforcement on columns that should never be visible to certain roles
3. Purpose-built views to give downstream consumers a clean, correctly-scoped schema to query against
4. Data-at-rest separation in OneLake (pre-masked reporting tables) for Direct Lake semantic models where the SQL layer is bypassed
5. OneLake RBAC to ensure that file-level access is restricted so that bypassing the SQL layer doesn't circumvent your controls
6. Purview classification for audit visibility and regulatory compliance documentation

From here, deepen your security posture with row-level security to complement column-level controls — the two are complementary, not substitutes. Review how workspace roles and item permissions layer onto SQL permissions, and ensure your deployment pipeline promotes security objects correctly across dev/test/prod environments. If you're using deployment pipelines for promotion, check out Fabric Git Integration and Deployment Pipelines to ensure your DDM configuration and security views travel with your schema changes rather than drifting between environments.