CREATE MATERIALIZED VIEW

In this section, you will find information about BigQuery Materialized Views and their Snowflake equivalent.

Grammar Syntax

CREATE [ OR REPLACE ] MATERIALIZED VIEW [ IF NOT EXISTS ] materialized_view_name
[PARTITION BY partition_expression]
[CLUSTER BY clustering_column_list]
[OPTIONS(materialized_view_option_list)]
AS query_expression

Click here to go to the BigQuery specification for this syntax.

Although Snowflake has the Materialized View declaration, it has a series of limitations and aspects to take into account that make it behave differently from other database engines such as BigQuery, some of them are listed below.

  • Materialized View is only available in the Enterprise edition.

  • Data in Materialized Views are always being updated, which adds functional complexity to it.

  • Materialized Views are recommended when queries are small, in rows and columns.

  • If the base table does not vary much, it is recommended to use Materialized View, otherwise a Dynamic Table.

  • Dynamic Table has fewer restrictions (some non-deterministic functions, and subqueries, among others, are allowed).

For more information, check this documentation and this comparison.

Given these reasons, the transformation of a BigQuery Materialized View is done using a Dynamic Table in Snowflake.

CREATE [ OR REPLACE ] DYNAMIC TABLE <name>
  TARGET_LAG = { '<num> { seconds | minutes | hours | days }' | DOWNSTREAM }
  WAREHOUSE = <warehouse_name>
  AS <query>
  [ COMMENT = '<string_literal>' ]

SnowConvert will convert all Materialized Views to Dynamic Tables. However, in specific cases, such as when you only have a base table with a few rows and columns, and the information is not so variable, it is possible to use a Materialized View. This process must be done manually.

Simple case

BigQuery

CREATE OR REPLACE MATERIALIZED VIEW mView1
AS SELECT * from table1;

Snowflake

--** MSC-WARNING - MSCEWI1103 - MATERIALIZED VIEW TRANSLATED TO DYNAMIC TABLE. **
CREATE OR REPLACE DYNAMIC TABLE mView1 
--** MSC-WARNING - MSCEWI1104 - TARGET_LAG IS SET TO 1 DAY BY DEFAULT. **
TARGET_LAG='1 day'
--** MSC-WARNING - MSCEWI1105 - WAREHOUSE set to a placeholder. **
WAREHOUSE=UPDATE_DUMMY_WAREHOUSE
AS 
SELECT * from table1;

Snowflake Dynamic Table has the TARGET_LAG and WAREHOUSE parameters which are mandatory, the first specifies the delay of the Dynamic Table, for this parameter the default value will be 30 minutes since it is the same implemented for BigQuery Materialized View. The second parameter specifies the name of the warehouse that provides the resources to update the Dynamic Table. It is currently not possible to obtain this value automatically in SnowConvert, which is why an EWI is added. An alternative solution may be to use the CURRENT_WAREHOUSE function.

Multiple tables

Note that the SELECT statement incorporates multiple joins and accesses multiple tables. Snowflake Dynamic Table has no limitations on this, so the transformation can be done without problems.

BigQuery

CREATE MATERIALIZED VIEW mydataset.mView2
AS SELECT * from table1 t1
inner join table2 t2 on t1.column1 = t2.col1;

Snowflake

--** MSC-WARNING - MSCEWI1103 - MATERIALIZED VIEW TRANSLATED TO DYNAMIC TABLE. **
CREATE OR REPLACE DYNAMIC TABLE mydataset.mView2 
--** MSC-WARNING - MSCEWI1104 - TARGET_LAG IS SET TO 1 DAY BY DEFAULT. **
TARGET_LAG='1 day'
--** MSC-WARNING - MSCEWI1105 - WAREHOUSE set to a placeholder. **
WAREHOUSE=UPDATE_DUMMY_WAREHOUSE
AS
SELECT * from
  table1 t1
  inner join
    table2 t2 on t1.column1 = t2.col1;

With Options clause

If the Create Materialized View has an options clause, this will be commented on since this is not supported in Snowflake

BigQuery

CREATE OR REPLACE MATERIALIZED VIEW mView
PARTITION BY DATE(column3)
CLUSTER BY column2, column3
OPTIONS(
  enable_refresh = true,
  refresh_interval_minutes = 20,
  description = "a view that refreshes every 20 minutes"
)
AS SELECT * from table1 t1
inner join table2 t2 on t1.column1 = t2.col1;

Snowflake

--** MSC-WARNING - MSCEWI1103 - MATERIALIZED VIEW TRANSLATED TO DYNAMIC TABLE. **
CREATE OR REPLACE DYNAMIC TABLE mView 
TARGET_LAG = '20 minutes'
--** MSC-WARNING - MSCEWI1105 - WAREHOUSE set to a placeholder. **
WAREHOUSE=UPDATE_DUMMY_WAREHOUSE
AS 
SELECT * from
  table1 t1
 inner join
    table2 t2 on t1.column1 = t2.col1;

Known issues

1. Limitations

Snowflake and BigQuery have a list of limitations that must be taken into account, most of them are shared in both languages, such as the non-use non-deterministic functions. For more information check Snowflake documentation. and BigQuery documentation.

2. Materialized Views with subqueries are converted to regular Views

Snowflake Materialized Views support subqueries if they meet certain specifications. This scenario is currently not recognized, every Materialized View that has a subquery is converted to a regular view. For more information regarding this case, check this documentation.

3. The CLUSTER BY and PARTITION BY clauses

Dynamic Tables in Snowflake do not support clustering or partitioning, so these clauses do not apply to the conversion.

4. TARGET_LAG value

The value set for this parameter will follow the following laws:

  • Materialized View has no Options Clause -> TARGET_LAG = 30 minutes

  • Options Clause has no Specific parameters (enable_refresh, refresh_interval_minutes) -> TARGET_LAG = 30 minutes

  • enable_refresh = false -> TARGET_LAG = 30 minutes

  • enable_refresh = true && refresh_interval_minutes = null -> TARGET_LAG = 30 minutes

  • enable_refresh = true && refresh_interval_minutes = int -> TARGET_LAG = int minutes

  • enable_refresh = true && refresh_interval_minutes = float -> TARGET_LAG = ceilingFunction(float) minutes

Related EWIs

  1. MSC-BQ0004: The OPTIONS clause within View is not supported in Snowflake.

  2. MSCEWI1103: Materialized View was converted to Dynamic Table.

  3. MSCEWI1104: TARGET_LAG is set to 1 day by default.

  4. MSCEWI1105: WAREHOUSE set to a placeholder.

PreviousCREATE VIEWNextCREATE SCHEMA

Last updated