Overview
Use this Snap to add an INTERSECT SQL operator to the separate queries coming from upstream Snaps. This Snap also allows you to preview the result of the INTERSECT SQL operation on the incoming SQL queries. You can validate the modified query using this preview functionality.
Note |
---|
The INTERSECT SQL operation does not eliminate duplicate records. You can add the ELT Unique Snap to the ELT Intersect Snap to remove duplicates. |
Prerequisites
None.
Limitation
Multiexcerpt include macro |
---|
name | ME_ELT_GBQ_StandardSQL_UOI |
---|
page | ELT Copy |
---|
|
Known Issues
Multiexcerpt include macro |
---|
name | ME_ELT_KI_Underscore_Refs |
---|
page | ELT Snap Pack |
---|
|
Input/Output | Type of View | Number of Views | Examples of Upstream and Downstream Snaps | Description |
---|
Input | Document | | | Multiple SQL queries. |
Output | Document | | - ELT Merge Into
- ELT Insert-Select
| The incoming SQL queries joined with the INTERSECT operator. |
Snap Settings
Info |
---|
title | 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. |
Parameter Name | Data Type | Description | Default Value | Example |
---|
Label | String | Insert excerpt |
---|
| File Writer |
---|
| File Writer |
---|
nopanel | true |
---|
|
| ELT Intersect | Common Records |
Get preview data | Checkbox |
Multiexcerpt macro |
---|
| Select this checkbox to include a preview of the query's output. The Snap performs limited execution and generates a data preview during Pipeline validation. In the case of ELT Pipelines, only the SQL query flows through the Snaps but not the actual source data. Hence, the preview data for a Snap is the result of executing the SQL query that the Snap has generated in the Pipeline. The number of records displayed in the preview (upon validation) is the smaller of the following: Rendering Complex Data Types in Databricks Lakehouse PlatformBased on the data types of the fields in the input schema, the Snap renders the complex data types like map and struct as object data type and array as an array data type. It renders all other incoming data types as-is except for the values in binary fields are displayed as a base64 encoded string and as string data type.
|
| Not selected | Selected |
Retain duplicates (Databricks Lakehouse Platform only) | Checkbox | Select this checkbox (when your target database is Databricks Lakehouse Platform) 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.

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 |
---|
| |
A preview of the outputs from the ELT Select Snaps is shown below:
Read Part A Output | Read Part B Output |
---|
| |
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:

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.

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) |
---|
|
Snap Output |
---|
|
ELT Select Snap (Source 2) |
---|
|
Snap Output |
---|
|
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 ensure that the Snap includes duplicate matching records in its output view.
ELT Intersect Snap |
---|
|
Snap Output |
---|
|
We can see that the output of this Snap contains exactly four matching records including a duplicate record ("FIRSTNAME": "Bruke", ...
)
In the end, we write this list of common records between the two source data sets into a DLP table out_ma_dl_elt_intersect_02 by configuring the ELT Insert Select Snap as shown below:

Download this Pipeline.
Downloads
Note |
---|
title | Important Steps to Successfully Reuse Pipelines |
---|
|
- Download and import the Pipeline into SnapLogic.
- Configure Snap accounts as applicable.
- Furnish Pipeline parameters as applicable.
|
Insert excerpt |
---|
| ELT Snap Pack |
---|
| ELT Snap Pack |
---|
nopanel | true |
---|
|
See Also