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.
ELT Router Snap is a Transform-type Snap that transforms SQL.
Valid accounts and access permissions to connect to the following:
Target: Snowflake, Redshift, Azure Synapse, Databricks Lakehouse Platform, or BigQuery
The SQL query in which you want to add the WHERE clause.
The modified SQL query with the WHERE clause and the condition.
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.
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.
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
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*
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
Select the output view that you want to route the SQL query to.
Evaluate filter condition before forwarding
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.
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.
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: