SnowflakeDocumentation Home

CREATE MATERIALIZED VIEW

Important Notice: Migration of Documentation Website

Please be advised that our documentation website is currently undergoing a migration to a new platform. To ensure you have access to the most up-to-date information, we kindly request that you visit our new documentation website located at:

Official Snowflake Snowconvert Documentation

For any immediate assistance or if you encounter any issues, please contact our support team at [email protected].

Thank you for your understanding.

Description

In Snowconvert, Redshift Materialized Views are transformed into Snowflake Dynamic Tables. To properly configure Dynamic Tables, two essential parameters must be defined: TARGET_LAG and WAREHOUSE. If these parameters are left unspecified in the configuration options, Snowconvert will default to preassigned values during the conversion, as demonstrated in the example below.

For more information on Materialized Views, click here.

For details on the necessary parameters for Dynamic Tables, click here.

Grammar Syntax

The following is the SQL syntax to create a view in Amazon Redshift. Click here to here to go to Redshifts specification for this syntax.

CREATE MATERIALIZED VIEW mv_name
[ BACKUP { YES | NO } ]
[ table_attributes ]
[ AUTO REFRESH { YES | NO } ]
AS query

Sample Source Patterns

Input Code:

IN -> Redshift_01.sql
CREATE MATERIALIZED VIEW mv_baseball AS
SELECT ball AS baseball FROM baseball_table;

Output Code:

OUT -> Redshift_01.sql
CREATE DYNAMIC TABLE mv_baseball
--** SSC-FDM-0031 - DYNAMIC TABLE REQUIRED PARAMETERS SET BY DEFAULT **
TARGET_LAG='1 day'
WAREHOUSE=UPDATE_DUMMY_WAREHOUSE
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "11/26/2024",  "domain": "test" }}'
AS
    SELECT ball AS baseball FROM
        baseball_table;

For the table attributes documentation you can check de following documentation:

The BACKUP and AUTO REFRESH clauses are deleted since they are not applicable in a Snowflake's Dynamic Table

Related Ewis

  • SSC-FDM-0031: Dynamic Table required parameters set by default

PreviousCREATE FUNCTIONNextCREATE PROCEDURE

Last updated