Migrating a snapshot after adding a check_col

Archive
1

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

Here’s are the steps and a simplified version of the script we used.

  1. Rename the old snapshot.
use schema snapshots;
alter table snapshots.my_snapshot rename to my_snapshot_old;

  1. Run the snapshot dbt snapshot -s my_snapshot

  2. Migrate the dbt_scd_id column to include the new_column preserving 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
)
;
  1. 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
);
  1. Insert the migrated rows.
insert into snapshots.snapshot
select * from snapshots.snapshots_migrated
;