The short answer
Enterprise data warehouses store sensitive personally identifiable information () and financial records. Meeting compliance frameworks (such as GDPR, HIPAA, or SOC2) requires restricting access without creating duplicate sanitized tables for different user tiers.
Azure Synapse Dedicated Pool provides two complementary security controls: Dynamic Data Masking (DDM) and Row-Level Security (RLS).
Dynamic Data Masking (DDM)
DDM obfuscates sensitive data at the column level on the fly as queries execute, without altering the underlying data on disk.
ALTER TABLE dbo.dim_customers
ALTER COLUMN credit_card_number
ADD MASKED WITH (FUNCTION = 'partial(0, "XXXX-XXXX-XXXX-", 4)');
ALTER TABLE dbo.dim_customers
ALTER COLUMN email
ADD MASKED WITH (FUNCTION = 'email()');When an authorized security officer queries the table, they see real data. When a standard business analyst or data scientist queries the table, they see masked strings (XXXX-XXXX-XXXX-1234 and [email protected]).
Row-Level Security (RLS)
RLS restricts which rows a user can see based on security defined in inline table-valued functions.
CREATE FUNCTION sec.fn_securitypredicate(@Region AS VARCHAR(50))
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS fn_securitypredicate_result
WHERE @Region = USER_NAME() OR IS_MEMBER('Executive_Global_Role') = 1;
CREATE SECURITY POLICY RegionFilterPolicy
ADD FILTER PREDICATE sec.fn_securitypredicate(region)
ON dbo.fact_sales
WITH (STATE = ON);When regional managers query dbo.fact_sales, the security policy automatically filters out rows from other geographic territories before scanning blocks, providing transparent multi-tenant data isolation within a single shared table.
Combining masking with row filtering
In production financial environments, teams often combine both features on the same table. For instance, a regional manager sees only transactions occurring within their state, and within those visible rows, customer credit card numbers and banking routing tokens are dynamically masked. This provides layered security without writing bespoke sanitized views for every role.