) to include duplicate records/values, if any, that match the intersection criteria in the output data set. Else, the Snap does not retain any existing duplicate entries in the output. Info |
---|
This setting is ignored in case of all other supported target databases/CDWs except Databricks Lakehouse Platform. |
| Not selected | Selected |
Troubleshooting
None.
Examples
Retrieving Common Records Between Two Tables
We need a query that combines two SELECT queries with an INTERSECT operator to retrieve common records between two tables. This example shows how we can use the ELT Intersect Snap to achieve this result.
Image Modified
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 |
---|
Image Modified | Image Modified |
A preview of the outputs from the ELT Select Snaps is shown below:
Read Part A Output | Read Part B Output |
---|
Image Modified | Image Modified |
Then, we connect the ELT Intersect Snap to the output view of the ELT Select Snaps. The SELECT * queries in both of these Snaps form the inputs for the ELT Intersect Snap. Upon execution, the ELT Intersect Snap combines both incoming SELECT * queries and adds the INTERSECT operator.
A preview of the ELT Intersect Snap's output is shown below:
Image Modified
We can add also an ELT Insert-Select Snap downstream and write the result of this query into another table.
Download this Pipeline.
In this example Pipeline which connects to a Databricks Lakehouse Platform database, we demonstrate how you can retain duplicate records in the common data that the ELT Intersect Snap produces in the output.
Image Modified
We use two ELT Select Snaps to capture data from the two different datasets that we want to extract common records from.
ELT Select Snap (Source 1) |
---|
Image Modified |
Snap Output |
---|
Image Modified |
ELT Select Snap (Source 2) |
---|
Image Modified |
Snap Output |
---|
Image Modified |
Let us connect the Snaps (that produce these two datasets) to the two input views of the ELT Intersect Snap. Notice that we have selected the Retain Duplicates (Databricks Lakehouse Platform only) check box to