Stream Selector

The Stream Selector processor passes rows to different output streams based on conditions. Define a condition for each output stream that you want to use. All rows that do not match a condition pass to a default stream.

When you define a condition, the Stream Selector processor creates an output stream that you connect to downstream stages. The processor passes a row to all streams where it matches the condition.

For example, say you create the following conditions: 
Stream 1: State like 'CA'
Stream 2: ID is NULL

When you run the pipeline, rows from California pass to Stream 1, rows with missing IDs pass to Stream 2, and any rows from California with a missing ID pass to both streams. Any rows that do not match either condition pass to the default stream.

Use the Stream Selector when you want to create two or more output streams for processing. If you only want to process data that matches one condition, you can use the Filter processor.

Default Stream

The Stream Selector processor passes all data that does not match a condition to a default stream.

When you configure a Stream Selector processor, you connect each output stream to downstream stages in the pipeline. The default stream is the last output stream in the processor.

You can configure the branch for the default stream to perform additional processing or to write directly to a destination. If you do not need to process data from the default stream, you can connect the stream to a Trash destination or use the Filter processor instead.

Stream Selector Conditions

The Stream Selector condition determines the data that the processor passes to the associated output stream. The condition must evaluate to true or false. Rows that evaluate to true pass to the output stream.

You can use a condition as simple as ID is NOT NULL or you can create as complex a condition as needed.

Here are some guidelines for conditions:
  • When you define a condition, you typically base it on column values in the row.

    For information about referencing columns in the condition, see Referencing Columns in Snowflake SQL Expressions.

  • You can use any syntax that can be used in the WHERE clause of a query, including functions such as isnull or trim and operators such as = or <=.

    For more information about the WHERE clause, see the Snowflake documentation.

  • Do not include WHERE in the condition.

  • You can also use user-defined functions (UDFs) in the condition.

For example, the following condition passes rows to the stream only when the year of the transaction date is 2020 or later:
year(transaction_date) >= 2020

Sample Conditions

The following table lists some common scenarios that you might adapt for your use:
Condition Example Description
total > 0 If the value in the total column is greater than 0, the row passes to the stream.
total <= 0 If the value in the total column is less than or equal to 0, the row passes to the stream.
accountId is NULL If the row has a null value in the accountId column, the row passes to the stream.

Note that NULL is not case sensitive. You can use null or Null in the condition.
upper(message) like '%ERROR%' If the message column contains the string, ERROR, the row passes to the stream.

The condition changes the strings in the message column to uppercase before performing the evaluation. This allows the condition to also apply to error and Error, for example.
initcap(country) like 'China' OR initcap(country) like 'Japan' If the value in the country column is China or Japan, the row passes to the stream.

The condition changes the strings in the country column to capitalize the first letter before performing the evaluation. This allows the condition to also apply to CHINA and japan, for example.

Configuring a Stream Selector Processor

Configure a Stream Selector processor to route data to different streams based on conditions.

  1. On the General tab, configure the following properties:
    General Property Description
    Name Stage name.
    Description Optional description.
    Cache Data Caches processed data.
  2. On the Conditions tab, click the Add icon for each condition that you want to create, then specify the condition to use.
    Each new condition creates a corresponding output location. You cannot edit or delete the condition for the default stream.