SSC-FDM-0002

Statement references multiple objects

Description

Materialized views in Snowflake cannot query more than a single object. If SnowConvert would produce a CREATE MATERIALIZED VIEW that has more than one object reference (tables, views, result sets...), then the materialized behavior wouldn't be supported.

In most cases, SnowConvert automatically detects this issue, adds an FDM to the problematic object, and outputs a regular CREATE VIEW object instead.

Code Example

Input Code:

IN -> SqlServer_01.sql

-- This SQL Server MATERIALIZED VIEW references dbo.Categories and dbo.Products,
-- thus making it a non-valid Snowflake MATERIALIZED VIEW
CREATE MATERIALIZED VIEW dbo.MyMaterializedView
AS
SELECT 
    *
FROM
    dbo.Categories c
JOIN
    dbo.Products p ON c.CategoryID = p.CategoryID
GROUP BY
    c.CategoryID,
    c.CategoryName;

Output Code:

OUT -> SqlServer_01.sql

-- This SQL Server MATERIALIZED VIEW references dbo.Categories and dbo.Products,
-- thus making it a non-valid Snowflake MATERIALIZED VIEW
--** SSC-FDM-0002 - CREATE MATERIALIZED VIEW REFERENCES MULTIPLE OBJECTS. SNOWFLAKE MATERIALIZED VIEWS CANNOT QUERY MORE THAN A SINGLE OBJECT. **
CREATE OR REPLACE VIEW dbo.MyMaterializedView
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
AS
SELECT
    *
FROM
    dbo.Categories c
JOIN
        dbo.Products p
        ON c.CategoryID = p.CategoryID
GROUP BY
    c.CategoryID,
    c.CategoryName;

Recommendations

• Remember that access to materialized views in SnowFlake requires Enterprise Edition or higher.

• If you need more support, you can email us at [e-mail]