Analyzing Data with SQL and Visualizations in Databricks

Key Takeaways

What's going on everybody? Welcome back to another video. Today we're going to be analyzing and visualizing data in data bricks. Now, in the last lesson, we looked at the SQL editor as well as notebooks. And so, what we're going to be doing is we're going to be looking at some data. We're going to be analyzing that data and then we're going to be putting it into a dashboard and creating different visualizations. This is actually the dashboard that we're going to be creating in this lesson. And I know what you're thinking, Alex, you put the exact same visualization twice. that makes no sense, but it will make sense once we get to it in the lesson. I highly recommend following along. All you have to do is have a data bricks free edition account. I will leave a link in the description so you can make that account and follow along. With that being said, let's jump onto my screen and get started. All right, so we're getting started right here on the dashboards. All we're going to do is we're going to come up here and we're going to create our own dashboard. So, we get started with this blank slate and it has all these little arrows and I recommend you read through these really quickly. But this is how you add filters. This is how we get our data which is right up here and I'll show you that in just a second. And this is how we actually add our visualizations to what they're calling our canvas. Let's come right up here and let's go to our data. Now, within our data set, we are able to write SQL queries. And I will say this is one of my personal favorite things about this is you can write the query and then you can use that query to then create a visualization. We're going to do that in this lesson because I like being able to visualize and kind of see my aggregations when I'm doing some type of group by in SQL. I like to see what the actual output is and you can really easily do that in here. You can also come down here and you can just come in and select some of your data. Now we are actually going to be using a sample data set. Everyone should have this data set. It's right here called the bake house. And so we can come in here and we can also just select one of our data sets. When we select this sales transaction, it's going to start our warehouse. So now it's connected to our data and our serverless warehouse is running. So now we have access to this data. If I come right here, I click on these three dots and I can click add to dashboard. So I'm going to click add to dashboard. It's going to read in this as a SQL query. So if we go back up here, we now have sales transactions as one of our data sets. So we can just come in here and write it ourselves or we can also come into the catalog and just select a data set and have it run it for us. Now before we start actually diving in and kind of analyzing and visualizing this data. There are other ways you can actually analyze data. And we looked at this in a previous lesson when working with data set. But as it pertains to connecting it to a dashboard, what we can do is let's come right up here and let's click on a notebook. Let's say we want to run that exact same query. And I'll just go back to the dashboard. I'm going to rename this really quick. I'm going to rename this as our transaction. If I can spell this right, dashboard. All right. So, if I go to my data, I could just copy this and then I come right over here to the SQL editor and we'll actually have access to uh our notebook right over here. So, this is just our fresh notebook. We haven't used it. I'm just going to make this SQL just so it can you can see how easy this is, but we're going to run this exact same thing. We're going to get our output right down here. Now, let's say this is the output that we want. It isn't, but this is the output we want. But if we come right over here to these three dots and we scroll down, I can click add to dashboard. So I'm going to add this to my dashboard. I'm going to say add to existing dashboard. And when I click on this, I'm going to click on the transaction dashboard and I'm going to import this. This data is then going to be imported over as an untitled data set. We can rename this. Uh I'm just going to say that this is from notebook. So this is our data from the notebook. It is the exact same query as you can see, exact same data. But you don't just have to create your data from right here in the create from SQL or in this dashboard tab. Now let's start actually working on our dashboard and kind of analyzing the data as we go. Let's just start by taking a look at our data. So we're working the sales transactions. We have transaction ID, customer ID, franchise ID. If we scroll over to the right hand side, we have the date and time that this transaction went through, the product that was purchased, the quantity, the unit price, total price, the way that they paid, and their card number. Now, this is not real data, so don't try to steal any of this card number, but we are going to be using this data to create our visualizations. Now, typically when I'm analyzing and then visualizing data, I have an end goal in mind. I know what I'm going to be doing with the data, and so I know how I need to analyze it. if I need to be use a group by or a window function, if I need to clean the data or pivot the data, these are things that I'll know ahead of time. Now, in this lesson, we're going to keep it kind of simple, just learn how to build these things. But in the last lesson in this series, we're going to be building a full project. It's going to have really messy data that we need to dig into. But here, we're going to learn a lot of the fundamentals. Let's start with that first bar chart that we saw earlier on. So, we're going to come right here. We're going to go create from SQL. And let's call this one, let's rename this before we actually write it. We're going to call this one product sales descending. Now, what this means is is we're going to take the product sales. So, right over here, we're going to take the product and then we're also going to look at this total price. So, we're going to calculate the sales. This is actually going to be quite easy. If you know SQL, this should be uh pretty straightforward because we're just going to be using a group buy for this. So, I'm going to come right over here and I'm going to say I want to take the product. So, let's take the product and I use tab for autocomplete here. So, I'm going to do uh comma, then I'm going to do the sum, and then I'm going to take the sum of total price. And again, I'm just going to hit tab. So, now that we have the product and the sum of total price, all we have to do is come right down here and say group by. And we're going to group by the product. And we should also order by. So, we're going to order by and we'll do total price. And we'll do that descending. And so, all we're doing, and let's run this. All we're doing is we're taking the product and we're grouping it. And then we're taking the sum of all of that total price. And I actually need to order by the sum of total price. I just took the column itself, but in this query, we're using the sum of total price. Let's try running that one more time. Listen, it happens to the best of us. So now we have the product right down here. And we're ordering it based off the sum of this total price. Now I'm going to leave this just like this. Although typically I would use an alias. I would say as and I would say total price or something like this, right? But I'm not going to do that because I want to show you in the dashboard how we can easily rename this. We don't have to use this column name. So now this data right here is ready for us to visualize. We can use this. So now let's come over to our untitled page and let's title this. We're going to say uh dashboard. So we're just going to name this dashboard. And we can get rid of these global filters for now, but we will need that in a little bit. Now, you have all these options down here at the bottom. We have the move, we have add visualization, add a text box, add a filter, undo, and redo. Now, let's focus on adding a visualization first, and we'll worry about the text box later because we'll give it kind of a header of transaction dashboard. You don't have to, but we will for this dashboard. Now, when you first create a visualization, we're going to have this widget on this right hand side. This is where you basically build out your visualizations. We're going to come in here and we're going to select the data that we want. We just built this product sales descending. Let's go ahead and click on this. We do want this bar chart, but let's come in here and let's look at all these different options. There is a lot of different types of visualizations that we can create, and a lot of these are the ones that you'll use 99% of the time. So we're going to click on this bar chart. Now we have to select the x-axis and the y ais in our chart. The axes are like this. We have we have two separate axes. And so we need to select what data goes on each of those axes. So let's select our x-axis. For this we're going to do the sum of the total price. And then for the yaxis we're going to select the product. Now this looks perfectly fine, right? I'm going to uh expand this a little bit just for a second. This looks perfectly fine as is, but there's a lot of little things that we can do to make it a lot better. The first thing that I'm noticing is that I, you know, I'm having a little tough time reading this. I'm saying, okay, how much is this one exactly? It's a little over 11,000. And if I hover over it, it'll tell me the exact number, but that's not good for a customer or somebody to see. I'm going to add these labels. And now we can see it right away. So, we don't have to kind of guesstimate. we can see the exact number. The next thing I'm noticing is that it's kind of all over the place. It looks like it's in alphabetical order here, but if you remember in our data back here, we had it in descending order based off of the sum of total price. So, highest to lowest, that's descending. And I want that in our dashboard as well. All we have to do, we're going to click on this. We're going to go down to these three bars right above product. And then we're going to click buy Xaxis. And then right here is the descending. So now we've ordered our data from highest to lowest. It's no longer alphabetical. Now there are some other things I want to highlight. We don't have to add these, but we absolutely can. First, let's add a title. So we're just going to call this total price by product. Keep it super simple. But we can also add a description. For example, let's say we wanted to add some context to this. Or maybe we wanted to highlight that this is our biggest seller. So we can say Golden Gate Ginger is our highest selling product eight years in a row. Now I'm just making this up. Uh this isn't real. This is just as an example, but let's say we had some historical data and this is now showing, hey, this is still our best seller and I just wanted to add that as some context. You can definitely do that. You don't have to, but you can. One other thing is let's take a look at these colors because we don't have to just keep these colors. We can also use a custom color. So they can be you know whatever color you think is best for your dashboard. We can also click on this plus sign and we can create some other options. Now if we click on product and this is one that I don't necessarily recommend. So each product is going to get its own color on each row. It's a little bit redundant. Now let's go back over here. Let's get rid of our product and let's select our total price. Now we have this gradient scale from blue to white. This isn't my favorite. I actually prefer if we come right up here and let's get the green blue. And so this is a great way to analyze and visualize at the same time. Sometimes you already know what you're going to be building out and so you can work with the data while you're building your dashboard. And now I can very easily see I'm like Golden Gate Ginger Man that is our product. like we're killing it with this product. But Richard Oasis, nobody likes that. It's doing okay, but it is our worst seller. Now, just remember, we use this product sales descending and let's go take a look at this. This is the only data available to us in that visualization. Let's come over here and let's look at our sales transactions. We have our sales transactions right here. This is all of our data. And so there are times where we don't even need to write a custom query for each visualization. Let's take a look at this. So let's use our sales transactions to create a visualization in our dashboard. Let's come down here. Let's create our new visualization. And the next one that we're going to take a look at is payment type. So let's change this data set to the sales transaction. So now we're using two different data sets. Just note that for future reference because that will come into play. But we're going to come down here and let's say we wanted to create a pie chart for the angle. And I'm just going to come down here. We're going to do payment method right here. It's going to give us a count. And we don't have to do count distinct. In fact, uh we probably shouldn't because it's just going to if we hover over it, you're going to see three because there's only three options. But if we do a count and hover over it now, it's 3.33,000. So now we're going to be able to see how many transactions are using each payment method. Now, for the color, this one is actually really important for a pie chart. Let's come in here and we want to do this based off of the payment method. We want to break it out. So, we have this payment method. We have Mastercard, AX, and Visa. Again, we can see the split based off of the color, but I really like labels. I think they're just super important. So, I'm going to add these labels in right here. Now, this is an example where it says count of payment method right here on the dashboard. I don't like that. I mean, that's just by default. It's going to take the uh name of it. But I'm going to come in here. I'm going to change this display name. So, all I'm going to do is I'm just going to call this payment method breakdown. And let's change that. And that looks a lot better. It just doesn't seem so I just tossed it in there. Seems like you intentionally named this dashboard. So, we were able to go in to this sales transactions that has a lot of different fields, a lot of different columns, and we're able to just kind of pick out which one we want to use. So far, our dashboard is looking great. We're just going to build one last visualization and then we're going to work on filtering. So, let's come right down here and let's create one last visualization. And this is going to be our dashboard. So, we're going to come in here. We're going to use sales transactions again, but this time we want to see our transactions over time, right? We have this date column and we want to use this. Let's use a line chart. And if we go back to our data, we have this date time. And this is really useful. We want to utilize this and see how many transactions are we having over time. Maybe there's a certain day of the week that people are just making a lot of transactions. This is really useful data for someone to know. So let's go back to our dashboard and let's come down here to our x-axis. And for this we want it to be our date column right down here. So we'll choose our date time. Now by default it's going to select monthly. But you can come in here and you can transform this. It's going to take that datetime column. It's going to be able to automatically change it to basically anything you want. So, let's just choose daily for now, but we can change it later on. We just have to choose our y-axis. So, let's click on our plus sign in order to see our sales over time. Let's take a look at our quantity. It's going to do the sum of quantity here. And if we go back just to check on this data, the quantity is the actual amount that we sold. So, we sold eight, we sold 36, we sold 40. And so, it's aggregating that data for us, which is really nice. If we come right over here, we can hover over this and we can kind of see each day and the sum of quantity. Now, again, we need to transform this a little bit because it's just sum of quantity, date, time, um, doesn't display the best. We want to customize this. We're going to just say date to keep it simple. And then for the sum of quantity, we're going to say quantity sold and keep it just like this. for our title because I think this one may need one. We'll say quantity of sales over time. Right, there we go. [snorts] Now, we have our dashboard built. It looks great. I'm super happy with this. One last thing we should add, and this is optional. You don't have to do this, but I'm going to add this title really quick. I'm going to call this our transaction. Let me spell that right. Transaction dashboard. And I'm going to format it just a little bit. I'm going to do it like this. I'm going to increase the size just like that. You can change it to be a different color, but it just kind of adds a little bit of finesse to it, right? We can put that up and make this smaller. Um, we can make this smaller if we want to go kind of that route. But it is up to you. The next thing that I want to show you though is adding a filter. Now, filters are quite important. customers, clients, managers, whoever is using this dashboard are going to want to filter in some way. They're going to want to say, "Oh, I want to filter on this product or I want to filter on this month or this year or whatever it is." And so adding a filter is really important. We can add a global filter right here. Now, let's come over here to our widget because we do have the ability to change the type of filter. We can select multiple values. It could just be a single value. Could be a date picker or range picker. So, a range of dates instead of just a single date. It could be a text entry where they're searching for something. Maybe it's a product. You can also do a range slider. Let's click on this range slider and let's come in here. We're going to go down to the sales transactions and let's go down to the quantity. Now, what this means is is we've created this global filter on quantity. Quantity is a numeric data type. So, we have this slider where we can basically say, hey, I only want to see where the quantity was over a certain amount. So we have a picker where we can select a range for the quantity. Maybe we only want to see the transactions that have a quantity greater than 30. For example, we would be able to do that in this dashboard. We can click in here and we can customize this. Maybe the minimum is zero and the maximum is let's say 100. I'm just going to set it for now. Let's come right over here and let's say we only want to look at it where it's greater than 30. So I'm going to click on that. You'll notice that this doesn't actually change at all, but this one changed and this one changed. And let's just highlight that a little more. Let's kind of slide this around. And you'll notice that only these ones are changing, but this one is not changing. The reason for that is the data set that we chose. So, let's come back here. Right up here, we have the product sales descending. And then if you go down here, we have our sales transactions and we have our sales transactions. But what are we actually filtering on? We are filtering on the quantity. And if you remember, let's go back to our product sales description. We don't have the quantity in here at all. And so this quantity is not connected to this data set. And so when we're applying this filter to this dashboard, it is not connected to this right here. Now, the way that we can fix that is we can replicate this exact dashboard. And this is what I was kind of talking about earlier, which is why we're going to create a second dashboard, but we're going to create the exact same thing, but we're going to use the other data set. So now we're in sales transactions. We're going to create our bar chart. For the x-axis, we're going to do the sum of total price. And for the yaxis, it's going to be the product. Let's find it right here. Let's scan this all the way over. Let's go down, add our labels, create our custom colors based off of the total price, and we'll change that coloring to be the green, blue. And you can already see that this is filtered based off of our global filter right over here. So, let's get rid of this. Let's just make it the same. And we actually need to do one more thing. We need to go like this. So now we have the exact same visualization, but this one is going to be connected to our filter. So this is a really important just thing to understand when you're building out these dashboards is people really like their filters. They want to be able to do that and this is actually going to be a big thing that people request once you build your dashboard. You're going to build it out. It's going to be great and they're like, "Hey, I want to be able to filter on this. I want to be able to filter on this." And so you're going to have to build that out and you may have to go back and connect your data in certain ways to be able to accommodate certain filters. So that's just something to think about and something to know. But now this one is connected just like this. And so I would actually replace this one with this new visualization right down here because I want it to be connected to all of our other data for these types of global filters. Now there are of course some other things that we could do. We can come in here and change some of these uh axes. We could add some more context in here. This is our sample data set. This is as far as we're going to go in this lesson. But like I mentioned earlier in the last video in this series, we're going to be building out a full project using real raw data. So, we're going to have to do some data cleaning. We're going to have to really dig in and analyze our data and visualize and create our dashboard. It is going to be an awesome project. So, I can't wait to do that. Before we go, I want to give a huge shout out to the sponsor of this entire series, and that is Data Bricks. Data Bricks is one of the best companies in the world to work with large-scale data and I'm so thankful that they have this free edition where you can get in and learn all of these things completely for free. If you haven't already, I highly recommend creating account for the data bricks free edition so you can learn all of this completely for free. With that being said, that is our entire video on analyzing and visualizing data. If you like this video, be sure to like and [music] subscribe and I'll see you in the next one.