ELT Router

Overview

You can use this Snap to route input SQLs to multiple output views based on the given conditional expression. You can add the output views in the Views tab and assign different conditional SQL expressions to them so that the Snap gives multiple output views. ELT Router Snap works similarly to ELT Copy Snap if no conditional SQL expressions are given.

Snap Type

ELT Router Snap is a Transform-type Snap that transforms SQL.

Prerequisites

Valid accounts and access permissions to connect to the following:

  • Target: Snowflake, Redshift, Azure Synapse, Databricks Lakehouse Platform, or BigQuery

Limitations and Known Issues

None.

Views

Type

Format

Number of Views

Examples of Upstream and Downstream Snaps

Description

Type

Format

Number of Views

Examples of Upstream and Downstream Snaps

Description

Input 

Document

  • Min: 1

  • Max: 1

  • ELT Select

  • ELT Copy

The SQL query in which you want to add the WHERE clause.

Output

Document

  • Min: 2

  • Max: ∞

  • ELT Insert-Select

  • ELT Union

The modified SQL query with the WHERE clause and the condition.

Error

Error handling is to have a generic way to handle errors without losing data or failing the Snap execution. You can handle the errors that might arise while running the Pipeline by choosing one of the options from the When errors occur section under the Views tab.

The options available are:

  • Stop Pipeline Execution: Stops the current pipeline execution when an error arises.

  • Discard Error Data and Continue: Ignores an error completely, discard that record, and continue with the rest of the records.

  • Route Error Data to Error View: Routes the data to an error view of the Snap and then decides what to do with the error.

See Error View in Pipeline in examples to understand the Snap error view settings and error output. For more information on the error view and handling error output in a Pipeline, see Error handling in Pipelines.

Snap Settings

  • Field names marked with an asterisk ( * ) are mandatory.

  • Suggestion icon - Suggestible fields and suggestions will be populated based on the account settings.

  • Click the = (Expression) button in the Snap's configuration, if available, to define the corresponding field value using expression language and Pipeline parameters. 

SQL Functions and Expressions for ELT

You can use the SQL Expressions and Functions supported for ELT to define your Snap or Account settings with the Expression symbol = enabled, where available. This list is common to all target CDWs supported. You can also use other expressions/functions that your target CDW supports.

Field Name

Type

Description

Field Name

Type

Description

Label*

String

The name for the Snap. You can modify this to be more specific, especially if you have more than one of the same Snap in your Pipeline.

Default Value: ELT Router for Quantity
Example: ELT RS Account

Get preview data

Checkbox

Routes

Specify your conditional SQL expressions and assign them to an output view using this fieldset. Click to add a new row. Ensure that you specify each condition in a separate row.

This fieldset consists of the following fields:

  • Conditional SQL Expression

  • Output view name

Conditional SQL Expression*

String

The Snap evaluates the expression. If the expression is true, then the input SQL is routed to the corresponding output view.

If none of the expressions meet the given conditions, then the Pipeline fails.

Default Value: None.
Example: QTY >1

Output view name

Dropdown

Select the output view that you want to route the SQL query to.

Evaluate filter condition before forwarding

Checkbox

The given input SQL will be evaluated along with the filter condition to determine if the filter condition is being satisfied.

  • If the filter condition is satisfied, then the Snap forwards the input SQL, along with filter condition, to the output view.

  • If filter condition is not satisfied, nothing will be passed and all downstream logic will be non-operational.

Troubleshooting

Error

Reason

Resolution

Error

Reason

Resolution

Invalid placement of ELT Router snap

ELT Router Snap cannot be used at the start of a pipeline

ELT Router Snap should have at least one upstream Snap. Please move ELT Router snap either to the middle or end of the pipeline.

Invalid Expression Detected

All the conditional expressions provided in the Snap are empty or invalid.

If the conditional expressions are not valid, the ELT Router Snap throws an error. Provide valid expressions that can read from the input SQL queries.

Invalid output view name detected

More than one conditional expression is tagged to single output view.

Each valid conditional expression should be tagged to different output view names.

Examples

Filtering data regarding employee sales by the column name emp id

In the following example, ELT select has the target table with the monthly sales details of the employees is connected to the ELT Router Snap to route sales details of each employee in a different output.

First, we use the ELT Select Snap to build a query to retrieve all records from the target table.

Upon execution, this Snap builds the query as shown below:

Then, we add the ELT Router Snap and configure it as needed. In this example, we want to retrieve the sales details for 3 months of the employees from the give table. So, we configure the ELT Limit Snap as shown below:

Based on this configuration, the ELT Limit Snap retrieves sales details based on the employee number. The output views look as follows:

Download this Pipeline.

Downloads