Reset When

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 subpartition is created within the existing window partition. For more information about Reset When, see the Teradata documentation.

Sample Source Patterns

Sample data

Teradata

Query

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);

Result

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

Query

CREATE TABLE account_balance
(
  account_id INTEGER NOT NULL,
  month_id INTEGER,
  balance INTEGER,
  UNIQUE (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);

Result

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

Query

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;

Result

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 has to be translated using native SQL syntax and nested subqueries, as follows:

Query

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;

Result

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. In essence, it is the same workaround.

Teradata

Query

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;

Result

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

Query

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;

Untitled

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

Query

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

Query

SELECT
    account_id,
    month_id,
    balance,
    ROW_NUMBER() OVER (
        PARTITION BY account_id
    --** MSC-ERROR - MSCEWI2077 - 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

MSCEWI2077: RESET WHEN clause is not supported in this scenario due to its condition