Databricks Introduces MATCH_RECOGNIZE SQL Operator

Databricks has released a new SQL operator called MATCH_RECOGNIZE in public preview, bringing regex-like sequence detection directly to its compute environments, including Lakehouse Real-Time. Traditionally, querying sequential event data in SQL required complex chains of common table expressions, self-joins, and window functions because SQL naturally treats rows as unordered sets. The new operator simplifies this by letting data practitioners write direct pattern-matching queries, eliminating the need for convoluted gaps and islands logic.

The operator has immediate applications across several industries. In cybersecurity, analysts can identify credential stuffing by flagging accounts with 5 or more failed login attempts within a 1-hour window that are immediately followed by a successful login. By using the FIRST(FAIL.event_time) function inside a DEFINE block, users can anchor the initial failure timestamp to dynamically evaluate subsequent events. In financial services, analysts can detect V-shaped stock trends without manually carving rows into separate groups using LAG and LEAD functions. Instead, they can partition data by symbol, order it by time, and define the trend as a sequence of states.

For e-commerce, product managers can track high-intent cart abandonment by identifying users who viewed a product page 2 or more times and added an item to their cart, but then stopped. MATCH_RECOGNIZE handles this by using the end-of-partition anchor $ to represent the final event in a session, bypassing the need for NOT EXISTS subqueries. In manufacturing and IoT, predictive maintenance systems can spot rising temperature trends before equipment fails. Using PREV and NEXT functions, developers can write simple rules like temperature > PREV(temperature) to continuously monitor sensor telemetry.

To help teams transition, Databricks is encouraging users to migrate legacy pipelines. Practitioners can use Genie Code to assist in rewriting their most complex window-function and self-join queries into simpler MATCH_RECOGNIZE syntax.