POST_START

Protecting Production PII While Preserving Analytical Access

I recently joined a team that’s working with a large customer dataset in Databricks. Our goal is to ensure that sensitive personal information (PII) is protected in production while still allowing analysts to perform their work. As part of my onboarding, I was asked to examine the structure of a table called customer_master in the production.customers schema. This would help us understand what data we’re dealing with and how we can apply proper access controls.

Understanding the Table Structure

I started by running the DESCRIBE TABLE EXTENDED command to get an overview of the table’s columns and their data types. This is a common first step when exploring a new dataset.

DESCRIBE TABLE EXTENDED production.customers.customer_master;

I saw a representative result like this:

col_name         | data_type | comment
------------------|----------|---------
customer_id      | bigint   | customer identifier
customer_name    | string   | customer display name
region           | string   | sales region

From this, I learned that the customer_master table contains customer identifiers, names, and regional information. While the customer_id is likely a unique identifier, the customer_name field might contain personally identifiable information. This made me realize that we need to be cautious about how this data is accessed and shared.

Verifying User Access

Before proceeding to query the actual data, I wanted to confirm who was logged in and had access to this table. I ran a simple query to check the current user.

SELECT current_user();

I saw a representative result like this:

current_user()
analyst@demo.com

This helped me understand that the current user was an analyst, which is consistent with the team’s needs. However, it also reminded me that we need to ensure that only authorized users can access this data, especially the sensitive fields like customer_name.

Exploring the Data

With the table structure and user context in mind, I decided to look at some sample data to get a better sense of what we’re working with. I ran a query to retrieve the first 20 rows of the customer_master table.

SELECT * FROM production.customers.customer_master LIMIT 20;

I saw a representative result like this:

customer_id | customer_name | region | status
------------|---------------|--------|--------
1001        | Maria Keller  | EU     | ACTIVE
1002        | Daniel Smith  | US     | ACTIVE
1003        | Sofia Rossi   | EU     | INACTIVE

These rows provided a clear view of the data we’re dealing with. The customer_name field clearly contains personal information, and the status field indicates whether the customer is active or inactive. This is valuable for analytical purposes, but we must ensure that this data is not exposed to unauthorized users.

Next Steps

Based on this exploration, I realized that the next step is to implement proper access controls using Unity Catalog. This will allow us to protect the PII while still enabling analysts to work with the data they need. I’ll start by creating access policies and roles that restrict access to sensitive fields, ensuring that only authorized users can view or query them.