Expression language reference¶
Expecto's expression language is how you write validation rules on an Entity and, in DuckDB form, mappings on a Transformer. Expressions are parsed into a typed AST (via SQLGlot), checked against a function allowlist, and compiled to DuckDB SQL — never string-interpolated. Anything outside the allowlist is rejected when you save, not at runtime.
Field references¶
| Reference | Means |
|---|---|
d.field_name |
A field on the data row being validated. |
c.context_field |
A value from the session context (injected per delivery). |
In transformer mappings, source columns are written as quoted identifiers that
match the supplier's header exactly, e.g. "Konto", "Betrag".
Operators¶
| Group | Operators |
|---|---|
| Comparison | ==, !=, >, <, >=, <= |
| Logical | and, or, not |
Functions¶
The allowlist groups by purpose. File-system and environment functions are blocked by design.
| Group | Functions (representative) |
|---|---|
| String | TRIM, LPAD, RPAD, REPLACE, len() |
| Matching | matches() (regex), in() |
| Null handling | is_empty(), nz(), COALESCE, NULLIF |
| Math | abs(), round() |
| Type | CAST, TRY_CAST |
| Date | STRFTIME, STRPTIME |
Rule examples¶
A rule passes when its expression is true for a row. The compiler turns it into a
violation-finding query of the form SELECT ... WHERE NOT (expression).
# Konto must be exactly 6 digits
len(d.konto) == 6 and matches(d.konto, '^[0-9]+$')
# Amount must not be zero
abs(d.amount) > 0
# Cost centre is required only for expense accounts (precondition + check)
not is_empty(d.cost_center)
Each rule also carries a severity (ERROR, WARNING, INFO), optional
preconditions, and localized messages. See
Business Rules for how severity drives blocking
and when a rule resolves to not_applicable.
Mapping examples (DuckDB form)¶
Transformer mappings are DuckDB expressions over the source columns:
LPAD(TRIM("Konto"), 6, '0') -- normalize account number
CAST(REPLACE("Betrag", ',', '.') AS DECIMAL(18,4)) -- German decimal → DECIMAL
STRPTIME("Belegdatum", '%d.%m.%Y') -- parse a date
(SELECT "name" FROM dict_kontenplan WHERE "code" = "Konto") -- catalog lookup
Safety model¶
- Parsed to a typed AST; no user input is concatenated into SQL.
- Validated against the function allowlist; DDL/DML is rejected at parse time.
- Quoted identifiers are escaped before use as DuckDB identifiers.