DWH — Data Warehouse

dwh · data warehouse · analytical warehouse · olap warehouse · enterprise data warehouse

Definition

A DWH differs from operational databases (OLTP) in its architecture: it is optimised for analytical queries (OLAP) — aggregating millions of records, complex joins, historical data. Data arrives through ETL or ELT pipelines from operational systems.

DWH architecture in e-commerce

Data sources              ETL / ELT            DWH                Activation
──────────────────        ───────────────      ───────────        ────────────
Website (events)  ──→
CRM               ──→   Transformation  →     BigQuery /          Dashboards
ERP               ──→   Cleansing       →     ClickHouse /        ML models
POS terminals     ──→   Aggregation     →     Snowflake /         Segments
Ad platforms      ──→                         Redshift            Reports

Common DWH solutions for e-commerce

Solution	Strengths	Who it suits
Google BigQuery	Scalability, ML integration	Teams in the Google ecosystem
ClickHouse	Real-time analytics, open source	High-traffic sites
Snowflake	Simplicity, data sharing	Mid-market and enterprise
Azure Synapse	Integration with Power BI and the Microsoft stack	Microsoft infrastructure
Amazon Redshift	AWS ecosystem	AWS infrastructure

How a DWH supports ML personalization

The DWH stores:      ML models learn:          Output:
─────────────        ───────────────────       ──────────────────────
Transactions    →    Churn prediction     →    Churn score per user
Behaviour       →    Affinity model       →    Affinity profile
RFM features    →    Lifetime value       →    LTV forecast
Session data    →    Next best product    →    A personal recommendation

Exporting predictions out of the DWH — into the CDP and on into the personalization platform — is done through the same ETL pipelines.

Frequently asked questions

How does a DWH differ from an ordinary database?

An operational database (OLTP) is optimised for fast transactions — inserting or updating a single record in milliseconds. A DWH (OLAP) is optimised for analytical queries — aggregating millions of rows in seconds. A DWH stores historical data; OLTP stores the current state.

How is a DWH used in e-commerce personalization?

The DWH holds the full history of transactions and behaviour, ML models are trained on that data, and the resulting models — churn, lifetime value, affinity — are exported back into operational systems for real-time personalization. The DWH is also used for offline segmentation and for building audiences to export into ad platforms.

What is ClickHouse and why does e-commerce use it?

ClickHouse is an open-source columnar OLAP database, originally developed at Yandex. It suits real-time analytics on event data: ad impressions, clicks, site events. In e-commerce it is used for real-time A/B test analytics, conversion dashboards and monitoring personalization metrics.

DWH — Data Warehouse

DWH architecture in e-commerce

Common DWH solutions for e-commerce

How a DWH supports ML personalization

Frequently asked questions

Related terms

We received your request