dbt Snapshots Preserve Historical Dimension Values

Dimension attributes rarely stay fixed. Product categories are reorganised, user segments evolve and internal teams are restructured. When these attributes change without any record of when each value was valid, historical analysis breaks: long-term trend reports group past data by today's categories, and historical comparisons stop meaning what they appear to mean.

Common practice makes this worse. Dimension tables are overwritten daily, or updated with MERGE statements that keep no as-of-date context. A conversion from 18 months ago then shows the product_category or user_segment it has now, not the one it had when the event happened. The data looks valid by today's values, but its history is wrong, which undermines trend analysis and year-over-year comparisons.

dbt snapshots implement slowly changing dimensions (SCD Type 2) as configuration. A snapshot names a unique_key and a change-detection strategy: timestamp, driven by an updated_at column, or check, which compares a list of check_cols. When a tracked attribute changes, dbt closes the old record and inserts a new one, maintaining dbt_valid_from and dbt_valid_to; the current record has a NULL dbt_valid_to. A complex engineering task becomes a clean, auditable declaration.

The benefit appears downstream. Joining events to a snapshot on the key, with TIMESTAMP_MICROS(event_timestamp) between dbt_valid_from and dbt_valid_to, returns the attributes that were valid when each event occurred. Attribution models can credit a conversion to the user_segment the user belonged to at the time, even after the user has moved to another segment. For first-party data collected through tools such as Robin Signals, the same historical context supports accurate long-term segmentation and behavioural analysis, and it is a precondition for estimating the causal impact of campaigns over time.

Any production data warehouse, on BigQuery or another platform, should snapshot its key dimensions. Reliable history is what makes trend reporting and long-term analysis trustworthy.