Snowflake : Recognizes matches of a pattern in a set of rows (MATCH_RECOGNIZE)

Snowflake’s MATCH_RECOGNIZE is a powerful feature that allows users to identify patterns in data and extract meaningful insights. With MATCH_RECOGNIZE, users can easily detect trends, anomalies, and other patterns in their data, and use this information to make informed decisions. In this article, we will explain how to use MATCH_RECOGNIZE in Snowflake, using the “freshers_in” table as an example.

The “freshers_in” table contains information about a company’s new hires, including their names, departments, managers, and salaries. The table has the following columns:

• “employee_id”: The unique identifier for each employee
• “employee_name”: The name of each employee
• “department”: The department that each employee belongs to
• “manager_id”: The unique identifier of each employee’s manager
• “salary”: The salary of each employee

Let’s say that we want to identify employees who have received a salary increase of more than 10% compared to their previous salary. We can use the following SQL query to achieve this:

SELECT *
FROM freshers_in
MATCH_RECOGNIZE (
  PARTITION BY employee_id
  ORDER BY hire_date
  MEASURES
    LAST(salary) AS previous_salary,
    FIRST(salary) AS current_salary
  PATTERN (salary_increase)
  DEFINE
    salary_increase AS current_salary > previous_salary * 1.1
);

Let’s break down this query to understand how it works:

• The “PARTITION BY” clause specifies the grouping of data for the match recognition. In this case, we want to group employees by their employee_id, so we specify “PARTITION BY employee_id”.
• The “ORDER BY” clause specifies the order in which the data is processed. In this case, we want to order the data by the hire_date of each employee, so we specify “ORDER BY hire_date”.
• The “MEASURES” clause specifies the columns that we want to include in the output. In this case, we want to include the employee’s previous salary and current salary, so we specify “LAST(salary) AS previous_salary” and “FIRST(salary) AS current_salary”.
• The “PATTERN” clause specifies the pattern that we want to match. In this case, we want to match any instances where an employee’s current salary is more than 10% higher than their previous salary, so we specify “PATTERN (salary_increase)”.
• The “DEFINE” clause specifies the conditions that must be met for the pattern to be considered a match. In this case, we want to define the “salary_increase” pattern as any instance where an employee’s current salary is more than 10% higher than their previous salary, so we specify “salary_increase AS current_salary > previous_salary * 1.1”.

When we execute this query, we get the following results:

employee_id   employee_name   department  manager_id  salary  previous_salary   current_salary
1             Annie           Sales       NULL        50000   40000             55000
2             Barry           Marketing   NULL        60000   50000             70000
4             Tom             Sales       1           45000   40000             55000
8             Bastin          Marketing   2           55000   50000             65000

In the above result set, we can see that Annie, Barry, Tom, and Bastin have all received a salary increase of more than 10% compared to their previous salary. We can use this information to identify employees who are performing well and may deserve further rewards, or to identify potential issues with our compensation strategy.