Creating dbt snapshots in YAML

1.What a snapshot records

A snapshot keeps the history that a mutable table throws away. Take an orders table whose status column is overwritten in place. When order 1 moves from pending to shipped, the row now reads shipped with a new updated_at, and nothing in the table says when it was pending. You can no longer work out how long orders take to ship. dbt snapshots fix this by implementing type-2 slowly changing dimensions. Instead of one row per order, the snapshot table holds one row per version of the order, and each row is stamped with the period when that version was true.

Two meta columns hold the history. dbt_valid_from marks when a version became true, and dbt_valid_to marks when it stopped being true. The current version has no end yet, so by default its dbt_valid_to is NULL. In the table above, the pending version ran from 2024-01-01 to 2024-01-02, and the shipped version is still open.

From dbt v1.9 on, you define snapshots in YAML properties files, placed in either the models directory or a snapshots directory. The older form, a {% snapshot %} Jinja block in a .sql file with a config() call, still works but is now the legacy method. New snapshots should use YAML, which is shorter and environment aware.

Sources12

2.Anatomy of a YAML snapshot

A snapshot entry sits under a top-level snapshots: key and has three main parts: a name, a relation that points at the data to track, and a config block that tells dbt how to detect changes. An optional description sits beside name to document the snapshot. relation takes either source() or ref(), the same functions that connect models in the DAG. The name identifies the snapshot within the project, and downstream models select from it with ref('orders_snapshot').

snapshots:
  - name: orders_snapshot
    relation: source('jaffle_shop', 'orders')
    config:
      schema: snapshots
      unique_key: id
      strategy: timestamp
      updated_at: updated_at

Two configs are always required. strategy must be timestamp or check, and it has no default. unique_key can be a primary key column, an array of columns such as [order_id, product_id], or an expression. dbt uses it to match each incoming row to that record's history. Each strategy also needs one more config of its own: updated_at for timestamp, check_cols for check.

The example has no target_schema. Before v1.9, every snapshot had to name a fixed target_schema, which made it hard to keep dev and prod apart. That requirement is gone. Without location configs, a snapshot builds wherever the generate_schema_name and generate_database_name macros put it, just like a model. Set schema and database when you want a custom location; a custom schema still goes through generate_schema_name. You can also apply the same settings from dbt_project.yml under the snapshots: key. Configs apply hierarchically, with the snapshot's YAML file listed ahead of dbt_project.yml and taking precedence. The general precedence rules are covered in [project configuration](lesson:1.6).

Sources312

3.Choosing the timestamp or check strategy

The strategy decides how dbt tells that a row has changed. With timestamp, dbt compares the row's updated_at value with the last snapshot run. If it is newer, dbt closes the old version and inserts the new one; if it is unchanged, dbt does nothing. With check, dbt compares the current values of the columns in check_cols against their stored values, and any difference counts as a change.

Use timestamp whenever the source has a reliable last-updated column. It tracks only one column, so adding or removing source columns needs no config change. A check snapshot that lists status and is_cancelled compares only those two. If another column starts to matter, someone has to edit check_cols. check_cols: all removes that upkeep, but comparing every column may be less performant. check is the right choice when the source has no trustworthy timestamp at all.

snapshots:
  - name: orders_snapshot_check
    relation: source('jaffle_shop', 'orders')
    config:
      schema: snapshots
      unique_key: id
      strategy: check
      check_cols:  ? 

updated_at has one catch: it takes a column name, not an expression. Picture a source that fills updated_at only after a record is first modified, so rows that were never changed have a null. coalesce(updated_at, created_at) fixes that, but you can't write it in the config. Instead, compute it in a staging or ephemeral model as a new column such as updated_at_for_snapshot. Point relation at that model with ref() and set updated_at: updated_at_for_snapshot. Depending on the data platform, the column may also hold ISO date strings or unix epoch integers. dbt warns if its data type doesn't match the adapter's configured default.

Sources1456

4.Current-row markers and hard deletes

A NULL end date on current rows makes date-range filters awkward. dbt_valid_to_current replaces it with a value you choose, such as "to_date('9999-12-31')", so current rows get a far-future end date. snapshot_meta_column_names renames the meta columns themselves, for example dbt_valid_from to start_date and dbt_valid_to to end_date. It takes a dictionary that maps each default meta column name to the name you want. Besides dbt_valid_from and dbt_valid_to, the snapshot table carries dbt_scd_id and dbt_updated_at, plus dbt_is_deleted when hard_deletes: new_record is on.

Neither strategy records a deleted source row unless you configure it. The hard_deletes config controls this, and its default is the one most likely to mislead. With ignore, a row deleted from the source keeps its NULL dbt_valid_to, so the snapshot still shows it as current.

snapshots:
  - name: my_snapshot
    config:
      hard_deletes: new_record  # options are: 'ignore', 'invalidate', or 'new_record'
      strategy: timestamp
      updated_at: updated_at

new_record adds a dbt_is_deleted column. A deletion inserts a row flagged True, and if the record comes back later, the restored version is flagged False. That keeps the history continuous, with no gaps, whereas invalidate only closes the last version. hard_deletes works with dbt-postgres, dbt-bigquery, dbt-snowflake and dbt-redshift. It replaces the legacy invalidate_hard_deletes=true. The old config still works on existing snapshots, but you can't use it together with hard_deletes.

Skipping the migration leaves a table in two formats. Say an existing snapshot adopts dbt_valid_to_current with snapshot_meta_column_names renaming the meta columns to start_date and end_date. The columns must exist under those names first, which is why the documented migration adds dbt_valid_from and dbt_valid_to with alter table. Even then, rows written before the change keep NULL in end_date, while every row inserted afterwards gets 9999-12-31. A filter on end_date then treats the same kind of current row two different ways, and downstream results are wrong.

Sources72

5.Running, protecting and consuming a snapshot

The dbt snapshot command builds snapshots (see [core commands](lesson:1.4)). The first run creates the table: the query's result set plus the meta columns, with every row open. Open means dbt_valid_to is NULL, or the dbt_valid_to_current value if you set one. Each later run looks for changed and new records. It closes changed records by setting dbt_valid_to, then inserts the new versions and any new records as open rows. History only builds up when the command runs, so schedule it to run regularly.

Snapshots can't be rebuilt. You can drop a model and rebuild it from its sources, but a snapshot's history exists only in the snapshot table. That's why dbt suggests a separate schema, such as snapshots, so end users know these tables are special. Give that schema different privileges from your models, and possibly run snapshots as a different user or role, so a snapshot is hard to drop by accident. Access is controlled with the same grants config models use ([grants](lesson:1.9)).

The unique_key needs a test. dbt matches rows by this key, so it has to be truly unique. When snapshotting a source, add a uniqueness test on that source ([data tests](lesson:5.1)). Do cleaning and deduplication upstream: build an ephemeral or staging model that applies them, then point relation at it with ref() ([ephemeral materialization](lesson:1.2)). The snapshot definition stays clean, and you can test and run the transformation on its own.

Sources1

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.You can write updated_at: coalesce(updated_at, created_at) directly in the snapshot config.Why is that wrong?

   updated_at takes only a column name. Compute the coalesced column in a staging or ephemeral model and point relation at it with ref().

   Covered in Choosing the timestamp or check strategy

2. 2.With default settings, a row deleted from the source is closed out in the snapshot.Why is that wrong?

   The default is hard_deletes: ignore, so the deleted row keeps a NULL dbt_valid_to and still looks current. Use invalidate or new_record to record deletions.

   Covered in Current-row markers and hard deletes

3. 3.Every snapshot must set target_schema.Why is that wrong?

   Since v1.9, target_schema is optional. Without it, snapshots use generate_schema_name and generate_database_name, so they build in the right place for each environment.

   Covered in Anatomy of a YAML snapshot

4. 4.You can keep invalidate_hard_deletes: true and add hard_deletes: new_record to the same snapshot.Why is that wrong?

   invalidate_hard_deletes is legacy. It still works on its own for existing snapshots, but it can't be combined with hard_deletes.

   Covered in Current-row markers and hard deletes

5. 5.Adding dbt_valid_to_current to an existing snapshot converts its old rows to the new end-date value.Why is that wrong?

   dbt does not migrate the table. Old current rows keep NULL while new ones get the configured value, so you get mixed data and wrong downstream results.

   Covered in Current-row markers and hard deletes

Practise it for real

Create a timestamp snapshot of the orders source in YAML, record a change, and read the snapshot from a downstream model.

1. 1.Create snapshots/orders_snapshot.yml with name orders_snapshot, relation source('jaffle_shop', 'orders'), and config schema: snapshots, unique_key: id, strategy: timestamp, updated_at: updated_at.

   Why: A YAML snapshot needs a name, a relation and the configs its strategy requires.

   You should see: dbt parses the project without errors.

2. 2.Run dbt snapshot.

   Why: The first run creates the snapshot table from the source's current rows.

   You should see: The log shows 1 of 1 OK snapshot for orders_snapshot.

3. 3.Select from the snapshot table dbt created.

   Why: Confirms that the meta columns exist and that every row is open.

   You should see: Your source columns plus dbt_valid_from and dbt_valid_to, with dbt_valid_to NULL on every row.

4. 4.Update a row's status and updated_at in the source, then run dbt snapshot again.

   Why: Later runs close changed records and insert their new versions.

   You should see: That id now has two rows: the old one with dbt_valid_to filled in, and the new one open.

5. 5.Create models/changed_orders.sql containing select * from {{ ref('orders_snapshot') }} and build it.

   Why: Downstream models read snapshots through ref, which adds the snapshot to the DAG.

   You should see: The model builds and returns every version of every order.