What is a Data Warehouse? (Database vs. Data Warehouse Explained)

database and data warehouse. Tell me do you know the difference between them? I'm sure you can give the definitions but do you truly understand what makes a database different from a data warehouse and when you should use a database data warehouse and what is the main advantage of both of them. It's a very simple concept but unless and until you have worked on projects where you have used both of them or one of them it would be very difficult for you to clearly understand the difference between them and that is what I want to simplify in this video. Now this video is taken from one of the chapters from my SQL beginner course that is present on SQL Nest. So if you like this video, if you understand this concept and if you want to learn other data warehousing concepts or other database and SQL concepts, then check out my courses on SQL Nest. You'll find a link in the video description. So I'm going to split this video into four different sections. In the section one, I'm going to talk about a quick definition and a very simple basic example. The section two and section three are going to be where I'll talk about the two major differences between a database and a data warehouse. And the last section I'm going to give you like an use case an example which will help you to understand how exactly a database and a data warehouse is actually used in a company. Now I do not have any fancy video editors to make this video really professional. I'm going to just share with you my experience of using a database and a data warehouse for the past 16 years. Hopefully that should be good enough for you to get the knowledge that you need about these two systems.

What is a data warehouse? In the most simplest of terms, data warehouse is a database. It can be used to store and analyze the data. But you treat data warehouse to be like a type of a relational database. Now both can be used to perform the same things. That is you can use database and data warehouse to store data and to analyze the data. But the difference comes in the type of data that you store. So store into a database and into a data warehouse that differs and the way you store it that also differs. Let's understand this with an example. Let's say there is someone who is in a school let's say in class 10 and they have several different subjects right in class 10 maybe you have mathematics, science, social studies, English and whatever else right now for each of these subjects you would generally have a notebook right where you would bring the notebook every day to the class. You would make a note of everything that has been taught to you. You write it down. It's like your daily journal about everything that is happening in that specific subject. Right? So you will have a notebook for mathematics, a notebook for English, a notebook for science, etc. Now each of these notebook you can consider it to be like a database. So you have a database for a specific subject. So mathematics of class 10 is basically in a different database. Science is in a different database etc. So each of this notebook you can consider it to be a database. So every day whatever activities that you are doing everything that you are learning you note it down in that notebook and that is basically your database. And now you imagine that you are in class 10 but you have had this notebook from class one class 2 class 3 for all the different subjects etc. So imagine data warehouse to be a library which stores all of your notebooks from your class one until class 10 for every single subject. Right? So data warehouse is that library that stores everything all the historical data all your historical notebooks is stored into one single place and not just one subject but every subject. This whole collection of data is basically your data warehouse. Okay. So database is basically what you do every day in a specific subject is into one single database. But data warehouse is the whole collection of everything from the beginning of time until today. Everything is stored in one place. That is basically your data warehouse. This is a very simple example but I hope you are getting that idea right now. You'll get better understanding when we move on to the next three sections.

Now let's get into the first major difference between a database and a data warehouse. That is the type of data that is being stored into both of these systems. So a database, a standard database uses OLTP that is online transaction processing. That means it's basically storing your current data. So every day whatever transactions and processing that you're doing that is stored into your normal database. For example, let's say a bank. In a bank, a customer could do multiple transactions in a day. He might go to an ATM, withdraw some cash. He might make some payments to let's say when buying a coffee, he might transfer some fund to his family, etc., etc. So, every single transaction that he's doing, it is immediately captured and it is captured into a database. So, this is basically OLTP, online transaction processing. Every single transaction done at that instant is stored into the database. Now these kind of databases are designed to make it very optimal for writing and updating the data because every time you do a transaction let's say a new transaction you're transferring some fund to someone a new record needs to get inserted into let's say a transaction table so we need to insert something very quickly and let's say every time you withdraw something your balance changes so some updates need to happen right so the online transaction processing that is the OLTP or the normal databases These databases are generally designed so that the writing and updating the data into the database can happen very fast. Whereas when it comes to reading they might not be that fast. Now this is OLTP or basically your normal database. When it comes to a data warehouse it uses OLAP that is online analytical processing. What this basically does is it stores data with an intention that it will use that data for analytic purpose or for reporting purpose. That means it is not really designed to store your everyday transactions. But a data warehouse is designed to store historical data. So it's not bothered about what a customer is doing every single day. But it is interested in what the customer has done from day one until today. So let's say the customer has been with the bank for 10 years or 20 years or whatever from the first day until now everything the customer has done. If you want that information then you generally store that information into a data warehouse. And it's not just about the customer. A bank could have different types of data. data related to all the different investment that the bank is doing or all the different products they are offering. Let's say a loan different types of loans everything right. So for whom have they given the loan in the last 20 years or whatever? Every single information that the bank is doing all the business information if you want to store it so that you can generally use it for some reporting for analytic purposes then you store it into an data warehouse that is OLAP. Okay. So your daily transactions is not generally stored in a data warehouse. It is stored in a database but your historical data. So if you if the bank wants to know what they what a customer has done over the period of time or how the bank has performed over a period of time then that information is stored into a data warehouse. Okay, this is OLAP and these data warehouses are designed in such a way that they might not be very optimal for writing and updating but they will be optimal for reading the data. So they will be designed in such a way that reading the data your SQL queries will execute super fast. Okay, that is how the designing of data warehouse will be done. So this is the difference number one type of data. Everyday transactions goes into a database. If you have a data where you're receiving everyday transactions, you want to store it somewhere, use a database. If you want to store some historical data for analytic purpose, for reporting purpose, then create a data warehouse and store

it there. Now let's talk about the second major difference that is how the data is stored in a database and in a data warehouse. Now in a database generally the data is stored in a normalized format. You might have heard about normalization. First normal form, second normal form, third normal form and so many others. So generally when a data is stored into a database they are normalized and stored. When I say normalized what do I mean? It basically means that I split the data into multiple different tables and data is scattered across multiple different tables. Now the advantage of this is you will not have duplicate data. So generally in a database you avoid having data redundancy. You avoid having duplicate data. Right? So that is why it will be very optimal for writing and updating the data. But reading might be slow. Why? Because your data would be scattered across multiple different tables. when you have to read that data, you will need to join all of those multiple different tables and then read the data which might be significantly slow. So if you have ever seen an ER diagram, entity relationship diagram, let's say user uh orders, customers, products table, so you would have multiple different tables related to one another using foreign key, primary key etc. That diagram basically kind of represents a database. But when it comes to a data warehouse, the data might be stored in a denormalized format. Not always but generally sometimes in a data warehouse it is very common to store the data in a denormalized format. That means you might have a very big table with hundreds of different columns. So all the data is stored let's say in very few tables. Okay. Now what is the advantage of that? The reading becomes very fast. If you want to read the data most of the data would be just present in one or two tables. So you will need to do minimum joins. So reading becomes very fast. Right. The disadvantage is that you might have duplicate data and data warehouse is fine with that. Okay. So this is the difference in a data warehouse. It is okay to have duplicate data but the main focus is readability. The data needs to be read first. So you try to put as much as data into a single table in a denormalized format. Whereas a database you normalize it. You split it into multiple tables to make the write and update efficient but the read could be slow. So generally when you store data in a data warehouse, we generally use the concept of fact and dimension tables. So fact tables are generally used to store business requirements or let's say some measures or metrics. Whereas dimension tables are storing more detailed information which is required to basically calculate a measure or a metric. So the storage differs right in a data warehouse you use fact and dimension tables. you generally use data modeling approaches like star schema, galaxy schema etc. And then in a database you generally store it in multiple different tables in a normalized format. So the storage of data differs between database and data warehouse. The reason for that we store the data in such a way that we can read the data as soon as possible that is in a data warehouse. But when it comes to a database, we store the data in such a way that we're not that much bothered about reading but we are more interested in making it optimal for writing and for updating. Okay. So the storage of data differs because the purpose is

different. Now let's conclude all of this with let's say a realistic use case so that you understand how a major company uses both a database and data warehouse. And in most cases a company would have both. Okay, most companies use both database as well as data warehouse. So for example, let's say a bank. A bank could have different type of data. So bank could have a core banking system. When I say core banking system, it's basically the core operations of a bank. Let's say the different accounts uh that the customers have created, all the different transaction that the customers do, uh their ATM withdrawals etc. So these are all the core bank operations. This could be stored into a separate database. Then there could be another database which could be explicitly used to store information about all the different investment that the bank is doing. Every single day the investment information everything could be stored into a separate database. Then there could be another database which could be specifically designed to store some complex products that the bank offers like loans or different types of loans etc. Then there could also be another database. Let's say if a bank has mobile app or internet banking those information daily transactions could be stored into another database. So a bank even though it's one company one bank they could have multiple different databases to store different type of data. Okay core banking in one database mobile app or internet banking in another database loan and other complex products into another database etc. But then all of these databases are basically helping the bank to run on a day-to-day basis. So for the bank to run and to basically perform their business every day, they need these databases. But then if the bank wants to build some new product or do some analytics or provide some report to some central bank, right? Then they will need to gather data from all of these databases. So if a bank wants to get complete information about a customer and a customer data might be present in all of these databases then you will the bank might need to go from one database to another to fetch all of this data rather generally what we do is we build one data warehouse and we build some pipelines. So we build a pipeline where the data is collected from all of these multiple databases. It collects the data let's say once a day, once a week, once a month and then puts all of this data into the data warehouse. And when I say pulls the data, it not necessarily has to pull every single data based on what you want to analyze, based on what report you want to generate. You might only need some explicit data about customers or products etc. So the data that you need, you pull it from multiple different databases. You build a pipeline for that. You move it into a data warehouse that becomes your analytics layer where you have all the historical data from all the different systems that the bank has and you use that analytics system to basically perform all the analytics and reporting and this is basically your data warehouse. All of these different databases that I talked about like this individual database for core banking for mobile app for internet banking for loan products etc. These could be your traditional databases like Postgress, Oracle, Microsoft SQL Server, MySQL, etc. When it comes to these data warehouses, these could be something like Snowflake, Google, BigQuery, Amazon Redshift, etc. So, I think that's all. This is the difference between a database and a data warehouse. In short, you need a database for the day-to-day activities of a company. Whereas you need data warehouse to gather information from all of these multiple databases and then analyze it and use it for reporting or for some other

analytics purpose. So if you found this video useful and if you understood this concept, let me know in the comments below. And if you have any questions, ask me in the comments. I'll try to answer. If you want to learn more about data warehousing and database concepts or SQL concepts, I have several courses on SQL Nest. Just check the link in the video description and take one of those courses for detailed understanding about everything SQL database and data warehouses. Thank you so much for watching and I'll see you soon in the next