HOW TO: Rename a Snowflake column (2024)

Ever found yourself staring at a data column wondering, "Why did I name it that !?" You're not alone.  For many of us, especially Snowflake users, the data landscape keeps changing. As new data flows in, those old column names might not make the cut anymore. Renaming columns isn't just about keeping things tidy—it's about staying updated and making the data conversations clear and consistent.

In this article, we’ll provide a step-by-step guide to renaming columns in Snowflake using simple SQL commands. We’ll cover the prerequisites, walk through the SQL syntax, troubleshoot common issues, and share tips for effectively managing Snowflake column renames.

Let’s dive right in!

Prerequisites to Snowflake rename column

Before renaming columns in Snowflake, you’ll need:

Without these, you'll not be able to rename Snowflake columns.

Want to take Chaos Genius for a spin?

It takes less than 5 minutes.

Enter your work email
Enter your work email

Step-by-Step Guide to Snowflake Rename Column

Renaming columns in Snowflake is straightforward if you follow these steps:

Step 1: Log in to Snowflake Web Interface (Snowsight)

Access Snowflake's official website and enter your credentials to Sign-up/log in.

Snowflake Login Page - Snowflake rename column

Step 2: Navigate to the SQL Worksheet

Once logged in, from the dashboard, head over to your profile and select the default role and warehouse.

Selecting the default role and warehouse

Then, click on the "Worksheets" tab on the top navigation bar. This is where you'll execute SQL commands.

Selecting worksheets tab

Step 3: Create a Database and Schema

Before creating tables, you'll need a database and a schema:

CREATE DATABASE demo_db;
CREATE SCHEMA demo_schema;
USE DATABASE demo_db;
USE SCHEMA demo_schema;
Snowflake rename column example
Creating a Database and Schema - Snowflake rename column

This sets up a database named demo_db and a schema named demo_schema, and it then sets your current context to this database and schema.

Step 4: Create a Table and Add Columns

Now, let's create a sample table with some columns:

CREATE TABLE students_table(
    id INT PRIMARY KEY,
    name STRING,
    address STRING
);
Snowflake rename column example
Creating a Table and adding Columns - Snowflake rename column

Step 5: Insert Dummy Data

Populate your table with some dummy data for demonstration purposes:

INSERT INTO students_table(id, name, address) VALUES 
(1, 'Chaos', 'Banglore'),
(2, 'Genius', 'San Francisco');
Inserting dummy data - Snowflake rename column

Step 6: Use DESCRIBE to Verify the Current Table Column Name

Before making changes, it's always a good practice to verify the current state. Use the command:

DESCRIBE TABLE <table_name>;

Review the output to locate the column you wish to rename.
For this students_table example, do this:

DESCRIBE TABLE students_table;
Using DESCRIBE to verify the current table column name - Snowflake rename column

Step 7: Use ALTER TABLE...RENAME COLUMN Command to Snowflake Rename Column

With clarity on the current column name, proceed with renaming the column:

ALTER TABLE <table_name> RENAME COLUMN <old_column_name> TO <new_column_name>;
Snowflake rename column example

Replace the placeholders with appropriate names.

For this students_table example, do this:

ALTER TABLE students_table RENAME COLUMN NAME TO STUDENT_NAME;
Snowflake rename column example
Using ALTER TABLE...RENAME COLUMN Command to Snowflake Rename Column - Snowflake rename column

Step 8: Rename Column with Existence Check:

Before renaming, it's wise to check if the table already exists. This reduces the chance of errors:

For example, let's rename the address column to student_address in the Students_table. We'll use IF EXISTS to check whether the table exists or not, in order to avoid errors.

ALTER TABLE IF EXISTS students_table RENAME COLUMN address TO street_address;
Snowflake rename column example
Renaming Column with Existence Check - Snowflake rename column

Step 9: Verify the Column Name Change

As a best practice, always confirm your changes. Again, run the DESCRIBE TABLE command:

DESCRIBE TABLE students_table;
Snowflake rename column example
Verifying the column name change - Snowflake rename column

As you can see, the updated column names, STUDENT_NAME and STUDENT_ADDRESS, should be visible in the results. You'll notice that the NAME/ADDRESS columns have been updated to STUDENT_NAME/STUDENT_ADDRESS.

Common Issues and Troubleshooting Tips to Snowflake Rename Column

There are a few common issues that can come up when renaming Snowflake columns:

1) Permission Issues:

If you encounter an error related to permissions, double-check your Snowflake role. Make sure it has the necessary privileges to alter tables. If unsure, consult your Snowflake admin or refer to this article on Snowflake's roles and privileges.

2) Column Doesn't Exist:

This error indicates a discrepancy between the column name you provided and the actual column names in the table. Review the table's schema using DESCRIBE TABLE to confirm the correct column name.

Error Showing Non-Existent Column - Snowflake rename column

3) Duplicate Column Name Error

If you try renaming a column to a name that already exists within the table, Snowflake will trigger an error. Always ensure the new column name is unique within the table. If necessary, rename the existing column first.

Error showing Duplicate Column Name - Snowflake rename column

Additional Tips for Snowflake Rename Column

Beyond the basic column renaming process, here are some additional tips to enhance your Snowflake experience:

Renaming Multiple Columns at Once:

While Snowflake doesn’t natively support renaming multiple columns in a single command, you can chain multiple ALTER TABLE...RENAME COLUMN commands to achieve this.

Note: ALTER TABLE...RENAME COLUMN command does not accept more than one column at a time
ALTER TABLE <table_name> RENAME COLUMN <current_column_name1> TO <new_column_name1>;
ALTER TABLE <table_name> RENAME COLUMN <current_column_name2> TO <new_column_name2>;
ALTER TABLE <table_name> RENAME COLUMN <current_column_name3> TO <new_column_name3>;
ALTER TABLE <table_name> RENAME COLUMN <current_column_name4> TO <new_column_name4>;
Snowflake rename column example

Keeping Track of Changes:

Especially in a team environment, it's crucial to document changes. Use version control systems or change logs to track alterations to your database schema.

Making Use of Snowflake History:

Snowflake’s Query History feature allows you to view past queries. If you ever need to backtrack or understand past changes, this feature can be invaluable.

To do this, select the "Activity" option from the left navigation panel. Within the "Activity" section, click the "Query History" option.

Activity section and Query history dropdown menu - Snowflake rename column
Snowflake query history - Snowflake rename column

Further Reading

For more in-depth insights and advanced operations, refer to Snowflake's official documentation.

Save up to 30% on your Snowflake spend in a few minutes!

Enter your work email
Enter your work email

Conclusion

And that's a wrap! By now you should be a pro at renaming columns in Snowflake. Let's recap real quick. Being able to rename columns is clutch as your data's needs change over time. The original names you gave those columns back in the day might not make as much sense now. Renaming them keeps things nice and organized as schemas evolve.

In this article, we covered:

  • Checking the current column name with DESCRIBE so you know exactly what you're working with
  • Using the ALTER TABLE…RENAME COLUMN command
  • Verifying it worked with another DESCRIBE call.
  • Handling common errors like permissions issues or duplicate names
  • Common Issues and Troubleshooting Tips for managing Snowflake column renames
  • Additional Tips for Snowflake rename column

Renaming columns in Snowflake is straightforward but requires care to avoid introducing issues. Follow this guide carefully whenever you need to rename columns in a Snowflake table.


FAQs

Does renaming a Snowflake column change the data in any way?

No, renaming a column has no effect on the underlying data itself. Only the column name is changed.

How long does a column rename take to complete?

Column renames in Snowflake happen instantly. There is no rebuilding or downtime required.

Can you undo a column rename in Snowflake?

Yes, you can revert a rename by running the RENAME COLUMN command again and swapping the names back. Snowflake also provides Time Travel and cloning capabilities to restore or roll back to previous points in time.

Can I rename multiple columns in a single command in Snowflake?

No, you have to issue separate commands for each column you want to rename. For example:

ALTER TABLE <table_name> RENAME COLUMN <current_column_name1> TO <new_column_name1>;
ALTER TABLE <table_name> RENAME COLUMN <current_column_name2> TO <new_column_name2>;
Snowflake rename column example

Do I need special permissions to rename a column in Snowflake?

Yes, you would typically need the appropriate ALTER permissions on the table to rename a column.

Can I rename a column to a name that already exists in the table?

No, the new column name must be unique within the table.

What happens if you rename a column used in other objects like views?

If other objects like views reference the renamed column, you will need to modify those objects to reflect the new name.

Is there a way to rename columns in bulk or via import?

No, unfortunately, there is no bulk rename capability currently.