In a columnar table, how does an engine rebuild a full row when each column is stored separately?

## The problem In a row store, a row is a contiguous unit; retrieving it is one read. In a columnar table the values of one row are scattered across as many chunks as the row has columns, potentially spread across a large file. Something has to tell the engine which values belong together. The answer used by essentially every columnar engine is deliberately minimal: **ordinal position within a row group**. ## The invariant Within one row group, every column chunk satisfies three properties: it contains a value for every row of that slice, the values appear in the same row order as every other chunk in the slice, and the count matches. Given that, the *k*-th entry of the `user_id` chunk and the *k*-th entry of the `amount` chunk are, by construction, the same row. Reassembly is a parallel walk over the chunks the query needs, or a gather at specific positions if only some rows survive a filter. This is why nothing resembling a row identifier is stored next to each value. Storing one would cost as much as the data for narrow columns and would be redundant, since position already carries the information. Some engines expose a virtual row-position pseudo-column derived from the ordinal, but it is computed, not stored. ## What follows from the invariant **A column cannot be reordered on its own.** Sorting is a property of the entire slice: to sort by `event_ts`, the writer must permute every column's values identically. That is exactly why sort or cluster keys are declared on the table and applied at write time rather than per column, and why re-sorting existing data means rewriting all of its columns, not just the sorted one. **Nulls must occupy a position.** If a column simply omitted absent values, its chunk would be shorter than its siblings and every position after the first null would be misaligned. Engines therefore keep the absence information separately — most commonly a validity bitmap with one bit per row — while the values array either contains a placeholder or is compacted with the bitmap describing how to expand it. Either way the logical position count is preserved. **Variable-length values need an offsets array.** For strings or binary blobs, the *k*-th value is not at a fixed byte multiple. The chunk carries an array of lengths or offsets alongside the bytes, so that position *k* can be located without scanning from the start. This is why gathering scattered positions from a string column is more expensive than from a fixed-width integer column. **Deletes cannot physically remove a value.** Removing entry *k* from one chunk would shift every subsequent entry and break alignment against every other column; removing it from all chunks means rewriting the entire slice. Engines therefore mark deleted positions — a deletion bitmap or an equivalent marker consulted at read time — and reclaim the space later when the slice is rewritten. (How and when that rewrite happens is the compaction story, owned elsewhere; the reason a marker is needed at all is this alignment invariant.) **Updates are the same problem.** Changing one field of one row cannot be an in-place edit of one chunk in an immutable layout; it becomes a delete marker plus a new row appended in a later slice. ## Reassembly cost Stitching is not free, and its cost has a shape worth knowing: - It is proportional to the number of **columns projected** times the number of **rows surviving**. Adding a column to the SELECT list adds a chunk to gather from for every surviving row. - Sequential reassembly (all rows of a slice, in order) is cheap: it is a linear walk of each chunk with excellent cache behaviour. - Positional gather (only the rows that passed a filter) is more expensive, and its cost depends on the encoding. A fixed-width, uncompressed chunk supports direct indexing; a run-length or delta-encoded chunk may require decoding from the start of a block to reach position *k*, so a scattered gather can end up decoding nearly the whole chunk anyway. That last point is exactly why deferring reassembly is a planning decision rather than an automatic win. ## Across row groups The positional invariant is scoped to a row group. Two chunks from different slices say nothing about each other, and the slices themselves are independent units — which is what allows them to be read in parallel and in any order. A row identity that must survive across slices (for a delete map, or for an upsert key) has to be expressed as something durable: a file or slice identifier plus an ordinal, or an explicit key column. ## Interview framing The crisp answer is one sentence — "the k-th value of every chunk in a row group is the k-th row" — followed by the consequences, because the consequences are what the interviewer is actually testing: why sorting is table-wide, why nulls need a bitmap, why deletes are marked rather than removed, and why gathering a filtered subset can cost more than reading everything.