Analytical Database Concepts

Each column's values sit contiguously in their own chunks, and the file metadata records where every chunk starts. The scan reads only the byte ranges of the columns the query names; the other columns are never opened or decompressed.

An encoding rewrites one column's values in a type-aware way — dictionary codes, run lengths, deltas — and can often be queried directly. A codec such as ZSTD then compresses those encoded bytes opaquely, and the block must be decompressed before anything can read it.

Columnar engines write immutable files, and the smallest unit a write can produce is a whole file. One row per insert means millions of tiny files, terrible compression, metadata bigger than data, and a background merge queue that can never catch up.

A zone map is small per-block metadata holding each column's minimum and maximum value in that block. Before reading a block, the engine tests the query's filter against those bounds and skips every block that cannot possibly contain a matching row.

Sorting puts equal values next to each other, so run-length encoding sees long runs and delta encoding sees small differences instead of noise. The same data can compress several times better sorted, and correlated columns downstream of the sort key gain too.

Table data lives as files in a shared object store that every node can read over the network, and compute clusters are stateless workers that read it. Either side can be resized, paused or duplicated without touching the other.

A data warehouse stores data in an engine-managed, usually proprietary format with an enforced schema and integrated compute. A data lake stores raw files in object storage that many engines can read, with structure applied only at query time.

A cluster has a fixed budget of memory, CPU and I/O. Past a point, extra concurrency splits that budget so thinly that queries spill to disk and contend for the same resources, so queueing the excess finishes more work per hour.

An exchange moves rows between compute nodes over the network. The planner inserts one wherever an operator needs matching key values on the same node and the current data placement does not already guarantee that — joins, grouping, global sorts, and the final gather.

Resuming provisions fresh worker VMs whose memory and local SSD caches are empty, so the first query reads everything remotely from object storage. Subsequent runs hit warm local caches and skip most of that network traffic.

APPROX_COUNT_DISTINCT returns an estimated number of distinct values computed from a small fixed-size sketch, rather than the exact number computed by tracking every value seen. It uses far less memory and time, at a few percent typical error.

Exact distinct counting must bring every copy of a value onto one node and hold a hash table sized by cardinality, so it costs a shuffle plus memory that spills. HyperLogLog builds a fixed-size sketch per node and merges sketches with a per-register maximum.

SUM, COUNT, MIN and MAX re-aggregate correctly because combining group results is the same operation again. AVG is a ratio, so store SUM and COUNT and divide at read time. Distinct counts and medians cannot be recombined at all from stored group results.

A rollup's win is base rows divided by the number of distinct grouping-key combinations that actually occur in the data. Coarse grain and low-cardinality keys win most; adding a near-unique key such as session_id saves almost nothing.

Broadcasting sends one full copy of the small dimension to every node so each node joins its local fact rows without moving them. The fact table is orders of magnitude larger, so shipping the dimension costs far less network traffic than redistributing both sides.