createMaterializedView

Creates a new materialized view in Snowflake with comprehensive data governance features including column-level policies, row access controls, aggregation policies, clustering, tags, and contacts. Materialized views are pre-computed data sets stored as tables that provide query performance benefits. Requires Snowflake Enterprise Edition or higher.

Note: Automatic rollback drops the materialized view.

Known limitation: This change type does not support database inspection features (snapshot, diff, diff-changelog, and generate-changelog commands).

Available attributes

Attribute

Type

Description

Required

catalogName

String

Name of the catalog (database) where the materialized view will be created

No

schemaName

String

Name of the schema where the materialized view will be created

No

viewName

String

Name of the materialized view to create

Yes

selectStatement

String

SELECT query defining the view*

Yes

replaceIfExists

Boolean

Replace the view if it exists using OR REPLACE**

No

ifNotExists

Boolean

Only create if the view doesn't exist using IF NOT EXISTS**

No

secure

Boolean

Create a secure view that restricts access to the view definition

No

copyGrants

Boolean

Retain access privileges from the original view when using OR REPLACE

No

clusterBy

String

Clustering expression(s) for query performance optimization

No

comment

String

Comment describing the materialized view

No

columnMaterializedView

Nested

Column definitions with optional policies and tags

No

rowAccessPolicy

Nested

Row-level security control for the view

No

aggregationPolicy

Nested

Aggregation privacy controls

No

tags

Nested

Key-value pairs of tags to apply

No

contacts

Nested

Key-value pairs mapping contact purposes to contact names

No

* Snowflake restrictions: No JOINs, subqueries, UDFs, window functions, HAVING, ORDER BY, or LIMIT

** replaceIfExists and ifNotExists are mutually exclusive

columnMaterializedView attributes

Attribute

Type

Description

Required

name

String

Column name (must match SELECT output exactly)

Yes

maskingPolicy

String

Name of masking policy to apply to this column

No

maskingPolicyUsing

String

Additional columns for masking policy context

No

projectionPolicy

String

Name of projection policy for column queries

No

tags

Map

Column-level metadata tags

No

rowAccessPolicy attributes

Attribute

Type

Description

Required

policyName

String

Name of the row access policy

Yes

on

String

Column name the policy applies to

Yes

aggregationPolicy attributes

Attribute

Type

Description

Required

policyName

String

Name of the aggregation policy

Yes

entityKey

String

Column used as the aggregation entity key

Yes

tags attributes

Attribute

Type

Description

Required

key

String

Tag name

Yes

value

String

Tag value

Yes

contacts attributes

Attribute

Type

Description

Required

key

String

Contact purpose: steward, support, or approver

Yes

value

String

Contact name

Yes

<?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 materialized view -->
  <changeSet id="create-mv-basic" author="examples">
    <pro-snowflake:createMaterializedView
                viewName="MV_SALES_BY_REGION"
                comment="Pre-computed sales aggregation by region"
                selectStatement="SELECT REGION, COUNT(*) AS SALE_COUNT, SUM(SALE_AMOUNT) AS TOTAL_SALES FROM SALES GROUP BY REGION"/>
  </changeSet>
  <!-- With tags -->
  <changeSet id="create-mv-with-tags" author="examples">
    <pro-snowflake:createMaterializedView
                viewName="MV_CUSTOMER_SUMMARY"
                ifNotExists="true"
                selectStatement="SELECT CUSTOMER_ID, COUNT(*) AS ORDER_COUNT FROM ORDERS GROUP BY CUSTOMER_ID">
      <pro-snowflake:tags>
        <pro-snowflake:entry key="environment" value="production"/>
        <pro-snowflake:entry key="department" value="analytics"/>
      </pro-snowflake:tags>
    </pro-snowflake:createMaterializedView>
  </changeSet>
  <!-- With column-level policies -->
  <changeSet id="create-mv-with-columns" author="examples">
    <pro-snowflake:createMaterializedView
                viewName="MV_CUSTOMER_SALES"
                clusterBy="CUSTOMER_ID"
                selectStatement="SELECT CUSTOMER_ID, CUSTOMER_NAME, SUM(AMOUNT) AS TOTAL FROM CUSTOMERS GROUP BY CUSTOMER_ID, CUSTOMER_NAME">
      <pro-snowflake:columnMaterializedView name="CUSTOMER_ID"/>
      <pro-snowflake:columnMaterializedView
                    name="CUSTOMER_NAME"
                    maskingPolicy="CUSTOMER_NAME_MASK">
        <pro-snowflake:tags>
          <pro-snowflake:entry key="pii" value="yes"/>
        </pro-snowflake:tags>
      </pro-snowflake:columnMaterializedView>
      <pro-snowflake:columnMaterializedView name="TOTAL"/>
    </pro-snowflake:createMaterializedView>
  </changeSet>
  <!-- Complete with all features -->
  <changeSet id="create-mv-comprehensive" author="examples">
    <pro-snowflake:createMaterializedView
                viewName="MV_COMPREHENSIVE_ANALYTICS"
                comment="Comprehensive analytics view with full governance"
                replaceIfExists="true"
                secure="true"
                copyGrants="true"
                clusterBy="REGION, PRODUCT_CATEGORY"
                selectStatement="SELECT REGION, PRODUCT_CATEGORY, COUNT(*) AS TRANSACTION_COUNT, SUM(SALE_AMOUNT) AS TOTAL_REVENUE FROM SALES GROUP BY REGION, PRODUCT_CATEGORY">
      <pro-snowflake:columnMaterializedView name="REGION"/>
      <pro-snowflake:columnMaterializedView name="PRODUCT_CATEGORY"/>
      <pro-snowflake:columnMaterializedView name="TRANSACTION_COUNT"/>
      <pro-snowflake:columnMaterializedView
                    name="TOTAL_REVENUE"
                    maskingPolicy="REVENUE_MASK">
        <pro-snowflake:tags>
          <pro-snowflake:entry key="pii" value="yes"/>
        </pro-snowflake:tags>
      </pro-snowflake:columnMaterializedView>
      <pro-snowflake:rowAccessPolicy policyName="SALES_ACCESS_POLICY" on="REGION"/>
      <pro-snowflake:tags>
        <pro-snowflake:entry key="environment" value="production"/>
      </pro-snowflake:tags>
      <pro-snowflake:contacts>
        <pro-snowflake:entry key="steward" value="my_data_steward"/>
      </pro-snowflake:contacts>
    </pro-snowflake:createMaterializedView>
  </changeSet>
</databaseChangeLog>