Snowflake External Table : Add new column

Question

I created a snowflake external table successfully and was able to load the data from s3. I want to add a column to this external table. In the Snowflake documentation they say that - "Alter an existing external table to add or remove columns using the following ALTER TABLE syntax: Add columns: ALTER TABLE … ADD COLUMN. Remove columns: ALTER TABLE … DROP COLUMN."

When I try adding a column to my external table, it gives me following error - "External table column TEST_COL must have a defining expression."

I am using following command to add the column - alter table EXTTABLE_TEST add column TEST_COL number;

Here is the link to Snowflake Documentation I am referring to - https://docs.snowflake.net/manuals/user-guide/tables-external-intro.html#adding-or-dropping-columns

Can someone please help me with this? Is it even possible to add/remove columns from external table. Any help is appreciated.

Thanks!

Answer 1

Try this syntax instead for an external table:

alter table EXTTABLE_TEST add column TEST_COL number as (value:TEST_COL::number);

More Docs on External Tables and examples: https://docs.snowflake.net/manuals/sql-reference/sql/create-external-table.html#usage-notes

If this could be made clearer in the documentation, we welcome any Snowflake official documentation suggestions. Near the bottom of the documentation is an option to "report doc issue" which can also be used for feedback.