Databricks SQL: Protecting Sensitive Columns with Masks

This tutorial demonstrates how to protect sensitive columns within your Databricks SQL tables using masking. Masking allows you to redact or substitute data while maintaining the structure and format of the original column, preventing unauthorized access to the actual values.

Script 1: Basic Masking with `REPLACE`

This script introduces the fundamental concept of masking using the `REPLACE` function. We’ll mask the ’email’ column, replacing all email addresses with ‘REDACTED’.

-- Create a temporary table with sample data.
CREATE TEMPORARY TABLE temp_users (
  user_id INT,
  first_name STRING,
  last_name STRING,
  email STRING
);

-- Insert sample data.
INSERT INTO temp_users (user_id, first_name, last_name, email)
VALUES
  (1, 'Alice', 'Smith', 'alice.smith@example.com'),
  (2, 'Bob', 'Johnson', 'bob.johnson@example.net'),
  (3, 'Charlie', 'Brown', 'charlie.brown@domain.org');

-- Mask the 'email' column by replacing all email addresses with 'REDACTED'.
SELECT
  user_id,
  first_name,
  last_name,
  REPLACE(email, '@example.com', 'REDACTED') AS masked_email
FROM
  temp_users;

Explanation:

• We create a temporary table named `temp_users` with sample user data including an email column.
• We insert three rows of data into this table.
• We use the `REPLACE` function to replace all occurrences of `@example.com` within the `email` column with the string ‘REDACTED’.
• The final `SELECT` statement displays the original data along with the masked email column.

This demonstrates the most basic masking approach – directly substituting specific values within a column.

-- Final Validation SELECT
SELECT COUNT() FROM temp_users;

1

Script 2: Masking with Regular Expressions

This script builds upon the previous example by demonstrating masking using regular expressions. We’ll mask email addresses that follow a specific pattern (e.g., ending with @example.com).

-- Create a temporary table with sample data.
CREATE TEMPORARY TABLE temp_users (
  user_id INT,
  first_name STRING,
  last_name STRING,
  email STRING
);

-- Insert sample data.
INSERT INTO temp_users (user_id, first_name, last_name, email)
VALUES
  (1, 'Alice', 'Smith', 'alice.smith@example.com'),
  (2, 'Bob', 'Johnson', 'bob.johnson@example.net'),
  (3, 'Charlie', 'Brown', 'charlie.brown@domain.org'),
  (4, 'David', 'Wilson', 'david.wilson@example.com');

-- Mask the 'email' column using a regular expression.
SELECT
  user_id,
  first_name,
  last_name,
  REGEXP_REPLACE(email, '/^.@example.com$/', 'REDACTED') AS masked_email
FROM
  temp_users;

Explanation:

• We use a regular expression to match email addresses ending with `@example.com`.
• The `REGEXP_REPLACE` function replaces the matched portion with ‘REDACTED’.
• This allows us to mask only those email addresses that conform to the specified pattern.

-- Final Validation SELECT
SELECT COUNT() FROM temp_users;

4

Script 3: Masking with `CASE` and `NULL`

This script introduces a more advanced masking technique using a `CASE` statement to selectively mask data based on conditions, effectively replacing values with `NULL` for specific cases. This is useful for scenarios where masking rules are complex or depend on other column values.

-- Create a temporary table with sample data.
CREATE TEMPORARY TABLE temp_users (
  user_id INT,
  first_name STRING,
  last_name STRING,
  email STRING,
  is_admin BOOLEAN
);

-- Insert sample data.
INSERT INTO temp_users (user_id, first_name, last_name, email, is_admin)
VALUES
  (1, 'Alice', 'Smith', 'alice.smith@example.com', TRUE),
  (2, 'Bob', 'Johnson', 'bob.johnson@example.net', FALSE),
  (3, 'Charlie', 'Brown', 'charlie.brown@domain.org', TRUE),
  (4, 'David', 'Wilson', 'david.wilson@example.com', FALSE);

-- Mask the 'email' column based on the 'is_admin' flag.  Admin emails are not masked.
SELECT
  user_id,
  first_name,
  last_name,
  CASE
    WHEN is_admin = TRUE THEN email
    ELSE REPLACE(email, '@example.com', 'REDACTED')
  END AS masked_email
FROM
  temp_users;

Explanation:

• We introduce an `is_admin` column to the `temp_users` table.
• The `CASE` statement checks if `is_admin` is `TRUE`. If it is, the original `email` value is returned. Otherwise, the email is masked using `REPLACE` as in the previous scripts.
• This allows us to selectively mask emails based on the administrative status of the user.

-- Final Validation SELECT
SELECT COUNT() FROM temp_users;

4