updated_at

snapshots/<filename>.sql

{{ config(
  strategy="timestamp",
  updated_at="column_name"
) }}

dbt_project.yml

snapshots:
  <resource-path>:
    +strategy: timestamp
    +updated_at: column_name

Description

A column within the results of your snapshot query that represents when the record row was last updated.

This parameter is required if using the timestamp strategy.

Default

No default is provided.

Examples

Use a column name updated_at

snapshots/orders.sql

{% snapshot orders_snapshot %}

{{
    config(
      target_schema='snapshots',
      unique_key='id',

      strategy='timestamp',
      updated_at='updated_at'
    )
}}

select * from {{ source('jaffle_shop', 'orders') }}

{% endsnapshot %}

Coalesce two columns to create a reliable updated_at column

Consider a data source that only has an updated_at column filled in when a record is updated (so a null value indicates that the record hasn't been updated after it was created).

Since the updated_at configuration only takes a column name, rather than an expression, you should update your snapshot query to include the coalesced column.

snapshots/orders.sql

{% snapshot orders_snapshot %}

{{
    config(
      target_schema='snapshots',
      unique_key='id',

      strategy='timestamp',
      updated_at='updated_at_for_snapshot'
    )
}}

select
    *,
    coalesce(updated_at, created_at) as updated_at_for_snapshot

from {{ source('jaffle_shop', 'orders') }}

{% endsnapshot %}