Skip to main content
The Filter transformation component lets you filter a dataset and return rows according to criteria of your choice. You can combine multiple filter criteria using AND or OR logic. This component is equivalent to writing a SELECT WHERE query.

​
Use case

This component can be used to narrow down the data you are processing, so you can focus on what is most relevant. For example, you can use it to:
  • Filter a financial dataset by transaction value to see your highest-value orders.
  • Filter recent customer interactions by date to analyze customer behavior within the past 30 days.
  • Filter customer data by different criteria so you can target different customer groups with tailored ads.

​
Properties

​
Name
string
required
A human-readable name for the component.
​
Mode
drop-down
Select how you will configure the filter:
  • Basic: In this mode, you specify filter conditions using the dialog box in the Filter conditions property.
  • Advanced: In this mode, you write a custom SQL expression in the Filter clause property.
​
Filter conditions
expression editor
required
Only used in Basic mode.Input Column: Choose a column from your input table that you will apply this filter to.Qualifier: Select whether matching rows in the data should be included (Is, the default) or excluded (Not).Comparator: Select from:
  • Less than: (default) Value in the Input Column must be less than that specified in the Value Column.
  • Less than or equal to: Value in the Input Column must be less than or equal to that specified in the Value Column.
  • Equal to: Value in the Input Column must be equal to that specified in the Value Column.
  • Greater than (default): Value in the Input Column must be greater than that specified in the Value Column.
  • Greater than or equal to: Value in the Input Column must be greater than or equal to that specified in the Value Column.
  • Like: Case-sensitive match of the value in the Input Column and the pattern in the Value Column. Read the following documentation:
  • ILike: Case-insensitive match of the value in the Input Column and the pattern in the Value Column. Read the following documentation:
  • Similar to: Matches the value in the Input Column with a SQL standard regular expression pattern in the Value column.
  • Null: Checks whether the value of the Input Column is the SQL “null” value.
  • Blank: Checks whether the value of the Input Column is an empty string.
  • Null or blank: Checks whether the value of the Input Column is an empty string or the SQL “null” value.
Value: The value entered by the user to use for comparison. If this value is enclosed in double quotes, it will be treated as a column in the generated SQL, which will allow for comparing columns.
​
Combine condition
drop-down
required
Only used in Basic mode.When multiple filter conditions are present, they can be separated by AND or OR.
  • AND: All the filter conditions must be true.
  • OR: Any of the filter conditions must be true.
For more complex conditions involving grouping several AND and OR conditions together, you can use one of the following options:
  • Use Advanced mode to write a custom SQL expression that combines conditions in any way you like.
  • Create a new field using a Calculator component that specifies your complex condition, and then use that new field in a filter condition.
​
Filter clause
expression editor
required
Only used in Advanced mode.Write an SQL WHERE clause to specify the filter condition. For example, you could write "score" BETWEEN 70 AND 80 to filter for rows where a numeric score value is between 70 and 80. Do not include the WHERE keyword in this expression, just the clause that would come after it.You can use the Maia prompt to help you write the SQL expression for this property by describing the required output in natural language. For example, you could write “Filter for rows where score is between 70 and 80” to get the above example expression.

​
Working with dates

Values representing a constant date/time need to be single-quoted so that they are recognized by Snowflake. The following formats are recognized by the Filter component, and will be quoted automatically:
  • yyyy-MM-dd
  • yyyy-MM-dd HH:mm:ss
  • yyyy-MM-dd HH:mm:ss +HH:mm
  • yyyy-MM-dd HH:mm:ss.SSSSSS
  • yyyy-MM-dd hh:mm:ss.SSSSSS +HH:mm
  • yyyy-MM-ddThh:mm:ss.SSSSSSZ
Other date/time formats recognized by Snowflake will work if they are single-quoted in the Filter property, such as RFC format ‘Thu, 21 Dec 2000 04:01:07 PM’. Read the following documentation for a complete list of what the following cloud platforms will recognize automatically: Functions and column references can also be used in this component. Values such as the following will work as expected:
  • CURRENT_TIMESTAMP()
  • “anotherDateColumn” + 365

​
Examples

Input Data

+-------------------+------+-------+------------+
|       EMAIL       | EXAM | SCORE |    DATE    |
+-------------------+------+-------+------------+
| tgf@gmail.com     | math |    66 | 2022-02-24 |
| toml@gmail.com    | math |    78 | 2022-01-25 |
| jenny1@gmail.com  | chem |    44 | 2021-11-05 |
| lucygh@aol.com    | chem |    70 | 2022-02-13 |
| suecool@gmail.com | math |    89 | 2022-01-29 |
+-------------------+------+-------+------------+
Finding all passing gradesWe can use “Greater than” comparators on integer type columns to find values greater than a given value.Filter component properties:
  • Filter Conditions:
    • Input Column: SCORE
    • Qualifier: Is
    • Comparator: Greater than or equal to
    • Value: 70
  • Combine Conditions: (We have a single filter so this doesn’t matter)
Output Data

+-------------------+------+-------+------------+
|       EMAIL       | EXAM | SCORE |    DATE    |
+-------------------+------+-------+------------+
| toml@gmail.com    | math |    78 | 2022-01-25 |
| lucygh@aol.com    | chem |    70 | 2022-02-13 |
| suecool@gmail.com | math |    89 | 2022-01-29 |
+-------------------+------+-------+------------+