Data warehouses and columnar storage

Your application database and your warehouse both speak SQL, but they are built for opposite workloads. That difference explains most warehouse performance advice, and the bill.

OLTP and OLAP

OLTP (online transaction processing) systems serve the application. OLAP (online analytical processing) systems serve analysis, which scans a lot of data but reads only a few columns of it.

Row and columnar storage

A row store keeps each row’s values together on disk. That is ideal for “load order 42”: one read returns the whole row.

A columnar store keeps each column’s values together. To compute SUM(amount) over a billion rows, it reads only the amount column and skips the other forty. Less data read from disk or object storage means faster queries, and on many warehouses a lower bill.

The cost is on writes and single-row lookups: inserting or reading one row touches every column file. That is why warehouses prefer bulk loads over row-by-row inserts.

Compression

Values in one column share a type and often repeat, so they compress far better than mixed rows:

• dictionary encoding replaces repeated strings such as country codes with small integers;
• run-length encoding stores “'GB' repeated 5,000 times” instead of 5,000 copies, which works best when data is sorted;
• numeric encodings store small differences between neighbouring values.

Smaller data means less to read, so compression speeds up queries as well as saving storage.

Partitioning and clustering

Reading only the needed columns is half the story; reading only the needed rows is the other half.

• Partitioning splits a table into separate chunks, usually by date. A query filtered on the partition column skips every other partition entirely. This is called partition pruning.
• Clustering sorts data within storage by one or more columns, such as customer_id. The engine keeps minimum and maximum values per block and skips blocks that cannot match.

-- BigQuery syntax
CREATE TABLE analytics.events (
  event_ts    TIMESTAMP,
  customer_id STRING,
  event_type  STRING
)
PARTITION BY DATE(event_ts)
CLUSTER BY customer_id
OPTIONS (require_partition_filter = TRUE);

Partition by the column most queries filter on, usually the event date. Avoid partitions so small that you end up with thousands of tiny files.

File formats and the lakehouse

Parquet is an open columnar file format. A file is divided into row groups, and each column chunk inside stores its own encoding, compression and min/max statistics. Query engines such as Spark, Trino and DuckDB, and most warehouses, can read it directly from object storage.

Plain files in a bucket lack transactions, schema changes and safe concurrent writes. Open table formats such as Apache Iceberg, Delta Lake and Apache Hudi add a metadata layer over Parquet files that provides them. Using these on cheap object storage, shared by several engines, is what people mean by a lakehouse.

Controlling cost

Warehouse pricing is usually based on bytes scanned (BigQuery on-demand) or compute time (Snowflake warehouses). Either way, scanning less data costs less.

• Avoid SELECT *. In a columnar store every extra column is extra data read.
• Filter on the partition column, and make that filter mandatory on large tables where your warehouse supports it.
• Don’t rely on LIMIT to save money: in BigQuery, a LIMIT on a full scan does not reduce the bytes billed.
• Build summary tables for dashboards instead of re-aggregating raw events on every load.

How to decide

• Serving an application with small reads and writes: OLTP database.
• Aggregating large history for reporting: columnar warehouse.
• Sharing large datasets across several engines, or storing more than you query: Parquet with an open table format.
• For any large table, choose partition and clustering columns from the filters people actually use.