Engineering Atlas › System Design Fundamentals

Analytics storage in a data warehouse

11 min read · last reviewed 2026-10-17

Open in the interactive app: practice, quiz, mock interview →

In one minute

How it works

Row store versus column store

An OLTP database stores each row contiguously, which is ideal for "fetch order 123". An analytical query like "revenue by month for 2025" touches three columns across 2 billion rows. A column store keeps each column in its own compressed blocks, so it reads only order_date, amount, and maybe region. Values in one column are similar, so dictionary, run-length, and delta encoding often achieve 5 to 20 times compression, and the engine can run vectorised operations over thousands of values per CPU instruction batch.

Data skipping

Warehouses divide tables into blocks and record min and max per column per block. Redshift uses 1 MB column blocks; Snowflake uses micro-partitions holding 50 to 500 MB of uncompressed data (Snowflake docs). A filter order_date >= '2025-01-01' skips every block whose max is earlier. Sorting or clustering a table on commonly filtered columns makes these ranges tight. Redshift's own example: five years of data sorted by date, queried for one month, can skip up to 98 percent of blocks (Redshift docs).

MPP execution

A query plan is split across many workers. Each scans its slice, filters, and computes partial aggregates; partial results are then shuffled by group key and combined. Joins are either broadcast (copy a small dimension to every worker) or shuffle (repartition both sides on the join key). The optimizer picks between them using table statistics, which is why stale statistics can turn a 5-second query into a 5-minute one.

Dimensional modelling

A fact table records measurable events at a declared grain, such as one row per order line. Kimball calls declaring the grain the pivotal step, done before picking any dimension or measure, and recommends starting from the most atomic grain (Kimball Group). Each fact row carries foreign keys to dimensions and numeric measures. Dimension tables describe the who, what, where, and when: customer, product, store, date. Changes to dimension attributes are handled with slowly changing dimension patterns, which Kimball numbers from type 0 to type 7. The two you meet most: type 1 overwrites the attribute, rewriting history, and type 2 adds a new row with validity dates so historic facts keep their original context.

Down the stack

Layer 1The contract

A warehouse promises SQL semantics and that each statement sees one consistent version of every table, even while loads run. That is per statement, not per transaction: Snowflake supports only READ COMMITTED for tables, so two statements in one transaction can see different data if a load commits in between (Snowflake docs). Committed loads are durable. Performance is not part of the contract, but the practical envelope is seconds for scans of tens to hundreds of GB and sub-second for cached or pre-aggregated results. Most cloud warehouses do not enforce primary or foreign keys. Snowflake enforces only NOT NULL and CHECK on standard tables (Snowflake docs), and BigQuery enforces neither key (BigQuery docs). Worse, Redshift's planner trusts declared keys anyway, so a non-unique "primary key" can make SELECT DISTINCT return duplicates (Redshift docs). Uniqueness is the modeller's job, and a key you declare must be one your pipeline actually guarantees.

Layer 2The mechanism

In Snowflake, a table is a set of immutable micro-partitions of 50 to 500 MB uncompressed, each storing columns separately with a header of per-column min, max, distinct count, and null count. The metadata service keeps these headers, so the optimizer prunes partitions before any worker touches storage. A DML statement writes new micro-partitions and atomically swaps the table's partition list, which is how snapshots and time travel work. Old versions stay queryable for 1 day by default, up to 90 days on Enterprise edition (Snowflake docs). Redshift stores each column in 1 MB blocks, against the 2 to 32 KB typical of row stores, with zone maps (min and max per block) (Redshift docs) and distributes rows across slices by a distribution key, so a join on that key needs no network shuffle. Execution is vectorised: operators process batches of around 1,000 to 4,000 values from one column at a time, and many engines compile query fragments to machine code. The idea comes from MonetDB/X100, which used 1,024-value vectors by default and showed tuple-at-a-time interpretation paying heavy overhead per row (Boncz et al., CIDR 2005).

Layer 3The machine

Vectorisation exists because of the CPU. A tight loop over a contiguous array of 32-bit integers fits in L1 cache (32 to 48 KB per core), lets the compiler use SIMD instructions that compare 8 or 16 values per instruction with AVX2 or AVX-512, and avoids branch mispredictions that cost about 15 cycles each. Row-at-a-time interpreters spend most of their time on virtual function calls and cache misses, typically 10 to 50 times slower. Compression helps twice: fewer bytes read from SSD or network, and dictionary codes small enough that filters run on integers instead of strings. Local NVMe caches on compute nodes read at several GB/s, while remote object storage supplies about 100 MB/s per connection, so warehouses aggressively cache hot micro-partitions on local disk.

Why it's built this way

  1. Analytical queries read few columns from many rows, so storing by column minimises bytes read.
  2. Reading bytes is still the dominant cost, so heavy compression and min/max metadata skip as much as possible.
  3. CPUs are fast only on contiguous, predictable data, so execution processes column batches instead of rows.
  4. Storage and compute needs grow at different rates, so cloud warehouses keep data in object storage and cache it near elastic compute.
  5. Immutable partitions make concurrency and caching simple, so updates write new partitions rather than modify old ones.

Diagram

flowchart TB
  Q["SQL query"] --> L["Leader: parse, optimize, plan"]
  L --> W1["Worker 1: scan, filter, partial agg"]
  L --> W2["Worker 2: scan, filter, partial agg"]
  L --> W3["Worker 3: scan, filter, partial agg"]
  W1 -->|"shuffle by group key"| F["Final aggregate"]
  W2 -->|"shuffle by group key"| F
  W3 -->|"shuffle by group key"| F
  S["Columnar storage with block min/max"] --> W1
  S --> W2
  S --> W3
  F --> R["Result"]

Worked example

A retailer has fact_sales with 3 billion rows at the grain of one line item, 25 columns, 2.4 TB uncompressed and 300 GB compressed. Dimensions: dim_product (200,000 rows), dim_store (1,500 rows), dim_date (10 years of days).

Query: weekly revenue by product category for the last 13 weeks.

SELECT d.week_start, p.category, SUM(f.net_amount) AS revenue
FROM fact_sales f
JOIN dim_date d    ON f.date_key = d.date_key
JOIN dim_product p ON f.product_key = p.product_key
WHERE d.date >= CURRENT_DATE - 91
GROUP BY 1, 2;

The table is clustered by date_key, so 13 weeks out of 10 years means scanning about 2.5 percent of blocks. Only three columns of 25 are read, about 12 percent of each block. Combined, the scan is roughly 300 GB times 0.025 times 0.12, about 900 MB. Both dimensions are small enough to broadcast, so no shuffle of the fact table is needed. On a medium cluster this returns in 1 to 3 seconds. Without clustering, it scans all 36 GB of those columns and takes closer to 30 seconds and 40 times the compute.

In the wild

Trade-offs

OptionStrengthsWeaknessesChoose when
Star schema (Kimball)Simple joins, fast, business-friendlyDuplication in dimensions; needs conformed keysBI and self-service analytics
Normalised enterprise model (Inmon)Single integrated source, less redundancyMany joins; slower to deliver valueLarge enterprises with many source systems
One big wide tableNo joins, fastest for one use caseRigid; hard to update dimension attributesSpecific dashboards with fixed questions
Cloud warehouseElastic, managed, separate storage and computeCost surprises from unbounded queriesMost teams without a dedicated platform group
Self-hosted columnar (ClickHouse)Very fast, cheap at scaleYou run it; weaker on complex joinsHigh-volume event analytics with in-house ops

Pitfalls in production

Leadership lens

Check yourself

  1. Why does clustering on date_key matter even though the engine already reads only needed columns?

    Show answer

    Column pruning reduces which columns are read, while clustering tightens block min/max ranges so whole blocks can be skipped; the savings multiply.

  2. When would a broadcast join be a bad choice?

    Show answer

    When the "small" side is large, because copying it to every worker exhausts memory and network; a shuffle join on the key scales better.

  3. Why add a new row instead of updating a dimension when a customer moves city?

    Show answer

    Type 2 history keeps past facts attributed to the city the customer lived in at the time, so historical reports do not change retroactively.

Sources and further study

Primary

Systems docs

Books

Other curricula

Watch

Free resources

Open in the interactive app: practice, quiz, mock interview →