Cloud Parts

Data warehouses

A columnar database for big analytical questions over all your data.

9 technologies on this page ↓
Live model
What you’re watchingLoad jobs pull data from three sources into the warehouse. An analyst’s query takes longer than an app lookup, but it can cover everything at once.

How it works

A data warehouse gathers data from many systems and stores it column by column, so a query that averages one field over billions of rows reads only that column. Compute scales separately from storage, and a single query can be spread across hundreds or thousands of CPU cores. A lakehouse applies the same idea to open table formats such as Apache Iceberg sitting in object storage.

Think of it as

A national library’s research archive. You go there to answer “how has this changed over ten years?”, and nobody expects the answer in a millisecond.

Reach for it when

  • BI dashboards and reporting across many sources
  • Ad-hoc analysis of years of history
  • Building ML training sets

Think twice when

  • Serving an app’s per-user reads and writes
  • Real-time lookups that need millisecond latency

A real example

Hourly jobs load orders, payment events and CSV exports into BigQuery. An analyst joins three years of data to see which regions churned after a price change; the query scans 2 TB, and on demand BigQuery bills for those bytes.

Technologies

Specific products, and what each does differently.

ClickHouseOpen sourceAn open-source column-oriented database for real-time analytics, fast enough to serve dashboards over billions of rows.DatabendOpen sourceA cloud data warehouse written in Rust that keeps every table as Parquet blocks and Git-like snapshots in object storage, so stateless compute clusters can start, scale and stop without moving data. Open core: Apache 2.0, with paid features under the Elastic License 2.0.Google BigQueryGoogle CloudGoogle Cloud’s serverless data warehouse: no clusters, and you pay per TiB scanned or per slot-hour.MotherDuckCommercialA serverless cloud data warehouse built on DuckDB, where one query can run partly on your laptop and partly in the cloud.SnowflakeCommercialA fully managed cloud data warehouse that separates storage from compute, with virtual warehouses billed per second.Apache HiveOpen sourceThe first SQL engine for Hadoop, and the source of the Hive Metastore, the table catalog that Trino, Spark, Impala and Flink still read.Mainly Query enginesAlibaba Cloud HologresCommercialAlibaba Cloud’s PostgreSQL-compatible real-time data warehouse, which takes streaming writes, answers analytical queries and serves key lookups from one copy of the data.Mainly Real-time analytics databasesFireboltCommercialA cloud data warehouse built for low-latency, high-concurrency queries behind apps and dashboards, also free to self-host as Firebolt Core.Mainly Real-time analytics databasesDatabricksCommercialA managed lakehouse platform built on Apache Spark and Delta Lake, with Unity Catalog for governance, on AWS, Azure or Google Cloud.Mainly Lakehouses

What each provider calls it

AWS
Redshift, Athena + S3 Tables
Google Cloud
BigQuery
Azure
Microsoft Fabric, Synapse
Open source & others
Iceberg + Trino, Apache Hive (and its Metastore, still the catalog many engines use), ClickHouse, DuckDB; also Snowflake, Databricks, MotherDuck (hosted DuckDB), Firebolt, Alibaba Cloud Hologres

In code · sql

SELECT region,
       date_trunc('month', churned_at) AS month,
       count(*) AS churned
FROM analytics.customers
WHERE churned_at >= '2023-01-01'
GROUP BY region, month
ORDER BY month, churned DESC;

Worth exploring

Article· August 2021What Is a Data Lakehouse and Answers to Other Frequently Asked QuestionsMichael Armbrust, Bharath Gowda, Reynold Xin, Matei Zaharia and Ali Ghodsi, DatabricksDatabricks answers ten questions: how a lakehouse differs from a data lake and a warehouse, how it compares on cost and speed, what governance it supports, and how it relates to a data mesh. Databricks sells a lakehouse, so read it as the case for one.
All resources →

Benchmarks

Benchmark · ClickHouse Inc.ClickBenchHow fast analytical databases run 43 SQL queries over one 100-million-row table of web analytics events, on the same cloud machines.32 systems in this catalog · 7 caveats to read first
How to read a benchmark →

Often confused with

LakehousesData lakesQuery enginesRelational databasesEvent streamsETL & ELT pipelinesReal-time analytics databasesData lake, lakehouse or warehouse?
Where it came from1983–2021

In the 1970s and early 1980s, companies ran their business on operational systems: order entry, billing, inventory. To ask a question across them, such as sales by region over five years, large companies ran several decision-support environments side by side, each repeating the work of gathering, cleaning and integrating the same data from those systems. Long analytical queries run directly on transaction databases also caused lock contention. Teradata’s DBC/1012 (1983) was one of the first machines built specifically for decision support.

In 1988, Barry Devlin and Paul Murphy of IBM described a “business data warehouse”: one integrated copy of the company’s data, loaded from the operational systems and kept for analysis. Bill Inmon’s 1992 book Building the Data Warehouse and Ralph Kimball’s 1996 The Data Warehouse Toolkit, with its star schemas of facts and dimensions, set the two main schools of design. Storage changed too: column stores such as Sybase IQ and the 2005 C-Store research system (which became Vertica) showed that reading only the needed columns made warehouse queries far faster.

The cloud removed the hardware. Google’s BigQuery (2010), Amazon Redshift (2012) and Snowflake (2015) sold warehouses as services, Redshift at under $1,000 per terabyte per year against $19,000 to $25,000 for a traditional one. Around 2020 the lakehouse idea brought the same features to open files in object storage.

Why it’s built this way

A separate copy of data for analysis
Every decision-support group was rebuilding the same extracts, and long analytical queries held locks that slowed transaction processing. One integrated, read-mostly copy served everyone without touching production.
Store data by column, not by row
Row stores are optimised for writing whole records, which suits transactions. Warehouse queries aggregate a few columns over many rows, so storing each column together means reading far less data.
Separate storage from compute
Appliances and shared-nothing clusters forced companies to buy for peak load and reshuffle data when resizing. Cloud warehouses keep data in shared storage and add compute on demand.

Timeline

  1. 1983

    Teradata DBC/1012, a database computer built for decision support

  2. 1988

    Devlin and Murphy at IBM describe the “business data warehouse”

  3. 1992

    Bill Inmon publishes Building the Data Warehouse

  4. 1996

    Ralph Kimball’s The Data Warehouse Toolkit popularises star schemas

  5. 2005

    C-Store paper makes the case for column stores

  6. 2012

    Amazon Redshift launches, after BigQuery (2010); Snowflake follows

  7. 2021

    Lakehouse paper argues for warehouse features on open files

Sources17

Improve this page on GitHub