How to Change Column Type in Snowflake

Galaxy Glossary

How do I safely change a column’s data type in Snowflake?

ALTER TABLE … ALTER COLUMN … SET DATA TYPE converts a column to a new data type when the existing data can be cast.

Sign up for the latest in SQL knowledge from the Galaxy Team!

Description

Why would you change a column type in Snowflake?

Data models evolve. Converting a VARCHAR price column to NUMBER improves numeric accuracy, enables math functions, and tightens validation—without recreating the table.

What is the correct ALTER COLUMN syntax?

Use ALTER TABLE table_name ALTER COLUMN column_name SET DATA TYPE new_type. Include IF EXISTS to avoid errors on missing tables or columns.

How do I make sure the conversion is safe?

Run SELECT column_name::new_type on a sample to confirm casting works. Wrap the change in a transaction so you can ROLLBACK if unexpected errors appear.

Practical example: convert price from VARCHAR to NUMBER

--Verify that all prices are numeric
SELECT price FROM Products WHERE TRY_TO_NUMBER(price) IS NULL;

--Alter the column
ALTER TABLE Products ALTER COLUMN price SET DATA TYPE NUMBER(10,2);

Best practices for ALTER COLUMN

Back up critical tables with CREATE TABLE … CLONE. Run the change during low-traffic windows. Always add or rebuild indexes and constraints after the type switch.

Should I recreate views after the change?

Views referencing the altered column pick up the new type automatically. Re-run test queries to catch casting assumptions.

Why How to Change Column Type in Snowflake is important

How to Change Column Type in Snowflake Example Usage


--Ensure all existing values cast cleanly
SELECT order_date::TIMESTAMP_NTZ FROM Orders;

--Change the Orders.order_date column from VARCHAR to TIMESTAMP_NTZ
ALTER TABLE Orders ALTER COLUMN order_date SET DATA TYPE TIMESTAMP_NTZ;

How to Change Column Type in Snowflake Syntax


ALTER TABLE [IF EXISTS] <table_name>
    ALTER [COLUMN] <column_name>
    SET DATA TYPE <new_data_type> [USING <conversion_expression>];

-- Ecommerce-focused variants
ALTER TABLE Products ALTER COLUMN price SET DATA TYPE NUMBER(10,2);
ALTER TABLE Orders ALTER COLUMN order_date SET DATA TYPE TIMESTAMP_NTZ;
ALTER TABLE Customers ALTER COLUMN created_at SET DATA TYPE TIMESTAMP_LTZ;

Common Mistakes

Skipping a pre-check for non-convertible data. If any value in Products.price contains non-numeric text, the ALTER TABLE fails. Fix by cleansing or using TRY_TO_NUMBER during the audit.
Forgetting to recreate or validate constraints. Changing a VARCHAR to NUMBER drops implicit string functions in views and may break CHECK constraints. Reapply or adjust constraints post-migration.

Frequently Asked Questions (FAQs)

Does the column order or position change?

No. ALTER COLUMN only changes the data type; column order remains intact.

Is downtime required?

Snowflake performs the change in-place and online. Large tables may briefly lock DML, so schedule during low activity.

Can I revert the type change?

Use BEGIN … ROLLBACK or restore from a zero-copy CLONE.

Want to learn about other SQL terms?

dbt tests

dbt tests are declarative assertions in dbt that automatically validate data quality during model builds.

dbt utils

dbt utils is a community-maintained dbt package that adds time-saving utility macros for analytics engineers, such as generating surrogate keys, safely unioning models, and automating date spines.

dbt Tutorial Glossary: Build, Test & Deploy SQL Models

dbt is an open-source framework that lets data teams transform raw warehouse tables into tested, documented, production-ready datasets using version-controlled SQL.

Trusted by top engineers on high-velocity teams

Assort Health

Curri

Rubie