Data Warehouse

A data warehouse (DW or DWH) is a centralized repository for structured, historical data integrated from disparate sources, optimized for reporting and analysis. It is a core component of business intelligence, supporting decision-making by analysts and managers.

In computing, a data warehouse (DW or DWH), also known as an enterprise data warehouse (EDW), is a system used for reporting and data analysis and is a core component of business intelligence. Data warehouses are central repositories of data integrated from disparate sources. They store current and historical data organized in a way that is optimized for data analysis, generation of reports, and developing insights across the integrated data. They are intended to be used by analysts and managers to help make organizational decisions.

The data stored in the warehouse is uploaded from operational systems (such as marketing or sales). The data may pass through an operational data store and may require data cleansing for additional operations to ensure data quality before it is used in the data warehouse for reporting. The two main workflows for building a data warehouse system are extract, transform, load (ETL) and extract, load, transform (ELT).

Components

The environment for data warehouses and marts includes several key parts. Source systems of data often consist of a company's operational databases, such as relational databases. Data integration technology and processes are used to extract data from these source systems, transform them, and load them into a data mart or warehouse. Architectures are needed to store data in the warehouse or marts, along with tools and applications for varied users. Metadata, data quality, and governance processes are also essential; metadata includes data sources (database, table, and column names), refresh schedules, and data usage measures.

Operational Databases

Operational databases are optimized for the preservation of data integrity and speed of recording of business transactions through use of database normalization and an entity-relationship model. Operational system designers generally follow database normalization to ensure data integrity. Fully normalized database designs often result in information from a business transaction being stored in dozens to hundreds of tables. Relational databases are efficient at managing the relationships between these tables. The databases have very fast insert/update performance because only a small amount of data in those tables is affected by each transaction. To improve performance, older data are periodically purged.

Data warehouses are optimized for analytic access patterns, which usually involve selecting specific fields rather than all fields as is common in operational databases. Because of these differences in access, operational databases (loosely, OLTP) benefit from the use of a row-oriented database management system (DBMS), whereas analytics databases (loosely, OLAP) benefit from the use of a column-oriented DBMS. Operational systems maintain a snapshot of the business, while warehouses maintain historic data through ETL processes that periodically migrate data from the operational systems to the warehouse.

Online analytical processing (OLAP) is characterized by a low rate of transactions and complex queries that involve aggregations. Response time is an effective performance measure of OLAP systems. OLAP applications are widely used for data mining. OLAP databases store aggregated, historical data in multi-dimensional schemas (usually star schemas). OLAP systems typically have a data latency of a few hours, while data mart latency is closer to one day. The OLAP approach is used to analyze multidimensional data from multiple sources and perspectives. The three basic operations in OLAP are roll-up (consolidation), drill-down, and slicing and dicing.

Online transaction processing (OLTP) is characterized by a large number of short online transactions (INSERT, UPDATE, DELETE). OLTP systems emphasize fast query processing and maintaining data integrity in multi-access environments. For OLTP systems, performance is the number of transactions per second. OLTP databases contain detailed and current data. The schema used to store transactional databases is the entity model (usually 3NF). Normalization is the norm for data modeling techniques in this system.

Predictive analytics is about finding and quantifying hidden patterns in the data using complex mathematical models to prepare for different future outcomes, including demand for products, and make better decisions. By contrast, OLAP focuses on historical data analysis and is reactive. Predictive systems are also used for customer relationship management (CRM).

Database

A database is an organized collection of data that is stored and managed electronically. It is designed to store, retrieve, and manage structured data using a database management system (DBMS) for querying and manipulating the data.

Data Marts

A data mart is a simple data warehouse focused on a single subject or functional area. Hence it draws data from a limited number of sources such as sales, finance, or marketing. Data marts are often built and controlled by a single department in an organization. The sources could be internal operational systems, a central data warehouse, or external data. Types of data marts include dependent, independent, and hybrid data marts.

Data Lake

A data lake is a centralized repository that stores large volumes of data in raw format that is processed at runtime. It can collect data from multiple sources such as APIs, files, databases, sensors, and websites. Unlike data warehouses, data lakes store data in structured, semi-structured, and unstructured formats, which makes them usable for machine learning and big data processing.

Variants

ETL

The typical extract, transform, load (ETL)-based data warehouse uses staging, data integration, and access layers to house its key functions. The staging layer or staging database stores raw data extracted from each of the disparate source data systems. The integration layer integrates disparate data sets by transforming the data from the staging layer, often storing this transformed data in an operational data store (ODS) database. The integrated data are then moved to yet another database, often called the data warehouse database, where the data is arranged into hierarchical groups, often called dimensions, and into facts and aggregate facts. The combination of facts and dimensions is sometimes called a star schema. The access layer helps users retrieve data.

The main source of the data is cleansed, transformed, catalogued, and made available for use by managers and other business professionals for data mining, online analytical processing, market research, and decision support. However, the means to retrieve and analyze data, to extract, transform, and load data, and to manage the data dictionary are also considered essential components of a data warehousing system. Many references to data warehousing use this broader context. Thus, an expanded definition of data warehousing includes business intelligence tools, tools to extract, transform, and load data into the repository.

ELT and Modern Architectures

In contrast to ETL, the extract, load, transform (ELT) workflow loads raw data directly into the target system, such as a data lake or cloud data warehouse, and performs transformations afterward. This approach leverages the processing power of modern systems to handle large volumes of data. ELT is often associated with cloud-based platforms like Amazon Web Services, Microsoft Azure, and Google Cloud, which offer scalable storage and compute. These platforms have influenced the evolution of data warehousing, enabling integration with Machine learning and Artificial intelligence workloads for advanced analytics.

Historical Context

The concept of data warehousing emerged in the 1980s, with key contributions from researchers and practitioners in the field of business intelligence. The term "data warehouse" was popularized by Bill Inmon in the early 1990s, who defined it as a subject-oriented, integrated, nonvolatile, and time-variant collection of data. Ralph Kimball later introduced the dimensional modeling approach, including star schemas, which became widely adopted. These foundational ideas remain central to modern data warehousing practices, though cloud technologies have shifted implementation details.