Key takeaways
- A data warehouse brings together data from multiple sources, with fixed definitions and history, specifically for reporting and analysis.
- With ETL, you transform data before loading it; with ELT, you load first and transform in the warehouse itself.
- A data lake stores raw data in any format; a data lakehouse adds table structure and governance on top.
- For AI on figures, a data warehouse is not mandatory, but it does make the answers more reliable and more secure.
What a data warehouse does
Source systems such as an ERP, CRM or point-of-sale system are built to record transactions quickly and correctly. They are not designed for questions that span systems and years. Each system also has its own codes and definitions: a customer in the CRM is not automatically the same customer in the accounting system.
A data warehouse solves this. It periodically extracts data from the sources, makes connections, applies one set of definitions and keeps history, even when a source system overwrites data. The result is a single source of truth: one place where revenue, margin and stock mean the same thing to everyone.
An example: Acme Groothandel, a wholesaler, wants to see the margin per customer segment per month. The orders are in the ERP, the segments in the CRM and the purchase prices in a separate price file. In a data warehouse, those three come together in one model on which dashboards and analyses run.
ETL and ELT: how data gets into the data warehouse
Data reaches the warehouse through a pipeline. With ETL (extract, transform, load), you extract data, process it in a separate tool and load the result. With ELT (extract, load, transform), you first load the raw data into the warehouse and then transform it with SQL in the warehouse itself. ELT has become common in cloud environments, because storage and computing power are flexible there.
There are many tools for extraction, such as Airbyte, Azure Data Factory and the pipelines in Microsoft Fabric. For transformation in the warehouse, dbt is often used. Which tool you choose matters less than making sure the loading processes are monitored and that someone owns the definitions.
| ETL | ELT | |
|---|---|---|
| Order | Extract, transform, load | Extract, load, transform |
| Where you transform | In a separate tool or server, before loading | In the data warehouse itself, usually with SQL |
| Raw data kept | Often not | Yes, in a separate layer |
| Suits | Fixed structures, or sensitive data you want to filter beforehand | Cloud warehouses, changing questions, recalculating afterwards |
Data warehouse, data lake or data lakehouse
Besides the classic data warehouse, you will come across two other terms. A data lake is a store for raw data in any format: tables, files, logs or images. You only define the structure when reading the data. A data lakehouse combines both: the storage of a data lake with open table formats such as Delta Lake or Apache Iceberg, so that you can also work on it with SQL and BI tools.
A data lake platform or lakehouse is mainly of interest with large volumes, many types of data or data science. Microsoft Fabric, for example, is built around OneLake, a single central data lake for the whole organisation. For many SMEs with mainly ERP, CRM and financial data, a relational data warehouse is enough, and simpler to manage.
| Data warehouse | Data lake | Data lakehouse | |
|---|---|---|---|
| Type of data | Structured (tables) | Everything: tables, files, logs, images | Everything, with a table layer on top |
| Structure | Defined in advance (schema-on-write) | Defined when reading (schema-on-read) | Open table formats with a schema |
| Typical technology | Relational database, such as PostgreSQL or SQL Server | Object storage, such as Azure Data Lake Storage or Amazon S3 | Object storage with Delta Lake or Iceberg, such as OneLake or Databricks |
| Strong at | Reporting, BI, consistent KPIs | Large volumes, raw data, data science | Combining both on one platform |
| Watch out for | Less suitable for unstructured data | Without governance, it becomes a messy "data swamp" | More components and specialist knowledge needed |
Layers and the data model in a data warehouse
A good data warehouse works with layers. In the first layer, raw data lands as it comes from the source. In the second layer, it is cleaned and merged: duplicate customers removed, codes translated, dates standardised. The final layer is organised for users and tools. In lakehouse environments, these layers are often called bronze, silver and gold.
For that final layer, the star schema is the standard. At the centre is a fact table with events and amounts, such as order lines; around it are dimension tables with descriptions, such as customer, product and date. Power BI works best with a model like this, and AI that writes SQL benefits from it too: understandable names and clear relationships increase the chance of a correct query.
Do you need a data warehouse for AI?
Not strictly, but for AI on figures it makes a big difference. An AI model that writes SQL on the raw tables of an ERP system has to deal with cryptic names, technical codes and missing relationships. On a data warehouse with understandable names, agreed definitions and a data dictionary, the answers are considerably more reliable.
There is also a security argument. Queries then do not run on your production system, you decide exactly which tables are available and you can enforce read-only access. For AI on documents (RAG), you do not need a data warehouse, but a good document index.
ENABLE works with a PostgreSQL data warehouse per customer. Sources such as Microsoft Dynamics 365 Business Central are loaded outside the portal, for example with Airbyte, and then used in ENABLE for dashboards, the SQL Explorer with AI and the Data API.
When a data warehouse is worth it
If you have one source system with good built-in reporting and little need to combine data, you can often still manage without one. In any case, start with the questions you want to answer and not with the technology: a warehouse into which everything is copied one-to-one, without a model and definitions, solves little.
A data warehouse is an investment in time and management. It usually pays off if you recognise one or more of these signs:
- You combine data from multiple systems, currently by hand in Excel.
- Different figures for the same concept circulate in meetings.
- Reports on your ERP system are slow or put a strain on day-to-day operations.
- You lose history, because source systems overwrite old values.
- You want to give AI or external systems access to data without letting them into your source systems.