We recently had to migrate changes captured in a
check strategy snapshot to a new version of the snapshot that included an extra column in
Here’s are the steps and a simplified version of the script we used.
- Rename the old snapshot.
use schema snapshots; alter table snapshots.my_snapshot rename to my_snapshot_old;
Run the snapshot
dbt snapshot -s my_snapshot
dbt_scd_idcolumn to include the
new_columnpreserving the previously captured values for other columns, as well as dbt valid from/to dates.
create table my_snapshot_old_migrated as ( with changed_ids as ( select id from snapshots.my_snapshot_old group by 1 having count(*) > 1 ), migrated_dbt_ids as ( select o.*, md5( coalesce(cast(o.id as varchar ), '') || '|' || coalesce(cast(o.name as varchar ), '') || '|' || coalesce(cast(o.price as varchar ), '') || '|' || coalesce(cast(o.new_column as varchar ), '') ) as dbt_scd_id_migrated from snapshots.my_snapshot_old o join changed_ids p on o.id = p.id ) select o.id, o.name, o.price, o.new_column, o.dbt_scd_id_mig as dbt_scd_id, o.dbt_updated_at, o.dbt_valid_from, o.dbt_valid_to from migrated_dbt_ids o order by o.id ) ;
- Delete any matching rows from the new snapshot.
delete from snapshots.snapshot where dbt_scd_id in ( select dbt_scd_id from snapshots.snapshots_migrated );
- Insert the migrated rows.
insert into snapshots.snapshot select * from snapshots.snapshots_migrated ;