Data Vault Modeling: Handling Physical Deletion & Retention

Managing Data Retention Policies in Data Vault

When source operational systems enforce strict data retention policies requiring physical deletion, enterprise data warehouse teams face a structural dilemma. Traditional approaches to data modeling often rely on soft deletions or logical status flags to preserve historical context. However, regulatory frameworks such as the General Data Protection Regulation (GDPR) and the California Consumer Privacy Act (CCPA) increasingly demand that personal data be completely removed from secondary storage, including analytical data platforms hosted on Snowflake.

Implementing physical deletion within an enterprise data model presents unique challenges. In an enterprise Data Vault ecosystem, core structures such as Hubs and Links frequently represent shared business entities across multiple organizational domains. A single customer business key stored in a Hub table might be referenced by sales, customer support, logistics, and marketing. If a system physically deletes a Hub record to satisfy a deletion request from one domain, downstream reporting pipelines and referential integrity across other operational areas will instantly break.

To resolve this conflict between strict compliance and structural resilience, data architects must decouple descriptive payload retention from core relationship keys. By structuring Satellites around distinct retention lifecycles and strictly managing business key design, enterprise teams can achieve complete compliance without sacrificing data model integrity.

Designing Lifecycles Through Satellite Splitting

In classic Data Vault 2.0 architecture, Satellite tables are split across several operational dimensions, such as source system boundaries, data rate of change, and the segregation of personally identifiable information (PII) from non-personal attributes. While separating personal attributes from non-personal attributes is a fundamental best practice for data privacy, it does not fully address the problem of varying retention periods within the same attribute class.

Attributes defined as personal data rarely share identical legal or operational lifecycles. For example, a customer’s telephone number and email address might only be required for active order delivery and immediate customer service interaction. Once an account becomes inactive, policy might mandate that these direct contact attributes be erased after two years. Conversely, shipping addresses, billing records, and legal names often must be retained for five years or longer to support product returns, legal defense, and tax audits. Meanwhile, non-personal metrics—such as historical transaction counts, aggregate order totals, or the date the account was first created—carry no privacy compliance risk and can be retained indefinitely for long-term analytical reporting.

If a data architecture consolidates all personal attributes into a single Satellite table, executing a two-year retention policy forces an engineer into a difficult tradeoff. The team must either perform complex, row-level or column-level updates (UPDATE statements setting values to NULL) or prematurely delete the entire Satellite record, thereby destroying five-year attributes that are legally required for tax auditing.

+-------------------------------------------------------------------------------+
|                             CUSTOMER HUB                                      |
|                             (Customer HK)                                     |
+------------------------+-----------------------+------------------------------+
                         |                       |
                         v                       v
+--------------------------------+     +--------------------------------+
| SHORT-TERM PERSONAL SATELLITE   |     | LONG-TERM PERSONAL SATELLITE    |
| (2-Year Retention)             |     | (5-Year Retention)             |
+--------------------------------+     +--------------------------------+
| - Customer HK                  |     | - Customer HK                  |
| - Load Date                    |     | - Load Date                    |
| - Email Address                |     | - First Name / Last Name       |
| - Phone Number                 |     | - Billing / Shipping Address   |
+--------------------------------+     +--------------------------------+
                         |
                         v
+--------------------------------+
| NON-PERSONAL SATELLITE         |
| (Indefinite / No Retention)    |
+--------------------------------+
| - Customer HK                  |
| - Load Date                    |
| - Total Billing Amount         |
| - Customer Since Date          |
+--------------------------------+

To streamline automated data management, Satellite tables should be architected according to their specific retention lifecycle rather than general categorizations. Rather than simply asking whether an attribute is personal, data architects should ask whether the attributes share the exact same retention lifecycle.

When Satellites are split by retention timelines, automated compliance tasks are significantly simplified:

• Short-Term Personal Satellite (2-Year Retention): Houses high-friction contact attributes like email addresses and mobile phone numbers. When two years elapse following customer inactivity, the automated pipeline issues a straightforward physical deletion against this specific Satellite table.

• Long-Term Personal Satellite (5-Year Retention): Holds customer names and address histories required for financial auditing and legal obligations. This table remains entirely untouched when the two-year contact Satellite is purged.

• Non-Personal Satellite (No Retention Expiry): Stores anonymized or non-critical business metadata, such as cumulative spend or initial conversion dates. This table is never subjected to automated erasure routines.

By managing retention at the Satellite table level, compliance teams can execute simple DELETE or table-dropping operations on aged partition slices without running expensive, locked UPDATE operations across multi-column tables in Snowflake.

Preserving Referential Integrity in Shared Models

While splitting Satellites manages descriptive payload lifecycles cleanly, the second major challenge involves handling core structural entities—specifically Hubs and Links. In a multi-domain enterprise model, deleting a business key from a shared Hub table breaks downstream joins, invalidates foreign key checks in information marts, and corrupts link-based relationship mappings across other operational domains.

In standard scenarios where business keys contain no sensitive personal data (e.g., an abstract sequence number like CUST-890123), the recommended best practice is to preserve the Hub and Link entries permanently while physically purging only the child Satellite records.

Because Data Vault links dependencies from the Satellite back to the Hub via immutable hash keys, deleting a record from a Satellite leaves the underlying structural framework intact. Queries executing against information marts will simply return NULL for the purged descriptive attributes while maintaining the structural continuity of historical order links, cross-domain bridge tables, and transaction counts. Foreign key constraints and automated ref-integrity tests continue to pass seamlessly because the parent hash key remains present within the Hub table.

Handling PII Stored Within Business Keys

A critical exception to keeping Hub keys intact occurs when an organization uses personal identifiers as primary business keys. Common examples include Social Security Numbers (SSN), national identity numbers, personal tax IDs, or personal email addresses.

If a business key contains PII and a physical deletion request is executed, leaving the key inside the Hub table violates regulatory compliance. The raw personal identifier remains directly visible or easily reverse-engineered if simple hashing without salting was applied.

To resolve this issue without compromising the primary architecture, organizations can choose between two main structural patterns.

Pattern 1: The Same-As Link Structural Pattern

The most common solution involves introducing an abstract, surrogate technical key alongside the natural personal key, establishing an internal mapping through a Same-As Link.

Under this pattern, the pipeline creates two primary inputs:

1. An abstract, non-PII technical business key (e.g., CUST-TECH-100293).

2. The natural PII business key (e.g., the raw SSN).

+-----------------------------------------------------------------------------------+
|                                  CUSTOMER HUB                                     |
+-----------------------------------------------------------------------------------+
| Customer HK (Hash of Technical Key)  | Tech_Customer_ID (e.g., CUST-TECH-100293)   |
| Customer HK (Hash of SSN Key)        | SSN_Business_Key (e.g., 999-00-1234)      |
+-----------------------------------------------------------------------------------+
                                         |
                                         v
+-----------------------------------------------------------------------------------+
|                             CUSTOMER SAME-AS LINK                                 |
+-----------------------------------------------------------------------------------+
| Link HK | Master Customer HK (Tech Key) | Deprecated Customer HK (SSN Key)        |
+-----------------------------------------------------------------------------------+
       |                                                    |
       v                                                    v
+-------------------------------+          +--------------------------------+
| NON-PERSONAL SATELLITE        |          | PERSONAL SATELLITE             |
+-------------------------------+          +--------------------------------+
| Key: Hash of Technical Key    |          | Key: Hash of Technical Key     |
| Attributes: Customer Since    |          | Attributes: First/Last Name    |
+-------------------------------+          +--------------------------------+

In this setup:

• Both keys are loaded into the main Customer Hub table.

• A Same-As Link mapping maps the relationship between the technical key and the PII key.

• All child Satellite tables use the hash of the technical key as their primary driving entity reference.

When a retention trigger or physical deletion request occurs for the customer’s personal data:

1. The row containing the natural PII key is physically deleted from the Customer Hub.

2. The mapping entry inside the Same-As Link is physically deleted.

3. The descriptive attributes in the Personal Satellite are physically deleted.

4. The Non-Personal Satellite record remains completely intact, driven securely by the non-PII technical key hash.

Because non-personal reporting pipelines exclusively reference the technical key, historical metrics remain fully operational, and the system successfully eradicates every instance of PII from the environment.

Pattern 2: Multi-Hub Architecture with Inter-Hub Link

An alternative pattern relies on separating the keys into two independent Hub tables:

1. Customer Hub (Technical): Contains only abstract technical keys.

2. Customer Identity Hub (PII): Contains only sensitive natural business keys (e.g., SSN).

3. Customer Identity Link: Connects the technical Hub key to the identity Hub key.

When data retention policies expire, engineers drop the records from the Identity Hub, the Identity Link, and the associated Personal Satellites. The core structural references in the Technical Customer Hub remain active across the rest of the enterprise data model, preserving relationship links to orders, support tickets, and long-term financial ledgers.

Strengthening Architectural Integrity

Adopting a lifecycle-driven approach to Data Vault modeling aligns physical storage behavior directly with compliance requirements. By structuring Satellite split boundaries according to retention lifecycles and utilizing surrogate technical keys for PII-bearing business keys, teams can satisfy stringent physical deletion mandates in Snowflake without corrupting downstream models or damaging structural integrity.

Organizations seeking to refine their enterprise modeling patterns can explore advanced implementation frameworks in our Data Vault 2.1 Training & Certification programs. For deeper insights into foundational key structures and relationships, review our guide on hash keys in the Data Vault or explore our overarching principles for modern Data Vault 2.0 architectures.

Automating Physical Deletion Workflows in Snowflake

Executing physical deletions within a cloud data warehouse like Snowflake requires careful operational planning to balance regulatory compliance, query performance, and storage costs. Unlike traditional relational database management systems, Snowflake utilizes an immutable micro-partition architecture. When a physical DELETE statement is issued against a table, Snowflake does not edit existing micro-partitions in place. Instead, it writes new micro-partitions that exclude the deleted records and marks the old partitions as unreferenced.

To ensure that personal data is completely erased and unrecoverable in accordance with strict physical deletion mandates, data engineering teams must account for Snowflake’s underlying data retention and recovery features: Time Travel and Fail-safe.

+-----------------------------------------------------------------------------------+
|                        SNOWFLAKE PHYSICAL PURGE PIPELINE                          |
+-----------------------------------------------------------------------------------+
|                                                                                   |
|  [ Active Table ]  ---> (Physical DELETE) ---> [ Time Travel Stage ] (0-90 Days)  |
|                                                         |                         |
|                                                         v                         |
|  [ Permanent Purge ] <--- (Data Expired) <--- [ Fail-safe Stage ] (7 Days)        |
|                                                                                   |
+-----------------------------------------------------------------------------------+

Handling Time Travel and Fail-safe Durations

Snowflake’s Time Travel feature enables access to historical data for query, restoration, or cloning within a configurable retention window (typically 0 to 90 days). If personal data is deleted from an active Satellite table but the underlying micro-partitions remain accessible within the Time Travel window, the data has not been fully purged from physical storage.

To meet strict physical deletion SLAs, organizations should implement automated purge pipelines that align table properties with legal compliance windows:

• Shortened Retention Settings: For personal Satellite tables subject to rapid deletion timelines, set the DATA_RETENTION_TIME_IN_DAYS parameter to 0 or 1 day. This minimizes the duration that deleted PII remains accessible via historical queries.

• Transient Table Options: For staging environments or intermediate personal data stores, consider utilizing TRANSIENT tables. Transient tables lack a Fail-safe period entirely, ensuring that dropped or purged data is removed from storage as soon as the designated Time Travel window expires.

• Managing Fail-safe: Data stored in standard permanent tables enters a mandatory 7-day Fail-safe period after the Time Travel window ends. While Fail-safe data is inaccessible to standard user queries and can only be recovered by Snowflake Support in emergency scenarios, compliance policies with strict physical removal mandates must account for this additional 7-day window when establishing legal deletion timelines.

Automating Purges via Metadata-Driven Pipelines

Rather than running ad-hoc script executions, enterprise environments benefit from metadata-driven automation to enforce Satellite-level retention rules. Because Satellites are cleanly segregated by lifecycle timelines, automated orchestration frameworks can monitor metadata tags attached to each Satellite table.

A typical metadata-driven erasure workflow operates through standard analytical steps:

1. Metadata Tagging: Tag each Satellite with its designated retention policy (e.g., RETENTION_PERIOD = '2_YEARS').

2. Identification Queries: An automated task queries business key activity metadata to identify inactive entity identifiers that have crossed the threshold date.

3. Targeted Erasure Execution: The orchestration platform generates dynamic DELETE operations targeted specifically at the expired Satellite tables (e.g., DELETE FROM SAT_CUSTOMER_PERSONAL_2YR WHERE CUSTOMER_HK IN (...)).

4. Partition Compaction: Following large-scale deletion events, run table re-clustering or consolidation tasks to optimize micro-partition efficiency and minimize storage fragmentation.

By combining lifecycle-segregated Data Vault modeling with precise Snowflake table configuration and automated metadata orchestration, data teams maintain continuous compliance while keeping storage usage efficient and performance optimized.

Watch the Video