Snowflake ALTER TABLE: How to Modify Table Structures
The ALTER TABLE command in Snowflake allows you to modify an existing table’s structure or properties directly without needing to recreat it. It enables you to:
- Rename or swap tables.
- Add, modify, or drop columns.
- Adjust settings like data retention times or schema evolution.
- Apply data governance measures such as tags and access policies.
Examples
Rename a Table
Snowflake SQL Query
_10ALTER TABLE original_table RENAME TO new_table_name;
Explanation
This command changes the name of an existing table from original_table to new_table_name. It’s a simple way to update the table’s identifier without affecting the data within it.
Swap Two Tables
Snowflake SQL Query
_10ALTER TABLE table_one SWAP WITH table_two;
Explanation
Use this command to interchange the names and all associated properties between two tables. It’s like renaming two tables simultaneously in a single operation, which can be handy during system migrations or when promoting a staging table to production.
Add New Column
Snowflake SQL Query
_10ALTER TABLE employees ADD COLUMN age INT;
Explanation
This adds a new column named age with an integer data type to the employees table. Adding a column lets you store additional data about each entry in the table without affecting existing data.
You can find more configurations and properties that can be set up when adding a new column here.
Rename a Column
Snowflake SQL Query
_10ALTER TABLE employees RENAME COLUMN firstname TO first_name;
Explanation
If you need to correct or change a column name for clarity or standards compliance, use this command. It updates the column’s name from firstname to first_name, making it clearer and more consistent with common naming conventions.
Drop and Add a Row Acceess Policy in One Command
To replace an existing row access policy with a new one:
Snowflake SQL Query
_10ALTER TABLE my_table _10DROP ROW ACCESS POLICY existing_policy, _10ADD ROW ACCESS POLICY new_policy ON (column_name);
