Mastering PII Data Handling in Data Engineering and Databricks: Comprehensive Study Notes

Speaker Introduction and Master Class Scope

Narendra Kumar, a Senior Data Architect with over $12$ years of IT industry experience ($8$ years specifically in Data Engineering), presents this master class on handling Personally Identifiable Information (PII) data. Kumar holds professional certifications from Databricks and Microsoft and is a Databricks Certified Solution Architect Champion.

This guide provides an exhaustive exploration of PII handling, covering definitions, technical implementation levels, and a decision framework for choosing appropriate techniques. The technical demonstrations utilize the Databricks Free Edition and a dedicated GitHub repository containing all notebooks and CSV files mentioned.

Definitions and Importance of PII and PHI Data

Personally Identifiable Information (PII)

PII refers to any data that can directly or indirectly identify a specific individual. It is categorized into two distinct types:

• Direct PII: Data points that uniquely identify a person on their own.
  • Examples: Full Name, Email Address, Phone Number, Social Security Number ($SSN$), and Passport Number.
• Indirect PII: Data points that do not identify a person in isolation but can identify an individual when combined with other data.
  • Examples: Date of Birth ($DOB$), $IP$ Address, and Device $ID$. For instance, combining an $IP$ address with a specific birth date can pinpoint a specific user of a shared machine.

Protected Health Information (PHI)

PHI refers to health-related data for an individual.

• Examples: Medical records, blood reports, and medical diagnosis information.
• Overlap: Certain fields like "Name" function as both PII and PHI when attached to medical records.

Rationale for Specialized Handling

1. Cost of Data Breaches: Financial penalties for leaks can reach millions or billions of dollars ($USD$).
2. Regulatory Compliance: Mandatory standards such as HIPAA (United States) and GDPR (Europe) dictate how PII must be stored and processed.
3. Reputational Damage: Leaks result in a loss of user trust and significant brand devaluation.

The High-Level Process of PII Handling

The framework for managing PII data consists of six primary stages:

1. Minimize: Collect and process only absolutely necessary PII. If data is not required for analytics, it should be excluded during ingestion.
2. Classify: Properly identify PII or PHI columns and apply metadata tags.
3. Protect: Use technical methods to obscure or transform data (Encryption, Pseudonymization, Anonymization).
4. Control: Regulate access via Role-Based Access Control ($RBAC$), column-level masking, and row-level filtering.
5. Monitor: Access logs must be recorded in audit tables to track who is interacting with sensitive data.
6. Delete: Establish mechanisms for the permanent removal of specific user data to comply with "Right to Erasure" requirements.

Implementation: Minimization and Classification

Minimization

Minimization involves excluding unnecessary PII from the ingestion pipeline.

• Scenario: A project requires customer analytics by country.
• Action: Ingest only Customer_ID, City, and Country. Explicitly exclude Name, Email, and Phone_Number. This allows for group-by operations without the overhead of PII protection.

Classification and Tagging

Classification involves adding metadata to columns to facilitate discovery and policy enforcement.

• Manual Tagging: Using SQL commands like ALTER TABLE ... ALTER COLUMN ... SET TAGS ('PII' = 'true', 'PII_type' = 'direct', 'classification' = 'email').
• Automated Tagging: Databricks "Data Classification" feature (currently in public preview) automatically identifies and tags PII columns at the catalog level using standard organizational schemas.
• Discovery: Tags can be queried via the Information Schema view: system.information_schema.column_tags.

Technical Protection: Pseudonymization

Pseudonymization replaces original data with artificial identifiers. Identification of the original data depends on the specific technique used.

Hashing

Hashing calculates a fixed-length string based on the input. It is an irreversible process (one-way).

• Functions:
  • $SHA2(column, 256)$: Generates a $256$-bit hash.
  • $MD5(column)$: A faster algorithm often used for wide datasets, though with a slightly higher collision risk in massive datasets ($billions+$ rows).
  • $SHA1(column)$ and $SHA2(column, 512)$.
• Utility: Useful for joining tables on PII columns and performing distinct counts without knowing the actual values.

Tokenization

Tokenization replaces PII with a randomly generated token and stores the mapping in a restricted lookup table.

• Process:
  1. Create a User Defined Function ($UDF$) to generate a unique identifier (e.g., a $UUID$).
  2. Extract distinct PII values and map them to new tokens in a Lookup_Table.
  3. Join the Lookup_Table with the original data and drop the PII columns, leaving only the tokens.
• Access Control: Only authorized administrators are granted access to the Lookup_Table to re-identify individuals.

Technical Protection: Anonymization

Anonymization removes or generalizes data so that re-identification is impossible.

Data Suppression

Excluding PII columns from views provided to end-users. While the backend table contains PII, the user-facing view SELECTs only non-PII attributes.

Generalization Techniques

1. Categorical Generalization: Replacing specific values with broad categories (e.g., changing "Software Engineer" and "Data Architect" to "Engineering").
2. Binning: Converting specific numerical points into ranges. Example: Converting a specific $DOB$ into an age bracket (e.g., "$20-25$").
3. IP Address Truncating: Removing pinpoint information by replacing the last byte of an $IP$ address with zero (e.g., $192.168.1.55 \rightarrow 192.168.1.0$). This preserves regional data for analytics while protecting the specific device identity.
4. Rounding: Obscuring exact numerical values like salaries. Replacing the last $3$ or $4$ digits with zeros (e.g., $85672 \rightarrow 80000$) provides a range for analysis with less precision.

Technical Protection: Encryption

Encryption uses mathematical keys to transform data into a reversible secret format.

Levels of Encryption

• Column Encryption: Applying functions like $AES_ENCRYPT(column, key)$. Data is stored in encrypted form and retrieved via $AES_DECRYPT(column, key)$. Keys must be stored securely in Databricks Secret Scopes.
• Encryption at Rest: Cloud providers (Azure ADLS, AWS S3) encrypt data on physical disks by default. Organizations can use Customer Managed Keys ($CMK$) stored in a Key Vault for higher security control.
• Encryption in Motion: Uses protocols like Transport Layer Security ($TLS$) and Secure Sockets Layer ($SSL$) to protect data while it travels between storage and compute clusters. This is typically handled automatically by the platform.

Control and Monitoring

Access Control Methods

• Role-Based Access Control (RBAC): Providing permissions based on Active Directory ($AD$) groups (e.g., "Admins" get ALL PRIVILEGES, "Analysts" get SELECT).
• Attribute-Based Access Control (ABAC): Using tags (PII = true) to automatically restrict column access based on workspace policies.
• Column Masking: Creating functions that conditionally mask data. Example: If a user is not in the "Admin" group, replace all but the first character of a name with *.
• Row Filtering: Creating functions that return a Boolean. Example: A manager sees all rows, while a regional employee sees only rows where $Country = 'India'$.

Monitoring

Databricks logs all access operations in the system.access.audit table. This tracks the user, workspace, $IP$ address, action taken, and timestamp, allowing for the creation of tracking dashboards.

Data Deletion and Retention

Compliance requirements often mandate data deletion within specific windows (e.g., $7$ to $30$ days after an account is closed).

• Delta Properties: Use $ALTER TABLE table_name SET TBLPROPERTIES ('delta.deletedFileRetentionDuration' = 'interval 30 days')$ to control how long history is kept for time travel.
• Vacuuming: The VACUUM command must be run to physically purge data files that have been deleted for longer than the retention period.
• Deletion Pipelines: A standard pipeline should be built to delete a specific $User_ID$ across all organizational tables (Silver and Gold layers) and finish with a VACUUM to ensure non-recoverability.

Decision Framework for PII Techniques

To choose a strategy, follow this logic tree for each column:

1. Is the column needed at all? No $\rightarrow$ Minimize (do not ingest). Yes $\rightarrow$ Move to step $2$.
2. Is summarized/aggregated data enough? Yes $\rightarrow$ Generalization (Binning/Categorization). No $\rightarrow$ Move to step $3$.
3. Is hashed data enough? Yes $\rightarrow$ Hashing (Irreversible). No $\rightarrow$ Move to step $4$.
4. Should PII be in a separate table? Yes $\rightarrow$ Tokenization. No $\rightarrow$ Move to step $5$.
5. Is reversible access needed? Yes $\rightarrow$ Column Encryption. No $\rightarrow$ Move to step $6$.
6. Direct exposure needed? Yes $\rightarrow$ Role-Based access with Masking and Filtering.

Note: These techniques are often used in combination. A table may use generalization for dates, hashing for IDs, and encryption for names, all while being governed by RBAC.

Questions & Discussion

Q: How do we handle the security of encryption keys in Databricks?
A: We never hardcode keys in notebooks. They are stored in Databricks Secret Scopes and referenced as variables. If a key is lost, the data becomes unrecoverable, so management of these keys is high priority.

Q: Why do I see masked values if I haven't set up user groups?
A: In the demonstration, functions check if a user is a member of an "Admin" group. Since those groups don't exist in the Free Edition, the check fails by default and routes the user to the else condition, showing the masked output.

Q: What is the risk of using MD5 hashing?
A: While fast, MD5 is more prone to collisions compared to SHA256. For datasets with billions of rows, SHA256 is preferred to ensure unique hash values for unique inputs.**