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. |
Prerequisites
None.
Limitations
None.
Limitation
| Multiexcerpt include macro | ||||
|---|---|---|---|---|
|
Known Issues
| Multiexcerpt include macro | ||||
|---|---|---|---|---|
|
...
| Input/Output | Type of View | Number of Views | Examples of Upstream and Downstream Snaps | Description |
|---|---|---|---|---|
| Input | Document |
|
| The SQL queries referencing separate tables in which you want to add the JOIN clause. |
| Output | Document |
|
| The incoming SQL queries joined with a JOIN clause. The output from executing this Snap varies based on which table is connected to which input view. |
Snap Settings
| 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. |
| Parameter Name | Data Type | Description | Default Value | Example | ||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Label | String |
| ELT Join | Combined Dataset | ||||||||||||||
| Get preview data | Check box |
| Not selected | Selected | ||||||||||||||
| ELT Join Type | String/Drop-down list | Choose the join type to use in the SQL. Available options are:
See Snowflake Join Types for more information. See Redshift Join Types for more information. See Azure Synapse Join Types for more information. See Join Types for Databricks on AWS for more information. See Join operation in BigQuery Standard SQL for more information.
| Inner | Left outer | ||||||||||||||
| ELT Join Condition | String/Expression | Specify the condition to initiate the JOIN operation. If you do not specify any condition here, the Snap uses You can specify any SQL expression that has a boolean output (true or false).
| N/A | CUST_CODE1 = CUST_CODE | ||||||||||||||
| Left Table Alias | String/Expression | Specify the alias to use for the table in the first input view. This enables you to qualify the columns to join with an alias name and resolve any ambiguity due to identical column names. | N/A | TBL1 | ||||||||||||||
| Right Table Alias | String/Expression | Specify the alias to use for the table in the second input view. This enables you to qualify the columns to join with an alias name and resolve any ambiguity due to identical column names. | N/A | TBL2 | ||||||||||||||
| Resultant Column Names Prefix Type | Drop-down list | Not applicable if target database is Databricks Lakehouse Platform (DLP). Choose an option from the list to prefix the resultant columns names with a table alias; this enables the Snap to prevent collision of identical column names in resultant table. Available options are:
| None | RT.D_DATE_SK |
...
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:
...
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 |
|---|---|
 |  |
...
First we use two ELT Select Snaps to retrieve data from the SF store and SF date_dim tables respectively. We configure the ELT Select Snaps as shown below; the respective output views are as shown below.
| ELT Select Snaps | Output |
|---|---|
|
|
|
|
Then, we configure the ELT Filter Snaps with the following filter conditions.
| ELT Filter Snaps | Output |
|---|---|
Filter condition: "SR_ITEM_SK">=120000
| Filters the records by SR_ITEM_SK column whose values are greater than or equal to 120000.
|
Filter condition: "D_YEAR">=2000
| Filters the records by year greater than or equal to 2000.
|
Then, we configure the ELT Join Snap to perform a left outer join based on the matching values of the D_DATE_SK column in both the tables by applying a Join condition. Note that we select Only Duplicate Columns for Resultant Column Names Prefix Type to avoid collision of identical columns in the resultant output.
...