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]
[HAVING condition]
[QUALIFY condition]
[ORDER BY expressions]
[LIMIT n]
[OFFSET n]

smelt extensions

smelt.ref()

Reference another model in the project:

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

smelt.source()

Reference an external source table defined in sources.yml:

FROM smelt.source('source.table')

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

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)
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY val)

Common Table Expressions

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

Set operations

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

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