updated_at
snapshots:
- name: snapshot
relation: source('my_source', 'my_table')
config:
strategy: timestamp
updated_at: column_name
snapshots:
<resource-path>:
+strategy: timestamp
+updated_at: column_name
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:
- 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.
-
Create an staging model to perform the transformation. In your
models/
directory, create a SQL file that configures an staging model to coalesce theupdated_at
andcreated_at
columns into a new columnupdated_at_for_snapshot
.models/staging_orders.sqlselect * coalesce (updated_at, created_at) as updated_at_for_snapshot
from {{ source('jaffle_shop', 'orders') }} -
Define the snapshot configuration in a YAML file. In your
snapshots/
directory, create a YAML file that defines your snapshot and references theupdated_at_for_snapshot
staging model you just created.snapshots/orders_snapshot.ymlsnapshots:
- name: orders_snapshot
relation: ref('staging_orders')
config:
schema: snapshots
unique_key: id
strategy: timestamp
updated_at: updated_at_for_snapshot -
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.