Updating varchar to valid currency fields in Snowflake

Have you ever set up tables in snowflake and realized later than you have varchar types instead of currency fields? This is common in Snowflake since it’s not a relational database platform. It can be set up to function in a similar way to support various BI platforms.

I was manually adding a new column as a NUMBER(38,2)) and then copying all the data from the varchar column to that in the right format using the TRY_CAST([column name] AS NUMBER(38,2)) and then deleting the old column and renaming the new one to the old name. This was pretty time intensive and prone to user error. So, here is a better way using a stored procedure that you can list the columns you want to alter and then run the procedure to quickly change them.

Why do this? In BI applications, in order to use a field for measurement, you must have numerical values and varchar fields won’t cut it.

Here is the code.

CREATE OR REPLACE PROCEDURE change_column_types()
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
// The name of the table you want to change
var table = '[schema].[database].[table]';

// List of columns to change
var columns = ['AMOUNTDUE', 'CREDITAMT', 'INVOICED', 'PAID', 'RETAINAGE']; 

columns.forEach(function(column) {
    var sql;

    // Add new column with NUMBER(38,2) type
    sql = 'ALTER TABLE ' + table + ' ADD COLUMN ' + column + '_new NUMBER(38,2);';
    snowflake.execute({sqlText: sql});

    // Copy data from old column to new column with casting
    sql = 'UPDATE ' + table + ' SET ' + column + '_new = TRY_CAST(' + column + ' AS NUMBER(38,2));';
    snowflake.execute({sqlText: sql});

    // Drop old column
    sql = 'ALTER TABLE ' + table + ' DROP COLUMN ' + column + ';';
    snowflake.execute({sqlText: sql});

    // Rename new column to old column name
    sql = 'ALTER TABLE ' + table + ' RENAME COLUMN ' + column + '_new TO ' + column + ';';
    snowflake.execute({sqlText: sql});
});

return 'Column types changed successfully.';
$$;

-- To execute the procedure
CALL change_column_types();

That should do it. Just add the table name and update the array of columns you want to alter. As always, I recommend backing up the table you are altering. There is nothing worse than accidentally dropping a table with lots of data.

Happy coding.