Skip to content

Filter

Filter rows from the input to pass a subset of rows to the next component based on a set of conditions.

The function of this component is similar to the Data Filter property found in data staging (Query) orchestration components.

Matillion ETL for Delta Lake on Databricks users may experience errors if using binary values. It is currently impossible to filter against a binary value with this component.

For additional documentation on filter conditions, refer to:

Properties

Name = string

A human-readable name for the component.

Filter Conditions = column editor

  • Input Column: Select an input column. The available input columns vary depending upon the data source.
  • Qualifier:
    • Is: Compares the column to the value using the comparator.
    • Not: Reverses the effect of the comparison, so "Equals" becomes "Not equals", "Less than" becomes "Greater than or equal to", etc.
  • Comparator: Choose a method of comparing the column to the value. Possible comparators include:
    • 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.
    • ILike: Case-insensitive match of the value in the Input Column and the pattern in the Value Column.
    • 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 Column: The value entered by the user to use for comparison. If this value is enclosed in double quotes, then it will be treated as a column in the generated SQL, which will allow for comparing columns.

Combine Conditions = drop-down

When multiple Filter Conditions are present, they can be separated by AND or OR. AND means all the Filter Conditions must be true, OR means any of the Filter Conditions must be true.

For more complex conditions involving grouping several AND and OR conditions together, consider creating a new field using a Calculator component that specifies your complex condition and then use that new field in a simple Filter Condition.

Strategy

Generates a WHERE clause.

Quoting Date/Time values in Snowflake

Values representing a constant date/time need to be single-quoted so that they are recognised by Snowflake. Matillion ETL recognises the following formats, and will quote them 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 recognised 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 Supported Formats for AUTO Detection for a complete list of what Snowflake will recognize automatically.

Functions and column references can also be used in this field. Values such as below will work as expected:

  • CURRENT_TIMESTAMP()
  • "anotherDateColumn" + 365

Video

Snowflake Delta Lake on Databricks Amazon Redshift Google BigQuery Azure Synapse Analytics
✅ ✅ ✅ ✅ ✅
© Copyright 2024 Matillion Ltd | Legal | Privacy Policy | Disclaimer | Modern Slavery Statement