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 PARTITION
ed 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
value that was found over all events that matched pattern named YEARMONTH
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.