SnowConvert AI - PostgreSQL - CREATE MATERIALIZED VIEW

Translation reference to convert PostgreSQL Materialized View to Snowflake Dynamic Table

Applies to

  • PostgreSQL

  • Greenplum

  • Netezza

Description

In Snowconvert, 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.

Grammar Syntax

CREATE MATERIALIZED VIEW [ IF  NOT EXISTS ] <table_name>
    [ (<column_name> [, ...] ) ]
    [ USING <method> ]
    [ WITH ( <storage_parameter> [= <value>] [, ... ] ) ]
    [ TABLESPACE <tablespace_name> ]
    AS <query>
    [ WITH [ NO ] DATA ]
Copy

Code Examples

Simple Case

Input Code:

PostgreSQL

CREATE MATERIALIZED VIEW product_summary AS
SELECT
    category,
    COUNT(*) AS total_products,
    MAX(price) AS max_price
FROM products
GROUP BY category;
Copy

Output Code:

Snowflake
CREATE OR REPLACE DYNAMIC TABLE product_summary
--** 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": "postgresql",  "convertedOn": "05/14/2025",  "domain": "no-domain-provided" }}'
AS
SELECT
    category,
    COUNT(*) AS total_products,
    MAX(price) AS max_price
FROM
    products
GROUP BY category;
Copy

IF NOT EXISTS

Hint

This syntax is fully supported in Snowflake.

This clause has been removed during the migration from PostgreSQL to Snowflake.

USING, TABLESPACE, and WITH

Note

This syntax is not needed in Snowflake.

These clauses are removed during the conversion process. In PostgreSQL, they are used to further customize data storage manually. This is something that Snowflake handles automatically (micro partitions), and it is typically not a concern.

Language: English