# Transforms Source: https://mostlyright.md/docs/reference/transforms/ A transform is a list of SQL steps. Each step is one SELECT over the source relations. The last step's result is the table. ## The engine `transform.engine` must be `duckdb_sql`. The statements run in DuckDB. The schema also accepts `engine: "none"`. A run refuses it: ``` TRANSFORM_PLAN_INVALID: the member transform.engine states none, and what a build of that produces has never been decided; state duckdb_sql and the steps that produce the declared columns ``` A run refuses an empty `transform.steps` the same way. Neither states what a build would produce. ## What a step receives Each source becomes one relation under its own `name`. Write `from asos_observations` to read a source whose `sources[].name` is `asos_observations`. One relation per earlier step, under its `step_id`. Steps run in array order, the order you wrote them, never sorted. A later step reads an earlier one by naming its id. A run refuses a step id that equals a source name, in either direction, and it names every collision at once: ``` TRANSFORM_STEP_SHADOWS_SOURCE: readings, stations name both a step and a source this transform reads; the step would replace the source for every step after it, and nothing would report it ``` Two steps sharing an id are `TRANSFORM_STEP_DUPLICATE`. Quote a name that is also a SQL keyword. `"select"` reads a column of that name. ### Where a source's column types come from The relation carries the header acquisition recorded, in that order. The source's `binding` decides the column types: | `binding` | What the relation holds | | --- | --- | | `text` (the default, and what an absent member means) | every column comes back `VARCHAR`, whatever the file said | | `typed` | the types the bytes themselves carry. Parquet and JSON/NDJSON keep theirs. The Reader infers CSV types over the whole file, with the dialect still pinned | Under `text` every declared type needs an explicit cast in the statement. The recipe casts, not a reader reading a sample buffer. ### Two relations you do not declare A recorded stream source presents text columns in one of two fixed headers. The first is `epoch`, `event_id`, `event_time`, `message`, `message_class`, `message_type`, `received_at`, `sequence`, in that order. The second inserts `member` after `event_time`. It arrives when the connector document declares a member field: a recording taken with `--member`, or a rotating series. A run accepts no other header. Read the frame with the engine's JSON functions. See [Live streams](/docs/build/live-streams/). A collection source hands the transform the pinned Reader's columns plus seven reserved provenance columns: `page_id`, `page_url`, `page_fetched_at`, `page_content_sha256`, `page_revision`, `page_discovered_at`, `page_ordinal`. See [Many-page sources](/docs/build/collections/). ## What the statement must return The worker projects the last step's relation into the declared columns. Every declared column must come back by name. Its engine type must narrow into its declared type without losing anything. ### Declared types | Declared | Engine types that satisfy it | | --- | --- | | `string` | `VARCHAR` | | `integer` | `TINYINT`, `SMALLINT`, `INTEGER`, `BIGINT`. Not the widest, which the engine hands back as an exact decimal | | `decimal` | `DECIMAL(p,s)` with `p` at most 38. Never `FLOAT` or `DOUBLE` | | `float` | `DOUBLE`, `FLOAT` | | `boolean` | `BOOLEAN` | | `date` | `DATE` | | `timestamp` | `TIMESTAMP WITH TIME ZONE` | | `json` | `JSON` or `VARCHAR` | The run refuses a type that cannot narrow, names the column, and never casts it: ``` TRANSFORM_COLUMN_TYPE_MISMATCH: column obs_time is declared timestamp and the statement returns TIMESTAMP ``` That refusal names the same word twice. The difference is the zone. The engine does not load `ICU`, so `AT TIME ZONE` is unavailable. Build a time-zoned timestamp out of an epoch with `to_timestamp(cast(x as bigint))`. Out of text, concatenate the offset: `cast(concat(ts, ':00+00') as timestamp with time zone)`. An average or a division over exact decimals comes back `DOUBLE`. A silent cast there produces a number that no longer says what it was measured as, so `decimal` refuses the floating types. Declare a column `float` where it genuinely is approximate. Where it is exact, end the expression in a `cast(… as decimal(p,s))`. The run refuses a declared column the statement never produces, before a row exists. The projection binds lazily first: ``` TRANSFORM_PLAN_INVALID: column station_name is declared and the statement returns no column of that name; it returns station, observed_at, air_temp_c ``` The projection drops a column the statement returns and the table does not declare. ### Row order The worker orders the result by the table's `grain`, then by every remaining declared column in declaration order. Two rows that tie on the whole key are identical in every column, so no tie resolves differently between builds. A step needs no `order by`, and an `order by` you write is not the stored order. ## What a run refuses ### One read-only statement per step A step is one statement, and its kind must be `SELECT`. The engine's own classifier decides the kind. A statement kind DuckDB grows later starts out refused. | Code | When | | --- | --- | | `TRANSFORM_STATEMENT_NOT_SINGLE` | the engine read more than one statement in the step | | `TRANSFORM_STATEMENT_NOT_SELECT` | the one statement is not a `SELECT`, and the detail names the kind | That rule refuses `ATTACH`, `COPY`, `INSTALL`, `LOAD`, `PRAGMA`, `CREATE`, `INSERT`, `UPDATE` and `DELETE`. Common table expressions, `UNION`, `QUALIFY`, `UNPIVOT`, `VALUES` and `SELECT DISTINCT` are each one `SELECT`, and the rule accepts them. ### The engine cannot reach anything The worker configures the connection before it reads a character of step text. It hands over the exact list of this run's own source files and its scratch directory. Then it sets `enable_external_access = false` and locks the configuration. With external access off, DuckDB refuses to open a file, fetch a URL, or fetch, verify or load an extension. The catalog re-opens read-only, so `COPY … TO` fails on permission and the attachment refuses `CREATE TABLE … AS`. None of this is a denylist of function or token names. A step reaches a source by naming its relation, never by opening its address again. If the worker cannot establish that confinement, it attempts no step and reports `TRANSFORM_ENGINE_UNCONFINED`. ### The result must be re-derivable The worker re-derives a finished table and compares it byte for byte. A step may not read anything the recipe and the acquired bytes do not fix. The engine answers both halves of that question, not a word list. Its parse of the statement says which names the statement references. Its function catalog says whether each name is re-derivable. The run refuses anything the catalog does not report as `CONSISTENT`, anything with side effects, and any name the catalog does not carry at all. That last case catches the niladic keywords. The check follows a built-in macro into its own definition, up to eight expansions deep. This refuses `current_date`, `current_timestamp`, `current_localtimestamp`, `localtime`, `localtimestamp`, `today()`, `now()`, `random()` and `gen_random_uuid()`, and anything that references them: ``` TRANSFORM_PLAN_INVALID: step daily_mean reads current_date, whose value is not fixed by the plan and the acquired bytes, so the result could not be re-derived; the engine reports it as not re-derivable and a sealed candidate must replay byte for byte ``` A date-granular read is the worst case. Both executions inside one build land on the same calendar day, so they agree and the candidate stands. Tomorrow's refresh re-derives it against a later day, and the comparison fails permanently. The refusal comes before the step runs. Qualify a column a publisher happened to spell like one of those names: `observations.current_date`. A two-part reference is never in question. The check asks the binder an unqualified name with nothing in scope. A sibling relation carrying a column of that name does not widen it. ### Time zone This engine builds every table in `UTC`. The locked settings establish the zone before the engine reads a statement. A recipe declaring another zone registers, then refuses on its first run with `RUN_RECIPE_INVALID`. State `UTC` in the document and write the conversion into the steps, where the result is stored. ## Functions worth knowing The run accepts everything the engine reports as `CONSISTENT`, which is nearly all of DuckDB's catalog. These come up most: | Need | Write | | --- | --- | | A cast that yields null instead of stopping the run | `try_cast(x as decimal(6,2))` | | A cast that stops the run on an unparseable value | `cast(x as integer)` | | Text digits with a decimal point into an integer | `cast(try_cast(x as decimal(6,2)) as integer)`, because `try_cast('5.00' as integer)` is null | | A time-zoned timestamp from epoch seconds | `to_timestamp(cast(x as bigint))` | | A time-zoned timestamp from text | `cast(concat(ts, ':00+00') as timestamp with time zone)` | | A calendar day out of a timestamp | `cast(ts as date)` or `date_trunc('day', ts)` | | A fixed-width time bucket | `time_bucket(interval 15 minute, ts)` | | A field out of a JSON frame | `json_extract_string(message, '$.price')` | | Substitute for an absent value | `coalesce(a, b)` | | Keep the latest row per key | `arg_max(v, ts)` | `interval` literals, `case`, `qualify`, window functions and the aggregate functions are all available. ## Shapes ### Rename and cast The commonest first step takes an all-text relation and produces the declared types. ```sql select station, cast(concat(valid, ':00+00') as timestamp with time zone) as observed_at, cast((try_cast(tmpf as decimal(6,2)) - 32) * 5 / 9 as decimal(6,2)) as air_temp_c, try_cast(relh as decimal(5,2)) as relative_humidity_pct from asos_observations where valid is not null and valid <> '' ``` The outer `cast` makes `air_temp_c` a `DECIMAL(6,2)`. Without it the division returns `DOUBLE` and the run refuses the column. ### Join two sources Each source is its own relation. A source's columns are whatever its header said. Rename inside a subquery rather than aliasing across the join. ```sql select r.station, m.station_name, r.observed_at, r.air_temp_c from readings r join (select stid as station, name as station_name from station_metadata) m on m.station = r.station ``` ### Unpivot: one column per period into rows ```sql select region, cast(replace(year_column, 'y', '') as integer) as year, try_cast(value_text as decimal(12,2)) as population from census_wide unpivot (value_text for year_column in (y2020, y2021, y2022, y2023)) ``` The `union all` spelling does the same, and the run accepts it equally: ```sql select region, 2020 as year, try_cast(y2020 as decimal(12,2)) as population from census_wide union all select region, 2021, try_cast(y2021 as decimal(12,2)) from census_wide ``` ### Deduplicate Keep one row per key, choosing which one explicitly. ```sql select station, observed_at, air_temp_c from readings qualify row_number() over ( partition by station, observed_at order by ingested_at desc, air_temp_c ) = 1 ``` Where the tiebreak is a value rather than a whole row, an aggregate says it more directly: ```sql select station, observed_at, arg_max(air_temp_c, ingested_at) as air_temp_c from readings group by station, observed_at ``` Write a total tiebreak either way. A `row_number()` over a partition whose ties nothing resolves picks an arbitrary row, and the replay has to reproduce that same row. ### Time bucketing ```sql select station, time_bucket(interval 1 hour, observed_at) as hour_start, cast(avg(air_temp_c) as decimal(6,2)) as mean_air_temp_c, count(*) as reading_count from readings group by station, time_bucket(interval 1 hour, observed_at) ``` `avg` returns `DOUBLE`, so the `cast` is what makes the column satisfy a declared `decimal`. Declare the column `float` instead where the mean is genuinely approximate and no width is worth choosing. The bucket boundary comes from the values, never from the clock. The run refuses `date_trunc('hour', current_timestamp)`. ## Units `units` is a claim about the table's columns, declared beside the table rather than inside the SQL. The transform never reads it. A v4 run never checks the arithmetic against it, so nothing here catches a column declared in metres that holds feet. Where the statement converts a reading, declare the unit of what the column ends up holding. The recipe declares `air_temp_c` as `Cel`, not `[degF]`. Leave a computed column out of `units` where nothing establishes its unit. A density built out of a mass and a volume has no established unit. Neither does a rate built out of a distance and a duration. Leave both out rather than give them a code the numbers may not be in. The accepted codes are in [Units and vocabulary](/docs/reference/units/). ## Ceilings The engine runs single-threaded under a memory ceiling and a bounded scratch ceiling. Both come from the container's own limit where one is readable, and otherwise from 1 GiB and 4 GiB. Over the memory ceiling, the run fails with DuckDB's own out-of-memory refusal. A run writes at most 10,000,000 rows. [Limits and ceilings](/docs/reference/limits/) has the rest. ## Codes a run reports Each of these carries `failed_stage: "transform"`. | Code | When | | --- | --- | | `TRANSFORM_PLAN_INVALID` | the plan cannot run as written: a declared column the statement does not return, a declared type outside the vocabulary, a relation with no columns, a step that reads something not re-derivable, or a statement the parser cannot serialize | | `TRANSFORM_STEP_DUPLICATE` | two steps claim one `step_id` | | `TRANSFORM_STEP_SHADOWS_SOURCE` | a `step_id` equals a source `name` | | `TRANSFORM_STATEMENT_NOT_SINGLE` | a step holds more than one statement | | `TRANSFORM_STATEMENT_NOT_SELECT` | a step's statement is not a `SELECT` | | `TRANSFORM_COLUMN_TYPE_MISMATCH` | a returned engine type cannot narrow into the declared type | | `TRANSFORM_ENGINE_UNCONFINED` | the engine could not be confined, and the worker attempted no step | | `RUN_EXECUTION_FAILED` | the engine refused the step in its own voice. The detail carries DuckDB's own code and words, for example `BINDER_ERROR: …` | | `RUN_RECIPE_INVALID` | `timezone` is not `UTC` | A `RUN_EXECUTION_FAILED` detail reads `step daily_mean would not run: BINDER_ERROR: Referenced column "avg_ng" not found…`. The engine's taxonomy and the engine's words both reach the run record. Read the detail, not the code.