Data Warehouse

The data is spread across ERP, CRM, the online shop and spreadsheets, and every analysis gathers it again from scratch. Depending on the source, a different number comes out. What applied before the last reorganization often can’t be reconstructed.

A data warehouse is the answer to that: a central database that exists only for analysis. The source systems keep recording transactions and orders. The warehouse loads a cleaned copy on a schedule, keeps the history and makes it available for reports, analyses and AI. The semantic model with the metrics sits on top of it; how it is built is covered under data modeling.

When a warehouse pays off

At the latest when reports need data from several systems and someone merges it by hand. Other signs are reports that run directly on the production database and slow it down, numbers that differ from report to report because each carries its own logic, and a current system that only one person still understands. With a single source, a BI tool can connect directly, and a warehouse would be overkill.

This is how we separate it from the data lakehouse. The warehouse holds structured, prepared data for reporting. For most companies whose data comes mainly from ERP, CRM and shop, it is the simplest solution. Raw files, logs, documents or training data for AI are more a case for the lakehouse, which has its own page. The two can be combined.

Three layers, so errors have a fixed place

We build a warehouse in layers. First come the raw data, taken over from the sources unchanged. That looks like wasted space but is the insurance: if something goes wrong in processing, we recompute without touching the source again.

The second layer cleans. Types are aligned, duplicates removed, keys matched. This is where the unified view of customers, products and orders is created. The third layer is the business layer. It is modeled for analysis, usually as facts and dimensions, and BI tools and metric models read from here.

In the Databricks world the stages are called bronze, silver and gold, known as the medallion architecture. What they are called doesn’t matter. They have to be cleanly separated.

Loading, transforming, keeping history

Data used to be transformed before loading (ETL), usually in a separate tool. In cloud warehouses ELT has become the norm: load raw first, then transform inside the warehouse. The compute power is already there, and the raw data stays available for later questions. How data gets out of the sources is a topic of its own, covered under data pipelines.

We maintain the transformations as SQL code in a repository, usually with dbt. The code is versioned, tested and runs automatically. It stays traceable who changed what and when, and several people can work in parallel. Wherever possible, only what is new or changed gets processed, not the whole data set every night.

History is handled with slowly changing dimensions. When a customer changes region, you can overwrite the old value (type 1), or the old record stays with a validity period and a new one is added (type 2). Type 2 is the standard when reports should also show the state at the time, such as the revenue of a region before the reorganization. Every tracked field costs storage and maintenance, so we agree with the business teams which fields really need it.

Data Vault or dimensional model

For the layer between raw data and business view there is Data Vault. It separates business keys (hubs), relationships (links) and descriptive attributes with their history (satellites). New sources or fields can be added without reworking existing structures, and every change stays traceable. The price is considerably more tables and more effort in modeling and loading.

We recommend it for many sources, frequent changes and strict audit requirements. With a manageable number of sources, a classic dimensional model is simpler and faster to finish. A common mix is Data Vault at the core with dimensional models on top for reporting.

Which platform

Modern cloud warehouses separate storage from compute. Both scale independently, and you only pay for compute while data is loaded or queried. The choice depends less on feature lists than on the surroundings: which cloud is already in house, what can the team operate, where may the data live, and how is it billed, by data scanned, by compute time or by reserved capacity? With moderate data volumes, a classic database such as PostgreSQL is often enough.

Moving out of a grown warehouse

Many companies still run a warehouse on an on-premises database or in SAP BW that has grown over the years. A move to the cloud makes sense when hardware would have to be replaced, load runs no longer fit the time window, or new requirements such as AI can’t be met. We work in steps: an inventory of load processes and reports first, then a target picture, then the move in topic blocks with parallel operation and a comparison of the numbers.

Most often this fails because everything is copied one to one. Old logic that nobody understands simply moves along. It is better to review it and simplify where possible. Just as common is the old system that keeps running because nobody set a shutdown date, until two systems are being maintained. And without an owner for each metric, conflicting definitions survive in the new system too. Unplanned queries and overly frequent load runs also drive up consumption, which is why budgets and monitoring are in place from the start.

Tests on every load

A warehouse is only useful if people believe the numbers. That is why automated tests run on every load. Are the keys unique? Are required fields missing? Does every order have a customer? Are totals within the expected range, did the data arrive on time, and do the totals match the source system?

If a test fails, the error is reported. The faulty data then either doesn’t reach the reports or is clearly flagged. Broader rules on ownership, catalog and access belong to data governance.