SnowConvert AI - Oracle - Create Materialized Views¶

Translation reference to convert Oracle Materialized View to Snowflake Dynamic Table

Description¶

In SnowConvert AI, Oracle 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 AI 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.

Sample Source Patterns¶

Oracle¶

CREATE MATERIALIZED VIEW sales_total
AS
SELECT SUM(amount) AS total_sales
FROM sales;
Copy

Snowflake¶

CREATE OR REPLACE DYNAMIC TABLE sales_total
--** 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":1, "minor":0},"attributes":{"component":"oracle"}}'
AS
SELECT SUM(amount) AS total_sales
FROM
sales;
Copy

Refresh Modes¶

Snowflake dynamic tables support an equivalent to Oracle’s materialized view refresh modes. The corresponding modes are as follows:

  • Oracle:

    • FAST: Refreshes only the rows that have changed.

    • COMPLETE: Refreshes the entire materialized view.

    • FORCE: Uses FAST if possible, otherwise uses COMPLETE.

  • Snowflake:

    • AUTO: Automatically determines the best refresh method.

    • FULL: Refreshes the entire table, equivalent to Oracle’s COMPLETE mode.

    • INCREMENTAL: Refreshes only the changed rows.

Default Refresh Mode¶

When using SnowConvert AI, the dynamic table’s default refresh mode is AUTO.

Mode Mappings¶

  • Oracle FAST and FORCE -> Snowflake AUTO

  • Oracle COMPLETE -> Snowflake FULL

For more details, refer to the official documentation on Oracle Refresh Modes and Snowflake Refresh Modes.

Oracle¶
CREATE MATERIALIZED VIEW CUSTOMER_SALES_SUMMARY
REFRESH COMPLETE
AS
SELECT
    CUSTOMER_ID,
    SUM(AMOUNT) AS TOTAL_AMOUNT
FROM
    SALES
GROUP BY
    CUSTOMER_ID;
Copy
Snowflake¶
CREATE OR REPLACE DYNAMIC TABLE CUSTOMER_SALES_SUMMARY
--** SSC-FDM-0031 - DYNAMIC TABLE REQUIRED PARAMETERS SET BY DEFAULT **
TARGET_LAG='1 day'
WAREHOUSE=UPDATE_DUMMY_WAREHOUSE
REFRESH_MODE=FULL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
AS
SELECT
   CUSTOMER_ID,
   SUM(AMOUNT) AS TOTAL_AMOUNT
FROM
   SALES
GROUP BY
   CUSTOMER_ID;
Copy

Known Issues¶

No known errors detected at this time.

Language: English