Translation reference to convert Teradata Reset When functionality to Snowflake
Description
Reset When determines the partition on which an SQL window function operates based on some specific condition. If the condition evaluates to True, a new dynamic sub partition is created within the existing window partition. For more information about Reset When, see the Teradata documentation.
Sample Source Patterns
Sample data
Teradata
IN -> Teradata_01.sql
CREATE TABLE account_balance
(
account_id INTEGER NOT NULL,
month_id INTEGER,
balance INTEGER
)
UNIQUE PRIMARY INDEX (account_id, month_id);
INSERT INTO account_balance VALUES (1, 1, 60);
INSERT INTO account_balance VALUES (1, 2, 99);
INSERT INTO account_balance VALUES (1, 3, 94);
INSERT INTO account_balance VALUES (1, 4, 90);
INSERT INTO account_balance VALUES (1, 5, 80);
INSERT INTO account_balance VALUES (1, 6, 88);
INSERT INTO account_balance VALUES (1, 7, 90);
INSERT INTO account_balance VALUES (1, 8, 92);
INSERT INTO account_balance VALUES (1, 9, 10);
INSERT INTO account_balance VALUES (1, 10, 60);
INSERT INTO account_balance VALUES (1, 11, 80);
INSERT INTO account_balance VALUES (1, 12, 10);
account_id
month_id
balance
1
1
60
1
2
99
1
3
94
1
4
90
1
5
80
1
6
88
1
7
90
1
8
92
1
9
10
1
10
60
1
11
80
1
12
10
Snowflake
OUT -> Teradata_01.sql
CREATE OR REPLACE TABLE account_balance (
account_id INTEGER NOT NULL,
month_id INTEGER,
balance INTEGER,
UNIQUE (account_id, month_id)
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
;
INSERT INTO account_balance
VALUES (1, 1, 60);
INSERT INTO account_balance
VALUES (1, 2, 99);
INSERT INTO account_balance
VALUES (1, 3, 94);
INSERT INTO account_balance
VALUES (1, 4, 90);
INSERT INTO account_balance
VALUES (1, 5, 80);
INSERT INTO account_balance
VALUES (1, 6, 88);
INSERT INTO account_balance
VALUES (1, 7, 90);
INSERT INTO account_balance
VALUES (1, 8, 92);
INSERT INTO account_balance
VALUES (1, 9, 10);
INSERT INTO account_balance
VALUES (1, 10, 60);
INSERT INTO account_balance
VALUES (1, 11, 80);
INSERT INTO account_balance
VALUES (1, 12, 10);
account_id
month_id
balance
1
1
60
1
2
99
1
3
94
1
4
90
1
5
80
1
6
88
1
7
90
1
8
92
1
9
10
1
10
60
1
11
80
1
12
10
Reset When
For each account, suppose you want to analyze the sequence of consecutive monthly balance increases. When the balance of one month is less than or equal to the balance of the previous month, the requirement is to reset the counter to zero and restart.
To analyze this data, Teradata SQL uses a window function with a nested aggregate and a Reset When statement, as follows:
Teradata
IN -> Teradata_02.sql
SELECT
account_id,
month_id,
balance,
(
ROW_NUMBER() OVER (
PARTITION BY account_id
ORDER BY
month_id RESET WHEN balance <= SUM(balance) OVER (
PARTITION BY account_id
ORDER BY month_id
ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING
)
) -1
) AS balance_increase
FROM account_balance
ORDER BY 1, 2;
account_id
month_id
balance
balance_increase
1
1
60
0
1
2
99
1
1
3
94
0
1
4
90
0
1
5
80
0
1
6
88
1
1
7
90
2
1
8
92
3
1
9
10
0
1
10
60
1
1
11
80
2
1
12
10
0
Snowflake
Snowflake does not support the Reset When clause in window functions. To reproduce the same result, the Teradata SQL code must be translated using native SQL syntax and nested subqueries, as follows:
OUT -> Teradata_02.sql
SELECT
account_id,
month_id,
balance,
(
ROW_NUMBER() OVER (
PARTITION BY
account_id, new_dynamic_part
ORDER BY
month_id
) -1
) AS balance_increase
FROM
(
SELECT
account_id,
month_id,
balance,
previous_value,
SUM(dynamic_part) OVER (
PARTITION BY account_id
ORDER BY month_id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS new_dynamic_part
FROM
(
SELECT
account_id,
month_id,
balance,
SUM(balance) OVER (
PARTITION BY account_id
ORDER BY month_id
ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING
) AS previous_value,
(CASE
WHEN balance <= previous_value
THEN 1
ELSE 0
END) AS dynamic_part
FROM
account_balance
)
)
ORDER BY 1, 2;
account_id
month_id
balance
balance_increase
1
1
60
0
1
2
99
1
1
3
94
0
1
4
90
0
1
5
80
0
1
6
88
1
1
7
90
2
1
8
92
3
1
9
10
0
1
10
60
1
1
11
80
2
1
12
10
0
Two nested sub-queries are needed to support the Reset When functionality in Snowflake.
In the inner sub-query, a dynamic partition indicator (dynamic_part) is created and populated. dynamic_part is set to 1 if one month's balance is less than or equal to the preceding month's balance; otherwise, it's set to 0.
In the next layer, a new_dynamic_part attribute is generated as the result of a SUM window function.
Finally, a new_dynamic_part is added as a new partition attribute (dynamic partition) to the existing partition attribute (account_id) and applies the same ROW_NUMBER() window function as in Teradata.
After these changes, Snowflake generates the same output as Teradata.
Reset When when conditional window function is a column
Same example as above, except that now the window function used in the RESET WHEN condition is defined as a column called previous. This variation changes the transformation slightly since it is no longer necessary to define the previous_value as in the previous example. It is the same workaround.
Teradata
IN -> Teradata_03.sql
SELECT
account_id,
month_id,
balance,
SUM(balance) OVER (
PARTITION BY account_id
ORDER BY month_id
ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING
) AS previous,
(
ROW_NUMBER() OVER (
PARTITION BY account_id
ORDER BY
month_id RESET WHEN balance <= previous
)
) AS balance_increase
FROM account_balance
ORDER BY 1, 2;
account_id
month_id
balance
previous
balance_increase
1
1
60
0
1
2
99
60
1
1
3
94
99
0
1
4
90
94
0
1
5
80
90
0
1
6
88
80
1
1
7
90
88
2
1
8
92
90
3
1
9
10
92
0
1
10
60
10
1
1
11
80
60
2
1
12
10
80
0
Snowflake
OUT -> Teradata_03.sql
SELECT
account_id,
month_id,
balance,
SUM(balance) OVER (
PARTITION BY account_id
ORDER BY month_id
ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING
) AS previous,
(
ROW_NUMBER() OVER (
PARTITION BY
account_id, new_dynamic_part
ORDER BY
month_id
)
) AS balance_increase
FROM
(
SELECT
account_id,
month_id,
balance,
SUM(balance) OVER (
PARTITION BY account_id
ORDER BY month_id
ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING
) AS previous,
SUM(dynamic_part) OVER (
PARTITION BY account_id
ORDER BY month_id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS new_dynamic_part
FROM
(
SELECT
account_id,
month_id,
balance,
SUM(balance) OVER (
PARTITION BY account_id
ORDER BY month_id
ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING
) AS previous,
(CASE
WHEN balance <= previous
THEN 1
ELSE 0
END) AS dynamic_part
FROM
account_balance
)
)
ORDER BY 1, 2;
account_id
month_id
balance
previous
balance_increase
1
1
60
0
1
2
99
60
1
1
3
94
99
0
1
4
90
94
0
1
5
80
90
0
1
6
88
80
1
1
7
90
88
2
1
8
92
90
3
1
9
10
92
0
1
10
60
10
1
1
11
80
60
2
1
12
10
80
0
Known Issues
The RESET WHEN clause could have some variations such as its condition. Currently, SnowConvert only supports binary conditions (<=, >=, <> or =), in any other type, as IS NOT NULL, SnowConvert will remove the RESET WHEN clause and add an error message since it is not supported in Snowflake, as shown in the following example.
Teradata
IN -> Teradata_04.sql
SELECT
account_id,
month_id,
balance,
ROW_NUMBER() OVER (
PARTITION BY account_id
ORDER BY month_id
RESET WHEN balance IS NOT NULL
ROWS UNBOUNDED PRECEDING
) as balance_increase
FROM account_balance
ORDER BY 1,2;
Snowflake
OUT -> Teradata_04.sql
SELECT
account_id,
month_id,
balance,
ROW_NUMBER() OVER (
PARTITION BY account_id
!!!RESOLVE EWI!!! /*** SSC-EWI-TD0077 - RESET WHEN CLAUSE IS NOT SUPPORTED IN THIS SCENARIO DUE TO ITS CONDITION ***/!!!
ORDER BY month_id
ROWS UNBOUNDED PRECEDING
) as balance_increase
FROM
account_balance
ORDER BY 1,2;
Related EWIs
SSC-EWI-TD0077: RESET WHEN clause is not supported in this scenario due to its condition.
