Databases vs Data Warehouse
- Ravi R
- 10 hours ago
- 3 min read
Data is all around us. With just about every app utilising some sort of data, it is important to understand the different ways data can be stored and processed, leading to an overall increased user experience and satisfaction. The two data storage systems that will be discussed in this blog post are databases and data warehouses as well as the similarities and differences between them.
Simply put, a database is the collection of data and information that could be retrieved for some use. The Database Management System (DBMS) is a piece of software that enables users to interact with the database. It is worth noting that both the actual database and the DBMS can be referred to as a “database”.
Some of the key functionalities that can be accomplished through the DBMS include:
Data Storage - Providing means of storing data in a centralised location
Data Retrieval - Efficiently retrieving data from the database through queries or commands
Data Manipulation - Perform create, read, update, and delete (CRUD) operations on data
Data Organisation - Storing data in a logical manner.
There are various types of databases that fall under the broader category of database systems. The two most utilised database types are relational databases and non relational databases. Relational databases store structured information into tables and these tables can be linked together with the use of a foreign key, making it easier to query and combine data from multiple tables. Furthermore, relational databases are commonly queried with Structured Query Language (SQL) and can perform operations such as create, read, update and delete or CRUD for short. Conversely to relational databases that store structured data, non relational databases (also referred to as NoSQL) has the ability to store unstructured data, semi-structured data and in some instances structured data as well.
Structured data is defined by having a fixed schema that fits into the rows and columns of a table. A fixed schema is a predefined structure that specifies how data is stored, including the columns and their data types, for example a name column having a data type of VARCHAR and age column having a data type of INT. Unstructured data does not have a fixed schema and has the ability to store more complex pieces of data, for example video and audio files. Finally, semi-structured data is a hybrid between structured and unstructured data.
The purpose of data warehouses is to combine large amounts of data from multiple different data sources into a single central repository for analysis. Furthermore, data warehouses utilise the ETL process (extract, transform, and load) to prepare data for analytics.
The ETL process extracts, cleans, and organises data from various sources into a dataset that would be stored in a system such as a data warehouse.
Extract - Raw data is collected from various locations such as SQL or NoSQL servers, emails, web pages, and other systems and is temporarily stored in the staging area. The extracted data can be structured or unstructured.
Transform - The extracted data in the staging area is cleaned, formatted, and transformed to make it fit for analysis.
Load - Finally, the newly transformed data is transferred from the staging area to the data warehouse. The loading process generally takes place outside of peak using times to avoid performance issues and disruptions.
Data warehouses typically use a three tier architecture approach to transform data for analytics, consisting of bottom tier, middle tier, and top tier. In the bottom tier, the various raw pieces of data go through the ETL process to clean and format the data to be stored in the warehouse. The middle tier contains the OLAP (Online Analytical Processing) server that acts as a medium between the bottom and top tiers. The OLAP ensures the quick retrieval, and collection of data ready for analysis and acts as a bridge between the bottom and top tiers. Finally, the top tier contains the front end interface that provides tools for viewing, analysing, and reporting data through dashboards, reports, and other means.
In conclusion, databases and data warehouses both serve important roles in different ways. Databases are designed to store and retrieve data while data warehouses are designed for the analytics, reporting, and visualisation of data. Understanding the difference between these two pieces of technologies helps organisations choose the right tool for the right task in addition to ensuring data is stored, processed, and analysed effectively and efficiently.