Guide to Data Warehouse (DWH) Architectures: Which One Should You Use When?

Traditional (Enterprise) Data Warehouse Architecture (On-Premise DWH)

This architecture has been the industry standard for decades. It is generally built on powerful relational databases hosted on physical servers.

• How It Works: Data is pulled from sources such as CRM/ERP and processed on an intermediate server (ETL). Afterwards, it is loaded to the target with Star Schema. That is, the data is shaped before it enters the warehouse (Schema-on-write).
• Who Should Use It: Companies (Banking, Public) where the data volume is predictable and data does not go out due to regulations.

Cloud Data Warehouse (Cloud DWH)

They are completely cloud-based structures that eliminate the problem of hardware management (Ex: Azure Synapse Analytics, Analytics). Snowflake).

• How It Works: Separates Storage and Processing Power. While keeping the data cheap in the cloud, it only supports powerful servers when you query (ELT).
• Who Should Use It: Institutions where the data volume grows very quickly and needs sudden processing power in periodic workloads (e.g. month-end reporting).

Data Lake

Data Lake, where all kinds of structured and unstructured data (Log files, images) are stored in huge and cheap pools. systems.

• How It Works: Data is thrown into the lake in raw format without transformation. The person who reads the data creates a schema at that moment (Schema-on-read).
• Who Should Use: Huge data teams storing IoT sensor data, with Machine Learning projects rather than business intelligence.

Modern Data Lakehouse

It is the most modern architecture of today. It combines the ACID features of Data Warehouse and the flexibility of Data Lake under one roof (Ex: Microsoft Fabric, Databricks).

• How It Works: Data is kept in open format (Parquet/Delta) but is queried like a SQL table. BI and AI teams use data instantly from a single copy.
• Who Should Use: Modern organizations that want to combine Big Data teams and BI teams on a single platform and aim to get rid of the costs of copying data.