Data warehouse
lastmod 2026-02-22created 2017-01-225 edits
A data warehouse is a database that converts data accumulated in the databases of core business systems into a common format and manages it, in order to help users make decisions. It is also abbreviated as DW.
Traditional data warehouses were designed around structured data. They use a Schema-on-Write approach, in which data is cleansed and transformed in advance through an ETL (Extract-Transform-Load) pipeline and then loaded. Representative examples include Teradata, Oracle Exadata, Amazon Redshift, and Snowflake, and they are optimized for SQL-based analytical queries.
Data quality and consistency are high, but schema changes are difficult and there are significant limitations in handling unstructured data (images, logs, JSON, etc.).
Overview
The term is a compound of data and warehouse. Accordingly, because a data warehouse efficiently integrates, coordinates, and manages the individual database management systems operated in a distributed manner across a large organization, it provides a practical methodology that serves as a foundation for an efficient decision-making system.
A data warehouse consists of management hardware, management software, extraction, transformation, and sorting tools, a database marketing system, metadata (meta data), and end-user access and utilization tools.
As a management methodology, it is a paradigm for making efficient use of a company's information assets: a structural and integrated technology environment aimed at the time-series accumulation and integration of data to support efficient decision-making from the company's strategic perspective. Databases are used by linking data tables scattered here and there.
Thus, a data warehouse is a collection of data that supports executives' decision-making and has four characteristics: subject-oriented (subjectoriented), integrated (integrated), time-variant (timevarient), and nonvolatile (nonvolatile).
Through subject orientation (subject-orientation), data is organized by subject so that it takes a form that is easy to understand even for end users (end user) and analysts who are not strong with computers; through integration (integration), data is converted into a consistent form (consistent naming of data, consistent measurement of variables, consistent coding structure, etc.) when it enters the data warehouse.
Therefore, through time variance (time-variancy), the data in a data warehouse represents accuracy over a certain period, and through nonvolatility (nonvolatilization), once data is loaded into the data warehouse, no changes such as “Insert” or “Delete” are performed other than updates by batch processing (batch) jobs.
The dictionary meaning of warehouse is simply a storehouse, but a data warehouse refers to an information management system that supports decision-making within an organization, including not just data but also analysis methods.
It centrally manages not only specialized data but all information needed across business activities, and consists of a raw data layer, a data warehouse layer, and a client layer, performing functions such as extracting, storing, and querying data. Because it centrally manages all information about business activities in a company-wide database, its capacity ranges from several hundred GB to several TB.
As a result, this was difficult on existing platforms such as large mainframes due to time and cost constraints, but it became possible with the advent of parallel servers and the increasing capacity and decreasing cost of magnetic disk devices. This is because, with a parallel server, a single search request can be split and processed in parallel across multiple processes, enabling high-speed searches. A relational database management system (RDBMS) is used to support parallel servers.
Categories
- Teradata
- Oracle Exadata
- Amazon Redshift
- Snowflake