ELT Copy

Overview

Flow-type Snap
Does not support Ultra Pipelines

ELT Copy creates copies of the output SQL query from the upstream Snap.

You can use this Snap to create multiple copies of the same query if you want to perform different operations upon it. A preview of the incoming SQL query's output is also passed to the output of this Snap's output if you selected the Get preview data checkbox in the upstream Snap.

Prerequisites

None.

Limitations

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)

Tip:

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 views


View	Description	Examples of upstream and downstream Snaps
Input	The SQL query to replicate.	Examples: ELT Select ELT Aggregate
Output	Multiple copies of the input SQL query.	Examples: ELT Join ELT Merge Into
Error	Error handling is a generic way to handle errors without losing data or failing the Snap execution. You can handle the errors that the Snap might encounter when running the pipeline by choosing one of the following options from the When errors occur list under the Views tab. The available options are: Stop Pipeline Execution Stops the current pipeline execution when the Snap encounters an error. Discard Error Data and Continue Ignores the error, discards that record, and continues with the remaining records. Route Error Data to Error View Routes the error data to an error view without stopping the Snap execution. Learn more about Error handling in Pipelines.

Snap settings

Note:

Suggestion icon (): Indicates a list that is dynamically populated based on the configuration.
Expression icon (): Indicates whether the value is an expression (if enabled) or a static value (if disabled). Learn more about Using Expressions in SnapLogic.
Add icon (): Indicates that you can add fields in the field set.
Remove icon (): Indicates that you can remove fields from the field set.


Field / Field set	Type	Description
Label	String	Specify a name for the Snap. You can modify this to be more specific, especially if you have more than one of the same Snap in your Pipeline. Default value: ELT Copy Example: Select Copy

Troubleshooting

None.

Examples

Performing Multiple Operations on the Same Table

We need a SELECT query with suitable clauses/operators for the intended operation. We can use separate Pipelines to build each query. Alternatively, we can also use the ELT Copy Snap to create multiple copies of the initial SELECT query and then use suitable Snaps for the intended operations. In this example, we want to sort the records in the table and filter the records in the table. To sort the records, we need a query with the ORDER BY clause. To filter records, we need a query with the WHERE clause. This Pipeline shows how we can use the ELT Copy, ELT Sort, and ELT Filter Snaps to accomplish these tasks.

First, we build a SELECT query to read the target table. To do so, we can use an ELT Select Snap, in this example: Read Part A. This Snap is configured to output a SELECT * query to read the target table in the database. Additionally, this Snap is also configured to show a preview of the SELECT query's execution:

A preview of the output from the ELT Select Snap is shown below:

Then, we add an ELT Copy Snap to the ELT Select Snap, so that the output of the ELT Select Snap is replicated. Each of the output views in the ELT Copy Snap contains the following output:

We can connect the appropriate Snaps to each of the ELT Copy Snap output views. In this example, we want to sort the records and filter them. So we add the ELT Sort Snap to one of the output views and the ELT Filter Snap to the other output view.

We use the following configuration for the ELT Sort Snap, since we want to sort the table records based on the values in the CUST_ID column in the ascending order.

This Snap builds the following query based on this configuration:

Since we want to filter the records based on the value in the GRADE column in the table, we configure the ELT Filter Snap to do so:

This Snap builds the following query based on this configuration:

We can also add ELT Insert-Select Snaps downstream of these Snaps and write the result of the queries into other tables.