Snowflake Recipes

Introduction

I recently shared few MATCH_RECOGNIZE recipes here, here and here. Since then I gotten lot of feedback asking to post an applied overview of the MATCH_RECOGNIZE query syntax.

To demonstrate the power of MATCH_RECOGNIZE I will use the Unemployment by State data set from BLS for this purpose. Use of this dataset was inspired by a blogpost by Kelsey Gonzales, Coronavirus Unemployment.

Analysis Goal

The goal of this analysis to take a look at the Unemployment data set and identify the States that are showing a decreasing Unemployment to date, and identify the State with the longest streak of decrease in Unemployment, month over month.

The Unemployment data look as following.

Analysis Approach

We will use MATCH_RECOGNIZE in Snowflake to find the decreasing Unemployment Rate pattern PARTITIONed by State

Enter MATCH_RECOGNIZE

The MATCH_RECOGNIZE clause is used to search for a pattern in single field value spanning over multiple records. This clause enables you to define patterns using regular expressions and aggregate methods to verify and extract values from the match.

The following example shows the basic structure of a MATCH_RECOGNIZE clause:

SELECT STATE, MONTHS_OF_DECREASING_UNEMPLOYMENT, STREAK_START_DATE, STREAK_END_DATE
FROM UNEMPLOYMENT_RATE
MATCH_RECOGNIZE(
  PARTITION BY STATE
  ORDER BY YEARMONTH
  MEASURES
    FIRST(YEARMONTH) AS STREAK_START_DATE,
    LAST(YEARMONTH) AS STREAK_END_DATE,
    COUNT(*) AS MONTHS_OF_DECREASING_UNEMPLOYMENT
  
  ONE ROW PER MATCH
  PATTERN (DECREASE+$)
  DEFINE
    INCREASE AS UNEMPLOYMENT_RATE > LAG(UNEMPLOYMENT_RATE),
    DECREASE AS UNEMPLOYMENT_RATE < LAG(UNEMPLOYMENT_RATE)
)
ORDER BY MONTHS_OF_DECREASING_UNEMPLOYMENT DESC;

PARTITION BY

PARTITION BY allows the match to be keyed and partitioned over a column name. A match will happen over every unique key specified by the partition statement. This enables a single query to be matched over all the keys and generate separate matches, one to every key.

MEASURES

MEASURES is used to define the projected values from the match using aggregate methods. For example, LAST(YEARMONTH) AS STREAK_END_DATE will output the last YEARMONTH value that was found over all events that matched pattern named DECREASE into field name STREAK_END_DATE.

PATTERN

The pattern defines the regular expression of events to be searched over the data stream. Pattern variables are user-defined and separated by spaces. Regex modifiers like ?, + and * can be used to modify the frequency of a variable when matching events.

PATTERN (INIT+ (REVERSAL | MODIFICATION))

The pattern on this example defines a variable INIT at least once, followed by a concatenation of either REVERSAL or MODIFICATION.

Pattern quantifiers are used to change how a pattern is mapped in the data stream, defining how many times a pattern needs to match to be valid. The following quantifiers are available:

• '*' - Zero or more times
• '+' - One or more times
• '?' – Zero or one time
• '|' - One pattern or another
• '^' - Regex Start Anchor
• '$' - Regex End Anchor

INIT REVERSAL+$

This example defines INIT 1 time, and REVERSAL 1 or more times, ending in REVERSAL (Regex End Anchor $).

DEFINE

DEFINE specifies the rules used to match a pattern variable to an event. The rules are Boolean expressions over aggregated values from the sequence of records.

DEFINE
  INCREASE AS UNEMPLOYMENT_RATE > LAG(UNEMPLOYMENT_RATE),
  DECREASE AS UNEMPLOYMENT_RATE < LAG(UNEMPLOYMENT_RATE)

This example defines rules INCREASE and DECREASE.

INCREASE defines the pattern where current UNEMPLOYMENT_RATE is greater than the previous UNEMPLOYMENT_RATE.

DECREASE defines the pattern where current UNEMPLOYMENT_RATE is less than the previous UNEMPLOYMENT_RATE.

Aggregate Methods

The following aggregate methods can be used in MEASURES and DEFINE:

• MIN – The minimum number aggregated so far.
• MAX – The maximum number aggregated so far.
• FIRST – The first value aggregated.
• LAST – The last value aggregated so far.

Query Output

The above MATCH_RECOGNIZE query will produce the following results, which show the States with a Streak decreasing Unemployment Rate.

We see that Rhode Island has a decreasing Unemployment Rate starting July 2021.