Skip to main content

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 %}