CREATE OR ALTER <object>¶

CREATE OR ALTER commands are DDL commands that combine the functionality of the CREATE command and the ALTER command, enabling you to define an object using the syntax supported by the CREATE <object> command with the limitations of the ALTER <object> command.

The commands maintain data and associations, meaning that data and other states, tag associations and attached policies, and privilege grants on the object are preserved. However, some object transformations can result in dropped data. For example, if a CREATE OR ALTER TABLE statement results in a dropped column, any data contained in the column is lost (but can still be recovered with Time Travel).

CREATE OR ALTER commands enable you to apply incremental updates to objects using a declarative, idempotent method. When executed, a CREATE OR ALTER statement results in one of these outcomes:

• If the object doesn’t exist, it’s created according to the definition.
• If the object exists, it’s altered into the object defined in the statement.
• If the object already matches the definition, it remains unchanged.

How CREATE OR ALTER differs from CREATE and ALTER¶

CREATE and ALTER are imperative commands: each statement describes a specific operation to perform, such as adding a column, changing a property, or renaming an object. CREATE OR ALTER is declarative: you describe the full desired state of the object, and Snowflake computes what changes are needed to reach that state.

This distinction explains both the key benefit and the key limitations:

• Idempotency. Running the same statement multiple times always produces the same result. This makes CREATE OR ALTER safe for deployment scripts and infrastructure-as-code workflows where the same script runs in both new and existing environments. For a more complete solution that manages Snowflake objects as code using a plan-then-deploy workflow across environments, see Snowflake DCM Projects.
• No renaming. Because CREATE OR ALTER works by comparing the desired definition to the current state, it can detect that a column was added or removed, but it can’t determine whether a column was renamed. If column b exists in the current table but not in the new definition, and column c appears in the new definition but not in the current table, Snowflake treats this as a drop of b and an addition of c, not a rename. Use ALTER TABLE ... RENAME COLUMN for explicit renames.
• ALTER limitations apply. When an object already exists, Snowflake applies the computed delta using the same engine as the corresponding ALTER command. Any operation that ALTER doesn’t support is also unsupported in CREATE OR ALTER. For example, you can’t change a column’s data type to an incompatible type.

A CREATE OR ALTER statement defines the complete desired state of an object. Snowflake computes and applies the minimal set of changes needed to reach that state, using the same operations available through the corresponding ALTER command.

See also:

CREATE <object> , ALTER <object>

CREATE OR ALTER commands¶

For specific syntax, usage notes, and examples, see:

Account Objects:

• CREATE OR ALTER AUTHENTICATION POLICY
• CREATE OR ALTER DATABASE
• CREATE OR ALTER ROLE
• CREATE OR ALTER WAREHOUSE

Database Objects:

• CREATE OR ALTER APPLICATION ROLE
• CREATE OR ALTER DATABASE ROLE
• CREATE OR ALTER DATA METRIC FUNCTION
• CREATE OR ALTER DYNAMIC TABLE
• CREATE OR ALTER EXTERNAL FUNCTION
• CREATE OR ALTER FILE FORMAT
• CREATE OR ALTER FUNCTION
• CREATE OR ALTER FUNCTION (Snowpark Container Services)
• CREATE OR ALTER PROCEDURE
• CREATE OR ALTER SCHEMA
• CREATE OR ALTER SEMANTIC VIEW
• CREATE OR ALTER STAGE
• CREATE OR ALTER TABLE
• CREATE OR ALTER TASK
• CREATE OR ALTER VIEW
• CREATE OR ALTER TAG

General usage notes¶

• Data governance: The CREATE OR ALTER commands don’t support data governance changes. Existing tags or policies are unaffected by CREATE OR ALTER statements and remain unchanged.

• Unsetting object properties and parameters: If a previously set property or parameter is absent in the modified object definition, it unsets it.

  If you unset an explicit parameter value, the parameter is reset to the default value. If the parameter is set on an object that contains the target object, the target object inherits the value set on the object that contains it. Otherwise, the parameter value for the object is reset to the default value.

  Unlike other properties, the CHANGE_TRACKING property will not be unset if not specified in a CREATE OR ALTER command.

• Atomicity: The CREATE OR ALTER TABLE command does not guarantee atomicity. In the very rare case that a CREATE OR ALTER TABLE statement for an extremely wide table fails during execution, it is possible that a subset of changes might have been applied to the table. If there is a possibility of partial changes, the error message, in most cases, includes the following text:

  Expand code block

  CREATE OR ALTER execution failed. Partial updates may have been applied.

  Show lessSee more

  Scroll to top

  For example, if the statement is attempting to drop column A and add a new column B to a table, and the statement is aborted, it is possible that column A was dropped but column B was not added.

  Note

  If changes are partially applied, the resulting table is still in a valid state, and you can use additional ALTER TABLE statements to complete the original set of changes.

  To recover from partial updates, Snowflake recommends the following recovery mechanisms:

  • Fix forward

    • Re-execute the CREATE OR ALTER TABLE statement. If the statements succeeds on the second attempt, the target state is achieved.
    • Investigate the error message. If possible, fix the error and re-execute the CREATE OR ALTER TABLE statement.

  • Roll back

    If it is not possible to fix forward, Snowflake recommends manually rolling back partial changes:

    • Investigate the state of the table using the DESCRIBE TABLE and SHOW TABLES commands. Determine which partial changes were applied, if any.

    • If any partial changes were applied, execute the appropriate ALTER TABLE statements to transform the table back to its original state.

      Note

      In some cases, you might not be able to undo partial changes. For more information, see the supported and unsupported actions for modifying column properties in the ALTER TABLE … ALTER COLUMN topic.

  • If you need help recovering from a partial update, contact Snowflake Support.

Limitations¶

The specific limitations of the CREATE OR ALTER <object> command depend on the object. Some examples of limitations are as follows:

• CREATE OR ALTER TABLE commands don’t support search optimization because search optimization is not part of the CREATE TABLE syntax.
• You can’t change the data type of a column in a table to an incompatible data type.
• You must suspend a task before you can alter it.
• CREATE OR ALTER TABLE … AS SELECT is currently not supported.
• You can’t rename objects or columns, or change column order.

For the limitations for a specific object, see the reference topic for the object.