How to Efficiently Change Data Types in Snowflake for Optimal Performance

When working with data, I usually have an idea of what type of data I will push into a field. Sometimes, for whatever reason, it is decided to change the type of data allowed in the field. Today, I want to show how that’s done in Snowflake.

I will start out working with a simple table that I’m creating for this explicit purpose.

CREATE TABLE TestOutChanging
(      TheDateString varchar(10) NOT NULL,
       TheDateDate  date  NOT NULL,
       BadChoicesWereMade number(28, 12) NULL   );

After creating the table, I get some data to be used in the table. That data follows:

INSERT INTO TestOutChanging
SELECT '14 January 2020', '1/14/2020', 7.4 UNION ALL
SELECT '20 March 2021', '3/20/2021', 1.25 UNION ALL
SELECT '17 August 2029', '8/17/2029', 25.45;
       

The first thing I notice when reviewing the data before INSERTing it into the table, I see that the data for the field TheDateString is longer than is allowed for the table. So, I’ll go ahead and change it before even attempting to INSERT it.

ALTER TABLE TestOutChanging MODIFY COLUMN TheDateString varchar(25);

Yes, the commands are a little different than in SQL Server. Also, notice that we didn’t specify that the column is NULL or NOT NULL. If we truly need to mess with that, we’d use the following command:

ALTER TABLE TestOutChanging MODIFY COLUMN TheDateString DROP NOT NULL;

After we run our code block above to change the TheDateString field, we have the following table structure:

Now, let’s say that our group has decided that they really don’t want the field TheDateDate to be of date data type. They want it to be a datetime data type. (At least it means that I don’t have to change the field name.)

ALTER TABLE TestOutChanging MODIFY COLUMN TheDateDate datetime;

After running, I get this wonderful error message:

Yes, that’s right. Snowflake does not allow me to change from one data type to another data type. If you truly need to change data types in a field, you have two options:

1. Drop the table and recreate it – with your field having the new data type.
2. Drop the column and add it back with the new data type.

Another fun thing that you cannot do is to shrink the size of the column. For example, you cannot shrink the column TheDateString back down to varchar(25) if the data you get is corrected. At that point, you’re stuck with the two options I pointed out above.

You also cannot change the scale of a numeric data type once it is set. For example, if you found you needed to change the column BadChoicesWereMade from number(28, 12) to number(28, 18), you would not be allowed. Now, you can change it to number(32, 12) if you so desire.

And finally, you cannot change the default value for a column. Yes, that’s right. Once you have a default value for a column, you cannot drop the default or even change the default without Snowflake giving you an error.

With that, we have come to the end of yet another post. Hopefully, you’ve learned what you can and just flat-out cannot do with data types in Snowflake. If you find that you need to change a data type in Snowflake, be prepared and double-check that you really need to change. Otherwise, be like Billy Joel in today’s song.