CREATE TRANSFORMATION

For what a transformation is and how it behaves over time, read Transformations first. This page is the statement reference. The examples use the balances, transfers and token_prices tables.

Grammar

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

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

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 page covers each kind.

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

DROP TRANSFORMATION balance_activity;

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

DROP TRANSFORMATION IF EXISTS balance_activity;

SHOW CREATE TRANSFORMATION

SHOW CREATE TRANSFORMATION balance_activity;