SCD_AT_TIME()

All functions > BUSINESS > SCD_AT_TIME()

Returns the state of an SCD (slowly changing dimension) value at the given point in time.

Signatures

Returns: State record value at point in time

SCD_AT_TIME(scd: ARRAY<T>, point_in_time: TIMESTAMP) → T

With:

• T : State record: ROW<time_update: TIMESTAMP, ...>

Notes

• SCD (Slowly Changing Dimension) tracks values that change over time
• Each record has a time_update timestamp and a value
• Returns the most recent value where time_update <= point_in_time
• If no records exist before point_in_time, returns NULL
• Useful for historical analysis and time travel queries
• Commonly used for tracking customer attributes, prices, or configurations over time

Examples

SELECT
    f1 := SCD_AT_TIME(ARRAY[ROW(TIMESTAMP '2022-01-01 00:00:00' AS time_update, 'A' AS value), ROW(TIMESTAMP '2022-06-01 00:00:00' AS time_update, 'B' AS value)], TIMESTAMP '2022-03-01 00:00:00')[value], -- Value at a timestamp between two SCD updates
    f2 := SCD_AT_TIME(ARRAY[ROW(TIMESTAMP '2022-01-01' AS time_update, 'A' AS value)], TIMESTAMP '2021-01-01')[value] -- NULL when the point in time is before the first history row
;