skip to content

In a columnar warehouse, when does a 400-column wide table stop being cheap for its readers?

level: principalimportance: nice to knowfreq 38%

answer

  1. unread columns cost nothing to read
  2. someone always writes a star-select
  3. metadata grows with columns times slices
  4. a byte budget split 400 ways leaves short chunks
  5. the write path never gets the discount

basics

~20 s

Width is nearly free only while every consumer projects narrowly. It stops being free once tools issue star-selects, once per-column metadata and short chunks add up, and on the write side, where every column is encoded on every load.

solid answer

~50 s

The argument for a wide table is sound: unread column chunks are never fetched, so a consumer reading five columns pays roughly the same whether the table has fifty or four hundred. The argument holds only under conditions you have to actively maintain. It breaks when consumers do not project — a BI connector pulling every column, an ELT step written as `INSERT ... SELECT *`, an ad-hoc export — because those pay for the full width on every run. It also erodes structurally: metadata grows with columns times row groups, and a fixed file-size budget spread over 400 columns leaves each chunk short, which weakens encoding and turns reads into many small requests. The write side pays unconditionally, since every column is encoded and its statistics computed on every load whether anyone reads it or not. The judgment call is to measure per-column read frequency and bytes, then either govern access through projecting views or split off the genuinely cold columns.

code

sql · 10 lines
sql
-- base table stays wide; consumers get a projecting view
CREATE VIEW events_core AS
SELECT event_id, event_ts, user_id, event_type, country, amount
FROM events_wide;

-- the cold tail lives beside it, joined only when actually needed
CREATE VIEW events_with_payload AS
SELECT c.*, p.raw_payload
FROM events_core c
JOIN events_payload p USING (event_id);

go deeper

for a junior

Know the starting fact: a query that names five columns does not pay for the other 395, because unread chunks are never fetched.

for a middle

Explain what still costs money at width — metadata per column per slice, shorter chunks under a fixed byte budget, and encoding every column on every load regardless of reads.

for a senior

Diagnose the real driver: find the consumers issuing star-selects on a schedule, quantify their bytes, and fix the projection before touching the schema.

for a principal

Own the policy. Measure per-column read frequency, decide between governing access through projecting views and splitting off a cold tail, and state explicitly what join cost the split buys you.

## Why the question is asked "Columnar storage makes wide tables free" is a half-truth that teams act on for years before the bill arrives. A principal-level answer accepts the premise, then states precisely the conditions under which it holds and the ones under which it fails — because the failure modes are the design work. ## Where width genuinely is cheap On the read path, an unreferenced column is not read, not transferred, not decompressed and not decoded. A query naming five columns of a 400-column table pays for five column chunks per slice it visits, plus metadata. There is no per-row penalty for the other 395: a columnar table does not carry a row header whose size grows with width. So for a disciplined consumer, width really is close to free, and that is what makes the one-big-table pattern attractive in the first place — no joins, no schema negotiation, one place to add a field. ## Where it stops being cheap **Consumers that do not project.** This is the dominant term and the one that actually bites. A dashboard tool configured to fetch a table and filter client-side, an ELT hop written as `SELECT *`, a notebook export, a reverse-ETL job — each of these reads the entire width every time it runs, on a schedule. On bytes-scanned pricing this is a direct, recurring cost; on a fixed cluster it is capacity taken from everyone else. Adding one wide column raises the cost of every such consumer instantly and invisibly. **Metadata and per-chunk overhead.** Every slice carries per-column entries: offsets, lengths, counts, statistics. That footprint grows with columns times row groups. On a very wide table with many slices, metadata becomes a real object that must be read and parsed before any data is fetched, and it adds fixed latency to short queries regardless of how narrow the projection is. **Short chunks under a byte budget.** Writers target a file or slice size in bytes. Spread that budget across 400 columns and each column chunk holds relatively few rows' worth of data. Short chunks compress worse — a dictionary or run-length encoding needs volume to pay off — and produce many small reads, where per-request latency and per-request cost dominate throughput. A wide table therefore interacts badly with small-batch ingestion in a way a narrow table tolerates. **The write path pays unconditionally.** Every load encodes every column, computes its statistics and writes its chunk, regardless of whether any query has ever referenced it. Ingest CPU, write amplification on later rewrites, and storage all scale with width. Cold columns are free to *read* and are never free to *write*. **Operational surface.** Four hundred columns is four hundred things to document, type, evolve and reason about in a schema change. That cost is human, not machine, but it is the one that most often triggers the eventual split. ## How to decide, concretely The decision should be evidence-driven, and the evidence is usually available: engines record query history, and often the columns each query referenced. From that, build a per-column read profile — how many jobs touch it, how many bytes they read through it, how often. Two patterns usually emerge: a small hot core touched by nearly everything, and a long tail of columns referenced by one weekly job or by nothing at all. Given that profile, the levers are: - **Govern the projection rather than the schema.** If the only real problem is star-selecting consumers, the fix is to expose the table through views that project the used columns and to make direct access to the base table the exception. This keeps the modelling benefit of one table. - **Split off the cold tail.** Move rarely-read wide columns — raw payloads, debug blobs, verbose text — into a companion table keyed to the main one. Readers of the hot core stop paying the metadata and chunk-sizing tax; the rare consumer of the tail pays a join. Note the trade you just made: joins on a distributed engine can be far more expensive than a wide scan, so this pays only when the tail is genuinely cold. - **Fix ingestion batch size before fixing the schema.** If chunks are short because loads are tiny, the width is not the root cause and splitting the table will not fix it. - **Delete.** Columns nothing has read in a year are cost with no counterparty. ## What a strong answer sounds like It refuses the binary. Width is not good or bad; it is cheap for narrow readers and expensive for wide readers, and the population of readers is a thing you can measure and govern. The failure is almost never "we chose 400 columns"; it is "we chose 400 columns and never controlled how they are read." A candidate who names the star-selecting consumers as the dominant term, mentions metadata and chunk sizing as the structural terms, and points out that the write side pays unconditionally, has covered the ground. One who proposes splitting the table without first measuring per-column reads — or without acknowledging the join cost they just bought — has not.

  • What evidence would you gather before proposing to split the table?
    A per-column read profile: which queries reference which columns, how often, and how many bytes each pattern scans. Most engines expose query history with referenced columns or at least the query text. The goal is to separate a hot core from a cold tail, and to learn whether the real cost is column width or a handful of star-selecting jobs — the remedies differ completely.
  • What do you give up by splitting the cold columns into a companion table?
    Joins. On a distributed engine a join between two large tables can cost far more than scanning extra columns, especially if it forces data movement. You also add a consistency and loading concern: two tables must be written together and stay aligned. The split pays only when the tail is genuinely cold and the join is rare.
  • Why does a very wide table interact badly with small-batch ingestion?
    Because a writer targets a byte budget per file or slice, and a small batch spread across hundreds of columns leaves each chunk holding very little. Encodings starve, compression ratios fall, and reads become many small requests dominated by per-request latency. The width amplifies a problem a narrow table would absorb.
  • Does adding an unread column to a wide table cost anything at all?
    Yes, on three axes. Every load encodes it and computes its statistics; metadata grows by one entry per slice; and the byte budget per slice is shared one more way, shortening every chunk slightly. Reads that never name it pay only the metadata term — but any star-selecting consumer pays for it in full, forever.

saying these in an interview costs you the question

  • Claims wide tables are free because columnar storage skips unread columns
  • Proposes splitting the table without measuring per-column reads
  • Ignores that every load encodes and writes every column
  • Forgets that splitting buys a join whose cost may exceed the saving
  • Treats metadata and chunk sizing as negligible at any width

context

resolving links…