MSC-BQ0007

Snowflake does not support named windows

This is a deprecated version of the SnowConvert documentation, please visit the official site HERE.

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:

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

Output Code:

SELECT SUM(col1)
/*** MSC-ERROR - MSC-BQ0007 - SNOWFLAKE DOES NOT SUPPORT NAMED WINDOWS ***/
--OVER(myWindow)
FROM test.exampleTable
/*** MSC-ERROR - MSC-BQ0007 - 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 SUM(col1) OVER(myWindow) FROM test.exampleTable WINDOW myWindow AS (ORDER BY col2);

Snowflake:

SELECT SUM(col1) OVER(ORDER BY col2) FROM 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]