ALTER TABLE¶

Syntax¶

ALTER TABLE [ IF EXISTS ] <name> RENAME TO <new_table_name>

ALTER TABLE [ IF EXISTS ] <name> SWAP WITH <target_table_name>

ALTER TABLE [ IF EXISTS ] <name> { clusteringAction | tableColumnAction | constraintAction  }

ALTER TABLE [ IF EXISTS ] <name> dataMetricFunctionAction

ALTER TABLE [ IF EXISTS ] <name> dataGovnPolicyTagAction

ALTER TABLE [ IF EXISTS ] <name> extTableColumnAction

ALTER TABLE [ IF EXISTS ] <name> searchOptimizationAction

ALTER TABLE [ IF EXISTS ] <name> SET
  [ DATA_RETENTION_TIME_IN_DAYS = <integer> ]
  [ MAX_DATA_EXTENSION_TIME_IN_DAYS = <integer> ]
  [ CHANGE_TRACKING = { TRUE | FALSE  } ]
  [ DEFAULT_DDL_COLLATION = '<collation_specification>' ]
  [ ENABLE_SCHEMA_EVOLUTION = { TRUE | FALSE } ]
  [ COMMENT = '<string_literal>' ]

ALTER TABLE [ IF EXISTS ] <name> UNSET {
                                       DATA_RETENTION_TIME_IN_DAYS         |
                                       MAX_DATA_EXTENSION_TIME_IN_DAYS     |
                                       CHANGE_TRACKING                     |
                                       DEFAULT_DDL_COLLATION               |
                                       ENABLE_SCHEMA_EVOLUTION             |
                                       COMMENT                             |
                                       }
                                       [ , ... ]

Where:

clusteringAction ::=
  {
     CLUSTER BY ( <expr> [ , <expr> , ... ] )
     /* RECLUSTER is deprecated */
   | RECLUSTER [ MAX_SIZE = <budget_in_bytes> ] [ WHERE <condition> ]
     /* { SUSPEND | RESUME } RECLUSTER is valid action */
   | { SUSPEND | RESUME } RECLUSTER
   | DROP CLUSTERING KEY
  }

Copy

tableColumnAction ::=
  {
     ADD [ COLUMN ] [ IF NOT EXISTS ] <col_name> <col_type>
        [
           {
              DEFAULT <default_value>
              | { AUTOINCREMENT | IDENTITY }
                 /* AUTOINCREMENT (or IDENTITY) is supported only for           */
                 /* columns with numeric data types (NUMBER, INT, FLOAT, etc.). */
                 /* Also, if the table is not empty (i.e. if the table contains */
                 /* any rows), only DEFAULT can be altered.                     */
                 [
                    {
                       ( <start_num> , <step_num> )
                       | START <num> INCREMENT <num>
                    }
                 ]
                 [  { ORDER | NOORDER } ]
           }
        ]
        [ inlineConstraint ]
        [ COLLATE '<collation_specification>' ]

   | RENAME COLUMN <col_name> TO <new_col_name>

   | ALTER | MODIFY [ ( ]
                            [ COLUMN ] <col1_name> DROP DEFAULT
                          , [ COLUMN ] <col1_name> SET DEFAULT <seq_name>.NEXTVAL
                          , [ COLUMN ] <col1_name> { [ SET ] NOT NULL | DROP NOT NULL }
                          , [ COLUMN ] <col1_name> [ [ SET DATA ] TYPE ] <type>
                          , [ COLUMN ] <col1_name> COMMENT '<string>'
                          , [ COLUMN ] <col1_name> UNSET COMMENT
                        [ , [ COLUMN ] <col2_name> ... ]
                        [ , ... ]
                    [ ) ]

   | DROP [ COLUMN ] [ IF EXISTS ] <col1_name> [, <col2_name> ... ]
  }

  inlineConstraint ::=
    [ NOT NULL ]
    [ CONSTRAINT <constraint_name> ]
    { UNIQUE | PRIMARY KEY | { [ FOREIGN KEY ] REFERENCES <ref_table_name> [ ( <ref_col_name> ) ] } }
    [ <constraint_properties> ]

Copy

For detailed syntax and examples for altering columns, see ALTER TABLE … ALTER COLUMN. .

For detailed syntax and examples for creating/altering inline constraints, see CREATE | ALTER TABLE … CONSTRAINT.

dataMetricFunctionAction ::=

    { ADD | DROP } DATA METRIC FUNCTION
      <metric_name> ON ( <col_name> [ , ... ] )
      [ , <metric_name_2> ON ( <col_name> [ , ... ] ) ]
      [ , ... ]
  |
    SET DATA_METRIC_SCHEDULE = {
        '<num> MINUTE'
      | 'USING CRON <expr> <time_zone>'
      | 'TRIGGER_ON_CHANGES'
    }

  | UNSET DATA_METRIC_SCHEDULE

Copy

dataGovnPolicyTagAction ::=
  {
      SET TAG <tag_name> = '<tag_value>' [ , <tag_name> = '<tag_value>' ... ]
    | UNSET TAG <tag_name> [ , <tag_name> ... ]
  }
  |
  {
      ADD ROW ACCESS POLICY <policy_name> ON ( <col_name> [ , ... ] )
    | DROP ROW ACCESS POLICY <policy_name>
    | DROP ROW ACCESS POLICY <policy_name> ,
        ADD ROW ACCESS POLICY <policy_name> ON ( <col_name> [ , ... ] )
    | DROP ALL ROW ACCESS POLICIES
  }
  |
  {
      SET AGGREGATION POLICY <policy_name> [ FORCE ]
      | UNSET AGGREGATION POLICY
  }
  |
  ADD [ COLUMN ] [ IF NOT EXISTS ] <col_name> <col_type>
    [ [ WITH ] MASKING POLICY <policy_name>
          [ USING ( <col1_name> , <cond_col_1> , ... ) ] ]
    [ [ WITH ] PROJECTION POLICY <policy_name> ]
    [ [ WITH ] TAG ( <tag_name> = '<tag_value>'
          [ , <tag_name> = '<tag_value>' , ... ] ) ]
  |
  {
    { ALTER | MODIFY } [ COLUMN ] <col1_name>
        SET MASKING POLICY <policy_name>
          [ USING ( <col1_name> , <cond_col_1> , ... ) ] [ FORCE ]
      | UNSET MASKING POLICY
  }
  |
  {
    { ALTER | MODIFY } [ COLUMN ] <col1_name>
        SET PROJECTION POLICY <policy_name>
          [ FORCE ]
      | UNSET PROJECTION POLICY
  }
  |
  { ALTER | MODIFY } [ COLUMN ] <col1_name> SET TAG
      <tag_name> = '<tag_value>' [ , <tag_name> = '<tag_value>' ... ]
      , [ COLUMN ] <col2_name> SET TAG
          <tag_name> = '<tag_value>' [ , <tag_name> = '<tag_value>' ... ]
  |
  { ALTER | MODIFY } [ COLUMN ] <col1_name> UNSET TAG <tag_name> [ , <tag_name> ... ]
                   , [ COLUMN ] <col2_name> UNSET TAG <tag_name> [ , <tag_name> ... ]

Copy

extTableColumnAction ::=
  {
     ADD [ COLUMN ] [ IF NOT EXISTS ] <col_name> <col_type> AS ( <expr> )

   | RENAME COLUMN <col_name> TO <new_col_name>

   | DROP [ COLUMN ] [ IF EXISTS ] <col1_name> [, <col2_name> ... ]
  }

Copy

constraintAction ::=
  {
     ADD outoflineConstraint
   | RENAME CONSTRAINT <constraint_name> TO <new_constraint_name>
   | { ALTER | MODIFY } { CONSTRAINT <constraint_name> | PRIMARY KEY | UNIQUE | FOREIGN KEY } ( <col_name> [ , ... ] )
                         [ [ NOT ] ENFORCED ] [ VALIDATE | NOVALIDATE ] [ RELY | NORELY ]
   | DROP { CONSTRAINT <constraint_name> | PRIMARY KEY | UNIQUE | FOREIGN KEY } ( <col_name> [ , ... ] )
                         [ CASCADE | RESTRICT ]
  }

  outoflineConstraint ::=
    [ CONSTRAINT <constraint_name> ]
    {
       UNIQUE [ ( <col_name> [ , <col_name> , ... ] ) ]
     | PRIMARY KEY [ ( <col_name> [ , <col_name> , ... ] ) ]
     | [ FOREIGN KEY ] [ ( <col_name> [ , <col_name> , ... ] ) ]
                          REFERENCES <ref_table_name> [ ( <ref_col_name> [ , <ref_col_name> , ... ] ) ]
    }
    [ <constraint_properties> ]

Copy

For detailed syntax and examples for creating/altering out-of-line constraints, see CREATE | ALTER TABLE … CONSTRAINT.

searchOptimizationAction ::=
  {
     ADD SEARCH OPTIMIZATION [
       ON <search_method_with_target> [ , <search_method_with_target> ... ]
     ]

   | DROP SEARCH OPTIMIZATION [
       ON { <search_method_with_target> | <column_name> | <expression_id> }
          [ , ... ]
     ]

  }

Copy

For details, see Search Optimization Actions (searchOptimizationAction).

Parameters¶

name

Identifier for the table to alter. If the identifier contains spaces or special characters, the entire string must be enclosed in double quotes. Identifiers enclosed in double quotes are also case-sensitive.

RENAME TO new_table_name

Renames the specified table with a new identifier that is not currently used by any other tables in the schema.

For more details about table identifiers, see Identifier requirements.

You can move the object to a different database and/or schema while optionally renaming the object. To do so, specify a qualified new_name value that includes the new database and/or schema name in the form db_name.schema_name.object_name or schema_name.object_name, respectively.

Note

• The destination database and/or schema must already exist. In addition, an object with the same name cannot already exist in the new location; otherwise, the statement returns an error.

• Moving an object to a managed access schema is prohibited unless the object owner (that is, the role that has the OWNERSHIP privilege on the object) also owns the target schema.

When an object (table, column, etc.) is renamed, other objects that reference it must be updated with the new name.

SWAP WITH target_table_name

Swap renames two tables in a single transaction.

Note that swapping a permanent or transient table with a temporary table, which persists only for the duration of the user session in which it was created, is not allowed. This restriction prevents a naming conflict that could occur when a temporary table is swapped with a permanent or transient table, and an existing permanent or transient table has the same name as the temporary table. To swap a permanent or transient table with a temporary table, use three ALTER TABLE ... RENAME TO statements: Rename table a to c, b to a, and then c to b.

SET ...

Specifies one or more properties/parameters to set for the table (separated by blank spaces, commas, or new lines):

DATA_RETENTION_TIME_IN_DAYS = integer

Object-level parameter that modifies the retention period for the table for Time Travel. For more details, see Understanding & Using Time Travel and Working with Temporary and Transient Tables.

For a detailed description of this parameter, as well as more information about object parameters, see Parameters.

Values:

• Standard Edition: 0 or 1

• Enterprise Edition:

  • 0 to 90 for permanent tables

  • 0 or 1 for temporary and transient tables

Note

A value of 0 effectively disables Time Travel for the table.

MAX_DATA_EXTENSION_TIME_IN_DAYS = integer

Object parameter that specifies the maximum number of days for which Snowflake can extend the data retention period for the table to prevent streams on the table from becoming stale.

For a detailed description of this parameter, see MAX_DATA_EXTENSION_TIME_IN_DAYS.

CHANGE_TRACKING = TRUE | FALSE

Specifies to enable or disable change tracking on the table.

• TRUE enables change tracking on the table. This option adds several hidden columns to the source table and begins storing change tracking metadata in the columns. These columns consume a small amount of storage.

  The change tracking metadata can be queried using the CHANGES clause for SELECT statements, or by creating and querying one or more streams on the table.

• FALSE disables change tracking on the table. Associated hidden columns are dropped from the table.

DEFAULT_DDL_COLLATION = 'collation_specification'

Specifies a default collation specification for any new columns added to the table.

Setting the parameter does not change the collation specification for any existing columns.

For more details about the parameter, see DEFAULT_DDL_COLLATION.

ENABLE_SCHEMA_EVOLUTION = { TRUE | FALSE }

Enables or disables automatic changes to the table schema from data loaded into the table from source files, including:

• Added columns.

  By default, schema evolution is limited to a maximum of 10 added columns per load operation. To request more than 10 added columns per load operation, contact Snowflake Support.

• The NOT NULL constraint can be dropped from any number of columns missing in new data files.

Setting it to TRUE enables automatic table schema evolution. The default FALSE disables automatic table schema evolution.

Note

Loading data from files evolves the table columns when all of the following are true:

• The COPY INTO <table> statement includes the MATCH_BY_COLUMN_NAME option.

• The role used to load the data has the EVOLVE SCHEMA or OWNERSHIP privilege on the table.

Additionally, for schema evolution with CSV, when used with MATCH_BY_COLUMN_NAME and PARSE_HEADER, ERROR_ON_COLUMN_COUNT_MISMATCH must be set to false.

COMMENT = 'string_literal'

Adds a comment or overwrites the existing comment for the table.

UNSET ...

Specifies one or more properties/parameters to unset for the table, which resets them back to their defaults:

• DATA_RETENTION_TIME_IN_DAYS

• MAX_DATA_EXTENSION_TIME_IN_DAYS

• CHANGE_TRACKING

• DEFAULT_DDL_COLLATION

• ENABLE_SCHEMA_EVOLUTION

• COMMENT

Clustering Actions (clusteringAction)¶

CLUSTER BY ( expr [ , expr , ... ] )

Specifies (or modifies) one or more table columns or column expressions as the clustering key for the table. These are the columns/expressions for which clustering is maintained by Automatic Clustering.

Important

Clustering keys are not intended or recommended for all tables; they typically benefit very large (i.e. multi-terabyte) tables.

Before you specify a clustering key for a table, please see Understanding Snowflake Table Structures.

RECLUSTER ...

Deprecated

Performs manual, incremental reclustering of a table that has a clustering key defined:

MAX_SIZE = budget_in_bytes

Deprecated — use a larger warehouse to achieve more effective manual reclustering

Specifies the upper-limit on the amount of data (in bytes) in the table to recluster.

WHERE condition

Specifies a condition or range on which to recluster data in the table.

Note

Only roles with the OWNERSHIP or INSERT privilege on a table can recluster the table.

SUSPEND | RESUME RECLUSTER

Enables or disables Automatic Clustering for the table.

DROP CLUSTERING KEY

Drops the clustering key for the table.

For more information about clustering keys and reclustering, see Understanding Snowflake Table Structures.

Table Column Actions (tableColumnAction)¶

ADD [ COLUMN ] [ IF NOT EXISTS ] col_name col_data_type . [ DEFAULT default_value | AUTOINCREMENT ... ] . [ inlineConstraint ] [ COLLATE 'collation_specification' ] . [ [ WITH ] MASKING POLICY policy_name ] . [ [ WITH ] PROJECTION POLICY policy_name ] . [ [ WITH ] TAG ( tag_name = 'tag_value' [ , tag_name = 'tag_value' , ... ] ) ] [ , ...]

Adds a new column. You can specify a default value, an inline constraint, a collation specification, a masking policy, and/or one or more tags.

A default value for a column that you are adding must be a literal value; it cannot be an expression or a value returned by a function. For example, the following command returns an expected error:

ALTER TABLE t1 ADD COLUMN c5 VARCHAR DEFAULT 12345::VARCHAR;

Copy

002263 (22000): SQL compilation error:
Invalid column default expression [CAST(12345 AS VARCHAR(16777216))]

When you first create a table, you can use expressions as default values, but not when you add columns.

The default value for a column must match the data type of the column. An attempt to set a default value with a non-matching data type fails with an error. For example:

ALTER TABLE t1 ADD COLUMN c6 DATE DEFAULT '20230101';

Copy

002023 (22000): SQL compilation error:
Expression type does not match column data type, expecting DATE but got VARCHAR(8) for column C6

For additional details about table column actions, see:

• CREATE TABLE

• CREATE | ALTER TABLE … CONSTRAINT

• CREATE MASKING POLICY

• CREATE TAG

ADD COLUMN operations can be performed on multiple columns in the same command.

If you are not sure if the column already exists, you can specify IF NOT EXISTS when adding the column. If the column already exists, ADD COLUMN has no effect on the existing column and does not result in an error.

Note

You cannot specify IF NOT EXISTS if you are also specifying any of the following for the new column:

• DEFAULT, AUTOINCREMENT, or IDENTITY

• UNIQUE, PRIMARY KEY, or FOREIGN KEY

RENAME COLUMN col_name to new_col_name

Renames the specified column to a new name that is not currently used for any other columns in the table.

You cannot rename a column that is part of a clustering key.

When an object (table, column, etc.) is renamed, other objects that reference it must be updated with the new name.

DROP COLUMN [ IF EXISTS ] col_name [ CASCADE | RESTRICT ]

Removes the specified column from the table.

If you are not sure if the column already exists, you can specify IF EXISTS when dropping the column. If the column does not exist, DROP COLUMN has no effect and does not result in an error.

Dropping a column is a metadata-only operation. It does not immediately re-write the micro-partition(s) and therefore does not immediately free up the space used by the column. Typically, the space within an individual micro-partition is freed the next time that the micro-partition is re-written, which is typically when a write is done either due to DML (INSERT, UPDATE, DELETE) or re-clustering.

Data Metric Function Actions (dataMetricFunctionAction)¶

{ ADD | DROP } DATA METRIC FUNCTION metric_name

Identifier of the data metric function to add to the table or view or drop from the table or view.

ON ( col_name [ , ... ] )

The table or view columns on which to associate the data metric function. The data types of the columns must match the data types of the columns specified in the data metric function definition.

[ , metric_name_2 ON ( col_name [ , ... ] ) [ , ... ] ]

Additional data metric functions to add to the table. Use a comma to separate each data metric function and its specified columns.

DATA_METRIC_SCHEDULE ...

Specifies the schedule to run the data metric function periodically.

'num MINUTE'

Specifies an interval (in minutes) of wait time inserted between runs of the data metric function. Accepts positive integers only.

Also supports num M syntax.

For data metric functions, use one of the following values: 5, 15, 30, 60, 720, or 1440.

'USING CRON expr time_zone'

Specifies a cron expression and time zone for periodically running the data metric function. Supports a subset of standard cron utility syntax.

For a list of time zones, see the list of tz database time zones.

The cron expression consists of the following fields, and the periodic interval must be at least 5 minutes:

# __________ minute (0-59)
# | ________ hour (0-23)
# | | ______ day of month (1-31, or L)
# | | | ____ month (1-12, JAN-DEC)
# | | | | _ day of week (0-6, SUN-SAT, or L)
# | | | | |
# | | | | |
  * * * * *

Copy

The following special characters are supported:

*

Wildcard. Specifies any occurrence of the field.

L

Stands for “last”. When used in the day-of-week field, it allows you to specify constructs such as “the last Friday” (“5L”) of a given month. In the day-of-month field, it specifies the last day of the month.

/{n}

Indicates the nth instance of a given unit of time. Each quanta of time is computed independently. For example, if 4/3 is specified in the month field, then the data metric function is scheduled for April, July and October (i.e. every 3 months, starting with the 4th month of the year). The same schedule is maintained in subsequent years. That is, the data metric function is not scheduled to run in January (3 months after the October run).

Note

• The cron expression currently evaluates against the specified time zone only. Altering the TIMEZONE parameter value for the account (or setting the value at the user or session level) does not change the time zone for the data metric function.

• The cron expression defines all valid run times for the data metric function. Snowflake attempts to run a data metric function based on this schedule; however, any valid run time is skipped if a previous run has not completed before the next valid run time starts.

• When both a specific day of month and day of week are included in the cron expression, then the data metric function is scheduled on days satisfying either the day of month or day of week. For example, DATA_METRIC_SCHEDULE = 'USING CRON 0 0 10-20 * TUE,THU UTC' schedules a data metric function at 0AM on any 10th to 20th day of the month and also on any Tuesday or Thursday outside of those dates.

• The shortest granularity of time in cron is minutes.

'TRIGGER_ON_CHANGES'

Specifies that the DMF runs when a DML operation modifies the table, such as inserting a new row or deleting a row.

Changes to the table as a result of reclustering do not trigger the DMF to run.

For details about the access control requirements for these actions, see DMF privileges.

External Table Column Actions (extTableColumnAction)¶

For all other external table modifications, see ALTER EXTERNAL TABLE.

ADD [ COLUMN ] [ IF NOT EXISTS ] <col_name> <col_type> AS ( <expr> ) [, ...]

Adds a new column to the external table.

If you are not sure if the column already exists, you can specify IF NOT EXISTS when adding the column. If the column already exists, ADD COLUMN has no effect on the existing column and does not result in an error.

This operation can be performed on multiple columns in the same command.

col_name

String that specifies the column identifier (i.e. name). All the requirements for table identifiers also apply to column identifiers.

For more details, see Identifier requirements.

col_type

String (constant) that specifies the data type for the column. The data type must match the result of expr for the column.

For details about the data types that can be specified for table columns, see SQL Data Types Reference.

expr

String that specifies the expression for the column. When queried, the column returns results derived from this expression.

External table columns are virtual columns, which are defined using an explicit expression. Add virtual columns as expressions using the VALUE column and/or the METADATA$FILENAME pseudocolumn:

VALUE:

A VARIANT type column that represents a single row in the external file.

CSV:

The VALUE column structures each row as an object with elements identified by column position (i.e. {c1: <column_1_value>, c2: <column_2_value>, c3: <column_1_value> ...}).

For example, add a VARCHAR column named mycol that references the first column in the staged CSV files:

mycol varchar as (value:c1::varchar)

Copy

Semi-structured data:

Enclose element names and values in double-quotes. Traverse the path in the VALUE column using dot notation.

For example, suppose the following represents a single row of semi-structured data in a staged file:

{ "a":"1", "b": { "c":"2", "d":"3" } }

Copy

Add a VARCHAR column named mycol that references the nested repeating c element in the staged file:

mycol varchar as (value:"b"."c"::varchar)

Copy

METADATA$FILENAME:

A pseudocolumn that identifies the name of each staged data file included in the external table, including its path in the stage.

RENAME COLUMN col_name to new_col_name

Renames the specified column to a new name that is not currently used for any other columns in the external table.

DROP COLUMN [ IF EXISTS ] col_name

Removes the specified column from the external table.

If you are not sure if the column already exists, you can specify IF EXISTS when dropping the column. If the column does not exist, DROP COLUMN has no effect and does not result in an error.

Constraint Actions (constraintAction)¶

ADD CONSTRAINT

Adds an out-of-line integrity constraint to one or more columns in the table. To add an inline constraint (for a column), see Column Actions (in this topic).

RENAME CONSTRAINT constraint_name TO new_constraint_name

Renames the specified constraint.

ALTER | MODIFY CONSTRAINT ...

Alters the properties for the specified constraint.

DROP CONSTRAINT constraint_name | PRIMARY KEY | UNIQUE | FOREIGN KEY ( col_name [ , ... ] ) [ CASCADE | RESTRICT ]

Drops the specified constraint for the specified column or set of columns.

For detailed syntax and examples for adding or altering constraints, see CREATE | ALTER TABLE … CONSTRAINT.

Data Governance policy and tag actions (dataGovnPolicyTagAction)¶

TAG tag_name = 'tag_value' [ , tag_name = 'tag_value' , ... ]

Specifies the tag name and the tag string value.

The tag value is always a string, and the maximum number of characters for the tag value is 256.

For information about specifying tags in a statement, see Tag quotas for objects and columns.

policy_name

Identifier for the policy; must be unique for your schema.

The following clauses apply to all table kinds that support row access policies, such as but not limited to tables, views, and event tables. To simplify, the clauses just refer to “table.”

ADD ROW ACCESS POLICY policy_name ON (col_name [ , ... ])

Adds a row access policy to the table.

At least one column name must be specified. Additional columns can be specified with a comma separating each column name. Use this expression to add a row access policy to both an event table and an external table.

DROP ROW ACCESS POLICY policy_name

Drops a row access policy from the table.

Use this clause to drop the policy from the table.

DROP ROW ACCESS POLICY policy_name, ADD ROW ACCESS POLICY policy_name ON ( col_name [ , ... ] )

Drops the row access policy that is set on the table and adds a row access policy to the same table in a single SQL statement.

DROP ALL ROW ACCESS POLICIES

Drops all row access policy associations from the table.

This expression is helpful when a row access policy is dropped from a schema before dropping the policy from an event table. Use this expression to drop row access policy associations from the table.

SET AGGREGATION POLICY policy_name [ FORCE ]

Assigns an aggregation policy to the table. Use the optional FORCE parameter to atomically replace an existing aggregation policy with the new aggregation policy.

UNSET AGGREGATION POLICY

Detaches an aggregation policy from the table.

{ ALTER | MODIFY } [ COLUMN ] ...

USING ( col_name , cond_col_1 ... )

Specifies the arguments to pass into the conditional masking policy SQL expression.

The first column in the list specifies the column for the policy conditions to mask or tokenize the data and must match the column to which the masking policy is set.

The additional columns specify the columns to evaluate to determine whether to mask or tokenize the data in each row of the query result when a query is made on the first column.

If the USING clause is omitted, Snowflake treats the conditional masking policy as a normal masking policy.

FORCE

Replaces a masking or projection policy that is currently set on a column with a different policy in a single statement.

Note that using the FORCE keyword with a masking policy requires the data type of the policy in the ALTER TABLE statement (i.e. STRING) to match the data type of the masking policy currently set on the column (i.e. STRING).

If a masking policy is not currently set on the column, specifying this keyword has no effect.

For details, see: Replace a masking policy on a column or Replace a projection policy.

Search Optimization Actions (searchOptimizationAction)¶

ADD SEARCH OPTIMIZATION

Adds search optimization for the entire table or, if you specify the optional ON clause, for specific columns.

Note:

• Search optimization can be expensive to maintain, especially if the data in the table changes frequently. For more information, see Search Optimization Cost Estimation and Management.

• If you try to add search optimization on a materialized view, Snowflake returns an error message.

ON search_method_with_target [, search_method_with_target ... ]

Specifies that you want to configure search optimization for specific columns or VARIANT fields (rather than the entire table).

For search_method_with_target, use an expression with the following syntax:

<search_method>(<target> [, ...])

Copy

Where:

• search_method specifies one of the following methods that optimizes queries for a particular type of predicate:

  Search Method	Description
  EQUALITY	Equality and IN predicates.
  SUBSTRING	Predicates that match substrings and regular expressions (e.g. [ NOT ] LIKE, [ NOT ] ILIKE, [ NOT ] RLIKE, REGEXP_LIKE, etc.)
  GEO	Predicates that use GEOGRAPHY types.

• target specifies the column, VARIANT field, or an asterisk (*).

  Depending on the value of search_method, you can specify a column or VARIANT field of one of the following types:

  Search Method	Supported Targets
  EQUALITY	Columns of numerical, string, binary, and VARIANT data types, including paths to fields in VARIANTs. To specify a VARIANT field, use dot or bracket notation (e.g. my_column:my_field_name.my_nested_field_name or my_column['my_field_name']['my_nested_field_name']). You may also use a colon-delimited path to the field (e.g. my_column:my_field_name:my_nested_field_name). When you specify a VARIANT field, the configuration applies to all nested fields under that field. For example, if you specify ON EQUALITY(src:a.b): This configuration can improve queries on src:a.b and on any nested fields (e.g. src:a.b.c, src:a.b.c.d, etc.). This configuration does not affect queries that do not use the src:a.b prefix (e.g. src:a, src:z, etc.).
  SUBSTRING	Columns of string or VARIANT data types, including paths to fields in VARIANTs. Specify paths to fields as described above under EQUALITY; searches on nested fields are improved in the same way.
  GEO	Columns of the GEOGRAPHY data type.

  To specify all applicable columns in the table as targets, use an asterisk (*).

  Note that you cannot specify both an asterisk and specific column names for a given search method. However, you can specify an asterisk in different search methods.

  For example, you can specify the following expressions:

  -- Allowed
  ON SUBSTRING(*)
  ON EQUALITY(*), SUBSTRING(*), GEO(*)
  

  Copy

  You cannot specify the following expressions:

  -- Not allowed
  ON EQUALITY(*, c1)
  ON EQUALITY(c1, *)
  ON EQUALITY(v1:path, *)
  ON EQUALITY(c1), EQUALITY(*)
  

  Copy

To specify more than one search method on a target, use a comma to separate each subsequent method and target:

ALTER TABLE t1 ADD SEARCH OPTIMIZATION ON EQUALITY(c1), EQUALITY(c2, c3);

Copy

If you run the ALTER TABLE … ADD SEARCH OPTIMIZATION ON … command multiple times on the same table, each subsequent command adds to the existing configuration for the table. For example, suppose that you run the following commands:

ALTER TABLE t1 ADD SEARCH OPTIMIZATION ON EQUALITY(c1, c2);
ALTER TABLE t1 ADD SEARCH OPTIMIZATION ON EQUALITY(c3, c4);

Copy

This adds equality predicates for the columns c1, c2, c3, and c4 to the configuration for the table. This is equivalent to running the command:

ALTER TABLE t1 ADD SEARCH OPTIMIZATION ON EQUALITY(c1, c2, c3, c4);

Copy

For examples, see Enabling Search Optimization for Specific Columns.

DROP SEARCH OPTIMIZATION

Removes search optimization for the entire table or, if you specify the optional ON clause, from specific columns.

Note:

• If a table has the search optimization property, then dropping the table and undropping it preserves the search optimization property.

• Removing the search optimization property from a table and then adding it back incurs the same cost as adding it the first time.

ON search_method_with_target | column_name | expression_id [, ... ]

Specifies that you want to drop the search optimization configuration for specific columns or VARIANT fields (rather than dropping search optimization for the entire table).

To identify the column configuration to drop, specify one of the following:

• For search_method_with_target, specify a method for optimizing queries for one or more specific targets, which can be columns or VARIANT fields. Use the syntax described earlier.

• For column_name, specify the name of the column configured for search optimization. Specifying the column name drops all expressions for that column, including expressions that use VARIANT fields in the column.

• For expression_id, specify the ID for an expression listed in the output of the DESCRIBE SEARCH OPTIMIZATION command.

To specify more than one of these, use a comma between items.

You can specify any combination of search methods with targets, column names, and expression IDs.

For examples, see Dropping Search Optimization for Specific Columns.

Usage Notes: General¶

• Changes to a table are not automatically propagated to views created on that table. For example, if you drop a column in a table, and a view is defined to include that column, the view becomes invalid; the view is not adjusted to remove the column.

• Dropping a column does not immediately free up the column’s storage space.

  • The space in each micro-partition is not reclaimed until that micro-partition is re-written. Write operations (insert, update, delete, etc.) on 1 or more rows in that micro-partition cause the micro-partition to be re-written. If you want to force space to be reclaimed, you can follow these steps:

    1. Use a CREATE TABLE AS SELECT (CTAS) statement to create a new table that contains only the columns of the old table you want to keep.

    2. Set the DATA_RETENTION_TIME_IN_DAYS parameter to 0 for the old table (optional).

    3. Drop the old table.

  • If the table is protected by the Time Travel feature, the space used by the Time Travel storage is not reclaimed until the Time Travel retention period expires.

• If a new column with a default value is added to a table with existing rows, all of the existing rows are populated with the default value.

• Adding a new column with a default value containing a function is not currently supported. The following error is returned:

  Invalid column default expression (expr)

• To alter a table, you must be using a role that has ownership privilege on the table.

• To add clustering to a table, you must also have USAGE or OWNERSHIP privileges on the schema and database that contain the table.

• For masking policies:

  • The USING clause and the FORCE keyword are both optional; neither are required to set a masking policy on a column. The USING clause and the FORCE keyword can be used separately or together. For details, see:

    • Apply a conditional masking policy on a column

    • Replace a masking policy on a column

  • A single masking policy that uses conditional columns can be applied to multiple tables provided that the column structure of the table matches the columns specified in the policy.

  • When modifying one or more table columns with a masking policy or the table itself with a row access policy, use the POLICY_CONTEXT function to simulate a query on the column(s) protected by a masking policy and the table protected by a row access policy.

• For row access policies:

  • Snowflake supports adding and dropping row access policies in a single SQL statement.

    For example, to replace a row access policy that is already set on a table with a different policy, drop the row access policy first and then add the new row access policy.

  • For a given resource (i.e. table or view), to ADD or DROP a row access policy you must have either the APPLY ROW ACCESS POLICY privilege on the schema, or the OWNERSHIP privilege on the resource and the APPLY privilege on the row access policy resource.

  • A table or view can only be protected by one row access policy at a time. Adding a policy fails if the policy body refers to a table or view column that is protected by a row access policy or the column protected by a masking policy.

    Similarly, adding a masking policy to a table column fails if the masking policy body refers to a table that is protected by a row access policy or another masking policy.

  • Row access policies cannot be applied to system views or table functions.

  • Similar to other DROP <object> operations, Snowflake returns an error if attempting to drop a row access policy from a resource that does not have a row access policy added to it.

  • If an object has both a row access policy and one or more masking policies, the row access policy is evaluated first.

• If you create a foreign key, then the columns in the REFERENCES clause must be listed in the same order as the columns were listed for the primary key. For example:

  create table parent ... constraint primary_key_1 primary key (c_1, c_2) ...
  create table child  ... constraint foreign_key_1 foreign key (...) REFERENCES parent (c_1, c_2) ...
  

  Copy

  In both cases, the order of the columns is c_1, c_2. If the order of the columns in the foreign key had been different (e.g. c_2, c_1), then the attempt to create the foreign key would have failed.

• Regarding metadata:

  Attention

  Customers should ensure that no personal data (other than for a User object), sensitive data, export-controlled data, or other regulated data is entered as metadata when using the Snowflake service. For more information, see Metadata Fields in Snowflake.

• ALTER TABLE … CHANGE_TRACKING = TRUE

  • When a table is altered to enable change tracking, the table is locked for the duration of the operation. Locks can cause latency with some associated DDL/DML operations. For more information, refer to Resource Locking.

• Indexes in hybrid tables:

  • When you use the ALTER TABLE command to add or drop a unique or foreign-key constraint in a hybrid table, the corresponding index will also be created or dropped. For more information on indexes in a hybrid table, see CREATE INDEX.

  • Foreign-key constraints are supported only across hybrid tables that are storaged in the same database. The ability to move a hybrid table from one database to another is not supported. The primary key, unique, and foreign key constraints defined on hybrid tables have their RELY field marked as TRUE.

  • A column that is used by an index cannot be dropped before the corresponding index is dropped.

Usage Notes: Data metric functions¶

Add a DMF to a table:

Prior to adding a data metric function to a table, you must:

• Set the schedule for the data metric function to run. For details, see DATA_METRIC_SCHEDULE.

• Configure the event table to store the results of calling the data metric function. For details, see View the DMF results.

• Ensure that the table is view is not granted to a share because you cannot set a data metric function on a shared table or view.

Additionally:

• You can add a data metric function to a table, view, or materialized view. You cannot set a data metric function on any other kind of table, such as a dynamic table or external table.

• When you specify a column, Snowflake uses the ordinal position. If you rename a column after adding a data metric function to the table or view, the association of the data metric function to the column remains valid.

• Only one data metric function of its kind can be added to a column. For example, a NULL_COUNT data metric function cannot be added to a single column twice.

• If you drop a column after adding a data metric function that references the column, Snowflake cannot evaluate the data metric function.

• Referencing a virtual column is not supported.

Drop a DMF from a table:

• Drop the data metric function from the table before using the DROP FUNCTION command to remove the data metric function from the system.

• You can use the DATA_METRIC_FUNCTION_REFERENCES function to identify the table and view objects that have a data metric function set on them.

Schedule a DMF

It takes ten minutes for the schedule to become effective once the schedule is set.

Examples¶

The following sections provide examples of using the ALTER COLUMN command:

• Renaming a Table

• Swapping Tables

• Adding Columns

• Renaming Columns

• Dropping Columns

• Adding, Renaming, and Dropping Columns in an External Table

• Changing the Order of Clustering Keys

• Adding and Dropping Row Access Policies

CREATE OR REPLACE TABLE t1(a1 number);

SHOW TABLES LIKE 't1';

+-------------------------------+------+---------------+-------------+-------+---------+------------+------+-------+--------+----------------+-----------------+-------------+-------------------------+-----------------+----------+--------+
| created_on                    | name | database_name | schema_name | kind  | comment | cluster_by | rows | bytes | owner  | retention_time | change_tracking | is_external | enable_schema_evolution | owner_role_type | is_event | budget |
|-------------------------------+------+---------------+-------------+-------+---------+------------+------+-------+--------+----------------+-----------------+-------------+-------------------------+-----------------+----------+--------|
| 2023-10-19 10:37:04.858 -0700 | T1   | TESTDB        | MY_SCHEMA   | TABLE |         |            |    0 |     0 | PUBLIC | 1              | OFF             | N           | N                       | ROLE            | N        | NULL   |
+-------------------------------+------+---------------+-------------+-------+---------+------------+------+-------+--------+----------------+-----------------+-------------+-------------------------+-----------------+----------+--------+

ALTER TABLE t1 RENAME TO tt1;

SHOW TABLES LIKE 'tt1';

+-------------------------------+------+---------------+-------------+-------+---------+------------+------+-------+--------+----------------+-----------------+-------------+-------------------------+-----------------+----------+--------+
| created_on                    | name | database_name | schema_name | kind  | comment | cluster_by | rows | bytes | owner  | retention_time | change_tracking | is_external | enable_schema_evolution | owner_role_type | is_event | budget |
|-------------------------------+------+---------------+-------------+-------+---------+------------+------+-------+--------+----------------+-----------------+-------------+-------------------------+-----------------+----------+--------|
| 2023-10-19 10:37:04.858 -0700 | TT1  | TESTDB        | MY_SCHEMA   | TABLE |         |            |    0 |     0 | PUBLIC | 1              | OFF             | N           | N                       | ROLE            | N        | NULL   |
+-------------------------------+------+---------------+-------------+-------+---------+------------+------+-------+--------+----------------+-----------------+-------------+-------------------------+-----------------+----------+--------+

CREATE OR REPLACE TABLE t1(a1 NUMBER, a2 VARCHAR, a3 DATE);
CREATE OR REPLACE TABLE t2(b1 VARCHAR);

DESC TABLE t1;

+------+-------------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+
| name | type              | kind   | null? | default | primary key | unique key | check | expression | comment | policy name |
|------+-------------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------|
| A1   | NUMBER(38,0)      | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    | NULL        |
| A2   | VARCHAR(16777216) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    | NULL        |
| A3   | DATE              | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    | NULL        |
+------+-------------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+

DESC TABLE t2;

+------+-------------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+
| name | type              | kind   | null? | default | primary key | unique key | check | expression | comment | policy name |
|------+-------------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------|
| B1   | VARCHAR(16777216) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    | NULL        |
+------+-------------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+

ALTER TABLE t1 SWAP WITH t2;

DESC TABLE t1;

+------+-------------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+
| name | type              | kind   | null? | default | primary key | unique key | check | expression | comment | policy name |
|------+-------------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------|
| B1   | VARCHAR(16777216) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    | NULL        |
+------+-------------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+

DESC TABLE t2;

+------+-------------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+
| name | type              | kind   | null? | default | primary key | unique key | check | expression | comment | policy name |
|------+-------------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------|
| A1   | NUMBER(38,0)      | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    | NULL        |
| A2   | VARCHAR(16777216) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    | NULL        |
| A3   | DATE              | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    | NULL        |
+------+-------------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+

CREATE OR REPLACE TABLE t1(a1 NUMBER);

DESC TABLE t1;

+------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+
| name | type         | kind   | null? | default | primary key | unique key | check | expression | comment | policy name |
|------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------|
| A1   | NUMBER(38,0) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    | NULL        |
+------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+

ALTER TABLE t1 ADD COLUMN a2 NUMBER;

ALTER TABLE t1 ADD COLUMN a3 NUMBER NOT NULL;

ALTER TABLE t1 ADD COLUMN a4 NUMBER DEFAULT 0 NOT NULL;

ALTER TABLE t1 ADD COLUMN a5 VARCHAR COLLATE 'en_US';

DESC TABLE t1;

+------+-----------------------------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+
| name | type                              | kind   | null? | default | primary key | unique key | check | expression | comment | policy name |
|------+-----------------------------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------|
| A1   | NUMBER(38,0)                      | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    | NULL        |
| A2   | NUMBER(38,0)                      | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    | NULL        |
| A3   | NUMBER(38,0)                      | COLUMN | N     | NULL    | N           | N          | NULL  | NULL       | NULL    | NULL        |
| A4   | NUMBER(38,0)                      | COLUMN | N     | 0       | N           | N          | NULL  | NULL       | NULL    | NULL        |
| A5   | VARCHAR(16777216) COLLATE 'en_us' | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    | NULL        |
+------+-----------------------------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+

ALTER TABLE t1 ADD COLUMN IF NOT EXISTS a2 NUMBER;

DESC TABLE t1;

+------+-----------------------------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+
| name | type                              | kind   | null? | default | primary key | unique key | check | expression | comment | policy name |
|------+-----------------------------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------|
| A1   | NUMBER(38,0)                      | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    | NULL        |
| A2   | NUMBER(38,0)                      | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    | NULL        |
| A3   | NUMBER(38,0)                      | COLUMN | N     | NULL    | N           | N          | NULL  | NULL       | NULL    | NULL        |
| A4   | NUMBER(38,0)                      | COLUMN | N     | 0       | N           | N          | NULL  | NULL       | NULL    | NULL        |
| A5   | VARCHAR(16777216) COLLATE 'en_us' | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    | NULL        |
+------+-----------------------------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+

ALTER TABLE t1 RENAME COLUMN a1 TO b1;

DESC TABLE t1;

+------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+
| name | type         | kind   | null? | default | primary key | unique key | check | expression | comment | policy name |
|------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------|
| B1   | NUMBER(38,0) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    | NULL        |
| A2   | NUMBER(38,0) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    | NULL        |
| A3   | NUMBER(38,0) | COLUMN | N     | NULL    | N           | N          | NULL  | NULL       | NULL    | NULL        |
| A4   | NUMBER(38,0) | COLUMN | N     | 0       | N           | N          | NULL  | NULL       | NULL    | NULL        |
+------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+

ALTER TABLE t1 DROP COLUMN a2;

DESC TABLE t1;

+------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+
| name | type         | kind   | null? | default | primary key | unique key | check | expression | comment | policy name |
|------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------|
| B1   | NUMBER(38,0) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    | NULL        |
| A3   | NUMBER(38,0) | COLUMN | N     | NULL    | N           | N          | NULL  | NULL       | NULL    | NULL        |
| A4   | NUMBER(38,0) | COLUMN | N     | 0       | N           | N          | NULL  | NULL       | NULL    | NULL        |
+------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+

ALTER TABLE t1 DROP COLUMN IF EXISTS a2;

+------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+
| name | type         | kind   | null? | default | primary key | unique key | check | expression | comment | policy name |
|------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------|
| B1   | NUMBER(38,0) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    | NULL        |
| A3   | NUMBER(38,0) | COLUMN | N     | NULL    | N           | N          | NULL  | NULL       | NULL    | NULL        |
| A4   | NUMBER(38,0) | COLUMN | N     | 0       | N           | N          | NULL  | NULL       | NULL    | NULL        |
+------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+

CREATE EXTERNAL TABLE exttable1
  LOCATION=@mystage/logs/
  AUTO_REFRESH = true
  FILE_FORMAT = (TYPE = PARQUET)
  ;

DESC EXTERNAL TABLE exttable1;

+-----------+-------------------+-----------+-------+---------+-------------+------------+-------+----------------------------------------------------------+-----------------------+
| name      | type              | kind      | null? | default | primary key | unique key | check | expression                                               | comment               |
|-----------+-------------------+-----------+-------+---------+-------------+------------+-------+----------------------------------------------------------+-----------------------|
| VALUE     | VARIANT           | COLUMN    | Y     | NULL    | N           | N          | NULL  | NULL                                                     | The value of this row |
+-----------+-------------------+-----------+-------+---------+-------------+------------+-------+----------------------------------------------------------+-----------------------+

ALTER TABLE exttable1 ADD COLUMN a1 VARCHAR AS (value:a1::VARCHAR);

DESC EXTERNAL TABLE exttable1;

+-----------+-------------------+-----------+-------+---------+-------------+------------+-------+----------------------------------------------------------+-----------------------+
| name      | type              | kind      | null? | default | primary key | unique key | check | expression                                               | comment               |
|-----------+-------------------+-----------+-------+---------+-------------+------------+-------+----------------------------------------------------------+-----------------------|
| VALUE     | VARIANT           | COLUMN    | Y     | NULL    | N           | N          | NULL  | NULL                                                     | The value of this row |
| A1        | VARCHAR(16777216) | VIRTUAL   | Y     | NULL    | N           | N          | NULL  | TO_CHAR(GET(VALUE, 'a1'))                                | NULL                  |
+-----------+-------------------+-----------+-------+---------+-------------+------------+-------+----------------------------------------------------------+-----------------------+

ALTER TABLE exttable1 RENAME COLUMN a1 TO b1;

DESC EXTERNAL TABLE exttable1;

+-----------+-------------------+-----------+-------+---------+-------------+------------+-------+----------------------------------------------------------+-----------------------+
| name      | type              | kind      | null? | default | primary key | unique key | check | expression                                               | comment               |
|-----------+-------------------+-----------+-------+---------+-------------+------------+-------+----------------------------------------------------------+-----------------------|
| VALUE     | VARIANT           | COLUMN    | Y     | NULL    | N           | N          | NULL  | NULL                                                     | The value of this row |
| B1        | VARCHAR(16777216) | VIRTUAL   | Y     | NULL    | N           | N          | NULL  | TO_CHAR(GET(VALUE, 'a1'))                                | NULL                  |
+-----------+-------------------+-----------+-------+---------+-------------+------------+-------+----------------------------------------------------------+-----------------------+

ALTER TABLE exttable1 DROP COLUMN b1;

DESC EXTERNAL TABLE exttable1;

+-----------+-------------------+-----------+-------+---------+-------------+------------+-------+----------------------------------------------------------+-----------------------+
| name      | type              | kind      | null? | default | primary key | unique key | check | expression                                               | comment               |
|-----------+-------------------+-----------+-------+---------+-------------+------------+-------+----------------------------------------------------------+-----------------------|
| VALUE     | VARIANT           | COLUMN    | Y     | NULL    | N           | N          | NULL  | NULL                                                     | The value of this row |
+-----------+-------------------+-----------+-------+---------+-------------+------------+-------+----------------------------------------------------------+-----------------------+

CREATE OR REPLACE TABLE T1 (id NUMBER, date TIMESTAMP_NTZ, name STRING) CLUSTER BY (id, date);

SHOW TABLES LIKE 'T1';

---------------------------------+------+---------------+-------------+-------+---------+------------+------+-------+--------------+----------------+
           created_on            | name | database_name | schema_name | kind  | comment | cluster_by | rows | bytes |    owner     | retention_time |
---------------------------------+------+---------------+-------------+-------+---------+------------+------+-------+--------------+----------------+
 Tue, 21 Jun 2016 15:42:12 -0700 | T1   | TESTDB        | TESTSCHEMA  | TABLE |         | (ID,DATE)  | 0    | 0     | ACCOUNTADMIN | 1              |
---------------------------------+------+---------------+-------------+-------+---------+------------+------+-------+--------------+----------------+

ALTER TABLE t1 CLUSTER BY (date, id);

SHOW TABLES LIKE 'T1';

---------------------------------+------+---------------+-------------+-------+---------+------------+------+-------+--------------+----------------+
           created_on            | name | database_name | schema_name | kind  | comment | cluster_by | rows | bytes |    owner     | retention_time |
---------------------------------+------+---------------+-------------+-------+---------+------------+------+-------+--------------+----------------+
 Tue, 21 Jun 2016 15:42:12 -0700 | T1   | TESTDB        | TESTSCHEMA  | TABLE |         | (DATE,ID)  | 0    | 0     | ACCOUNTADMIN | 1              |
---------------------------------+------+---------------+-------------+-------+---------+------------+------+-------+--------------+----------------+