snapshots when column data type changes

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?

Thanks a lot

1 Like

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

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.