Preserve query results at a point in time

Help
6

Do you mean how do you query a snapshot table to get a point-in-time result? If so then,

yes!

You would do something like

select * 
from {{ ref('the_snapshot') }} 
where getdate() between dbt_valid_from and dbt_valid_to

, keeping in mind that the current version of a row will have dbt_valid_to of null. It’s common to coalesce that to a value in the distant future for ease of querying, normally something like 2099-12-31.

Check out Building models on top of snapshots for our best practices when working with snapshots.

Snapshots are updated each time you run the dbt snapshot command, and will never be dropped by dbt, even with a full-refresh command. It’s a good idea to put your snapshots into a different schema to prevent someone from accidentally dropping them, as their data is by definition non-recoverable (without backups), in contrast to most dbt models where as long as you have the source tables you can get back to the starting point.

1 Like