CREATE MATERIALIZED VIEW

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:

Redshift

IN -> Redshift_01.sql

CREATE MATERIALIZED VIEW mv_baseball AS
SELECT ball AS baseball FROM baseball_table;

Output Code:

Snowconvert Output

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:

• Sortkey

• DistKey

• DistStyle

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