Data Modeling in SQL

Foreign.

And thank you for joining today's Live training. My name is Rhys and I'm going to be your moderator today. We're going to kick off the session in a couple of minutes. We're just waiting so everyone has a chance to join. In the meanwhile, though, we'd love to hear from you. So let us know where you're joining from using the chat or the comments, depending on where you're watching from. And yeah, tell us something that you'd like to get out of today's webinar. Today's Live training will be hosted on datacamp Workspace, so if you don't have a datacamp account yet, make sure you register for your free account. I've also posted a link to the workspace that we'll be using today. So yeah, go find that, duplicate the workspace, and then you'll be able to code along with us. I'll be back to repeat these messages for any new joiners shortly, but for the moment, I'll see you soon. A bit. Hello everyone and thank you for joining today's Live training. My name is Rhys and I'm going to be your moderator today. We're going to kick off today's session at the top of the hour in about a minute or so. We've just been waiting so everyone has a chance to join. In the meanwhile, though, we'd love to hear from you. So let us know where you're joining from using the chat or the comments, depending on where you're watching from. And yeah, tell us something that you'd like to get out of today's webinar. Just a note that. Excuse me. Just a note that today's live training will be hosted on datacamp Workspace. So if you don't have a Data Camp account yet, make sure you register for a free account.

Now.

There's also a link to the workspace that we'll be using today. You can either scan the QR code on screen or you can find the link to the comments. I will repost it again shortly for anyone else, and I think that's probably us about ready to start. So what I will do now is hand over to your host for today's session, Richie. Richie, please take it away.

Hi there, Data Scamps and Data Champs. This is Richie. Now, there's a common complaint that 80% of data analysis is actually just data wrangling rather than doing meaningful analysis. And one way to reduce that time is by making sure the data is structured in a sensible way in your database. So this can dramatically improve data quality so you can get to the important part of generating insights as quickly as possible and the ideas around which data deciding which data goes in which database. TABLE this is known as data modeling, not to be confused with statistical modeling, which is more about the running linear regression and all these other models to make predictions. So today you're going to learn the basics of data modeling and it is just a spectacularly useful skill, especially if you ever need to complain to your database admin or data engineer about why all the data is in the wrong place and it's making your analysis harder. So today's instructor is Andy Elseth. He's a data engineer at Nucleus and, and he spends all day building data pipelines and doing data warehousing for the retail industry. So his work involves setting up ETL flows for customers, improving database reporting and optimizing cloud spend. So that is to say, he's a true expert at this. And with that over to you, Andy.

Well, first, maybe let's not complain to our data engineers straight away. So, but yes, we are in charge of data quality and getting those pipelines filled. Just a quick heads up like everyone said. My name is Andy Alseth. I'm really excited here to give an introduction to building a data model primarily using SQL. I've had a pretty circuitous route here to get into data. I started off in the consumer packaged good industries, then I went to graduate school and then now I do retail data at my current company, Nucleus. And so we're a software company that builds solutions for consumer goods companies and retailers. And we're really trying to enable them and be good partners and help them be nimble and efficient using their data. So data modeling is a sort of just a truly fundamental skill that can really make or break the success of projects. And I've enjoyed both learning about data modeling and implementing data models. It's a nice, fun challenge. So while this might seem to be just a skill for engineers or analysts, it's a useful framework regardless of where you sit in an organization to get the most value out of your data. All right, let's see. Yeah. All right, so data modeling in SQL. So structured query language. So we just kind of as baseline, we're just, we want to, we're assuming like just a little bit of SQL knowledge here. I will use a lot of SQL, but the goal here isn't to learn the SQL, it's really to build the data model. So about what we're going to do here. So our case study is computer workstation sales from 1980 to 1996, roughly. This is a data set that was collected by the researcher Olav Sorensen and it's provided by Dartmouth University at the following link. And so the goal right now is to learn the basics of dimensional modeling using both STAR and Snowflake schemas. What we are focusing on right now is analytics. So olap, olap online analytics processing. Instead of transactional, which is more normalized, we're going to use the Kimball method for dimensional modeling as a secondary goal here. In the process of doing all these transformations and making a data model, we're going to essentially end up with a data pipeline as a result of the work that we're doing. It's a nice little side benefit there and you'll get to see how to do all those transformations. We're going to use DuckDB, which is a really cool tool that's out there now. And why is this important? So poorly maintained model data can lead to long and potentially expensive queries, especially now that queries are run on a lot of cloud databases and that does essentially you pay as you go. And so the longer your queries run, the more expensive they are, but not just on the analytics side, but also the pipeline. So you want to make sure that everything is set up in a manner so that you're doing the fewest, the most efficient computation as possible, while simultaneously ensuring that you get all the right results. Because nothing tanks a data project quite like the CEO saying that number doesn't look right. And so having a good data model can really ensure that that is the case, that everything looks nice, and that you are essentially that you're providing a source of truth instead of a source of dissonance within an organization. All right, so moving along, so modeling, so what is data modeling? So like Richie said, model. I like this Inigo Montoya meme here. You keep using that word. I think you. I do not think you mean. It means what you think it means. I got this from the Jorisa now Jerry's who does the author of the Fundamental Fundamentals Fundamentals of Data Engineering, his substack. So models, right. So like Richie was saying, there's statistical models. This is not really what we're doing here. DBT called Data Build Tool is a tool that analysts use. And it's, it's still not quite the model that we're thinking of. Right. So we are looking kind of at an overarching organization of all of our data. And so I really like this quote from Serge Gurckiewicz. He just recently came out a book called Data Modeling with Snowflake. I've read through it. It's a great, great book, great introduction. I'm a Snowflake user, so it's also, it's good, gives great context in that regards. But the quote is, a model is not simply the shape that the data takes once uploaded to a database, but rather the blueprint for business operations. And I really like it because it highlights three key aspects of data modeling. So first, a model is how the data is stored in a database. That's just what you end up getting. That is the very base level and a model. So even if you do modeling or if you don't do modeling, you have a model. It might not be a good model, it might not fit your business very well, but it is a model. Next up is that the model is ground. This quote illustrates that a model is grounded in how the business operates. We're really trying to get business value out of this data. We don't just keep data for the fun of it, for the most part costs money to keep and it costs money to analyze. So it really needs to be grounded in how the business operates. And third, more subtly, a model really guides how the data is used to analyze business operations. So the model might be static, but it's in charge of dictating how the data flows through the organization. So it's depending on how you set everything up, it really will change how queries are run against it and how much money is spent. So data modeling is. If you make a model or if you just throw data into a warehouse, I mean, technically you're modeling, it just doesn't necessarily reflect the fundamentals of, of the business, which can potentially lead to a lot of issues downstream, can lead to conflicting metrics. When you're trying to do conflicting features, if you're trying to do just basic visualizations or if you are trying to do more advanced machine learning. So let's move on. So, yeah, so the data modeling. So we know what a data model is. Data modeling is just the act of creating a model. And so we're going to keep those ideas of how to get business using business value. And also how do we want to view the data while it is in flight and being analyzed. Before we get our hands too dirty here, we're going to do some terminology, not too much, but so primary key. So primary key is a column or potentially set of columns that, that uniquely identify a row. And this is kind of like the very basics of what it means to be a relational database, right? You really need to be able to get value. You need to have a primary key that you can identify all your data off of foreign keys. So foreign keys are columns that references another table's primary data. So what we're doing here is we're storing our data and then we want to reference it to other stuff. And then once we do that, then we can come up with interesting analyses and we can do all sorts of other stuff that really brings the value out of seeing the interconnections in our data. A fact or a metric. So it's really just the thing that you're measuring that can be dollars, units sold, all those kind of things. And there's different types of facts and we'll go into that in a little bit more depth. All right, dimensions and attributes. So if you. Facts are the things that you're measuring, dimensions and attributes are the variables that you want to slice and dice the facts by. So say you want to see that your company, you sell across the world. I want to rank the company or I want to rank sales by country in order of sales descending. So that is something that dimensions and attributes can help you with. And your data model will help you do that more efficiently. Then the grain, the grain is kind of the, it's, it's kind of the base of everything. It's, it's the lowest level of detail captured for a data set. And so even though you might say, hey, I only really care about monthly, my, my boss only cares about quarterly, why do I need to go down and get the individual purchase orders? Why do I need to spend all that time deduplicating records? And the reason is, is because if you get that lowest atomic grain, what happens is that your dimensions that you have go down to that lowest grain and you can slice and dice at very fine levels. You can slice and dice at the finest levels. Really, that's enabling you to do all of your analysis. The dimensional modeling is what we're going to use because we're focusing on analytics here. There's also the whole space of transactional databases and that's a little bit different in terms of how we might want to organize our data set. And we're just focusing on dimensional analytics for the moment. And Kimble is, he's one of the thought leaders in the area and he has a four step process. His data warehouse toolkit is a fantastic read. It's got great examples and it kind of all boils down to these four steps. So define the business process, declare the grain, identify the dimensions and Identify the facts that might not necessarily mean a whole lot. Right. Whole lot to you at the moment. But we're going to dive in and get started using the Kimbell model on this computer warehousing data set. Going to switch up my screen here and we are going to go to a workspace here. What we're doing, we have this workspace. Let me close this real fast. What we're using right now, we're using DuckDB, which is an awesome technology that I really got to. I enjoyed getting my hands dirty on this project. It is essentially a local dummy database. If you're familiar with Python, it's essentially all you have to do is a simple import statement here with Python and then you are running an SQL database locally. It's great. It really reduces the headache that a lot of people get when they're trying to first learn databases. So this is what we're going to go with. To start with. To begin. I've started. All right, let's just make sure it's running. We've done our imports here. Then what we're going to do right now is we are going to start off and create our initial connections here. Let me pull some stuff over real fast. How do we do that? We are going to start, we're going to create a variable and then we're going to do DuckDB Connect and what this is going to do is create a database for us. What we're going to do here is. This is going to get us this connection object and then we are going to run SQL using that. We're actually going to create two schemas. We don't need to be too concerned about what that is. But basically it's a way for different tables with the same name to potentially be stored together. So we're going to schema snow and then this is just very basic SQL syntax here. So we're going to create snow and star and then we are going to execute this. All right, it successfully executed. And now what we're going to do here is we are going to pull in our initial data and so the data set is included here in the files. We have this firm data and product data. We're only focusing on the firm data here. So what we're going to do here. So DuckDB has really cool functionality. What you can do is we're going to use the. This, it's called ctas, but it's going to be created table and we're going to call it RAW Companies as. And so this is telling us, okay, we're just going to select everything. From our data and I'm just going to copy and paste this here just to make sure that I get this exactly right. All right, so I have executed this and so we can actually do here is I'm actually, I can show you that the data is in the table. So actually has this. DuckDB has this function called table or this method called table. And so RAW Companies is the table that we loaded in. It's a little bit hard to see with some of this formatting, but the data is in this table right here. What we're going to do here is we're just going to verify the data that we have in this data set. Let me take a look here. We're going to use the SQL keyword. We're going to describe this frog company is. And then what we're going to do here is some fancy Python syntax just to fetch all of this stuff and then into a variable so that we can print it all out. So these are the columns that we have in this data set. So we've got gear, we've got firm name, firm IDs, we've got all these different variables here. So we'll talk a little bit more about how we format this data set. This is something that you're going to come into pretty often if you're a data engineer. Somebody hands you a file and you have to come out and say, okay, well what the heck do I do with that? Now with all these, we're just going to take a look at Snowflake. Instead of going through the full process of going with, going from a conceptual to logical to a physical and the to be more frugal with our time here, I've kind of come up with a model and what I'm going to do is walk you through the design choices that I've made in this. And so let's start off with the idea of Kimball dimensional modeling. So step one, declare the business process. And so this is essentially we are looking at workstation sales. That is what we are interested in analyzing. And this might be. This is pretty straightforward for this example. But when you are working with business data, it might not be totally obvious that this is exactly what you are attempting to accomplish. You might have a couple of different files. You need to pick the one that is actually the focus of your or the different focus focuses of your of your business. And right now we're just going to focus on a table or warehouse of one fact just to make this easy. So declaring the business process Step one, straightforward. We've done that. And then from there you end up having a central location from which you design the rest of your database. And so why do we call this snowflake model? Well, so what we have here is at the center, we've got workstation sales, which is going to hold our facts, which we'll talk about a little bit later. And then from there, okay, we have all these foreign keys which link to other tables. But then, for example, here we go to companies, okay, so our companies have names, but those companies have attributes and dimensions on themselves. So we then have to link to other tables. And so this is what we call normalization. And I'll talk a little bit more about the pros and cons about that. But, but basically that is, that's why we have a snowflake. So it keeps on going out from itself versus a star, which is kind of. It attempts to be one layer around the fact table, which will. I'll show you an example of that a little bit later. They talk about one through four. I think there's a step zero, an initial step, know thy data. Because it's really the most important part of a data set is knowing kind of how everything works. So there's something that I do want to check because that I, that I noticed when I was working with this. And so I'm just going to do this right here. So the latitude, longitude and zip data columns. So let's just take a look here at what that looks like. Like, all right, so if we take a look here, it honestly looks kind of a little bit different than I expected. So latitude is integer or it's a, it's a, it's a. It's a real number that's smaller than one. And typically we have latitudes and longitudes with large double digits. It's because these are in radians, so we're going to have to convert those because most BI tools typically deal with latitudes and longitudes in the degrees versus in radians. Then we see zip code here. The data was stored as an integer. So the problem with that is that it truncates a leading zero. And so these are just a couple of examples of data quality that you're pretty commonly going to run into. All right, so let's do our initial data cleansing here. And so what I'm going to do, so we're going to alter this, our base table, raw cleanse companies. And then we are going to alter. We're going to change zip and then we're Going to set the data type as Vargar. For those who know SQL, you might want to actually make it a different data type to be more efficient. But we're not too concerned here. All right, so what do we want to do? I mean, we need to ensure that these leading zeros are present. How do we do that? We're going to do an update statement and then we're going to update the table. Then what we're going to do is we are going to set the column zip equal to. We're going to do a function here, a string function. It's going to take do the right function. So we're going to just add a couple of zeros here. And then what we're going to do is concatenate that back with the column and then we're going to take the rightmost five characters and that will give us those leading zeros that we're looking for. All right, and then we want to also convert our variables from degrees to radians. And so we're going to have to do another update statement. And then we are going to do two different sets. We're going to do set our latitude and then we're going to use the degrees function, provided we're going to set latitude equal to degrees, comma, longitude equal to these. All right, so we've got that. So what I'm doing here is essentially I'm just creating a string of SQL, then I'm appending to it, and then these semicolons right here allow me to have all these together and then they are executed sequentially by the query engine. So what we're going to do here, we're going to run this. Let me see an error. Set data, data type. Do data type. All right, we're good there. All right, let's try running this exact same query again and let's see what we end up getting. All right, that looks much better. That looks something like we would expect to see. So that is kind of the. That's just kind of some of our initial data cleansing that we're doing. So let's then move on. So one of the cool things about using Python with SQL and that a lot of data engineers do, it's what we call templating, we template via the SQL. So I'm going to create a variable here. So we're using the drive principle, do not repeat yourself. So I'm going to have this variable and then from there what I can do is I can actually create that string and then it'll input that variable So I don't have to handle it. And I could potentially reuse this for other schemas. You might have a lot of different schemas or warehouses or databases, and you might want to reuse it. So templating is a way to do that. There's this Compustat dimension, and it's just a dataset. And we're going to take a look at how we analyze it. This table copustat, like we were saying, what we're going to do is we're going to create a primary key and this. It's going to be type varchar. For reasons that we'll talk about a little bit later, we're going to have just going to copy and paste the rest of these just out of. Just for time's sake. So we have ID name, historical ID and historical name. And then what we want to do is have a primary key here that we define as this variable. And so what we're going to do. So when we're thinking about dimensional modeling, the Kimball method in particular, when we're talking about our primary key in a lot of different cases, we want to have something called a surrogate key, which is not necessarily the value that the business handed to us, because if it's dependent on what the business sent us, then if that breaks or changes somehow, then we have to accommodate that. But if we make a change in the future, if we do a process that we handle on our own, we don't have to deal with that. So what we're going to do is there's a couple of ways to create a surrogate key. And you can use like an ordered integer, so you can do 1, 2, 3, 4, 5, for example, and just keep on going up and up and up and assign that before you put data in a table. But what I'm going to do is it's MD5 hash, so it's essentially just turning any set of strings and it does a cryptographic algorithm on it and then it turns it into its own thing. I think it's honestly quite elegant in terms of how it works. Basically, you're creating the key from the data itself. It's repeatable, and it can be done outside of a database system. You don't have to handle sequential integers or any of that. This is going to be the first part of the query we're going to insert into this Compustat table that we will create the columns. These columns. So right here we're using a cte, a common table expression. Don't worry about that. Too much. But basically what we're doing is we're changing up these column names and then what we're gonna do is we're gonna run a select statement. And so to get our primary key we have this function right here called MD5. So it's an MD5 hash and so it's going to create a primary key for us based off of the data. I think it's pretty neato. So yes, it might be a little bit more data to store, but it's unique, it's pretty repeatable. There's just sort of trade offs, all sorts of trade offs that you have to decide on for yourself, for your business. So that's what we're going to do here for that's my preference. So the cid. So we're going to run this and then. Name schema is not defined. Oh well, it helps if I run that cell. Right, so we have the data in there. Now let's take a quick sneak peek at that. So we're going to just do a select star from Snow Compustat and we'll just say let's go limit five. All right, so it's a little bit broken, a little bit hard to see. Let me see if I can wind this up a little bit. But basically this is our key, this is our MD5 hash. We have null values here. It'll still create a hash from that based off of some logic that I did. But yeah, you can see here, each of these is unique and there's very low. That's what we're going to use for our key here. Now we're going to go and create the CEO dimension that we looked at a little bit earlier. So we've got, we're going to create a, replace this, the CEOs. And so just kind of as a note, just in terms of consistency, a lot of people like to use plurals for their, for their tables. Some people like to do singular. It really all depends on what you want to do so what you prefer. So we're going to CIP and then that's going to be a bar cart because we're going to make that an MP5/ash as well. We're going to do CEO name Barcar and then we're going to set a primary key. And so what the primary key does, in addition to telling us, hey, this is the key that we're going to index off of. This is a key that we're going to make sure everything else is unique off of. This actually enforces that constraint is what it's called in SQL. So it enforces the primary key constraint. So essentially it can't. The column in this variable and the variable in any row of the CEO ID column cannot be null, so it can't be null and it has to be unique. So those two constraints are enforced by DuckDB. Some databases don't, but they should. All right, so now we're going to do an insert into the CEOs. So we're going to insert into our schema CEOs, we're going to take an MD5 hash and then we're going to store the CEO name with that from our data. All right, and then tables, this is another way you can actually access the table. So we need to, need to add the identifier in front of CEOs, right, there we go. So we have a unique character for each of these. All right, so now we are going to look at that locations dimension and in particular what we're going to look at is the idea of a slow changing dimension. So what we're going to call a type 2 dimension. So if you think about your data, right, so for example, your headquarters might change in examples of this, that means that your zip code and your latitude and your longitude are going to change. So how do we handle that? Well, the Kimball method outlines different types of dimensions, so we're not going to go into those all of them. But a type 2 dimension is a slowly changing dimension. And what you do with a type 2 dimension is that instead of trying to update a prior one, having a single record, what you do is you just add another record. If you're changing your database slowly, if you're increasing it, if this record isn't changing very often, you're not going to be adding that many rows. But it gets you the same consistency and you can look back to your old data relatively easily. Which is great because in your analytics database, the transactional database may throw out some data, so you really want to make sure you keep it. And so a type 2 slide slowly changing dimension allows you to do that. So what we're going to do here is we've got this additional SQL here that I'm going to fill in and so we're going to add in, we're going to add these in again. Just going to use an MD5/like before and it's in. And I'm not going to show you guys again, but that's. That data's in there. All right, so let's then move on to the company's dimension. So this is kind of, this is where we get into the snowflaking occurring. So we started at the far end and now we're working our way in. And some of that is to enable us to more easily assign some of these keys like. So we can double check that. Okay, the key that I created for that primary key that I created for another thing also works for as a foreign key as well. And that my logic is sound here. So as companies, so if we scroll back up here, this is one of our bigger ones, right? So this has a bunch of different fields and has foreign keys referencing these other tables here. So we are going to have to recalculate these values and store them in this table so that we can reference these the other tables in our model. All right, so let's move on here. So we've got that. All right, so companies, so what we're going to do here, similar to before, we're going to create it with all those columns that we specified that I specified in the model. So we got ID name. So we actually have dimensions here that are integers this founding year. This, that's totally normal. You can have dimensions of many different data types. And then we're setting our primary key as the company id. So then we have to insert the data just like before. This is where the SQL gets a little bit more hairy, a little bit more convoluted. So I'll make sure that we look at the exact right things. So we paste this here. All right, so we start off here. We're doing an insert operation into this table. And so what we're doing here is I should, should note that, okay, we're doing a distinct. Right. So like, the important part is we don't want to put in every single row of the database, we want to get the unique rows. Right? Because the whole idea behind a dimension is that you are storing less data than you would have. And it's a way to both decrease the amount of data that you're storing, but also increase your logical consistency. So we're selecting only the distinct values right here, which correlate to the different unique companies. Because we're going on a yearly basis, we're going to see at most one dimension. We're going to see one row per year. We recreate, we do an MD5 hash here to do the company ID. We have this firm name variable which we're pulling in as our name. But then you see here, we have to recreate the MD5 hash in order to get the Compustat key. And so, yes, we're repeating logic, so that kind of goes against the do not repeat yourself principle. In a production environment, you could do it a couple of different ways. You could have this template templated, you could store the data somehow. You could also store it in a view potentially and then join the data. There's a lot of different ways to potentially get around that. But yes, you do need to recalculate with this method. You need to recalculate these hashes every single time. But once you get it down, it just works. Okay, so then we've got this ownership field right here and we're actually going to do a little bit of modification on that. So you can see it has 012. Okay, so that's not super valuable to us as a company. Okay, so what does zero mean? What if somebody comes in and I don't know what zero means? The analyst is like zero versus two. Like, so the ownership field here. Okay, so who owns the company? So if it's zero, well then it's private one, then public two, then subsidiary. I found that from a data set that was provided with this. And then we're going to kind of do the exact same thing with this estate. So this is the results. Okay, so when a company is removed from the data set, why did they get removed? They exited the market, they were acquired, spun off, change names. These case statements allow us to put the business logic into a manner so that our visualizations make sense to a user or an analyst of any sort. We have that in there. We're going to run that. Let me trust that that works and then, okay, we're going to do. Okay, so we're on the right side where we had that little snowflake dimensions, now we're on the left side and we have this product offering dimension. And so what we have here is we've got our. Yet again, we're just going to create our table here with a product offering id. And then we've got the different product types offered. And so product key to that. So this product key. So like this is an example here of a choice that I made in our database design. So thinking about what is really a fact. So let's go back up here, excuse the scrolling. So, so in essence this is a numerical value, right? And so this is the different types of products that are offered based off of this offering id. I'm not, we're not super interested in necessarily that and keeping track of it, but we're more interested on a dimensional basis. So what year say we offered it 12 years or something like that, I don't know. Or we, we had 12. Oh, we want to see maybe the companies who had five offerings, how did they do? I put this as a dimension. Depending on how the business viewed it, it could be a fact. But this is kind of where data modeling gets a little bit squishy. But it's really how are you going to use it as an organization? And so that led to my choice there. All right, so we're going to use I love CTE's common table, except common table expressions. They allow you to do some really cool stuff with pre filtering data. They allow you to really help your queries be a bit more logical and flow a little bit better. In my personal opinion, what we're going to do here, like what we did before with case statements, we're doing another case statement. But what we're doing here, what we're doing here is we are essentially converting this and actually my, what I kind of initially led you astray here. But so this, this table, what it actually does, so these variables, what we're looking at is essentially other flag variables. And so hey, if we have system equals one and graphics equals zero, we're going to look at this, this, this, this company sells desktop systems. If so if we look at the logic here, 0 and 1, obviously then we only sell graphic systems and then system one and graphic one, then desktop and graphic systems. All right, so that's a, that's something that you'd see typically with when collaborating with your business partners. You'd really want to analyze that and figure out, okay, that's really what we want to see see in our data model. Those are the results that we want to put in there. And that's how people will view it. And then we also, right here we have to, yet again we have to add in our handy dandy MB5 function here. Let's get ourselves a primary key. Then we've got this integration dimension. And then this integration dimension is what we'd call a type 4 dimension. It changes fast. So what we're. Instead of a slow changing dimension where potentially every other year you change something and you're looking at daily data. What if this data changes frequently? You don't want to have to update that on a super high cadence because then you're essentially blowing, you're blowing your data out of the water. Right? What we're going to do is we're going to create what's called a type 4 dimension. Basically what we're doing here, Let me copy and Paste this stuff here. These are the different variables that we're looking at here. What we do is we then do our insert operation here. What we're doing here is we've got all these different flags. Before we also did these, we did the case statements. We're converting these to flags. What this is looking at is of the parts that we put into our computer, how do we source them? What's the integration that we have in our company? We buy or sell and look, there's a bunch of different combinations. What we do is from here we're going to just insert all of them that we see. All the different combinations that we see in this case we are only inserting the ones that are present in this table. But another option is if you don't have too many variables is to just go ahead and put everything. So for example, this is 1, 2, 3, 4, 5. So that would be two to the seventh possible options here. So that's not too bad. But if you've got like 50 or 100, you're starting to get a lot of data stored there. So that's you'd want to hold off on something like that. But that is the use case for a type 4 dimension. All right, so dates, dates are super important when you're thinking about a dimensional model with this data, unfortunately it's only at the year level, which doesn't really give us a whole lot of. It doesn't really doesn't give a whole us a whole lot of information in certain ways. So the data that I assigned with it is just the zodiac year with it. But if you're thinking if you store at the atomic level the lowest grain, just day hours or even orders, if you're doing more entity based then you can get. This data table can get really big, it can turn into quarters, it can turn into weeks. Whatever special date functionality that your company might use, you can store it in this table. What we're doing here is we're. This is just some fancy recursive CTEs but basically we're just loading this data in. And so what I'm going to do here is I'm just going to. Let's give you guys a brief introduction or show you guys what this looks like. Why is. Did I not run it. Interesting. I will keep on going since this isn't super critical to this. So we are going to move along onto this. Okay. We're finally at the facts. Like this is the important stuff. I know we're getting close, but this is kind of where we wrap Everything up all together real nicely. So we want to talk about the different types of facts that we have here. And so we have that central facts table. So we've got this year ID, company ID, all of these different IDs, but then these are the facts right here. We got sales, research, budget, employee count, offering count and category count. So what's going to happen here is sales, if you think across years, you can add those together and that makes a meaningful statistic. Same with research budget like you want, you can add together the different research budgets together. Employee count, not really. It's not like you have five employees one year, 10 the next. You add those together. You don't have 15 employees, you still have just 10 employees that year. So you have to be careful, particular when you're working with your analysts and how they view these. Some people don't like to commingle additive and non additive facts. That's a personal business choice. And so we are going to add all of these together in a big chunk of SQL here We are doing the same thing. We are keeping track of all of our names. And then we are adding everything. We are adding these facts and note we're not doing distinct here, we're just doing regular select. We're adding in every single row. We are just converting this data. All right, so now we can just do it. This is just a quick look just to make sure that some stuff that, that things work as anticipated. So for example, we're going to look at this very last. Yes, because I changed the name of things so I will comment that out. So we are going to look at companies. All right, so basically we're just taking a look at the total number of locations that we have of company. Yes. And then basically what we're doing here, okay, we're seeing that we have 333, 388 different locations. When we drew our company's data set to our locations data set. And this is kind of a way that I use just to do a gut check and I do know that those numbers are correct. In the interest of time, we're going to keep going. All right, star data model. Okay, we are at the star. So looks a little bit different. So we've got very similar foreign keys on this side. But if you see here we've got this company dimension, we've just collapsed everything down into that one. Why might you do that? Okay, so analytically it's a little bit better. You have to do less joins. Right. So if you wanted to get to latitude and longitude, you, you have to do one additional join and joins are extraordinarily expensive. That's what really gets you your really long running queries, especially if they're not done well. So we're going to update our schema. We're going to look at STAR here and then I am just going to, we're going to create a whole new companies table in this STAR schema. Alright. So we do that. So we have that and then we're going to do our insert into operation. And if you notice it looks, it definitely looks, it looks a little bit different here than before. Right. So it's a little bit longer but there are also less calculations of hashes. So that, that's also one thing that's a little bit easier. But alternatively it can be harder to update these if your schema changes. So it's really a give or take. This is where you have to do a lot of thinking in terms of what you want to do. All right, so we've added that. All right, so what we're going to do here is. So I'm going to run some queries right now. So we are going. And we're going to show you kind of the difference real fast. All right. What? Okay, so we made those two things. Why are they different? Right, so then we're going to do. Right here. All right, and we are. All right, so this is our, our snowflake model right here. Okay. We're taking the sum of the sales by zip code and then we're ordering them. All right. So we have the exact same fact table, which is nice. So we're going to do that. And then we only have to do change to Star dot companies and we have essentially the same queries. All right, so numbers match up.598. So those numbers match up. So we're getting the same query here. All right, so let's take a look at the difference then. So if we've got these two queries, they create the same thing, same number of rows. Let's go here. We're using the handy dandy timeit function here. We're gonna do 10 iterations here. All right. 117 microseconds to do that on the snowflake schema. Ah yes, I have to change that as well. 109 microseconds. All right, so we saw an improvement. When I did this at home it was a little bit better. It was like 180 and like 70, but you get the gist of it. So we're doing Less joins here, right. We are joining less data together, so it's going to run faster. So if you do that over and over again, that can really make a huge difference. And so with that, there's lots that you can do here and there's a lot of other optimizations that you can carry out. Let's see what in particular. So there's Kimball. The Kimball group has some, some watch outs for analytical processing and they have some. 10 different, they have 10 different ideas for that. And so I believe I had a. We can pop that in the chat, but that's what I have. So what. This really gets you, right? So we implemented the same queries, they ran a little bit faster. So modeling makes a difference, Right? We didn't even talk about what it looks like if you're trying to implement a semantic layer, if you're trying to get data consistency and how you manage that. But having a model gives you something you can talk to all your stakeholders with and it allows you to really do the fun stuff on top of it. With me, that's everything I got there. Let's see if the.

All right, super, thank you very much, Andy. That was really great stuff. And it is interesting to see how just by changing the shape of your data, you're going to be able to have more performant queries, spend less time waiting for things to run. All right, we've got about five minutes for the end. We go to audience questions in a moment. Before we get to that, just like to let you know we have got some more events coming up. So there is a webinar on Thursday about combining data strategy with a people strategy. So that's a leadership focused webinar. And then next Tuesday, the next live training is on responsible AI. So it's how you go about evaluating your machine learning models using Python, evaluating them for fairness and things like that. So if you are involved in creating any machine learning models, please do show up next Tuesday. All right, so first question from the audience comes from an anonymous LinkedIn user saying, can you tell us a bit more about DuckDB? What's the point of it?

All right, so DuckDB, if I had to do this before, there would have to be a data set loaded, there'd have to be a query and there'd have to be a database set up for me. But this, I literally ran it on my, I did most of the development on my home PC. Makes it super easy to spin up stuff also. So the, the DuckDB engine, it runs with a bunch of different interfaces. The Python is just a nice wrapper on top of everything. But I really like it for the use case of potentially doing your transformations all in SQL instead of going to Pandas where you then have to switch to a different language. You can have everything in SQL if you scale up and then you can't use DuckDB anymore. If you can't be on a single node, a single piece of compute, then you can easily scale it out. Lots of cool things about that. They're always changing.

It does make things spectacularly easy to get going and analyzing. All right, so there are a few questions around MD5, so can you maybe just explain what hashing is and why you want to index your data?

All right, so for dimensions, using the MD5 hashtag, it gets you. So it's essentially creates a unique string or a nearly unique string. That is a downfall is that there is a potential for what they call collisions. So you can potentially have two strings create the same hash. You have to be in the range of 2 to the 64th data entries to do that though, which is a lot, especially on a dimensional data set. You're not going to likely run into that. If you do get into really big data, that is something to concern. So on facts, you actually do in certain cases want to use integers or dates. Because if you think about it, if you want to partition your data, the easiest way is the date, right? So I want to look up stuff from 2019, if there is an easy way, if you can naturally store it in like a integer format like year, your year, month, month, date, date, where it's just a string of integers that relate to that, the query engine can actually index based off of that and give you faster performance for dimensional joining. MV5/ hash is nice, but other primary keys work better as integers.

All right, good tip. Next question comes from Roy saying, what are the data validations you can use beyond just saying not null.

I didn't implement it in here, but you can do foreign key which is very similar. It's a not null. But then it also looks at another table, it'll prevent you to say hey, I'm not going to let you put this data in here because it doesn't reference this other table in a way that I recognize. So that's one, there's that unique and there you can actually put in some SQL systems custom constraints as well. And a big piece is to kind of do this beforehand, right? If you have these constraints once you load your data, you are in the process, it fails because of this constraint. You don't want to be called at 3am because of that. You want to be doing a testing ahead of time and maybe getting an alert before beforehand and that. So try these constraints are nice, but try to get clean data before you do these.

Okay, so maybe solve the data cleaning problems like before it goes into the database rather. Join. Okay, Michael asks, does the data always start out CSV or do you ever use JSON?

No, JSON is very popular obviously that's how. So a lot of APIs, application program interfaces, that's how you pull a lot of data, you make a request and then you get back a JSON or XML or other semi structured data where you don't know the schema ahead of time. DuckDB I believe has that functionality, but pretty much every modern database engine has the ability to parse JSON files. I believe there are a couple of courses on datacamp that cover that or will soon potentially cover that as well. There is the functionality to do that. It requires a little bit different syntax but it's doable. That's where testing is really important because you are never, you are not guaranteed that specific attributes or specific key value pairs exist.

There definitely are courses on importing data from JSON. If you look for like Python and importing and I can't remember the name, there's one called like Optimizing Pandas or something like that which just cover bringing oh streamlined data in Pandas, I think it's called which Discover importing data from JSON. All right, so with that we are at time. Sorry, didn't get to absolutely everyone's questions but thank you once again. Andy. Yeah, really interesting session on yeah how you go out structuring data. So thank you for joining us.

Thank you.

And thank you also to Reece Moderating. Thank you to everyone who asked a question. Thank you to everyone who showed up today. Please do join us again next Tuesday for the Responsible AI Live training. Hope to see you all again soon. All right, thank you.