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.

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.

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.

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.

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

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:

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.

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:

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

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.

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