Bird
Raised Fist0
SnowflakeHow-ToBeginner · 3 min read

How to Alter Table in Snowflake: Syntax and Examples

To alter a table in Snowflake, use the ALTER TABLE command followed by the table name and the specific modification like ADD COLUMN, DROP COLUMN, or RENAME COLUMN. This command lets you change the table structure without losing data.
📐

Syntax

The ALTER TABLE command in Snowflake allows you to modify an existing table's structure. You specify the table name and the action you want to perform, such as adding, dropping, or renaming columns.

  • ADD COLUMN: Adds a new column to the table.
  • DROP COLUMN: Removes an existing column.
  • RENAME COLUMN: Changes the name of a column.
  • SET DATA TYPE: Changes the data type of a column.
sql
ALTER TABLE table_name ADD COLUMN column_name data_type;
ALTER TABLE table_name DROP COLUMN column_name;
ALTER TABLE table_name RENAME COLUMN old_name TO new_name;
ALTER TABLE table_name ALTER COLUMN column_name SET DATA TYPE new_data_type;
💻

Example

This example shows how to add a new column, rename an existing column, and drop a column from a table named employees.

sql
ALTER TABLE employees ADD COLUMN department STRING;
ALTER TABLE employees RENAME COLUMN department TO dept_name;
ALTER TABLE employees DROP COLUMN dept_name;
Output
Statement 1 executed successfully. Statement 2 executed successfully. Statement 3 executed successfully.
⚠️

Common Pitfalls

Common mistakes when altering tables in Snowflake include:

  • Trying to drop a column that does not exist, which causes an error.
  • Renaming a column to a name that already exists in the table.
  • Changing a column's data type to an incompatible type without data conversion.
  • Not considering the impact on dependent views or queries.
sql
/* Wrong: Dropping a non-existent column */
ALTER TABLE employees DROP COLUMN non_existent_column;

/* Right: Check column exists before dropping or handle error */
-- Use DESCRIBE TABLE employees; to verify columns before altering
📊

Quick Reference

ActionSyntax ExampleDescription
Add ColumnALTER TABLE table_name ADD COLUMN col_name data_type;Adds a new column to the table.
Drop ColumnALTER TABLE table_name DROP COLUMN col_name;Removes a column from the table.
Rename ColumnALTER TABLE table_name RENAME COLUMN old_name TO new_name;Changes the name of a column.
Change Data TypeALTER TABLE table_name ALTER COLUMN col_name SET DATA TYPE new_type;Modifies the data type of a column.
✅

Key Takeaways

Use ALTER TABLE followed by the specific action to modify table structure in Snowflake.
Always verify column names exist before dropping or renaming to avoid errors.
You can add, drop, rename columns, and change data types safely without losing data.
Check dependencies like views or queries before altering table columns.
Test changes in a development environment before applying to production.