Transformations

A transformation is a named, catalog-owned writer. It reads one or more relations and writes its output rows into a table. After the transformation is created, the engine keeps the target in step with every committed change to the sources, across restarts, until the transformation is dropped. The statement reference is CREATE TRANSFORMATION.

The shape of a transformation

A transformation is a SELECT body plus a target:

• the body filters, computes, groups, windows and joins its sources;
• the target is a table, or one attribute group of it;
• output columns bind to target columns by name, so use AS to rename an output.

The engine subscribes the transformation to its sources through PSI, applies each committed change once, and writes the result into the target. A transformation is a continuous writer whose output you read as an ordinary table.

CREATE TRANSFORMATION balance_activity INTO balances ATTRIBUTE GROUP activity AS
SELECT
  chain_id,
  recipient AS address,
  token,
  COUNT(*) AS transfer_count,
  MAX(transferred_at) AS last_transfer_at,
  MAX(_index) AS activity_index
FROM transfers
GROUP BY chain_id, recipient, token;

Every transfer that commits updates the recipient's activity group in balances, including after the process restarts.

The target and its owner

A transformation owns what it writes: a whole table, or one attribute group of it. Other producers and other transformations write the other groups of the same rows. Dropping a transformation leaves its target and the target's rows in place.

Chaining

A transformation's target is an ordinary table, so another transformation can read it. A change to the first source flows through every link of the chain, each link matched on the table and attribute group it reads.

CREATE TRANSFORMATION transfer_value_lookup INTO transfer_values AS
SELECT
  t.chain_id AS chain_id,
  t.token AS token,
  t._index AS _index,
  t.amount AS amount,
  p.price_usd AS price_usd
FROM transfers t
LOOKUP JOIN token_prices p ON p.chain_id = t.chain_id AND p.token = t.token;

transfer_values is then a source for the next transformation, for example a fold of USD volume per token.

Folds into an attribute group

A transformation with GROUP BY keeps one output row per group and updates it as source rows arrive, change and disappear. The aggregates include COUNT, SUM, MIN, MAX, AVG and COUNT(DISTINCT …), and an output may be arithmetic over aggregate calls, such as a weighted mean. A fold writes one attribute group of its target, so several transformations each own a different group of the same table. A transformation that writes a group outputs the target's full primary key, and every other output is a column of that group.

Reading and writing the same table

Lineage is tracked per attribute group. A transformation may read a table and write one group of that same table. The engine never delivers the transformation's own writes back to it, and checks at CREATE that no chain of transformations loops back through the groups it reads. See CREATE TRANSFORMATION.

After a restart

A transformation records its progress durably. When the node starts again, each transformation resumes in the cheapest exact way, and logs its choice as event=derived_runtime_resume_shape:

• Exact resume continues from the transformation's cursors and target rows without re-reading the source. LOOKUP JOIN transformations resume from their event-side cursor.
• Current rebuild makes one pass over the source's current rows and writes only the groups that changed.
• Reseed rebuilds from a source snapshot.

Redefining and removing

CREATE OR REPLACE TRANSFORMATION installs a new revision of the definition, which reseeds. DROP TRANSFORMATION removes the writer and leaves the target table and its rows in place.

Observing transformations

SELECT
  name,
  source_position,
  rows_written,
  completed_rows,
  transformation_revision
FROM system.transformation_revisions;

SELECT
  row_id,
  source_relation_id,
  derived_relation_id,
  target_relation_id,
  transformation_revision
FROM system.derived_lineage;