A validity period is the interval during which a stored version of an entity represents its applicable state. In a slowly changing dimension type 2 (SCD2) table, each version carries valid-from and valid-to timestamps. A current version commonly uses a far-future end date when its actual end is not yet known. section 12.2 section 12.2.1
When an attribute changes, preserve the prior version rather than overwrite it. Insert a row for the new state beginning at the change date, and end the earlier row at that same date. Because an entity can have multiple versions, its identifier alone is not unique; an SCD2 key combines the entity identifier with the validity start date or timestamp. section 12.2.1
Table 12.3 Sample data in the PARTNER table according to the SCD2 pattern
|
Partner ID
|
Partner name
|
Rating
|
Valid from
|
Valid to
|
|---|---|---|---|---|
|
103
|
Crave Coffee
|
B
|
1.6.2023
|
8.8.2023
|
|
103
|
Crave Coffee
|
A
|
8.8.2023
|
31.12.9999
|
Compare the two partner versions: rating B ends when rating A begins, while the new version remains current through the far-future date. section 12.2.1
An append-only design stores only valid-from. Each change inserts another version and never updates the earlier row. Derive valid-to as the next valid-from for the same entity; the final row receives a far-future date. This avoids update logic and preserves history, while leaving interval calculation to a view or materialized result. section 12.2.2 section 12.5
Derive intervals from appended versions
Use LEAD() within each entity’s ordered version history to retrieve the following valid-from value. NVL() replaces the NULL for the last version with 9999-12-31, producing a valid-to value without changing stored history. select partner_id, valid_from, NVL(LEAD(valid_from) over ( partition by partner_id order by valid_from ), '9999-12-31') as valid_to from PARTNER; section 12.2.2
In an incremental Snowflake pipeline, append changed rows from a stream into a warehouse history table, then expose calculated intervals through a view. For example, a product category change creates a second version for the same product ID; the earlier version ends on the new version’s valid-from date. section 12.3.2 section 12.5
Table 12.10 Contents of the PRODUCT_VALID_TS view
|
Product ID
|
Product name
|
Category
|
Minimum quantity
|
Price
|
Valid from
|
Valid to
|
|---|---|---|---|---|---|---|
|
1
|
Baguette
|
Bread
|
2
|
2.5
|
2023-06-01
|
9999-12-31
|
|
2
|
Bagel
|
Bread
|
6
|
1.3
|
2023-06-01
|
9999-12-31
|
|
3
|
English muffin
|
Bread
|
6
|
1.2
|
2023-06-01
|
2023-08-08
|
|
3
|
English muffin
|
Pastry
|
6
|
1.2
|
2023-08-08
|
9999-12-31
|
|
13
|
Sourdough bread
|
Bread
|
1
|
3.6
|
2023-08-08
|
9999-12-31
|
Inspect the resulting product view to see unchanged products remain open-ended and the English muffin’s Bread version end when its Pastry version begins. section 12.3.2
When querying historical data:
- Filter the timeline for the point in time required, rather than treating every stored version as current. section 12.5
- Order versions by valid-from within each entity before deriving the following start time as valid-to. section 12.2.2
- Design repeated incremental runs to avoid duplicate versions, gaps, and inconsistent timelines. section 12.5
