Transforms

Transforms are functions that take a relation and produce a relation.

Usually they are chained together into a pipeline, which resembles an SQL query.

Transforms were designed with a focus on modularity, so each of them is fulfilling a specific purpose and has defined invariants (properties of the relation that are left unaffected). That’s often referred to as “orthogonality” and its goal is to keep transform functions composable by minimizing interference of their effects. Additionally, it also keeps the number of transforms low.

For example, select and derive will not change the number of rows, while filter and take will not change the number of columns.

In SQL, we can see this lack of invariant when an aggregation function is used in the SELECT clause. Before, the number of rows was kept constant, but introduction of an aggregation function caused the whole statement to produce only one row (per group).

These are the currently available transforms:

TransformPurposeSQL Equivalent
deriveCompute new columnsSELECT *, … AS …
selectPick & compute columnsSELECT … AS …
filterPick rows based on their valuesWHERE, HAVING,QUALIFY
sortOrder rows based on the values of columnsORDER BY
joinAdd columns from another table, matching rows based on a conditionJOIN
takePick rows based on their positionTOP, LIMIT, OFFSET
groupPartition rows into groups and applies a pipeline to each of themGROUP BY, PARTITION BY
aggregateSummarize many rows into one rowSELECT foo(…)
windowApply a pipeline to overlapping segments of rowsOVER, ROWS, RANGE
loopIteratively apply a function to a relation until it’s emptyWITH RECURSIVE …

See also

  • fromfrom is the main way of getting data into a pipeline (it’s not listed above since it’s not technically a transform, since it doesn’t receive an input).