All Posts By

Michael Olschimke

Michael Olschimke is the Co-Founder and CEO of Scalefree and a "Data Vault 2.0 Pioneer" with over 20 years of IT experience. A Fulbright scholar and co-author of Building a Scalable Data Warehouse with Data Vault 2.0, Michael is a global authority on AI, Big Data, and scalable Lakehouse design across sectors like banking, automotive, and state security.

In Data Vault Friday

Define Type 2 Dimension Keys in Data Vault

Resolving the Type 2 Dimension Key Challenge in Data Vault Architectures

In modern Enterprise Data Warehouse (EDW) and Data Lakehouse architectures built on Data Vault 2.0, delivering user-friendly information marts is a core objective. Business intelligence (BI) developers and analytics engineers often rely on dimensional modeling—specifically star schemas—to provide high-performance, intuitive structures for reporting tools. However, bridging the gap between Data Vault structures and dimensional models can introduce architectural friction, particularly when handling slowly changing dimensions (SCDs).

A primary hurdle data teams encounter is referencing specific row versions in a Type 2 dimension from a fact table or bridge entity. While Data Vault provides a robust, scalable framework for loading raw and business data, mapping those patterns directly into dimensional models requires careful key management. Understanding how to generate, maintain, and reference a Type 2 hash value ensures high performance, seamless cross-environment deployment, and complete historical integrity across your analytics ecosystem.

Understanding the Core Problem: Type 1 vs. Type 2 Keys

To understand why this issue arises, we must look at how keys are defined in a standard Data Vault model versus a dimensional star schema.

In Data Vault, business entities are represented by Hubs, and their relationships are captured in Links. Attributes and their historical changes reside in Satellites.

  • Hub Keys (Type 1): The primary key of a Hub (or Link) is typically a deterministic hash key computed solely from the business key (e.g., MD5(Customer_ID) or SHA256(Customer_ID)). Because a business key remains constant for a given entity, this hash key is immutable. In dimensional terms, this acts like a Type 1 key—it identifies the business entity itself, but not a specific historical version of that entity’s attributes.

  • Satellite Rows (Type 2): Historical changes are tracked in Satellites by appending a load date or snapshot timestamp to the hash key. Thus, a unique state of an entity is identified by the composite key: (Hash Key + Snapshot Date/Load Date).

When transitioning to a Kimball-style star schema, the BI layer expects a single surrogate key—a Type 2 dimension key—for every distinct row version in a dimension table. This unique identifier allows fact records to join directly to the exact point-in-time attribute state of an entity.

+-----------------------------------------------------------------------------------+
|                                   DATA VAULT                                      |
|                                                                                   |
|   +-----------------------+               +-----------------------------------+   |
|   |       Hub_Customer    |               |          Sat_Customer             |   |
|   +-----------------------+               +-----------------------------------+   |
|   | HK_Customer (Type 1)  | <-----------+ | HK_Customer                       |   |
|   | Customer_ID           |               | Load_Date                         |   |
|   +-----------------------+               | Customer_Name, Address, Status... |   |
|                                           +-----------------------------------+   |
+-----------------------------------------------------------------------------------+
                                         |
                                         | Derived via PIT Table
                                         v
+-----------------------------------------------------------------------------------+
|                                DIMENSIONAL MART                                   |
|                                                                                   |
|                           +---------------------------+                           |
|                           |       Dim_Customer        |                           |
|                           +---------------------------+                           |
|                           | HK_Customer_T2 (Type 2)   | <--- Needs Unique Row Key |
|                           | Customer_ID               |                           |
|                           | Customer_Name             |                           |
|                           | Valid_From / Valid_To     |                           |
|                           +---------------------------+                           |
+-----------------------------------------------------------------------------------+

Without a unified Type 2 surrogate key, data modeling teams face significant challenges when wiring fact tables to dimensions, particularly in heterogeneous or distributed database environments.

Point-in-Time (PIT) Tables as the Dimension Foundation

To resolve point-in-time relationships without performing costly, multi-table snapshot joins at query time, Data Vault utilizes Point-in-Time (PIT) tables. A PIT table acts as an query-assistance structure within the Business Vault. It matches the hash key of an entity with the active satellite record timestamps at specific snapshot intervals (e.g., daily or hourly).

In a standard PIT table, the grain of the table is defined by two key elements:

  1. Entity Hash Key (HK_Customer): The Type 1 identifier of the business key.

  2. Snapshot Timestamp (Snapshot_Date): The point in time for which the entity state is calculated.

Together, (HK_Customer, Snapshot_Date) form a composite alternate key. While this composite key uniquely identifies a specific row version in the PIT table, relying on a two-column composite key to join fact tables to dimensions introduces several operational drawbacks:

  • Tool Incompatibility: Certain legacy BI suites, reporting applications, and semantic layers struggle with composite foreign key joins or exhibit degraded query generation performance.

  • Increased Storage and Join Overhead: Joining multiple big data tables over multiple columns increases query complexity, memory utilization, and execution plan overhead.

To streamline downstream consumption, data architects must derive a single primary key from the PIT table to serve as the Type 2 dimension key.

Surrogate Keys in PIT Tables: Sequences vs. Hash Values

When selecting a single primary key strategy for PIT tables and Type 2 dimensions, two primary approaches emerge: auto-incrementing sequence keys and deterministic hash values.

The Sequence Key Approach

Data Vault Alliance (DVA) patterns historically supported the use of sequence numbers (e.g., auto-incrementing integers) as surrogate primary keys for PIT tables and dimensions. Sequences offer simple integer joins and minimal storage footprints.

However, sequence keys introduce significant limitations in modern, distributed data platform architectures:

  • Lack of Synchronization: Sequences depend on localized database engines to generate values sequentially. If your data architecture spans hybrid environments—such as storing heavy IoT fact data in a cloud platform like Snowflake or Databricks while hosting dimensional marts on-premise or in a separate cloud instance—sequence values cannot be synchronized naturally across systems.

  • Pipeline Dependencies: Generating sequences requires serial execution or centralized coordination during processing, which impedes mass parallel processing (MPP) and distributed cloud pipelines.

The Type 2 Hash Value Strategy

To overcome the limitations of sequences, a deterministic Type 2 hash value can be calculated directly within the PIT table. Unlike a standard Type 1 hash key derived solely from the business key, a Type 2 hash value is computed by hashing the combination of the business key (or Hub hash key) and the snapshot timestamp.

$$\text{Type 2 Hash Value} = \text{Hash Function}(\text{Business Key} \mathbin{\Vert} \text{Snapshot Date})$$

Using a deterministic algorithm (such as MD5 or SHA-256) offers compelling architectural advantages:

  • Environment Independence: Because the computation relies purely on data values rather than stateful sequence generators, the calculated hash value is identical regardless of where or when it is processed. You can calculate the key in an AWS data lakehouse, an Azure SQL environment, or an on-premise database, and the resulting keys will align perfectly.

  • Fully Parallelizable: Hash calculation is stateless and native to big data engine transformations, making it ideal for ELT frameworks built on tools like dbt, Spark, or native cloud data warehouses.

If you are evaluating modern transformation tools to orchestrate these models, exploring options like datavault4dbt for automated modeling can significantly accelerate your pipeline deployment.

Resolving Type 2 Dimension Keys from Fact Entities and Bridge Tables

Calculating the Type 2 hash value inside the PIT table and applying it to the Type 2 dimension table solves half the equation. The remaining challenge is attaching that exact same Type 2 dimension key to your fact entities, bridge tables, or transactional links.

A fact table record typically records an event at a specific point in time. In a Data Vault environment, fact structures—such as Bridge tables, non-historized Links, or transactional Satellites—contain:

  1. The Type 1 Hub Hash Key (HK_Customer).

  2. An event or transaction timestamp.

To join a fact record to the corresponding version in a Type 2 dimension, the fact entity must obtain the matching Type 2 hash value.

Handling Non-Historized Links and Transactional Messages

In near-real-time streaming scenarios or non-historized Link implementations (e.g., high-volume IoT messages or API transactions), an incoming event timestamp might not match an exact PIT snapshot timestamp directly.

To bridge this gap, the loading logic for the fact entity or Bridge table calculates the next feasible snapshot timestamp during ELT processing. For example, if a message arrives at 11:45 PM and daily snapshot PIT tables are evaluated at 09:00 AM, the transformation logic assigns the next applicable snapshot date (e.g., the next morning at 09:00 AM) to the event record.

+-----------------------------------------------------------------------------------+
|                             BRIDGE / FACT SELECTION                               |
|                                                                                   |
|   +---------------------------------------------------------------------------+   |
|   | Event_ID | HK_Customer (Type 1) | Transaction_Date | Applicable_Snapshot    |   |
|   +---------------------------------------------------------------------------+   |
|   | 982341   | 0x4A8F...           | 2026-10-14 23:15 | 2026-10-15 09:00:00     |   |
|   +---------------------------------------------------------------------------+   |
+-----------------------------------------------------------------------------------+
                                         |
                                         | Join on Alternate Key:
                                         | (HK_Customer + Snapshot_Date)
                                         v
+-----------------------------------------------------------------------------------+
|                                    PIT TABLE                                      |
|                                                                                   |
|   +---------------------------------------------------------------------------+   |
|   | HK_Customer_T2 (Primary Key) | HK_Customer (Type 1) | Snapshot_Date       |   |
|   +---------------------------------------------------------------------------+   |
|   | 0x9B22... (Type 2 Hash)      | 0x4A8F...            | 2026-10-15 09:00:00 |   |
|   +---------------------------------------------------------------------------+   |
+-----------------------------------------------------------------------------------+
                                         |
                                         | Extract Primary Key
                                         v
+-----------------------------------------------------------------------------------+
|                                  FINAL FACT TABLE                                 |
|                                                                                   |
|   +---------------------------------------------------------------------------+   |
|   | Event_ID | HK_Customer_T2 (Type 2 Foreign Key) | Amount | Transaction_Date|   |
|   +---------------------------------------------------------------------------+   |
|   | 982341   | 0x9B22...                           | 150.00 | 2026-10-14 23:15 |   |
|   +---------------------------------------------------------------------------+   |
+-----------------------------------------------------------------------------------+

The Recommended Lookup Pattern: PIT Join vs. Virtual Re-computation

Once the fact entity has both the Type 1 Hash Key and the applicable Snapshot Timestamp, there are two potential ways to append the Type 2 Hash Key to the fact table:

  1. Virtual Re-computation: Re-calculating the hash algorithm inside a virtual presentation view over the fact table by joining back to the Hub to retrieve the raw business key, concatenating it with the snapshot timestamp, and running the hash function dynamically.

  2. PIT Lookup Join (Best Practice): Joining the fact or bridge structure directly to the existing PIT table using the alternate key (HK_Customer, Snapshot_Date) to retrieve the pre-calculated HK_Customer_T2 primary key.

Virtual re-computation inside presentation views introduces significant query degradation when end users run complex reports over large datasets. Dynamic text concatenation and hashing across millions of rows at query execution time stresses database CPU and memory resources.

The recommended best practice is to perform a simple point lookup during the physical loading or staging of the fact or Bridge structure:

  • Join the fact entity to the PIT table on HK_Customer = PIT.HK_Customer AND Applicable_Snapshot = PIT.Snapshot_Date.

  • Extract the pre-computed HK_Customer_T2 hash value from the PIT table.

  • Store HK_Customer_T2 directly within the fact table as a single-column foreign key.

Because both the Type 2 Dimension and the Fact Table retrieve their surrogate keys directly from the underlying PIT table, key alignment is guaranteed across the entire star schema. To dive deeper into optimizing these dimensional bridge patterns, review our technical guide on Bridge Tables in Data Vault.

Architectural Best Practices for Enterprise Deployment

Implementing a robust Type 2 hash key pattern requires maintaining clear alignment across storage layers, platform environments, and modeling standards.

In this article:


Implementing Type 2 Dimensions in Scalable Data Architectures

Designing a scalable enterprise data warehouse requires balancing raw historical auditability with intuitive analytical performance. By adopting Type 2 hash values derived from Point-in-Time tables, data architects eliminate the operational friction of sequence management while maintaining clear point-in-time accuracy across star schemas.

This pattern empowers teams to run distributed, multi-cloud analytics without sacrificing join performance or structural consistency. Whether you are modernizing an existing data warehouse or establishing a cloud-native platform on Snowflake or Databricks, aligning your dimensional modeling strategy with proven Data Vault standards ensures long-term flexibility and maintainability.

To evaluate your current platform architecture or learn more about building enterprise-grade Data Vault solutions, explore our comprehensive Data Vault 2.1 Training & Certification programs or download The Data Vault Handbook to guide your team’s modeling strategy.

Watch the Video

In Data Vault Friday

Pre-Joining Business Keys to Load the Raw Data Vault

Handling Surrogate Keys in Source Systems for Data Vault Modeling

When data engineering teams begin ingesting operational source systems into an enterprise data warehouse, they frequently encounter a common structural design choice: relational databases that rely entirely on surrogate keys instead of natural or business keys. In highly normalized operational platforms—such as modern enterprise resource planning (ERP) systems, customer relationship management (CRM) tools, and specialized content management platforms—database architects intentionally replace wide, multi-column natural keys with compact, single-column technical identifiers. While this practice delivers major performance benefits for operational online transaction processing (OLTP) workloads, it introduces specific challenges when loading data into a Data Vault architecture.

Understanding why operational systems use surrogate keys, how this design conflicts with enterprise integration requirements, and how to resolve the mismatch in your staging environment allows data architects to build scalable, resilient pipelines. By adopting standardized staging pre-joins and structured lookups, teams can ingest surrogate-key-driven source systems directly into a Raw Data Vault while preserving cross-system integration and historical traceability.

In this article:


Operational Database Design: Why Source Systems Favor Technical IDs

Operational software developers learn early in their training to isolate database performance from business logic changes. In software engineering, replacing a multi-column business key—such as a combination of country code, branch identifier, and account sequence—with an auto-incrementing integer or a synthetic big integer offers immense operational benefits. Natural business keys tend to be wide text strings with variable lengths, whereas synthetic technical keys occupy fixed, minimal byte sizes on disk and in memory.

Because database indexes scale their performance based on the width of the indexed columns, smaller surrogate key columns yield significantly smaller index trees. This index compression allows database engines to fit far more index pages into active memory, dramatically accelerating point lookups and single-record updates. Furthermore, joining two operational tables on single-column integer keys consumes far fewer computational cycles and memory overhead than multi-column string joins.

Another driving factor for technical IDs in operational systems is normalization. In complex software applications, even simple status attributes, lookup types, and reference values are often offloaded to centralized key-value tables. Every transactional entity references these lookup items using internal surrogate keys. This ensures strict referential integrity within the boundary of that specific application database, but it obscures the underlying business meaning from external consumers who need human-readable context.

The Data Integration Dilemma: Surrogate Keys vs. Shared Business Keys

While technical surrogate keys are optimal for application performance within a single operational database, they fail as integration keys across an enterprise data landscape. The fundamental purpose of a Data Vault Hub core entity is to establish a single, shared business key that integrates identical real-world concepts across independent operational systems.

If a customer exists in a CRM system with a synthetic internal ID of 10042 and in an ERP system with a synthetic internal ID of 88391, storing these technical values as Hub primary keys fragments the data. The Hub would create two distinct business key hash keys for the exact same customer, completely defeating the purpose of enterprise data integration. A surrogate key is fundamentally an internal artifact bound strictly to one application instance; it carries no semantic meaning outside that specific database schema.

To maintain a true enterprise model, the Raw Data Vault must model entities based on genuine business keys—identifiers assigned, recognized, and managed by the business users, such as a Tax ID, Social Security Number, SKU, or Customer Account Number. When source systems deliver data anchored entirely by technical IDs, the data ingestion framework must resolve those technical IDs back to their corresponding business keys before or during the loading process into the Data Vault.

The Staging Pre-Join Strategy: Denormalizing Data at Ingestion

The standard, vendor-agnostic method for addressing surrogate-key operational sources in Data Vault modeling is the pre-join pattern. In Data Vault methodology, the staging layer serves as an intermediate zone designed to prepare raw source data for hash key derivation, metadata tagging, and staging transformations. While cross-system data joins are strictly forbidden in staging to prevent early business logic coupling, performing a single-source denormalization pre-join within the boundaries of a single source system is completely compliant and widely encouraged.

How the Pre-Join Pattern Operates

Consider a sales transaction table in a CRM or ERP source system. The transaction line item records an order ID, a customer technical key, and a product technical key, but omits the human-readable product SKU and customer account number. Somewhere within that same source database schema resides a product master table and a customer master table, both containing the mapping between their respective technical surrogate keys and the natural business keys.

+-----------------------------------------------------------------------------------+
|                              Operational Source System                            |
|                                                                                   |
|   Sales Transactions Table                    Product Master Table                |
|   +-----------------------+                   +-----------------------+           |
|   | txn_id        (PK)    |                   | prod_surrogate_key(PK)|           |
|   | prod_surrogate_key(FK)| ----------------> | prod_sku       (BK)   |           |
|   | cust_surrogate_key(FK)|                   +-----------------------+           |
|   +-----------------------+                                                       |
+-----------------------------------------------------------------------------------+
                                        |
                                        |  Pre-Join Transformation (Staging View Layer)
                                        v
+-----------------------------------------------------------------------------------+
|                                  Staged Data Set                                  |
|                                                                                   |
|   Enriched Transaction Staging Table / View                                       |
|   +---------------------------------------------------------------------------+   |
|   | txn_id  | prod_surrogate_key | prod_sku (BK) | cust_surrogate_key | ...    |   |
|   +---------------------------------------------------------------------------+   |
+-----------------------------------------------------------------------------------+
                                        |
                                        |  Derive Hash Keys from Resolved Business Keys
                                        v
+-----------------------------------------------------------------------------------+
|                                  Raw Data Vault                                   |
|                                                                                   |
|   Hub_Product (HK: prod_sku)   <---   Link_SalesTxn   --->   Hub_Customer (...)   |
+-----------------------------------------------------------------------------------+

To prepare this transaction data for the Raw Data Vault, a view layer is defined directly on top of the raw landed staging tables or Data Lake landing files. This view executes a join between the transaction table and the reference master tables using the source system’s technical keys:

  1. The transaction record supplies the operational event data and the internal surrogate keys.

  2. The internal surrogate keys are joined against the source system’s own master tables.

  3. The lookup query retrieves the natural business keys (such as the product SKU and customer account number).

  4. The output view presents a consolidated, denormalized record containing both the operational payload and all necessary business keys.

  5. Hash key calculation functions execute directly against these retrieved business keys to generate the Hub and Link hash keys required for Raw Data Vault loading.

Because this pre-join takes place entirely within the domain of a single source system, it does not introduce cross-source dependency or enforce artificial business rules. It simply re-establishes the full context of the entity as it originally existed within the source environment.

To master these core design principles and implement resilient staging pipelines for your enterprise, consider enrolling in formal Data Vault 2.1 Training & Certification.

Resolving Lookup Dependencies Across Full Loads, CDC, and Data Lakes

While pre-joining source tables in a staging view works seamlessly when processing complete source table extracts (full snapshot loads), operational data pipelines frequently rely on Change Data Capture (CDC) or delta extraction feeds. Delta feeds introduce temporal lookup challenges that require specific architectural patterns.

The Delta Feed Lookup Problem

In a CDC streaming or batch pipeline, a transaction table might emit a newly inserted record containing a customer technical key. However, because the customer’s demographic profile was not modified during that transaction event, the customer table emits no CDC change event. As a result, the incoming micro-batch contains the transaction event with a surrogate key, but the staging environment lacks an incoming customer master record to join against.

To resolve these missing business key lookups in delta environments, data architects utilize three primary historical lookup mechanisms:

  • Persistent Staging Area (PSA) / Data Lake Lookups: When using an append-only Data Lake or Persistent Staging Area that maintains all historical landed files, the staging view query can search backwards through past landed batches. By querying the most recent historic snapshot of the master table stored in the Data Lake, the view resolves the technical key to its corresponding business key without re-extracting the entire master table from the source OLTP database.

  • Target Data Vault Forward Lookups: If past historical files are not accessible in the staging area, the staging process can perform a forward lookup against the Raw Data Vault itself. When the source master table was previously loaded into the Data Vault, the business key was stored in the core Hub, while the source technical surrogate key was stored alongside other descriptive attributes inside an attached Satellite. The staging pipeline can query this target Satellite, locate the matching technical surrogate key, retrieve the associated business key or hash key, and complete the current transaction load.

  • Automated Transformation Frameworks: Modern data transformation suites simplify this lookup logic by generating standardized pre-join SQL patterns automatically. Orchestration platforms integrated with automation libraries like datavault4dbt natively support pre-join declarations in model configurations, ensuring that technical-to-business key resolutions remain maintainable and performant across large-scale data platforms.

Advanced Handling: Source Vaults and Deferred Business Integration

In rare operational scenarios, source systems deliver incoming transaction feeds containing technical surrogate keys, but the corresponding master records mapping those surrogate keys to business keys are missing, corrupted, or delayed due to application timing issues. When a pipeline receives an operational surrogate key that cannot be resolved to a natural business key through local pre-joins or historic lookups, data engineers must choose between two primary handling strategies.

Option 1: Ghost Records and Error Handling

The preferred Data Vault approach for transient missing keys is inserting a standardized Ghost Record (or Unknown Member) into the target Hub. The transaction is loaded into the Link table using a pre-defined error hash key representing “Unknown Business Key.” Once the operational source system resolves the missing master record and delivers the natural key in a subsequent extract batch, an automated re-keying process or point-in-time calculation updates the downstream relationship references without breaking loading pipelines or causing load failures.

Option 2: The Source Vault Pattern

When an operational system is completely incapable of providing reliable business keys over extended periods, data architects may temporarily deploy a Source Vault pattern. In a Source Vault, the technical surrogate key provided by the source database is temporarily treated as the driving identifier in a localized Hub structure.

+-----------------------------------------------------------------------------------+
|                                 Source Vault Zone                                 |
|                                                                                   |
|   Hub_LocalCustomer                                                               |
|   +---------------------------------------------------------------------------+   |
|   | HK_LocalCustomer (Hash of System_A_Surrogate_Key)                         |   |
|   | System_A_Surrogate_Key                                                    |   |
|   +---------------------------------------------------------------------------+   |
+-----------------------------------------------------------------------------------+
                                        |
                                        |  Business Rule Integration (Business Vault)
                                        v
+-----------------------------------------------------------------------------------+
|                                 Business Vault Zone                               |
|                                                                                   |
|   Same-As Link (SAL_Customer) / Business Hub                                      |
|   +---------------------------------------------------------------------------+   |
|   | HK_EnterpriseCustomer  <--->  HK_LocalCustomer                            |   |
|   +---------------------------------------------------------------------------+   |
+-----------------------------------------------------------------------------------+

While this allows incoming transaction data to be ingested immediately without blocking pipelines, it restricts multi-source integration at the Raw Data Vault layer. To achieve cross-system integration later on, the team must construct Same-As Links (SAL) or mapping logic within the Business Vault layer once business rules or cross-reference mappings become available. Because this approach defers core integration and adds architectural overhead, it should be reserved as an exception mechanism rather than a primary modeling strategy.

To learn more about implementing robust staging strategies, mastering advanced lookup patterns, and designing enterprise-grade Data Vault solutions for your organization, reach out to our expert team for dedicated guidance on Data Vault consulting.

Watch the Video

In Data Vault Friday

Alternatives to End-Dating Views for Data Vault Satellites

High-Volume Data Vault End-Dating: Virtualization and Point in Time Optimization Strategies

Managing end-dated history across high-volume Satellite tables is a frequent operational challenge in enterprise data architectures. When Satellites scale beyond 100 million records, standard relational patterns that rely on materializing end dates through SQL analytical functions often encounter significant performance degradation. In high-throughput Data Vault 2.0 deployments, maintaining a materialized view that derives load end dates via window functions introduces severe compute overhead, data locking, and extended processing windows. Enterprise architectures running on modern database platforms such as Oracle, Snowflake, or SQL Server require scalable strategies to streamline or eliminate these intermediate materializations while maintaining query integrity and downstream performance.

The fundamental operational bottleneck stems from duplicate materialization steps within the data pipeline. Many teams build a materialized end-dating view based on the windowed LEAD() function to determine when a given Satellite record was superseded. This view is then used downstream both for change detection during incremental Satellite loads and as a filtering layer during the population of Point in Time (PIT) tables. When loading PIT tables, the pipeline applies date ranges against these materialized end dates, resulting in a secondary materialization step. Executing two sequential materializations for high-volume datasets multiplies compute consumption, extends batch execution windows, and introduces unnecessary structural dependencies into the data warehouse architecture.

To eliminate this performance bottleneck, data engineers must re-evaluate the core architectural purpose of the load end date. By adopting pure virtualization strategies and leveraging optimized Point in Time loading mechanisms, enterprises can achieve dramatic performance improvements without sacrificing historical accuracy or report stability.

In this article:


The Hidden Costs of Materialized End-Dating Views

In a standard Data Vault implementation, Satellites capture descriptive state over time using an insert-only pattern. Each record receives a load date timestamp indicating when it entered the system. To simplify historical point-in-time querying without specialized join structures, some teams introduce a Load End Date column, calculated as the moment immediately preceding the subsequent load date for a given business key or hash key.

Calculating this timestamp relies on the SQL LEAD() window function partitioned by the target hash key and ordered by the load date. When this logic is wrapped inside a materialized view, the database engine must evaluate the window function across the entire historical record set or handle complex incremental updates. In massive datasets containing over 100 million rows, maintaining this materialized state presents several technical challenges:

  • High Re-computation Overhead: Appending a new delta to a Satellite alters the end date of the previously active record for that specific hash key. Because an existing record’s attribute value changes upon every load, the underlying data structure loses its pure insert-only advantages.

  • Full View Refreshes: Many relational engines and cloud data platforms cannot perform incremental maintenance on materialized views that contain complex partition-by analytical functions like LEAD(). As a result, the engine defaults to a full view refresh, processing all 100 million-plus records merely to update the small percentage of keys modified in the latest load.

  • Redundant Intermediate I/O: Materializing the view writes millions of derived rows to disk, only for the subsequent PIT table loading process to read those same rows back, perform date-range filtering, and write the final PIT structure.

Because end dates change dynamically whenever new record versions arrive, attempting to maintain physical materialized views at scale consumes significant disk I/O and processing budget. Understanding that the load end date is a derived, non-essential attribute is the first step toward streamlining the overall pipeline.

Rethinking PIT Table Loads Without Materialized End Dates

Point in Time (PIT) tables serve as performance-tuning structures in a Data Vault architecture. They act as query accelerators that bridge Hubs and Satellites, storing the exact foreign keys and load dates needed to retrieve the correct Satellite version for any given snapshot date. A common misconception is that building a PIT table requires pre-calculated load end dates within the Satellite or an intermediate end-dating view.

Standard PIT table loading procedures can identify the active delta for any snapshot timestamp directly from the underlying Satellite using load dates alone. By querying the Satellite table using a maximum load date subquery—where the Satellite load date is less than or equal to the target snapshot date—the engine isolates the active hash key and load date pairing natively.

SQL

SELECT
    p.SnapshotDate,
    h.AccountHashKey,
    MAX(s.LoadDate) AS SatelliteLoadDate
FROM HubAccount h
CROSS JOIN DimSnapshotDate p
LEFT JOIN SatAccount s
    ON h.AccountHashKey = s.AccountHashKey
   AND s.LoadDate <= p.SnapshotDate
GROUP BY
    p.SnapshotDate,
    h.AccountHashKey;

When executing this pattern, the query optimizer evaluates the condition based purely on indexing or micro-partitioning around the Hash Key and Load Date composite primary key. There is no requirement for a BETWEEN clause referencing a Load End Date. By eliminating the materialized end-dating view from the PIT load pipeline, the architectural flow shifts from two sequential disk materialization steps down to a single optimized write operation into the physical PIT table.

Furthermore, removing intermediate materializations allows database platforms to streamline execution plans. Modern query engines can combine the inline evaluation of window functions or subqueries directly into the set-based INSERT string loading the PIT table, reducing redundant temporary storage and parallelizing I/O across available compute nodes.

Evaluating Virtualized End-Dating Views vs. Inline Logic

If end dates are not strictly required for PIT table generation, why do end-dating views persist in Data Vault modeling discussions? In practice, end-dating views serve two primary administrative and development convenience functions:

  1. Source-to-Target Change Detection: During incremental Satellite loads, incoming staging records are compared against the active Satellite version to filter out unchanged records (delta hashing). A virtualized view providing the current active record simplifies this comparison logic.

  2. Ad-Hoc Developer Convenience: Data analysts and developers frequently write direct ad-hoc queries against historical Satellites. A virtual view exposing a calculated end date allows users to write intuitive WHERE @TargetDate BETWEEN LoadDate AND LoadEndDate queries without manually constructing window functions.

To balance developer ergonomics with high-volume performance, technical architects recommend using fully virtualized (non-materialized) end-dating views. A standard relational view defined with a windowed LEAD() calculation carries zero storage footprint and requires no batch maintenance time:

SQL

CREATE VIEW VW_SatAccount_EndDated AS
SELECT
    AccountHashKey,
    LoadDate,
    LEAD(LoadDate, 1, TIMESTAMP '8888-12-31 23:59:59') OVER (
        PARTITION BY AccountHashKey 
        ORDER BY LoadDate
    ) AS LoadEndDate,
    RecordSource,
    HashDiff,
    AccountStatus,
    CreditLimit
FROM SatAccount;

When a pipeline queries a virtualized view during an incremental load, the database engine does not compute the LEAD() function across all 100 million rows in the base table. Instead, through query pushdown and predicate filtering, the engine restricts the window calculation exclusively to the subset of hash keys present in the incoming staging batch. If a daily delta touches only 2% of the total hash keys in a Satellite, the compute cost for the window function drops by 98% compared to a full materialized view refresh.

Engineers seeking formal expertise in implementing these query optimization patterns and satellite loading standards can explore structured learning programs such as Data Vault 2.1 Training & Certification.

Eliminating End Dates from Downstream Query Patterns

In a fully optimized Data Vault 2.0 architecture, business-facing query layers—such as Information Marts, dimensional views, and flat reporting tables—should never reference or join against Satellite Load End Dates. Relying on end dates in reporting views introduces complex range joins (BETWEEN) that impede database optimizers and prevent effective join elimination.

Instead, all dimensional constructs should join Information Mart views to Satellites using the PIT table as the primary driver. The PIT table supplies the exact composite primary key (HashKey + LoadDate) required for an equi-join into the target Satellite. Equi-joins on hashed primary keys perform orders of magnitude faster than range-based date evaluations, particularly in high-concurrency BI environments.

SQL

SELECT
    p.SnapshotDate,
    h.AccountNumber,
    s.AccountStatus,
    s.CreditLimit
FROM PitAccount p
JOIN HubAccount h 
    ON p.AccountHashKey = h.AccountHashKey
JOIN SatAccount s 
    ON p.AccountHashKey = s.AccountHashKey 
   AND p.SatAccountLoadDate = s.LoadDate
WHERE p.SnapshotDate = :ReportingDate;

Using PIT tables to drive Information Marts offers an essential operational advantage: snapshot stability. In real-time or high-frequency loading environments, data continuously streams into Raw Data Vault Satellites. If a dimensional reporting view queries a Satellite directly using a calculated end date, two execution runs of the same report within minutes of each other could return different results if a background load inserted a new record version in the interim.

By routing queries through a PIT table generated at a fixed snapshot time (for example, daily at 06:00 AM), business reporting remains fully stable throughout the business day. The PIT table locks the reporting context to a specific point in time, isolating end-user dashboards from ongoing background ETL ingestion.

To review architectural frameworks for structuring high-performance reporting layers on top of Data Vault stores, consult Scalefree’s consulting services.

Advanced Architecture: The Snapshot Partitioning Strategy

While virtualized end-dating views resolve processing bottlenecks for the vast majority of high-volume deployments, certain edge scenarios—such as legacy database platforms with inefficient window function implementations or extreme data volumes—may still experience latency during batch loads. For these constrained environments, architects can utilize an advanced design pattern known as the Snapshot Partitioning Strategy.

This pattern repurposes the standard PIT table to act as an active-record index for Satellite loading operations, eliminating the need for windowed functions entirely.

Conceptual Mechanics

A standard PIT table contains partitions organized by discrete historical snapshot dates (e.g., daily or hourly snapshots). In the Snapshot Partitioning Strategy, an additional, dedicated snapshot partition is added to the PIT structure representing the “end of time”—typically assigned a far-future timestamp such as 8888-12-31 23:59:59.

Because 8888-12-31 is greater than any actual system load date, running the standard PIT population logic for this specific snapshot date forces the routine to identify the latest active LoadDate for every single hash key currently residing in the Satellite.

                    PIT TABLE STRUCTURE
+---------------------+-------------------+---------------------+
|    SnapshotDate     |  AccountHashKey   | SatAccountLoadDate  |
+---------------------+-------------------+---------------------+
| 2026-10-14 00:00:00 | 0x4A8F...         | 2026-05-12 14:22:10 |  <- Historical Snapshot
| 2026-10-15 00:00:00 | 0x4A8F...         | 2026-10-10 09:15:00 |  <- Historical Snapshot
| 8888-12-31 23:59:59 | 0x4A8F...         | 2026-10-10 09:15:00 |  <- Active Record Partition
+---------------------+-------------------+---------------------+

Operational Batch Workflow

By maintaining this far-future snapshot partition inside the PIT table, data pipelines can eliminate window functions from Satellite change-detection workflows using a simple step-by-step sequence:

  1. Staging Change Detection: When a new batch arrives in the staging layer, the staging load query joins against the 8888-12-31 partition of the PIT table to retrieve the latest Satellite load date for each incoming key.

  2. Direct Satellite Comparison: Using the retrieved load date, the pipeline performs an equi-join directly to the target Satellite to fetch the current HashDiff and evaluate whether true attribute changes occurred.

  3. Satellite Ingestion: New and modified records are inserted into the Satellite with the current batch timestamp.

  4. Partition Refresh: Upon completion of the batch load, the pipeline drops and regenerates only the 8888-12-31 partition in the PIT table to capture the newly inserted Satellite load dates.

This strategy trades a minor post-load maintenance step—rebuilding a single partition—for the complete removal of expensive LEAD() window scans across multi-hundred-million-row tables. For detailed guidance on structuring enterprise pipelines and physical storage layouts, explore The Data Vault Handbook.

Summary Recommendation Matrix

Choosing the right end-dating strategy depends on your platform’s compute engine, underlying data volume, and pipeline performance targets. The following matrix outlines the recommended approaches across common enterprise scenarios:

Platform & Data Scale Primary Recommendation Secondary / Alternative Strategy Key Architectural Benefit

Cloud Data Warehouses

 

(Snowflake, Databricks, BigQuery)

 Fully Virtualized View Direct inline windowing in PIT scripts Eliminates storage footprint; compute scales dynamically with incoming batch size.

High-Performance RDBMS

 

(Oracle, SQL Server 2016+)

 Fully Virtualized View Snapshot Partitioning Strategy Avoids full view materialization; leverages predicate pushdown on hash key indexes.

Legacy/Constrained Engines

 

(PostgreSQL, older SQL Server)

 Snapshot Partitioning Strategy Optimized base subqueries without end dates Completely bypasses slow LEAD() evaluations by utilizing partitioned PIT indexes.

By eliminating physical end-date materialization, data engineering teams can transform a major pipeline bottleneck into a streamlined, scalable processing workflow. Relying on virtualized views for change detection and utilizing PIT tables for downstream Information Mart queries maintains strict Data Vault 2.0 compliance while delivering superior performance across high-volume data estates.

Watch the Video

In Data Vault Friday

Subsetting Business Keys in Data Vault Hubs

Subsetting Business Keys: Modeling Shared Entities in Raw vs. Business Vault

Managing heterogeneous business objects stored within a single operational table presents a classic data modeling dilemma for enterprise data teams. Operational software vendors often build flexible schemas to accommodate broad usage patterns, placing diverse entities like customers, suppliers, contract holders, and injured parties into a single generic table such as a partner database. When designing an enterprise analytical platform, data architects must decide whether to split these entities during the initial load or maintain source alignment. Within the framework of Data Vault 2.0, the resolution lies in establishing a clear boundary between the Raw Data Vault and the Business Vault.

Understanding how to model shared business keys requires examining the foundational principles of Data Vault architecture. Operational systems frequently force distinct business concepts into shared structures due to software constraints or legacy design decisions. However, analytical environments demand a representation that reflects the actual semantic model of the enterprise. By applying soft rules in the Business Vault while keeping the Raw Data Vault strictly aligned with source systems, organizations can maintain auditability while delivering flexible, domain-specific data structures to business users.

To master these architectural patterns and ensure your enterprise warehouse scales effectively, explore our comprehensive Data Vault 2.1 Training & Certification programs designed for data engineers and solution architects.

In this article:


The Challenge of Mixed Business Objects in Source Systems

Operational software packages—ranging from enterprise resource planning systems like SAP to custom legacy applications—frequently consolidate multiple real-world entities into a single relational table. A common example occurs in insurance core systems, where a single table records all individuals or organizations involved in a policy or claim. Within this single structure, the record could represent a policyholder, a beneficiary, an injured party, or a third-party driver. Similarly, commercial ERP systems often manage both customers and vendors inside a unified party or partner table.

This consolidation happens because software vendors prioritize operational efficiency, generic software design, and database normalization over business semantics. The operational application might use a unified primary key generator across all partner types, meaning that customers and suppliers share the same ID space and column attributes. While this architecture serves the immediate transactional workflow of the application, it creates significant friction when business users attempt to query, report on, or analyze specific business entities.

When enterprise data teams ingest these unified tables into an analytical environment, they encounter a fundamental semantic mismatch. Business stakeholders rarely ask questions about a generic party; instead, they query specific business concepts, such as customer lifetime value or vendor lead times. If data architects attempt to resolve this mismatch by filtering and splitting data prior to storage, they risk compromising the integrity and auditability of the underlying data platform.

The Raw Data Vault Perspective: Absolute Source Fidelity

The primary objective of the Raw Data Vault is to capture an unmodified, historical record of source system data. To preserve absolute auditability, data must be loaded into the Raw Data Vault without applying business filters, conditional transformations, or selective routing. The only condition permitted during a Raw Data Vault load is a delta check to determine whether a business key or satellite record has already been ingested.

When processing a source table that contains mixed business objects, the Raw Data Vault design must mirror the source structure. If the source system maintains a single partner table, the Raw Data Vault must implement a single partner Hub (h_partner) along with one or more corresponding partner Satellites (s_partner). Attempting to split the partner table into separate customer and supplier Hubs within the Raw Data Vault violates the core principles of Data Vault modeling.

+-----------------------------------------------------------------------------------+
|                                 RAW DATA VAULT                                    |
|                                                                                   |
|  +--------------------+         +--------------------+         +---------------+  |
|  |     stg_partner    |  ====>  |     h_partner      |  ====>  |   s_partner   |  |
|  | (Source Data Set)  |         |  (Raw Partner Hub) |         | (Raw Sat)     |  |
|  +--------------------+         +--------------------+         +---------------+  |
+-----------------------------------------------------------------------------------+

Loading data as-is ensures that the data platform retains a complete and indisputable representation of what existed in the operational environment at any given point in time. If a source system contains misclassified records, overlapping key ranges, or changing attribute definitions, those realities are captured faithfully in the Raw Data Vault. Applying filter conditions or inner joins during the raw loading process obscures the lineage and makes it impossible to reconcile the data warehouse against source audit logs.

Furthermore, operational source systems often change unexpectedly or suffer from misuse over time. In enterprise settings, legacy systems frequently lack formal fields for new operational requirements, leading staff to repurpose existing text or phone fields for notes and alternative identifiers. If raw ingestion pipelines depend on specific attribute values to route data into distinct Hubs, minor operational changes in the source system can silently corrupt or break downstream data pipelines.

The Business Vault Perspective: Applying Soft Rules for Semantic Separation

While the Raw Data Vault remains strictly source-aligned, the Business Vault provides the mechanism for restructuring data to match the enterprise semantic model. The Business Vault sits between the Raw Data Vault and the consumption layer (Information Marts), serving as the dedicated layer where soft business rules, complex transformations, and domain-specific logic are applied.

In scenarios where a single raw Hub contains multiple business concepts, architects can create virtualized Hubs in the Business Vault—such as bv_h_customer and bv_h_supplier—by applying filtering logic over the underlying Raw Data Vault constructs. These Business Vault Hubs isolate specific subsets of business keys using defined business rules without modifying or duplicating the underlying raw storage.

+-----------------------------------------------------------------------------------+
|                               BUSINESS VAULT                                      |
|                                                                                   |
|                         +--------------------------+                              |
|                   +---> |      bv_h_customer       |                              |
|                   |     | (Filtered Business Hub)  |                              |
|  +-------------+  |     +--------------------------+                              |
|  |  h_partner  | -+                                                               |
|  +-------------+  |     +--------------------------+                              |
|  |  s_partner  |  +---> |      bv_h_supplier       |                              |
|  +-------------+        | (Filtered Business Hub)  |                              |
|                         +--------------------------+                              |
+-----------------------------------------------------------------------------------+

The logic required to distinguish between entities like customers and suppliers can range from basic attribute filtering to multi-table relational analysis:

  • Attribute-Based Logic: Filtering records based on categorical values stored in the raw Satellite, such as a partner type flag, status code, or geographic region (e.g., WHERE status IN (1, 2, 3) AND country = 'DE').

  • Behavior-Based Logic: Identifying entities based on their participation in transactional relationships. For instance, a partner may be classified as a customer only if their key exists in a non-historized transaction Link representing sales orders.

  • Composite Logic: Combining descriptive attributes, organizational boundaries, and historical activity to dynamically categorize entities as business rules evolve.

Because business definitions change far more frequently than source system structures, encapsulating these rules in the Business Vault offers significant architectural flexibility. If executive leadership redefines what constitutes an active customer, data teams can update the Business Vault view logic without reloading historical data or altering the physical Raw Data Vault schema. For deeper insights into designing scalable architecture and managing business rules effectively, refer to our guide on Enterprise Data Warehouse Solutions.

Handling Satellites and Hash Keys in Subsetted Entities

A common technical question when splitting a single Hub into multiple Business Vault Hubs is whether the associated Satellites must also be physically split or re-hashed. In most implementations, physically splitting Satellites or generating new hash keys is unnecessary and discouraged.

When a Business Vault Hub is instantiated as a virtualized view over the raw Hub, it preserves the original hash keys derived from the source business keys. Because the hash key remains unchanged, downstream queries can join the virtualized Business Vault Hub directly back to the original Raw Data Vault Satellite.

+-----------------------------------------------------------------------------------+
|                          VIRTUALIZED JOIN MECHANISM                               |
|                                                                                   |
|  +--------------------+       INNER JOIN ON       +----------------------------+  |
|  |   bv_h_customer    |  ======================>  |         s_partner          |  |
|  | (Filtered Hash Keys|       HASH_KEY            | (Raw Satellite Deltas)     |  |
|  +--------------------+                           +----------------------------+  |
|                                                                                   |
|  Result: Automatically retrieves ONLY satellite deltas matching customer keys.   |
+-----------------------------------------------------------------------------------+

This join mechanism automatically filters the descriptive records. When joining bv_h_customer to s_partner on the shared hash key, the query naturally restricts the returned descriptive deltas to those belonging to customer keys. The remaining partner records belonging to suppliers or third parties are excluded by the join condition itself, eliminating the need to physically duplicate or split Satellite tables.

Retaining original hash keys yields several distinct operational benefits:

  • Storage Efficiency: Prevents duplicate physical storage of descriptive attributes across multiple derived Satellites.

  • Query Performance: Avoids dynamic hash re-computation inside views, allowing query engines to leverage existing hash indexes and distribution keys.

  • Simplified Maintenance: Keeps the lineage clear and direct between the raw ingestion layer and the virtualized business layer.

However, an exception to this rule occurs when business keys must be parsed or split into multi-part keys. For example, if a Vehicle Identification Number (VIN) contains embedded sub-keys representing the manufacturer, plant, and engine type, separating these components requires generating new hash keys. In such cases, materializing the resulting Business Vault Hub rather than calculating hashes on the fly inside a view ensures optimal query performance.

Architectural Boundaries for Upstream Filtering

When operational requirements dictate that certain records must be excluded from the analytical platform entirely—for example, due to legal restrictions, privacy regulations, or strict scoping—data architects must determine where that filtering should occur.

A fundamental rule of Data Vault design is that filtering logic must never be embedded within the staging or raw ingestion pipelines of the data warehouse. If data enters the staging area, it must flow into the Raw Data Vault to maintain complete auditability. If an organization requires that certain records (such as non-supplier partners) never be stored within the data warehouse, the filter must be implemented at the source export layer before the data is delivered to the ingestion boundary.

+-----------------------------------------------------------------------------------+
|                        FILTER LOCATION ARCHITECTURE                               |
|                                                                                   |
|  +------------------+     Source Export      +-----------------+                  |
|  | Operational Source|   Filter (If needed) | Staging / Raw   |                  |
|  | System Database  |  =================>  | Data Vault      |                  |
|  |                  |    [WHERE type='S']    | (Full Audit)    |                  |
|  +------------------+                        +-----------------+                  |
|                                                       |                           |
|                                                       v                           |
|                                              +-----------------+                  |
|                                              | Business Vault  |                  |
|                                              | Filter Logic    |                  |
|                                              +-----------------+                  |
+-----------------------------------------------------------------------------------+

The data platform team is responsible for auditing the data delivered to the platform, not for auditing the operational rules of source applications. If the source system delivers a dataset, the Raw Data Vault must store it completely. If business requirements specify that subsetting should occur after ingestion, that logic belongs exclusively in the Business Vault.

By maintaining a clear division between source-aligned raw ingestion and logic-driven business modeling, organizations build resilient data platforms capable of adapting to changing operational landscapes. To explore how your organization can implement these patterns or evaluate your current system architecture, learn more about our specialized Data Vault Consulting Services.

Watch the Video

In Data Vault Friday

Snapshot Replacement Date

What is the Purpose of the Replacement Snapshot Date Timestamp?

In modern enterprise data warehousing, managing storage consumption while preserving historical accuracy presents a constant balance for solution architects and data engineers. Within the Data Vault 2.0 methodology, Point-in-Time (PIT) tables play an essential role in optimizing performance when querying temporal data across Satellite structures. However, as the volume of daily snapshots scales over years of operations, maintaining high-frequency PIT records across millions of business entities becomes financially and technically unsustainable.

To address this challenge, organizations implement logarithmic retention strategies, gradually pruning older daily snapshot dates down to weekly, monthly, or annual intervals. While this approach dramatically reduces computational overhead and storage costs, it introduces a subtle architectural issue: what happens when historical transactions occur on dates that no longer exist in the snapshot dimension?

This challenge is resolved through a core architectural pattern in the Scalefree ecosystem: the replacement Snapshot Date Timestamp (SDTS). By utilizing a centralized control snapshot table, data teams can smoothly transition historical transactions to the next best logical state, preserving query integrity without driving up infrastructure costs.

The Role of Control Tables in Data Vault Architectures

To understand the function of the replacement SDTS, it is first necessary to examine the broader role of operational control tables within a Data Vault environment. At Scalefree, as well as across client production deployments, the data loading process relies heavily on two primary control tables to manage state, orchestration, and temporal alignment.

The first control table tracks the Load Date Timestamp (LDTS). This table serves as the primary operational log for incoming data streams from source systems. Each row represents a specific payload delivered to the staging area or data lake, uniquely identified by a combination of the source system identifier and the load timestamp. The table tracks the exact lifecycle of a batch as it progresses through each architectural layer—monitoring when execution starts and completes in Staging, Raw Data Vault, Business Vault, and the Information Marts. This operational visibility simplifies workflow orchestration, pipeline debugging, and batch processing verification.

The second control table is the Control Snapshot table. Unlike the load date control table—which manages incoming data absorption—the control snapshot table governs outgoing information delivery. It defines the temporal schedule for generating snapshot views across the Business Vault and Information Marts. Whether snapshots are generated every five minutes, hourly, or once daily at a standardized schedule (such as 07:00 UTC), every single execution records an entry in this control structure.

This synchronization is foundational. PIT tables and Bridge tables require a unified, standardized set of snapshot date timestamps to ensure that temporal queries across disparate entities yield aligned, deterministic results. By defining and managing these dates centrally, engineers prevent temporal skew when reporting against historical states.

In this article:


PIT Tables and the Storage Challenge of Daily Snapshots

Point-in-Time tables function as performance-indexing structures in Data Vault. They capture the relevant surrogate keys and load dates of surrounding Satellites for a given Hub or Link entity at specific snapshot intervals. By building a PIT table, analysts and reporting tools can query complex dimensional histories via fast inner joins rather than executing costly subqueries or window functions over raw Satellite structures.

However, the size of a PIT table expands exponentially based on two factors: the cardinality of the underlying entity and the frequency of the snapshot dates. Consider a business with 10 million active customer records in a Hub. If a daily snapshot process runs every night, the PIT table accumulates 10 million rows per day. Over a single year, that translates to more than 3.6 billion indexing rows for a single entity. For high-frequency architectures—such as near-real-time environments taking snapshots every five or fifteen minutes—the storage and processing requirements grow even faster.

Because historical reporting requirements change over time, business users rarely need daily granular fidelity for data that is several years old. While operations require precise daily snapshots for the past 90 days to track recent activity, executive reporting five years out typically requires only monthly or yearly snapshots.

To manage this footprint, data architects employ a logarithmic PIT strategy. Under this model, high-frequency snapshots are retained only for a recent window (e.g., daily snapshots for 90 days). Beyond that threshold, the density of retained snapshots is systematically reduced. Between 3 and 12 months, the system may only preserve three snapshots per month (beginning of the month, middle, and end/ultimo). For history extending beyond a year, retention may drop to annual snapshots (beginning and end of the year).

By pruning unneeded snapshot instances from the PIT tables, storage consumption drops significantly, allowing queries against large historical datasets to remain fast and cost-effective.

Decoupling Ingestion from Information Delivery

A common question among data engineering teams is how snapshot processes interact with active ingestion loads. For instance, if an automated pipeline is populating the Raw Data Vault while a snapshot is being generated, does the snapshot create data locks or run against incomplete transactions?

The core principle of Data Vault 2.0 is the complete decoupling of data ingestion from information delivery. The Load Date Timestamp handles incoming source payloads, capturing data into the Data Lake and Raw Data Vault as soon as it arrives. Whether data enters via real-time streaming interfaces or nightly batch windows, ingestion operates independently of the downstream delivery schedules.

The Snapshot Date Timestamp, by contrast, operates purely on the delivery side. Implemented within PIT tables, Bridge tables, and downstream Business Vault components, snapshots represent historical checkpoints constructed for user analysis and business intelligence reporting.

Because incoming updates are written directly to Raw Data Vault Satellites with their respective LDTS, snapshot processes running in the Business Vault simply evaluate the state of the data as it existed at the configured snapshot timestamp. There is no need to pause raw data ingestion during snapshot generation. Real-time streams and batch pipelines can write continuous loads into the Raw Data Vault without interfering with snapshot creation.

The Temporal Gap: Facts vs. Reduced Dimensions

While logarithmic PIT retention solves the storage problem, it introduces a structural challenge when joining transactional facts to historical dimension views.

Transaction facts represent real-world events—such as customer orders, invoices, or financial movements—that occur continuously across every day of the year. A transaction occurring on August 2nd contains a timestamp belonging precisely to August 2nd.

Dimensional views, however, are built directly on top of PIT tables. Consequently, the temporal granularity of a PIT table directly dictates the available temporal versions within the corresponding dimension.

This creates a join mismatch:

  1. A transaction occurs on August 2nd, requiring a dimensional context for August 2nd.

  2. The logarithmic retention policy has pruned the August 2nd snapshot from the PIT table, keeping only the snapshot for August 1st (the beginning of the month).

  3. Because August 2nd no longer exists in the PIT table, no dimension row is generated for that date.

  4. When the fact view attempts to join directly to the dimension view on August 2nd using a standard inner join, the join fails.

If an inner join drops the match, the transaction record is filtered out of the reporting view entirely, resulting in reported revenue losses or inaccurate metrics in business dashboards. Using a left outer join instead might preserve the fact row, but it leaves the dimensional attributes null and often leads to severe performance degradation when querying large data warehouses.

Resolving the Gap with the Replacement SDTS

To prevent data loss and performance issues without sacrificing the storage savings of logarithmic PITs, modern Data Vault models implement the replacement Snapshot Date Timestamp pattern within the control snapshot table.

When a snapshot date is pruned from a PIT table, it is deactivated in the Control Snapshot table rather than completely deleted. For every deactivated snapshot date, a data steward or automated control process assigns a replacement SDTS. This replacement timestamp points to the next logical, still-existing snapshot date in the system.

In determining which existing snapshot date to select as the replacement, the logic defaults to the closest preceding snapshot date. For example, if a transaction occurs on August 2nd, but the August 2nd snapshot has been pruned while the August 1st snapshot remains active, the control table sets the replacement SDTS for August 2nd to August 1st.

This choice follows standard temporal logic: from the standpoint of the transaction on August 2nd, the August 1st snapshot reflects the most recent known state of the business entity (such as a customer’s address or status). The system cannot assume attributes from a future snapshot (e.g., September 1st), as those changes had not yet occurred when the transaction took place.

+-------------------+-------------------+-------------------+
|  Actual Snapshot  |  Is Active In PIT |  Replacement SDTS |
|  Date Timestamp   |      Table?       |                   |
+-------------------+-------------------+-------------------+
|  2026-08-01 07:00 |       TRUE        |  2026-08-01 07:00 |
|  2026-08-02 07:00 |       FALSE       |  2026-08-01 07:00 |
|  2026-08-03 07:00 |       FALSE       |  2026-08-01 07:00 |
|  ...              |       ...         |  ...              |
|  2026-09-01 07:00 |       TRUE        |  2026-09-01 07:00 |
+-------------------+-------------------+-------------------+

When joining transaction facts to dimensional structures, the fact view uses the replacement SDTS lookup to reference the correct PIT record key.

  • Active Snapshots: For active dates (such as August 1st), the replacement SDTS matches the original SDTS, leaving the join unaltered.

  • Pruned Snapshots: For pruned dates (such as August 2nd), the replacement SDTS maps the join key back to the August 1st PIT record.

This mechanism allows the fact table to maintain an inner join to the dimension view without dropping transaction records. The original transaction date on the fact record remains untouched for chronological filtering and time-series aggregation, while the dimensional lookup routes cleanly to the latest valid historical attribute record.

Managing the “Fog of Data” and Business Trade-Offs

This reduction of historical attribute fidelity is known conceptually as the fog of data.

In video games, rendering engines use a “fog of war” or distance fog to blur background details that are far from the player, freeing up processing power for the immediate environment. Similarly, enterprise data architectures apply a fog of data to deep historical records. While businesses require exact, day-by-day dimensional tracking for recent events, historical context from years past rarely demands daily precision.

       HIGH DETAIL                        FOG OF DATA
(Exact Daily Attributes)        (Aggregated / Snapshot Approximations)
<------------------------> <------------------------------------------>
[ Today ] --- [ -90 Days ] | [ -1 Year ] --- [ -3 Years ] --- [ -10 Years ]
  Daily PIT Snapshots      |   Monthly / Annual Retained Snapshots

Implementing this pattern requires open alignment between data engineering teams and business stakeholders. When evaluating storage management, organizations generally choose between two models:

  1. Full Detail Retention: Retain every daily snapshot indefinitely. This keeps historical attribute joins accurate down to the single day, but leads to escalating cloud infrastructure and storage costs.

  2. Fog of Data Strategy: Retain daily snapshots for a rolling window (such as 90 or 180 days), transitioning older records to monthly or annual snapshots using replacement timestamps.

For the vast majority of enterprise analytical use cases, stakeholders accept the fog of data for older history because it preserves core transactional totals while keeping platform performance high and storage costs manageable.

Implementing Control Snapshots in Modern Tooling

Building and maintaining control tables manually can add operational friction to data engineering workflows. To automate this management, teams working with modern cloud data platforms often leverage automated frameworks.

For teams utilizing dbt (data build tool), Scalefree maintains datavault4dbt, an open-source package designed to automate Data Vault 2.0 structures in dbt. The package includes built-in hooks and macros designed to construct control tables, calculate snapshot schedules, and manage PIT tables using standardized Data Vault logic.

By automating PIT construction, snapshot schedules, and replacement timestamp assignments, engineering teams can implement scalable temporal modeling across complex data landscapes. To learn more about mastering these architectures, explore Data Vault training options to help your team implement scalable patterns effectively.

Watch the Video

In Data Vault Friday

Modelling Transactions as Data Vault Hubs

Modeling Transactions as Hubs in Data Vault Architecture

Data Vault modeling provides a robust, scalable, and adaptable foundation for enterprise data warehousing. However, database designers often grapple with how to handle highly normalized source systems containing parent-child transaction structures. A common scenario involves a core transaction header table—identifiable by a primary key alongside foreign key references to customers or sales representatives—connected to several dependent child or detail tables.

When encountering these structures, data engineers frequently consider modeling the primary transaction entity as a Hub and placing the child structures into dependent Satellite tables using child keys. While this approach appears intuitive on the surface, it introduces subtle architectural challenges regarding business key definition, parallel loading efficiency, and query performance in the downstream delivery layer. Understanding how to manage source normalization, pre-join staging views, and non-historized link patterns is essential to building an enterprise-grade solution.

For practitioners looking to master these structural patterns, participating in official Data Vault 2.1 Training & Certification provides deep operational clarity on core principles, hash key strategies, and automated ETL design.

In this article:


Structural Transformation vs. Data Content Modification

One of the core tenets of the Data Vault methodology centers on preserving source data fidelity. A foundational rule dictates that while engineers may alter the structure of incoming data to fit the Raw Data Vault schema, they must never modify or manipulate the actual data content during the ingestion process.

Source systems built on relational databases often arrive in third normal form (3NF). These environments utilize technical surrogate keys to enforce table relationships and maintain integrity. When ingesting this data into a staging environment, developers sometimes fear that combining or splitting incoming tables violates core modeling practices. However, staging-layer transformations that focus purely on structural realignment are entirely permissible.

Denormalizing or normalizing tables during staging simply alters the physical representation of the payload to streamline loading into the Raw Data Vault. As long as business values, system timestamps, and audit trailing mechanisms remain completely untampered with, structural preparation remains a standard best practice.

The Staging Layer and Pre-Join Views

To avoid multi-table joins during the Raw Data Vault loading phase, architects can leverage a dedicated view layer on top of the physical staging table. This approach allows pre-joining technical relationships before applying hash algorithms.

Consider a scenario where a core transaction table holds a technical primary key alongside a true business key, such as a basket ID or invoice number. The associated child tables only reference the technical primary key as a foreign key. By creating a view on top of the child staging table that joins back to the parent staging table on the technical surrogate key, engineers can pull the business key directly into the child view payload.

This pre-join technique exposes the parent business key across all child views. As a result, hash keys for both the Hubs and dependent Satellites can be calculated in parallel directly from the staging view layer, maintaining complete loading independence without sacrificing performance.

Defining Business Keys in Normalized Source Tables

A primary source of confusion when modeling transaction headers lies in distinguishing technical surrogate keys from true business keys. In many normalized transactional systems, primary keys are auto-incrementing integers generated purely for database indexing.

Relying on technical system IDs as primary business keys in a Hub can lead to integration bottlenecks. A business key should represent an operational identifier used by business users to track a real-world concept across multiple enterprise systems—such as an order number, basket ID, or policy code.

When a source system isolates the business key to the parent table and relies solely on technical foreign keys in the child entities, the pre-join view pattern resolves the dependency. By referencing the parent table in the staging view, the child entity inherits the functional business key. This allows developers to hash the true business key across all downstream Data Vault components, ensuring seamless enterprise integration even if source technical IDs differ across platforms.

Parallel Loading Mechanics in Raw Data Vault

Data Vault architecture achieves massive scalability by enabling fully parallelized loading pipelines. Because Hubs, Links, and Satellites rely on deterministic hash keys derived from business keys, each target table can be populated independently without waiting for parent surrogate key generation.

Using pre-join views in the staging layer ensures that every staging entity contains the necessary business keys and foreign business keys required to calculate target hash keys. Once the initial staging layer finishes refreshing, all downstream Raw Data Vault tables—including Hubs, Links, and Satellites—can be loaded simultaneously in a single parallel batch execution.

To streamline implementation details across cloud platforms, utilizing automated orchestration frameworks like datavault4dbt enables engineering teams to easily configure pre-join staging views and parallelized pipeline templates.

Modeling Transactions with Non-Historized Links

While treating a transaction header as a central Hub is a frequent starting point, experienced architects typically advocate for modeling transactions, events, and message streams using non-historized Links (also known as transactional Links).

Transactions inherently represent point-in-time events rather than static master data concepts. A classic customer master entity changes attributes over time, requiring historical tracking in a Satellite. In contrast, an individual transaction represents an immutable event involving a confluence of business concepts: a customer, a store, a sales representative, and a product.

+------------------+       +----------------------------+       +------------------+
|   Hub_Customer   | <---> |  Link_Transaction (NH)     | <---> |     Hub_Store    |
+------------------+       +----------------------------+       +------------------+
                                         |
                                         v
                           +----------------------------+
                           |   Sat_Transaction_Detail   |
                           +----------------------------+

A non-historized Link spans across all participating Hub business keys and incorporates an alternate key—such as the transaction or basket ID—directly within the Link structure. This design captures the exact granularity of the incoming transaction event without forcing artificial grain shifts.

Granularity and Performance Implications

Relying on a core Hub surrounded by multiple binary Links (two-way relationships connecting the transaction Hub to customer, staff, and location Hubs) creates severe query complexity. Reconstructing a simple transaction history requires joining multiple individual Links, which significantly degrades performance on large enterprise datasets.

Conversely, a non-historized Link establishes the fundamental grain of the transaction in a single structure. When building downstream delivery layers, such as star schemas or dimensional data marts:

  • The non-historized Link maps directly to the central fact entity.

  • Surrounding Hubs and Satellites map cleanly into dimension entities.

This direct mapping eliminates costly grain-shift operations where incoming transaction rows are aggregated and subsequently expanded, saving substantial compute resources on modern cloud data warehouses.

Attaching Child Tables to Non-Historized Structures

Child tables associated with a transaction core—such as line items, tax breakdowns, or audit details—can be modeled as Satellites attached directly to the non-historized Link. Depending on whether the underlying source data permits modifications or remains static, these details can be housed in standard multi-active Satellites, star Satellites, or non-historized Satellites.

By anchoring descriptive child details directly to the non-historized Link, the entire transactional context remains consolidated. Downstream queries can perform straight inner joins between the factual event Link and its descriptive Satellites, maximizing read performance for analytics and reporting.

To evaluate whether your enterprise data warehouse model aligns with best-practice Data Vault standards, consider requesting an expert data platform review to optimize load speeds and query efficiencies.

Summary Checklist for Transactional Modeling

When designing transactional structures within a Raw Data Vault, keep the following core design steps in mind:

  1. Identify True Business Keys: Distinguish between technical surrogate IDs and operational identifiers (e.g., basket IDs or invoice numbers).

  2. Build Pre-Join Views in Staging: Join child tables to parent entities within the staging view layer to expose the true business key across all child records.

  3. Adopt Non-Historized Links: Capture point-in-time events and multi-entity relationships using transactional Links containing the transaction alternate key.

  4. Attach Child Satellites to the Link: Connect child detail tables directly to the transactional Link as dependent or non-historized Satellites.

  5. Optimize for Delivery: Map non-historized Links straight to downstream fact tables to preserve native grain and ensure peak query performance.

By applying these patterns, data engineering teams can effectively normalize source data structures, enforce parallel loading streams, and deliver high-performance analytical environments.

Watch the Video

In Data Vault Friday

JEDI Testing Data Vault

The Jedi Test in Data Vault: Proving Source Data Completeness Without Compromise

When evaluating an enterprise Data Vault model, data architects often face a difficult question from executive leadership and project stakeholders: how do you know if the target architecture is actually the best model for the organization? When teams assemble massive Data Vault diagrams across multiple printed pages and tape them to office walls, they are looking for reassurance that their data warehouse architecture is sound. The immediate answer to whether a specific diagram represents the ultimate, perfect model is simply impossible to know without deeper context. Finding the ideal model depends on the physical nature of your incoming source feeds, performance latency targets, and specific analytical requirements from end users.

However, striving for a theoretically perfect model on day one is often the wrong objective. In modern analytics, having a valid model is significantly more important than having a theoretically perfect one. A valid model guarantees that your target schema accurately reflects and captures all incoming source data consistently over time without dropping records, corrupting values, or introducing phantom rows. Once a model is proven valid, data engineering teams can refactor, optimize, and evolve the structure iteratively without ever needing to re-load historical source files.

To systematically prove that an enterprise Data Vault model is valid, data practitioners rely on a foundational validation mechanism known as the Jedi Test.

In this article:


Understanding the Principles of the Jedi Test

The core philosophy of the Jedi Test relies on a simple mathematical concept: inverse operations. If your Data Vault loading process acts as a transformation function that decomposes raw operational feeds into granular architectural building blocks—such as business keys, relationships, and descriptive attributes—then an inverse process should be able to stitch those components back together to recreate the exact source delivery.

According to the rules of the Jedi Test, an enterprise must be able to reconstruct the source data exactly as it arrived at the staging boundary. Reconstructing the source dataset means reproducing not only every attribute value across all rows, but also matching the raw record counts precisely. If you can rejoin your Hubs, Links, and Satellites using the underlying metadata to output the identical snapshot or delta file delivered on a given date, you have successfully proven that your target model suffers from no structural data loss or artificial row duplication.

       [ Source Data Feed ]
                 │
                 ▼  (Loading Pipeline / Forward Function)
  ┌──────────────────────────────┐
  │   Hubs  |  Links  |  Sats    │  <-- Raw Data Vault
  └──────────────────────────────┘
                 │
                 ▼  (Jedi Test / Inverse Function)
       [ Reconstructed File ]

This capability transforms model validation from a subjective debate into an automated test suite. Because Data Vault ingestion relies heavily on metadata-driven patterns, the inverse query logic can be generated automatically. If the reconstructed query result matches the original staging snapshot line-by-line and count-by-count, your model passes the Jedi Test. You then possess a reliable foundation that supports downstream dimensional modeling, Business Vault extensions, and future schema refactoring. Practitioners seeking to master these underlying architectural patterns can deepen their knowledge through formal Data Vault training.

Managing Identical Snapshots and Unchanged Data Deliveries

A common challenge in real-world data pipelines occurs when incoming source snapshots contain no structural or value changes compared to the previous delivery. Consider an operational scenario where a source database dumps a full snapshot of a customer table every morning to an SFTP server or cloud storage bucket. On a quiet weekend, the Monday morning delivery contains records that are identical to Sunday’s file.

This scenario raises a vital question regarding auditable preservation: if an incoming file contains no actual data changes, how do you preserve enough operational metadata to satisfy the Jedi Test and reconstruct that specific snapshot?

Evaluating Operational Timestamps and Record Tracking Satellites

Data pipelines often receive operational metadata alongside source files, such as SFTP upload times, blob storage metadata, or file naming conventions containing timestamps (e.g., customer_20261013.csv). In Data Vault modeling, timestamps controlled directly by source systems or extraction routines represent business timelines rather than warehouse load timestamps.

If incoming feeds provide unique operational timestamps or changing file attributes on every delivery, storing those metadata fields within standard Satellites or utilizing them as load metadata differentiates each load cycle. However, when source systems offer no changing metadata, no file timestamps, and completely static payloads, storing every unchanged row cycle after cycle threatens to pollute the data warehouse with massive redundancy.

One potential solution often discussed is the implementation of a Record Tracking Satellite (RTS). An RTS records every business key present in every incoming feed regardless of whether descriptive attributes changed. While an RTS satisfies strict tracking requirements, maintaining one across large enterprise tables introduces substantial storage overhead and query friction because millions of business keys must be inserted on every load step.

Reconstructing Deliveries Without Unnecessary Storage Overhead

Fortunately, passing the Jedi Test for identical snapshots does not require heavy Record Tracking Satellites. Standard Data Vault patterns decouple the storage of descriptive changes from the orchestration logs that track ingestion events.

When a full snapshot arrives, the processing engine checks the business keys and descriptive payloads against the target Hubs and Satellites:

  • Hubs ignore existing business keys that have already been registered during prior loads.

  • Standard Satellites suppress identical rows using delta checks, writing no new records when payload attributes remain unchanged.

To satisfy the Jedi Test and reconstruct the exact snapshot for that day, the architecture relies on an operational control table that logs every batch attempt, coupled with specialized satellite logic for record presence.

Incoming Source Batch  ──► [ Batch Control Log ] (Logs metadata & execution timestamp)
                                   │
                                   ▼
Hubs / Standard Satellites ◄── [ Delta Check ] (Suppresses duplicate records)
                                   │
                                   ▼
Effectivity Satellites    ◄── [ Tracking ]   (Updates active/deleted status)

By querying the batch control log for the precise execution window and joining the active records from the Hubs and Satellites as of that timestamp, the engine seamlessly reproduces the snapshot without physically duplicating unchanged rows in storage.

The Critical Role of Effectivity Satellites and Deletions

While hub design and business key selection rarely break a model’s validity, improper handling of physical record deletions is a frequent point of failure in data warehouse projects. In a standard Raw Data Vault, Hubs and Satellites operate on an insert-only basis. Hubs never delete a business key, and standard Satellites only record new descriptive state changes.

If an operational source physically drops a customer record, a standard Satellite receives no new data for that key during the next snapshot. Without explicit tracking logic, a query attempting to reconstruct that snapshot days later would pull the last known descriptive state from the Satellite, erroneously including the dropped customer in the reconstructed file. This failure to reflect deleted records causes the model to fail the Jedi Test.

Day 1: Customer A arrives       ──► Hub registered  │ Sat payload logged (Active)
Day 2: Customer A unchanged     ──► No Hub insertion │ No Sat insertion   (Active)
Day 3: Customer A deleted source──► No Hub insertion │ No Sat insertion   (Deleted in Source)
       * Without Effectivity Satellite: Day 3 reconstruction still shows Customer A!
       * With Effectivity Satellite:    Day 3 flags Customer A as deleted (Jedi Test Passes).

Tracking Deletions with Effectivity Satellites

To solve this problem and maintain historical validity, architectures implement Effectivity Satellites (or driving key logic) to track record presence over time:

  1. Initial Insertion: When a business key first appears, the system inserts the key into the Hub, logs the payload in the standard Satellite, and creates an entry in the Effectivity Satellite setting an explicit start timestamp and a high end-date (e.g., 9999-12-31).

  2. Unchanged Loads: While the key continues to arrive unchanged, the Effectivity Satellite record remains active without requiring additional entries.

  3. Deletion Event: When a subsequent full snapshot omits a business key, the ingestion engine detects the missing key, updates the Effectivity Satellite, and writes a new delta record marking the key as deleted as of the current load timestamp.

  4. Reappearance: If the business key reappears in a future snapshot, the system writes a new delta record reactivating the key with an end-date of all times.

By combining the current active status from Effectivity Satellites with historical payload records from standard Satellites, the Jedi Test inverse query can perfectly reconstruct any historical snapshot—including deleted rows—at any point in time.

Operational Control Tables and Auditability Mechanics

To manage batch processing, orchestration, and inverse snapshot generation effectively, enterprise Data Vault architectures utilize dedicated operational control tables. These tables track ingestion history and manage Point-in-Time (PIT) construction.

┌────────────────────────────────────────────────────────────────────────┐
│                        BATCH CONTROL TABLE                             │
├──────────────┬─────────────────┬───────────────────┬───────────────────┤
│ Source System│ Batch Load Date │ Staging Status    │ Raw Vault Status  │
├──────────────┼─────────────────┼───────────────────┼───────────────────┤
│ ERP_CUSTOMERS│ 2026-10-13 08:00│ COMPLETED         │ COMPLETED         │
└──────────────┴─────────────────┴───────────────────┴───────────────────┤
                                   │
                                   ▼
┌────────────────────────────────────────────────────────────────────────┐
│                       SNAPSHOT CONTROL TABLE                           │
├─────────────────┬───────────────────┬──────────────────────────────────┤
│ Snapshot Date   │ Active Status     │ Business Description / Caption   │
├─────────────────┼───────────────────┼──────────────────────────────────┤
│ 2026-10-13 00:00│ ACTIVE            │ Daily Financial Closing Snapshot │
└─────────────────┴───────────────────┴──────────────────────────────────┘

Managing Batch and Snapshot Metadata

A robust deployment typically maintains two distinct metadata control structures:

  1. Batch Control Table: Tracks execution metadata for every incoming data pipeline execution. It records the unique combination of source system identifier and load timestamp, along with execution flags tracking whether staging and Raw Data Vault loading steps started and completed successfully.

  2. Snapshot Control Table: Manages logical timeline points used to build and refresh Point-in-Time (PIT) tables and dimensional models. This table defines which historical dates represent official reporting snapshots and allows data stewards to attach custom labels or manage snapshot retention policies.

Auditability Approaches: Updates vs. Insert-Only Logging

When implementing control tables, data engineering teams must determine how updates to batch and execution statuses are handled to meet compliance and auditability requirements.

  • In-Place Updates with Audit Triggers: The orchestration pipeline updates execution flags directly within the control table as processing steps complete. Database triggers record changes to an underlying audit shadow table to maintain an immutable log.

  • Insert-Only Event Logging: Every orchestration status change is written as a new immutable event row. Window functions (such as QUALIFY or ROW_NUMBER()) are applied over the execution ID to select the latest status during operational queries. This approach aligns naturally with append-only data lake storage and distributed engines like Apache Hive or Databricks.

  • Master Data Management (MDM) Integration: For human-managed snapshot schedules where business users designate specific historical markers, control tables can be managed within an MDM framework or loaded into the Data Vault as specialized reference tables or Non-Historized Links.

By pairing rigorous control tables with proper Effectivity Satellite logic, data teams establish a fully auditable architecture that passes the Jedi Test, guarantees complete historical fidelity, and provides a dependable foundation for long-term data engineering success. Learn more about these enterprise patterns and explore our comprehensive architectural services by visiting Scalefree.

Watch the Video

In Data Vault Friday

Standardizing Business Keys

Handling Prefixed Business Keys and Collision Codes in Data Vault

Integrating data from multiple operational systems into an enterprise data warehouse often presents serious challenges regarding data identity. When building an enterprise data model, architects frequently discover that distinct source systems utilize identical natural keys to represent entirely different real-world entities. Within a Data Vault architecture, resolving these identity overlaps correctly is essential for maintaining a historical, scalable, and audit-compliant Raw Data Vault.

A common scenario occurs when existing source systems utilize identical natural keys for different business entities. To prevent hash collisions and preserve identity in the account Hub, data engineers often implement a Business Key Collision Code (BKCC). The situation grows even more complex when a new, third source system enters the architecture, supplying data feeds where natural keys contain system prefixes embedded directly within the string. Resolving these incoming prefixed keys requires a careful evaluation of hard rules, string parsing, and Same As Link structures to ensure long-term architectural integrity.

In this article:


The Architecture Challenge: Multi-System Overlaps and Prefixed Key Feeds

To understand the core modeling dilemma, consider an environment with two existing legacy systems, System A and System B. Both systems populate the core account Hub within a Raw Data Vault architecture. Because both systems generate overlapping numeric identifiers—where account ID 42 in System A represents a completely different customer than account ID 42 in System B—the integration pipeline assigns a Business Key Collision Code during loading. System A receives a BKCC of ‘A’ and System B receives a BKCC of ‘B’. By combining the natural key with its collision code prior to hashing, the platform maintains two distinct entries in the account Hub, avoiding accidental key collisions.

+-----------------------------------------------------------------------------------+
|                            RAW DATA VAULT ACCOUNT HUB                             |
+------------------------------------+-----------------------+----------------------+
| Hub Hash Key                       | Business Key (Account) | BK Collision Code    |
+------------------------------------+-----------------------+----------------------+
| 0x4A8F... (Hash of '42' + 'A')     | 42                    | A                    |
| 0x9B2C... (Hash of '42' + 'B')     | 42                    | B                    |
+------------------------------------+-----------------------+----------------------+

The complexity increases when integrating a third source, System C. This new system acts as a consolidated or downstream feed that references accounts originally created across System A, System B, or native to System C itself. Instead of storing clean natural keys alongside a metadata indicator, System C prefixes its identifier strings directly, producing values such as “A_42”, “B_42”, or “C_101”.

When ingesting System C into the data warehouse, data architects face an immediate operational decision:

  • Option 1: Strip the prefixes in staging via a hard rule. Parse the prefix from the string to extract the original local natural key (“42”) and map the extracted prefix (“A”) directly into the existing BKCC attribute.

  • Option 2: Ingest the keys intact as raw business keys. Load the full string (“A_42”) directly into the Hub as a native System C business key with a BKCC of ‘C’, and resolve cross-system equivalencies using a Same As Link.

Evaluating these two options requires balancing data vault loading standards, transformation rules, and query performance. Teams seeking to establish enterprise-grade modeling standards can explore formalized guidance through dedicated Data Vault 2.1 Training & Certification programs.

Option 1: Parsing Key Prefixes in Staging Using Hard Rules

The first approach focuses on stripping the system prefix during the initial staging phase before hashing the business key for the Raw Data Vault. If “A_42” coming from System C represents the exact same physical business account as account “42” originating from System A, mapping them to the exact same Hub record eliminates redundant storage and avoids unnecessary downstream joins.

+-----------------------------------------------------------------------------------+
|                        OPTION 1: STRIPPING PREFIX IN STAGING                      |
+-----------------------------------------------------------------------------------+
| Incoming Raw Feed from System C:  "A_42"                                          |
| Applied Hard Rule (Delimiter Split on '_'):                                       |
|   -> Extracted Collision Code:    "A"                                             |
|   -> Extracted Natural Key:       "42"                                            |
|                                                                                   |
| Target Hub Record:                                                                |
|   -> Hash Key: Hash('42' + 'A')  ==> Matches existing System A Hub Entry          |
+-----------------------------------------------------------------------------------+

The Hard Rule Requirement in Raw Data Vault Ingestion

A primary tenet of Data Vault 2.0 is the strict separation between hard rules and soft rules. Hard rules are structural, deterministic transformations applied during data staging. They include data type casting, string trimming, uppercase formatting, and string splitting based on fixed file definitions or standard delimiters. Soft rules, by contrast, involve business logic, conditional evaluations, lookups, or data quality assertions, which must strictly reside within the Business Vault.

Stripping a prefix from an incoming business key qualifies as an acceptable hard rule only if it applies uniformly across all records without conditional branching logic.

Consider an incoming CSV or flat-file feed where key attributes use a compound delimiter format. Just as a pipeline splits a comma-separated record into discrete columns based on a positional rule, a staging process can split a single key column by an underscore delimiter into two distinct fields:

  1. Prefix Segment: Assigned directly as the Business Key Collision Code.

  2. Key Segment: Assigned directly as the local natural Business Key.

If this string-splitting logic executes universally across every incoming row without requiring IF...THEN...ELSE conditional checks, it remains a valid hard rule. Under this model, an incoming value of “A_42” yields a natural key of “42” and a BKCC of “A”. When passed through the MD5 or SHA-256 hashing algorithm, it generates the exact same hash key as the original record ingested from System A.

Risks and Pitfalls of Staging String Manipulations

While stripping prefixes keeps the account Hub clean and prevents hub duplication, relying on incoming string structures introduces operational risks that data engineering teams must carefully evaluate:

  • Inconsistent Delimiters: If System C occasionally transmits keys without an underscore (e.g., “A42” instead of “A_42”), simple character splitting fails or truncates valid keys.

  • Missing or Malformed Prefixes: If a record arrives as “42” without a prefix, assigning a default collision code requires conditional handling, pushing the logic into the realm of soft rules.

  • Collision Code Mismatches: If System C uses prefixes that do not match the BKCC conventions established for System A and System B (for instance, using “SYS_A_42” instead of “A_42”), mapping the prefix to the correct BKCC requires lookup tables or conditional translation logic, which violates Raw Data Vault ingestion principles.

When string structures vary across feeds, attempting to harmonize keys in staging corrupts the auditability of the Raw Data Vault. Organizations looking to audit their staging and ingestion architectures can leverage a structured data platform review to identify potential compliance bottlenecks before deployment.

Option 2: Ingesting Raw Keys and Resolving Equivalency via Same As Links

The second architectural option preserves the incoming business keys exactly as presented by the source system. Under this pattern, “A_42” from System C is ingested without modification into the account Hub with a BKCC set to ‘C’.

+-----------------------------------------------------------------------------------+
|                    OPTION 2: RAW INGESTION & SAME AS LINK                         |
+-----------------------------------------------------------------------------------+
| Account Hub:                                                                      |
|   1. Key: '42'   | BKCC: 'A'  | System: A  ==> Hash: 0x4A8F...                   |
|   2. Key: 'A_42' | BKCC: 'C'  | System: C  ==> Hash: 0x7C3E...                   |
|                                                                                   |
| Same As Link (SAL) in Business Vault:                                             |
|   -> Master Account Hash: 0x4A8F... (System A)                                    |
|   -> Secondary Hash:      0x7C3E... (System C)                                    |
+-----------------------------------------------------------------------------------+

Preserving Raw Source Fidelity

Loading the business key as “A_42” ensures that the Raw Data Vault reflects the exact source system representation without risking data corruption during staging. This approach aligns fully with the auditability requirements of the Data Vault methodology. Each source system maintains its own isolated footprint within the core Hub structure.

However, because “42” from System A and “A_42” from System C refer to the same real-world customer, storing them as separate Hub records creates duplicate business entities within the Raw Data Vault.

Resolving Identity Overlaps in the Business Vault

To unify duplicated business entities across disparate source systems, Data Vault utilizes the Same As Link (SAL) pattern. A Same As Link is a specialized link structure designed to record identity equivalencies between two or more Hub records.

When System C introduces keys that duplicate existing accounts in System A or System B, the integration layer populates a Same As Link inside the Business Vault. This link maps the hash key of the System C record (Hash('A_42' + 'C')) to the master hash key of the System A record (Hash('42' + 'A')).

Using Same As Links offers significant architectural advantages:

  • Zero Loss of Source Auditability: Raw keys remain pristine in the Raw Data Vault, allowing complete tracing back to System C source extract logs.

  • Support for Complex Business Logic: If determining whether “A_42” truly equals “42” requires conditional validation, reference table lookups, or fuzzy matching, that logic sits safely within the Business Vault as a soft rule.

  • Flexibility for Unlinked Entities: Accounts native to System C that do not exist in System A or System B simply remain unlinked or resolve to themselves without breaking the core hub structure.

Downstream Performance Considerations

The trade-off of Option 2 centers on storage volume and query complexity. Storing additional Hub records increases row counts within core tables and satellite structures. Furthermore, downstream reporting queries, point-in-time (PIT) tables, and bridge constructs must navigate the Same As Link to consolidate customer metrics.

In enterprise environments with large data volumes, querying Same As Links on the fly can introduce join overhead. Modern modern data platforms often mitigate this by constructing consolidated Same As Link helper tables or leveraging optimized bridge patterns in the Information Mart layer. Engineers working with automated transformation tools like datavault4dbt can streamline the generation of these Business Vault constructs through standardized macro patterns.

Decision Matrix: Evaluating Hard Rules vs. Same As Links

Choosing between stripping prefixes in staging (Option 1) or maintaining raw keys with Same As Links (Option 2) depends on data cleanliness, prefix consistency, and organizational risk tolerance.

Evaluation Criterion Option 1: Strip Prefixes (Hard Rule) Option 2: Raw Ingestion + Same As Link
Raw Data Vault Integrity High, provided logic is purely mechanical and unconditional. Maximum. Source keys are stored exactly as received.
Logic Complexity Minimal. Uses standard string-split operations in staging. Moderate to High. Requires Business Vault rules and SAL maintenance.
Tolerance for Source Bad Data Low. Malformed prefixes cause staging pipeline failures or silent data corruption. High. Unparseable or missing prefixes are loaded cleanly and handled in business logic.
Hub Row Count Lower. Reuses existing Hub entries across source systems. Higher. Generates new Hub records for every unique source key string.
Downstream Join Overhead Minimal. Entities share the same Hash Key directly in the Hub. Requires joining through Same As Links or Bridge tables in Information Marts.
Transformation Classification Hard Rule (Staging). Soft Rule (Business Vault).

Key Decision Drivers

Data architects evaluating these options should apply the following decision framework:

  1. Are the incoming prefixes 100% deterministic? If System C strictly enforces a standard format (e.g., [SYSTEM]_[ID]) across every single record without exception, stripping the prefix via a universal delimiter split is a valid hard rule.

  2. Does the extracted prefix match existing BKCC values exactly? If the prefix “A” maps directly to the collision code ‘A’ used by System A, Option 1 seamlessly integrates the keys into the existing Hub structure.

  3. Is there any risk of missing, malformed, or ambiguous prefixes? If incoming data occasionally lacks an underscore, contains multiple underscores, or uses inconsistent system codes, Option 1 carries unacceptable risk. In this scenario, Option 2 is mandatory.

In practice, data feeds from third-party systems or legacy platforms rarely maintain absolute formatting perfection over time. Consequently, many enterprise data warehouse teams default to Option 2 to safeguard the Raw Data Vault against unexpected source schema shifts, reserving complex key harmonization for the Business Vault. To dive deeper into core hash key design and collision code strategies, review our technical guide on hash keys in the Data Vault.

Strategic Recommendations for Data Vault Implementations

When managing multi-system integration challenges, following consistent architectural guidelines prevents long-term technical debt across your enterprise data platform:

  • Enforce Unconditional Hard Rules: Never introduce conditional CASE statements, lookup joins, or source-specific branching logic when staging business keys for the Raw Data Vault. If a transformation cannot be written as a universal string split, it belongs in the Business Vault.

  • Isolate Business Logic in the Business Vault: Use Same As Links and Bridge tables to manage complex entity resolution. Keeping business logic separate from Raw Data Vault loading routines ensures that changes in business rules do not require re-ingesting historical source data.

  • Document Collision Code Conventions: Maintain clear enterprise documentation defining valid Business Key Collision Codes across all source systems. Standardizing collision code definitions early prevents overlapping key issues when integrating new platforms.

  • Plan for Downstream Performance: When using Same As Links extensively, implement point-in-time tables and standardized Information Mart bridges to optimize reporting performance for analytics users.

Navigating complex business key collisions and multi-system integration requires a solid foundation in Data Vault modeling standards. Whether you are modernizing a legacy warehouse, deploying on modern cloud data warehouses, or training your team on enterprise best practices, tailored consulting support can accelerate your delivery timeline. Explore how our team supports enterprise data teams by reviewing our comprehensive Data Vault consulting services or reach out directly through our contact page to speak with an architect.

Watch the Video

In Data Vault Friday

Deriving SCD Type 2 Dimensions from Data Vault

Deriving Slowly Changing Dimensions Type 2 from Data Vault Models

A frequent architectural challenge in enterprise data warehousing is determining how to derive a Slowly Changing Dimension (SCD) Type 2 from a complex Raw Data Vault design. When building analytical presentation layers, business analysts often require a single, unified dimensional record that combines descriptive attributes across parent entities, child entities, hierarchical structures, and cross-entity relationships. Navigating these multi-entity joins while maintaining complete historical accuracy requires a structured, repeatable modeling approach.

In complex enterprise environments, managing multi-table joins across temporal boundaries can quickly become overwhelming without standardized modeling rules. Organizations looking to establish scalable data architectures often enroll their engineering teams in comprehensive Data Vault certification programs to master advanced dimensional derivation and Point-in-Time (PIT) construction patterns.

By understanding the role of tracking Satellites, leveraging Point-in-Time structures, and utilizing inner-join alignment patterns, data teams can virtualize robust SCD Type 2 dimensions directly on top of their Data Vault storage layer without sacrificing query performance.

In this article:


Structural Auditability: The Role of Tracking Satellites

Before constructing an SCD Type 2 view across multiple entities, data architects must ensure that the underlying Raw Data Vault layer contains complete tracking structures. In a typical scenario involving two core Hubs—Hub A and Hub B—connected by a Link, alongside a Hierarchical Link on Hub A, changes occur not only in descriptive attribute Satellites but also within the relationships themselves.

Relationships between business entities evolve over time. For example, when a corporate asset is reassigned from one department to another, the Link record captures the association, but the core Link structure cannot indicate whether a specific assignment remains active. To track operational state changes, deletion events, and relationship lifecycles, data engineers deploy Status Tracking (ST) Satellites—frequently referred to as Key Satellites or Foreign Key (FT) Satellites—on both Links and Hierarchical Links.

A common oversight when designing Data Vault models is neglecting to place tracking Satellites directly on the Hubs. Operational systems frequently perform physical deletions or discontinue delivering specific business keys. To track when an entity key is removed or deactivated in the source system without losing historical auditability, a Key Satellite must be attached directly to the Hub.

Retaining deleted entities within derived dimensions is crucial for reporting consistency. If a customer record is deleted from an operational source system, removing that customer from a Type 2 dimension breaks downstream Fact table references, altering historical revenue aggregations. Instead of physically removing deleted entities, the derived dimension retains the business key and exposes an explicit deletion flag, allowing end users to filter records dynamically based on their specific reporting requirements.

Aligning Historical Timelines with Point-in-Time (PIT) Tables

Deriving a unified SCD Type 2 dimension across multiple entities presents a significant temporal challenge: each attached Satellite records attribute changes on its own independent timeline. Joining a Satellite on Hub A directly to a Satellite on Hub B using standard date range comparisons results in complex, non-equi joins that degrade query engine performance on large datasets.

To resolve these mismatched timelines and enable high-speed processing, architects implement Point-in-Time (PIT) tables across the Data Vault structures. PIT tables act as temporal index structures, creating regular snapshot dates across the lifecycles of parent Hubs, Links, and Hierarchical Links.

A PIT table maps each snapshot date to the specific load date timestamps active in each attached Satellite at that exact moment. By placing PIT tables on Hub A, Hub B, the connecting Link, and the Hierarchical Link, all temporal records across the entire graph are synchronized onto a common snapshot grid. This enables the query engine to execute lightning-fast inner joins across multi-entity paths using simple equality conditions on hash keys in the Data Vault and snapshot timestamps.

Step-by-Step Join Strategy for Multi-Entity SCD Type 2 Derivation

When building an SCD Type 2 dimension for an entity represented by Hub A—including attributes from its own Satellites, related attributes from Hub B, hierarchy details from a parent Hub A, and the parent’s related Hub B attributes—the derivation follow a structured join order.

Establishing Base Granularity and Entity A Attributes

The derivation begins by selecting from the primary PIT table associated with Hub A (PIT_Hub_A). This PIT table establishes the base Type 2 snapshot granularity for the dimension, generating one record per business key per snapshot interval.

  1. Entity Identification: Perform an inner join from PIT_Hub_A to Hub_A using the Hub hash key to retrieve the physical business key.

  2. Entity Descriptive Attributes: Perform inner joins from PIT_Hub_A to the descriptive Satellites on Hub A using the business key pointer and load date timestamp pointers stored in the PIT table.

  3. Operational State: Join the Key Satellite on Hub A via PIT_Hub_A to incorporate active/deleted status flags into the dimension output.

Traversing to Related Entity B Attributes

To incorporate attributes from the related entity Hub B active at the exact time of the snapshot, the query traverses the intermediate Link using PIT-to-PIT alignment.

  1. Link Alignment: Join PIT_Hub_A to the Link connecting A and B using the entity A hash key.

  2. Link PIT Integration: Perform an inner join from the Link to the corresponding Link PIT table (PIT_Link_A_B) matching both the Link hash key and the exact snapshot date from PIT_Hub_A.

  3. Link State Verification: Join the Link’s Status Tracking Satellite via PIT_Link_A_B to verify that the relationship was active at the snapshot timestamp.

  4. Entity B PIT Integration: Extract the entity B hash key from the active Link record and join to PIT_Hub_B, matching the entity B hash key and aligning on the original snapshot date.

  5. Entity B Attribute Retrieval: Join PIT_Hub_B to Hub B’s descriptive Satellites using the pointers contained in PIT_Hub_B to extract the relevant attributes.

Incorporating Parent Hierarchy Attributes

To add attributes belonging to the parent of Hub A within an organizational or operational hierarchy, the query utilizes the Hierarchical Link structure.

  1. Hierarchy Link Alignment: Join the entity A hash key to the Hierarchical Link on Hub A as a child key reference.

  2. Hierarchy PIT Integration: Inner join to the Hierarchical Link PIT table (PIT_Hierarchical_Link) using the Hierarchical Link hash key and the current snapshot date.

  3. Hierarchy State Check: Verify relationship status via the Hierarchical Link’s Status Tracking Satellite to ensure the parent-child relationship was valid during that snapshot.

  4. Parent Entity Retrieval: Extract the parent hash key from the active Hierarchical Link and join to PIT_Hub_A_Parent using the parent hash key and snapshot date.

  5. Parent Attribute Extraction: Perform inner joins from PIT_Hub_A_Parent into the descriptive Satellites on Hub A to pull parent-level descriptions.

Extending to Related Entity B Attributes for the Parent

Finally, if the business requirement demands attributes from the entity B record linked to the parent of entity A, the traversal pattern repeats for the parent node.

The parent hash key retrieved from the hierarchy path is joined to the A-to-B Link, aligned with PIT_Link_A_B using the common snapshot date, validated through the Link’s tracking Satellite, mapped to PIT_Hub_B_Parent, and joined to Hub B’s descriptive Satellites. Because every step relies strictly on PIT-backed equality joins on pre-indexed surrogate keys and snapshot dates, the query optimizer processes the complex multi-table graph with high efficiency.

Snapshot Granularity vs. Pure Attribute Deltas

A common point of discussion among data warehousing practitioners is whether a snapshot-based dimension derived from PIT tables represents a true Ralph Kimball-style SCD Type 2 dimension. By pure definition, a traditional SCD Type 2 dimension inserts a new record version only when an underlying attribute value physically changes. In contrast, a PIT-backed dimension view generates a row for every snapshot date configured in the PIT table, regardless of whether attribute values changed between intervals.

In practice, modern BI and reporting tools such as Power BI, Tableau, and Qlik process snapshot-based Type 2 dimensions without issue. The analytical user queries the dimension at a specific point in time, and the underlying reporting engine aggregates fact measures seamlessly against the dimensional attributes presented for that date.

If an enterprise consumption tool specifically requires absolute delta-only versioning (such as legacy SSAS multidimensional cubes), data teams can apply an analytical window function—such as LAG() or LEAD()—over the virtualized dimension view. This window function compares consecutive snapshot records and suppresses rows where no descriptive attribute values changed. Applying this delta-filtering logic at the Information Mart presentation layer preserves the performance advantages of PIT tables within the Data Vault 2.0 core repository while satisfying downstream tool constraints.

Optimizing Analytical Query Performance

Deriving multi-entity dimensions using PIT tables provides a resilient, highly scalable foundation for enterprise analytics. By using PIT tables as temporal bridges across Hubs, Links, and Satellites, data teams eliminate costly range joins and enable parallelized query execution across large datasets.

When designing complex Business Vault views, referencing core resources like The Data Vault Handbook can help architects select the appropriate bridge and PIT patterns for their specific workloads. Conducting periodic data platform review sessions ensures that PIT snapshot frequencies and indexing strategies remain aligned with evolving business SLA requirements, delivering optimal performance across the enterprise reporting ecosystem.

Watch the Video

In Data Vault Friday

Deriving Date Dimension From Data Vault

Date Dimension in Data Vault

Designing a high-performance date dimension is a foundational requirement for any enterprise analytics solution. In traditional data warehousing, generating and maintaining a physical date dimension often involved cumbersome Master Data Management (MDM) processes, manual spreadsheet uploads, or heavy operational ETL jobs.

Within a modern Data Vault 2.0 framework, best practices have evolved significantly. Rather than relying on external master data maintenance processes, organizations can streamline date dimension management by combining automated SQL scripting in the Raw Data Vault with dynamic virtualization in the Information Mart layer.

Understanding how to construct this architecture allows data engineering teams to minimize physical storage, eliminate manual maintenance, and provide custom date presentations tailored to distinct business domains. Organizations seeking to modernize their enterprise data platforms can explore comprehensive Data Vault certification to align their team on standard dimensional modeling patterns.

In this article:


The Legacy Challenge of Pre-Populated Date Tables

In early data warehousing methodologies, setting up a date dimension typically required generating a static set of dates covering several decades and loading it into a physical database table via an MDM interface or flat file ingestion process. While these tables required minimal updates once populated, the initial setup and maintenance cycle introduced unnecessary pipeline complexity.

Deploying static tables across multiple staging environments created operational friction:

  • Manual file uploads were required when deploying to lower development or testing environments.

  • Extending calendar ranges (for example, adding future dates for financial forecasting) required manual database administrator intervention.

  • Tailoring calendar structures for specific business units often led to physical table duplication across the data warehouse.

To resolve these maintenance bottlenecks, contemporary Data Vault 2.0 architectures decouple the core reference storage of date records from the presentation layer delivered to end-user Business Intelligence platforms.

Raw Data Vault Architecture: The Reference Date Pattern

Instead of treating the date dimension as an externally managed source table, modern Data Vault architectures implement a central Reference Date table (Ref_Date) directly inside the Raw Data Vault repository. This table is populated programmatically using a SQL generation script, completely removing the need for external flat file ingestion.

The primary reference structure stores calendar dates at a grain of one row per calendar day. The primary key of the Ref_Date table is a standardized integer surrogate key formatted as YYYYMMDD (for example, 20261013).

Core Reference Date Attributes

The base Ref_Date table contains flat, fully denormalized attributes describing standard calendar metrics. Common fields include:

  • Calendar Date: Standard ISO date string (2026-10-13).

  • Day Attributes: Day of the week, day name, day of the month, and day of the year.

  • Week Attributes: ISO week number, week start date, and week end date.

  • Month Attributes: Month number, month name, short month name, and first/last day of the month flags.

  • Quarter Attributes: Quarter number (Q1–Q4) and corresponding start/end dates.

  • Year Attributes: Calendar year integer.

Because standard calendar definitions rarely change, storing these denormalized descriptions directly in the primary reference table provides a clean foundation for downstream analytical reporting.

Handling Specialized Calendars via Reference Satellites

While standard calendar dates remain static, many enterprise domains require alternative temporal perspectives, such as corporate fiscal calendars, manufacturing shop-floor calendars, or retail 4-4-5 trading schedules. For instance, a company’s fiscal year might commence on April 1st rather than January 1st, completely shifting fiscal quarter and month assignments relative to the standard calendar.

To accommodate distinct business calendars without altering the base Ref_Date structure, data architects deploy a Reference Satellite attached to the central reference table. A Fiscal Calendar Reference Satellite (Ref_Sat_Fiscal_Calendar) captures alternative temporal logic keyed directly to the standard date integer code.

This Reference Satellite introduces domain-specific fields such as:

  • Fiscal Day of Year: Sequential day count within the active corporate fiscal year.

  • Fiscal Week: Assigned fiscal trading week identifier.

  • Fiscal Month: Fiscal period number (1 through 12).

  • Fiscal Quarter: Designated corporate reporting quarter (e.g., FQ1, FQ2).

  • Fiscal Year: Corporate financial reporting year.

By isolating fiscal logic inside a dedicated Reference Satellite, the data platform maintains absolute separation of concerns. If corporate fiscal definitions are updated or a new business unit with a different financial schedule is acquired, new versions or additional Reference Satellites can be added without altering existing base calendar dependencies.

Information Mart Virtualization and Custom Presentation

Once the Ref_Date table and associated Reference Satellites are established in the Raw Data Vault, the Information Mart layer exposes these records to downstream analytical tools like Power BI, Tableau, or custom reporting dashboards.

Rather than physically copying and materializing date dimension tables into separate domain-specific data marts, teams create light virtual views on top of the Data Vault reference structures.

Customizing Dimensions per Information Mart

Different business domains require different date dimension presentation formats. A supply chain dashboard may require highly granular attributes like ISO week numbers, shipping day flags, and manufacturing period codes. Conversely, a high-level executive financial report may only require year, fiscal quarter, and month attributes.

Virtualizing the Information Mart layer enables complete customization without data duplication:

  • Attribute Filtering: Views select only the specific columns required for a target domain, reducing semantic noise for end users.

  • Column Renaming: Technical column names stored in the Data Vault layer are aliased to business-friendly labels matching corporate glossaries.

  • Language Localisation: Language-specific month or day names can be selected conditionally within the view logic based on target user requirements.

This approach ensures that all enterprise Information Marts derive their temporal definitions from a single centralized physical source (Ref_Date), satisfying single-source-of-truth requirements while allowing individual domain models total freedom over their semantic presentation.

Scripted Population vs. Manual Maintenance

Populating the Ref_Date table using an automated SQL script offers distinct operational advantages over traditional manual ETL pipelines:

  • Environment Consistency: Executing a standardized SQL script creates identical reference date ranges instantly across Development, Test, and Production environments.

  • Extensibility: Extending the calendar range—such as generating dates ten years into the future for financial modeling—requires simply modifying a loop parameter in the SQL generator script and re-executing the procedure.

  • Zero ETL Maintenance: Because reference date generation runs autonomously, data engineering teams eliminate ongoing pipeline maintenance, job monitoring overhead, and file dependency risks.

Teams building scalable cloud architectures on modern data lakes can leverage frameworks such as datavault4dbt to automate the deployment of reference structures and downstream views directly inside their transformation workflows.

Strategic Benefits for Modern Enterprise Data Platforms

Transitioning from pre-populated MDM tables to a virtualized reference date pattern aligns fully with the core tenets of agility, auditability, and scalability defined by Data Vault 2.0. Storing raw calendar data once inside a centralized Ref_Date structure while providing flexible presentation views across information domains drastically cuts maintenance costs.

Organizations conducting a comprehensive data platform review often identify legacy dimension tables as a prime candidate for refactoring. Implementing script-generated reference date tables alongside virtualized Information Mart dimensions accelerates deployment times, streamlines governance, and provides a future-proof foundation for enterprise-wide analytics.

Watch the Video

In Data Vault

Architectural Limitations of PostgreSQL for Enterprise Data Vault Workloads

Architectural Limitations of PostgreSQL for Enterprise Data Vault Workloads

PostgreSQL stands as the industry standard for open-source transactional (OLTP) database engines. Its reliability, strict ACID compliance, and robust extension ecosystem make it a primary choice for operational workloads. It is the foundation that other open-source alternatives build upon.

PostgreSQL derivatives and extensions modify this core engine to handle heavy analytical (OLAP) and Data Vault workloads while preserving the existing PostgreSQL expertise.

Given this ubiquity, engineering organizations often aim to use vanilla PostgreSQL as their initial data warehousing platform. Vanilla” PostgreSQL refers to the core, single-node version of the database system, which serves as the blueprint for its derivatives and extensions. It is published as an open source project at https://www.postgresql.org/. This approach is commonly paired with Data Vault to achieve agile, historized data modeling.

While theoretically sound, this architectural combination regularly encounters a severe performance threshold as data volumes scale into the mid-to-high gigabyte and terabyte range.

I have created multiple applications that relied on PostgreSQL for operational workloads during my career as a software engineer (before my Data Vault career). With this brief history in mind, it is no surprise that Scalefree relied on PostgreSQL to some extent: we built our first internal data warehouse on PostgreSQL, even knowing its limitations. It was good to start with, when the internal data volume at Scalefree was low, and query complexity was limited. We chose vanilla PostgreSQL because of its derivatives; once we experience the database’s limitations, we would migrate to one of its commercial derivatives.

In this article:



Strategic Context: Digital Sovereignty, Open Source, and Transatlantic Tech

Before diving into the technical mechanics, it is essential to address why open-source infrastructure matters so deeply to the future of enterprise architecture.

At Scalefree, our commitment to open-source software is foundational. We actively maintain and contribute to the community, which you can explore directly via the Scalefree GitHub repository. A prime example of this dedication is our open-source project, datavault4dbt, which brings robust Data Vault generation capabilities to standard PostgreSQL as well as its derivatives and cloud warehouse counterparts like Amazon Redshift.

Beyond pure engineering efficiency, a broader geopolitical driver is at play: digital sovereignty. As a Fulbright Scholar deeply invested in transatlantic affairs, I closely observe the shifting dynamics between the United States and Europe. True strategic alignment requires balance. The US does not benefit from a technologically dependent Europe; rather, the long-term stability of the transatlantic alliance relies on the US having a strong, digitally independent, and technologically sovereign ally in Europe. This calls for a pragmatic approach to digital sovereignty: use it when available and feasible, build capabilities, and create alternatives without going into panic mode on political trends that change every four or eight years.

The Open Source Sovereignty Paradox

A common counterargument often arises here: If many of these major open-source projects are driven, maintained, and funded by US developers and tech giants, how do they actually provide a sovereign alternative to US commercial technology?

The answer lies in the fundamental nature of open-source licensing and governance, which fundamentally alters the power dynamic:

  1. Elimination of Vendor Lock-In and Extraterritorial Jurisdiction: Proprietary cloud solutions are bound by commercial licenses, corporate terms of service, and the host nation’s domestic laws (such as the US Cloud Act). If a geopolitical or regulatory shift occurs, access can be restricted or altered. Open-source code, once published under licenses such as Apache 2.0 or MIT, is in the public domain. It cannot be revoked, repatriated, or shut off by a foreign corporate or state actor. Those licenses are our preferred choice for our open source projects at Scalefree.
  2. The Right to Fork and Host Locally: Open source grants European enterprises the ultimate sovereign right: the ability to fork the code, host it completely on local infrastructure, and maintain it independently. Even if the primary contributor base remains in Silicon Valley, European engineers have full visibility into the source code, allowing them to audit for security, eliminate telemetry, and adapt it to localized regulatory compliance (like GDPR) without foreign oversight.
  3. Decoupling Innovation from Capital Concentration: Open source democratizes access to state-of-the-art software architecture. It allows a European ecosystem to build sovereign, high-performance platforms without being forced to route massive capital into proprietary foreign hyper-scaler ecosystems.

Building enterprise data architecture on open, vendor-neutral infrastructure is a critical pillar of that sovereignty. However, to achieve true independence, our open-source tools must be architected to handle enterprise-scale workloads.

The following analysis details the structural reasons why vanilla PostgreSQL is fundamentally ill-suited for large-scale analytical (OLAP) processing, with a specific focus on how Data Vault methodology exacerbates these limitations and on open-source alternatives to remedy them.

1. Storage Paradigm Conflict: Row-Oriented vs. Columnar

The primary bottleneck stems from PostgreSQL’s native storage architecture. As a traditional relational database, it utilizes a row-oriented storage engine (heap tables), where complete records are written sequentially to disk blocks.

OLTP Optimization: This architecture is optimal for point-write and point-read operations, such as fetching a single entity profile via a unique identifier.

OLAP Inefficiency: Analytical queries typically perform aggregations over hundreds of millions of rows while restricting the projection to a minimal subset of attributes (e.g., computing a sum over a single numeric column).

Because PostgreSQL reads data row-by-row, it must scan every byte of every unrequested column within the target disk blocks into memory. This introduces immense, non-value-add disk I/O bottlenecks that degrade query performance at scale.

2. Structural Degradation Under Data Vault

Data Vault provides exceptional flexibility and auditability by decomposing business domains into discrete structural components:

  • Hubs: Unique business keys.
  • Links: Relationships and associations between keys.
  • Satellites: Contextual attributes, descriptive states, and historical tracking.

While highly effective for parallelized ingestion and decoupling business logic, this normalization strategy creates severe friction for a row-oriented relational engine.

The Multi-Way Join Complexity

To reconstruct a coherent business entity for downstream consuming layers (such as Business Intelligence tools), data engineers must reverse this decomposition. A single analytical query often requires a 10-to-20-way join across multiple large Hubs, Links, and versioned Satellites.

As dataset sizes expand, this structural complexity impacts the engine in two ways:

  1. Optimizer Limitations: The PostgreSQL query planner struggles to generate precise cardinality estimations across deeply nested join trees, frequently reverting to inefficient join strategies (e.g., nested loops instead of parallel hash joins).
  2. Memory Subsystem Pressure: Executing these multi-way joins requires significant memory allocation (work_mem). When a query’s requirements exceed physical memory allocations, PostgreSQL spills intermediate operations to disk, decelerating execution speeds by orders of magnitude.

The Lack of Inner Join Elimination (Join Reduction)

A particularly acute limitation of the PostgreSQL query optimizer in Data Vault architectures is its inability to perform Inner Join Elimination (also known as Join Reduction).

In complex Data Vault environments, users or Business Intelligence tools frequently query comprehensive, multi-table views or abstract layers that automatically stitch Hubs, Links, and Satellites together. If an end-user runs a report that only requests attributes from a single Satellite and its parent Hub, the remaining 10 tables in that view are technically redundant to the final output.

Modern OLAP query optimizers recognize declarative primary/foreign key constraints and automatically eliminate these unnecessary tables from the execution plan. The PostgreSQL query optimizer cannot eliminate redundant Inner Joins. Even if no columns are selected from a joined table, and even if a valid foreign key guarantees a 1:1 match, Postgres will stubbornly execute every single inner join defined in the query or view. This results in massive, redundant table scans and CPU cycles spent processing joins that have zero impact on the final result set.

Accumulation of Dead Tuples (Bloat)

Data Vault architecture is inherently append-only; changes in source systems trigger the insertion of a new row within the corresponding Satellite to preserve history. This high-frequency append behavior, coupled with PostgreSQL’s Multi-Version Concurrency Control (MVCC), accelerates the creation of dead tuples. Under continuous analytical workloads, the native AUTOVACUUM process frequently falls behind, resulting in table and index bloat that degrades scan performance.

3. The Window Function Bottleneck (And the Fallacy of Recent Releases)

Beyond complex joins, Data Vault workloads rely extensively on Window Functions (LEAD, LAG, RANK, ROW_NUMBER) to determine active states, calculate durations between historical intervals, or isolate the latest record within a Satellite stream.

In vanilla PostgreSQL, window functions are computationally expensive when executed against vast datasets. The engine must sort the partition keys in memory or spill them to temporary disk files to establish the window bounds.

The Limits of Engine Optimizations in Recent Versions

Proponents of the ecosystem often point to performance enhancements introduced in recent iterations—specifically PostgreSQL 17 (which introduced advanced streaming I/O optimizations) and PostgreSQL 18 (which implemented an asynchronous I/O (AIO) framework for sequential and bitmap heap scans).

While these enhancements represent significant milestones for core engine efficiency, they do not resolve the analytical window function bottleneck:

  • I/O vs. Execution Model: These upgrades optimize the database’s ability to read blocks from storage more rapidly. However, the underlying execution model for window functions remains bound to single-node, row-by-row compute processing.
  • Diminishing Returns at Scale: Increasing disk I/O throughput offers negligible relief when a LAG() or ROW_NUMBER() function across hundreds of millions of rows forces a massive, single-threaded disk-sort operation. The bottleneck is merely shifted from storage I/O to compute saturation.

4. Single-Node Architectural Constraints

Enterprise analytical data platforms utilize Massively Parallel Processing (MPP) architectures. They shard and distribute datasets across a cluster of compute nodes, allowing complex joins and window calculations to execute concurrently.

Vanilla PostgreSQL is a single-node database system. While it supports intra-query parallelism across multiple CPU cores, it remains bound by the hardware limits of a single virtual or physical machine. Scaling a vanilla PostgreSQL instance to meet growing OLAP demands requires vertical hardware scaling (scale-up), which scales linearly in cost but delivers diminishing returns in performance.

Open-Source Alternatives and PostgreSQL Derivatives

If your organization is committed to maintaining a sovereign, open-source stack while preserving existing PostgreSQL expertise, you do not need to migrate to proprietary cloud data warehouses. Several open-source derivatives and extensions modify the Postgres engine specifically to handle heavy OLAP and Data Vault workloads:

1. Apache Cloudberry (Incubating, Open-Source MPP)

For enterprise Data Vaults, Apache Cloudberry stands out as an incredibly powerful evolutionary step. It is an open-source Massively Parallel Processing (MPP) database derived from the Greenplum 7 codebase but aggressively modernized.

Why Apache Cloudberry outclasses Greenplum for Data Vaults:

  • Modern Upstream Kernel: Apache Cloudberry remedies legacy tracking issues by building on a much newer PostgreSQL 14 kernel (whereas older Greenplum installations remain anchored to legacy Postgres backends).
  • Superior Analytical Feature Set: Cloudberry supports a massive list of critical performance and optimization features, including incremental sort for window functions, run-time filters for joins, aggregation pushdowns, query pipelining, and advanced BRIN indexing.
  • True Community-Driven Open Source: Following Broadcom’s acquisition of Greenplum’s parent company, the source for Greenplum was closed. As a project under the Apache Software Foundation (ASF), Cloudberry offers vendor-neutral governance under an Apache 2.0 license, completely free from mandatory vendor lock-in.

2. Citus (Open-Source Extension)

How it helps: Citus transforms PostgreSQL into an MPP database by distributing tables and queries across a cluster of multiple nodes.

Data Vault Impact: Citus allows you to shard Hubs, Links, and Satellites by a common business key. When a multi-way join occurs, the compute overhead is distributed across the entire cluster, breaking through the single-node hardware ceiling.

3. Hydra & pg_analytics (Open-Source Columnar Extensions)

How it helps: These extensions add a native columnar storage engine directly inside the PostgreSQL kernel.

Data Vault Impact: By enabling columnar storage for non-historized links, dependent child links, and bridge tables, queries only scan the exact attributes requested by a BI tool, eliminating the I/O tax of scanning entire rows.

Conclusion

Vanilla PostgreSQL is an exceptional transactional engine, but its row-based architecture, single-node limitations, lack of inner join reduction, and row-by-row window function execution create structural barriers for large-scale Data Vault implementations.

Postgres should be protected for what it excels at: transaction management and operational metadata serving. When transitioning to heavy Data Vault modeling and analytical workloads, engineers must decouple their compute to an internal data lake or look toward specialized open-source derivatives like Apache Cloudberry that preserve both technological sovereignty and high-performance scalability.

 

Cover Image designed by Magnific

In Data Vault Friday

Defining Dimension Keys in Data Vault Dimensional Models

Defining Dimension Keys in Data Vault Dimensional Models

A recurring challenge that data engineers and BI architects face during dimensional modeling workshops is determining the optimal strategy for defining dimension keys when deriving Information Marts from a Raw Data Vault. The choice of dimension key architecture directly impacts query performance, system scalability, distributed processing capabilities, and the overall complexity of analytical views. To make an informed architectural decision, data teams must evaluate two primary parameters: the temporal granularity of the dimension (Type 1 vs. Type 2) and the underlying surrogate key mechanism (hash values vs. traditional sequence numbers).

When establishing a robust data warehouse architecture, implementing proper modeling patterns across your enterprise layer ensures that downstream reporting tools perform efficiently without introducing severe maintenance overhead. Organizations seeking to refine these architectures often turn to comprehensive Data Vault certification programs to standardize their dimensional modeling practices across disparate data engineering teams.

Understanding the mechanics behind both Type 1 and Type 2 dimensions within the context of a Data Vault ecosystem provides the necessary clarity to choose between hash-based keys and integer sequence keys. Each combination offers distinct advantages and trade-offs depending on whether your processing engine relies on virtualized relational views, physical OLAP cubes, or distributed cloud data platforms.

In this article:


Understanding Type 1 and Type 2 Dimensions in Data Vault

To build effective Information Marts, architects must first distinguish how Slowly Changing Dimensions (SCD) operate when served from a Data Vault source layer. In traditional dimensional modeling, a Type 1 dimension overwrites attribute changes, maintaining only the current state of a business entity. Conversely, a Type 2 dimension tracks full history by adding a new record version every time an attribute value changes over time.

In a Data Vault architecture, the tracking of history is simplified and standardized within Satellite structures. Raw Data Vault Satellites record changes as they occur at the source system level. However, when delivering dimensional views for Business Intelligence tools, versioning is often aligned with snapshot dates rather than isolated attribute deltas. Instead of generating a new dimension row exclusively when an attribute changes, a version can be exposed for each relevant snapshot date per dimension member.

Most modern BI and reporting tools handle this snapshot-driven versioning seamlessly. The end user simply queries an entity description as it existed on a given presentation date. Whether that specific description originated from a newly inserted delta record or was carried forward from a prior snapshot in the Satellite layer is largely irrelevant to the reporting presentation layer. This fundamental mechanism establishes how both Type 1 and Type 2 dimension keys are derived and maintained across the analytical stack.

Evaluating Type 1 Dimension Key Options: Hashes vs. Sequences

For a Type 1 dimension—where history is excluded and only a single current record exists per entity—the dimension key must uniquely represent the business entity at its primary business key granularity. In an ideal scenario with clean data, a Type 1 dimension key matches the exact granularity of the Hub entity. The central decision lies in whether to expose the natural Data Vault hash keys in the Data Vault directly as the dimension key or substitute them with synthetic sequence numbers.

Option 1: Utilizing Hub Hash Keys for Type 1 Dimensions

The simplest and most direct pattern for a Type 1 dimension key is leveraging the primary hash key already generated inside the Hub. Because the Hub contains one distinct hash key per business key, this value naturally matches the unique granularity required by a Type 1 dimension. In scenarios where source system duplicates exist, master data resolution can be managed through a Same-as-Link structure, using the master record’s hash key as the unified dimension key.

Using the Hub hash key as the Type 1 dimension key offers significant operational benefits across the pipeline:

  • Ubiquity: The Hub hash key is already propagated across the entire Data Vault ecosystem, including Satellites, Links, Point-In-Time (PIT) tables, and Bridge tables.

  • Simplified Lineage: Facts derived from Non-Historized Links, Dependent Child Links, or Bridge tables natively retain these Hub hash keys, eliminating the need for complex join lookups during dimension generation.

  • Distributed Compatibility: Hash keys allow dimensions and facts to be built independently across distributed hybrid environments (such as cloud-hosted facts and on-premise master dimensions) while maintaining deterministic join alignment.

A common critique of using hash values as dimension keys revolves around string length, visual complexity, and storage overhead—particularly when using standard 32-character MD5 or SHA-256 hex strings. While these arguments were valid in legacy OLAP cube architectures, modern columnar data warehouses handle string storage and joins far more efficiently. Furthermore, for systems sensitive to memory footprint, teams can convert 32-character hex strings into 16-byte binary representations on-the-fly within the Information Mart views. This cuts key storage size in half without introducing complex sequence generation pipelines into the core Data Vault 2.0 repository.

Option 2: Implementing Sequence Keys for Type 1 Dimensions

When project stakeholders insist on numeric surrogate keys, teams often consider generating integer sequences (such as BIGINT) to act as Type 1 dimension keys. While sequence keys reduce storage footprint down to 8 bytes, implementing them inside a Data Vault architecture requires specific structural patterns to avoid anti-patterns like record lookups during raw loading.

Two primary patterns exist for introducing Type 1 sequence keys into a Data Vault framework:

  1. Hub Sequence Extension: Adding an incremental sequence generator directly to the Hub table structure. This sequence is strictly populated upon Hub insertion and serves exclusively as a technical surrogate key for downstream dimensional export, never for raw loading lookups.

  2. Computed Satellite Sequences: Creating a dedicated Computed Satellite attached to the Hub within the Business Vault layer. A sequence generator function assigns an integer ID to each unique business key as new records are registered in the Hub.

The Computed Satellite approach represents the cleaner architectural pattern because it keeps core Hub definitions unpolluted and treats key assignment as an explicit business rule. However, sequence keys introduce severe operational drawbacks in modern distributed systems. Because sequence generators are stateful and system-dependent, an on-premise system and a cloud-based warehouse will generate completely different sequence numbers for the same business key. Reconciling facts and dimensions across distributed platforms requires cross-network lookups through corporate firewalls, introducing network latency, security bottlenecks, and pipeline fragility.

Mastering Type 2 Dimension Keys and Pit Table Integration

Type 2 dimensions introduce a higher level of complexity because the dimension key must uniquely identify a specific version or snapshot state of a dimension member. Joining facts to a Type 2 dimension requires aligning both the business entity identifier and the relevant temporal boundary.

Multi-Column Joins vs. Single-Column Hash Keys

One approach to building Type 2 dimensions involves joining facts to dimensions using a compound primary key consisting of the Type 1 Hub hash key and the corresponding snapshot date. While functional, compound join conditions across multi-column keys can degrade query execution speed, complicate BI semantic layer configurations, and increase overall query complexity inside custom analytical dashboards.

The preferred Data Vault standard utilizes a single-column composite hash key for Type 2 dimensions. This surrogate version key is calculated by concatenating the primary business key with the specific snapshot date, then applying the standard hashing algorithm:

  • Type 2 Dimension Key = HASH(Business Key + Snapshot Date)

This formulation guarantees a unique 1-column hash identifier for every distinct snapshot version of every dimension member, matching the exact temporal granularity of the Type 2 dimension.

Generating Type 2 Keys via PIT Tables and Bridge Tables

Rather than calculating complex hash expressions dynamically inside user-facing views—which can hamper execution performance—data engineers leverage Point-In-Time (PIT) tables to materialize these Type 2 dimension keys efficiently.

In standard Data Vault modeling practices, the primary key of a PIT table is defined as the unique hash value derived from the parent business key and the snapshot date. Simultaneously, the PIT table retains an alternate compound key consisting of the individual Hub hash key and the snapshot date column.

This structural design enables a highly optimized join pattern when building information marts:

  1. Dimension Layer: The Type 2 dimension view uses the primary key of the PIT table (the pre-calculated composite Type 2 hash key) directly as its single-column dimension key.

  2. Fact Layer: Fact tables and Bridge tables contain the individual Hub hash key along with temporal event dates or snapshot dates.

  3. Information Mart Join: To assign the correct Type 2 dimension key reference inside a fact view, the Bridge or Fact table performs an inner join against the PIT table’s alternate key (Hub Hash Key + Snapshot Date). This lookup instantly retrieves the PIT table’s primary key—the Type 2 dimension key—without requiring complex dynamic hashing functions during query execution.

By grounding both the fact and dimension views on the underlying PIT table structure, query engines perform highly optimized single-column joins while guaranteeing complete temporal consistency across all historical snapshots.

Managing Snapshot Retention and the Fog of Data

While PIT-based Type 2 dimension keys deliver high query performance, architects must account for long-term storage lifecycle policies. In production environments, data teams often implement logarithmic snapshot retention strategies within PIT tables to control table size over time. For example, a system might retain daily snapshots for the past 30 days, weekly snapshots for the past year, and monthly snapshots thereafter.

When older daily snapshots are pruned from a PIT table, facts referencing those specific historical dates risk losing their corresponding PIT key association—a concept known in analytical modeling as the “fog of data.” Addressing these temporal gaps requires specialized Business Vault logic, such as snapshot windowing routines or fallback date assignment rules detailed in The Data Vault Handbook. Ensuring that historical fact records map gracefully to the nearest available dimension snapshot maintains auditability without sacrificing query performance.

Selecting the right dimension key strategy ultimately balances system architecture, pipeline simplicity, and downstream query performance. For modern cloud data platforms and virtualized Information Marts, utilizing native Data Vault hash keys for Type 1 dimensions and PIT-derived composite hash keys for Type 2 dimensions provides the most scalable, deterministic, and maintainable foundation for enterprise analytics.

Watch the Video