.png){kind=small}
Adding New Columns to Satellites and Managing HASHDIFFs in Data Vault 2.0
- Rhys Hanscombe
- Feb 25
- 2 min read
A common challenge in Data Vault 2.0 is how to add new columns to an existing Satellite (SAT) while maintaining HASHDIFF integrity. A recent forum discussion explored best practices and different approaches, particularly when using AutomateDV.
This post summarizes the discussion, practical guidance, and current standards.
The Scenario: Adding Columns Without Breaking HASHDIFF
One forum member referenced Dan Linstedt’s original guidance:
When adding new columns, place them at the end of the SAT.
Trailing NULLs at the end are excluded from the HASHDIFF computation.
This approach aims to avoid triggering a full reload of the satellite when only new columns are added.
Another member added:
Use CTEs to append columns dynamically.
AutomateDV will soon support src_additional_columns for more flexible satellite updates.
At first glance, this seems like an elegant solution—but the forum quickly identified practical limitations.
Practical Challenges
A forum member noted:
Dynamically excluding trailing NULLs from HASHDIFF is “nearly impossible” in production ETL.
The risk of misalignment or inconsistent HASHDIFFs increases.
Many recommend creating a new satellite instead of altering an existing one.
Expert Guidance: Create New Satellites
An instructor clarified the current DV2 standard:
Do not alter existing SATs to add columns.
Always create a new SAT for new columns.
All NULLs—even trailing ones—must be included in HASHDIFF calculations, typically as empty strings.
Trimming delimiters is non-standard and deprecated since DV2 CDVP2 training.
Other members reinforced this:
Some noted that older textbook approaches with trailing NULL trimming can work but are not compliant with modern DV2.
Another emphasized: always use a CREATE-only schema. Deploy new SATs and loaders for schema changes—never ALTER existing tables—to preserve auditability and data integrity.
Key Takeaways
Create a new satellite when adding columns to avoid duplicate records and HASHDIFF inconsistencies.
Include all NULLs in HASHDIFF, even trailing ones; do not trim delimiters.
Do not ALTER existing satellites; use a CREATE-only approach for schema changes.
AutomateDV features like src_additional_columns can help manage new columns efficiently, but core principles remain the same.
Following DV2 standards ensures auditability, consistency, and compliance.
Why This Matters
Altering existing satellites may seem convenient, but it can:
Break HASHDIFF calculations
Trigger unintended reloads
Complicate auditing
Deviate from DV2 compliance
Creating new satellites maintains clean history, simplifies loaders, and aligns with modern Data Vault 2.0 best practices.
Join the Conversation
This blog is based on a real discussion from the Data Community forum.
If you’re working with AutomateDV or building DV2 satellites, this is a common challenge with multiple solutions.
