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.
In any of the supported target databases, this Snap does not appropriately identify nor render column references beginning with an _ (underscore) inside SQL queries that use the following constructs and contexts (the Snap works as expected in all other scenarios):
ONcondition (ELT Join, ELT Merge Into Snaps)
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)
Inside SQL query editor (ELT Select and ELT Execute Snaps)
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.
Snap Input and Output
Type of View
Number of Views
Examples of Upstream and Downstream Snaps
An upstream Snap is not mandatory. Use the input view to connect the Snap as the terminating Snap in the Pipeline.
A downstream Snap is not mandatory. Use the output view to connect the Snap as the first Snap in the Pipeline.
Required. The 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.
ELT Execute for SF
Required. Use this field set to define your SQL statements, one in each row. Click to add a new row. You can add as many SQL statements as you need.
SQL Statement Editor
Required. Enter the SQL statement to run, in this field. The SQL statement must follow the SQL syntax as stipulated by the target database—Snowflake, Redshift, Azure Synapse, or Databricks Lakehouse Platform.
drop table base_01_oldcodes;
Failure: DQL statements are not allowed.
The ELT Execute Snap does not support Data Query Language (DQL) and hence statements containing
Remove any DQL statements (containing
Sample Queries for the ELT Execute Snap
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.
After the Scheduled Task/Pipeline is run:
Target Table: OUT_ELT_EXECUTE_SF_003
Example 2: Using one ELT Execute Snap to Create and Fill a Table
Let us observe the configuration of the ELT Execute Snap (first Snap in the above Pipeline).
ELT Execute Snap
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 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