SSC-EWI-BQ0004

Snowflake does not support named windows.

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.

Severity

Medium

Description

BigQuery allows the definition and usage of named windows in aggregate functions, they are defined in the WINDOW clause of the query they are used and can be used inside the OVER clause of these functions.

Snowflake does not support declaring named windows, please consider taking the window definition and apply it to all usages of that window directly in the OVER clause of the functions.

Code Example

Input Code:

BigQuery

IN -> BigQuery_01.sql

SELECT 
    COUNT(col1) OVER(myWindow)
FROM 
    test.exampleTable
WINDOW 
    myWindow AS (ORDER BY col2);

Output Code:

Snowflake

OUT -> BigQuery_01.sql

SELECT
    COUNT(col1)
    !!!RESOLVE EWI!!! /*** SSC-EWI-BQ0004 - SNOWFLAKE DOES NOT SUPPORT NAMED WINDOWS. ***/!!! OVER(myWindow)
FROM
    test.exampleTable
!!!RESOLVE EWI!!! /*** SSC-EWI-BQ0004 - SNOWFLAKE DOES NOT SUPPORT NAMED WINDOWS. ***/!!!
WINDOW
    myWindow AS (ORDER BY col2);

Recommendations

• Review your named window definitions, it might be possible to take the definition and apply it to the OVER clause of the functions it is used in. However, keep in mind the functional differences between BigQuery and Snowflake window frames still apply, take the following case as an example:

BigQuery:

SELECT 
    COUNT(col1) OVER(myWindow)
FROM 
    test.exampleTable
WINDOW 
    myWindow AS (ORDER BY col2);

Snowflake:

SELECT 
    COUNT(col1) OVER(ORDER BY col2)
FROM 
    test.exampleTable;

These two queries will produce the same rows but the Snowflake results will not be ordered, this is because the ORDER BY clause for window frames does not impact the entire query ordering as it does in BigQuery.

Recommendations

• If you need more support, you can email us at [email protected]