ProductDerived tables

Describe the table you need; it stays current

Write the aggregate, the enriched view or the balance you need as a query. A transformation keeps its target, or one attribute group of it, in step with every committed change to the sources, across restarts, until you drop it. No stream processor, no results to reconcile.

Get startedTransformations in the docs

The definition

Filter, fold, enrich and align in time

A transformation is a query body plus a target. The body filters, computes, groups, windows and joins its sources; select a shape to see the statement behind it.

Statement reference

A fold into one attribute group01 / 04

activity per balance row, kept current from transfers

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;

The target

A whole table, or one group of it

A transformation owns what it writes and nothing else. The target exists before the statement runs, and output columns bind to target columns by name.

Whole table01
Own every column of the target
The transformation writes the whole table. Its output is read as an ordinary table, so dashboards, services and other transformations read it the same way they read anything else.
INTO transfer_values
One attribute group02
Own one group, share the row
With a group named in the target, the transformation writes only that group. Its output carries the target’s full primary key, and every other output is a column of the group. Other producers and other transformations write the other groups of the same rows.
INTO balances ATTRIBUTE GROUP activity
The same table03
Read a table, write one group of it
Lineage is tracked per attribute group, so a transformation can read a table and write one group of that same table. The engine never delivers its own writes back to it, and checks at creation that no chain of transformations loops back through the groups it reads.
own writes filtered · no loop through the groups it reads

Materialized views

Name a result, read it like a table

A materialized view is maintained continuously into an engine-owned table and read by its name. Inserts, updates and deletes on a source reach it, it is exact after a restart, and only the engine writes it.

Give a view parameters and it becomes a template that holds no data by itself. A materialization fixes the arguments and creates a maintained instance, read by its own name.

CREATE MATERIALIZED VIEW: a maintained result with a name of its own
CREATE MATERIALIZATION: one instance of a parameterized template
CREATE OR REPLACE: a new revision; the rows are rebuilt from it

Views in the docs

volume per token, maintained

CREATE MATERIALIZED VIEW token_volume AS
SELECT chain_id, token, SUM(amount) AS volume, COUNT(*) AS transfers
FROM transfers
GROUP BY chain_id, token;

a template, and one instance of it

CREATE MATERIALIZED VIEW balances_by_chain(${chain:UINT16}) AS
SELECT address, token, amount
FROM balances
WHERE chain_id = ${chain};
CREATE MATERIALIZATION balances_chain_1 OF balances_by_chain(chain => 1);

How changes arrive

Published once, applied exactly once

Every derived table, materialized view, index and live query is fed by the same publication of committed writes, so nothing you derive drifts from its sources.

01
Every committed write is published once
Each committed write to a tablet is published as an immutable batch on that tablet’s committed feed. Only committed changes are published, and for keyed tables the published row is the key’s complete current row.
committed feed
02
Each definition subscribes by exact pair
A transformation or view selects changes by relation, component and key, never by a stream name alone. A transformation that writes one group of its own source has that group filtered from its subscription.
stream:key pair
03
Each keeps its own durable cursor
A consumer acknowledges only what it has applied and resumes from its cursor after a restart. A cursor the retained feed no longer covers reseeds from a snapshot; nothing is skipped silently.
durable cursor
04
Producers never wait for consumers
A slow transformation or index does not hold back a write. Delivery is admitted per owner against the feed’s retention capacity, so one consumer cannot grow the retained feed without bound.
never blocks a write

One row per subscription in system.psi_streams: consumer kind, demand, queued frames and bytes, delivered and acknowledged sequence, resnapshot count and source-to-delivery lag.

PSI in the docs

Chaining and restarts

The output of one feeds the next

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.

Enrich, then fold

The enriched rows land in a table of their own. That table is a source for the next transformation, for example a fold of value per token, and a change to the first source flows through every link.

transfers → transfer_values → the next fold

the first link of a chain

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;

Pick up where it left off

A transformation records its progress durably. When the node starts again, each one resumes in the cheapest exact way and logs its choice: an exact resume from its cursors and target rows, a current rebuild over the source’s current rows, or a reseed from a source snapshot.

event=derived_runtime_resume_shape

logged once per transformation at start

event=derived_runtime_resume_shape
# exact resume: continue from the cursors and the target rows
# current rebuild: one pass over the source's current rows
# reseed: rebuild from a source snapshot

Observing

Progress, health and lineage, as rows you query

Three system relations show what every transformation is doing, and you read them like any table: one row per transformation, one per revision with its source position, rows written, completed rows, condition and health, and the lineage from each source to its target.

system.transformations: one row per transformation, with its name and identity
system.transformation_revisions: per revision: source position, rows written, completed rows, condition and health
system.derived_lineage: the source, transformation and target of each live revision

System relations

every revision, and where it stands

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

lineage from source to target

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

Lifecycle

Change the definition, keep the table

Definitions are revised in place and removed without touching the rows they produced.

Redefine

Install a new revision

Replace the definition under the same name and a new revision is installed. The new revision reseeds the target from its sources, and the revision and lineage relations show it.

CREATE OR REPLACE TRANSFORMATION

Alter the table

Change the schema under a running writer

A transformation that reads or writes a table is re-bound to each new schema generation and keeps running. Add a column or an attribute group while it runs; existing rows read the new column at its introduction value.

ALTER TABLE … ADD ATTRIBUTE GROUP

Remove

Stop the writer, keep the rows

Dropping a transformation stops the writer and leaves the target table and its rows in place. A dropped materialized view takes its engine-owned rows with it, and instances of a template are dropped before the template.

DROP TRANSFORMATION · DROP MATERIALIZED VIEW

Questions about derived tables

What a technical buyer asks about transformations and materialized views, answered from the docs.

Transformations in the docsOpen an issue

Both are maintained continuously from their sources through the same publication of committed writes. A transformation is a named writer you point at a table that already exists, or at one attribute group of it, and other producers can write the other groups of the same rows. A materialized view keeps its rows in an engine-owned table that only the engine writes, and is read by its name.

Write the query once, keep reading the table

Start a node, create your two tables and define a transformation. The getting-started guide takes you from install to a self-maintaining derived table.

Get startedStatement reference

Describe the table you need; it stays current

Filter, fold, enrich and align in time

A whole table, or one group of it

Own every column of the target

Own one group, share the row

Read a table, write one group of it

Name a result, read it like a table

Published once, applied exactly once

Every committed write is published once

Each definition subscribes by exact pair

Each keeps its own durable cursor

Producers never wait for consumers

The output of one feeds the next

Enrich, then fold

Pick up where it left off

Progress, health and lineage, as rows you query

Change the definition, keep the table

Install a new revision

Change the schema under a running writer

Stop the writer, keep the rows

Questions about derived tables

Is a transformation the same as a materialized view?

What happens to the target when I drop a transformation?

Can a slow transformation slow down my writes?

Which joins can a definition use?

How do I see whether a transformation is caught up?

Write the query once, keep reading the table