Alter Standard Table - Overview

Alters properties of an existing Snowflake standard table. This change supports a wide range of table modifications including renaming, swapping, clustering, data retention, change tracking, schema evolution, policies, tags, contacts, and search optimization.

Known limitations:

  • This change type does not support automatic rollback.

  • Snowflake snapshots created with Liquibase Secure 5.1 are incompatible with earlier versions due to enhanced object type detection for TABLES and VIEWS. Regenerate all snapshots with 5.1 after upgrading to avoid false differences in diff and diff-changelog operations.

Available Attributes

Attribute

Type

Description

Required

catalogName

String

Name of the catalog (database) containing the table

No

schemaName

String

Name of the schema containing the table

No

tableName

String

Name of the table to alter

Yes

ifExists

Boolean

If true, only alters if the table exists using `IF EXISTS`

No

Available Operations

You can specify one or more of the following nested elements to perform different alter operations. Multiple operations can be combined in a single change.

Table Management

  • renameTo - Rename the table or move it to a different schema/catalog

  • swapWith- Swap the table with another table

Performance Optimization

Data Retention

Change Tracking and Evolution

Metadata and Governance

Security Policies

Data Metrics

Validation Rules

  • tableName is required and cannot be empty

  • For each alter operation, mutually exclusive options cannot be used together

  • Referenced objects (contacts, tags, policies) must exist before they can be associated

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
  xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
  xmlns:pro-snowflake="http://www.liquibase.org/xml/ns/pro-snowflake"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
        http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-latest.xsd
        http://www.liquibase.org/xml/ns/pro-snowflake
        http://www.liquibase.org/xml/ns/pro-snowflake/liquibase-pro-snowflake-latest.xsd">
  <!-- Multiple operations in a single change -->
  <changeSet id="comprehensive-alter" author="examples">
    <pro-snowflake:alterStandardTable tableName="COMPREHENSIVE_TABLE" ifExists="true">
      <!-- Update comment -->
      <pro-snowflake:alterComment newComment="Updated comprehensive table with governance"/>
      <!-- Modify clustering -->
      <pro-snowflake:alterClustering clusterBy="TIMESTAMP, REGION"/>
      <!-- Update data retention -->
      <pro-snowflake:alterDataRetention
                newDataRetentionTimeInDays="60"
                newMaxDataExtensionTimeInDays="14"/>
      <!-- Enable change tracking and schema evolution -->
      <pro-snowflake:alterChangeTracking newChangeTracking="true"/>
      <pro-snowflake:alterSchemaEvolution newEnableSchemaEvolution="true"/>
      <!-- Add governance tags -->
      <pro-snowflake:alterTags>
        <pro-snowflake:setTags>
          <pro-snowflake:entry key="environment" value="production"/>
          <pro-snowflake:entry key="compliance" value="sox"/>
          <pro-snowflake:entry key="data_classification" value="confidential"/>
        </pro-snowflake:setTags>
      </pro-snowflake:alterTags>
      <!-- Add contacts -->
      <pro-snowflake:alterContacts>
        <pro-snowflake:setContacts>
          <pro-snowflake:entry key="steward" value="data.governance@company.com"/>
          <pro-snowflake:entry key="support" value="platform.support@company.com"/>
        </pro-snowflake:setContacts>
      </pro-snowflake:alterContacts>
      <!-- Add row access policy -->
      <pro-snowflake:alterRowAccessPolicy
                policyName="TABLE_ACCESS_POLICY"
                on="USER_ID"/>
    </pro-snowflake:alterStandardTable>
  </changeSet>
</databaseChangeLog>