SSC-FDM-0022

Window frame unit was changed to Rows

Severity

Low

Description

This warning is added when an unsupported Window Frame Unit was changed into Rows, leading to output differences. One example of this is the GROUPS unit, which is not supported by Snowflake.

Please note that this message is also used in cases where a Window Frame Unit is partially unsupported leading to it being changed, like the RANGE unit.

Example Code

Given the following data as an example to explain it.

C_NAME	C_BIRTH_DAY
USA	1
USA	4
Poland	9
Canada	10
USA	5
Canada	12
Costa Rica	3
Poland	4
USA	2
Costa Rica	7
Costa Rica	10

Oracle:

Code

IN -> Oracle_01.sql

SELECT
    C_NAME,
    SUM(C_BIRTH_DAY)
    OVER (ORDER BY C_BIRTH_DAY
    RANGE BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS MAX1
FROM WINDOW_TABLE;

Result

C_NAME	MAX1
USA	-
USA	1
Costa Rica	3
USA	6
Poland	6
USA	14
Costa Rica	19
Poland	26
Canada	35
Costa Rica	35
Canada	55

Snowflake:

Code

OUT -> Oracle_01.sql

SELECT
    C_NAME,
    SUM(C_BIRTH_DAY)
    OVER (ORDER BY C_BIRTH_DAY ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING /*** SSC-FDM-0022 - WINDOW FRAME UNIT 'RANGE' WAS CHANGED TO ROWS ***/) AS MAX1
    FROM
WINDOW_TABLE;

Result

C_NAME	MAX1
USA	-
USA	1
Costa Rica	3
USA	6
Poland	10
USA	14
Costa Rica	19
Poland	26
Canada	35
Costa Rica	45
Canada	55

Recommendations

• Ensure deterministic ordering for rows to ensure deterministic outputs when running in Snowflake.

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