In this article

An account for the Snap

You must define an account for this Snap to communicate with your target CDW. Click the account specific to your target CDW below for more information:

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.

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

ELT Snap Pack does not support Legacy SQL dialect of Google BigQuery. We recommend that you use only the BigQuery's Standard SQL dialect in this Snap.

Known Issues

In any of the supported target databases, this Snap does not appropriately identify nor render column references beginning with an _ (underscore) inside SQL queries/statements that use the following constructs and contexts (the Snap works as expected in all other scenarios):

WHERE clause (ELT Filter Snap)
WHEN clause
ON condition (ELT Join, ELT Merge Into Snaps)
HAVING clause
QUALIFY clause
Insert expressions (column names and values in ELT Insert Select, ELT Load, and ELT Merge Into Snaps)
Update expressions list (column names and values in ELT Merge Into Snap)
Secondary AND condition
Inside SQL query editor (ELT Select and ELT Execute Snaps)

Workaround

As a workaround while using these SQL query constructs, you can:

Precede this Snap with an ELT Transform Snap to re-map the '_' column references to suitable column names (that do not begin with an _ ) and reference the new column names in the next Snap, as needed.

In case of Databricks Lakehouse Platform where CSV files do not have a header (column names), a simple query like SELECT * FROM CSV.`/mnt/csv1.csv` returns default names such as _c0, _c1, _c2 for the columns which this Snap cannot interpret. To avoid this scenario, you can:
- Write the data in the CSV file to a DLP table beforehand, as in: CREATE TABLE csvdatatable (a1 int, b1 int,…) USING CSV `/mnt/csv1.csv` where a1, b1, and so on are the new column names.
- Then, read the data from this new table (with column names a1, b1, and so on) using a simple SELECT statement.
In case of Databricks Lakehouse Platform, all ELT Snaps' preview data (during validation) contains a value with precision higher than that of the actual floating point value (float data type) stored in the Delta. For example, 24.123404659344 instead of 24.1234. However, the Snap reflects the exact values during Pipeline executions.

Snap Input and Output

Input/Output	Type of View	Number of Views	Examples of Upstream and Downstream