A Data Warehouse (DW) is a system in computing used for the storage, query, and analysis of data. A DW is a central repository for the integration of data from one or several sources, such as transactional systems, databases, files, and spreadsheets. It stores historical and current data in one place, allowing users to perform complex analyses and obtain valuable insights about the business.
Origins of Data Warehouse
To tell the story of the Data Warehouse, we need to understand the scenario before its development. Currently, we have two types of databases: transactional and analytical. The transactional database is responsible for storing all the technological structure and data of the company, including raw and support data, while the analytical database provides the technological and data structure needed for data analysis activities.
In the 70s and 80s, the only type of database available was the transactional one. It was used simultaneously for service support and for analysis and reports, causing delays and problems in both areas, affecting the effectiveness of services and analyses.
The concept of DW originated in the late 80s when IBM researchers Barry Devlin and Paul Murphy developed what they called a business data warehouse. The goal was to create a system where data flowed from the operating system to the decision-makers' sectors in a fluid and integrated way.
Thus, the DW is mainly focused on queries and complex analyses, while the transactional database is responsible for the insertion, updating, and deletion of data. This division is essential to ensure the effectiveness of the analyses of the analytical database and the proper functioning and security of the transactional database.
How Does a Data Warehouse Work?
We then enter the area of data architecture, which is responsible for implementing and managing the DW. The data architect has knowledge about business and understands the needs of the company and will be responsible for studying the existing data and deciding which business rules and information should be part of the DW.
The structure of a DW is developed to enable efficient query and analysis of the stored data. Thus, when constructing a DW, it is necessary to know what the data will be used for.
In the DW, dimensional modeling is used, in which data is organized around topics or subjects. There are two main models of dimensional modeling: the Star Schema and the Snowflake Schema.
The data will go through a process called ETL or ELT, which stands for Extract, Transform, and Load data.
The Importance of Data Warehouse in a Business
The DW is mainly focused on queries and complex analyses, bringing several advantages to companies. With the implementation of the DW, the company can store its data from different sources in one place, facilitating the understanding of data and allowing greater accuracy in analyses.
The company can reduce financial and time costs associated with extracting and transforming data. The data analysis area can dedicate more to the analyses themselves and less time searching, preparing, and cleaning the data to be analyzed.
The DW can be accessed by several users simultaneously, aiding the interaction process between sectors. Additionally, with its base of historical data, the DW serves other data areas, such as data science, allowing the company to predict future trends and behaviors.
Overall, the Data Warehouse assists the company in decision-making, whether by analyzing patterns or by projections.
Data Warehouse vs Data Lake
Another term often discussed in this data universe is Data Lake, and it is a common doubt to know the difference between this and the Data Warehouse.
The Data Lake is a centralized repository of raw data, stored in its original form. That is, in the Data Lake, the data are not treated or structured. The data can come in various forms and from various sources, without following a defined rule. At this moment, from the receipt of the data, it is still not known what the data will be used for.
Since the data in a Data Lake are not treated, they can be accessed by analysts or data scientists in their original form. Thus, the Data Lake also serves a purpose, as analysts and scientists may need this information without the cleaning and specifications made by the data engineer.
The data in a Data Lake can be used immediately, in a few months, years, or perhaps never; everything will depend on the needs of the professionals in the area. Therefore, Data Lakes are generally built with Big Data technologies, such as Hadoop, Spark, among others. For this reason, the Data Lake is particularly interesting for data scientists, who can work with the data in their native format and perform real-time or near-real-time analyses.
The Data Lake requires data governance and continuous maintenance, so it does not become, as the term in English says, a "Data Swamp."
Data Mart
To conclude our article, it is interesting to mention the Data Mart. The Data Mart is a repository aimed at serving specific teams, such as marketing, sales, HR, among others, providing only the information that these areas need. The Data Mart is a subset of the Data Warehouse and contains only the data relevant to a certain department, being created from the Data Warehouse through the selection and grouping of data.
ETL or ELT for a Data Warehouse, Data Lake, or Data Mart
The process of ETL (or ELT) to centralize data in a Data Warehouse, Data Lake, or Data Mart can be quite complex, demanding hours of development, API study, definition of replication strategy, among other important aspects.
However, this process can be facilitated with data integration platforms, like Kondado, where it is possible to use pre-built connectors from more than 80 tools and configure data integrations with just a few clicks.