# CREATE TRANSFORMATION

> Grammar, body rules, joins, folds, group targets, redefinition and removal of transformations.

For what a transformation is and how it behaves over time, read
[Transformations](/docs/concepts/transformations) first. This page is the statement reference.
The examples use the `balances`, `transfers` and `token_prices` tables.

## Grammar

```text
CREATE [OR REPLACE] TRANSFORMATION <name>
    INTO <existing table> [ATTRIBUTE GROUP <group>]
    AS <body>

<body> := a SELECT over one or more relations, with filters, GROUP BY folds,
          windows and joins (INNER, LEFT, RIGHT, FULL, ASOF LEFT, LOOKUP)

DROP TRANSFORMATION [IF EXISTS] <name>
SHOW CREATE TRANSFORMATION <name>
```

## Body rules

- The body is a `SELECT`; it may filter, compute, group, window and join.
- `WHERE` filters and computed outputs (such as `amount * 2 AS doubled`) apply per row.
- `GROUP BY` folds take `COUNT`, `SUM`, `MIN`, `MAX`, `AVG` and `COUNT(DISTINCT …)`, and an
  output may combine aggregate calls arithmetically.

## Target rules

- The target is a table that exists before the statement runs.
- Output columns bind to target columns by name. Rename with `AS`.
- The transformation owns what it writes: the whole target, or one attribute group of it.
- With `ATTRIBUTE GROUP g`, the transformation writes only group `g`. Its output includes the
  target's full primary key, and every other output is a column of `g`.
- Each attribute group has one writer, so several transformations and producers maintain one
  row together.

## A fold into an attribute group

```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;
```

The reply to a successful `CREATE TRANSFORMATION` carries `"ddl_kind":4`. From then on,
every transfer updates the `activity` group of the recipient's balance row, while another
producer writes the `holding` group.

## A filtered transformation

```sql
CREATE OR REPLACE TRANSFORMATION large_transfer_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
WHERE amount > 1000000000
GROUP BY chain_id, recipient, token;
```

## Joins

A transformation body may join relations. The [Joins](/docs/sql/joins) page covers each kind.

| Join | In a transformation |
| --- | --- |
| `INNER JOIN`, `LEFT JOIN`, `RIGHT JOIN`, `FULL JOIN` | Equality conditions between the sides. A change on any side re-derives the joined rows. |
| `ASOF LEFT JOIN` | Each row joins the right row in force at its time. |
| `LOOKUP JOIN` | Each event reads the lookup row by its key when it is processed and emits one row. |

```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;
```

## Reading and writing the same table

A transformation may read a table and write one attribute group of it. The engine never
delivers the transformation's own writes back to it, and checks at `CREATE` that a chain of
transformations closes no loop through the groups it reads and writes.

## Redefining: CREATE OR REPLACE

`CREATE OR REPLACE TRANSFORMATION` with an existing name installs a new revision of the
definition. The new revision reseeds the target from its sources, and
`system.transformation_revisions` and `system.derived_lineage` show the new revision.

## DROP TRANSFORMATION

```sql
DROP TRANSFORMATION balance_activity;
```

The target table keeps its rows. `IF EXISTS` makes a missing name a no-op:

```sql
DROP TRANSFORMATION IF EXISTS balance_activity;
```

## SHOW CREATE TRANSFORMATION

```sql
SHOW CREATE TRANSFORMATION balance_activity;
```

---

Source: https://nyxdb.io/docs/sql/create-transformation/