Versions Compared

Old Version 7

changes.mady.by.user Anand Vedam

Saved on

compared with

New Version 8

changes.mady.by.user Anand Vedam

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

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.
Image RemovedImage Added

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. 

...

Parameter NameData TypeDescriptionDefault ValueExample 
LabelString
Insert excerpt
File Writer
File Writer
nopaneltrue
ELT IntersectCommon Records
Get preview dataCheck boxCheckbox


Multiexcerpt macro
namegetpreviewdata

Select this check box checkbox to include a preview of the query's output. The Snap performs limited execution and generates a data preview during Pipeline validation.

In 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:

  • Number of records available upon execution of the SQL query generated by the Snap.

  • The value set in the Preview Data Count setting (default is 50 records).


Not selectedSelected
Retain duplicates (Databricks Lakehouse Platform only)CheckboxWhen your target database is Databricks Lakehouse Platform, select this checkbox to include duplicate records/values, if any, that match the intersection criteria in the output data set. Else, the Snap does not retain duplicate entries in the output.Not selectedSelected

Troubleshooting

None.

...

We can add also an ELT Insert-Select Snap downstream and write the result of this query into another table.

Download this Pipeline.

Retaining Duplicate Values/Entries in Intersection Data (Databricks Lakehouse Platform)

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 Added

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 Added

Snap Output

Image Added


ELT Select Snap (Source 2)

Image Added

Snap Output

Image Added

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 make sure that the Snap includes duplicate matching records in its output view.

ELT Intersect Snap

Image Added

Snap Output

Image Added

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:

Image Added

Download this Pipeline.

Downloads

Note
titleImportant Steps to Successfully Reuse Pipelines
  1. Download and import the Pipeline into SnapLogic.
  2. Configure Snap accounts as applicable.
  3. Furnish Pipeline parameters as applicable.

...