Elevate SQL Productivity: The Power of Notebooks and SQL Editor

Summary

SQL Productivity: This video showcases SQL techniques including recursive queries, temporary tables and stored procedures for building advanced data operations.
Developer Experience: Learn how Databricks notebooks and SQL editor improve productivity through new features like recursive queries and stored procedures.
SQL Kitchen Metaphor: Building a cookbook of SQL techniques enables data professionals to execute immediately or store recipes in Unity Catalog for team access.

Elevate SQL Productivity: The Power of Notebooks and SQL Editor

Talk by Jason Messer, Senior Product Manager, 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

Chapters

FAQs

What SQL techniques are covered in this video?

This video dives into the SQL kitchen, exploring recursive queries, temporary tables, and stored procedures as well-scripted recipes. These advanced SQL techniques enable more sophisticated data operations and transformations within the Databricks Data and AI platform.

How do SQL notebooks improve developer experience?

This video demonstrates how Databricks combines SQL editor and notebooks to streamline SQL development and data analysis. The integrated environment enables data professionals to write, test and execute SQL efficiently while building reusable code recipes.

How can I query my data warehouse without writing SQL?

Genie AI and similar tools enable business users to query data through natural language questions instead of SQL. These assistants generate and execute SQL automatically, making data accessible without requiring database expertise.

How can business users analyze data without writing SQL?

Databricks provides multiple no-SQL interfaces including Genie natural language querying, visual SQL builders, and semantic layers that abstract technical SQL complexity. These tools enable business teams to self-serve data analysis.

What are advanced SQL patterns for data analysis?

SQL notebooks support window functions, common table expressions (CTEs), and advanced aggregations for sophisticated analysis. Databricks SQL notebooks combine interactive exploration with reusable, productionized SQL for end-to-end analytics.

Full transcript

`0:00` Session introduction

Hello Brricsters. Thank you so much for joining us all of you at home. We're so excited to see you back here. I'm a senior solutions architect at Datab Bricks. We're coming to you live from San Francisco. I'm in room 208 right now. We're getting really excited to see another great session. We're going to talk about elevating your SQL productivity. But before we do that, I want to take a quick minute just to take a look at what we have on the schedule for tomorrow.
So, we will have some breakout sessions for our viewers at home tomorrow. We're also though going to kick off with our keynote. So, we're going to have some really exciting announcements, more awesome announcements tomorrow about the data intelligence platform enhancements. We've already had a few sneak peeks today. And then for those of you who are here on site, please visit the expo. We have all of our sponsor booths.
Some amazing partners are here with us. You can participate in one of our lightning talks or go through our brain date discussions, something new that we've added this year. And then we're going to throw it over to our breakouts like we did today. And so with that, I'm really excited to present this next speaker. He is a product manager in our developer experience team. Developer experience is one of our most important pillars at Data Bricks.
If it's not simple, it might not be easy, but we're going to keep it simple for you. And he's going to show us some enhancements that we've made to the SQL editor and in notebooks that are really going to elevate and accelerate your productivity when you're working within SQL within data bricks. So, with that, I'm going to toss it over to you. Take it away. Awesome. Thank you for that introduction and thank you everyone for coming.

`1:52` Strategy and philosophy

I know it's end of the day. We're probably all a little bit tired. Uh there's a lot of energy throughout the whole conference and so I know I'm personally a little bit tired. So hopefully um I can make it worth it for you for coming tonight. Um if it's worth any consolation, uh my wife and I, we had our first baby two months ago and so I've been on paternal leave for a while and uh Oh, thank you. That's so nice. But uh while I was uh up, maybe I shouldn't say the hour, but you know, a lot of times at two in the morning, I had a lot of time to think about this talk.
And so uh you know, I I have a lot of things I'm super excited to talk to you about today. So hopefully I can make it worth your while. Um again, as kind of mentioned, I'm going to talk about how we can elevate our productivity um on data bricks when writing SQL. And so my my high level goal is I hope there's one thing that um you all take away that you didn't know before. Uh one little tipper trip that I can show you.
So with that, uh this is a forward-looking statement. I think you've probably seen on all the sessions. Basically says if I say something today that doesn't come true, you can't hold me accountable for that. Hopefully, uh that doesn't happen. But I got to show the slide. Um and then I will just say beforehand uh the surveys that they give you to talk about the sessions, they actually do really help us. And so if you have the time, would love if you filled out the survey and let me know how I did and what we what you've liked and what we can do better uh in the future.
So with that, let's uh just jump to the agenda. There's three things that I want to do today and uh you can just jump to see ahead. Uh I want to spend 35 minutes doing a live demo. So uh that will hopefully be exciting for everybody. Uh but before that I want to do a quick introduction to writing SQL on data bicks. What are are our goals? What is my goal as a product manager for SQL authoring uh with SQL on data bicks?
Then I want to talk about uh the kind of the two main services that you might use to write SQL. So the notebook and the SQL editor. Um and then yeah I want to jump into a live demo. Um so with that let's jump in. So writing SQL on data bricks um at a high level my goal when creating SQL authoring experiences is to create simple and powerful experiences. And really what I mean by that is I want to create simple experiences that are easy for people who aren't as familiar with data bricks and who don't know SQL as much to get on and get onboarded and really get insights out of their data.

`3:45` Service overview

So we're we're really trying to make it easier to onboard with more powerful. Um, I know there's some of you I've talked to you today already, but you've you've mentioned how you're in data bricks for four plus hours every day writing SQL. We want to make you more productive in everything you do. And so we're trying to really raise the bar and make you more productive uh with some of our features and experiences. We do this with kind of three core pillars.
So the first is with an approachable yet powerful UX. Uh we've spent a lot of time over the last few years kind of really trying to dial in our UX to make it simple um but also uh powerful like I said I guess. And so, you know, we want to really uh dial it down so that you have the editor code that you're looking at is kind of core to what you have and the output box is core. And then we're kind of tucking away some of the other more uh less used features.
And so, um we're also like while trying to tuck away those, we're trying to make them accessible with things like the command pallet uh which helps you to access uh those more powerful features. Um the second pillar is collaborative by nature. So we know you often don't work alone in your uh data explorations and often you need to share those insights with other people and so uh you know with both the new SQL editor and the notebook they're collaborative by nature support real-time collaboration have uh things like commenting and are also within the data bricks ecosystem which are very easy to share.
Now the third pillar is we're infusing all of our experiences with intelligence and you know I think you could some people might say oh it's just like AI but we're doing more than just AI to infuse intelligence into these experiences. One example I'll talk about now that I'll show later in the demo is like personalized autocomplete. So based on your usage of autocomplete, the tables that you use, your favorites, we specially rank the autocomplete algorithm to give you those tables right when you need them.
And so it's more than just AI uh that we're infusing into all the product experiences to really help you get more out of them. I couldn't talk about infusing things with intelligence without uh doing a slide on the data bricks assistant. And so just want to just call this out briefly is I like to call this your contextaware SQL companion. And it's like uh like other LLMs, it helps you write code, but what makes ours different is that it's context aware.
And so it knows things like the code that you've written before. It knows the code that your co-workers have written. It knows the cells that you're working with. It knows which tables are popular. And it takes all of that to in the moment when you need uh to have a suggestion or you're not sure what to write. Uh you can hit a keyboard shortcut, pop it open, and it will give you an answer um that will hopefully um be correct.
And I I'll show a lot more of this and hopefully my answers are correct. uh too, but LLMs, you know, they they have a mind of their own sometimes. Okay, so those are kind of the goals that I have uh when creating SQL authoring experiences. Now, I want to talk briefly about the SQL editor and the data bricks notebook. So, these are kind of the two core services that you might use to um author SQL and you one of the questions I get is why do you have both?
Like why do why do you have a SQL editor and a data bricks notebook? Aren't they pretty similar? And you know, the answer is really that because you all have asked us for both. And so there are some of you, I'm sure, in this room who have been uh using SMS maybe for 5, 10, 15 years and you're really used to that environment. And so I would guess if that's you, you probably are going to be somebody who likes to use the SQL editor as as your primary authoring surface for SQL.
And so with that, we've really tried to make a focused, streamlined, and familiar um authoring experience. We also have a few nice features like like a catalog picker. And so you can say this is the catalog schema I want to use. um that way you don't have to take the type the full three-level namespace every time that you're um working uh in the SQL editor. The data bricks notebooks on the other hand is this is kind of like our bread and butter.
This was our first um authoring product that we had and it's really great if you want to work iteratively across multiple cells um and do more complex analysis. Um, for me, I love to start with a notebook and just, you know, my first cell will be just a basic select statement and then I'll I'll look and like I think I need something in this column and just kind of iteratively work and I'll show an example of that again in the demo.
Um, you can also take advantage of markdown if you really want to tell a story with your analysis. That's something that the notebook's great for. And then also supports multi- language development. So if you get to the point where you think, you know, you might want Python, you can do that uh with the data bricks notebook. So those are all the slides I have. Um, which slides are boring. So, let's let's jump into uh the fun thing here with a live demo.
Okay, so I want to start uh just at the homepage and talk about how we're making it easier for you to organize your work within data bricks. So, if I just go over here and I click new uh query, this is a this is a new thing that we we got a lot of feedback that people were saying, "Oh my gosh, like I hate I have new query all over uh like an untitled new query all over my workspace. It's really cluttering things." And so, uh we've we fixed that.

`8:24` SQL editor live demo

I see some heads nodding. Um so, now when you create a new query, uh by default, it goes in the drafts folder. Um and so this is a this is a folder where you're going to have your unnamed things. And now once I decide that I want this to persist beyond my current um you know execution um I can give this a name. I can say deis demo here. If I just hit enter it will automatically move the query. Um I can use the move dialogue here if I want to.
But we also have access to the workspace file system side panel with the new SQL editor. So you can see it's right here. And then I can actually just drag this um over here into my data summit 2025 demo repo. And this is actually a git repo. And so I'll call out the first new feature that we have with the new SQL editor is um git integration. So um previously with the old SQL editor you you wouldn't have git integration.
Now you can see I have this little popup here with the git modal. And so I can work directly with CI/CD and git with the new SQL editor. While we're over here on this side panel I want to go over here and show you um the catalog explorer and some of the new things that we have too. So previously again like to preview tables you might have needed to write a simple SQL query. uh will no longer uh you can just go to the catalog explorer and hover here and click preview data and it will really quickly uh bring you a preview of the data.
So I can say okay this maybe is what I'm looking for. You know this Craigslist vehicles this looks similar to the car deal data set. Let's see if maybe this is the one I want to. And so you can really quickly just preview um and kind of scroll through to see which table you want to use. Another thing I'll call out um is I mentioned this earlier but favoring tables um is something that is a little bit maybe underrated um because it does help uh the assistant and helps us in other interactions to give you more relevant suggestions.
So if I was to just go over here to this Craigslist data set and I was to uh go right here and I was to say um it's already one of my favorites but you know you could add to favorites but I'll remove it for my favorites from this case. You can see it right here. I have favorites. Um, I also call this for you tab is really nice because it's u mainly the stuff that you'll work with most of the time. I think we do a pretty good job of finding um tables that are going to be useful for you.
If I went over to all, you know, there's just so much in here that um I could look at. So, I'll stick with the for you. Um, next thing I want to orient you is the run button. Now, I I find actually there's a surprising amount of people who aren't familiar with all the options in the run button. So, if you click this dropown, you have the ability to run all statements or run a single statement. Um, so for example, if I check run all statements and write select one and select, if I could type two and then I run that, um, you can see I got both statements here.
And we also now have multiple result statements. So you can really quickly flip back and forth between the two of them. Um, whereas if I was to uncheck that, it's like you'd think I'm just on this statement. I just hit command enter to run and I just have the single statement to run. Another thing I want to call out is we have the ability to run and download all rows. So, um, previously if you wanted to download more, um, than the data that was returned, you'd have to re-execute your query if you do that.
But now, if you know you're going to want to export to CSV or Excel right from the get-go, you're able to run and download all of your rows at once. So, that's another cool feature. I'll turn off this limit here. Next thing I want to just talk about briefly is the catalog picker. So, it's main is what I want and I can select JSON here. That's where we're going to be working. Um, and then let's talk about the editor box.
Um, I'll just type select star from and just calling out the autocompletes again. Um, I have things like car sales data, country data. Um, these would traditionally be lower on the ranking list, but since these are my favorite uh tables, they pop up first. So, I can do that. I'll show uh another new feature that we've released recently for both the notebooks and the new SQL editor is the ability to query snippets. And so, um, you know, I could I could write my select star that way, but I could also just type SSF and that pulls up a snippet here.
And if I type that, I get that instantly and I can say car sales data and then hit tab and it brings me to the end of the screen and just delete that. And so um query snippets are a really cool way that you can kind of templatize common SQL that you write. Um so you can just insert it whenever you need it. Um SSF is one that I use. And then I also use it for uh in the wear clause a lot of times with dates. I might want to do like you know I do like within the last six months a lot of the times.
And so instead of having to write that out every time I can just add my query snippet to do that. Um, and I'll show you you can get to query snippets if you go here to this dot menu and you hit view and then you go to query snippets. Um, it will pull them up and then you can just create them this way. So, pretty simple and uh I think a pretty underrated way to improve your productivity in SQL. So, there's that. Uh, one more thing uh I will show is the command pallet.
So, I mentioned kind of we are trying to simplify the UI but also uh give you your features right at your fingertips. So, if I hit command shiftp, and I'll show you the icon down here as well. If you don't want to if you don't remember the keyboard shortcut, you can always hover it here, but command shift P or control shiftp on Windows pulls up the command pallet. And this is a a place where you can take quick action.
So, honestly, the thing I most commonly use this for is if I want to delete a query, but you know, you can do things like open developer settings, for example, uh which just will pull this open. If you want to, you know, open query snippets, um you know, you can you can do that. Um, or maybe if you want to change your theme uh to dark mode, for example, which uh I know a lot of people uh a lot of people love and I love, but uh for demo sake, I'll switch back to light mode.
Let's see. Um another thing I'll call out uh here before we jump to the output box is um SQL formatting. So if you hit command shift F, you're able to format your query um just like this. And actually might have see, let's see if I lowerase this. Um, okay. Yeah, I'm showing a we we now have the ability to customize your SQL formatting. And so, um, if I go back over to developer settings here, and I scroll down, you can see SQL format.
So, we got a ton of feedback from customers saying, "Hey, I love your auto formatter, but it doesn't fit with my company style guides. I want to, you know, put commas at the at the beginning or at the end or, you know, wherever you want to do." And so, now you can have your cake and eat it, too. You can, uh, you can customize your SQL however you want, uh, with this. So just to maybe show an as as example um I like uppercasing my keywords but we could change it to lowercase here and then if I go here and hit command shift F you can see that it lowercased my my keyword casing.
I think those are the features that I wanted to show you uh within the editor box. Let me uh grab the Craigslist vehicle data set as well. Format that and then let's run all so I can just show you the multiple statement results one more time. So again, previously, um, if you were to write this, you would only see the Craigslist vehicle, uh, data. You wouldn't get both statements. And so now you can really quickly flip back and forth.
So, you know, maybe I'm looking for a column for to join on. So maybe I can scroll over and I can see, okay, you know, it looks like, okay, it looks like we have VIN here in this. Let me look over. Let me flip over to this result. Is there a VIN column here? And it looks like, okay, there is VIN. So maybe we can join in the future. So just an example of how uh, you could do that. Let me delete this and uh just go with this one data set because that's all we're going to need for the next little bit of the demo.
Um next thing I want to jump into is the result table. So um fun story, this is actually the first uh product area that I worked on when I worked at data bricks. They said I joined and they're like, "Hey, we have a result table. It could be way better. Can you like rebuild it?" And I was like, "Yes, that's such a fun project." And so uh spent a lot of time uh improving this over over the last two years. Um I've talked about before we have, you know, spreadsheet like selection mechanics. you can grab things if you want to use like keyboard shortcuts to, you know, only select certain things.

`15:55` Filtering and visualization

If you copy, it does all that. But what I really want to focus on today is the improvements to filtering that we've made. So, one of the things that I I personally love to do is I'll write kind of more of a general SQL statement and then I'll use filtering to dive in and answer different questions about the data all without re-executing uh my code. So, let's uh first filter uh the make and let's look at. Um, you know, I like.
I don't have one. I would love to have one one day maybe but I don't have one. So uh you can see I can add a filter uh by clicking into the column that way and then I get is one of I can search or I can select things this way or I can just search here. So if I type um I could filter that and now we see only. Another quick way to apply filters is if I rightclick I can say filter by or exclude this value. So maybe A4 is one that I can look at.
I also like whites. So maybe I'll filter to only find whites. And then another cool thing about filtering is the options you have to filter are dependent on the data type. So I have this sale date here which is a date type. And if I add a filter on this one, let's see like maybe I want to get maybe in 2015 and above for the sale date. Um I have is one of but if I go I can say after here and I can select a date here. And so I actually have a calendar to go pick.
And so I can go back here uh you know switch over to 2015 for example, January and January 1st. And so instead of having to memorize any syntax, you can you know just click through like that. And so now we have A4s that are whites that were sold after January 1st of 2015. So that's one way that I could add filters. But you know, I think there's probably even an easier way to do this. And this is one of the ways that I think we're infusing intelligence into the product.
So, if I just hit the AI button here, I can say um white A4s sold after Jan 1 2015. And boom, we have the same filter created. And so, as you can see, that wasn't like a simple I didn't just say like, you know, like state of Georgia or something like that was a pretty complex with four different filters that I could do just natural language. And so um really quickly that's a way that you can use filters. Um now the other thing I'll show you is we recently announced or released the ability to do this on your full data set too.
So I got a lot of feedback before I was like I love filters but my tables have billions of rows like I can't I can't use those because they're only um you know working on the truncated data. Well if you see over here you can see the filters are applied to truncated data. And if I click this I can change this to full data set. And so what this does now will rerun the query um with a with a cache version and it will then give me all of the rows on all of my data.
So you can see before we had four or five rows, now we have 368 rows um that are uh that apply to this filter set. Another thing we've improved with filters is they also apply to visualization. So, if I click this plus button, I can add a visualization, which is a a great way to, you know, tell a story about my data or maybe if I'm looking for an outage at a specific time. I can go in here and I'll say, let's maybe look at uh let's look at the color and let's say what's it would be interesting to see what the average price of cars by color are.
So, then we can go to selling price on the y ais. Change this to be average here. And as you can see, this preview is showing me with um all of the colors. But if I hit save here, it will create the visualization. And you're seeing that it's only showing white because I'm filtering the color of white here. So if I remove this and apply the change, it will then show me for A4s that were sold after the state. Here's the average selling price.
So uh I guess for you know, we got to give someformational stuff here. So looks like white is the most expensive selling. So if you're going to buy a new, maybe you want to buy it white so it will have a higher resale value. Um, and then you know if I was to take this off completely. Uh, so we have no more filters and apply that. Um, I can see for all cars, it looks like charcoal is the if we're taking out this no value, it looks like charcoal is the most expensive uh, one here.
So filters, they're awesome. I think they'll save you a lot of time. I hope they'll save you a lot of time. And uh, they here's a lot of cool features uh, with them that we've uh, a lot of improvements that we've made. Okay, I'm going to uh remove this dashboard or this visualization. We won't need it for the remainder of the demo. And the next thing I want to talk about is how um we're infusing AI into your development.
So, let's talk now about the data bricks assistant. Um you know, there's a lot of different use cases for the data bricks assistant. You know, I think the one that I find is the most useful is for just how quickly it helps me to do things that I I could have done it on my own, but I could not have done it that quickly. And so, you know, let's say we have this car sales data. Maybe I'm interested in what's like the what's the average price of a car by make.

`20:43` Databricks assistant

You know, I could write out the, you know, seven lines of SQL really quickly or I can just type average price of car by make and, you know, instantly it it has the context, it has what we're working on, and it can just give me the SQL query um to be able to do that. And then I can run that. And let's see uh if this is a surprise to people. Looks like Rolls-Royce is the most expensive car and followed by Ferrari and Lamborghini, which uh which that makes sense.
Um so this is the assistant side panel chat. Uh this is a this is a great place to go um if you just have questions about stuff too. I'll show you in a little bit how you can just ask it questions and it will um give you uh it will have give you resources and references for its answers as well. Um but I want to talk now about how we're infusing the assistant into you know all of the little interactions that you're working in.
So let's take this out. Let's go back to select star from car sales data and I want to join this. And if I just type join here, you can see I'm instantly given with a suggestion. This is our AI autocomplete um which it tells me how I can join this. And so um if I can just run this, it will run uh successfully. And so we can see that in this car sales data set, there were 494 cars that were also sold on Craigslist. Um, and so, uh, with assistant autocomplete, it could it could do that really quickly.
And, you know, one of the best tools that it has is it can look at the other code that you've written before. So, it knows that I've done this join before. And that's why I was able to recommend that so quickly. The next thing I want to talk about is assistant quick fix. And so, um, you know, maybe let's add a wear clause and let's say where make is equal to and do capital here and model is equal to A4. And uh I don't know if you all caught it.
I don't you probably didn't, but there's actually an error in uh this SQL statement that I've written right here. And when I was putting this demo together for the first time, I actually did not catch it. Uh but thankfully the assistant did. And so if I hit run all, we see there's an error. And then instantly within seconds, the assistant has actually recommended how I can fix this. So it says, "Oh, hey look, in your Craigslist data set, in your car sales data set, there was um there there columns that were had for make and model."
And so you need to qualify that. And so then without taking my hands off the keyboard, I can hit command enter and I can run that and it has solved my problem. And so again, we're really trying to take the assistant away from this like chat interface, which that in itself is is great and is useful, but we're trying to bring it into the experiences that you have so that it, you know, it's just right there at your fingertips as you're working.
The next thing I want to talk about is optimize. And so, um, one of the pieces of feedback that we got is like, hey, can you use the information you have on the data intelligence platform to help me optimize my SQL so that I write better and more effective SQL code? And I'm happy to report that uh, we're we're trying to do that. It's um, we have a SQL engine that will do a lot of that behind the scenes. So if you you know write your SQL in an inefficient way, it will kind of take care of that.
But it can't help you for when you actually like write bad SQL and forget things like maybe using a forgetting to use a partition column um or or like in this case doing a cross join with myself. And so if I um just hover here, the first thing you'll see is I get a warning. Uh this shouldn't pop up four times. So that's maybe a bug with my demo environment, but it says, "Hey, you uh you have a a partition column for transaction date uh which you're not using.
You should probably use that. probably shouldn't just query the whole data set like this. And then also it shows me in the join as well. And so um if I run this, let me change this to 15. I've uh I've run this and it's it can be cached. So make sure it's not cached here and run this. We can see that this query executes. I'm going to grab a sip of water while it's executing. So it looks like it took about 8 seconds to do that.
And if I look down here to the bottom right, you can see I have this optimize button. Before I do that, I want to show you the actual query profile too. So if I click in here for C performance, I can click on this statement and I can get a query profile. One of the cool things that we recently released now is you have this um this visual of the query and you can see the longest running operator. So I can see that grouping um and aggregating here was was the longest running thing and then I can kind of click through and look at the rest of the diagram to see what else took a long time.
But if that's too much for you, you can go ahead and you can just click the optimize button here. This will open the assistant. It takes the query profile. It provides it um as context. And then it says, "Hey, here's a few things that you should probably do. Like look, this is reading 1. 3 gigabytes of data. It's processing 200 million rows of data. Um and it's saying maybe what you should do is you should probably apply a filter here.
You can probably get rid of this cross join honestly. Um, and then you can, you know, use your partition column. And so then it will, um, give me a suggestion here. Um, it looks like this one just wanted me to reduce. When I did it earlier, it told me to completely remove the cross join. Um, so I'll just do that, uh, for this. And then, um, it looks like, yeah, um, looks like we should be able to run this here. And uh we can see that ran much faster once using the partition column and removing the um the cross join.
So uh assistant optimize is another way that we're trying to help you to be more efficient and more productive with your SQL. We've got a lot more that we're trying to do with it. We're just starting to roll it out, but um hopefully you're able to get some value out of it. if and even if um the suggestion is not great, I do think just the natural language suggestion for you know what's taking time and how you can improve will hopefully help you out.
In the next part of this demo, I want to shift over to how you can use um AI to help you use AI in your own um analysis. And so um let me click over here and in this one I have some Amazon book review data. And so I have a review summary and review text. And you know what? If I want to do sentiment analysis and you know maybe I also want to translate review summary to Norwegian. Um I I speak a little bit of Norwegian.

`26:51` AI functions and scripting

We have some people from Norway in the room. So you know we can we can do that. Um well previously if I wanted to do that I would have had to you know switch to Python gotten some type of translation model or sentiment model. Um it would not have been able I would not have been able to do it easily in SQL. Well now with uh with data bricks AI functions um we're able to do that. So um you know I can go over here and I can say hey tell me about data bricks SQL AI functions.
That's how I always talk to my AI too. I say hey that's awesome. But it says hey okay so we have uh data bricks AI functions are um purpose-built SQL functions. Um here's a bunch of examples. Here's actually an example of AI analyze sentiment. It took you know looks we're looking at reviews. It says hey you might want to do that. Um, and then it provides me um, references to documentation if I want to look over at that.
Um, and so this is a really great way for you to just learn about the product and I can say, "Oh, this is awesome. Um, please help me um use AI translate and AI um sentiment to um translate let's say review summary to Norwegian and give me a sentiment score and then after thinking for a second says hey yeah here's how you do that provides me the code and I can just point click and I can run it and let's see if it gives me the correct output.
Okay, so I have review summary. I have review summary in Norwegian. We can ask uh Julia, you know, actually I know this one not good eco bra looks pretty good to me. And then we can see the sentiment score which is negative um for that as well. And so you can see really quickly without writing any Python, just a few lines of SQL. I didn't even write the SQL. Um I was able to translate and give a sentiment score. The next one I want to show off is AI forecast.
This is a newer one that we have, but basically this helps you predict future values based on historical values. And so we can see here this is the same data set we were working with earlier, but I just basically looked at the cars and I said, you know, by the year of the car, what's the average selling price? And if I sort this, it looks like this goes up to 2015. So say we want to see what's the average what's going to be the average price of a used car in 2024.
Um for example, we could try and predict 10 years out. And so I can say uh can you tell me about um AI forecast? Um what are its arguments? And so it says hey yeah I can tell I can do that. You know your wish is my command. Um, and so it will give me the different arguments um, for AI forecast um, as well as the syntax and also gives me an example here with a generic query. And so I can say, oh, awesome. I always like to tell it's awesome.
Um, and I can say, can you help me with my current analysis? I'm just going to leave the typos. Doesn't matter. Um, LMS are good at reading through that. um to predict the average selling price of a car um up to Jan 1st, 2025. And I can enter there and it looks like um it's right actually. So it gives me the syntax here. Uh and then I can hit run all and uh you know pulling behind the curtains. I shouldn't do this, but uh this is a newer one and it gets it wrong like half the time.
So it looks like uh looks like we lucked out this time, but you can see that it predicted the values and so it says in 2024 this says the median or the average selling price of a car is going to be $27,414. And then we have an upper bound of 36,000 and a lower bound of 21,000. So um just to prove that this is not just like don't just do this for fun, let's look up um what is the average? I've actually looked this up before.
Um, I don't need location now, but it looks like the average selling price in 2024 of a used car is $28,400. And so it was actually pretty accurate based on the small historical data set that I have. So you can imagine um with your data, it's super super simple to make accurate predictions that can really help you and your businesses. The next example I'm going to show is AI query. And so this is kind of a a general purpose um you know Swiss Army knife if you will of uh AI functions.
And so you can see here what I do. Uh this again is the Amazon book um review data set. And uh it looks like I forgot to clear my results. So we can see the result already. But you basically just give it um the uh AI endpoint that you want to use the LM you want to use. And then you just say whatever you want here in free form. You could say if the value is zero rights, I love cookies. You know, you can say whatever you want here for the LM to do.
And it will look at every single row and it will do what you ask it to do. And so for this case, I said, "Hey, based on the text, give me a rating from 1 to 10, tell me how helpful the review is." And if I pull this up here, I have the review, the title, and then if I switch over to this data set, and I have a helpfulness score of saying, "Hey, this is this helpful." And so really, you can imagine for anything that doesn't fit into one of these standard use cases, um, you can you can also, you know, use AI query to basically fill in any gaps you have there.
So this is a again a really powerful um function that is awesome uh that we or it's awesome to me that we allow you to do this right in SQL. The next thing I want to talk about is um some of the new language features that we've released. So SQL scripting is uh is now here. So you can write for loops, you can do if statements. Uh I know uh a lot of you I've talked to in this room are excited about this. Um but I I think there's a lot of use cases for this.
For me, I think maybe the most useful one that I've seen is just if you have your data set that's updating every single day, maybe you know you want to do a specific check on the rows that come in and then based on those rows, you can say, "Oh, I want to insert this into a table over here saying this is like a bad table or we need to flag this or something more we can look into." And so now you can do that with SQL. We also support SQL pipe syntax.
So, um I know this is uh not not maybe everyone in this room loves pipe syntax. It's funny since releasing it, I've got a lot of negative feedback about, oh, like why would you ever write SQL that way? Uh but the point is not to tell you to use it. The point is just saying that you have the flexibility now if you want to use pipe syntax um you can use that. And so basically this allows you to write your SQL in any order.
So I can say you know from car sales data set select and join and I it doesn't matter which order I write this it will run successfully. Um it looks like oh did I uh not sure what's going on. The assistant could probably fix it. Should we just try it? Let's just try. I don't know if it will know pipe syntax already. Um, okay. It looks like it didn't know the pipe syntax. Um, I promise this works. Uh, you know, if this wasn't a live demo, it would it would work.
Uh, okay. Uh, moving on. Now, let's talk briefly about the notebook uh for the last five minutes. So, um, again, like why would you use the notebook uh versus the SQL editor or or the SQL editor versus the notebook? Um, and I think it really just is depending on your preference for uh for what you like. Um, you know, each has maybe things that maybe they're a little bit better at, but generally we're working hard to make it so that you can everything that you can do in the SQL editor, you can do in the notebook.

`34:23` Notebook workflows and conclusion

Um, and there are a few things that you can do in the notebook that you can't quite do in uh in the SQL editor. So, um, one thing I want to show you that I don't know if a lot of people know about is within the notebook, um, you can click this focus mode button here and this will pull pull open, um, basically like a SQL editor type UI. And so if you're someone who's like, "Hey, I really really like the SQL editor UI, but I also want to kind of work across cells and do the multi-ell authoring, you have the ability to do that now with focus mode."
And so you can see this is almost uh exactly what we had before. Um if I click over here, I can, you know, go between different cells um doing that. And so that's just uh something that I just wanted to call out. Um another cool thing that we have the ability to do with the SQL editor right now, um is if I filter this, maybe I'm looking at this is different data about different countries, their life expecties and population.
I'm going to filter Europe here. And then I have this button to create SQL query from filters. So then I can just click this and I've been given a new cell um which has the SQL um that basically uh generates this exact table. And so you can imagine like for example with the example I did earlier like you can do some pretty complex stuff with your filters and then you can just click one button if you want to generate the SQL for that if you want to share that to someone else or if you just want to work work iteratively.
So like just with this example, I can just say create temporary view my view as and then give it this. Oops, I hit the wrong button. And then I can run that. And then you can imagine I go over here down to my Europe statistics um uh spot and I can say you know let's select the average and let's do life expectancy and let's do region and I can do from and I can say my view and I can group by region for example do the SQL formatting for that and oh I added an extra comment here.
Oh, sorry. Yeah, thank you. I, you know, the assistant should have caught that one for me. Um, so then, yeah, there you go. So, you can see um in the notebook, you have the ability to kind of work iteratively like that across different cells. Um, also show one thing. Um, if if you do ever want to go to Python, I'm actually going to change I need to change a few things here. So I change this to a Python notebook and then I change I'm using a SQL warehouse which that's one thing is you can use the same compute across SQL notebooks and the SQL editor.
But if I change this to my all-purpose compute here confirm this and then let me run all again here. You can see actually that um my in my output now I have this underscore SQLDF um which is storing basically um the SQL output and so I can just add a code cell really quickly here and I can say um maybe for row in and I can say underscore SQL DF collect and then I can say for column in row and then I could say print colum for example, and I could run that.
And so you could see really quick was able to switch over to Python. And uh you know, maybe the one kind of cool thing that I'll show that you can do here is you can actually do step-by-step debugging here. So if I add a if I just click to add a breakpoint here and then say debug cell, I get like, you know, step by step here. And so let me minimize this a little bit here. If I continue execution for example, you can go through and so not saying this is something that you would do but uh you know you have a lot of power in the notebook to be able to switch between languages and if you want to use things like the debugger you have the option to do that.
You know there's uh there's a lot more I could I could go on talking about the notebook uh forever. Uh but uh I I think for the purpose of this demo we got one one minute left and uh it's the last session of the day. So I think I will go ahead and end here. Um, so let's switch back to slides and uh say um that is a wrap. I just did a 35 minutes of uh of live demo. I appreciate you all sticking with me uh throughout it and hopefully there was uh you know maybe one or two things that you learned today um that you didn't know before.
And so I hope everyone is enjoying the conference. Hope everyone has a great rest of your day.

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.