Sheets

A pipe expresses a sequence: each stage receives the previous stage's rows and hands on its own. That is the right model for a transformation you can order by hand, and the wrong one for a set of interdependent calculations.

Spreadsheet work is the second kind. The sheet operator takes a set of named formulas, works out the order from what they reference, and evaluates them — including per-row values that depend on a figure derived from every row.

from:
  dataset: sales
pipe:
  - op: sheet
    formulas:
      - as: profit
        expr: "revenue - cost"
      - as: total_profit
        reduce: "sum(profit)"
      - as: share
        expr: "profit / total_profit"

total_profit reduces over profit before share reads it. Writing the three in any other order produces the same result.

Two kinds of formula01

Every formula sets exactly one of two keys, and which one it sets is the declaration — there is no separate scope annotation that could disagree with the expression's shape.

That split is what keeps identifiers unambiguous. The natural spreadsheet spelling, revenue / sum(revenue), needs revenue to be a single value and a whole column in the same expression — implementable, but correct only for a fixed list of aggregate names and silently wrong for anything outside it. Routing whole-column values through a named reduce removes the ambiguity instead of managing it.

A reduce is written into every row on the way out, so the next stage sees it as an ordinary column.

Ordering, and what is refused02

The order comes from the references, resolved before any row is read. Alongside it, these are rejected as the sheet compiles, rather than partway through a scan:

• a cycle, reported with every formula that takes part in it

• two formulas sharing an as name

• an expression the language cannot parse

One check necessarily waits for rows: a reference to a name that is neither another formula nor a column of the input. Which columns the input has is not knowable until the rows arrive — and it is the union of the keys across all of them, since a column absent from the first row is still a column of the sheet. So a mistyped column name surfaces on the first evaluation, not at compile time, and the error names the identifier it could not resolve.

There is no iterative or circular calculation mode. A cycle is an error.

Errors during evaluation03

- op: sheet
  onError: fail # or: null
  formulas: [...]

fail is the default and aborts on the first error, matching every other operator. A stage that emits partially-wrong rows is worse than one that stops, because the stages after it compute on the damage and nothing surfaces it. A spreadsheet's #DIV/0! works because a person is looking at the cell; a query pipeline has no such reader.

null writes null into the failing cell and carries on. It exists for imported workbooks, where a handful of bad rows should not fail the whole query.

Aggregates computed by the source04

When a sheet's rows are known to be every row that matched — rather than a page cut short by a safety cap — a reduce over a source column can be computed by the database instead of scanned in memory. All eligible reduces go in a single query, derived from the same plan the rows came from so it spans exactly the rows it is divided into.

This never changes an answer. It is skipped whenever it could:

• the rows were clipped, or their provenance is unknown

• the reduce reads a column the sheet itself computed, which exists nowhere else

• the aggregate has no portable SQL spelling

• the query pages with an offset, which cannot be expressed over an aggregate

• the prefix already groups or aggregates, so it hands the sheet no table columns to aggregate over — its output exists only in that result

A reduce that is not delegated is simply computed here, and one that fails to delegate falls back to the same place.

Windows05

Sheets have no window functions, deliberately. Put a window stage before the sheet and read its output like any other column:

pipe:
  - op: window
    fn: lag
    field: revenue
    partitionBy: [region]
    orderBy: [{ field: ts }]
    as: prev_revenue
  - op: sheet
    formulas:
      - as: growth
        expr: "revenue - prev_revenue"

This is better than an inline lag(), not a workaround for the lack of one. Ordering and partitioning are exactly what a spreadsheet's LAG() leaves implicit, and exactly what makes it wrong when rows arrive in a different order. The window stage makes both explicit. It works in the other direction too — a window can rank by a column a sheet computed.

Memory06

Columns are materialised only for reduces that take a native kernel, and a delegated reduce materialises none at all, so an ordinary sheet holds one or two. For a sheet that reduces over many distinct columns that cannot be delegated, columnBudgetBytes caps the resident set and spills the least recently used beyond it.

Leave it unset unless that describes your sheet. It is not a limit on how large a sheet can be — that is governed by the executor's row cap, and by the row representation, neither of which spilling a column affects.

Extending it07

A sheet can already name any aggregate your expression language has, so no registration is needed to use one. Registering a kernel gives that aggregate a scan over the typed column instead of a boxed one, and lets it be delegated to the source when it names a SQL spelling:

reg := sheet.NewRegistry()
_ = reg.RegisterReduce(p95Kernel{}) // PushdownName() → "percentile_cont"
engine.SetSheetFuncs(reg)

Registrations are per-engine, not global — two hosts in one process would otherwise collide. A name already taken by a built-in is refused rather than shadowed: a sheet using only the built-in set means the same thing on every host, and that is the one property worth protecting.

Requirements08

The operator needs an expression compiler, declared as exprCompiler. Unlike the evaluator used elsewhere, it prepares an expression once and reports the names it references — dependency resolution rests on that analysis, so there is nothing useful to fall back to without it. A deployment that has not wired one sees sheet reported as unavailable rather than failing at query time.