Skip to content
AI Engineer Guide.

concept · data · advanced · concept 10 of 17

Columnar formats

0 of 17 complete

Why it matters

A query that sums one measure across a billion rows touches a tiny fraction of the table's columns. In row storage the engine still reads whole records from disk, so most of the I/O is wasted. Columnar layout keeps each column's values adjacent, which lets the reader skip irrelevant columns, compress runs of similar values and evaluate predicates against per-block statistics before decompressing anything.

Mental model

A row store is a stack of completed forms; a column store is a set of trays, one per question, each holding that answer for every form. The metaphor breaks because forms are rarely independent: a column store still has to reassemble whole rows for a point lookup, and that reassembly is the cost it hides.

How it works

Mechanism, step by stepInteractive
  1. 1Decide the access pattern first: full-record lookups by key belong in a row store, wide scans and aggregations in a columnar one.
  2. 2Choose the container: Parquet for open analytics interchange, ORC where the Hive ecosystem expects it, Arrow for in-memory exchange between processes.
  3. 3Write in large batches so each file holds a useful row group and column chunks are big enough to compress well.
  4. 4Order rows by the columns your filters use most, because sort order determines which row groups a predicate can skip.
  5. 5Compact small files on a schedule, since each file adds metadata and open cost to every query.
  6. 6Verify with the query plan that the engine is actually pruning row groups rather than reading everything.

Step 1 of 6

Decide the access pattern first: full-record lookups by key belong in a row store, wide scans and aggregations in a columnar one.

Step 1/6 · step 1

Figure. The mechanism in this page's own steps. The figure adds sequence, not new claims.

What to notice. A step can be read alone, but the order is the mechanism. The control lets you stop on any one.

Text version

The page's steps: 1. Decide the access pattern first: full-record lookups by key belong in a row store, wide s… 2. Choose the container: Parquet for open analytics interchange, ORC where the Hive ecosyste… 3. Write in large batches so each file holds a useful row group and column chunks are big en… 4. Order rows by the columns your filters use most, because sort order determines which row … 5. Compact small files on a schedule, since each file adds metadata and open cost to every q… 6. Verify with the query plan that the engine is actually pruning row groups rather than rea…

  1. Decide the access pattern first: full-record lookups by key belong in a row store, wide scans and aggregations in a columnar one.
  2. Choose the container: Parquet for open analytics interchange, ORC where the Hive ecosystem expects it, Arrow for in-memory exchange between processes.
  3. Write in large batches so each file holds a useful row group and column chunks are big enough to compress well.
  4. Order rows by the columns your filters use most, because sort order determines which row groups a predicate can skip.
  5. Compact small files on a schedule, since each file adds metadata and open cost to every query.
  6. Verify with the query plan that the engine is actually pruning row groups rather than reading everything.

Trade-offs

ChoiceGood forCostFails when
ParquetOpen interchange across enginesRow-group granularity limits skippingRows arrive in tiny files that never get compacted
ORCHive and Presto style stacksWeaker support outside that ecosystemYou need portable files for external consumers
Wide table, many columnsFlexible ad hoc queriesScan cost grows with column countMost queries touch a handful of fields
Narrow nested modelCheap scans of common fieldsJoins at read timeThe warehouse cannot push down through the nesting
Trade-off mapInteractive

Good for

Open interchange across engines

Cost

Row-group granularity limits skipping

Fails when

Rows arrive in tiny files that never get compacted

  1. Rows arrive in tiny files that never get compacted
  2. You need portable files for external consumers
  3. Most queries touch a handful of fields
  4. The warehouse cannot push down through the nesting

Figure. Each choice with what it buys, what it costs, and the case where it loses.

What to notice. The fourth column is the decision: if the losing case describes your situation, the choice is wrong.

Text version

Four trade-offs: Parquet is good for Open interchange across engines, costs Row-group granularity limits skipping, and fails when Rows arrive in tiny files that never get compacted. ORC is good for Hive and Presto style stacks, costs Weaker support outside that ecosystem, and fails when You need portable files for external consumers. Wide table, many columns is good for Flexible ad hoc queries, costs Scan cost grows with column count, and fails when Most queries touch a handful of fields. Narrow nested model is good for Cheap scans of common fields, costs Joins at read time, and fails when The warehouse cannot push down through the nesting

Failure modes

  • Small file accumulation. Query planning time grows and scans open thousands of files. Debug: count files per partition and check average file size against your target.
  • Schema drift across files. Some files have extra or renamed columns and readers disagree on nulls. Debug: read the footer metadata of a sample and diff column types by partition.
  • Compression defeating pruning. A single dictionary-encoded block spans many distinct keys and statistics cannot exclude it. Debug: compare rows scanned against rows returned in the query plan.

In production

Monitor file count and average file size per partition as first-class metrics. Compact on write or on a schedule, and keep row-group size stable so predicate pruning stays effective.

Common misconceptions

People think: Columnar storage is always faster.

Actually: It wins on wide scans and loses on single-row lookups and frequent updates, where reassembly and rewrite cost dominate.

People think: Compression is just a smaller file.

Actually: Compression also enables pruning and vectorised decoding, which is where most of the query speedup comes from.

Interview angle

Explain the layout, then connect it to a query: which columns are read, which row groups are skipped, why compression helps. Mention the small-file problem and where row storage still wins. A weak answer says columnar is faster without naming the access pattern that makes it true.

Sources