createTable with dynamicTable

Creates a dynamic table in Snowflake. Dynamic tables are materialized views that automatically refresh based on changes to underlying data. This extends core Liquibase's createTable change type with dynamic table-specific features.

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 dynamic table to create	Yes
`remarks`	String	Comment describing the table	No
`targetLag`	String	Refresh lag (e.g., `5 minutes`, `1 hour`, `DOWNSTREAM`)	Yes
`warehouse`	String	Warehouse name for refresh operations	Yes
`query`	String	The AS SELECT query defining the table	Yes
`replaceIfExists`	Boolean	If true, replaces the existing with `OR REPLACE`*	No
`ifNotExists`	Boolean	If true, only creates if the table doesn't exist*	No
`isTransient`	Boolean	If true, creates a transient dynamic table	No
`refreshMode`	String	Refresh mode: `AUTO`, `FULL`, or `INCREMENTAL`	No
`initialize`	String	Initialization mode: `ON_CREATE` or `ON_SCHEDULE`	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
`copyGrants`	Boolean	If true, copies grants from existing table**	No
`requireUser`	Boolean	If true, requires user context for refresh***	No
`immutableWhere`	String	Immutable WHERE clause for the query	No
`backfillFrom`	String	Source table for backfilling data****	No

*replaceIfExists and ifNotExists are mutually exclusive.

**copyGrants requires replaceIfExists=true.

***Cannot use requireUser=truewith initialize=ON_CREATE.

****Cannot specify tagswhen using backfillFrom.

Nested elements

Element	Type	Description
`columnDynamicTable`	Column definition	Column with Snowflake-specific properties (optional)
`tags`	MapWrapper	Key-value pairs of tags to apply
`rowAccessPolicy`	RowAccessPolicy	Row-level security policy
`aggregationPolicy`	AggregationPolicy	Privacy-preserving aggregation policy

columnDynamicTable attributes

Optional element to define columns with Snowflake-specific properties like masking policies and collation.

Attribute	Type	Description
`name`	String	Column name
`type`	String	Snowflake data type
`nullable`	Boolean	Whether column allows NULL
`collation`	String	Column collation (e.g., `en-ci`)
`maskingPolicy`	String	Name of masking policy to apply
`remarks`	String	Column comment

-- Basic dynamic table
CREATE DYNAMIC TABLE DAILY_SALES_SUMMARY
WITH
  TAG (
    environment = 'production',
    owner = 'sales-analytics'
  ) TARGET_LAG = '5 minutes' WAREHOUSE = RD_WH COMMENT = 'Automatically refreshed summary of daily sales' AS
SELECT
  DATE_TRUNC ('day', ORDER_TIMESTAMP) AS SALE_DATE,
  SUM(ORDER_AMOUNT) AS TOTAL_SALES,
  COUNT(*) AS ORDER_COUNT
FROM
  ORDERS
WHERE
  ORDER_STATUS = 'COMPLETED'
GROUP BY
  DATE_TRUNC ('day', ORDER_TIMESTAMP);


-- Dynamic table with columns and features
CREATE DYNAMIC TABLE CUSTOMER_360_VIEW (
  CUSTOMER_ID NUMBER (38, 0) NOT NULL,
  CUSTOMER_NAME VARCHAR(200) COLLATE 'en-ci'
  WITH
    MASKING POLICY NAME_MASK COMMENT 'Customer name with masking',
    TOTAL_PURCHASES NUMBER (18, 2)
)
WITH
  TAG (
    environment = 'production',
    department = 'analytics'
  ) TARGET_LAG = '1 hour' WAREHOUSE = RD_WH REFRESH_MODE = INCREMENTAL INITIALIZE = ON_CREATE CLUSTER BY (CUSTOMER_ID) DATA_RETENTION_TIME_IN_DAYS = 7 COMMENT = 'Real-time customer data aggregation' AS
SELECT
  c.CUSTOMER_ID,
  c.CUSTOMER_NAME,
  SUM(o.ORDER_AMOUNT) AS TOTAL_PURCHASES
FROM
  CUSTOMERS c
  JOIN ORDERS o ON c.CUSTOMER_ID = o.CUSTOMER_ID
GROUP BY
  c.CUSTOMER_ID,
  c.CUSTOMER_NAME;

-- Basic dynamic table
CREATE DYNAMIC TABLE DAILY_SALES_SUMMARY
WITH
  TAG (
    environment = 'production',
    owner = 'sales-analytics'
  ) TARGET_LAG = '5 minutes' WAREHOUSE = RD_WH COMMENT = 'Automatically refreshed summary of daily sales' AS
SELECT
  DATE_TRUNC ('day', ORDER_TIMESTAMP) AS SALE_DATE,
  SUM(ORDER_AMOUNT) AS TOTAL_SALES,
  COUNT(*) AS ORDER_COUNT
FROM
  ORDERS
WHERE
  ORDER_STATUS = 'COMPLETED'
GROUP BY
  DATE_TRUNC ('day', ORDER_TIMESTAMP);


-- Dynamic table with columns and features
CREATE DYNAMIC TABLE CUSTOMER_360_VIEW (
  CUSTOMER_ID NUMBER (38, 0) NOT NULL,
  CUSTOMER_NAME VARCHAR(200) COLLATE 'en-ci'
  WITH
    MASKING POLICY NAME_MASK COMMENT 'Customer name with masking',
    TOTAL_PURCHASES NUMBER (18, 2)
)
WITH
  TAG (
    environment = 'production',
    department = 'analytics'
  ) TARGET_LAG = '1 hour' WAREHOUSE = RD_WH REFRESH_MODE = INCREMENTAL INITIALIZE = ON_CREATE CLUSTER BY (CUSTOMER_ID) DATA_RETENTION_TIME_IN_DAYS = 7 COMMENT = 'Real-time customer data aggregation' AS
SELECT
  c.CUSTOMER_ID,
  c.CUSTOMER_NAME,
  SUM(o.ORDER_AMOUNT) AS TOTAL_PURCHASES
FROM
  CUSTOMERS c
  JOIN ORDERS o ON c.CUSTOMER_ID = o.CUSTOMER_ID
GROUP BY
  c.CUSTOMER_ID,
  c.CUSTOMER_NAME;