"Regex for Rows": Simplifying Pattern Detection in SQL with MATCH_RECOGNIZE

Imagine you work in cybersecurity and you have a table that tracks login attempts. This table includes each login attempt as a success or failure and when the attempt took place. You want to find strange login patterns, so you might ask the question, “which users had consecutive login failures, followed by success?” Finding this type of suspicious activity with standard SQL is challenging. SQL treats rows as unordered sets of facts without a timeline and there is no inherent concept of a sequence of events.

You could count failed logins per user, but counts won’t help you understand if these login attempts were a narrow timespan or spread out over a month; and it can’t tell you if a successful login occurred right after the failures. To do that in SQL, you will end up with a complex query that chains multiple common table expressions together, anchoring the time window to the first failure, then checking each subsequent row. 

MATCH_RECOGNIZE simplifies this. Now available in Databricks compute (including Lakehouse Real-Time), MATCH_RECOGNIZE lets you describe the sequence you care about directly, like a regular expression for rows. One SQL clause now handles the pattern matching and you’ve eliminated overly complicated SQL reliant on “gaps and islands” logic.

Let’s look at industry-specific examples of how MATCH_RECOGNIZE makes sequence detection simple across different industries.

Cybersecurity: Identifying suspicious log-in anomalies