SSC-EWI-BQ0004

Snowflake does not support named windows.

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 [e-mail]