Handling Physical Deletion in Data Vault for Regulatory Compliance
Operational source systems frequently enforce strict data retention policies, necessitating physical deletions to remain fully compliant with legal frameworks like GDPR or CCPA. When moving this operational data into a modern enterprise cloud environment—such as Snowflake, Databricks, or Amazon Redshift—data engineering teams often encounter a major structural conflict: how to physically purge sensitive records without breaking downstream reporting, analytical models, or the foundational integrity of an enterprise-wide data warehouse.
This challenge becomes acute when business keys, hubs, and links are shared across multiple business units and analytical domains. A naive approach to physical deletion runs the risk of orphaning references, corrupting downstream data marts, or accidentally destroying non-sensitive historical context that the business has a legitimate legal reason to keep.
By understanding how to properly split satellites based on lifecycle patterns, as well as how to architect reference structures when personally identifiable information (PII) is involved, organizations can achieve full regulatory compliance while preserving data warehouse consistency.
In this article:
The Challenge of Enforcing Physical Deletion in Enterprise Models
In standard analytical architectures, handling soft deletes via logical status flags or operational tracking attributes (such as is_deleted flags or tombstone records) is the preferred method for managing missing or deleted source records. However, privacy laws such as GDPR’s “Right to be Forgotten” or strict industry-specific data retention mandates explicitly demand the physical removal of sensitive records from storage media.
When executing physical purges, data teams quickly realize that operational entities rarely follow a single, monolithic lifecycle. Consider a typical customer record containing the following attributes:
-
Short-term operational attributes: Email address, phone number
-
Longer-term transactional/legal attributes: Shipping address, billing address, legal full name
-
Non-sensitive analytical attributes: Total lifetime spend, initial registration timestamp (
customer_since), account status
If an organization places all of these attributes into a single storage structure, enforcing a two-year retention rule on contact details requires either complex, resource-intensive attribute-level update scripts or the accidental, premature deletion of five-year legal auditing records.
Furthermore, simply running DELETE statements against core business entities can break foreign key relationships or disrupt shared enterprise hubs and links used by other business units. Solving this problem requires aligning satellite separation directly with data lifecycles rather than relying purely on functional attribute groups.
Designing Satellites by Lifecycle and Data Retention Requirements
In standard modeling, practitioners split satellites based on source system boundaries, rate of change, or a basic division between personal (PII) and non-personal attributes. While separating PII into its own satellite is a solid first step, it does not fully solve retention issues because different pieces of personal data carry vastly different legal retention schedules.
A far more effective modeling philosophy asks: Do these attributes share the exact same retention lifecycle?
Instead of creating one massive PII satellite, the recommended approach is to split satellites according to explicit operational and regulatory lifecycles.
+-----------------------------------------------------------------------+
| HUB_CUSTOMER |
| - Customer_HK (Hash Key) |
| - Customer_ID (Business Key) |
+-----------------------------------------------------------------------+
|
+-------------------------+-------------------------+
| | |
v v v
+------------------+ +------------------+ +------------------+
| SAT_CUST_PII_2YR | | SAT_CUST_PII_5YR | | SAT_CUST_NONPII |
| - Phone Number | | - First Name | | - Total Spend |
| - Email Address | | - Last Name | | - Customer Since |
| | | - Billing Addr | | |
+------------------+ +------------------+ +------------------+
1. Two-Year Satellite (Short-Lived Personal Data)
This satellite stores operational contact information, such as temporary phone numbers and email addresses, needed primarily for active order fulfillment, support communications, or short-term marketing. When a customer account becomes inactive and reaches the two-year threshold, the entire table partition or dataset for that customer in this specific satellite can be physically deleted.
2. Five-Year Satellite (Longer-Lived Legal Data)
This satellite stores attributes like full names and billing or shipping addresses. These records must often be retained for five years to satisfy tax auditing, warranty fulfillment, or legal compliance mandates. Deleting records from the two-year satellite leaves this satellite completely intact and operational.
3. Non-Personal Satellite (Indefinite Analytics Data)
This satellite holds anonymized or purely transactional attributes, such as total order values, account creation dates (customer_since), or aggregated interaction metrics. Because these attributes contain no personal identifiers, they carry no retention restrictions and can be preserved indefinitely for deep historical analysis.
By associating retention policies with dedicated satellites rather than individual columns, physical purges are simplified into clean table-level or partition-level delete operations. This eliminates the need for expensive UPDATE statements that leave behind empty or nullified columns across historical rows.
Maintaining Integrity Across Shared Hubs and Links
In an enterprise architecture, hubs and links form the structural backbone of the data model. A single HUB_CUSTOMER record might connect to dozens of downstream business domains, including sales orders, customer service tickets, shipping logistics, and web analytics.
+------------------+ +------------------+ +------------------+
| HUB_ORDER | | LINK_CUST_ORDER | | HUB_CUSTOMER |
| - Order_HK |<------->| - Cust_Order_HK |<------->| - Customer_HK |
| - Order_Number | | - Order_HK | | - Customer_ID |
| | | - Customer_HK | | |
+------------------+ +------------------+ +------------------+
|
v
+------------------+
| SAT_CUST_PII_5YR |
| - First Name |
| - Last Name |
+------------------+
When a physical deletion request is processed for a customer, a common mistake is attempting to purge the business key from HUB_CUSTOMER and all associated records in LINK_CUSTOMER_ORDER. Doing so introduces severe issues:
-
Referential Breakdown: Downstream link tables and order hubs suddenly point to non-existent customer hash keys, causing automated integrity tests and join logic to fail.
-
Loss of Context: Orders placed by that customer lose their structural connection, preventing accurate historical reporting on aggregate sales metrics or inventory movements.
The Recommended Standard Strategy
When regulatory rules require the physical deletion of descriptive attributes, delete only the records inside the descriptive Satellites. Leave the Hub and Link entries untouched.
Because technical business keys (such as an internal system UUID like CUST-90210) contain no personal identifiers, retaining them in the Hub and Link does not violate privacy mandates. The business key simply remains in the model as an anonymous structural anchor.
downstream reporting tools query the model via point-in-time (PIT) tables or bridge structures, the missing satellite entries naturally yield NULL values for personal attributes (e.g., Name, Address) while maintaining the valid join path necessary to aggregate non-personal financial metrics.
To learn more about implementing these patterns in modern analytical platforms, explore our dedicated Data Vault 2.1 Training & Certification program, where we cover advanced satellite design, deletion workflows, and compliance patterns in depth.
The Data Vault Handbook:
Core Concepts and Modern Applications
Build Your Path to a Scalable and Resilient Data Platform
The Data Vault Handbook is an accessible introduction to Data Vault. Designed for data practitioners, this guide provides a clear and cohesive overview of Data Vault principles.
Handling PII Within Business Keys
While using anonymous technical keys is an industry best practice, real-world source systems sometimes rely on sensitive attributes as primary identifiers—such as a Social Security Number (SSN), national ID number, or personal email address.
If an SSN is loaded directly into HUB_CUSTOMER as the core business key, physically purging the customer’s personal data means the Hub itself must be purged, which breaks the structural links mentioned above.
To resolve this issue, you must decouple the sensitive business key from the satellite’s primary key structure.
Architecting a Same-As Link for PII Business Keys
When a sensitive attribute must be ingested as a business key, use a dual-key architecture combined with a Same-As Link:
-
Technical Business Key: Generate or assign an internal surrogate or technical business key (e.g.,
TECH_CUST_1001). -
Sensitive Business Key: Ingest the sensitive key (e.g.,
SSN_999_00_1234) into the Hub alongside or as a secondary key representation. -
Same-As Link Mapping: Establish a Same-As Link mapping table (
LINK_CUST_SAME_AS) that maps the hash key of the technical key to the hash key of the sensitive key. -
Satellite Primary Key Assignment: Crucially, construct all descriptive satellites—especially non-personal satellites—using the hash key derived from the technical business key, rather than the sensitive key.
+-----------------------+
| HUB_CUSTOMER (SSN) |
| - SSN_HK |
+-----------------------+
^
|
+-----------------------+ +-----------------------+
| HUB_CUSTOMER (TECH) | | LINK_CUST_SAME_AS |
| - Tech_Cust_HK |<----->| - Tech_Cust_HK |
+-----------------------+ | - SSN_HK |
| +-----------------------+
|
+---------------------------------------+
| |
v v
+-----------------------+ +-----------------------+
| SAT_CUST_NONPII | | SAT_CUST_PII |
| (PKEY: Tech_Cust_HK) | | (PKEY: Tech_Cust_HK) |
| - Customer Since | | - First Name |
+-----------------------+ +-----------------------+
Executing the Deletion Workflow
When a deletion request arrives for a customer governed by this architecture:
-
Purge Sensitive Satellite: Delete the row from
SAT_CUST_PII. -
Purge Sensitive Hub Entry: Delete the record containing the sensitive SSN business key from the SSN Hub.
-
Purge Mapping Entry: Delete the relationship entry in
LINK_CUST_SAME_AS. -
Preserve Non-Personal Context: Leave
HUB_CUSTOMER (TECH)andSAT_CUST_NONPIIintact.
Because SAT_CUST_NONPII relies on the non-sensitive Tech_Cust_HK, all non-personal history (such as registration date or aggregated order history) remains completely intact and operational for enterprise reporting.
For teams building automated pipelines using modern transformation engines, review our guidelines on datavault4dbt package solutions or read through The Data Vault Handbook for additional modeling strategies.
Best Practices for Regulatory Compliance in Modern Architectures
Automating physical deletions in high-volume enterprise environments requires clear architectural rules and disciplined modeling workflows.
1. Establish Strict Business Key Definition Protocols
During the initial data discovery and modeling phase, enforce a firm policy against using sensitive personal data (e.g., SSNs, phone numbers, email addresses) as primary business keys. Advocate for immutable technical identifiers at the source system level whenever possible.
2. Group Satellites by Regulatory Lifecycles
Avoid creating monolithic “catch-all” satellites. Collaborate directly with legal, risk, and compliance teams to categorize attributes based on their required retention spans (e.g., 30 days, 2 years, 7 years, or indefinite). Group attributes into distinct satellites matching these precise lifecycles.
3. Automate Deletion via Metadata-Driven Pipelines
Avoid writing manual DELETE scripts for individual compliance tickets. Build automated, metadata-driven deletion pipelines that evaluate retention periods against satellite timestamps and execute partition drops or bulk deletes targeted strictly at expired satellite records.
4. Audit Downstream Impacts with Automated Reviews
Prior to altering production data models to support new deletion rules, run a comprehensive data platform review to ensure that downstream consumers, data marts, and business intelligence dashboards gracefully handle missing satellite records without throwing pipeline errors or breaking calculations.
By adopting lifecycle-based satellite splits and decoupled structural keys, data architecture teams can easily satisfy demanding regulatory deletion mandates without compromising the structural stability or long-term analytical value of their enterprise data platforms.
