Language Reference

smelt SQL is a logical SQL superset — PostgreSQL base with cherry-picked features from DuckDB and Spark. Models are compiled to target-specific SQL for execution.

SELECT statement

Standard SQL SELECT with all common clauses:

SELECT [DISTINCT] columns
FROM table_references
[WHERE condition]
[GROUP BY expressions | GROUP BY ALL]
[HAVING condition]
[QUALIFY condition]
[ORDER BY expressions | ORDER BY ALL [ASC | DESC] [NULLS FIRST | NULLS LAST]]
[LIMIT n]
[OFFSET n]

ORDER BY ALL orders by every select-list item, left to right; an optional direction and NULLS placement apply to the whole ordering (ORDER BY ALL DESC).

A model file contains at most one query body. Any content after it — a second SELECT, stray tokens, or the tail of an unsupported construct — is an error, surfaced as a trailing-top-level-content diagnostic; it is never silently ignored.

smelt extensions

smelt.<path>

Reference another model in the project:

FROM smelt.model_name
FROM smelt.model_name(filter => condition, limit => n)

smelt.sources

Reference an external source table declared as a per-entity .yml under paths::

FROM smelt.sources.source.table

smelt.define — user-defined functions

Declare a reusable SQL fragment with optional type annotations. Files live in functions/.

-- Tier 1 (unannotated)
smelt.define add_one(x) AS (x + 1)

-- Tier 2 (parameters annotated)
smelt.define safe_divide(
  numerator: Expr<Numeric>,
  denominator: Expr<Numeric>
) AS (
  CASE WHEN denominator = 0 OR denominator IS NULL
    THEN NULL
    ELSE CAST(numerator AS DOUBLE) / CAST(denominator AS DOUBLE)
  END
)

-- Tier 3 (fully annotated, return type verified)
smelt.define safe_divide(
  numerator: Expr<Numeric>,
  denominator: Expr<Numeric>
) -> Expr<Double> AS (
  CASE WHEN denominator = 0 OR denominator IS NULL
    THEN NULL
    ELSE CAST(numerator AS DOUBLE) / CAST(denominator AS DOUBLE)
  END
)

See the Functions guide for the full type annotation language, fragment sorts (TableExpr, SelectItems), and PASSING clauses.

smelt.functions.* — calling user-defined functions

-- Positional arguments
SELECT smelt.functions.safe_divide(revenue, cost) AS margin FROM smelt.orders

-- Named arguments
SELECT * FROM smelt.functions.sessionize(
  smelt.events,
  user_col => user_id,
  ts_col   => event_time
)

-- PASSING clause for fragment parameters
SELECT *
FROM smelt.functions.session_rollup(smelt.events, user_id, event_time)
PASSING metrics AS (COUNT(*) AS events, SUM(amount) AS total)

-- Struct spread: project all fields of an Expr<Struct<{...}>> return as columns
SELECT smelt.functions.parse_event_payload(payload).*
FROM smelt.sources.raw.events

When a function declares -> Expr<Struct<{field1: Type1, field2: Type2, …}>>, the .* suffix expands the struct fields into individual columns in the model's output schema. Each field becomes a separately named column with its declared type. This expansion is visible to downstream models and the LSP — hover, diagnostics, and completions all reflect the struct's declared fields.

Row-polymorphic functions (Struct<{…, ..r}>) expand declared fields plus any extras bound from the call-site argument's schema.

An unrecognized type name in any struct field position of a function annotation is an InvalidFunctionTypeRef error anchored at the declaration. For example, -> Expr<Struct<{a: Integer, b: Bogus}>> where Bogus is not a known type emits InvalidFunctionTypeRef at the return-type annotation. The error fires at the declaration so that callers projecting the struct's fields observe the resulting Unknown column as a downstream consequence rather than receiving a separate call-site diagnostic.

smelt.extern — external function declarations

Declare a backend-native function so smelt can type-check call sites:

smelt.extern regex_match(
  text: Expr<Text>,
  pattern: Expr<Text>
) -> Expr<Boolean>

smelt.as_struct() — struct packing

Bundle columns from a table alias into a struct value:

SELECT
  smelt.as_struct(o EXCEPT customer_id) AS order_data,
  smelt.as_struct(c EXCEPT customer_id) AS customer_data
FROM orders AS o
JOIN customers AS c ON o.customer_id = c.customer_id

JOIN syntax

All standard JOIN types are supported:

FROM a
INNER JOIN b ON a.id = b.id
LEFT JOIN c USING (id)
RIGHT JOIN d ON a.key = d.key
FULL OUTER JOIN e ON a.id = e.id
CROSS JOIN f

Comma-separated FROM items (FROM a, b) are also supported and are equivalent to CROSS JOIN:

FROM a, b
WHERE a.id = b.id

Window functions

SUM(amount) OVER (PARTITION BY user_id ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
ROW_NUMBER() OVER (PARTITION BY group_col ORDER BY sort_col)
LAG(value, 1) OVER (ORDER BY date)
LAST_VALUE(value IGNORE NULLS) OVER (ORDER BY date)
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY val)

An IGNORE NULLS or RESPECT NULLS modifier may follow a function's arguments inside the parentheses (LAST_VALUE(value IGNORE NULLS)). IGNORE NULLS skips NULL inputs when picking a value; RESPECT NULLS (the default) keeps them.

Common Table Expressions

WITH
  cte1 AS (SELECT ...),
  cte2 AS (SELECT ... FROM cte1)
SELECT * FROM cte2

An optional column list after the CTE name rebinds the inner SELECT's column types to the declared names, positionally:

-- Inner SELECT columns are renamed to (a, b) while keeping their types
WITH cte(a, b) AS (SELECT CAST(1 AS INTEGER), CAST(2.0 AS DOUBLE))
SELECT a, b FROM cte
-- a: Integer, b: Double

When the column list is omitted, the inner SELECT's own aliases are used unchanged.

When the declared column count does not match the inner SELECT's actual column count, smelt emits AliasColumnArityMismatch anchored at the column-list span. Alias names are applied positionally up to whichever list is shorter; any remaining columns retain their inferred names:

-- Error: alias list has 1 name but SELECT produces 2 columns
WITH cte(a) AS (SELECT CAST(1 AS INTEGER), CAST(2 AS INTEGER))
SELECT a FROM cte
-- AliasColumnArityMismatch at (a)

Set operations

SELECT ... UNION ALL SELECT ...
SELECT ... INTERSECT SELECT ...
SELECT ... EXCEPT SELECT ...

GROUP BY extensions

GROUP BY ALL
GROUP BY CUBE(a, b)
GROUP BY ROLLUP(a, b)
GROUP BY GROUPING SETS ((a, b), (a), ())

GROUP BY ALL groups by every select-list item that is not an aggregate, so the grouping keys never drift out of sync with the projection as it evolves. With no non-aggregate items it degenerates to a single-group (whole-table) aggregation, exactly as writing out the grouping keys by hand would.

Labelling rollup rows

CUBE, ROLLUP, and GROUPING SETS produce extra "subtotal" rows where the grouped-out columns are returned as NULL. Use GROUPING() to detect those rollup rows and label them with a sentinel value:

SELECT
  CASE WHEN GROUPING(category) = 1 THEN 'ALL' ELSE category END AS category,
  CASE WHEN GROUPING(region)   = 1 THEN 'ALL' ELSE region   END AS region,
  SUM(amount) AS total
FROM smelt.sales
GROUP BY CUBE(category, region)

Pitfall: do not use COALESCE(col, 'ALL') for rollup labels

COALESCE(category, 'ALL') looks like a shorter way to write the same thing, but it is wrong whenever category is nullable. A real NULL in the source data and a CUBE-rolled-up NULL both collapse to 'ALL', producing two rows that look like the grand total but are actually different aggregations. GROUPING(col) = 1 distinguishes "this column was rolled up by CUBE" from "this column happens to be NULL in the data", so real NULLs stay as NULL (or can be labelled separately) and only true rollup rows get the sentinel.

COALESCE(col, 'ALL') is only safe when col is declared NOT NULL at the source.

Subqueries

-- Scalar subquery
SELECT (SELECT MAX(amount) FROM orders) as max_amount

-- EXISTS
WHERE EXISTS (SELECT 1 FROM orders WHERE orders.user_id = users.id)

-- IN subquery
WHERE user_id IN (SELECT user_id FROM active_users)

VALUES-derived tables

A VALUES clause in a derived-table position produces a typed schema. smelt infers each column's type as the least upper bound (LUB) of the corresponding elements across all rows, following the numeric promotion chain (SmallInt < Integer < BigInt < Decimal < Double):

-- Alias column list provides names; types are inferred from the rows
SELECT id, region, created_at
FROM (
    VALUES
        (1, 'us-west-2', CAST('2024-01-01' AS TIMESTAMP)),
        (2, 'eu-west-1', CAST('2024-01-02' AS TIMESTAMP))
) AS t(id, region, created_at)
-- id: SMALLINT, region: TEXT, created_at: TIMESTAMP

-- Multi-row promotion: Integer + Double → Double
SELECT x FROM (VALUES (CAST(1 AS INTEGER)), (CAST(2.0 AS DOUBLE))) AS t(x)
-- x: Double

-- Without an alias column list, columns are named col1, col2, …
SELECT col1, col2 FROM (VALUES (1, 2)) AS t

When the alias column list has a different length from the number of VALUES columns, smelt emits AliasColumnArityMismatch anchored at the column-list span:

-- Error: alias list has 1 name but VALUES produces 2 columns per row
SELECT a FROM (VALUES (CAST(1 AS INTEGER), CAST(2 AS INTEGER))) AS t(a)
-- AliasColumnArityMismatch at (a)

Type casting

CAST(x AS INTEGER)
x::INTEGER          -- PostgreSQL-style
TRY_CAST(x AS DATE) -- Returns NULL on failure

CAST/:: pass NULL through from the input but never introduce it, so the result is non-nullable when the input is. TRY_CAST instead returns NULL on any failed conversion, so its result type is the target type but is always nullable, regardless of the input.

Numeric literal forms

smelt accepts plain integer and decimal literals (1, 1.5), including scientific notation (1e8, 1.5e-3). Underscore digit separators are accepted anywhere a run of digits appears — the integer part, the fractional part, and the exponent digits — as long as each underscore sits strictly between two digits: 1_000_000, 1_000.000_1, and 1_000_000.5_00e1_0 all lex as a single numeric literal. A leading, trailing, or doubled underscore (_1, 1_, 1__0) is rejected as a parse error rather than being silently reinterpreted as a shorter literal plus an alias.

A numeric literal immediately followed by letters with no separating space — 0x1F — is not accepted as a single literal; it produces a parse error rather than being silently reinterpreted (e.g. as 0 implicitly aliased to x1F). Write a space before an intended alias (1 x) or drop the hex-prefix form.

E'...' (escape string) and B'...' (bit-string-shaped) prefixed string literals lex as ordinary string literals.

Dollar-quoted string literals — $$...$$ and tagged $tag$...$tag$ (tag: a letter or underscore followed by letters, digits, or underscores) — also lex as ordinary string literals and infer as Text. The body needs no escaping: embedded single quotes are content, and a $$ inside a tagged body is content too (only the exact matching closing delimiter ends the string). An unterminated dollar-quote is a parse error rather than being silently split into smaller tokens.

Date/time extraction

EXTRACT(EPOCH FROM timestamp_col)   -- returns DOUBLE (Unix timestamp)
EXTRACT(YEAR FROM date_col)          -- returns BIGINT
EXTRACT(MONTH FROM timestamp_col)    -- returns BIGINT
EXTRACT(DAY FROM date_col)           -- returns BIGINT
EXTRACT(HOUR FROM timestamp_col)     -- returns BIGINT
EXTRACT(MINUTE FROM timestamp_col)   -- returns BIGINT
EXTRACT(SECOND FROM timestamp_col)   -- returns BIGINT
EXTRACT(DOW FROM date_col)           -- day of week, returns BIGINT
EXTRACT(DOY FROM date_col)           -- day of year, returns BIGINT
EXTRACT(QUARTER FROM date_col)       -- returns BIGINT
EXTRACT(WEEK FROM date_col)          -- returns BIGINT

EXTRACT(EPOCH FROM ...) returns a DOUBLE (floating-point Unix timestamp). All other fields return BIGINT.

Timezone conversion

AT TIME ZONE converts between naive (TIMESTAMP) and timezone-aware (TIMESTAMP WITH TIME ZONE) values:

ts AT TIME ZONE 'UTC'                -- TIMESTAMP -> TIMESTAMP WITH TIME ZONE
tstz AT TIME ZONE 'America/New_York' -- TIMESTAMP WITH TIME ZONE -> TIMESTAMP

The result type depends only on the operand's tz-awareness, not the timezone name:

• A naive TIMESTAMP operand attaches the given timezone, producing TIMESTAMP WITH TIME ZONE.
• A TIMESTAMP WITH TIME ZONE operand converts to that timezone's local wall-clock time and drops the offset, producing a naive TIMESTAMP.

Because each application flips tz-awareness, the operator chains: ts AT TIME ZONE 'UTC' AT TIME ZONE 'America/New_York' first attaches UTC (producing TIMESTAMP WITH TIME ZONE), then converts to America/New_York local time and drops the offset again (producing TIMESTAMP). AT TIME ZONE binds tighter than comparison and arithmetic operators, so ts AT TIME ZONE 'UTC' > ts2 and ts AT TIME ZONE 'UTC' + INTERVAL 1 HOUR both apply the conversion before the outer operator. Nullability propagates from the operand: a NULL timestamp produces a NULL result.

SQL-standard string function forms

TRIM, SUBSTRING, and POSITION accept both the ordinary comma-separated call form and the SQL-standard keyword-argument form:

TRIM(x)                              -- ordinary form
TRIM(x, chars)                       -- ordinary form
TRIM(BOTH chars FROM x)              -- SQL-standard form
TRIM(LEADING chars FROM x)
TRIM(TRAILING chars FROM x)
TRIM(BOTH FROM x)                    -- modifier without explicit chars
TRIM(FROM x)                         -- no modifier at all

SUBSTRING(x, start)                  -- ordinary form
SUBSTRING(x, start, length)          -- ordinary form
SUBSTRING(x FROM start)              -- SQL-standard form
SUBSTRING(x FROM start FOR length)
SUBSTRING(x FOR length)              -- start implied as 1

POSITION(sub IN x)                   -- SQL-standard form only — POSITION has
                                      -- no comma-separated equivalent; use
                                      -- STRPOS(x, sub) for that

Both forms produce the same result type: TRIM/SUBSTRING return VARCHAR; POSITION returns BIGINT (a 1-based match offset).

Aggregate result types

smelt assigns canonical return types to aggregates so the same model writes the same output schema on every backend — SUM(integer) gives you BIGINT whether you target DuckDB or PostgreSQL, even though the engines disagree natively. Knowing the exact widening rules matters when a downstream column or test expects a specific type; COUNT(*) is a frequent surprise because it returns BIGINT rather than INTEGER.

Aggregate	Argument type	Result type	Nullable
COUNT(*), COUNT(expr)	any	BIGINT	no
SUM(x)	SMALLINT, INTEGER, BIGINT	BIGINT	yes
SUM(x)	FLOAT, DOUBLE	DOUBLE	yes
SUM(x)	DECIMAL(p, s)	DECIMAL(38, s)	yes
AVG(x)	any numeric	DOUBLE	yes
MIN(x), MAX(x)	any	same as x	yes

Notes

• SUM(DECIMAL) widens precision to 38. Real pipelines that accumulate ~1e6 rows of DECIMAL(10, 2) overflow precision 10 quickly; smelt mirrors DuckDB's widen-to-38 to avoid silent corruption. Scale is preserved.

• COUNT is non-null; everything else is nullable. Other aggregates can return NULL when the input group is empty (common with LEFT JOIN-fed GROUP BY). To substitute a default, wrap in COALESCE:

  SELECT
    c.customer_id,
    COALESCE(SUM(o.amount), 0) AS lifetime_spend
  FROM smelt.customers AS c
  LEFT JOIN smelt.orders AS o USING (customer_id)
  GROUP BY c.customer_id
  

• Cast COUNT if a downstream column expects INTEGER. CAST(COUNT(*) AS INTEGER) is safe up to 2^31 - 1 rows; above that, leave it as BIGINT.

See docs/specs/types.md §5 for the normative rules and docs/type_semantics.md for backend divergence notes.

String collation

smelt supports the COLLATE clause for explicit string collation: expr COLLATE collation_name.

In portable models (no engine: declaration), only the binary collation is allowed. Binary collation names are case-insensitive and accepted on all target backends:

Collation name	Notes
"C" or POSIX	ISO/ANSI byte-order comparison (PostgreSQL/DuckDB convention)
BINARY	DuckDB default byte-order comparison
UTF8_BINARY	Spark default byte-order comparison

Binary collation is a no-op for type inference: expr COLLATE "C" returns the same type as expr.

-- ok: binary collation is portable
SELECT name COLLATE "C" AS sorted_name FROM t
SELECT name COLLATE BINARY AS sorted_name FROM t

Using any non-binary collation (case-insensitive, locale-aware, accent-insensitive) in a portable model is a NonPortableCollation error. The comparison degrades to Unknown type to prevent silent cross-engine divergence:

-- error: non-portable collation — use COLLATE "C" or remove the clause
SELECT name COLLATE NOCASE AS sorted_name FROM t

To use a non-binary collation, declare an engine on the model so smelt can emit engine-specific SQL.

Binary string comparisons and grouping are stable across all target engines. Under binary (byte-wise) collation, the following operations produce identical results on DuckDB, Spark, and PostgreSQL regardless of the database's locale setting:

• Equality and ordering (=, <, <=, >, >=)
• GROUP BY and DISTINCT on string columns
• ORDER BY on string columns
• MIN and MAX over string columns

This means a portable smelt model that groups, sorts, or deduplicates strings produces the same rows in the same order on every engine — no cross-engine divergence, no silent locale differences.

Multi-dialect features

These features are parsed in smelt SQL and rewritten to target-specific syntax:

• QUALIFY — window function filtering (DuckDB/Spark origin)
• Lambda expressions — x -> x + 1 for array functions
• PIVOT / UNPIVOT — table rotation
• Array subscript — arr[1] notation
• DATE literals — DATE '2024-01-01' normalization
• Pattern matching operators — LIKE, ILIKE (case-insensitive LIKE), and GLOB (DuckDB glob-style pattern matching, e.g. name GLOB 'x*') each infer Boolean. NOT GLOB is not supported — DuckDB itself rejects that form.
• MAP literals — MAP {'a': 1, 'b': 2} (DuckDB's brace map-literal syntax) infers Map(key_type, value_type), unifying key types and value types independently across entries the same way ARRAY[…] element types unify. An empty MAP {} infers Map(Unknown, Unknown). MAP is not a reserved word — MAP(a, b)-style function calls and a column literally named map both continue to parse as before; only MAP immediately followed by { is treated as a map literal.
• List comprehensions — [expr FOR x IN list], optionally filtered with [expr FOR x IN list IF cond] (DuckDB syntax), builds a new list by evaluating expr once per element of list, keeping only the elements that satisfy IF cond when present. Comprehensions can nest (the source list, or the element expression, may itself be a comprehension). Exactly one FOR clause is accepted per [...] — chained FOR x IN a FOR y IN b is a syntax error, matching DuckDB. The result always infers as Array<T>: when expr is exactly the loop variable ([x FOR x IN list]), T is the source list's element type; for any other expr, T infers as Unknown.