# Transformations

> Continuous, checkpointed writers that keep a table, or one attribute group of it, derived from other relations.

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](/docs/sql/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](/docs/concepts/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.

```sql
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.

```sql
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](/docs/concepts/attribute-groups) 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](/docs/sql/create-transformation#reading-and-writing-the-same-table).

## 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

| Relation | What it shows |
| --- | --- |
| `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. |

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

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

---

Source: https://nyxdb.io/docs/concepts/transformations/