createTable with standardTable

Creates a standard table in Snowflake with Snowflake-specific features such as clustering, change tracking, data retention, policies, tags, and contacts. This extends core Liquibase's createTable change type.

Note: Automatic rollback drops the table.

Known limitation: 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)

No

schemaName

String

Name of the schema

No

tableName

String

Name of the table to create

Yes

remarks

String

Comment describing the table

No

replaceIfExists

Boolean

If true, replaces existing table with OR REPLACE*

No

ifNotExists

Boolean

If true, only creates if table doesn't exist*

No

isTransient

Boolean

If true, creates a transient table (no Fail-safe)**

No

temporary

Boolean

If true, creates a temporary table**

No

clusterBy

String

Comma-separated list of columns for clustering

No

dataRetentionTimeInDays

Integer

Time Travel retention period (0-90 days)

No

maxDataExtensionTimeInDays

Integer

Maximum extension beyond retention period

No

changeTracking

Boolean

If true, enables change data capture (CDC)

No

enableSchemaEvolution

Boolean

If true, enables flexible schema changes

No

defaultDdlCollation

String

Default collation for string columns (e.g., utf8)

No

copyGrants

Boolean

If true, copies grants from existing table***

No

*replaceIfExists and ifNotExistsare mutually exclusive.

**isTransient and temporaryare mutually exclusive.

***copyGrants requires replaceIfExists=true.

Nested elements

Element

Type

Description

columnStandardTable

Column definition

Column with Snowflake-specific properties

tags

MapWrapper

Key-value pairs of tags to apply

contacts

MapWrapper

Contact purposes to contact names****

rowAccessPolicy

RowAccessPolicy

Row-level security policy

aggregationPolicy

AggregationPolicy

Privacy-preserving aggregation policy

joinPolicy

JoinPolicy

Policy controlling join operations

****Valid contact purposes: steward, support, approver(case-insensitive).

columnStandardTable

Snowflake-specific column element. See columnStandardTable for attributes and nested elements.

<?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">
  <!-- Basic standard table with primary key and constraints -->
  <changeSet id="create-order-details" author="examples">
    <pro-snowflake:createStandardTable
                tableName="ORDER_DETAILS"
                remarks="Order line items with comprehensive constraints">
      <pro-snowflake:columnStandardTable name="order_id" type="NUMBER(38,0)" nullable="false">
        <pro-snowflake:constraints
                        primaryKey="true"
                        primaryKeyName="pk_order_details"
                        enforced="true"
                        rely="true"/>
      </pro-snowflake:columnStandardTable>
      <pro-snowflake:columnStandardTable name="line_number" type="NUMBER(10,0)" nullable="false">
        <pro-snowflake:constraints
                        primaryKey="true"
                        primaryKeyName="pk_order_details"/>
      </pro-snowflake:columnStandardTable>
      <pro-snowflake:columnStandardTable name="customer_id" type="NUMBER(38,0)" nullable="false">
        <pro-snowflake:constraints
                        foreignKeyName="fk_details_customer"
                        referencedTableName="customers"
                        referencedColumnNames="customer_id"/>
      </pro-snowflake:columnStandardTable>
      <pro-snowflake:columnStandardTable name="order_reference" type="VARCHAR(100)" nullable="false">
        <pro-snowflake:constraints
                        unique="true"/>
      </pro-snowflake:columnStandardTable>
      <pro-snowflake:columnStandardTable name="status" type="VARCHAR(20)" nullable="false"/>
      <pro-snowflake:tags>
        <pro-snowflake:entry key="environment" value="production"/>
        <pro-snowflake:entry key="cost_center" value="sales"/>
      </pro-snowflake:tags>
    </pro-snowflake:createStandardTable>
  </changeSet>
  <!-- Advanced table with autoincrement, policies, and column-level features -->
  <changeSet id="create-user-profiles" author="examples">
    <pro-snowflake:createStandardTable
                tableName="USER_PROFILES"
                remarks="User profiles with comprehensive Snowflake features"
                clusterBy="user_id, created_date"
                dataRetentionTimeInDays="30"
                changeTracking="true"
                enableSchemaEvolution="true">
      <pro-snowflake:columnStandardTable
                    name="user_id"
                    type="NUMBER(38,0)"
                    autoIncrement="true"
                    startWith="1"
                    incrementBy="1"
                    autoIncrementOrder="ORDER"
                    nullable="false">
        <pro-snowflake:constraints
                        primaryKey="true"
                        primaryKeyName="pk_user_profiles"/>
      </pro-snowflake:columnStandardTable>
      <pro-snowflake:columnStandardTable
                    name="email"
                    type="VARCHAR(200)"
                    maskingPolicy="email_mask"
                    nullable="false">
        <pro-snowflake:constraints
                        unique="true"/>
        <pro-snowflake:tags>
          <pro-snowflake:entry key="pii" value="email"/>
        </pro-snowflake:tags>
      </pro-snowflake:columnStandardTable>
      <pro-snowflake:columnStandardTable
                    name="username"
                    type="VARCHAR(50)"
                    collation="en-ci"
                    nullable="false"/>
      <pro-snowflake:columnStandardTable
                    name="profile_data"
                    type="VARIANT"
                    defaultValueComputed="OBJECT_CONSTRUCT()"/>
      <pro-snowflake:columnStandardTable
                    name="created_at"
                    type="TIMESTAMP_NTZ"
                    defaultValueComputed="CURRENT_TIMESTAMP()"
                    nullable="false"/>
      <pro-snowflake:columnStandardTable
                    name="created_date"
                    type="DATE"
                    defaultValueComputed="CURRENT_DATE()"
                    nullable="false"/>
      <pro-snowflake:columnStandardTable
                    name="status"
                    type="VARCHAR(20)"
                    defaultValue="active"
                    nullable="false"/>
      <pro-snowflake:columnStandardTable
                    name="account_balance"
                    type="NUMBER(18,2)"
                    defaultValueNumeric="0.00"
                    projectionPolicy="sensitive_data_projection"
                    nullable="false"/>
      <pro-snowflake:columnStandardTable
                    name="is_verified"
                    type="BOOLEAN"
                    defaultValueBoolean="false"
                    nullable="false"/>
      <pro-snowflake:tags>
        <pro-snowflake:entry key="environment" value="production"/>
        <pro-snowflake:entry key="department" value="user_management"/>
      </pro-snowflake:tags>
      <pro-snowflake:contacts>
        <pro-snowflake:entry key="steward" value="data_steward"/>
        <pro-snowflake:entry key="support" value="platform_support"/>
      </pro-snowflake:contacts>
    </pro-snowflake:createStandardTable>
  </changeSet>
</databaseChangeLog>