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. The updated_at field may support ISO date strings and unix epoch integers, depending on the data platform you use.

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 a 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.

Was this page helpful?

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

0
Loading