Data warehouse

Overview

A data warehouse is purpose-built for analytics: it stores structured, typically cleaned and modeled data, and is optimized for read-heavy queries that scan and aggregate large volumes of rows — the OLAP (Online Analytical Processing) workload. This contrasts with OLTP (Online Transactional Processing) databases like Postgres or MySQL, which are optimized for many small, concurrent reads and writes of individual records.

Structure and storage

Most analytical warehouses use columnar storage, which stores each column's values contiguously on disk. This makes it fast to scan only the columns a query needs and compresses well, since values within a column tend to be similar. Data is loaded into a warehouse through ETL/ELT pipelines, and a schema is enforced up front ("schema-on-write"), which is one of the key differences from a schema-on-read data lake.

Cloud data warehouses

Modern cloud warehouses such as Snowflake, Google BigQuery, and Amazon Redshift separate storage from compute, letting teams scale query capacity independently of stored data volume, and charge based on usage rather than fixed hardware.

DuckDB's place in the picture

DuckDB is an embedded, in-process analytical database — it runs the same columnar, vectorized query engine as a cloud warehouse, but as a library inside your process rather than a separate server. It's well suited to warehouse-style analytics on datasets that fit on a single machine, with no infrastructure to manage:

Copy code
CREATE TABLE sales AS
SELECT * FROM read_parquet('sales_2026.parquet');

SELECT region, SUM(revenue) AS total_revenue
FROM sales
GROUP BY ALL
ORDER BY ALL DESC;

MotherDuck extends this to a serverless cloud data warehouse built on DuckDB, letting the same SQL scale from a laptop to shared, cloud-hosted databases without switching engines.