Common expression patterns

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

​

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.

​

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:

1. In the Reformat gem, under Target Column, click Select Column.
2. Give the column the name stratify_by_revenue.
3. Click Select expression > Conditional. A WHEN clause appears.

​

Configure the WHEN clause

1. For WHEN, click Select expression > Function.
2. Select Data type cast, which converts a value of one data type into another data type.
3. Select Throw error on failure to ensure the pipeline doesn’t run if the type cast fails.
4. Click Select expression > Column and select ANNUALREVENUE.
5. Click Select data type > Float to convert the column to a Float type.
6. Click Select operator and select less than.
7. Click Select expression > Value and enter 1000000 as the value.

​

Configure the THEN clause

1. For THEN, click Select expression and select Value. Enter Low Revenue as the value.
2. Click + on the next line and select Add CASE to add another WHEN clause.
3. Repeat steps 3 to 8 to set up the rest of the comparison expressions.
4. Click + on the next line and select Add ELSE to add an ELSE statement.
5. 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

​

Set up base filter conditions

To set up the grouping expressions to match the image above:

1. After creating the Filter gem, click Add condition. An option to Select expression appears.
2. Click Select expression > Column.
3. Select TOTAL_EXPECTED_REVENUE from the list.
4. Click Select operator and select is not null.
5. Click + Add Condition to add another condition expression.
6. Click Select expression > Column.
7. Select TOTAL_AMOUNT from the list.
8. Click Select operator and select greater than.
9. Click Select expression > Value.
10. Enter 100000 as the value.

​

Add grouped OR condition

1. Click Add Group. A grouped expression row appears.
2. Click Select expression > Column.
3. Select LATEST_CLOSED_QTR from the list.
4. Click Select operator and select equals.
5. Click Select expression > Value.
6. Enter 2023Q3 as the value.
7. 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.

1. Open your project and select Parameters in the header.
2. Click + Add Parameter.
3. Name the parameter region.
4. Select the Type and choose Array > String.
5. Click Select expression > Value.
6. Type AMER and click Done.
7. Select + to add another string to the array.
8. Type APAC and click Done.
9. Now, click Save.

​

Use the parameter in an expression

Now, you’ll use the parameter in an expression inside a Filter gem.

1. Create and open the Filter gem.
2. Remove the default true expression.
3. Click Select expression > Function and select array_contains.
4. In the array dropdown of the function, click Configuration Variable and select the region parameter.
5. 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.