ELT Router
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
Limitations and Known Issues
None.
Views
Type | Format | Number of Views | Examples of Upstream and Downstream Snaps | Description |
---|---|---|---|---|
Input | Document |
|
| The SQL query in which you want to add the WHERE clause. |
Output | Document |
|
| 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:
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 |
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* | 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. |
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.
|
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: