Overview
Use this Snap to add a MINUS SQL operator to the separate queries coming from upstream Snaps. The MINUS SQL operator corresponds to ANSI EXCEPT. In Azure Synapse and BigQuery, this Snap uses the EXCEPT operation unlike in Redshift and Snowflake, where the Snap uses the MINUS operation. This Snap also allows you to preview the result of the MINUS SQL operation on the incoming SQL queries. You can validate the modified query using this preview functionality.
Image Modified
| Note |
|---|
- The MINUS SQL operation does not eliminate duplicate records. You can add the ELT Unique Snap to the ELT Minus Snap to remove duplicates.
- This Snap's output is not commutative. The Snap's output changes based on which table is connected in which input view. For example, if you use queries for tables A and B as inputs, where query A is connected to the first input and query is connected to the second, the result from the Snap's execution would not be the same if it was the other way around. Basically, A MINUS B is not the same as B MINUS A.
|
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 Unique
- ELT Insert-Select
| The incoming SQL queries with the MINUS operator included. |
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 Minus | Exclude Common Records |
| Get preview data | Checkbox | | Multiexcerpt include macro |
|---|
| name | getpreviewdata |
|---|
| page | ELT Intersect |
|---|
|
| Info |
|---|
In Azure Synapse and BigQuery, this Snap uses EXCEPT operation unlike in Redshift and Snowflake, wherein the Snap uses MINUS operation. |
| Output preview in Azure Synapse |
|---|
 Image Modified |
| Not selected | Selected |
| Retain duplicates (Databricks Lakehouse Platform only) | Checkbox | If your target database is a Databricks Lakehouse Platform instance, select this checkbox to indicate that the Snap can include duplicate entries in its output. Else (by default), the Snap retrieves only unique records from the MINUS/EXCEPT operation result. | Not selected | Selected |
Troubleshooting
None.
Examples
Retrieving Uncommon Rows From a Table
We need a query with the MINUS operator if we want to compare two tables and retrieve the uncommon records from the first table. This example shows how we can use the ELT Minus Snap to build this query.
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 Minus Snap to the output view of the ELT Select Snaps. The SELECT * queries in both of these Snaps form the inputs for the ELT Minus Snap. Upon execution, the ELT Minus Snap combines both incoming SELECT * queries and adds the MINUS operator.
A preview of the ELT Minus Snap's output is shown below:
Image Modified
We can also add an ELT Insert-Select Snap downstream and write the result of this query into another table.
Download this Pipeline.
Downloads
| Note |
|---|
| title | Important Steps to Successfully Reuse Pipelines |
|---|
|
- Download and import the Pipeline into SnapLogic.
- Configure Snap accounts as applicable.
- Provide Pipeline parameters as applicable.
|
| Insert excerpt |
|---|
| ELT Snap Pack |
|---|
| ELT Snap Pack |
|---|
| nopanel | true |
|---|
|
See Also
