# Migrating a snapshot after adding a check\_col

**URL:** <https://discourse.getdbt.com/t/migrating-a-snapshot-after-adding-a-check-col/485>\
**Category:** Archive\
**Created:** [July 30, 2019, 4:25pm UTC](https://discourse.getdbt.com/t/migrating-a-snapshot-after-adding-a-check-col/485 "2019-07-30T16:25:07Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![claus](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/claus/32/75_2.png) [@claus](https://discourse.getdbt.com/u/claus)\
**Post date:** [July 30, 2019, 4:25pm UTC](https://discourse.getdbt.com/t/migrating-a-snapshot-after-adding-a-check-col/485/1 "2019-07-30T16:25:07Z")

</div>

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.

```auto
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.

```auto
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.

```auto
delete from snapshots.snapshot
where dbt_scd_id in (
    select dbt_scd_id from snapshots.snapshots_migrated
);

```

1. Insert the migrated rows.

```auto
insert into snapshots.snapshot
select * from snapshots.snapshots_migrated
;

```