Handling Contradicting Facts About a Single Business Key in Data Vault
A frequent dilemma facing data warehouse architects and BI engineers during batch processing is determining how to manage contradicting descriptive data associated with a single business key. In modern enterprise environments, data ingested into the staging area often presents conflicting values within the exact same load cycle. These conflicts typically manifest under two primary operational scenarios: multi-source data feeds providing divergent descriptive attributes for a common entity, or Change Data Capture (CDC) streams containing rapid successive updates packed into a single batch delivery.
Resolving these conflicting data points correctly without compromising core enterprise data modeling standards requires a clear distinction between the Raw Data Vault layer and downstream Business Vault components. Teams embarking on enterprise data platform modernizations often enroll in formal Data Vault training to master the separation of concerns necessary for handling complex multi-source consolidation and high-frequency stream integration.
Understanding the structural mechanics behind source prioritization and CDC sequence ordering ensures that auditability remains entirely intact while downstream reporting tools receive unified, accurate golden records.
In this article:
Scenario 1: Resolving Multi-Source Contradictions via Source Prioritization
The first scenario occurs when multiple source systems deliver descriptive attributes regarding the exact same business key within the same processing window. For example, an organization’s HR system and CRM platform may both store employee records. The HR system serves as the primary master system for internal workforce data, recording an employee name as “Marc Winkelman” with a “c”. Simultaneously, the CRM platform captures customer-facing account managers and registers the same employee key with the name “Mark Winkelman” using a “k”.
When both source extracts are staged concurrently, both records share the exact same business key and receive the exact same Load Date Timestamp (LDTS). How the data repository ingests and downstream views present this conflicting descriptive data determines the ultimate stability of the data warehouse.
Raw Data Vault Satellites: Single Source per Satellite
A common design mistake is attempting to consolidate conflicting records from disparate source systems into a single Raw Data Vault Satellite. Inserting conflicting attributes from multiple systems into one Satellite breaks best practices, leads to primary key collisions, and introduces undesirable source coupling.
In a properly modeled Data Vault 2.0 architecture, the golden rule remains strict: one source table feeds one target table in the Raw Data Vault. The Hub table holds the distinct business key once, acting as the passive integration point. However, two separate Satellites are constructed beneath that Hub: an HR Employee Satellite and a CRM Employee Satellite. Each Satellite captures the raw state delivered by its respective operational system, complete with its own load metadata and descriptive payload.
This architecture completely preserves auditability. If the CRM source pipeline experiences a delay or failure at 3:00 AM while the HR extract completes successfully at 1:00 AM, the HR pipeline executes independently without waiting for or depending on CRM data availability.
Business Vault Alignment and Point-in-Time (PIT) Tables
Because the raw data remains split across system-specific Satellites, resolving which value takes precedence is deferred to the Business Vault and Information Mart layers. This approach treats data prioritization as an agile, adaptable business rule rather than a rigid physical constraint imposed during raw ingestion.
To unite these disparate timelines on a common axis, data engineers construct Point-in-Time (PIT) tables. A PIT table creates regular snapshot dates across all Satellites attached to a parent Hub or Link object. The PIT table contains the business key, the Snapshot Date Timestamp (SDTS), and key pointers—specifically the combination of business key and load date timestamp—for each corresponding Satellite record active at that snapshot interval.
Formulating the Golden Record using COALESCE Logic
Once the PIT table establishes the alignment pointers across all source Satellites for a given snapshot, an Information Mart view (such as an Employee Dimension) can construct a single unified record. Priority rules are applied using standard database expressions like COALESCE within the selection logic.
To implement this prioritization pattern, the view performs an INNER JOIN from the central PIT table into the HR Satellite based on the HR pointer keys, alongside another INNER JOIN into the CRM Satellite using the CRM pointer keys. Because the PIT table brings both pointers onto a single row, the SELECT clause can evaluate the attributes conditionally.
Using COALESCE(hr_sat.employee_name, crm_sat.employee_name), the query engine attempts to extract the employee name from the authoritative HR Satellite first. If the attribute is null or absent for that snapshot, the expression falls back gracefully to the CRM Satellite’s attribute value. This pattern ensures deterministic, rule-based cherry-picking of attributes while maintaining full traceability back to the individual underlying Satellites.
Scenario 2: Handling High-Frequency Change Data Capture (CDC) Batches
The second scenario involves receiving multiple state changes for a single business key from a single source system inside a single micro-batch or Change Data Capture (CDC) pipeline extract.
In high-volume operational engines, an entity attribute might undergo several rapid updates within seconds. For instance, a user might enter a name as “Marc”, update it to “Mark”, and subsequently correct it back to “Marc”. When the CDC ingestion pipeline processes these events in a nightly or hourly batch, all three state changes arrive bundled in a single file or stream package.
The Pitfall of Primary Key Violations
If an ETL process attempts to attach a single batch-level Load Date Timestamp to all three CDC change rows, inserting them directly into a standard Satellite results in a primary key violation. In Data Vault modeling, the primary key of a standard Satellite is defined as the composite of the parent key (e.g., hash keys in the Data Vault) and the Load Date Timestamp.
Assigning an identical timestamp to three distinct state changes for the same business key destroys uniqueness and prevents standard delta tracking. Furthermore, treating these rapid sequential changes as a multi-active payload is an anti-pattern. Multi-active Satellites are explicitly designed for instances where multiple child attributes are concurrently valid at the exact same moment in time (such as multiple active phone numbers delivered in a flattened JSON array), not for sequential historical updates.
Owning the Load Date Timestamp vs. Source Dates
A critical architectural principle in Data Vault design is that the data warehouse team must retain complete ownership over the generation and management of the Load Date Timestamp. Relying on source-supplied timestamps or operational system last-modified dates as the physical primary key of a Satellite introduces significant risk.
Operational business dates supplied by source systems are frequently subject to quality issues. Source timestamps can be null, formatted inconsistently across tables, adjusted retrospectively by database administrators, or corrupted during system migrations. Using a source date as a Satellite primary key key breaks the load dependency chain and impedes deterministic incremental loading. The Load Date Timestamp must strictly represent the point in time when the record entered the data warehouse ecosystem.
Resolving Sequential Order with Technical Sequence Numbers
To load multiple intra-batch CDC updates into a Satellite without violating primary key constraints or relying on unstable source dates, data engineering pipelines utilize technical sequence numbers or change log row IDs.
CDC source systems almost universally output a technical sequence ID, incrementing transaction counter, or precise log sequence number (LSN) alongside each captured record. When processing the staged CDC batch, the loading mechanism evaluates this sequence order and adjusts the applied Load Date Timestamp slightly for each consecutive record within the batch—typically adding fractional sub-second increments (such as microseconds) per row sequence.
By adding microsecond adjustments based on the technical stream sequence, each consecutive record inside the CDC batch receives a unique, monotonically increasing Load Date Timestamp. This preserves the precise order in which changes occurred in the operational source system while satisfying the primary key requirements of the standard Satellite structure.
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.
Architectural Integrity in Enterprise Data Platforms
Maintaining clean separation between source ingestion, business rules, and presentation layers is essential for long-term scalability. Combining multiple source systems into a single raw Satellite to avoid duplicate records introduces severe maintenance liabilities, forces pipeline dependencies, and undermines the modularity of the data platform.
Keeping raw data isolated by source system within the Raw Data Vault layer guarantees that operational failures in one source do not disrupt the availability of independent domain pipelines. The Business Vault layer, empowered by Point-in-Time tables and rule-driven Information Mart views, handles source harmonization, prioritization logic, and deduplication dynamically. Reviewing your platform architecture through comprehensive data platform review services ensures that your ingestion pipelines balance high throughput with total auditability.
By applying deterministic sequence ordering to intra-batch CDC records and leveraging PIT-backed COALESCE selection across multi-source Satellites, data teams effectively resolve all forms of contradicting facts—ensuring high query performance and absolute governance across the entire enterprise data asset.
