Mirroring the RESET WHEN Functionality to Snowflake

The power of the SnowConvert integration in BlackDiamond Studio is rarely on better display that when it replicates the functionality of the RESET WHEN clause in Snowflake.

First, how does the RESET WHEN clause work in Teradata SQL? Depending on the evaluation of the specified condition, RESET WHEN determines the group or partition over which an ordered analytical function operates. If the condition evaluates to TRUE, a new dynamic partition is created inside the specified window partition. 

See the Teradata documentation for more information about RESET WHEN.

How to Mirror RESET WHEN to Snowflake Example

Suppose we have the following data set and we want to analyze the sequence of consecutive annual increases in profits:

When a year's profit is less than or equal to the previous year's profit, 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 clause as shown below:

 SELECT
  subsidiary_id,
  year_id,
  profit,
  (
    ROW_NUMBER() OVER (
      PARTITION BY subsidiary_id
      ORDER BY year_id
      RESET WHEN profit <= SUM(profit) OVER (
          PARTITION BY subsidiary_id
          ORDER BY year_id
          ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING
      )
    ) -1
  ) AS profit_increase
FROM subsidiary_profit
ORDER BY 1, 2;

That gives us the following set of results:

This is where the SnowConvert for Teradata tool comes in to do its job. Since 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 shown below:

SELECT
  subsidiary_id,
  year_id,
  profit,
  (
    ROW_NUMBER() OVER (
      PARTITION BY subsidiary_id, new_dynamic_part
      ORDER BY year_id
    ) - 1
  ) AS profit_increase
FROM
  (
    SELECT
      subsidiary_id,
      year_id,
      profit,
      previous_value,
      SUM(dynamic_part) OVER (
        PARTITION BY subsidiary_id
        ORDER BY year_id
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
      ) AS new_dynamic_part
    FROM
      (
        SELECT
          subsidiary_id,
          year_id,
          profit,
          SUM(profit) OVER (
            PARTITION BY subsidiary_id
            ORDER BY year_id
            ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING
          ) AS previous_value,
          (
            CASE WHEN profit <= previous_value THEN 1 ELSE 0 END
          ) AS dynamic_part
        FROM subsidiary_profit
      ) A
  ) B
ORDER BY 1, 2;

Two nested subqueries are required to support the RESET WHEN functionality in Snowflake. 

In the internal subquery labeled A, a dynamic partition indicator labeled dynamic_part is generated and filled in. This indicator (dynamic_part) is set to 1 if a year's profit is less than or equal to the previous year's profit. Otherwise, it is set to 0. In the outer subquery labeled  B, a new_dynamic_part indicator is generated as a result of the SUM window function in the source. Lastly, the new_dynamic_part is automatically added as a new partition attribute to the already existing partition attribute (account_id) and the same window function ROW_NUMBER()  is applied just like Teradata.

After these changes, the Snowflake query will generate precisely the same output as Teradata. 

You can try this out today by getting started with SnowConvert and BlackDiamond Studio for free.