Data warehouse: definition, architecture and use cases

A data warehouse is fundamentally different from a transactional database (OLTP). Whereas a transactional database is optimised for fast read/write operations on individual records, a data warehouse is optimised for aggregations and joins over billions of rows via columnar storage.

The classic architecture follows the star or snowflake model: a central fact table (analytics events, transactions) surrounded by dimension tables (users, products, dates, channels).

Leading cloud data warehouses include:

• Google BigQuery: per-query billing, ideal for the Google ecosystem
• Snowflake: multi-cloud flexibility, data sharing
• Amazon Redshift: optimal for the AWS ecosystem
• ClickHouse: open source, very performant for real-time analytics

Data transformations in the warehouse are usually orchestrated with dbt (data build tool), which lets you write versioned, tested and documented SQL transformations.

The data warehouse is the cornerstone of a mature data-driven organisation. It breaks down silos between teams, creates a single source of truth and answers complex analytical questions spanning multiple systems.

Without a data warehouse, every team works with their own partial data, leading to inconsistencies and poorly informed decisions.

• Cohort and LTV analyses per acquisition channel
• Multi-touch funnels impossible to build with siloed data
• Cross-product retention and churn in a single query

1. 1Define a clear data model (star modelling) before importing raw data.
2. 2Use dbt to structure, test and document your SQL transformations.
3. 3Implement a data catalogue (DataHub, Atlan) so teams can discover available tables.
4. 4Configure data quality alerts (Great Expectations, Monte Carlo) to detect anomalies automatically.
5. 5Manage role-based access to secure sensitive data.