Overview

You can use this Snap to execute SQL queries in the target database—Snowflake, Redshift, Azure Synapse, Databricks Lakehouse Platform (DLP), or BigQuery. You can run the following types of queries using this Snap:

• Data Definition Language (DDL) queries

• Data Manipulation Language (DML) queries

• Data Control Language (DCL) queries

Prerequisites

Valid accounts and access permissions to connect to the following:

• Source: AWS S3, Azure Cloud Storage, or Google Cloud Storage

• Target: Snowflake, Redshift, Azure Synapse, DLP, or BigQuery

Limitations

• This Snap does not support multi-statement transaction rollback of any of the DDL, DCL or DML statements specified.

• Each statement is auto-committed upon successful execution. In the event of a failure, the Snap can rollback only updates corresponding to the failed statement execution. All previous statements (during that Pipeline execution runtime) that ran successfully are not rolled back.

• You cannot run Data Query Language (DQL) queries using this Snap. For example, SELECT and WITH query constructs.

• Use this Snap either at the beginning or in the end of the Pipeline. 

• This Snap executes the SQL query only during Pipeline Execution. It does NOT perform any action (including showing a preview) during Pipeline validation.

Known Issues

• While you specify an SQL statement in the SQL Statement Editor of an ELT Snap as an expression, the dynamic validation for the expression displays inline errors when there is more than one incoming document and without the '__sql__' key to the current Snap, when you select Get Preview Data checkbox in the previous Snap, and when Preview Document Count in your user settings is set to a value more than 1.

  • To prevent this error and similar ones, do not select the Get Preview Data checkbox in the previous Snap, set the Preview Document Count in your user settings to 1, or append a condition where 1 = 0 to the SQL statement with the Get Preview Data checkbox selected.

• Due to an issue with BigQuery table schema management (the time travel feature), an ALTER TABLE action (Add or Update column) that you attempt after deleting a column (DROP action) in your BigQuery target table causes the table to break and the Snap to fail.

  • As a workaround, you can consider either avoiding ALTER TABLE actions on your BigQuery instance or creating (CREATE) a temporary copy of your table and deleting (DROP) it after you use it.

• When your Databricks Lakehouse Platform instance uses Databricks Runtime Version 8.4 or lower, ELT operations involving large amounts of data might fail due to the smaller memory capacity of 536870912 bytes (512MB) allocated by default. This issue does not occur if you are using Databricks Runtime Version 9.0.
• ELT Pipelines created prior to 4.24 GA release using one or more of the ELT Insert Select, ELT Merge Into, ELT Load, and ELT Execute Snaps may fail to show expected preview data due to a common change made across the Snap Pack for the current release (4.26 GA). In such a scenario, replace the Snap in your Pipeline with the same Snap from the Asset Palette and configure the Snap's Settings again.

• The Snap’s 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

Snap Settings

Pipeline Execution Statistics

As a Pipeline executes, the Snap shows the following statistics updating periodically. You can monitor the progress of the Pipeline as each Snap performs executes. In case of DLP, the Snap captures and displays these details for DML statements onlyits action.

• Records Added

• Records Updated

• Records Deleted

You can view more information as follows, by clicking the Download Query Details link to download a JSON file. In case of DLP, the Snap captures and depicts additional information (extraStats) on DML statement executions.

Troubleshooting

Sample Queries for the ELT Execute Snap

CREATE OR REPLACE WAREHOUSE me_wh WITH warehouse_size='X-LARGE';
CREATE OR REPLACE TABLE "TEST_DATA".NEW_DATA(VARCHAR_DATA VARCHAR(10));
CREATE OR REPLACE TABLE "TEST_DATA".DT_EXECUTE_01(VARCHAR_DATA VARCHAR(100),TIME_DATA TIME,FLOAT_DATA FLOAT,BOOLEAN_DATA BOOLEAN,NUMBER_DATA NUMBER(38,0),DATE_DATA DATE);
TRUNCATE TABLE IF EXISTS "public".simple_data_02;
INSERT OVERWRITE INTO "BIGDATAQA"."TEST_DATA"."OUT_ELT_EXECUTE_SF_003" SELECT * FROM ( SELECT * FROM "BIGDATAQA"."TEST_DATA"."DT_EXECUTE_03" )

Example 1: Updating a Target Table Based on Updates to Another Table

The following Pipeline updates a target (backup) table - OUT_ELT_EXECUTE_SF_003 periodically based on the updates to another table DT_EXECUTE_03 (source). These tables are present in a Snowflake database, and we use data views from this database to present the updates that the Pipeline does in the target table.

There are two steps to achieve this functionality using the ELT Execute Snap:

1. Create a Pipeline with only the ELT Execute Snap for performing the periodic update.

2. Create a Scheduled Task from this Pipeline to trigger a job at specific times of the day, as needed. See Tasks Page for information on creating Scheduled Tasks from Pipelines.

   • This task regularly looks into the DT_EXECUTE_03 table for updates and inserts the latest data from this table into the target (backup) table.

Before we create the Pipeline:

Source Table: DT_EXECUTE_03
Target Table: OUT_ELT_EXECUTE_SF_003

We configure the ELT Execute Snap to run a DML query, as follows. 

Once we create the Scheduled Task (after saving this Pipeline), the task runs as scheduled. Then, the ELT Execute Snap copies the data from the source table and inserts into the target (backup) table.

After the Scheduled Task/Pipeline is run:

Download this Pipeline.

Example 2: Using one ELT Execute Snap to Create and Fill a Table

In this example Pipeline, we create a new table in the Redshift database and fill data into this table using an ELT Execute Snap. We later read the data from this table using an ELT Select Snap.

Let us observe the configuration of the ELT Execute Snap (first Snap in the above Pipeline).

ELT Execute Snap
Snap Output

We have added two SQL statements into the SQL Statements field set—one for creating/overwriting a table and another for inserting a row into same table. ELT Execute Snap does not have a data preview except for the placeholder SQL statement that indicates the Snap is validated successfully. The Snap executes the SQL queries real-time when we run the Pipeline.

We connect an ELT Select Snap to the ELT Execute Snap to read the data from the newly-created table in the Redshift database. In this Snap:

• We use the same ELT Database account that we use for the previous Snap.

• Define/select the values for the database, schema and the table name to identify the table that the previous Snap is configured to create.

  • Alternatively, we can enable the SQL query editor and include the select * from dev.public.new_table_trg; statement. 

  • It is also important here to note that we cannot run this DQL query using the ELT Execute Snap.

ELT Select Snap
Snap Output

Download this Pipeline.

Downloads

See Also

• ELT Snap Pack

• Best Practices for Using the ELT Snap Pack

• Getting Started with SnapLogic