Expression language · Trinium ETL docs

Browse the docs

Expression language

Wherever Trinium asks for a rule or a calculation, you type the same thing: a SQL expression. One dialect, the same in every node, whatever the data came from.

The rules

Wrap column names in [brackets]

Always write a column name inside square brackets: [unit price], [STATUS]. Names are matched exactly as they appear, so [STATUS] and [status] are different columns; the Columns menu inserts the exact name for you. (Bare price and quoted "unit price" also work, but brackets are the Trinium standard.)

Text goes in single quotes

Use 'Austin' for text values. Double quotes mean a column name, not a piece of text.

Conditionals use CASE

CASE WHEN [amount] > 100 THEN 'big' ELSE 'small' END is SQL's IF / THEN / ELSE.

Equality is a single =

Write [status] = 'active' (one equals sign), and combine tests with AND / OR / NOT.

One dialect, everywhere

The same expression syntax runs identically whether your data came from a CSV, Postgres, or S3. (A source node's own SQL query, e.g. Postgres, still uses that database's dialect.)

The clock is the instance timezone

now() and current_date read the timezone set in Settings (the same clock that fires your schedules), so to_char(now() - INTERVAL '62 days', '%Y%m%d') is 62 days ago on your calendar, not UTC's.

The run's day: ${date}

${date} is the day this run is for, and you can type it into any field: an expression, a source query, a file path. ${date-1} is the day before, ${date:%Y%m%d} the same day as 20261001. Every node and every step of a flow sees the same day, even across midnight. In an expression write it as a date: [order_date] = DATE '${date-1}'. current_date is the moment that node runs instead. Also available: ${datetime} and ${run_id}.

How do I…?

Common tasks, each with the node that does it and the exact text to type.

Task	Where	What to type
Days between two dates	Formula	`[ship_date] - [order_date]` Whole days, for date columns. For date-times: date_part('day', [end_at] - [start_at]).
Rank rows	Multi-Row Formula	`RANK() OVER (ORDER BY [amount] DESC)` Ties share a rank. Add PARTITION BY [region] to rank within each group.
Running total	Multi-Row Formula	`SUM([amount]) OVER (ORDER BY _row)` _row is the order the rows arrived in.
Running balance that never goes below zero	Multi-Row Formula	`GREATEST(0, COALESCE(_prev, 0) + [received] - [shipped])` Set Each row reads to the previous row's result: _prev is the row above's answer, empty on the first row.
Split into deciles	Multi-Row Formula	`NTILE(10) OVER (ORDER BY [score])` Equal-sized bands numbered 1 to 10; NTILE(4) gives quartiles.
90th percentile per group	Summarize	`Group by: region · Percentile of amount, 90` 50 is the median.
Count the rows	Summarize	`Leave Group by empty · Count` One row holding the total.
Mask an email but keep it joinable	Formula	`encode(sha256(lower([email])), 'hex')` The same address always gives the same value.
Catch near-duplicate spellings	Filter	`levenshtein(lower([vendor]), lower([supplier])) <= 2` Two edits or fewer. Fuzzy Match does this across whole tables.

Functions

Every function the expression editor offers, with an example of each, is on the functions page.