Visual expressions reference - Prophecy Documentation

This page contains a reference of the different visual expression builder components, which include the expression options, operator options, and data types.

Expression options

The visual expression builder supports the following expression options:

Option	Description
Column	Select an input column from your source tables. You can view all of the available input columns from the dropdown menu.
Value	Enter any value. If you enter a string value, it’ll be considered as a string within quotes. If you enter a number, it’ll be considered a numerical value, but you can click Check to read value as string. The same applies to boolean values. For example, if you enter `True`, it’ll be considered a boolean unless you select Check to read value as string.
Function	Choose a function from a list of all supported function category groups. The list displays each function’s description, including mandatory arguments. You can optionally add additional arguments if applicable.
Data type cast	Cast a variant column into its appropriate data type. Use `TRY_CAST` to avoid errors. On failure, it sets the value to `null`. Note: For Snowflake, `TRY_CAST` is only supported on string data types.
Conditional	Use a conditional `WHEN` clause. Within `WHEN`, use a comparison expression. Within `THEN`, use a simple expression. You can add multiple `CASES` but only one `ELSE`. `ELSE` also uses a simple expression. You can also add `IF`, `ELSEIF`, or `FOR` conditions. `FOR` uses a variable name and expression value. `IF` and `ELSEIF` are comparisons.
Configuration Variable	Use a variable from the list of your pipeline parameters or project variables.
Incremental	Use advanced dbt configurations.
Custom Code	Write your own custom code for expressions not supported by the visual expression builder. As you type, suggestions will be provided.

Data types

Category	Supported Types
Basic	`Boolean` `String` - String / Varchar `Date & time` - Date / Datetime / Timestamp / Timestamp NTZ `Number` - Integer / Long / Short `Decimal number` - Decimal / Double / Float
Other	`Binary` `Byte` `Char` `Calendar interval` / `Day time interval` / `Year month interval` `Null` `Variant`

Comparison operators

Operator	Description
`equals`	Checks if two values are equal.
`not equals`	Checks if two values are not equal.
`less than`	Checks if a value is less than another.
`less than or equal`	Checks if a value is less than or equal to another.
`greater than`	Checks if a value is greater than another.
`greater than or equal`	Checks if a value is greater than or equal to another.
`between`	Checks if a value lies between two others.

Existence checks

Operator	Description
`is null`	Checks if a value is null.
`is not null`	Checks if a value is not null.
`in`	Checks if a value exists in a given list.
`not in`	Checks if a value does not exist in a list.

Boolean predicates

Type	Predicates
Unary	`Exists` (in subquery) `In` `Is null`
Binary	`Between` `Equality` `Less than` `Less than or equal` `Greater than` `Greater than or equal`
Groups	`Not` `And` `Or`