Skip to main content

updated_at

snapshots/snapshots.yml
snapshots:
- name: snapshot
relation: source('my_source', 'my_table')
config:
strategy: timestamp
updated_at: column_name
dbt_project.yml
snapshots:
<resource-path>:
+strategy: timestamp
+updated_at: column_name

caution

You will get a warning if the data type of the updated_at column does not match the adapter-configured default.

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_snapshot.yml
snapshots:
- name: orders_snapshot
relation: source('jaffle_shop', 'orders')
config:
schema: snapshots
unique_key: id
strategy: timestamp
updated_at: updated_at

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.

  1. Create an staging model to perform the transformation. In your models/ directory, create a SQL file that configures an staging model to coalesce the updated_at and created_at columns into a new column updated_at_for_snapshot.

    models/staging_orders.sql
    select  * coalesce (updated_at, created_at) as updated_at_for_snapshot
    from {{ source('jaffle_shop', 'orders') }}

  2. Define the snapshot configuration in a YAML file. In your snapshots/ directory, create a YAML file that defines your snapshot and references the updated_at_for_snapshot staging model you just created.

    snapshots/orders_snapshot.yml
    snapshots:
    - name: orders_snapshot
    relation: ref('staging_orders')
    config:
    schema: snapshots
    unique_key: id
    strategy: timestamp
    updated_at: updated_at_for_snapshot

  3. Run dbt snapshot to execute the snapshot.

Alternatively, you can also create an ephemeral model to performs the required transformations. Then, you reference this model in your snapshot's relation key.

0