Advanced SQL Patterns for Production Analytics
Overview
| Experience | In Person |
|---|---|
| Track | Data Warehousing |
| Industry | Enterprise Technology |
| Technologies | Databricks SQL |
| Skill Level | Advanced |
| DOWNLOAD SESSION SLIDES | |
MATCH_RECOGNIZE is landing on Databricks SQL — and it changes how analysts express row patterns.
This session is demo-driven, built around production-grade examples — the kind of pattern queries that already run in pipelines, dashboards, and alerts. For each one we watch the query shrink from dozens of lines to a few. Readable. Maintainable. A fraction of the cost to run.
You'll leave able to write — and challenge — patterns the way you'd say them out loud.
Session Speakers
Fabien Contaminard
/Sr. Specialist Solutions Architect DWH
Databricks
Serge Rielau
/Principal Software Engineer
Databricks
Full Summary
Pattern matching in SQL: How MATCH_RECOGNIZE replaces dozens of joins
SQL struggles with questions that depend on sequences of rows: V-shaped price dips, sessions in clickstreams, funnels, or matching trades to prior quotes. MATCH_RECOGNIZE, standardized in SQL:2016 and pioneered in commercial systems years earlier, treats these as patterns over ordered rows, similar to regular expressions. The session demonstrates MATCH_RECOGNIZE in Databricks SQL, with no committed release timeline. For teams building business intelligence and analytics on the Databricks Platform, adopting row-patterns when available brings clarity, maintainability, and speed to problems that once required sprawling joins and CTEs.
FAQ
Oracle introduced the concept roughly fifteen years ago, and it became part of the SQL:2016 standard. The session demonstrates MATCH_RECOGNIZE in Databricks SQL, with no committed timeline announced.
Yes. An ORDER BY inside MATCH_RECOGNIZE is mandatory, because patterns only make sense over a defined sequence. Partitioning appropriately is equally important to avoid interleaving unrelated rows.
Avoid it for problems that standard aggregates or window functions already solve cleanly, such as simple averages or small rolling windows. Queries are easier to read and maintain with conventional SQL in those cases.
Union the series into one ordered stream and express the relationship as a pattern, for example "quotes then trade." The demo replaced an inequality join on about 11 million rows with a single pass that ran in seconds, while the join-based approach took minutes.
Default behavior skips past matched rows before resuming, so matches never overlap. Adjusting AFTER MATCH SKIP to restart within the previous match enables overlapping patterns, which is essential for cases like detecting W-shapes that share a peak or trough.