In modern data platforms, historisation and integration layers often rely on dual SCD2 (Slowly Changing Dimension Type 2) tables to store both technical and functional timelines of data.
These dual timelines allow us to track the full lineage of source data, including both business-driven reporting periods (functional timeline) and technical events such as reloads, hotfixes, or late-arriving data (technical timeline).
While dual SCD2 tables are powerful for preserving history, querying across multiple timelines can be computationally expensive, particularly in cloud platforms like Azure Databricks and Snowflake.
To improve query performance and reduce compute costs, we can introduce Point-in-Time (PIT) tables with surrogate keys.
PIT Tables: Concept and Benefits
The concept of PIT tables originates in the Data Vault 2.0 methodology (see Dan Linstedt & Michael Olschimke, Building a Scalable Data Warehouse with Data Vault 2.0, 2015). In Data Vault, PIT tables are used to create fast snapshot views* across historised data by precomputing the relationships between Hubs, Links, and Satellites at specific points in time.
In a similar way, PIT tables can be applied to dual SCD2 tables in historisation and integration layers. Each PIT table contains:
- The surrogate keys of the corresponding SCD2 table.
- A unique snapshot identifier represents a specific reporting date and includes additional metadata.
By joining fact or dimension queries to PIT tables instead of directly scanning historised data, we can significantly accelerate snapshot retrieval and simplify query logic.
Defining Snapshots
A snapshot in this context is uniquely identified by:
- Business reporting date: the functional snapshot date delivered by the data provider.
- Delivery number: this includes re-deliveries that occur on the same reporting date.
- Technical loading timestamp: capturing the moment data is loaded into the platform.
- Integration run identifier: distinguishing reruns of integration queries (e.g., after hotfixes, golden source updates, or reference data changes).
This combination provides a robust and unambiguous way to address specific versions of data, whether functional or technical.
Dual SCD2 Performance Features
Dual SCD2 tables already contain two important performance flags:
is_functional_current: This indicates whether the record belongs to the current functional snapshot.is_technical_current: This indicates whether the record is included in the current technical load.
These flags allow view schemas to efficiently filter for current-only scenarios, such as:
- Functional Current Views: show all functional snapshots (reporting dates) but only the latest technical load.
- Technical Current Views: show only the latest technical and functional version (most recent reporting date).
- Full History Views: expose all functional and technical versions, including re-deliveries and reruns.
However, as soon as multiple snapshots are queried, especially across both timelines, performance may degrade without additional optimizations.
PIT Tables for Dual SCD2
To address this, we introduce PIT tables matching each dual SCD2 table. Each PIT table contains:
- The surrogate key(s) of the dual SCD2 table.
- The unique snapshot identifier consists of the reporting date, delivery date, and technical run.
This design allows us to:
- Accelerate snapshot queries by avoiding full-table scans.
- Support dimensional modeling: surrogate keys can be mapped to virtual dimensional views or 3NF enterprise models, forming the foundation for Kimball-style datamarts.
- Lower cloud compute costs by pushing down simplified joins and filters.
Related Concepts: SnowPIT and Beyond
A related approach is SnowPIT, a Snowflake-specific implementation of PIT tables, described in Snowflake’s Data Vault Whitepaper (Snowflake, 2020). SnowPIT tables apply the PIT methodology to simplify point-in-time joins in Snowflake environments.
This demonstrates that PIT tables are not limited to Data Vault but can also be extended to dual SCD2 implementations in historisation and integration layers.
Conclusion
By introducing surrogate keys and PIT tables into the historisation and integration layers, data platforms can:
- Improve performance of complex snapshot queries.
- Reduce compute costs on cloud platforms.
- Provide a clear and scalable foundation for both enterprise data models and dimensional data marts.
Although PIT tables are rooted in Data Vault methodology, their benefits extend to any historisation architecture that requires efficient access to multiple timelines. When combined with dual SCD2 tables, PIT tables become a powerful enabler for delivering high-performing, flexible, and cost-efficient data platforms.
References
- Dan Linstedt & Michael Olschimke, Building a Scalable Data Warehouse with Data Vault 2.0 (2015).
- Snowflake, Data Vault, and Snowflake: Leveraging the Cloud Data Platform (2020).
- Roelant Vos is the author of various articles on PIT tables and performance on The Data Vault Guru Blog (datavaultguru.com).