snapshots when column data type changes

Help
2

dbt will upcast column types when possible (e.g. changing from a varchar(20) to varchar(100) when the column gets larger).

But it can’t safely convert a number column into a boolean column. Let’s say the column you’re having issues with is called impacted_column. You could do a couple of things:

Make the existing query backwards compatible

In your snapshot block, have something like case when impacted_column is true then 1 else 0 end as impacted_column

Manually update the existing table

You could also update the table to contain a boolean column, and migrate the existing column’s values. As long as the next time the snapshot runs, the right columns still exist, dbt will be happy.

:warning: You should probably make a copy of the table first just in case: create table my_db.snapshots.my_table_backup as (select * from my_db.snapshots.my_table)

  1. Ensure that your snapshot jobs are paused or are scheduled far enough into the future that it won’t run while you’re making changes
  2. Rename your old column so that the name is available for reuse: alter table my_table rename column impacted_column to impacted_column_old
  3. Add a new column with the right datatype: alter table my_table add column impacted_column boolean
  4. Backfill the new column: update table my_table set impacted_column = case when impacted_column_old = 1 then true else false end
  5. Drop the old column: alter table my_table drop column impacted_column_old
  6. Run the dbt snapshot command again
1 Like