In this article

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

Support for Ultra Pipelines

Works in Ultra Pipelines.

Limitations

None.

Known Issues

None.

Views

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
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	Select this checkbox to include a preview of the query's output. The Snap performs limited execution and generates a data preview during Pipeline validation. In the case of ELT Pipelines, only the SQL query flows through the Snaps but not the actual source data. Hence, the preview data for a Snap is the result of executing the SQL query that the Snap has generated in the Pipeline. The number of records displayed in the preview (upon validation) is the smaller of the following: Number of records available upon execution of the SQL query generated by the Snap. The value set in the Preview Data Count setting (default is 50 records). Rendering Complex Data Types in Databricks Lakehouse Platform Based on the data types of the fields in the input schema, the Snap renders the complex data types like map and struct as object data type and array as an array data type. It renders all other incoming data types as-is except for the values in binary fields are displayed as a base64 encoded string and as string data type.
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
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

Download and import the Pipeline into SnapLogic.
Configure Snap accounts as applicable.
Provide Pipeline parameters as applicable.

	File	Modified

You are not logged in. Any changes you make will be marked as anonymous. You may want to Log In if you already have an account.

No files shared here yet.

Drag and drop to upload or browse for files

Snap Pack History

Click here to expand...

See ELT Snap Pack History.

ELT Router