I have been creating snapshots of a table fine. However, recently, one column changes data type from number to boolean, so the snapshot run has been failing ever since, returning this error Expression type does not match column data type, expecting NUMBER(38,0) but got BOOLEAN
Is there a way to just stack the new snapshots on top of the old one? Like boolean values on top of the number value?
Or anyone has any better ideas on how I can still snapshot the table without losing all the past snapshots?
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.
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)
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
Rename your old column so that the name is available for reuse: alter table my_table rename column impacted_column to impacted_column_old
Add a new column with the right datatype: alter table my_table add column impacted_column boolean
Backfill the new column: update table my_table set impacted_column = case when impacted_column_old = 1 then true else false end
Drop the old column: alter table my_table drop column impacted_column_old
You should probably make a copy of the table first just in case: