Cooking With SQL: From Ingredients to Insights With Minimal Prep

Summary

• SQL technique mastery: This video covers advanced SQL techniques including recursive queries, temporary tables, and stored procedures that help optimize data preparation and analysis.
• Practical recipe collection: This video builds a go-to cookbook of SQL techniques and patterns that professionals can execute immediately or store in Unity Catalog for reuse.
• Efficient data transformation: By combining SQL staples with modern techniques, teams can reduce data preparation overhead and derive insights more efficiently.

Cooking With SQL: From Ingredients to Insights With Minimal Prep

Talk by Fabien Contaminard, Sr. Specialist Solutions Architect DWH, Databricks ; Serge Rielau, Principal Software Engineer, Databricks
In this video we’ll dive into the SQL kitchen and use a combination of SQL staples and nouvelle cuisine such as recursive queries, temporary tables, and stored procedures. We’ll leave you with well-scripted recipes to execute immediately or store for later consumption in your Unity Catalog. Think of this video as building your go-to cookbook of SQL techniques. Bon appétit!
Here's more to explore:
The best data warehouse is a lakehouse: https://www.databricks.com/product/databricks-sql
How to migrate legacy data warehouses: https://www.databricks.com/resources/ebook/migrate-your-legacy-data-warehouse-databricks
See all the product announcements from Data + AI Summit: https://www.databricks.com/events/dataaisummit-2025-announcements

FAQs

What are recursive queries in SQL?

Recursive queries allow you to write SQL that references itself iteratively. They're useful for traversing hierarchical data structures or performing iterative calculations within SQL workflows on Databricks.

How do temporary tables improve SQL workflows?

Temporary tables let you break complex queries into manageable steps, improving readability and performance. They serve as intermediate data storage within a session, making complex transformations easier to manage.

What are stored procedures and why use them?

Stored procedures are pre-written SQL routines that can be called and reused. On Databricks, they enable you to encapsulate complex logic, promote code reuse, and store recipes in Unity Catalog for the team.

How can these SQL techniques reduce preparation work?

By combining advanced SQL techniques, teams can write more efficient transformations that reduce manual data preparation overhead. The cookbook approach allows reuse of proven patterns across analytics projects.

Full transcript

Thank you everyone for joining this session. So session is about cooking with SQL from ingredients to insight with manual preparation. So I'm here on stage with Serge Row. So he's a chief potato pillar and I'm I'm the pitch for this session. So S tell me more about you. Sure. Yeah. So I've been doing SQL like forever since I was like mid mid20s which was a long time ago and I started off at IBM in the research center learning from like the co-inventors of SQL and did 15 years at DB2 as SQL architect uh short stint as chief architect at open pages another five years Salesforce and here I've been for five years now as well uh and I'm what's lovingly called the SQL zone bar here which means anything that happens on the SQL front goes through my desk.
It's uh if it's SQL centric features I typically lead them and if it's features that happen to have a SQL surface somehow I I consult. So that's me. Oh yeah and I'm not allowed to cook in the kitchen at home. So this is the only chance I have is cooking. So yeah so I'm a bit younger than S but not that much. So I've been doing only SQL in my career. So this is uh what I tell what I say about me. I have one skill. It's SQL.
So don't don't talk me about Spark AI. It's fantastic but it's just not my uh my skill set. Just previously before joining data I was in Vertica. I was technical success manager and before that I've been in Terodata doing professional services and actually all those jobs requires a lot of SQL and um I've been working uh with Oracle database. I think the first version I came up with like was like 73 something and uh SQL server both Microsoft and cybase my SQL possql and after I had the chance to move to the MPP world with green plum then terata and vertica fantastic database but now it's all about data intelligence platform so the one on unique data bricks so we have a bit of the menu alatab um this is this is examples that we will cover today.
Uh so we try to stick around the around the seam of the of the cooking and we'll we'll see how you can optimize a Greek salad preparation. So this is a Greek salad optimizer. So, let's say you have multiple ingredients to make a beautiful Greek salad, but you to chop your ingredients to keep your uh your kitchen clean, you need some times in between ingredients. And uh actually, you want to be the most efficient possible.
So, you want to minimize the amount of time it will take for you to uh to prepare this uh this Greek salad. So to be able to handle this we are creating like a table to uh to get all the edges with the time uh that it it takes to move from one ingredient to another and we will do a very naive algorithm that you will try every combination possible and just pick the fest one. So basically this is this is a data set. So we are creating a table.
I have six ingredients that I uh self join just eliminating uh the same ingredients and I put a random number as a time to preparation just for of course for the demo. So this looks like this. This is just a symbol of the data set and then we're building we're building this query. So this is a new feature in data bricks. This is a recursive query. So basically the upper part of the query is just selecting all the all the ingredients getting them into an array to make sure that uh I know what ingredients I've been dealing with and after I'm going to union a new data set with a self join from the recursive city just on a new ingredients that is not already in my list.
And at the end at the bottom of the query I'm just uh selecting everything. So there is a work condition. I want to pick the recipe where I have uh I've run through all the ingredients and I'm sorting them by the total time ascending and we can just actually get to one. So let's see the demo with this. So it's the other way around. Yes, it's actually it's hard to cl. Okay, so this is the same exact code that I've shown you in the in the slides.
Of course, because there is some random numbers, probably you won't see exact the same uh the same values in the data set. Don't worry, we'll get there. Okay, so this is the data set. So this is an example. So if I'm already baking cucumbers and I want to bake tomatoes to chop tomatoes, it takes 26 seconds just to cleans the knife or something. I think you you get the idea. And now we'll be running the recursive query.
So this is exactly the code that I've shown you in the slides. So this is a previous run. But let's uh let's complete the query. So you know recursive query is u I was quite surprised when I joined datab bricks that it it wasn't there because it's uh it's been in uh in other database this forever and now we have the feature so I'm very excited about it and we see here actually we have two uh two candidates for the fastest way to prepare your Greek salad.
So we can use this order of ingredients or this one both takes 80 seconds to uh to shop shoes. So yeah, so in my example it was 76 seconds. So probably the random is close enough here. We'll go to see a bit more complicated example here now uh with a with a cake. Imagine I am a birthday cake company and due to some reasons I I am limited in my egg supplies. I have only 250 eggs at my dispos disposition. But this is a busy weekend and I have lots of orders that if I want to fulfill them, I would need 5,000 eggs.
So each cake are being unique here because they are those are birthday cake and each cake I can sell it for a certain price and it also requires a certain amount of the eggs. Let's just focus on the eggs. Let's say I have no issue with other ingredients with the weight with the with the milk and stuff. So actually this problem is not that simple to solve. This is the 01 naps knapsack problem with which has multiple exact solution.
And uh to be fair if you if you had to tackle this problem the first thing you do will probably be the first one the first algorithm here which is called the gluten algorithm. Basically you are sorting all your all your cakes by the division of the price by the number of eggs to have like the better candidates first. But when you fill up uh all your combination, it could be a good solution, but it it could also be a bad solution.
Uh usually it's a good enough solution, but let's find out the exact solution. So usually we solve that doing dynamic programming. So dynamic programming maybe you you saw that at school or in Kaggle or in somewhere. Usually we we saw this algorithm in in C or Python or probably every every language and actually I will show you how to solve this in SQL. So what we need for this? So we need a table that uh have all the information I need.
So an ID, a name of the customer because it's nice to ship the good cake to the good person, the price I of this cake and the number of eggs. I will use also for loops. So I'm using SQL scripting here. So another new feature that arised recently in the platform and just a SQL transformation command. So let's have let's have a quick look at the at the data. So again the sample there is a lot of random this is just to generate a sample.
So of course the customer customer name here are a bit junky but you have the price you have the number of eggs. So that looks like like this. So this is the glutton algorithm. So here basically I'm just selecting all the cakes. I'm ordering them so by price/ eggs in a descending order. So this is a window the window part of the order. And after I'm taking the sum of price over the same window to find out the output and also I can aggregate the ID to know uh what cake should I uh should I should I bake.
I'm ordering in the by the total price in the descending order because I want to have the best price. And here my press on this example is 2, and 700 or and some stuff. So with dynamic programming, so it's a bit different approach. We are first building an array of 251 values. I first fill them up with a lot of zeros just ination of the algorithm. And after I just pass one row at a time the component of my table. The order here does not matter.
I I have no order by close here in the for loop. it has no uh no in incidence on the output of this algorithm and higher here after I'm transforming the array of values and I'm replacing the zeros by the by the value of the price only if the number of eggs is uh is below the current number of eggs or the price is better if the number of eggs is the same. So this this is it. This is this is uh this is the code and with this solution I have close to uh to 2,900 revenue.
So let's run the demo. So just so you know I'm showing you the code. I'm not running it because sometimes because it's built with random functions sometimes the gluten algorithm shows the best solution. But this is not the goal of this example. So uh we we've preid the table and I've made sure gluten algorithm is not the best solution. So this is this is the data set. So it's a bit different from the one from the slide because it's an another table.
So here we are running the gluten algorithm. So it's it's really a quite simple query. So on this new data set, the price here is uh 3,400 on and some leftovers. And now we we are running here the dynamic programming. So it takes a bit longer because we have to process the rows one at a time. Um so probably if you have like millions of possibilities, we should maybe rethink a bit the approach. But uh it will go it will go to the end in uh 20 30 seconds.
And uh just at the end here I'm selecting the last value of my array. So I have my array. In the array I put the price and the list of the ids of the cakes and I updating them with a transform function with a case statement to make sure it's only a good a better a better fit at this time. and just looping over on the we have just to wait for the end it will uh it will come along. Did did some of you did already run some dynamic programming in not necessarily in SQL but in other language?
Yeah, I have one here, one over here. So, okay. So, actually you're learning something. That's cool. So, no. Okay. So, now I have my solution and you see my solution is better than the gluten algorithm. So, the other one was four uh 3,400. This is 3,600. So by a small query and 20 seconds we earn $200 and that's it for me. All right, that was great. I'm just feel sorry for all these kids who don't get cake all in the name of profit.
Another way would have been to maybe optimize by the number of kids get get happy but hey we are a business. So all right so the next problem we're going to solve is one of alphabet soup and um so here's our so we are this fine dining restaurant right and we have international customers they're coming from all over the world not everyone is named and Jones and we have international cuisine not everything is mashion burgers and the likes and fries so um what we have discovered is that our staff has trouble finding guests when they come and they try to get a reserved table and they say their name, the staff types them in and they can't find them for some reason.
This causes a bit of confusion. People's complaining and stuff. So now we we have talked to our tech support probably some 15year-old intern who's also working in the kitchen and who knows about computers and uh and he said the problem is one of collation. So these international guests, they have like all sorts of accents and other interesting characters in their names and some of our cuisine is inspired or something Slavic and comes with interesting names as well.
So and we need to actually go and update our database and make all these string columns uh case insensitive as well as accent insensitive accents covering everything from accents to uh like other interesting like German um and so forth. So what do we need for this? So I already mentioned collations. Collions is a feature that came in uh I don't know six months ago or something and um it's in public preview. Um and what this allows us is to customize the way how we sort and compare strings.
So by default um data bricks um collates everything in UTF8 binary which basically just uni code points and that means all the lowerase characters are coming before the uppercase characters and then after that eventually come all the E with all the different various squiggles above and then come the German umlouts and then come the cerivic characters and it's all a big mess right so and when you compare them of course an an an e with an accent goo is not the same as a regular e and so and so on and so forth.
So um what we need there is we need to actually go and teach data bricks to go and uh use a uni code collection that is case insensitive and access incentive which means that these things are the same. The other thing that we need is we are going to use dynamic SQL. Uh because what we're going to do is we're going to take strings and call and and combine them like glue them together and then take the string and execute it as a statement which is something that you would do all the time in Python with like spark. sql give it a string but here we can do this within SQL.
So we use SQL to build SQL to then execute it. Okay. Lastly we use the information schema and the information schema is something that has been around for a long time. Uh it is kind of like similar to describe and show but describe is a problem that it gives you a fixed report. Information schema has the ability to give you just about data metadata in a relational form and you can uh then go and pick things out within SQL again without having to resort to Python and the likes.
So and then we use SQL scripting. Um SQL scripting is a is a new feature that's in public preview just for a few weeks now. um and already like started using it in his um in his cake example and we're going to dive into this one in a bit more detail. So um the formal name for SQL scripting is SQL SPSM that is the name of the language in the ANC standard. So it is a public it's a it's a standardized language has been around for quite a while. uh was originally I think designed by who worked for Cybis at the time and then ended up at Oracle.
So and the first ones who actually implemented it was probably my team when I was an intern in 1996 in DB2. So but it has been used. It's also used by my SQL. It is largely used by terod data. It is very much so also Google SQL. They have done some smaller changes but overall it is something that has been getting more and more popular. So it's an it's an open open definition of a procedural language very similar to TSQL and PL/SQL in the sense of like it's a different dialect.
So all of that is SQL um but this is the formal standard we are following because we always want to be open. Okay. So inside of uh SQL PSM the features you have is you have local variables right which is kind like the basics. Uh you have a compound here and you can say begin and then put stuff inside local variables. You have the for loop only use that one. Um you can also have loops like generic forever loops while repeat and then you can control the loops.
You can uh exit out of a loop prematurely when you think you're done or you can iterate by basically shortcutting to the end of the iteration and then repeating the next one. We have multiple ways of doing conditionals. So case statements if then else statements all the usual stuff and we have condition handlers. So condition handlers are basically your try catch. So you can say hey if I see this specific condition this specific error message then take this action and you handle the error or you reignal and you pass it back up right and uh signal and reignal two ways errors which means you can now do procedural logic without having to resort to python java scal or any other host language if you're already a python programmer and that's what you like perfect by all means stick with it if you're a SQL person who says I don't really want to learn Python now you have your way of doing that and you can do it all in dbsql in in the warehouse, right, where you don't have access to these other languages.
Okay, so um just a quick overview of what's going to happen. Um we are going to uh first grab uh we're going to loop through all the tables in a schema, right? We don't know how many we have. And we are going to grab the table names and then glue together a statement that changes using an alter table statement that changes the coll the collation for future columns um to uni code um case insensitive access insensitive.
So that would be an altra table and then set the default collation. Then we are going to iterate through all the columns with within each of these table and glue together alter table statements to alter the column collation that is for the existing columns. The default collations for the future and the uh and the next one is then for the existing columns. We're also going to collect all of these columns together into an array. all these columns in an array because we want after we have done that we want to tell delta to please run statistics so that we actually get statistics based on this new collection which means things that were not the same before and now suddenly the same which changes cardalities and number of distinct values and so so forth right so and then we're going to do this as I said for uh all the tables in our schema and let me just go straight to the three fingers there Okay, let's hope we haven't lost the cluster in the meantime.
So, we're going to create we have some some standard some simple thing. We have like someone with a German name, someone with a Slavic name and a uh and an English English name. And we have also a cuisine like some German cuisine, some cuisine, and uh some good Serbian shapi. Um what happened here? Did I lose my One sec? For some reason, it got me out of the screen. There you go. Okay. So, we have done this and when we run the query here and we're trying to find these users, the these these customers, they cannot be found, right?
Because all we did was we had either the wrong casing or we missed an um or we missed uh these other little accents. So, now let's look at the script here. So, you see here there are some uh local variables, right? It's not nothing very too too exciting here. Um we set the collation uni code accelative um case insensitive. Here we are doing our loop over the tables. We only want tables. We're not going to look at views.
Would blow the example out of the water here. It makes it too big to also deal with views. And then we are going to uh glue together this alter table statement, right? And um use this together, execute it. So now this part is done. The future is safe. We have a nested for loop here. Nested loop. And um we are again finding all the all the columns now within that table that we're currently looking into. Right? We find want to find all the string columns.
We do a we do a set again. We we glue together this string with the alter table statement. Execute that. And then we're going to add the column that we found into an array to collect them all together. And then after this loop through all the columns, we're going to take a look and say, did we actually find any string columns? If there are no string columns, there's nothing to analyze, right? If there are string columns, then we run an analyze table statement.
And for that, we're going to use a lambda function, which basically unnsts the array into a commaepparated uh string with all the column names. and uh then execute that on analyze. So let's see whether that works. Permission denied. How that still failing doesn't have select. Look. Okay, I messed this. I'm I'm I need to do all these things. I'm not going to do this now. We don't have enough time. Okay. So, um what we should have gotten now is it would would have changed these things and then this is a previous run.
I don't know. Um I I was a different user then. Um so the um and then it would give me uh all the three names properly. It was also it would also order them properly. So the D goes before the M goes before the S and this particular D otherwise would be at the end. We went through this. Okay. So now the next one is we're going to make peanut butter jelly sandwiches at least theoretically. Okay. So the problem that we have now is uh our kitchen helper who probably was our IT support um is tasked to collect ingredients for orders.
Okay. And these orders um they can be in various places, right? The ingredients are stored depending on what they are. They might be in a bread box, they might be in the pantry or they might be in the refrigerator. So we have different sources, different data sources, right? And occasionally what happen is as we collate our stuff, we find out we don't have enough inventory. In this case, we have to basically put everything back, right?
So, we are going to make sure that this is a transactional thing. So, what we'll need, we want to use temporary tables, our our basket, so to speak, where we collect everything in um which collect trends in data within our session. We're going to use explicit transaction aka multi-statement transactions, which can undo our actions if we fail halfway through. So, we don't have to do a logical undo and like remember what we did, undo it. we can just go and uh and just let the failure cascade and it will just make it as if nothing ever happens.
We're going to use the identifier clause which is a a more safer way to do dynamic SQL. It's more like parameterized SQL. I'm going to explain that and then we're going to use that signal that we introduced earlier. So temporary tables are at the end of the day managed delta tables. Okay, so they have all the power that comes with delta like for example liquid clustering but they live in a session private schema. So they don't live in a schema that we defined in UC.
There is a virtual schema called session and that's where they live and that is private to us which means anyone else can create it here with the same name and they will not collide with us. Also if we have another session running the same the same scripts again with the same name they don't they do not collide. Okay. And uh it's a great way to hold intermediate results. It's a great way to hold results that you want to then reuse multiple times and then later on clean up.
So um and they are automatically dropped when the session ends. So you don't have to worry about clean up. What if my session for some reason you know my my my session crashes or something else happens. I don't have to go and then mop it up and come up with unique names. So it takes care of itself. And the only thing you have to do is say create temporary. That's the only difference compared to a normal data table. So that's the extent of it.
Okay. The other thing multi-statement transactions. So it's typically in uh in data bricks every statement is a transaction itself. So we auto commit. You do an insert committed. You do a delete committed right? Everything commits right away. And in this case what we're doing is we're basically saying when you say begin you attach atomic to it. Today we're just going to do that. etc. It means everything in this compound statement will either live and die altogether.
Right? So, and the semantics for this are going to be repeatable read, which is if you are selecting from a table once and then you select from the same table again, you will get exactly the same result set throughout the duration of the transaction. Unless of course you update the table yourself. So, if your session updates something, you can see your own changes, but you cannot see changes of others while the transaction is ongoing.
So here's a comparison. So I do very simple. I just do an insert into t values one. Another one that gives me an error because it's a division by zero error. On the left side, the first insert never happened. On the right side, the first insert happened. It committed. The second one failed, but the one is still there. Okay. Um we will so this is currently in private preview. Um as we progress through implementing this feature, we will eventually get to top level begin work and then commit and warback statements.
So you can use this in Python and whatever to just run individual statement and control fully how these statements works. Okay. Identifier clause is um you have um basically you take a string literal but instead of turning a string lit into a SQL statement you turn it into an identifier which is a reference to an object. And you can do this pretty much anywhere in a query for tables, schemas, catalogs, catalog names, columns even function name.
You can write query queries that have parameterized function names. Okay. Um here's an example. You have declare v equals t and then select star from identifier v and it will then substitute the v with t resolve that and then execute it. It's much safer than on the other side where you have execute immediate. Right? Let me just stick with the slides in the interest of time. Um so um what we have here is we have our bread box.
Um we have a fridge, we have a pantry, we fill in data, right? um including our recipe for the peanut butter sandwich. Okay, so that's just setup and then when you run it, we see here so there is the temporary table that we created. It's begin atomic uh that says everything happens together and then uh in here you see the use of identifier where we basically using we're using the query here to receive the location of where the item is.
Right? So if it says oh okay so um the strawberry jam is in the fridge so therefore we get fridge as a result we run the select over the fridge and then if the um if we don't have enough in our inventory then we give a signal okay we raise an error and uh that then works like this and we can't go okay let me see if I thank you I give it a What? Okay, there was our kids manual. Let me assume in the interest of time, let me skip that part.
We trusted that. Let me do the temp permission denied. Damn it. Still not something is not quite right. Okay. Um, okay. So, we have our this was what we did. So, the idea is here we go now forward. We skip ahead. We have done a bunch of peanut butter jelly sandwiches and we um run low on strawberry jam, right? We only down to like 20 grams of strawberry jam. We are um executing this again and now we're getting an error user raised exception story jam, right?
We're out of story jam. Okay, fine. Okay, so last last step. So we have done all these scripts, right? because they're they're they're complex and things can go wrong like we just saw. Uh so ideally we want to get in a situation where we have consistent quality, right? So if our line chef gets sick or something else happens, you want to make sure everything is always repeatable, right? So what we want to do is we write down our recipes and codify the execution.
So for that we need SQL procedures and SQL procedures are written like this. So you have um they are in um Unity catalog. They can have in out and inout parameters. They can have result sets. They contain any SQL script or SQL statement. You can grant access to them. So you can control who can refer to them. And uh you use a call statement to execute them. And here's a simple example. Create a procedure. This is the dot dot dot just the previous example.
I'm going to show that in a moment. And then you call um here you see the create procedure um and we use today SQL SQL security invoker which means the invoke of the procedure is the one under which author authority you execute but we will change that in an upcoming drop and make it definer right which means the definer can control uh what's happening inside and then what you see here is we do a we do an invocation um and if it works it works if it doesn't then everything gets rolled back as as for and that is it.
I apologize for demo problems. That's just what happens when you do live stuff.

Learn more about the Databricks Data and AI platform.