Data Warehouse

A large company rarely stores its data in a single place. The online shop has its own database, accounting has another, the warehouse and customer service each have their own again. Every one of these systems is designed to handle daily operations: taking an order, writing an invoice. But anyone who wants to know how revenue in Bavaria has developed over five years has to bring all these sources together. That is exactly what the data warehouse is for: a second, large repository into which copies of all important data flow and are brought into a uniform form. The English name means literally "data warehouse," and that describes it quite well.

Why companies store their data twice

At first glance it seems wasteful to store the same data a second time. But there are two solid reasons for it. The first is speed: a large analysis often reads millions of rows. If it ran on the online shop's database, that database would become slow. Customers would then no longer be able to place orders while the analysis was running. The data warehouse cleanly separates the two.

The second reason is consistency. In the shop, a field might be called "customer number," in accounting "debtor," in customer service "client ID." The same person is meant. As long as no one merges these terms, the systems cannot be evaluated together. The data warehouse establishes a binding structure into which all sources are translated.

Then there is memory. Operational systems usually only store the current state: a customer's current address, today's stock level. A data warehouse also preserves old states. Only this makes it possible to compare developments over years, and that is exactly what management and controlling need.

The path the data takes in

Data does not get into the warehouse by itself. It is fetched by programs that typically run at night, when there is little activity. This process is called ETL, short for the three English steps Extract, Transform, Load. Extract means: read out from the source systems. Transform means: rename, standardize, clean up errors and duplicates. Load means: store it in the warehouse.

Internally, the data is organized differently than in a normal database. The so-called star schema is common. At the center is a large table with the events, for example all sales. Around it lie smaller tables with the corresponding descriptions: product, branch, date, customer. This arrangement is tailored for fast summing and grouping.

A common mistake is confusing it with the data lake. A data lake takes in data raw and unsorted, including images, videos, or log files. A data warehouse requires a fixed structure beforehand. The lake is the raw-material storage yard, the warehouse the sorted shelf. Many companies today operate both side by side.

From the quarterly figure to the training dataset

A data warehouse is rarely visible itself. One sees its result: the dashboard with the revenue curves, the quarterly report of a public company, the recommendation "other customers also bought." Government agencies and health insurers work this way too. Almost every figure that a large organization publishes about itself has, at some point, passed through a data warehouse.

In business news, the term mainly appears in connection with the providers. Snowflake, Google BigQuery, Amazon Redshift, and Databricks rent out such storage as a cloud service, meaning computing power that one rents rather than operating oneself. Analysts view their stock prices as a gauge of how much companies are currently investing in data analysis.

For AI, the topic has gained additional significance. Anyone who wants to apply a model to their own business data needs that data clean and complete in one place. A well-maintained data warehouse is therefore often the prerequisite for any AI project in a company. The unspectacular part of the work lies here, not in the model itself.