In this article
...
Use this Snap to add a JOIN clause to join tables in separate queries coming from the upstream Snaps. This Snap also allows you to preview the result of the output query. You can validate the modified query using this preview functionality.
| Note | ||
|---|---|---|
| ||
Starting from 4.24 GA, ensure to configure an account for this Snap. |
...
| Info | ||
|---|---|---|
| ||
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. |
...
Method-2: Dynamic Substitutions
You must enable expressions when using Pipeline parameters for dynamic substitutions. Format the SQL expression, except the Pipeline parameter's reference, as a string.
For example, if you want to use the Pipeline parameter, name, which contains the value of a column in the ELT Join Condition field:
...
| Note | ||
|---|---|---|
| ||
When expressions are disabled, use \ as an escape character to treat underscore (_) as a string. For example:
|
Troubleshooting
...
| Error | Reason | Resolution |
|---|---|---|
Syntax error when database/schema/table name contains a hyphen (-) such as in (CDW: Azure Synapse) | Azure Synapse expects any object name containing hyphens to be enclosed between double quotes as in "<object-name>". | Ensure that you use double quotes for every object name that contains a hyphen when your target database is Azure Synapse. For example: default."schema-1"."order-details". |
Examples
Performing Inner Join
We need a query that contains a JOIN clause. This example demonstrates how we can use the ELT Join Snap to build a query with the JOIN clause.
First, we build SELECT queries to read the target tables. To do so, we can use two ELT Select Snaps, in this example: Read Part A and Read Part B. Each of these Snaps is configured to output a SELECT * query to read the target table in the database. Additionally, these Snaps are also configured to show a preview of the SELECT query's execution as shown:
| Read Part A Configuration | Read Part B Configuration |
|---|---|
 |  |
...