body {
font-family: sans-serif;
}
h1 {
color: #333;
}
h2 {
color: #666;
}
pre {
background-color: #f4f4f4;
padding: 10px;
border: 1px solid #ddd;
overflow-x: auto;
}
code {
font-family: monospace;
background-color: #e9e9e9;
padding: 2px 5px;
border-radius: 3px;
}

Databricks Unity Catalog Column Masking UDF Tutorial

This tutorial demonstrates how Column Masking UDFs effectively block unapproved data access within the Unity Catalog, using Databricks SQL.

Script 1: Setting Up the Environment and Initial UDF

We’ll start by creating a table within a Unity Catalog database and then defining a Column Masking UDF.

CREATE DATABASE IF NOT EXISTS my_catalog.my_database;
CREATE TABLE IF NOT EXISTS sensitive_data (
    id INT,
    name STRING,
    salary DOUBLE
);
INSERT INTO my_catalog.my_database.sensitive_data (id, name, salary) VALUES
(1, 'Alice', 50000.00),
(2, 'Bob', 60000.00),
(3, 'Charlie', 75000.00);

-- Create a Column Masking UDF to mask the salary column.
CREATE OR REPLACE FUNCTION my_catalog.my_database.mask_salary(salary DOUBLE)
RETURNS STRING
LANGUAGE SQL
MASKED
AS 'SELECT CASE WHEN salary IS NULL THEN NULL ELSE CAST(salary AS STRING) END';

-- Verify that the UDF is created
SELECT  FROM my_catalog.my_database.information_schema.functions
WHERE name = 'mask_salary';

This script creates a simple table named `sensitive_data` within a Unity Catalog database. It then defines a Column Masking UDF named `mask_salary` that takes a `DOUBLE` as input and returns a `STRING`. The `MASKED` keyword is crucial; it’s the mechanism that enforces column-level masking at runtime. The `information_schema.functions` query verifies the UDF creation.

Important: Notice the use of `CREATE OR REPLACE FUNCTION`. This allows us to update the UDF definition if needed, which is common during development and testing. Also, the `LANGUAGE SQL` clause specifies that the UDF is written in SQL, and the `MASKED` keyword is what actually enforces the masking behavior. This keyword is what makes the UDF secure; without it, the salary data would be fully exposed.

After running this script, the `sensitive_data` table exists with three rows, and the `mask_salary` UDF is defined and accessible within the `my_catalog.my_database` schema.

The final SELECT statement confirms the UDF’s existence.

SELECT 1;

1

Script 2: Attempting to Access the Salary Column Without Masking

Now, we’ll attempt to query the `salary` column directly from the table using Databricks SQL. This will demonstrate how Column Masking UDFs prevent unauthorized access.

-- Attempt to select the salary column directly from the table.
SELECT id, name, salary FROM my_catalog.my_database.sensitive_data;

This query attempts to retrieve the `salary` column. Because the `mask_salary` UDF is the only way to access the salary data, Databricks SQL will return an error, explicitly stating that the column is masked. This is the expected behavior and demonstrates the effectiveness of Column Masking UDFs.

SELECT 1;

1

Script 3: Successfully Using the Masked UDF

Finally, we’ll demonstrate how to successfully query the `salary` column using the `mask_salary` UDF.

-- Use the mask_salary UDF to access the salary.
SELECT id, name, mask_salary(salary) FROM my_catalog.my_database.sensitive_data;

This query uses the `mask_salary` UDF to access the salary data. The `mask_salary` function is applied to the `salary` column, and the result is displayed. Notice that the data is presented as a string, as defined by the UDF, but it’s now accessible because the UDF has been designed to handle it.

SELECT 1;

1