OLTP vs OLAP Explained in 5 Minutes

In the previous video, we have understood what a data warehouse is. Now, a very common question that you might ask yourself is if a data warehouse is just another database, then why can't we perform all of those activities like reporting, storing historical data, performing analytics? Why can't we do everything on a database? Why do we separately need a data warehouse? Now the answer to this question lies in your understanding of OLTP and OLAP systems. If you understand the difference between OLTP and OLAP then it will make sense on why you need to have a separate data warehouse and database. Now OLTP can be treated as the heartbeat of your business whereas OLAP can be treated as the brain of your business. Let's start with OLTP. OLTP stands for online transactional processing. Now databases such as Postgress, MySQL, Oracle, Microsoft SQL Server, these are generally treated as OLTP systems. Now these OLTP databases are designed in such a way that they should be able to handle thousands of tiny lightning fast transactions per second efficiently. For example, if you take the example of a bank, as a customer, you might be performing several transactions in a day. Let's say in a net banking you might want to update your profile or change your password. You might want to transfer some funds to your friends etc. All of these transactions can be efficiently handled by OLTP databases because they are designed to handle these write and update operations very efficiently. For example, let's say if two people are trying to book the last seat on a flight at the same time, the OLTP systems will be designed in such a way to handle these concurrent transactions efficiently without causing any errors. So these OLTP databases can help your business run the day-to-day activities very smoothly without causing any issues. Now coming to OLAP which basically stands for online analytical processing. So data warehouses such as Snowflake, Amazon, Redshift, Google, BigQuery are examples for OLAP systems. OLAP systems are designed in such a way that they don't care about your daily transactions. What you are doing every minute, every day, they don't really care about that. They are looking at the bigger picture. OLAP is optimized for read operations. If you want to scan millions of rows to calculate let's say yearon-year growth of your business or customer churn rate then these kind of reports can be efficiently built on OLAP system and that's why we say that OLTP is the heartbeat so that it makes sure that everything runs smoothly on a day-to-day uh basis whereas OLAP is the brain of the business that can analyze what has happened so it can help you to better understand your business so that you can take the right steps forward. Now the big question is why can't I perform these OLAP operations directly on an OOLTP system? So why do I need two separate databases or two separate uh systems here? Now let's understand this with an example. Imagine that you only have OLTP system in your business. Okay. And your and let's say it's a bank. So all of your daily transactions everything is basically getting stored into your OOLTP database. And let's say you have some data analyst, data scientist or some other engineers who are trying to do some analysis, build some reports on that same OOLTP system where you have all the historical data. Okay. Now it could happen that your data analyst could run some query to let's say find the yearonyear growth of your business which is like consuming a lot of memory and lot of CPU because they are writing a very complex uh SQL query. This complex SQL query might consume all of the memory and CPU that is available which might in turn lock the table that let's say a transaction table or something and this might impact your day-to-day transaction that the customer is doing. Let's say a customer is trying to withdraw something from the ATM and it needs to update the transaction table but your transaction table is already locked because your data analyst ran a very complex query which is using this transaction table. Right? So your OLAP operations done by your internal data analyst is now impacting your day-to-day business that the customer was trying to perform. Right? So this is why you do not want to have one single database for OLAP and OLTP because your OLAP operations which might consume a lot of resources might interfere with your day-to-day activities that could happen on an OOLTP system and that is why you want to segregate the OOLTP systems separately and you want to have a separate uh data warehouse for all of your OLAP operations. So this is basically the science of separating the OLTP from OLAP. OLTP is for your day-to-day daily transactions. OLAP is for your historical data so that you can analyze it, build reports and understand your business better. I hope this makes

sense and I hope you now understand what is the difference between OLAP and OOLTP and why we need to separate these two systems.