Data warehouses and lakehouses

The database behind your app answers "show order 81" in a millisecond. "Revenue by country for the last 90 days" scans hundreds of millions of rows, and a different kind of store does it in seconds: a data warehouse. This guide explains why analytical stores are built the way they are, how BigQuery, Snowflake, Redshift, Databricks and the others differ, and which to choose, with a chooser and code you can run.

1. OLTP and OLAP

Data reaches the warehouse by batch loads, change data capture or streams (T08), usually transformed with SQL (ELT, often with dbt). A lakehouse keeps the data as open files (Parquet) in object storage (T06), with a table format such as Apache Iceberg or Delta Lake adding transactions, schema changes and time travel, so several engines can query the same tables.

2. How a warehouse runs a query

3. The products

4. Differences at a glance

5. When to choose which, and why

1. Never run heavy analytics on the production database. Copy the data out (CDC or batch) into an analytical store.
2. Internal BI and reporting on one cloud? That cloud's warehouse: BigQuery, Redshift or Fabric. Snowflake when you're multi-cloud or want strict workload isolation.
3. Data engineering and ML on the same data? A lakehouse: Databricks, or Iceberg tables shared by several engines.
4. Dashboards inside your product, on data seconds old, with many users? ClickHouse, Druid or Pinot.
5. Small data, one analyst? DuckDB on Parquet files may be all you need.

Try it: choose for your requirements

6. Why column stores scan less, in code

A 3-million-row orders table with 20 columns, and the query "total amount for one day". A row store reads every column of every row: 480 MB. A column store reads only the two columns the query uses: 48 MB. Partitioning by day means only one day's files are opened: 1.6 MB, 300 times less. And a sorted column compresses to almost nothing with run-length encoding. The program was run in all three languages; each prints the output below it.

DAYS, ROWS_PER_DAY, COLUMNS, BYTES = 30, 100_000, 20, 8   # an orders table: 3 million rows, 20 columns of 8 bytes

rows = DAYS * ROWS_PER_DAY
mb = lambda b: f"{b / 1_000_000:,.1f} MB"

# Query: SELECT SUM(amount) FROM orders WHERE day = '2026-11-03'
row_store = rows * COLUMNS * BYTES                  # whole rows are stored together, so every column is read
column_store = rows * 2 * BYTES                     # only the day and amount columns are read
partitioned = ROWS_PER_DAY * 2 * BYTES              # partitioned by day: only one day's files are opened
print("row store, full scan:     ", mb(row_store))
print("column store, 2 columns:  ", mb(column_store))
print("plus partition pruning:   ", mb(partitioned))
print(f"reduction: {row_store // partitioned}x less data read")

# Sorted columns compress well: run-length encoding stores each value once with a count.
day_column = [d for d in range(DAYS) for _ in range(ROWS_PER_DAY)]
runs = []
for d in day_column:
    if runs and runs[-1][0] == d:
        runs[-1][1] += 1
    else:
        runs.append([d, 1])
print(f"day column: {len(day_column):,} values -> {len(runs)} runs of (value, count)")

const DAYS = 30, ROWS_PER_DAY = 100_000, COLUMNS = 20, BYTES = 8; // an orders table: 3 million rows, 20 columns of 8 bytes

const rows = DAYS * ROWS_PER_DAY;
const mb = (b: number) => (b / 1_000_000).toLocaleString("en-US", { minimumFractionDigits: 1, maximumFractionDigits: 1 }) + " MB";

// Query: SELECT SUM(amount) FROM orders WHERE day = '2026-11-03'
const rowStore = rows * COLUMNS * BYTES; // whole rows are stored together, so every column is read
const columnStore = rows * 2 * BYTES; // only the day and amount columns are read
const partitioned = ROWS_PER_DAY * 2 * BYTES; // partitioned by day: only one day's files are opened
console.log("row store, full scan:     ", mb(rowStore));
console.log("column store, 2 columns:  ", mb(columnStore));
console.log("plus partition pruning:   ", mb(partitioned));
console.log(`reduction: ${Math.floor(rowStore / partitioned)}x less data read`);

// Sorted columns compress well: run-length encoding stores each value once with a count.
const dayColumn: number[] = [];
for (let d = 0; d < DAYS; d++) for (let i = 0; i < ROWS_PER_DAY; i++) dayColumn.push(d);
const runs: [number, number][] = [];
for (const d of dayColumn) {
  if (runs.length && runs[runs.length - 1][0] === d) runs[runs.length - 1][1]++;
  else runs.push([d, 1]);
}
console.log(`day column: ${dayColumn.length.toLocaleString("en-US")} values -> ${runs.length} runs of (value, count)`);

import java.util.*;

public class Main {
    static final int DAYS = 30, ROWS_PER_DAY = 100_000, COLUMNS = 20, BYTES = 8; // an orders table: 3 million rows, 20 columns of 8 bytes

    static String mb(long b) { return String.format(Locale.US, "%,.1f MB", b / 1_000_000.0); }

    public static void main(String[] args) {
        long rows = (long) DAYS * ROWS_PER_DAY;
        // Query: SELECT SUM(amount) FROM orders WHERE day = '2026-11-03'
        long rowStore = rows * COLUMNS * BYTES;            // whole rows are stored together, so every column is read
        long columnStore = rows * 2 * BYTES;               // only the day and amount columns are read
        long partitioned = (long) ROWS_PER_DAY * 2 * BYTES; // partitioned by day: only one day's files are opened
        System.out.println("row store, full scan:      " + mb(rowStore));
        System.out.println("column store, 2 columns:   " + mb(columnStore));
        System.out.println("plus partition pruning:    " + mb(partitioned));
        System.out.println("reduction: " + rowStore / partitioned + "x less data read");

        // Sorted columns compress well: run-length encoding stores each value once with a count.
        int[] dayColumn = new int[DAYS * ROWS_PER_DAY];
        for (int d = 0, i = 0; d < DAYS; d++) for (int r = 0; r < ROWS_PER_DAY; r++) dayColumn[i++] = d;
        List<int[]> runs = new ArrayList<>();
        for (int d : dayColumn) {
            if (!runs.isEmpty() && runs.get(runs.size() - 1)[0] == d) runs.get(runs.size() - 1)[1]++;
            else runs.add(new int[] {d, 1});
        }
        System.out.printf(Locale.US, "day column: %,d values -> %d runs of (value, count)%n", dayColumn.length, runs.size());
    }
}

row store, full scan:      480.0 MB
column store, 2 columns:   48.0 MB
plus partition pruning:    1.6 MB
reduction: 300x less data read
day column: 3,000,000 values -> 30 runs of (value, count)

7. Say it in the interview

8. Common mistakes

9. Check yourself

10. Sources and further reading

Reviewed on 4 November 2026. Pricing models and features change; check before relying on them.