Data Warehouse in System Design: Columnar Storage, Star Schema, ETL & MPP (Visualized)

A data warehouse is a centralized repository that stores large volumes of structured, historical data and is optimized for read-heavy analytical queries rather than for the high-frequency transactional writes of an operational system. Where a production database answers questions like "what did this user order five minutes ago?", a warehouse answers questions like "what were our top-selling product categories by region over the last three years?" — scanning billions of rows to return a single aggregated result.

Warehouses sit downstream of production systems. Raw transactional data is extracted from databases, logs, and third-party APIs; transformed into a clean, consistent shape; and loaded into the warehouse. Business analysts and data scientists then run SQL queries, build dashboards, or train machine learning models on top of it. Because analytical queries touch wide swaths of data rather than individual rows, warehouses are architected very differently from ordinary relational databases.

The distinction between OLTP (Online Transaction Processing) and OLAP (Online Analytical Processing) is the most important concept in data warehouse design. OLTP systems — PostgreSQL, MySQL, MongoDB — are optimized for thousands of concurrent small reads and writes that touch a few rows at a time. OLAP systems are designed for rare but enormous queries that scan millions or billions of rows and return aggregated statistics.

Row-oriented databases (PostgreSQL, MySQL) store each row contiguously on disk: all columns for row 1, then all columns for row 2, and so on. This is great for INSERT and full-row UPDATE but terrible for analytical queries that only need two or three columns out of fifty. An analytical query like SELECT SUM(revenue) FROM orders WHERE year = 2023 must still read every column of every row into memory — most of it wasted.

Columnar storage flips this: all values for column 1 are stored together, then all values for column 2, and so on. The query above now only reads the revenue and year columns off disk, skipping the other 48. That alone can reduce I/O by 90%+. Columnar layout also enables excellent compression — because a column holds one data type and values are often similar (e.g., a country column with 100 distinct values across 10 billion rows), run-length encoding or dictionary compression achieves ratios of 10:1 or better.

A single machine cannot scan petabytes of data in seconds. Modern warehouses use Massively Parallel Processing (MPP): the query is split into fragments, each fragment runs on a separate node working on its slice of the data, and results are merged. Snowflake calls each compute cluster a virtual warehouse; BigQuery uses a serverless MPP engine called Dremel; Redshift distributes data across slices within nodes. The key insight is that storage and compute are often separated — you can spin up a large compute cluster for a heavy workload and scale it back down, paying only for what you use.

ETL (Extract, Transform, Load) is the traditional pipeline: data is pulled from source systems, cleaned and reshaped in a staging environment, and then loaded into the warehouse in its final form. This keeps raw messy data out of the warehouse but requires a powerful external processing layer.

ELT (Extract, Load, Transform) has become dominant with cloud warehouses: raw data is loaded first, then transformed inside the warehouse using SQL and tools like dbt. Because modern warehouses have near-unlimited compute, running heavy transformations inside them is often cheaper and faster than maintaining a separate processing cluster. The raw source data is preserved for reprocessing.

Warehouses use denormalized schemas optimized for fast reads rather than the normalized schemas used in OLTP systems. The two dominant patterns are the star schema and the snowflake schema.

In a star schema, a central fact table holds measurable events or metrics (e.g., each row is one order with columns for order_id, amount, quantity, and foreign keys). Surrounding dimension tables hold descriptive context: dim_customer, dim_product, dim_date, dim_store. The fact table has one foreign key per dimension, and each dimension is fully denormalized into a single flat table. The result looks like a star — one center, many arms — and joins are simple: fact joins directly to each dimension.

A snowflake schema normalizes dimension tables further. Instead of one flat dim_product, you might have dim_product referencing dim_category and dim_supplier. This reduces storage redundancy but requires more joins, which can slow analytical queries. Most teams prefer star schemas for their simplicity and query speed.

As the volume and variety of data grew, two alternative paradigms emerged alongside the classical warehouse. A data lake (e.g., AWS S3 + Athena, HDFS + Hive) stores raw data in any format — CSV, JSON, Parquet, images, video — at very low cost, with schema applied only at query time (schema-on-read). This is flexible but can become a disorganized "data swamp" without governance. A lakehouse (Databricks Delta Lake, Apache Iceberg, Apache Hudi) blends both: data sits on cheap object storage in open table formats that support ACID transactions, schema enforcement, and fast SQL — giving warehouse-quality queries at data-lake costs.

Snowflake pioneered the separation of storage and compute in cloud warehouses. Data lives in S3 in Snowflake's micro-partition format; compute runs in independently-scalable virtual warehouses (clusters of VMs). You can run multiple workloads — BI dashboards and ETL pipelines — simultaneously on the same data without resource contention, and scale each cluster up or down in seconds.

Google BigQuery uses a serverless model: there are no clusters to manage. Queries are split across thousands of workers by the Dremel engine, billed by bytes scanned. BigQuery's slot model and automatic scaling make it attractive for intermittent workloads. Storage uses Capacitor, Google's proprietary columnar format, with automatic partitioning and clustering to minimize scans.

Amazon Redshift distributes data across compute nodes using a distribution key you choose. Choosing the right DISTKEY and SORTKEY is crucial for performance: if the distribution key matches your most common join or filter column, data moves minimally between nodes. Redshift Serverless removes manual cluster sizing. All three platforms support external tables, semi-structured JSON via SUPER/VARIANT types, and native integration with their cloud ecosystems.

The query below joins a fact table to three dimension tables and aggregates revenue by quarter and product category — a typical warehouse query pattern. Note how all filtering happens on dimension columns, and the engine uses columnar storage to scan only the required columns in each table.

A regular OLTP database (PostgreSQL, MySQL) is optimized for high-frequency, low-latency transactional operations — inserts, updates, and point reads. It uses row-oriented storage, normalized schemas, and row-level locking. A data warehouse is optimized for analytical queries over historical data: it uses columnar storage, denormalized star schemas, and massively parallel query execution. They answer different questions: the OLTP database tells you the current state of one record; the warehouse tells you aggregated trends across billions of records over years.

Use ETL when data must be cleaned or masked before it enters your environment — for regulatory reasons, or when raw data is too large/messy to load directly. Use ELT when you have a powerful cloud warehouse (Snowflake, BigQuery, Redshift) and want to preserve raw data for iterative transformation using tools like dbt. ELT has become the modern default because cloud warehouses have the compute to transform data internally at scale, and keeping raw data lets you re-derive derived tables as business logic changes.

An analytical query typically selects 2–5 columns out of 50+ and aggregates them over millions of rows. With row-oriented storage, reading those 2 columns still requires loading all 50 columns of every row into memory — 96% of the I/O is wasted. Columnar storage stores each column separately, so only the needed columns are read from disk. Combined with compression (dictionary encoding, run-length encoding), columnar layouts reduce I/O by 80–95% for typical analytical queries, which translates directly into faster query times and lower compute costs.