Keep the history of changing values with snapshots

How to do it

A load copies today's value, so last quarter's revenue by plan would use today's plans. This kind of history is called a type 2 slowly changing dimension. A transformation tool such as dbt has snapshots that build it. Use the timestamp strategy with an updated_at column, or compare listed columns when there is none. Record deletes as their own version. Snapshots cannot be rebuilt from the source, so protect them as Rehearse restoring a dropped warehouse table says. Personal columns in snapshots still follow Erase people from the warehouse within the month. When the app already keeps row history, load that table instead.

Examples

YAML example

snapshots:
  - name: customer_plans
    relation: source('app', 'customers')
    config:
      unique_key: id
      strategy: timestamp
      updated_at: updated_at
      hard_deletes: new_record

SQL example

-- Revenue by the plan each customer had on the day of the order
SELECT c.plan, sum(o.amount) AS revenue
FROM orders o
JOIN customer_plans c
  ON c.id = o.customer_id
 AND o.ordered_at >= c.dbt_valid_from
 AND o.ordered_at < coalesce(c.dbt_valid_to, '9999-12-31')
GROUP BY c.plan;

How to check

Change a test customer's plan in the staging source and run the load. The snapshot holds two rows for that customer whose periods do not overlap, and exactly one is current. A model test fails on any key with two current rows.

Sources

• dbt snapshots

Related rules

• DEN-33 Rehearse restoring a dropped warehouse table in Data engineering
• DEN-25 Erase people from the warehouse within the month in Data engineering
• DAT-76 Keep the history of records that settle disputes in Data and databases