Columnar File Formats

The previous three lessons discussed the shape of the table: the separation of fact and dimension, what the degree of normalization makes joins pay, a dimension that keeps no history silently making the answer wrong. In all three the table stood as a logical object: a set with rows, columns and a result. One thing was never asked: when this table lands on disk, what sits side by side, the rows or the columns.

What this lesson measures is that decision. The measure does not change: files touched, cells read and cost. The only thing that changes is how many cells the same query has to read from the same file. Two questions are answered in turn. What does selective reading gain and where does that gain end; where does compression carry that limit.

• MF41. The source, the consumption rows coming out of the pipeline and the file layout are fictional and are produced inside the lesson. No warehouse, lake, file format or query engine is called. The seed is 20260218.
• MF42. Columnar file format and row-oriented file format are each a class name. The distinction is in a single behavior: the columnar read takes only the selected columns, the row-oriented read takes the whole record.
• MF43. The record carries seven columns. The cells read are the product of the rows touched and the columns read.
• MF44. Cost is the number of files times the per-file constant of 120, plus the cells read. The constant is a choice; in this lesson both layouts touch the same single file, so the constant stays outside the difference.
• MF45. Compression is modeled in terms of cells read, and no algorithm is written. The compressed cost of a column is the number of runs in that column: identical values that follow one another in file order come down to a single cell. The compression procedure was measured in the Introduction to Linux course in the Linux and System Administration curriculum; it is not repeated.
• MF46. In the row-oriented layout the unit of a run is the whole record. Because the pairs of subscriber and period are unique, two consecutive records are never equal and no shrinking takes place.
• MF47. Ordering within the file is a write decision and can be only one per file.
• MF48. The partition key is fixed in this lesson and is period. The key itself is the subject of the next lesson.

What Selective Reading Makes You Pay

The query wants the consumption of a single period by region; it needs two columns, region and m3. The block below sets up the pipeline, the layout and the cost measure, then sweeps the same query as the number of selected columns rises from one to seven.

# FICTIONAL. The meter readings of the measurement network , the consumption rows and the file
# layout are produced inside the lesson; no data warehouse , data lake , file format or query
# engine is called.
SEED, M32 = 20260218, 0xFFFFFFFF


def rng(t):
    x = ((t ^ (t >> 16)) * 2246822507) & M32
    x = ((x ^ (x >> 13)) * 3266489909) & M32
    s = [(x ^ (x >> 16)) & M32]

    def next_value():
        s[0] = (s[0] * 1664525 + 1013904223) & M32
        return s[0] / 4294967296
    return next_value


# values stay Turkish: sorting by region and type depends on their order
# dogu/bati/kuzey/guney/merkez = east/west/north/south/central ; mesken/isyeri = residential/commercial
REGION = ["dogu", "bati", "kuzey", "guney", "merkez"]
TYPE = ["mesken", "isyeri"]
PERIODS = [f"2026-{a:02d}" for a in range(1, 13)]
COLUMNS = ("subscriber", "period", "region", "type", "index", "unit", "m3")
FILE_CONSTANT = 120          # the fixed cost of touching a file , in cells
SUBSCRIBERS = {}
for i in range(200):
    r = rng(SEED + 37 * i)
    SUBSCRIBERS[f"A{i + 1:03d}"] = {"region": REGION[int(r() * 5)], "type": TYPE[int(r() * 2)],
                                    "start": 10000 + int(r() * 60000)}


def rows():
    """Consumption rows coming out of the pipeline. In a healthy source the screen and dedupe
    stages drop no rows , so consumption is computed directly from the source , by period difference."""
    raw = []
    for i, (sub, a) in enumerate(SUBSCRIBERS.items()):
        e = a["start"]
        for p in PERIODS:
            r = rng(SEED + 101 * i + 7 * PERIODS.index(p))
            e += 4 + int(r() * 46)
            raw.append({"subscriber": sub, "period": p, "region": a["region"], "type": a["type"],
                        "index": e, "unit": "m3"})
    table, out = {(k["subscriber"], k["period"]): k for k in raw}, []
    for k in raw:
        i = PERIODS.index(k["period"])
        prev = table.get((k["subscriber"], PERIODS[i - 1])) if i else None
        if prev is not None:
            out.append(dict(k, m3=k["index"] - prev["index"]))
    return out


def write(records, partitioning=()):
    """Distributes the rows to files by the partition key."""
    layout = {}
    for k in records:
        layout.setdefault(tuple(k[a] for a in partitioning), []).append(k)
    return layout


def query(layout, partitioning, selected, filters=None, columnar=True):
    """Files touched , cells read and cost = files x FILE_CONSTANT + cells.
    The columnar read takes only the selected columns , the row-oriented read takes the whole record."""
    filters = {} if filters is None else filters
    pruning = {a: v for a, v in filters.items() if a in partitioning}
    files, cells = 0, 0
    width = len(selected) if columnar else len(COLUMNS)
    for key, records in layout.items():
        if pruning:
            pair = dict(zip(partitioning, key))
            if any(pair[a] != v for a, v in pruning.items()):
                continue
        files += 1
        cells += len(records) * width
    return {"files": files, "cells": cells, "cost": files * FILE_CONSTANT + cells}


ROWS = rows()
LAYOUT = write(ROWS, ("period",))
ADD_ORDER = ["region", "m3", "subscriber", "type", "unit", "index", "period"]
print(f"pipeline rows {len(ROWS)} , files in the period layout {len(LAYOUT)} ,"
      f" rows per file {len(ROWS) // len(LAYOUT)}")
print("\nselected columns   columnar cells     cost   row-oriented cells     cost   difference")
for n in range(1, len(COLUMNS) + 1):
    sel = tuple(ADD_ORDER[:n])
    a = query(LAYOUT, ("period",), sel, {"period": "2026-07"}, True)
    b = query(LAYOUT, ("period",), sel, {"period": "2026-07"}, False)
    print(f"{n:>16}   {a['cells']:>14}  {a['cost']:>7}   {b['cells']:>18}"
          f"  {b['cost']:>7}   {b['cost'] - a['cost']:>10}")

pipeline rows 2200 , files in the period layout 11 , rows per file 200

selected columns   columnar cells     cost   row-oriented cells     cost   difference
               1              200      320                 1400     1520         1200
               2              400      520                 1400     1520         1000
               3              600      720                 1400     1520          800
               4              800      920                 1400     1520          600
               5             1000     1120                 1400     1520          400
               6             1200     1320                 1400     1520          200
               7             1400     1520                 1400     1520            0

The second row is the starting number of the lesson. Of two layouts touching the same file, the same 200 rows and the same filter, one reads 400 cells and pays 520, the other reads 1400 cells and pays 1520. Neither is smarter; one has to take five columns the query did not ask for, the other does not.

The real finding of the table is the constancy of the two columns on the right. The row-oriented read stays at 1400 cells and does not react at all to the number of selected columns, because it already took the whole record on the first query. The columnar read grows by exactly 200 cells with every added column. The difference therefore falls steadily: 1200, 1000, 800, 600, 400, 200 and at the seventh column 0.

The closing point is not a threshold but a definition. What the columnar format gains is the number of columns the query does not read, no more and no less. At the seventh column the gain is zero, because there the query already asks for the whole record. To call a layout “fast because it is columnar” means this table was not read: it is true for a query that reads two columns and false for a query that reads seven.

The file constant has no share in this table. Because both layouts touch a single file, the fixed part of the cost is 120 in both, and the cost difference equals the cell difference exactly. Had the constant been 60 or 480, the right-hand column would not have changed. This is a result insensitive to the constant, and the opposite is seen in the next lesson.

Compression Is Measured in Cells Read

The model above counts every cell as one unit. The second gain of the columnar layout lies here: because all the values of a column sit adjacent, consecutive repetitions can come down to a single cell. The block below measures this shrinking in runs and counts the seven columns of the file one by one.

# This block continues the COLUMNS , FILE_CONSTANT , rows() and write() definitions of the first block.
def run_count(values):
    """The number of blocks formed by consecutive identical values."""
    n, prev = 0, object()
    for d in values:
        if d != prev:
            n += 1
            prev = d
    return n


def compressed_cells(records, selected, columnar=True):
    """Compressed read , in cells. In the columnar layout each column is adjacent
    within itself and consecutive identical values come down to a single cell; in the
    row-oriented layout the record stands whole , the keys of two records are never the
    same , so no shrinking takes place."""
    if not columnar:
        return len(records) * len(COLUMNS)
    return sum(run_count([k[s] for k in records]) for s in selected)


FILE = write(rows(), ("period",))[("2026-07",)]
print(f"single file , period 2026-07 , rows {len(FILE)}")
print("\ncolumn     distinct values   runs   uncompressed   compressed")
for s in COLUMNS:
    print(f"{s:<11}{len(set(k[s] for k in FILE)):>15}   {run_count([k[s] for k in FILE]):>4}"
          f"   {len(FILE):>12}   {run_count([k[s] for k in FILE]):>10}")
raw, comp = len(FILE) * len(COLUMNS), compressed_cells(FILE, COLUMNS)
rowwise = compressed_cells(FILE, COLUMNS, False)
print(f"\nseven columns , columnar uncompressed: cells {raw} , cost {raw + FILE_CONSTANT}")
print(f"seven columns , columnar compressed  : cells {comp} , cost {comp + FILE_CONSTANT}")
print(f"seven columns , row-oriented         : cells {rowwise} , cost {rowwise + FILE_CONSTANT}")

single file , period 2026-07 , rows 200

column     distinct values   runs   uncompressed   compressed
subscriber             200    200            200          200
period                   1      1            200            1
region                   5    158            200          158
type                     2     95            200           95
index                  200    200            200          200
unit                     1      1            200            1
m3                      46    196            200          196

seven columns , columnar uncompressed: cells 1400 , cost 1520
seven columns , columnar compressed  : cells 851 , cost 971
seven columns , row-oriented         : cells 1400 , cost 1520

The closing point of the previous section comes out of the table with this block. The query that reads all seven columns reads 851 cells in the columnar layout and 1400 cells in the row-oriented layout; the cost is 971 against 1520. Where the gain of selective reading ends, the gain of compression continues, and the difference stops at 549 cells.

The parting of the run column and the distinct value column is the most misleading place of the lesson. region carries five distinct values and yet gives 158 runs: because the file is written in subscriber order, the five values are scattered over two hundred rows and there are hardly any consecutive repetitions. period and unit carry a single value and come down to 1 cell; neither changes across the file. What compresses is not a small number of different values but identical values standing side by side.

The subscriber and index columns are the opposite end of this. Both are unique per row, so they give 200 runs and stay exactly the same as the uncompressed value. A query that reads these two columns sees no compression gain at all in the columnar layout.

Ordering Is Chosen for a Single Query

The number of runs depends on the order in which the file is written, so it can be changed by a write decision. The block below writes the same file in three different orders and compares the cells read by three queries.

# This block continues the definitions of the previous two blocks.
ORDERINGS = {"subscriber (natural)": lambda k: (k["subscriber"],),
             "region , type": lambda k: (k["region"], k["type"]),
             "m3": lambda k: (k["m3"],)}
QUERIES = {"region+type": ("region", "type"), "m3": ("m3",), "subscriber+index": ("subscriber", "index")}
FILE = write(rows(), ("period",))[("2026-07",)]
print("single file , cells read ; in parentheses cost = 1 x 120 + cells")
print("\nordering              " + "".join(f"{a:>18}" for a in QUERIES) + f"{'seven columns':>18}")
for name, key in ORDERINGS.items():
    s = sorted(FILE, key=key)
    h = [compressed_cells(s, sel) for sel in QUERIES.values()] + [compressed_cells(s, COLUMNS)]
    print(f"{name:<21} " + "".join(f"{f'{x} ({x + FILE_CONSTANT})':>18}" for x in h))
h = [len(FILE) * len(sel) for sel in QUERIES.values()] + [len(FILE) * len(COLUMNS)]
print(f"{'uncompressed':<21} " + "".join(f"{f'{x} ({x + FILE_CONSTANT})':>18}" for x in h))

single file , cells read ; in parentheses cost = 1 x 120 + cells

ordering                     region+type                m3  subscriber+index     seven columns
subscriber (natural)           253 (373)         196 (316)         400 (520)         851 (971)
region , type                   15 (135)         200 (320)         400 (520)         617 (737)
m3                             252 (372)          46 (166)         400 (520)         700 (820)
uncompressed                   400 (520)         200 (320)         400 (520)       1400 (1520)

The second row is the largest gain. When the file is ordered by region and type, the query reading these two columns goes from 253 cells to 15 cells, and the cost falls from 373 to 135. The five regions and the two types now stand as adjacent blocks, and fifteen runs are enough to describe the whole file.

The second column of the same row carries the second claim of this course: a layout can also make things worse. Ordering by region raises the m3 column from 196 runs to 200 runs. The number is small but the direction is clear: for that column the ordering is not merely useless, it is worse than the natural order and lands in exactly the same place as the uncompressed value (200). The query that reads the cubic meter column loses all of its compression because of a decision taken for the region query.

The third row shows the same trade from the opposite direction. When the file is ordered by cubic meters, that column comes down to 46 cells, while region and type stay at 252, that is, indistinguishable from the 253 of the natural order. A file can have only one ordering; the two cannot be chosen at the same time.

The last column gives the total price of the decision: all seven columns come to 851 in the natural order, 617 in the region order and 700 in the cubic meter order. All three numbers are below the 1400 of the row-oriented layout, but which one gets chosen depends only on which query’s name is written. A file whose ordering was chosen without a query name has, by the rule of the course, not been measured.

Summary

• The difference between the columnar and the row-oriented format is a single behavior: the columnar read takes the selected columns, the row-oriented read takes the whole record. On a two-column query the columnar layout pays 400 cells / a cost of 520 and the row-oriented layout pays 1400 cells / a cost of 1520.
• The gain of selective reading is as large as the number of columns the query does not read, and it melts by 200 cells with every added column; it is exactly zero at the seventh column. Because both layouts touch a single file, the per-file constant of 120 stays outside this difference.
• When compression is modeled in terms of cells read, the closing point comes out of the table: all seven columns are 851 cells in the columnar layout and 1400 in the row-oriented layout.
• What compresses is not a small number of distinct values but identical values standing side by side: region gives 158 runs with five distinct values while period comes down to 1 run with a single value; subscriber and index, which are unique per row, do not compress at all.
• Ordering within the file is chosen for one query and takes away the gain of another: ordering by region and type brings that query from 253 to 15 cells while taking the m3 column from 196 to 200, that is, back to its uncompressed value.

Next Step

All the numbers of this lesson were measured inside a single file. The partition key was held fixed as period, the filter always fell on that key and every query touched a single file; for this reason the per-file constant changed no difference. The key itself was never questioned. The next lesson removes this constant and asks two questions: what happens when the filter falls on a field outside the partition key, and where does splitting the same data into more files move the cost, even if the cells read do not rise at all.