Skip to main content

Advanced SQL Patterns for Production Analytics

Summary

  • MATCH_RECOGNIZE, landing on Databricks SQL, enables analysts to express row-pattern queries—such as V-shaped price curves, gaps and islands, session boundaries, and checkout funnel abandonment—in a few readable lines instead of dozens of lines of self-joins or window function chains.
  • The syntax works like regular expressions applied over rows rather than strings, with DEFINE blocks describing each row's role in a pattern and PATTERN clauses specifying the sequence using familiar regex-style notation.
  • MATCH_RECOGNIZE is faster than equivalent CTE chains because the query engine processes it as a native pattern-matching operation, and it supports AFTER MATCH SKIP variants for controlling whether overlapping patterns are detected.

Advanced SQL Patterns for Production Analytics

Watch: Advanced SQL Patterns for Production Analytics
MATCH_RECOGNIZE is landing on Databricks SQL — and it changes how analysts express row patterns. this video 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.
Talk By: Fabien Contaminard, Sr. Specialist Solutions Architect DWH, Databricks ; Serge Rielau, Principal Software Engineer, Databricks ;

Chapters

FAQs

What is MATCH_RECOGNIZE and what problem does it solve in SQL?

MATCH_RECOGNIZE is a SQL 2016 standard that lets analysts describe patterns over sequences of rows the same way regular expressions describe patterns over strings. It solves the problem of writing complex row-pattern queries—like detecting V-shaped price curves or session boundaries—which previously required unwieldy self-joins or brittle window function chains spanning dozens of lines.

What are the four production SQL patterns covered in this video?

This video covers V-shaped price curve detection for stock market trough analysis, gaps and islands for identifying contiguous or broken data sequences, session analysis for defining session boundaries by time gaps, and behavior detection for identifying checkout funnel abandonment without conversion.

Why is MATCH_RECOGNIZE faster than equivalent CTE or self-join approaches?

MATCH_RECOGNIZE is processed by the query engine as a native pattern-matching operation, avoiding the repeated data scans that self-joins and CTE chains require. This makes it both more readable and more efficient for production pipelines and dashboards that run these queries continuously at scale.

When is MATCH_RECOGNIZE landing on Databricks SQL?

This video notes that no committed timeline has been announced for general availability, though the feature is under active development and being demonstrated. Viewers are advised to check Databricks release notes for the official availability announcement.

Full transcript

[00:08] everyone. We'll start the session right now. Uh you know the tricks. This is data bricks summit. So everything uh we're showing today there is no timeline committed yet but
[00:21] we can talk about this later. Don't forget at the end of the s session to complete your surveys. If you liked it put it five stars. If you didn't like it, put it one star. But I hope you will
[00:33] like it. And the talk today is about recognizing patterns over multiple set of rows. And we will have a nice conversation about match recognized function.
[00:47] So I am Fabian Contaminar. I am with search for this talk and search please. >> Sure. Yeah. So my name is Silo. I've been with data bricks for six years. I'm the what they call so nicely the SQL
[00:59] which means anything SQL uh in the data bricks company goes not to be confused with a SQL database product but a SQL language API that's kind of like me uh before I joined data bricks I was for
[01:12] some time in Salesforce and before I was an IBM lifer working as a SQL architect with people like Don Chamberlain so uh yeah SQL has been my life pretty much >> yeah and I've been SQLing my career
[01:26] like like s so I'm in in data bricks since two years I'm a specialist solution architect in data warehousing previously I worked at vertica as a customer success manager and before that
[01:38] I worked at terod data for multiple years and yeah mostly SQL and data wing so it's a good fit for this talk
[01:50] >> all right so we're going to so originally actually we had planned to do like a much broader talk but when we started diving into match recognized we really figured that this itself is a talk and it's it's a very interesting uh topic. It's not a new thing really. It
[02:02] was introduced by another vendor many many many 15 years ago something. >> Yeah. >> And um so I just want to take you through it. Uh if you want to quickly summarize it, it's like regular expression but not over strings but over
[02:16] rows. So you twist it. Um and we're going to have four examples and all of them go to Fabian. He's like really a good SQL puzzler. So I take no credit whatsoever. He takes all the all the glory. Uh so we're going to talk about
[02:28] V-shaped curves which is like troughs and in for example stock market. So we're going to talk gaps and islands which is another classic example. Uh sessionization and finalist like if you card card abandonment and so on and so
[02:41] forth and how to how you can merge time series of different tables. So and with that I give it to Fabian and I'm just going to be the demo guy who's going to push buttons. >> Thank you S. So, so yeah the first
[02:54] example we wanted to show you. So this one is the one you will find in the documentation. So Matt recognized as search is around 15 years. Uh it was invented by Oracle. So let's give them
[03:06] proper credit for this. And actually to give them the credits I use the same data they have in their example. It's a nice way to introduce you uh to uh this function.
[03:19] So let's assume you are having a data set an order data set. So here it's very basic. You have basically a price evolving over time and your business is wondering about oh okay but sometimes we
[03:32] have some events they are looking like a Vshapes. I would like you to help me find all the Vshapes we have in the data. So they're telling me okay a Vshape curve actually
[03:44] is you need two consecutive event of the price going down immediately followed by two consecutive events of price going up. So you are a data engineer or data
[03:56] analyst you say okay SQL can do almost everything but the year is SQL 99 so we don't have all the modern function we have we have basically joins
[04:09] so this question is actually solvable with this uh with this SQL 99 syntax but you can see it's actually pretty tough to to write pretty tough to run because
[04:22] we are coding the the table multiple times. I think it's nine for for this example. And uh the performance yeah they're not great. So it's very hard to read, very hard to write and actually
[04:34] the performance is really not good. But you get the good results. This is a this is already a good uh a good way. Now the the time has passed and we are moving to year 2003.
[04:48] So the window functions have landed in the NCSQL standards. So now it's actually it's a bit simpler to write. It's much faster to execute but you need
[05:01] to rely on cities to do that. So first you need to write your first city then the second one then a third one then a fourth one. then you make it a final
[05:14] query where you are handling all your predicates with uh your following events. So the performance is good but you see it's still difficult to to read to write to maintain
[05:27] um if you if you've done some uh some MCU it's very tough to to deal with that. But let's have another condition. Let's
[05:39] say okay you spent multiple hours to write this query and your business is uh phoning back to you and say oh actually I' I messed up my definition it's not
[05:51] two consecutive elements going down followed by two consecutive elements of price going up it's at least two consecutive elements of price going down and at least two consecutive elements of price going up. So you need to rethink
[06:04] your query once again and this is this is very hard. But now fortunately the year is SQL 2016 and the match recognize function has landed over SQL.
[06:18] So you have this this operator match recognize that now you can use following a table or a view or CT and basically you need you will set some data how you you will deal with the
[06:30] data. So you need to partition it by an element. You need to order it. It is very important. If you don't order your data, it won't work. And after I give you a little tips, you need to to read
[06:42] it bottom up. So you are defining some conditions. So here it's very simple. I have a done and up condition based on the price compared by versus the previous price. And I'm looking for a
[06:54] pattern which is at least to done followed by at least two up. And we can compute some more metrics. I won't bother you with all the details but this query solves the issue and the very good
[07:06] thing with this way of doing the pattern recognition using the regular expression syntax like if if your business change the definition it's very very easy to change you just have to change the
[07:18] pattern you don't need to change anything else so it's very very powerful so the tips partition by order by first then you go bottom up you running your
[07:30] define, checking your pattern. You have some uh some uh configuration you can tell about the output. We'll uh we'll have some example later on this and you can add some extern some additional
[07:43] information about what you want to output from this data set. If you want to get familiar familiarized with the function, you need to play with pattern. So try with a very basic
[07:56] example. The one which is in documentation is nice and try to think of anything. Okay, you want to to have all the consecutive ascending strings. So the pattern will be ups if you want
[08:08] to to see all the price going up but below 24 for the ramping up and the final one when it's above 25. You have the second pattern here with new
[08:20] definition you can introduce. If you need to find the Vshapes or the A shapes, which is the opposite, you can run the the pipe operator for the like to act like an or. So you run two done,
[08:32] two up or two up at least to two up and two down and we will see the the code running now. All right, let's see. So this is live as always. Very dangerous.
[08:45] So uh make sure we're not cheating here anywhere. So we use our cash off And then uh so we take a look at the data. Actually
[08:58] let me just show this one because inserts is not really that interesting. So we have a pile of data here. Right. This is the uh and this is how the shape of the graph. You see the the Vshapes
[09:10] here. Nice. Right. So okay. So running too many joins. Let's see how that goes. We don't have that much data. So we can actually do it.
[09:23] There we go. Okay, we got the output. But again, it's not a it's not a pretty query. So now we run the one with a CTE which should be faster and you see it keeps going and going and
[09:36] going going eventually we get there. Okay. And now here is the match recognize you to see. So much cleaner
[09:48] uh to see. All right. Sometimes it doesn't render. I don't know why. There we go. And you can go and as Saman was saying
[10:02] earlier when you play with the patterns, right? So in this case, we have uh we have just any number of ups, right? And we want to just show the end. So, we have one row per match that we
[10:15] return. There we go. So, we see all the all the streaks, all the all the bare market, so to speak, right? And uh here is one
[10:28] where we That's the last one that you described with the um one that ends with a 25. Oops. >> Okay. >> In this one, you can see if you just
[10:41] show a bit the query. Sure. >> Oh, sorry. You want to show the query? Yeah. >> Yeah. We have two functions here that we are using which is are the match number and the classifier which are functions that give you details about the match.
[10:53] So the match number will give you an ID of the matching pattern and the classifier will show you how your data was has been classified by by the enzyme.
[11:08] >> And you see this here. See, this one was the first part of the pattern. And then, sorry, this is the first sequence, the second sequence. And these are the the ones that matched. Yeah. All right. And now this guy here. So,
[11:20] this was one with the up and the down. >> I clicked the wrong buttons. That's me. I live in the shell.
[11:36] >> There you go. Try again. There you go. All right. So there we go. >> So this is the Vshape of the E-shape example. >> Yeah. All right. Back to the slides. >> So okay. So this first chapter was
[11:48] really to introduce you with this pattern recognition. Now we want to try to solve actual uh problem. So one very very popular problem uh in SQL is actually the gap and island problem.
[12:01] So what it is uh you are seeing your data and you want to aggregate a consecutive uh sequence of events. So basically you are trying to to transform
[12:14] a time series data set into a period oriented data set. We want to get together the down and want to get together the up. But of course we don't if you run just a basic group by here
[12:27] you will have all the done merged together. you will have all the up merged together. So this is the source data set and this is the target data set. So there is a bit more row here because it's aggregated. But we can see we have uh we
[12:41] have the same properties. We don't have the same level of detail but we have the same properties from the starting point ending point of the of of the previous data set. So how to solve this in uh in SQL?
[12:54] Actually there is two techniques uh that is that are involving window function. The first one is about having a bread detection and a cumulative sum. So you can see the break dete detection in the
[13:07] first city where I'm checking the price direction against the lag of the same price direction. If it's the same it will be false and it will output a zero. If it's different it will be true and
[13:21] then it will output a one. And then with this data you run a cumulative sum over this number and that will give you an aggregating point to run your group group by against. So you you can see in
[13:34] the final part of the query in the group by I am using this uh GRP column that is computed from the cumulative sum above and this is how you you can solve the gap and problem u with the window
[13:48] function. There is another technique and this one has a name. This is the tab toan techniques uh which means travelers something it's Japanese so apologies for
[14:00] Japanese speakers. Uh this one it's in performance it's more or less the same than the previous one. I like it because it's easier to write because you are running basically you are sub factoring two row number of events. You see in the
[14:15] window only the partition by is changing. So you have the partition by like your main partition by that you want to have. So here it's a simple column and in second one I'm adding the price detection and with this technique
[14:28] in one only one city you've get your GRP column then you can uh you can run your uh your aggregate against but again who are familiar with those
[14:40] techniques here please raise some hands one hands two hands yeah so you see it's it's not very popular and this problem I think you probably seen it in your data at some
[14:53] point. Now with match recognize again it's way just more simple to define. So you are defining two events new event where your your price direction is different from
[15:05] the previous one and the same where the price direction is the same from the from the previous one and you run this this pattern. So one new and zero to n same because you can have only one point
[15:18] in your in your data and that's it you're done again very easy to write very easy to run
[15:31] let's see the code in action now all right so caps and islands so this is the first one again pretty lengthy
[15:46] Okay, it's already done. We don't have enough data to really, but you don't want to make it too slow. So, this one again a different way. Same information.
[15:58] And here's the match recognizer. No, that's the match recogniz.
[16:21] Thank you. So now let's go into a new even more complex example. So this one is about sessionization and final analysis. So imagine you are browsing on a website, you are shopping on Amazon, you
[16:33] are putting items in your cart and the data you get from a from your provider doesn't have any specific session ID. You have maybe a user ID, maybe a username, maybe an IP, you have some
[16:46] string of events, a stream of events that you will uh you need to to run through, but you don't have any ID. So there is this technique which is called sessionization to give you an ID. So I won't show you
[16:59] the classical query because uh it's uh it's not a good fit for for this talk. But what we want to do here have a look at the data. You can see uh you can see
[17:12] the same same user ID uh doing uh doing some stuff on this uh on this shopping website. But we can see there is a break in the in the date. So where where it's highlighted there's like a 12 days uh 12
[17:26] days gap. So this is not the same session. He did something one day he did something he he went to his life and he came back again on the website to do something else. And this is important
[17:39] because you don't want to mix the data from the two session together. You want them to get independent. And actually obviously with the the topic you can
[17:51] write a match recognize to find the session. So you need first to define what is a session. So here for this example I'm saying if I have nothing happening for 15 minutes I'm considering
[18:05] it session. But let's say your your business definition is not this one. It's quite easy to change. Just update your uh your defined rules here. And the pattern we're using is almost the same
[18:18] as the gap and island that we run before in the measure output. I'm using this uh this match number function to to act
[18:31] like a session ID. So it's a session ID just the time the query is running. If you want to materialize, you can use that. But if you just need it for a quick query, it's fine. You can you can use this. And also something we didn't
[18:44] talk that much. Uh you can see the option all rows per match. So that means I won't aggregate the data together. I will just output all the data um at the
[18:57] same granularity as they are incoming but I'm adding this new match number function as a new column.
[19:10] And where when we are running the query we can see now we have when we have our break we have a new session ID column that is going from one to two because it's a new session
[19:25] okay something a bit more complex now you want to do final analysis so sess session is nice it's very important but you want to to check what is happening on your website so here you you are
[19:37] looking for a pattern you are you are checking for a specific behavior. So the one I'm using in this example I want to to check the the three events following
[19:49] which will be check begin checkout enter payment and purchase. So this is a a good pattern and what we are doing here we are chaining two match recognize uh sequentially and we can reuse the
[20:03] session ID that we compute for the sessionization and we can reinject it in the partition by on the second one as I said we don't want to mix together the two session because they are independent
[20:18] so here I'm just doing a CD computation I'm checking the the median and average time between the checkout and the purchase. It's just so this example fits on the slide.
[20:39] Let's go. Now you want to check for unknown behavior. Okay, previous example was quite uh quite uh easy to understand. Now it's different. We want to to look for what's happening
[20:52] when my checkout has began but we didn't end with a purchase. So there is this very good trick that you can use. You can define uh a pattern
[21:06] as a true. So it means any row will will qualify for it. So what I'm looking for in this pattern >> we lost the uh we lost the video for some reason. Yeah.
[21:18] We are back. >> Okay. Okay. >> What we're looking with this uh pattern is one event of checkout and whatever happened afterwards. I'm just making
[21:30] sure in a following city that there is no purchase element. So I'm running here. I'm running I'm putting all the events into an array into an array and I'm checking there is no purchase event in this array.
[21:44] And when you are running this so just for the display you can look for all the be behavior that has happened with a check out but without any purchase. So we can see the most popular one is just
[21:57] a checkout with no with a session ending right now and you have all the other examples here as a data set. This data set is nice. It's not very usable. So uh just for you it's quite
[22:12] easy to transform the previous data set to this one which is a sunki diagram and it's for your business user usually it's a it's a more better experience to have this visual versus uh versus um the
[22:26] table of events we had before. And now we'll see this running live. All right. So
[22:44] look at the data think. >> Oh s you're cheating. You have a session ID in your data. >> I have a session ID in my data. >> Yeah. >> Oh probably I shouldn't have that. Should I?
[22:56] >> Okay fine.
[23:08] >> So and there is your data. Okay. Fine. So we have this dirty data and we need to sessionize it first. So we use the match number
[23:24] as the inflight session ID. So it does all these groups and then puts them together. There you go. So there's our session ID. One, two, three, four, five. and then there is a new session
[23:36] >> for some other user and so there you go.
[23:50] let's go and reuse the session ID and that is validation. So that's the length of how long we are in the court. Is that what you're doing?
[24:02] >> Yeah, I think it's a the time the median time difference between the >> oh between the two sessions. There you go. Okay. So now we have you want to now chain them
[24:18] and we are getting now this information here. So now it's aggregated. So we know the lineage whatever you want to. So the the story basically that was taken here. So, how at which point do people abandon the shopping cart the shopping cart, right? Or when did they actually end up
[24:32] um Okay. And now you want to get it all ready to get into the graph. And there's our sinki.
[24:54] There's going to be a test
[25:08] Okay. Uh last chapter. Um this function is very very cool and for me when I fully understand how it works, it was like uh when the time I discovered the window function. You remember this time when you discover the row number over it
[25:21] was cool, right? Yeah. And honestly, this is the same with mat recognize when you can do pattern analysis. The good thing with this function is you can be super creative. So, let me show
[25:34] you a real example we had at a customer. So, here we will be talking about quotes and trades. Sometimes you have some trades happening
[25:46] and before the trades you have any number of quotes could be one could be zero could be could be 10 we don't know when you want to match to match all the
[25:58] information together you want to have the information of the trades and you want to have the information of the last quotes or maybe some information about all the quotes. Usually what you're doing today you are computing you are
[26:11] taking your your trade time stamp you are checking when was the previous trade with the like function and then you are running this join which is a left join
[26:23] from the trade to the quotes with a horrible inequality between the quote time stamp versus the trade on the previous trade time stamps. It's first it's not easy to not that
[26:37] easy to write but also the performance you will see I keep that for the demo with match recognize because match recognize is about analyzing patterns
[26:51] be creative here what I'm doing I'm saying okay let's say let's assume my quotes and my threads are one stream of events of course I don't have the same columns in both tables so I have I can
[27:04] union all the data and put some nulls where it's mandatory to have like a super data set of both quotes and threads
[27:19] and once I have this data yeah you're looking for a trade and whatever codes you had before so the define is very simple the pattern is very simple so here the measures it's more for the example you don't need that
[27:31] but let's say you want to comput stuff about about the thread and the quotes. So you will do that in the measure but basically that's it and let's go to the demo part.
[27:51] All right. So this is a lot of data. I want to show the first thousand. There you go. All right. So all these buys, sells.
[28:04] >> Yeah, this is this is the example data set from from the quotes. >> So >> Oh, it was from the tread. Sorry. >> Yeah.
[28:21] Okay. So, and now we can take a look and see how much do we have in there. All right. So we have >> about
[28:33] >> 10 million >> one million threads and 10 million quotes. Okay, for this example. So this is not a 20 rows data set. That's >> that's about that >> decent stuff. Warning, don't run this query. >> Yeah.
[28:45] >> Yeah. Okay. We only have Q&A at the end. So let's not run this query.
[29:00] this over here. >> You see this query? >> Yeah. So this is where the time goes. >> Yeah. >> So which is not entirely shocking.
[29:26] noodle. >> So the the previous query that we run before the session was about four minutes. So it's a long time to wait. >> Yeah. So this one took what 5 seconds, 6 seconds, something like that. >> Okay. Not too bad.
[29:39] >> You can choose the cor profile as well. >> I can show the query profile. I can do that.
[29:55] There we go. profile is not available. >> Yeah. Okay. This happens sometimes. This this is a live demo. >> Yeah. Code code still not complete. >> But basically qu profile there is no joints. There is a union and all the
[30:07] data from the union go straight into the mat recognize function. >> Right. All right. So shall we do the bonus thing or we have nine minutes to go Q&A? >> We can go we have a bonus chapter for
[30:20] you. >> All right. Let's do that. Okay, I'll let him talk. >> Yeah. So, as I told you, you can be very very creative with this function. So, do you remember the chapter one chapter
[30:33] chapter one Vshape example? >> This one. >> Now, we want it to be a bit more complex. We want to find the W shapes. So, consecutive streams of doning elements price with up
[30:48] with down with up. So, it's a bit more complex. So we say, "Oh, okay. I just need to adjust my pattern." So this is what is in this example. We can see we've put the the done plus and
[31:02] the up plus together and we we want exactly two of this pattern here. So let's look at the data. >> Sorry, the data. >> Oh no, the is nice. Yeah.
[31:15] >> So we can see we we found one W shape from the five to the 13. Maybe go back to the data set. I wonder something. Oh, to the show the graph.
[31:28] >> The graph. Okay. >> Oh, are we sure we have only one W chip here? Actually, I I'm seeing two of them. >> One W. They're overlapping.
[31:40] >> Exactly. There is two W here, but it's just one is starting when the previous one is not finished. They they can be overlapped. The W check >> the unique character like two W's
[31:53] together. >> Exactly two W's together.
[32:07] >> What the heck happened? >> I have no clue. >> It's French. Don't want to take on this. >> We can try.
[32:19] >> Oops. Oh yeah. Not my Touch ID. Oh, it works for me. Anyways, just kidding. >> What have you done?
[32:34] right. >> Not this one. >> More French. >> More. >> What's your language? >> Absolutely.
[32:51] So thanks to Chloe, we're spending like two minutes coding and 90 and the rest of the 58 minutes logging in. >> So genie code. Genie code. Well, I don't that one.
[33:03] >> Okay,
[33:15] >> We were here. Let's see. >> Please run this one. Don't mess it up. Search please. I >> I try my best. What can I say? Okay. So we were describing this here. >> Yeah. >> Uh yeah. >> So here I'm using you can run the query.
[33:28] >> We can run the query. Okay. You sure this going to work? >> Okay. And show show the results. >> And now we have found our two W uh chip
[33:40] curves. What I changed between this pattern and the one above, it's the same pattern. The thing that has been changed is this after match skip
[33:52] uh phrase. So here in all the previous patterns we showed up to you, we are going to skip next rows. So it means when you are finding a pattern, you are starting where at the pattern after the
[34:06] pattern you have found. And now you you can be okay I have found a pattern and I want to go to the next up element. So has the up is inside the W shape. This allows you to run some overlaps. So the
[34:20] same row same row set can qualify to multiple patterns. >> So like a rolling window. >> AB. Yeah it's Yeah, exactly. It's like a rolling window. And with this you can also get very very creative.
[34:35] Okay, thank you. Um, okay, fair warning because you can do a lot of stuff with metricalize. I just want you to don't be silly. So, here you have like the the most basic
[34:47] query. You know, this is just an average on a table with a simple group by actually you can you can transform that into a match recognized pattern. We are just running uh one event which is true
[35:01] and we're looking for any stream of events of this and compute the average into the muser. Okay, you will get the good answer but probably keep it classical. It's more easier for your
[35:15] colleagues to work more easier for a lot of people. I have another example uh which is a tad more complex. This one is um it's um
[35:28] how do you say it? It's a it's a range window function where you are looking at your current row up to the three next row to get to get the minimum price. So you want to have the minimum price
[35:40] between now and in the in the following four days. You can also run that in metric recognize zoom >> with this pattern. So again we are
[35:53] putting like the true t as true as a definition and we're checking for uh t up to four elements. So you can do this in mat recognize it
[36:05] will work but we have the proper function that are more known. So you cannot just just use the proper function when possible.
[36:24] Why pattern over rows is important because now I think you have all attendance the cannot we are doing everything with Jenny Jenny is great J does everything but as I as we we we told you this function is out there since 15 years
[36:38] it's on most on competitors so it's nice we are getting it in data bricks but almost no one use it so you you need to get familiar yourself to understand it
[36:51] and also to write some codes to find some patterns. As I told you, you can be very creative. So there is a lot of way to solve stuff with SQL and especially with this function and the more you will do that, the better the agents will
[37:05] learn from it and you will have more better experience out of the box with Jenny. And also if one day the Gene output query is incorrect and you need
[37:17] to debug it, it's nice you have some basic knowledge of how this works so you can try to to debug it yourself. All right.

Learn more about the Databricks Data and AI platform.

The information provided herein is for general informational purposes only and may not reflect the most current product capabilities or configurations.