Use the visual expression builder to create conditional logic, combine multiple filter conditions, build reusable expressions with parameters, and define complex business rules without writing SQL manually.
Common use cases include:
- categorizing records based on conditions
- building multi-condition filters with
AND and OR
- creating derived columns
- handling null values
- using parameters in expressions
- creating reusable business logic across pipelines
This page explains common expression patterns you can create with the visual expression builder.
Common expression examples
| Goal | Example pattern |
|---|
| Categorize records using conditions | WHEN revenue < 1000000 THEN 'Low Revenue' |
| Combine multiple conditions | Amount > 100000 AND Region = 'APAC' |
| Replace null values | COALESCE(region, 'Unknown') |
| Build nested logic | Multiple WHEN clauses with ELSE |
| Filter rows using grouped logic | Nested AND / OR conditions |
| Use runtime values in expressions | Configuration variables and parameters |
Expressions that return values vs conditions
Some expressions return calculated values, while others return true or false.
For example:
- Filter conditions must evaluate to
true or false.
- Reformat expressions can return calculated values such as text, numbers, or dates.
Understanding the expected output type can help prevent validation and runtime errors.
Expression-building features
The following table describes options for the visual expression builder.
| Feature | Description |
|---|
| Comparison | Lets you establish relationships between two simple expressions connected by an operator. This mode helps you perform comparisons or existence checks on your data that always evaluate to true or false. |
| Grouping | Combine multiple comparison expressions into groups using logical operators like AND and OR. This structure enables you to express intricate business logic in a visual format. |
| Parameters | Enables you to include variables in your expressions that may vary at runtime. Parameters only show up in the Configuration Variables of the visual expression builder after you have created them at the pipeline level. |
Examples
Create conditional logic for derived columns
Let’s say you want to stratify accounts based on their annual revenues. Each condition we set up is limited to one comparison. This example combines conditional logic with comparison operators.
Create a new conditional column
To set up the comparison expressions to match the image above:
- In the Reformat gem, under Target Column, click Select Column.
- Give the column the name
stratify_by_revenue.
- Click Select expression > Conditional. A
WHEN clause appears.
- For
WHEN, click Select expression > Function.
- Select Data type cast, which converts a value of one data type into another data type.
- Select Throw error on failure to ensure the pipeline doesn’t run if the type cast fails.
- Click Select expression > Column and select
ANNUALREVENUE.
- Click Select data type > Float to convert the column to a Float type.
- Click Select operator and select
less than.
- Click Select expression > Value and enter
1000000 as the value.
- For
THEN, click Select expression and select Value. Enter Low Revenue as the value.
- Click
+ on the next line and select Add CASE to add another WHEN clause.
- Repeat steps 3 to 8 to set up the rest of the comparison expressions.
- Click
+ on the next line and select Add ELSE to add an ELSE statement.
- Click Select expression and select Value. Enter
Unknown as the value.
This conditional expression will categorize your accounts based on revenue thresholds, making it easier to perform segment-specific analysis and reporting. When the pipeline runs, each account will be assigned to the appropriate revenue category based on the conditions you’ve defined.
Build multi-condition filters with AND/OR logic
When filtering data, you often want the output data to meet multiple criteria. You can use Grouping for this by creating multiple AND and OR statements.
Assume you have a dataset where you want to filter for the following:
- Total expected revenue that
is not null
- Total amounts that are greater than
100000
- Latest closed quarters that equals
2023Q2 or 2024Q2
You can have any number of groups and nestings (a group within a group). You can also always
change the grouping conditions between AND and OR.
Set up base filter conditions
To set up the grouping expressions to match the image above:
- After creating the Filter gem, click Add condition. An option to Select expression appears.
- Click Select expression > Column.
- Select
TOTAL_EXPECTED_REVENUE from the list.
- Click Select operator and select
is not null.
- Click + Add Condition to add another condition expression.
- Click Select expression > Column.
- Select
TOTAL_AMOUNT from the list.
- Click Select operator and select
greater than.
- Click Select expression > Value.
- Enter
100000 as the value.
Add grouped OR condition
- Click Add Group. A grouped expression row appears.
- Click Select expression > Column.
- Select
LATEST_CLOSED_QTR from the list.
- Click Select operator and select
equals.
- Click Select expression > Value.
- Enter
2023Q3 as the value.
- Click + Add Condition and repeat steps 2 to 6 to set up the other
OR condition.
This complex filter will return only high-value opportunities from specific quarters that have valid expected revenue values. By combining AND and OR conditions in this way, you can create precise data subsets that match your exact business requirements.
Create reusable expressions with parameters
When you use a pipeline parameter in a visual expression, you can manipulate the value of that parameter using different configs at runtime. Let’s review an example that leverages an array parameter in a Filter gem.
Imagine that you want to filter an Orders dataset based on the region where the order was placed. Specifically, you only want to keep rows where the region is included in the array parameter.
Create an array parameter
First, you’ll set up a region parameter, which will be an array of strings that includes a subset of regions.
- Open your project and select Parameters in the header.
- Click + Add Parameter.
- Name the parameter
region.
- Select the Type and choose Array > String.
- Click Select expression > Value.
- Type
AMER and click Done.
- Select
+ to add another string to the array.
- Type
APAC and click Done.
- Now, click Save.
Use the parameter in an expression
Now, you’ll use the parameter in an expression inside a Filter gem.
- Create and open the Filter gem.
- Remove the default
true expression.
- Click Select expression > Function and select
array_contains.
- In the array dropdown of the function, click Configuration Variable and select the
region parameter.
- In the value dropdown of the function, click Column and select the order region column.
The output of this gem will only include rows where the order region matches at least one value in the region array. When you run the pipeline interactively, it will use the values of the default array that you set up in the previous section.
Validate your expressions
Run the pipeline up to and including the gem with your expression, and observe the resulting data sample. To do so, click the play button on either the canvas or the gem. Once the code has finished running, you can verify the results to make sure they match your expectations. You can explore the result of your gem in the Data Explorer.
Common issues
Filter expressions failing validation
Filter conditions must evaluate to true or false.
For example:
Amount > 1000 is valid.CASE WHEN Amount > 1000 THEN 'High' END is not valid as a filter condition because it returns text instead of a boolean value.
Use conditional expressions to create derived columns in gems like Reformat, and use boolean comparisons in Filter gems.
Conditional logic not returning expected results
Verify that:
- conditions are evaluated in the expected order.
- grouped
AND and OR conditions are structured correctly.
- all possible cases are handled with an
ELSE condition when appropriate.
Null values causing unexpected behavior
Some expressions return NULL when input values are NULL.
Use functions such as COALESCE() to provide default values when needed.
Expression validation errors
Validation errors can occur when:
- data types do not match.
- required function arguments are missing.
- column references are incorrect.
- expressions return an unexpected value type.
Nested field references not resolving correctly
When working with nested or structured data, verify that:
- the correct field path is selected.
- the referenced field exists in the input schema.
- the expression uses the expected nested structure.
Multi-condition filters returning unexpected rows
When combining AND and OR conditions:
- use grouping to control evaluation order.
- verify that conditions are nested correctly.
- test expressions incrementally to confirm the output.
Tips
Here are some additional tips to keep in mind when using the visual expression builder:
- The expression dropdowns support search.
- Each argument of your function is another expression since you have the same expression options to choose from.
- You can drag and drop your comparison expressions to rearrange them.
- Just as with conditions, you can also drag and drop your grouping expressions to rearrange them.
- You can delete individual expressions, conditions, and groupings by clicking the trash icon at the end of the rows.
