Implementing Dynamic Data Masking and Anonymization Pipelines for GDPR and CCPA Compliance in dbt and Snowflake

Introduction

Your e-commerce analytics platform processes 50 million customer records. Your data science team needs email addresses for cohort analysis, your marketing analysts need demographic data for segmentation, your customer support team needs to look up individual users, and your compliance officer just informed you that three of those teams shouldn't be seeing raw PII at all — and that a regulator may want proof of that tomorrow. Welcome to one of the most operationally complex problems in modern data engineering.

GDPR and CCPA don't just ask you to promise that you protect personal data. They require you to demonstrate, with auditable evidence, that access to personal information is systematically controlled, that data is retained only as long as necessary, and that individuals can exercise their right to erasure or access. In a warehouse-centric architecture where you've embraced the ELT model and your transformation logic lives in dbt, those requirements land squarely in your data engineering pipeline. You can't bolt compliance onto your existing system after the fact without paying a painful technical debt penalty.

This lesson will teach you how to design and implement a production-grade dynamic data masking and anonymization pipeline that integrates Snowflake's native security primitives with dbt's transformation framework. You won't just learn the mechanics — you'll understand the architectural trade-offs that determine whether your compliance controls are genuinely robust or just theater. By the end, you'll have built a system where the right people see the right data, every access decision is logged, and you can respond to a subject access request or deletion demand within hours rather than weeks.

What you'll learn:

• The regulatory distinction between masking, pseudonymization, and anonymization, and why it matters for your architecture
• How Snowflake's Dynamic Data Masking and Row Access Policies work at the engine level, and how to manage them at scale
• How to build reusable, policy-driven dbt macros that encode compliance logic directly into your transformation layer
• How to implement a deletion propagation pipeline that satisfies GDPR Article 17 ("right to erasure") without destroying analytical value
• How to design an auditable access control system that connects Snowflake RBAC, column-level policies, and dbt model permissions

---

Prerequisites

You should be comfortable with:

• SQL and Jinja templating in dbt — if you're new to dbt, start with the Introduction to dbt (Data Build Tool) lesson first
• Snowflake's role-based access control fundamentals. If RBAC is new territory, read Configuring Role-Based Access Control in Snowflake and BigQuery before continuing
• Basic understanding of data warehouse architecture and ELT patterns
• Python or SQL at an intermediate-to-advanced level
• Familiarity with GDPR and CCPA at a conceptual level (what constitutes PII, data subject rights, data controller/processor distinctions)

---

Regulatory Foundations: What the Law Actually Requires of Your Pipeline

Before writing a single macro, you need to understand exactly what GDPR and CCPA mandate at the data layer — because conflating these requirements leads to architectures that satisfy lawyers but fail engineers, or vice versa.

The Three Distinct Technical States of Personal Data

Regulators and data engineers use "anonymization" loosely, but the technical and legal distinctions carry enormous consequences.

Pseudonymization replaces direct identifiers (like an email address) with a pseudonym (like a hash or UUID) that can be reversed if you have the key. Under GDPR Recital 26, pseudonymized data is still considered personal data because re-identification is possible. Your pseudonymized customer ID table is still in scope. This is the most common technique in analytics pipelines and it reduces risk without eliminating regulatory obligations.

Masking hides or replaces data at query time or at rest, but the original value remains stored and recoverable. Dynamic Data Masking in Snowflake is pseudonymization at the query layer — the underlying data is untouched. Masking is an access control tool, not an anonymization technique.

True anonymization irreversibly removes the ability to identify an individual, including through combination with other datasets. GDPR Recital 26 exempts truly anonymous data from regulation. In practice, achieving true anonymization for anything beyond aggregate statistics is extremely difficult — a 2019 Nature study demonstrated that 99.98% of Americans can be re-identified from just 15 demographic attributes. The bar is high.

Key insight: Most compliance architectures should combine all three: Snowflake Dynamic Data Masking for access control at the query layer, dbt-driven pseudonymization for analytical use cases, and genuine anonymization only for fully aggregated, low-cardinality outputs like summary dashboards.

GDPR-Specific Requirements That Drive Architecture

Article 5(1)(e) — Storage Limitation: You must not retain data longer than necessary. This forces you to implement time-based deletion pipelines, not just masking.

Article 17 — Right to Erasure ("Right to be Forgotten"): Users can request deletion of their personal data. Your pipeline must be able to propagate a deletion from a source CRM or application database through your entire warehouse and all derived models.

Article 25 — Data Protection by Design: Privacy controls must be built into the system architecture, not added as an afterthought. Regulators have specifically cited production ELT pipelines where raw PII lands in staging tables accessible to analysts as violations of this principle.

Article 30 — Records of Processing: You must maintain documented records of what personal data you process, why, and who has access. Your dbt model documentation and Snowflake audit logs directly serve this purpose.

CCPA Section 1798.100 — Right to Know: Consumers can request a full accounting of personal information collected about them. Your pipeline needs to be able to answer that query efficiently.

Warning: Many data teams interpret GDPR as "hash your emails and you're done." Auditors and regulators are increasingly sophisticated. A masking policy that doesn't survive a SQL UNION attack, or a pseudonymization key that lives in the same database as the data it protects, will not hold up under scrutiny.

---

Architecture Overview: Layers of a Compliant Pipeline

A production-grade compliance architecture for Snowflake and dbt operates across four distinct layers, each with its own responsibility:

┌─────────────────────────────────────────────────────┐
│  Layer 4: Audit & Lineage                           │
│  Snowflake Access History + dbt docs + OpenLineage  │
├─────────────────────────────────────────────────────┤
│  Layer 3: Access Control                            │
│  Snowflake Dynamic Data Masking + Row Access Policies│
│  RBAC Roles + dbt post-hooks                        │
├─────────────────────────────────────────────────────┤
│  Layer 2: Transformation & Pseudonymization         │
│  dbt staging/intermediate models + Jinja macros     │
│  Deterministic hashing + tokenization               │
├─────────────────────────────────────────────────────┤
│  Layer 1: Ingestion & Raw Storage                   │
│  Snowflake RAW database + network policies          │
│  Encrypted at rest, access-restricted               │
└─────────────────────────────────────────────────────┘

The key architectural principle is defense in depth. No single layer is sufficient. If your dbt transformation layer pseudonymizes emails in the mart, but raw data in the staging database is accessible to the analytics role, your compliance posture is compromised at the source.

---

Setting Up the Snowflake Foundation: Databases, Roles, and Network Policies

Let's build this from the ground up. We'll use a realistic scenario: a SaaS company processing customer subscription and behavioral data.

Database Architecture

The first decision is database segregation. Raw PII should live in a separate database from transformed data, with separately managed access controls.

-- Create isolated database structure
CREATE DATABASE raw_pii
  DATA_RETENTION_TIME_IN_DAYS = 1  -- Minimize retention in raw layer
  COMMENT = 'Ingested source data containing PII. Restricted access.';

CREATE DATABASE analytics
  DATA_RETENTION_TIME_IN_DAYS = 7
  COMMENT = 'Transformed analytics data. Masking policies applied.';

CREATE DATABASE analytics_gdpr_deleted
  DATA_RETENTION_TIME_IN_DAYS = 1
  COMMENT = 'Staging area for deletion propagation. 24hr TTL.';

-- Schema structure within analytics
CREATE SCHEMA analytics.staging;
CREATE SCHEMA analytics.intermediate;
CREATE SCHEMA analytics.marts;
CREATE SCHEMA analytics.compliance;  -- Dedicated compliance utilities schema

Role Hierarchy for Compliance

-- Functional roles (assigned based on job function)
CREATE ROLE pii_reader COMMENT = 'Can read raw PII. Restricted to specific teams.';
CREATE ROLE pii_masked_reader COMMENT = 'Sees masked PII. Standard analyst access.';
CREATE ROLE dbt_transformer COMMENT = 'Used by dbt service account.';
CREATE ROLE compliance_admin COMMENT = 'Manages masking/row access policies.';
CREATE ROLE data_engineer COMMENT = 'Full access to non-PII schemas.';

-- Role hierarchy
GRANT ROLE pii_masked_reader TO ROLE data_engineer;
GRANT ROLE data_engineer TO ROLE dbt_transformer;
GRANT ROLE pii_reader TO ROLE compliance_admin;

-- Grant privileges
GRANT USAGE ON DATABASE raw_pii TO ROLE dbt_transformer;
GRANT USAGE ON ALL SCHEMAS IN DATABASE raw_pii TO ROLE dbt_transformer;
GRANT SELECT ON ALL TABLES IN DATABASE raw_pii TO ROLE dbt_transformer;

GRANT USAGE ON DATABASE analytics TO ROLE pii_masked_reader;
GRANT USAGE ON ALL SCHEMAS IN DATABASE analytics TO ROLE pii_masked_reader;
GRANT SELECT ON ALL TABLES IN DATABASE analytics TO ROLE pii_masked_reader;

Note: The dbt_transformer role needs read access to raw PII to run transformations, but it should not be granted to human users. Your dbt service account authenticates with this role, and all transformation logic should treat it as a system account, not a user role.

---

Snowflake Dynamic Data Masking: How It Actually Works

Dynamic Data Masking (DDM) operates at the column level. You create a masking policy — essentially a SQL function — and attach it to a column in a table or view. Snowflake evaluates the policy at query execution time, before results reach the client. The underlying storage is never modified.

The critical mechanism is CURRENT_ROLE() and IS_ROLE_IN_SESSION(). The masking policy checks the querying user's active role and returns either the real value or a masked substitute.

Creating Your First Masking Policy

-- Switch to compliance admin role for policy management
USE ROLE compliance_admin;

-- Email masking policy
CREATE OR REPLACE MASKING POLICY analytics.compliance.mask_email
  AS (val STRING) RETURNS STRING ->
  CASE
    WHEN IS_ROLE_IN_SESSION('pii_reader') THEN val
    WHEN IS_ROLE_IN_SESSION('pii_masked_reader') THEN
      REGEXP_REPLACE(val, '^(.)(.*?)(@.*)$', '\\1***\\3')
    ELSE '***MASKED***'
  END;

-- SSN masking (show last 4 only for authorized roles)
CREATE OR REPLACE MASKING POLICY analytics.compliance.mask_ssn
  AS (val STRING) RETURNS STRING ->
  CASE
    WHEN IS_ROLE_IN_SESSION('pii_reader') THEN val
    WHEN IS_ROLE_IN_SESSION('pii_masked_reader') THEN
      CONCAT('***-**-', RIGHT(val, 4))
    ELSE NULL
  END;

-- Phone number masking
CREATE OR REPLACE MASKING POLICY analytics.compliance.mask_phone
  AS (val STRING) RETURNS STRING ->
  CASE
    WHEN IS_ROLE_IN_SESSION('pii_reader') THEN val
    WHEN IS_ROLE_IN_SESSION('pii_masked_reader') THEN
      CONCAT(LEFT(val, 3), '***', RIGHT(val, 2))
    ELSE NULL
  END;

-- Date of birth masking (return only year for masked readers)
CREATE OR REPLACE MASKING POLICY analytics.compliance.mask_dob
  AS (val DATE) RETURNS DATE ->
  CASE
    WHEN IS_ROLE_IN_SESSION('pii_reader') THEN val
    WHEN IS_ROLE_IN_SESSION('pii_masked_reader') THEN
      DATE_TRUNC('year', val)
    ELSE NULL
  END;

The email policy above is worth examining closely. For a pii_masked_reader, the regex preserves the first character, replaces the local part with ***, and keeps the domain intact. So john.doe@example.com becomes j***@example.com. This is a common analytics requirement — you want to verify email domain distributions without exposing full addresses.

Applying Policies to Columns

-- Apply masking policies to the customer dimension table
ALTER TABLE analytics.marts.dim_customers
  MODIFY COLUMN email
  SET MASKING POLICY analytics.compliance.mask_email;

ALTER TABLE analytics.marts.dim_customers
  MODIFY COLUMN phone_number
  SET MASKING POLICY analytics.compliance.mask_phone;

ALTER TABLE analytics.marts.dim_customers
  MODIFY COLUMN date_of_birth
  SET MASKING POLICY analytics.compliance.mask_dob;

Warning: DDM policies applied to tables are not automatically inherited by views built on those tables in Snowflake — unless the view uses SECURE VIEW. If you build an unprotected view on top of a masked table, users can sometimes bypass masking by querying the view with metadata functions. Always use CREATE SECURE VIEW for views that expose masked columns, and test this explicitly during security reviews.

Conditional Masking: Context-Aware Policies

Real-world scenarios often require more nuance. A customer support agent should see full customer details when they're actively working a ticket, but not when running bulk analytical queries. Snowflake supports session context variables for this purpose.

-- Context-aware masking using session policy
CREATE OR REPLACE MASKING POLICY analytics.compliance.mask_email_context_aware
  AS (val STRING) RETURNS STRING ->
  CASE
    WHEN IS_ROLE_IN_SESSION('pii_reader') THEN val
    WHEN SYSTEM$GET_TAG_ON_CURRENT_TABLE('pii_classification') = 'SUPPORT_VISIBLE'
      AND IS_ROLE_IN_SESSION('support_agent') THEN val
    WHEN IS_ROLE_IN_SESSION('pii_masked_reader') THEN
      REGEXP_REPLACE(val, '^(.)(.*?)(@.*)$', '\\1***\\3')
    ELSE '***MASKED***'
  END;

---

Building dbt Macros for Systematic PII Management

Here's where the dbt layer becomes essential. Without systematic macro-driven PII handling, your compliance logic gets scattered across hundreds of model files — impossible to audit and easy to miss. The goal is to centralize masking and pseudonymization logic in reusable macros that model authors call explicitly, making PII handling visible in the codebase.

This is closely related to the principles in Building a Multi-Layer dbt Project with Staging, Intermediate, and Mart Layers — compliance controls should be applied consistently at the right layer, not sprinkled arbitrarily.

The `mask_pii` Macro Family

Create a dedicated macros/compliance/ directory in your dbt project:

macros/
  compliance/
    mask_pii.sql
    hash_pii.sql
    apply_masking_policy.sql
    tag_pii_columns.sql
    generate_deletion_filter.sql

-- macros/compliance/hash_pii.sql
{% macro hash_pii(column_name, salt_secret='PII_HASH_SALT') %}
  {#-
    Produces a deterministic, one-way hash of a PII column.
    The salt is pulled from Snowflake secrets, not hardcoded.
    Use this for pseudonymization in analytical models.
  -#}
  SHA2(
    CONCAT(
      {{ column_name }},
      '::',
      {{ "'" ~ salt_secret ~ "'" }}
    ),
    256
  )
{% endmacro %}

Wait — hardcoding a salt reference is a mistake. In production, you want to pull the salt from Snowflake Secrets Manager or an environment variable:

-- macros/compliance/hash_pii.sql (production version)
{% macro hash_pii(column_name, use_secret=true) %}
  {#-
    Deterministic SHA-256 hash with externally managed salt.
    Salt is fetched from Snowflake secret at query time.
    This means the hash is consistent for the same input,
    enabling joins and cohort analysis without exposing PII.
  -#}
  {% if use_secret %}
    SHA2(
      CONCAT(
        COALESCE(CAST({{ column_name }} AS STRING), ''),
        '::',
        $pii_hash_salt  -- Snowflake session variable set by wrapper procedure
      ),
      256
    )
  {% else %}
    SHA2(COALESCE(CAST({{ column_name }} AS STRING), ''), 256)
  {% endif %}
{% endmacro %}

Key insight: The salt is what makes your hashes non-reversible from the outside — without it, an attacker who obtains your hashed email list can pre-compute hashes for every known email address and reverse the mapping. Store your salt in Snowflake Secrets or AWS Secrets Manager, never in your dbt project repository or environment variables that appear in CI logs.

The `apply_masking_policy` Post-Hook Macro

This macro is the linchpin of your dbt integration. It runs as a post-hook and programmatically applies Snowflake masking policies to columns based on metadata you define in your model's meta block.

-- macros/compliance/apply_masking_policy.sql
{% macro apply_masking_policy() %}
  {#-
    Reads PII column metadata from this model's config and applies
    the appropriate Snowflake masking policy to each tagged column.
    Must be called as a post-hook.
  -#}

  {% set pii_columns = config.get('meta', {}).get('pii_columns', {}) %}

  {% if pii_columns %}
    {% for column_name, pii_type in pii_columns.items() %}
      {% set policy_name %}
        {% if pii_type == 'email' %}analytics.compliance.mask_email
        {% elif pii_type == 'phone' %}analytics.compliance.mask_phone
        {% elif pii_type == 'ssn' %}analytics.compliance.mask_ssn
        {% elif pii_type == 'dob' %}analytics.compliance.mask_dob
        {% elif pii_type == 'name' %}analytics.compliance.mask_name
        {% elif pii_type == 'address' %}analytics.compliance.mask_address
        {% else %}analytics.compliance.mask_generic
        {% endif %}
      {% endset %}

      ALTER TABLE {{ this }}
        MODIFY COLUMN {{ column_name }}
        SET MASKING POLICY {{ policy_name | trim }};

    {% endfor %}
  {% endif %}

{% endmacro %}

Now, any dbt model that exposes PII simply declares it in its config:

-- models/marts/dim_customers.sql
{{
  config(
    materialized='table',
    meta={
      'pii_columns': {
        'email': 'email',
        'phone_number': 'phone',
        'date_of_birth': 'dob',
        'full_name': 'name',
        'billing_address': 'address'
      },
      'data_classification': 'confidential',
      'gdpr_relevant': true,
      'ccpa_relevant': true,
      'retention_days': 730
    },
    post_hook="{{ apply_masking_policy() }}"
  )
}}

SELECT
  customer_id,
  {{ hash_pii('email') }} AS customer_hash,  -- pseudonymized for joins
  email,                                       -- original, will be masked by policy
  full_name,
  phone_number,
  date_of_birth,
  billing_address,
  subscription_tier,
  created_at,
  country_code
FROM {{ ref('stg_customers') }}
WHERE NOT is_deleted

This design is elegant for compliance auditing: a grep for pii_columns across your dbt project gives you a complete inventory of every model that processes personal data.

The `tag_pii_columns` Macro

Snowflake's Object Tagging feature lets you apply searchable metadata tags to columns. This is how you build your Article 30 Records of Processing inventory programmatically.

-- macros/compliance/tag_pii_columns.sql
{% macro tag_pii_columns() %}
  {% set pii_columns = config.get('meta', {}).get('pii_columns', {}) %}
  {% set gdpr_relevant = config.get('meta', {}).get('gdpr_relevant', false) %}
  {% set ccpa_relevant = config.get('meta', {}).get('ccpa_relevant', false) %}

  {% if pii_columns %}

    -- Tag the table itself
    ALTER TABLE {{ this }}
      SET TAG analytics.compliance.data_classification =
        '{{ config.get("meta", {}).get("data_classification", "internal") }}';

    {% if gdpr_relevant %}
      ALTER TABLE {{ this }}
        SET TAG analytics.compliance.gdpr_in_scope = 'true';
    {% endif %}

    {% if ccpa_relevant %}
      ALTER TABLE {{ this }}
        SET TAG analytics.compliance.ccpa_in_scope = 'true';
    {% endif %}

    -- Tag individual PII columns
    {% for column_name, pii_type in pii_columns.items() %}
      ALTER TABLE {{ this }}
        MODIFY COLUMN {{ column_name }}
        SET TAG analytics.compliance.pii_type = '{{ pii_type }}';
    {% endfor %}

  {% endif %}

{% endmacro %}

After a full dbt run, you can query your entire PII inventory with a single SQL statement:

-- Generate your GDPR Article 30 Records of Processing inventory
SELECT
  table_catalog,
  table_schema,
  table_name,
  column_name,
  tag_value AS pii_classification,
  created
FROM TABLE(
  analytics.information_schema.tag_references_all_columns(
    'analytics.compliance.pii_type',
    'column'
  )
)
ORDER BY table_schema, table_name, column_name;

This is your living Records of Processing document, always in sync with your actual pipeline state.

---

Pseudonymization in the Staging Layer

The staging layer is where raw PII first enters your dbt project. This is the right place to make the critical architectural decision: which columns get pseudonymized immediately (so they never appear as raw PII in downstream models), and which get passed through (with DDM policies applied at the mart layer).

The general principle: pseudonymize in staging for analytical use cases, pass through with masking for operational use cases.

-- models/staging/stg_customers.sql
{{
  config(
    materialized='view',
    meta={
      'pii_columns': {
        'email': 'email',
        'phone_number': 'phone'
      },
      'gdpr_relevant': true,
      'ccpa_relevant': true
    },
    post_hook=[
      "{{ apply_masking_policy() }}",
      "{{ tag_pii_columns() }}"
    ]
  )
}}

WITH source AS (
  SELECT * FROM {{ source('raw', 'customers') }}
),

cleaned AS (
  SELECT
    id                                        AS customer_id,
    -- Pseudonymized identifier for cross-system joins
    {{ hash_pii('LOWER(TRIM(email))') }}      AS customer_hash,
    -- Email preserved but masking policy applied post-hook
    LOWER(TRIM(email))                        AS email,
    -- Name normalization
    TRIM(first_name)                          AS first_name,
    TRIM(last_name)                           AS last_name,
    CONCAT(TRIM(first_name), ' ', TRIM(last_name)) AS full_name,
    -- Phone standardization before masking
    REGEXP_REPLACE(phone, '[^0-9]', '')       AS phone_number,
    -- DOB cast and validated
    TRY_CAST(date_of_birth AS DATE)           AS date_of_birth,
    -- Non-PII fields passed through normally
    subscription_tier,
    country_code,
    signup_channel,
    created_at,
    updated_at,
    -- Soft delete flag — critical for deletion propagation
    COALESCE(is_deleted, FALSE)               AS is_deleted,
    deleted_at
  FROM source
)

SELECT * FROM cleaned

Notice the customer_hash column. This SHA-256 hash of the normalized email becomes the stable analytical identifier that downstream models use for joins, cohort analysis, and funnel attribution. If a customer deletes their account, you can null out their PII while preserving the customer_hash as a tombstone — analysts can still count deletion events in their cohorts without seeing personal data.

---

Implementing the Right to Erasure Pipeline

This is the most technically complex compliance requirement. When a user submits a deletion request, you have 30 days (GDPR) or 45 days (CCPA) to process it, but your pipeline may have propagated that user's data across dozens of models, incremental materializations, and potentially external shares. You need a systematic deletion propagation mechanism.

For more on incremental model complexities, see Incremental Models at Scale: Strategies for Efficiently Processing Late-Arriving Data and Partition Pruning in dbt — the same principles that make incremental models efficient also make deletion propagation tricky.

The Deletion Manifest Pattern

Create a dedicated deletion manifest table that acts as the source of truth for all deletion requests:

-- In Snowflake directly (not managed by dbt)
CREATE TABLE analytics.compliance.deletion_requests (
  request_id        VARCHAR(36) DEFAULT UUID_STRING(),
  customer_hash     VARCHAR(64) NOT NULL,  -- SHA-256 hash
  request_source    VARCHAR(50) NOT NULL,  -- 'GDPR', 'CCPA', 'INTERNAL'
  requested_at      TIMESTAMP_NTZ DEFAULT CURRENT_TIMESTAMP(),
  deadline_at       TIMESTAMP_NTZ NOT NULL,
  completed_at      TIMESTAMP_NTZ,
  verified_by       VARCHAR(100),
  status            VARCHAR(20) DEFAULT 'PENDING',  -- PENDING, PROCESSING, COMPLETE, FAILED
  affected_tables   VARIANT,  -- JSON array of affected table names after processing
  PRIMARY KEY (request_id)
);

The `generate_deletion_filter` Macro

-- macros/compliance/generate_deletion_filter.sql
{% macro generate_deletion_filter(hash_column='customer_hash') %}
  {#-
    Generates a WHERE clause that excludes records for customers
    with pending or completed deletion requests.
    Used in incremental model refresh logic.
  -#}
  {{ hash_column }} NOT IN (
    SELECT customer_hash
    FROM analytics.compliance.deletion_requests
    WHERE status IN ('PROCESSING', 'COMPLETE')
  )
{% endmacro %}

Applying Deletion Logic to Incremental Models

-- models/marts/fct_subscription_events.sql
{{
  config(
    materialized='incremental',
    unique_key='event_id',
    on_schema_change='append_new_columns',
    incremental_strategy='merge',
    meta={
      'gdpr_relevant': true,
      'deletion_propagation': true
    }
  )
}}

SELECT
  event_id,
  customer_hash,  -- No raw PII in fact tables
  event_type,
  subscription_tier,
  revenue_usd,
  event_at,
  source_system
FROM {{ ref('stg_subscription_events') }}

{% if is_incremental() %}
  WHERE event_at > (SELECT MAX(event_at) FROM {{ this }})
    AND {{ generate_deletion_filter('customer_hash') }}
{% else %}
  -- Full refresh also excludes deleted customers
  WHERE {{ generate_deletion_filter('customer_hash') }}
{% endif %}

Warning: This pattern handles new records from deleted customers — they won't be added to your fact tables. But it does NOT remove existing records for recently deleted customers. You need a separate deletion propagation job that runs after the deletion request is registered and before your compliance deadline. Design this as a separate scheduled procedure, not as part of your regular dbt pipeline.

The Deletion Propagation Stored Procedure

-- Snowflake stored procedure for deletion propagation
CREATE OR REPLACE PROCEDURE analytics.compliance.propagate_deletion(
  p_customer_hash VARCHAR
)
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
AS $$
  var affectedTables = [];

  // Tables that store customer_hash and contain PII-adjacent data
  var tablesToPurge = [
    { table: 'analytics.marts.dim_customers', column: 'customer_hash', action: 'nullify_pii' },
    { table: 'analytics.marts.fct_subscription_events', column: 'customer_hash', action: 'delete' },
    { table: 'analytics.marts.fct_support_tickets', column: 'customer_hash', action: 'nullify_pii' },
    { table: 'analytics.intermediate.int_user_sessions', column: 'customer_hash', action: 'delete' }
  ];

  for (var i = 0; i < tablesToPurge.length; i++) {
    var t = tablesToPurge[i];
    var sql;

    if (t.action === 'delete') {
      sql = `DELETE FROM ${t.table} WHERE ${t.column} = '${p_customer_hash}'`;
    } else if (t.action === 'nullify_pii') {
      // Preserve the row for analytical integrity (counts, etc.)
      // but null out all PII columns
      sql = `UPDATE ${t.table}
             SET email = '[DELETED]',
                 full_name = '[DELETED]',
                 phone_number = NULL,
                 date_of_birth = NULL,
                 billing_address = NULL
             WHERE ${t.column} = '${p_customer_hash}'`;
    }

    snowflake.execute({ sqlText: sql });
    affectedTables.push(t.table);
  }

  // Mark deletion as complete
  var completionSql = `
    UPDATE analytics.compliance.deletion_requests
    SET status = 'COMPLETE',
        completed_at = CURRENT_TIMESTAMP(),
        affected_tables = PARSE_JSON('${JSON.stringify(affectedTables)}')
    WHERE customer_hash = '${p_customer_hash}'
      AND status = 'PROCESSING'
  `;
  snowflake.execute({ sqlText: completionSql });

  return 'Deletion propagated to ' + affectedTables.length + ' tables.';
$$;

The nullify_pii vs delete distinction is important here. For fact tables that contribute to revenue reporting and aggregate analytics, you may want to preserve the row count (so your total transaction counts remain accurate) while removing the personal data. For session and behavioral data, complete deletion is cleaner. This choice should be documented in your Records of Processing.

---

Auditing and Lineage: Proving Compliance

Having the technical controls in place is necessary but not sufficient. You need to be able to prove that those controls worked. This connects directly to the lineage tracking concepts in Multi-Hop Data Lineage Tracking Across the Modern Data Stack.

Snowflake Access History

Snowflake maintains a detailed ACCESS_HISTORY view in ACCOUNT_USAGE that records every query, which user ran it, which role they used, and which columns they accessed. For PII columns specifically, this is invaluable for compliance reporting.

-- Query to identify all access to PII-tagged columns in the last 30 days
WITH pii_column_references AS (
  SELECT
    qh.query_id,
    qh.user_name,
    qh.role_name,
    qh.query_start_time,
    f.value:objectName::VARCHAR AS accessed_object,
    col.value:columnName::VARCHAR AS accessed_column
  FROM snowflake.account_usage.access_history qh,
    LATERAL FLATTEN(input => qh.base_objects_accessed) f,
    LATERAL FLATTEN(input => f.value:columns) col
  WHERE qh.query_start_time >= DATEADD(day, -30, CURRENT_TIMESTAMP())
)

SELECT
  pcr.user_name,
  pcr.role_name,
  pcr.accessed_object,
  pcr.accessed_column,
  COUNT(*) AS access_count,
  MIN(pcr.query_start_time) AS first_access,
  MAX(pcr.query_start_time) AS last_access
FROM pii_column_references pcr
JOIN TABLE(
  analytics.information_schema.tag_references_all_columns(
    'analytics.compliance.pii_type',
    'column'
  )
) tagged ON UPPER(pcr.accessed_column) = UPPER(tagged.column_name)
  AND UPPER(pcr.accessed_object) LIKE '%' || UPPER(tagged.table_name)
GROUP BY 1, 2, 3, 4
ORDER BY access_count DESC;

This query is the foundation of your monthly PII access report. It answers: "Who accessed which personal data fields, and how often, in the last 30 days?"

Building a Compliance Dashboard Model in dbt

-- models/compliance/compliance_pii_access_summary.sql
-- This is a restricted model — only compliance_admin can query it
{{
  config(
    materialized='table',
    schema='compliance',
    meta={
      'access_roles': ['compliance_admin'],
      'description': 'Monthly PII access audit summary for GDPR Article 30 reporting'
    }
  )
}}

SELECT
  DATE_TRUNC('day', query_start_time)  AS access_date,
  user_name,
  role_name,
  accessed_object,
  accessed_column,
  COUNT(DISTINCT query_id)              AS query_count
FROM TABLE(
  {{ source('snowflake_account_usage', 'access_history') }}
)
-- filter and flatten logic here (simplified for readability)
WHERE query_start_time >= DATEADD(month, -3, CURRENT_TIMESTAMP())
GROUP BY 1, 2, 3, 4, 5

---

Managing Masking Policies at Scale with dbt Hooks and CI/CD

As your dbt project grows to dozens of models and your PII taxonomy expands, manual policy application becomes unmanageable. The solution is a fully automated policy-management macro that runs as part of your CI/CD pipeline.

This integrates naturally with the CI/CD patterns covered in Automating dbt Environment Promotion with CI/CD Pipelines, Slim CI, and State-Aware Deployments.

Policy Drift Detection

One dangerous failure mode is a masking policy that gets accidentally dropped or overwritten during a schema migration. Build a dbt test that verifies policy attachment:

-- tests/generic/has_masking_policy.sql
{% test has_masking_policy(model, column_name, expected_policy) %}

SELECT
  '{{ column_name }}' AS column_name,
  '{{ expected_policy }}' AS expected_policy,
  COALESCE(policy_name, 'NONE') AS actual_policy
FROM (
  SELECT pm.policy_name
  FROM TABLE(
    analytics.information_schema.policy_references(
      REF_ENTITY_NAME => '{{ model }}',
      REF_ENTITY_DOMAIN => 'TABLE'
    )
  ) pm
  WHERE LOWER(pm.ref_column_name) = LOWER('{{ column_name }}')
    AND pm.policy_kind = 'MASKING_POLICY'
)
WHERE actual_policy != '{{ expected_policy }}'

{% endtest %}

Apply it in your schema YAML:

# models/marts/schema.yml
models:
  - name: dim_customers
    description: "Customer dimension table with masked PII"
    columns:
      - name: email
        description: "Customer email. Masked for non-PII roles."
        tests:
          - has_masking_policy:
              column_name: email
              expected_policy: ANALYTICS.COMPLIANCE.MASK_EMAIL
          - not_null
      - name: phone_number
        tests:
          - has_masking_policy:
              column_name: phone_number
              expected_policy: ANALYTICS.COMPLIANCE.MASK_PHONE

This means that every dbt test run verifies that your masking policies are in place. A policy that gets accidentally dropped will fail your CI pipeline before it reaches production.

This testing approach builds on the fundamentals of Implementing dbt Tests and Data Quality Checks in Production Pipelines — extending data quality testing to encompass compliance controls, not just data validity.

Tip: Run dbt test --select tag:pii_column as a dedicated compliance check step in your CI pipeline, separate from your regular data quality tests. This makes it easy to report specifically on compliance test pass rates to your compliance team, and to set different alerting thresholds for compliance failures vs. data quality failures.

---

Hands-On Exercise

You've absorbed a lot of concepts. Let's tie them together with a structured exercise that mirrors a real-world compliance implementation.

Scenario: You're the data engineer at a subscription software company. Your Snowflake warehouse has a raw.app_db.users table with columns: id, email, full_name, phone, dob, plan_tier, signup_date, country. Marketing analysts need demographic and behavioral data, but must not see raw emails or phone numbers. Customer support agents need to see names and emails for active support tickets. Compliance requires full audit trails.

Step 1: Create the masking policies

Using the SQL patterns from this lesson, create masking policies for email, phone, and dob. Add a third visibility tier: a support_agent role that sees full name and email but not phone or DOB.

Step 2: Build the staging model

Create models/staging/stg_users.sql that:

• Produces a user_hash via hash_pii('LOWER(TRIM(email))')
• Normalizes the phone number
• Casts DOB to a DATE type
• Applies apply_masking_policy() and tag_pii_columns() as post-hooks
• Defines the pii_columns meta block

Step 3: Build the mart model

Create models/marts/dim_users.sql that:

• References stg_users
• Filters out deleted users using generate_deletion_filter()
• Includes non-PII enrichments from a stg_user_subscriptions source
• Applies DDM post-hooks

Step 4: Write compliance tests

Add has_masking_policy tests in your schema.yml for each PII column. Add a custom test that verifies no model in the marts schema exposes raw email without a masking policy attached (hint: query information_schema.policy_references).

Step 5: Simulate a deletion request

Insert a test customer. Run your deletion procedure against their customer_hash. Verify that:

• Their raw PII is nullified in dim_users
• Their user_hash still exists (tombstone)
• The deletion_requests table shows status = 'COMPLETE'
• Your compliance audit query shows no subsequent access to their data

---

Common Mistakes & Troubleshooting

Mistake 1: Applying DDM to views instead of tables, then building views on views

DDM policies on a base table don't automatically cascade through view chains. If view_b is built on view_a which is built on masked_table, and view_b is not a SECURE VIEW, users may be able to use metadata functions or inference attacks to recover masked values. Always use CREATE SECURE VIEW for any view that references masked columns, and regularly audit your view dependency chains.

Mistake 2: Hashing without salting

Unsalted SHA-256 hashes of common values (email addresses, phone numbers) are trivially reversible via pre-computed rainbow tables. Always use a secret salt. Rotate the salt when a team member who knew it leaves the company — this invalidates all existing hashes, which will require a full refresh of pseudonymized columns. Plan for this operationally.

Mistake 3: Forgetting about Snowflake Time Travel

When you delete or nullify PII records, the original data remains accessible via Snowflake Time Travel for the duration of DATA_RETENTION_TIME_IN_DAYS. For GDPR compliance, your deletion process must also explicitly handle Time Travel data. Set DATA_RETENTION_TIME_IN_DAYS = 0 for tables containing raw PII once you've processed it, or use FAIL_SAFE = FALSE to minimize the window. Include this in your deletion procedure.

-- After deletion propagation, minimize the time travel window
ALTER TABLE analytics.marts.dim_customers
  SET DATA_RETENTION_TIME_IN_DAYS = 0;

Mistake 4: Not testing masking from the right role

A common trap: you apply a masking policy and then verify it works by querying as SYSADMIN or ACCOUNTADMIN. Both of these roles bypass DDM policies by default in Snowflake. Always test masking behavior by impersonating the exact roles your analysts use, using a dedicated test user account.

-- Test masking as a specific role
USE ROLE pii_masked_reader;
SELECT email FROM analytics.marts.dim_customers LIMIT 5;
-- Should show j***@example.com, not full email

Mistake 5: Letting your pseudonymization key live in the same system as your data

If your salt for hashing PII is stored in a Snowflake secret that the same role used by dbt can access, an attacker who compromises that role has everything they need to reverse your pseudonymization. The salt should be stored in an external secrets manager (AWS Secrets Manager, HashiCorp Vault) and injected at pipeline run time by your orchestration layer — not accessible from within Snowflake itself.

Mistake 6: Ignoring the dbt_project.yml global post-hooks

If you have PII-tagging and masking post-hooks defined at the model level, a new team member creating a model and forgetting the post-hook will produce an unprotected model. Use dbt_project.yml to apply default post-hooks to entire schema directories:

# dbt_project.yml
models:
  your_project:
    marts:
      +post-hook:
        - "{{ tag_pii_columns() }}"
      +meta:
        default_masking_applied: false  # triggers a compliance test alert if not set

---

Summary & Next Steps

You've built a compliance architecture that operates at every layer of the stack. Snowflake's Dynamic Data Masking provides role-aware query-time protection without touching underlying data. dbt macros centralize pseudonymization and policy application, making PII handling visible in your codebase and auditable in your CI pipeline. The deletion propagation procedure satisfies Article 17 without destroying analytical continuity. Object tags and Access History queries give you a living Article 30 Records of Processing inventory.

The system you've built has these properties that regulators care about:

• Defense in depth: No single access control failure exposes raw PII
• Auditability: Every access is logged, and your policy enforcement is tested in CI
• Completeness: Deletion requests propagate through all derived models
• Proportionality: Analysts get the data they need, masked to the minimum necessary level

What to do next:

1. Deepen your row-level security: Column masking protects fields, but row-level policies control which records are visible at all. Read Implementing Row-Level Security and Column Masking Policies for Multi-Tenant Analytics in Snowflake and BigQuery to add another layer to your compliance architecture.

2. Extend your data governance framework: The tagging and lineage patterns here connect naturally to a full data governance strategy. Data Governance: Catalogs, Lineage, and Access Controls covers how to surface your compliance metadata in a data catalog.

3. Automate deletion request intake: Build a Snowflake Task or Airflow DAG that polls your CRM or customer portal for deletion requests and automatically populates your deletion_requests manifest table. The orchestration patterns in Orchestrating dbt Runs with Airflow: Scheduling, Dependencies, and Error Handling in Production apply directly here.

4. Plan for data sharing scenarios: If you share data with external partners via Snowflake Secure Data Sharing, your masking policies don't automatically apply to shares. Review Implementing Cross-Warehouse Federation and Data Sharing with Snowflake Secure Data Sharing to understand the implications.

The most important thing you can do today is audit your current pipeline for the gap between where PII should be protected and where it actually is. Run the tag inventory query, pull your Access History for the last 30 days, and map what you find against your data classification policy. That gap analysis will tell you exactly where to start implementing the patterns from this lesson.