Transforms¶

The closed transform whitelist a mapping's transform: chains may draw from — 24 built-ins, each typed with an input domain and an output type. Chains typecheck at compile: a step whose input type is outside its domain, or a chain whose terminal type is not assignable to the field's declared type, is a TypeCheckError; a name outside the whitelist is an UnknownTransformError naming the closest match.

Chains start at string — source extraction yields text — and each step's output feeds the next step's input. Args are spec-level literals, written as a bare name (trim), a single-arg mapping ({parse_ts: ISO8601}), or a list ({to_decimal: [12, 4]}).

String¶

Casts and parses¶

A timestamp that states its own offset¶

parse_ts reads a local wall clock, and to_utc is the only door into UTC. So 2026-01-06T12:00:00+01:00 is text that says something the transform is not allowed to believe. Which zone a column is written in is the spec's statement to make — to_utc names it, or zone_in: declares it — and here the data contradicts whatever the spec said.

Every engine bloomery targets resolves that disagreement the same silent way — it drops the offset and keeps 12:00, an hour off the instant the row actually carries, with nothing downstream able to see it. bloomery produces NULL for such a value instead, identically on DuckDB, PostgreSQL and Trino.

A NULL is something the rest of the system already knows how to report. On an entity with a quality: block, the implicit coercible rule reads it as a failed cast and its default disposition sends the row to the reject table; for the _ingested_at ingestion-metadata column — the one no rule can reach, because ingestion metadata is never a mapped field — the generated audit stops the run outright. On an entity with no quality: block the value is simply NULL, which is better than a wrong number and is still quiet.

Two things it deliberately does not do:

• It does not convert. Reading the offset would make one declaration mean a local clock on one row and an instant on the next, decided by the bytes. Normalise upstream, or state the zone once with to_utc on a source that has been normalised.
• It does not refuse Z on a bare parse. Z names UTC, which is the zone the timestamp type is already in, so nothing is lost by dropping it — where a numeric offset loses exactly the difference between the wall clock and the instant.

Z under to_utc is NULL. [{parse_ts: ISO8601}, {to_utc: Europe/Paris}] declares the text a wall clock in Paris, so a trailing Z contradicts it exactly as +01:00 does, and every engine yields NULL for it. Keeping the value would shift an instant already in UTC by Paris's offset a second time.

ISO8601_INSTANT — a source that stamps instants¶

A source whose every timestamp carries Z or a numeric offset is writing instants, not wall clocks, and says so with parse_ts: ISO8601_INSTANT:

# mapping.yaml
event_at:
  from: "$.event_at"
  transform: [{parse_ts: ISO8601_INSTANT}]

2026-01-06T12:00:00+01:00 becomes the instant 11:00 UTC, and 2026-01-06T11:00:00Z the same instant, identically on DuckDB, PostgreSQL and Trino. Zoneless text is NULL under it — the mirror of ISO8601, where offset text is NULL — so one declaration never means a wall clock on one row and an instant on the next.

The value is already UTC, so the compiler holds the chain to that:

• A to_utc after it is refused at resolve. Both sides are timestamp, and the type system cannot see the second conversion; the compiler can. Drop the to_utc, or parse with ISO8601 if the text is a wall clock without a zone.
• A zone_in: beside it may only be UTC. Any other zone is refused at resolve, and none is needed: an instant is never read as a wall clock, so no undeclared-zone check fires on it.

A parse_ts format with %z or %Z is refused at parse, naming ISO8601_INSTANT: no format parses a zone and then drops it. An escaped %%z or %%Z is literal text and is not refused.

On upgrade, a source whose timestamps carry offsets produced plausible, wrong values before this landed and produces NULLs after it. That is the point of the change, and it is still a value moving under you: check the affected columns with a coercible rule before you promote the upgrade.

parse_date: ISO8601 is unaffected — an ISO date has no time and therefore no offset.

zone_in: — what clock the source runs on¶

A wall clock read for its position is refused unless something says which clock it came off. parse_ts cannot know, and the timestamp type calls whatever it produces UTC — so a feed publishing local times with no zone on them compiles clean and answers with an order placed at 21:30 on 31 January in New York counted as a January order. It is 02:30 on 1 February. No row is null, nothing is duplicated, and February is short by that order's revenue.

A converting chain has already said it: {to_utc: America/New_York} names the clock the wall clock was written on. What has nowhere to go is the other claim — this feed really does write UTC — and zone_in: is that place, on the mapping's field beside currency_in::

# mapping.yaml
placed_at:
  from: "$.placed_at"
  zone_in: UTC
  transform: [{parse_ts: ISO8601}]

It is only asked for where the instant's position decides an answer — a mart's date role, a comparison against a literal instant in a metric's expression or filter:, an as-of anchor, a rollup's kept bucket. A timestamp that is carried, projected, or compared with another column has no boundary to fall the wrong side of, and declaring a zone for it would state a fact nothing reads. Where the declaration is missing, the refusal is UndeclaredZone, and it names both spellings that fix it.

On the mapping, never on the canonical field. A canonical placed_at fed by a New York feed and a London feed runs on two clocks, and a field-level key would have one answer for two facts — the same argument currency_in: is here for. Each mapping declares its own:

# mapping_uk.yaml
placed_at:
  from: "$.placed"
  transform: [{parse_ts: ISO8601}, {to_utc: Europe/London}]

# mapping_us.yaml
placed_at:
  from: "$.placed"
  transform: [{parse_ts: ISO8601}, {to_utc: America/New_York}]

Both spellings together are checked, not merely allowed. zone_in: Europe/London beside {to_utc: Europe/London} is a redundancy the compiler reads; a disagreement between them is refused naming both, because one of the two is wrong and nothing in the spec says which. A non-UTC zone_in: with nothing converting it is refused for the opposite reason: the declaration is right and the instant is still wrong.

What this does not check is whether the zone is the true one. A Europe/London on a New York feed shifts every row by five hours with the compiler's full blessing. The question the declaration answers is what clock does this source system run on, which is written in that system's documentation rather than in its rows — so it is a question with a findable answer, and bloomery can only check that somebody answered it.

Null handling and JSON¶

Arithmetic¶

Decimal precision and scale are tracked through arithmetic: multiply/divide widen to decimal(p1+p2, s1+s2); crossing the 38-digit precision cap is a loud TypeCheckError telling you to narrow with an explicit to_decimal step.

Currency¶

# entity_model.yaml — the converted amount is its own field
amount_usd: {type: "decimal(12,4)", canonical: amount_usd}

# mapping.yaml
amount_usd:
  currency_in: EUR
  from: "$.amount"
  transform: [{to_decimal: [12, 4]}, {convert: [EUR, USD, paid_at]}]

Everything about that line is declared, and none of it is inferred. The anchor could be guessed from a mart's date role, and a wrong guess is a plausible number computed against the wrong day, which is the failure class this project exists to refuse.

currency_in: — what the source holds¶

A conversion out of an undeclared currency is refused. convert's first argument says what the column is in before the conversion, and on its own that is an assertion checking itself: writing {convert: [JPY, USD, paid_at]} over euros reads the yen rate, applies it to euros, and every cast still succeeds. currency_in: is the fact it is checked against, and it lives on the mapping's field because that is where a source path and a transform chain meet — a canonical field is shared across mappings, and one fed by a euro feed and a dollar feed would need two answers for one declaration.

A chain declares once. Where no direct rate exists, convert through a bridge currency: each step's input is the previous step's output, so only the first needs declaring.

amount_usd:
  currency_in: EUR
  from: "$.amount"
  transform:
    - {to_decimal: [12, 4]}
    - {convert: [EUR, CHF, paid_at]}
    - {convert: [CHF, USD, paid_at]}

The column's currency is what the last conversion produces, and that is what must match the canonical field's currency:. A step whose from disagrees with what the chain is holding at that point is refused, naming both.

Per-row denomination — the code lives in a column¶

A payment processor exports the amount and the currency it was taken in on the same row. Name the sibling column that carries the code, and convert's first argument names it back — the same checked redundancy a literal code gets:

amount_usd:
  currency_in: {column: currency_code}
  from: "$.amount"
  transform: [{to_decimal: [12, 4]}, {convert: [currency_code, USD, paid_at]}]

The rate is then looked up per row, against that row's own code. The currency column names a string field of the same entity, mapped by a direct from: path, exactly as the anchor does — and, like the anchor, it must not convert itself: both are re-lowered into the conversion that reads them, so a converting one would be spliced inside the rate lookup. Only the first step of a chain converts out of the column; a bridge hop after it is an ordinary code-to-code conversion and is checked as one.

Two operational facts follow from the rate being read per row, and neither is a compile-time error because neither is a fact about the spec:

• A code the rate relation has no row for converts to NULL. It is D11's miss, one row at a time: the amount is gone and visible rather than priced at a neighbouring rate. The row is not quarantined as a failed cast — convert nulls on purpose, so the implicit coercible rule does not claim it. To reject on it instead, declare quality: [{rule: not_null, on_fail: quarantine}] on the field. on_fail is required: no rule in this project carries a default disposition.
• A row already in the target currency still reads the rate relation. USD → USD is a lookup like any other, so the feed needs a self-rate row for every code that can appear in the column, or those rows convert to NULL.

The anchor names a date or timestamp column of the same entity, mapped by a direct from: path — a fields: entry or a key: one. A recipe or macro anchor is refused rather than spliced, because its whole derivation would be copied into every converted column. Both currency codes must be ISO-4217 (three uppercase letters): they are compared against the rate relation exactly as written, so eur would match no rate and convert every amount to NULL rather than failing.

The rate relation¶

Conversion reads a table the operator supplies and bloomery never builds. Declare its shape once, in the catalog:

fx_rates:
  relation: fx_rate      # resolved through the naming policy, at the silver layer
  from: from_ccy
  to: to_ccy
  rate: rate
  valid_from: valid_from
  valid_to: valid_to     # required

Both interval ends are required. One end is not an interval: a payment would match every rate at or before its date, and the lookup would multiply rather than convert. Deriving the upper bound with LEAD(valid_from) was considered and rejected — it makes every conversion a window function over the whole rate table, and it extends the newest rate forward forever, so a stale feed converts at last week's price instead of failing.

A date that no interval covers converts to NULL, and that is deliberate: a miss stays visible, where the nearest neighbouring rate would be a number nobody could tell from a real one.

What the rate table has to guarantee¶

bloomery reads this relation and emits no model for it, so it audits nothing about its contents. Two properties are the operator's to hold:

• Intervals for one (from, to) pair must not overlap. The lookup is a scalar subquery, so an overlapping feed matches more than one rate and the model fails loudly at run time. That is the intended end of the spectrum — every shape that keeps running picks one rate silently.
• A rate must exist for every pair and date you convert. A code that matches nothing converts to NULL, whether it is a typo, a currency you have not loaded, or a gap in the feed. convert refuses a code that is not three uppercase letters, because that much is checkable from the spec alone; whether USD rates were actually loaded is not. Declare a {rule: not_null, on_fail: quarantine} rule on the converted column if a missing rate should stop the run rather than propagate.

Without fx_rates: in the catalog, convert is refused at emit with UnsupportedByTarget naming the column — the transform stays whitelisted and typechecked, and the refusal names the declaration that would lift it.

What this does to CurrencyMismatch¶

Nothing, directly. Two operands with distinct declared ISO-4217 codes still may not meet, and no token waives that. What conversion adds is a third option where there were two: write the converted amount into a field the catalog declares in the target currency, and the arithmetic is same-currency arithmetic, which was never a violation.

The error message names whichever fix is actually available — convert, when the catalog declares rates; declare rates or derive upstream, when it does not.

Custom transforms¶

register_transform(spec) adds an extension transform to a process-global overlay — a deployment-time act, typically an adapter package registering at import. A name collision with any existing transform, built-in or extension, raises TransformRegistrationError: shadowing a vetted transform silently would defeat the whitelist. A TransformSpec declares the name, arity and per-argument kinds, input type domain, output type (a fixed type or a function of input type and args), and a builder that produces a dialect-neutral SQLGlot AST — never string SQL. See the API reference.

That AST is neutral because each dialect renders it, and two of the transforms here need different SQL per engine — to_utc and parse_ts both have engine-specific behaviour worth reading before you pick a warehouse.

Every transform above produces the type it declares, on all three engines, and the same page describes the battery that holds that true. One caveat survives it: divide is inexact on DuckDB, which has no exact decimal division — prefer {multiply: "0.01"} to {divide: 100} there, as the shipped examples do.