Use this Snap to build SELECT SQL queries to fetch records from the specified table. Executed in its default state after providing database, schema, and table name, the Snap builds a standard SELECT * FROM query. However, you can use the inbuilt SQL query editor to build complex queries to perform complex operations spanning multiple tables such as JOIN, AGGREGATE, LIMIT, and DISTINCT. Additionally, this Snap can also connect to multiple upstream Snaps. This enables you to build a query to perform operations on multiple tables together or separately at the same time.
A valid SnapLogic account to connect to the database in which you want to execute the query if you also want to preview the data.
SnapLogic accounts are also required if you want to use the Snap's suggest feature in fetching schema and table names.
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.
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.
Suggestions displayed for the Schema Name field in this Snap is from all databases that the Snap account user can access, instead of the specific database selected in the Snap's account or Settings.
While you specify an SQL statement in the SQL Query Editor of the 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.
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)
ON condition (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)
Secondary AND condition
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.
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
Type of View
Number of Views
Examples of Upstream and Downstream Snaps
Max: No limit
The database, schema, table names in which the query must be executed. You can also pass this information using an upstream ELT Snap. Additionally, you can also pass such information for several tables to the Snap using a separate input view for each table.
A SELECT SQL query for the table specified in the Snap. If you use the SQL editor, then an SQL query built based on your inputs in the editor. Optionally, the output also includes a preview of the query's output if the Get preview data checkbox is selected.
SQL Functions and Expressions for ELT
You can usetheSQL Expressions and Functions supported for ELTto define your Snap or Account settings with the Expression symbol = enabled, where available. This list is common to all target CDWs supported.You can also use other expressions/functions that your target CDW supports.
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.
Fetch Sales Data
Get preview data
Select this 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).
Rendering Complex Data Types in Databricks Lakehouse Platform
Based on the data types of the fields in the input schema, the Snap renders the complex data types like map and struct as object data type and array as an array data type. It renders all other incoming data types as-is except for the values in binary fields are displayed as a base64 encoded string and as string data type.
If you have selected this field, then you must ensure that the database name is also provided, either in the Snap's Database Name field or in the Account. Otherwise, the Snap's output contains only the SQL built using the Snap. This does not indicate that the SQL is correct.
The name of the database in which the target/source tables are located. Leave it blank to use the database name specified in the account settings.
You must ensure that the database name is correct. Otherwise, even though the Snap builds an SQL query, you will receive an error when executing the Snap since the query executes only in the target database.
If your target database is Databricks Lakehouse Platform (DLP), you can, alternatively, mention the file format type for your table path in this field. For example, DELTA, CSV, JSON, ORC, AVRO. See Table Path Management for DLP section below to understand the Snap's behavior towards table paths.
Schema Name (Not applicable to Databricks Lakehouse Platform)
The name of the database schema. In case it is not defined, then the suggestion for the table name retrieves all tables names of all schema in the specified database (except for DLP) when you click .
Ensure that you include the exactly same schema name including the double quotes, if used, when you repeat the schema name in the Table Name field.
Leave this field blank if your target database is Databricks Lakehouse Platform.
The name of the table or view for which you want to build the SQL queries.If it is not defined, then the Snap retrieves all the table names and views associated with the specified schema when you click
Ensure that you include the exactly same schema name, if at all, including the double quotes as specified in the Schema Name field.
If your target database is Databricks Lakehouse Platform (DLP), you can, alternatively, include the path in this field. For example, `/mnt/elt/mytabletarget`. Learn more about the Snap’s behavior toward paths in the Table Path Management for DLP section.
When your target database is BigQuery, this Snap supports wildcard search in this field.
Select this to enable the SQL query editor. You can use this editor to build all types of SQL queries that will execute on multiple tables. Enabling the SQL query editor activates the fieldset Input View to Virtual Table Map in the Snap.
To perform operations upon multiple tables, you must pass the table names through upstream Snaps. Specify each table name in a separate input view.
Activates when you select the Enable SQL query editor checkbox. This is useful when you enable multiple input views in the ELT Select Snap. Use this field set to map each input view to a dummy table. You will reference the table name specified in each input view with this dummy/virtual table name.
Each input view corresponds to the SQL coming from the upstream Snap and that SQL is referenced by the dummy table name.
Virtual table name columns must not have duplicates.
One input view must not be associated with different table names.
Click + to add rows. Each input view must be specified in a separate row.
This field set consists of the following fields:
Virtual Table Name
Select the input view whose table reference is to be mapped.
Virtual Table Name
Specify the dummy/virtual table name that will be assigned to the table reference in the selected input view.
SQL Query Editor
Enter the SQL queries that you want to build using this Snap. Even though the Snap's name is ELT Select, you can use this SQL query editor to build any SQL query.
SQL Comments are allowed
You can include inline comments and multi-line comments before, inside, or after your statement in this editor. It supports all standard SQL comment syntaxes as listed below:
-- comment text
# comment text
/* multi-line comment text */
// comment text
SELECT DISTINCT FROM MYTABLE
Table Path Management for DLP
File Format Type (Database Name field)
Table Path exists?#
All other requirements are valid?
Snap Operation Result
Failure. Snap displays error message.
Failure. Snap displays error message.
# We recommend that you specify a table path that resolves to a valid data file. Create the required target file, if need be, before running your Pipeline.
Preventing SQL Injection
You can pass Pipeline parameters as values in an SQL query; however, if you do not phrase the query properly it can lead to the parameter's name being bound as a value in the database. This potentially incorrect information being inserted into the database is known as SQL injection. It is thus necessary to take precautions when including Pipeline parameters in your SQL query to prevent SQL injection. Based upon the intended use of the Pipeline parameter, use one or both the following methods to prevent accidental SQL injection:
Method-1: Simple Substitutions
You can reference the Pipeline parameter directly with a JSON-path without enabling expressions.
For example, consider a Pipeline parameter name which contains the value of a column. You want to write a SELECT query to fetch records from the table mytable where the column's name matches the value in the Pipeline parameter.
SELECT * FROM mytable WHERE colname = _name
Method-2: Dynamic Substitutions
You must enable expressions when referencing table names using Pipeline parameters. Format the query, except the Pipeline parameter's reference, as a string.
For example, if you you want to write a SELECT query to fetch records from a table and you are passing the table's name in the Pipeline parameter table:
"SELECT * FROM" + _table
In the example above, you must still use JSON-paths if you want to use another Pipeline parameter for substituting values in the SQL:
"SELECT * FROM " + _table + " WHERE colname = _name"
The Snap evaluates the expression and also carries out path substitutions.
Here is how it works
The Snap pre-processes the query to extract any JSON-Paths and converts them to bound parameters. For example, consider the following query:
SELECT * FROM mytable WHERE name = _name
The Snap converts this query into the following before turning it into a prepared statement for the database:
SELECT * FROM mytable WHERE name = ?
The Snap evaluates the JSON-Path to get the value to bind the Pipeline parameter in the prepared statement.
Using escape characters
When expressions are disabled, use \ as an escape character to treat underscore (_) as a string.
SELECT \_2, \_3 FROM mytable WHERE colname = \_name
Database cannot be blank.
(when seeking the suggested list for Schema Name field)
Suggestions in the Schema Name and Target Table Name fields do not work when you have not specified a valid value for the Database Name field in this Snap.
Specify the target Database Name in this Snap to view and choose from a suggested list in the Schema Name and Target Table Name fields respectively.
SQL exception from Snowflake: Syntax error in one or more positions in the SQL query.
Column names in Snowflake tables are case-sensitive. It stores all columns in uppercase unless they are surrounded by quotes during the time of creation in which case, the exact case is preserved. See, Identifier Requirements — Snowflake Documentation.
Ensure that you follow the same casing for the column table names across the Pipeline.
Database encountered an error during preview processing
(Target CDW: BigQuery)
This can happen when the target database cannot recognize/interpret the table name.
Ensure that the table name is valid and the table with provided name exists. Using a wildcard in the SQL editor mode, ensure that the table name is enclosed between backticks (`). For example: `scd*`, `mode*`.
Retrieving a Fixed Number of Records from a Table
In a typical scenario, we use the SELECT command to retrieve records from a table. We can control how many records are retrieved by specifying a WHERE condition. However, if we want to limit the number of records retrieved from the table without any conditions, or if we want to retrieve a fixed number of records starting from a specific row, we must use the SELECT command with the LIMIT clause. This example shows how we can use the ELT Limit Snap to achieve this result.
First, we use the ELT Select Snap to build a query to retrieve all records from the target table.
Upon execution, this Snap builds the query as shown below:
Then, we add the ELT Limit Snap and configure it as needed. In this example, we want to retrieve the next 5 records after the first record. So, we configure the ELT Limit Snap as shown below:
Based on this configuration, the ELT Limit Snap retrieves 5 (ELT Limit field's value) records starting from the second record (ELT Limit Offset field's value)
We can also add an ELT Insert-Select Snap downstream and write the result of this query into another table.
The ELT Create View Snap enables you to create a new view when the view does not exist in the target database and/or schema or if the view already exists in the database and/or schema, and you choose to drop the existing view and re-create it.
The ELT Insert-Select Snap is more flexible and easier to use, especially if the number of columns in your source data set is very large. You can choose to update values only in a subset of columns in the target table.
The ELT Execute Snap can retrieve and execute SQL queries from the upstream Snap's output when referenced in the SQL Statement Editor using the Expression language (with the Expression button enabled).
The ELT Load Snap can infer the schema from the source files in Amazon S3, ADLS Gen2, Microsoft Azure Blob Storage, or Google Cloud Storage location and use it to create, overwrite, and append the target table in your Snowflake instance with the source data. The source files can be in the AVRO, CSV, JSON, ORC, or PARQUET format. Learn more at Automatic Schema Inference with ELT Load Snap.
Target Table Name in the following Snaps supports retrieving editable views with the table names from the selected target schema:
The pivot values in the ELT PivotSnap turns dynamic when you select Enable dynamic pivot values. The following field settings are added as part of this dynamic pivot values feature:
Filter Predicate List: A fieldset to filter the predicate list of the pivot values.
Pivot Values Filter: Condition required to filter the pivot values.
Boolean Operator: Predicate condition type through AND or OR Boolean operators
Sort Order: Sorting order of the pivot values.
You can specify the type of Microsoft Azure external storage location (source)—an Azure Data Lake Gen2 or a Blob Storage—to access your source data using the Storage Integration type of authentication and load it to your target Snowflake instance.
Upgraded with the latest SnapLogic Platform release.
To include a new option Overwrite existing table in the Target Table action field.
To display the final SQL query in its output preview upon Pipeline validation.
Enhanced the end Snap SQL query in the ELT Insert Select Snap’s preview output to display the CREATE TABLE... or the DELETE/DROP TABLE statements to be run before the query that inserts/loads data into a new table in the Snowflake target CDW.
Fixed an issue with ELT Insert Select and ELT Merge Into Snaps where they cause the Pipeline to fail when the specified target table does not exist. After this fix, the Snaps create a new target table if it does not exist during Pipeline validation.
The new table that is created will not be dropped in the event of a subsequent/downstream Snap failing during validation.
Fixed the issue with ELT Load Snap where the Snap caused an SQL exception—[Simba][SparkJDBCDriver](500051) ERROR processing query/statement when reading from a CSV file in S3 mount point on DBFS in the case of a DLP target instance.
Fixed an issue with the ELT SCD2 Snap where the Snap was rounding off decimal values to the nearest integer—the value 57.601000000000 in the source table was written to the target table as 58.000000000.
Introducedthe following new ELT Snaps:
ELT Cast Function: Snap to convert a data type of a column in the input SQL string into other supported data types.
ELT String Function: Snap to support the various string functions supported by the different databases.
ELT Router: Snap to enable routing input SQL queries into multiple output views based on the given conditional expressions.
Enhanced the following Snaps to display the final SQL query in their output preview upon Pipeline validation.
Improved usability of the suggestions features for these Snaps by making them case-insensitive. For example, typing default in the Schema Name field displays both default and DEFAULT, if they co-exist. You do not need to type DEFAULT to invoke and select the schema name DEFAULT from the suggestions list.
Enhanced the ELT SCD2 Snap to address different feature requests and issues raised by multiple customers. These changes provide more flexibility in configuring your SCD2 operations using this Snap.
Removed Check for nulls and duplicates in the source field and added two dropdown lists - Null Value Behavior and Invalid Row Handling.
Made the following items in the Meaning field of the Target Table Temporal Fields fieldset mandatory while making the Invalid historical rowsflag optional.
Enhanced the ELT Aggregate Snap to support the following GROUP BY features across all supported CDWs:
Group by Cube
Group by Grouping Sets
Group by Rollup
Automatic GROUP BY for all input columns.
Fixed an issue with ELT Merge Into Snap where the Snap erroneously modified the target table column name when the column name contained the target table name.
Fixed an issue in ELT SCD2 Snap where the Snap causes incorrect results with Snowflake targets, when:
The Historical Row End Date value is provided.
Nulls and Invalid rows are recognized, but one or more start dates in the source are null.
Fixed the issue in ELT Transform Snap where the Output Schema of the Snap does not populate all the column names from its Input Schema.
Fixed the issue with ELT Merge Into Snap where the Snap erroneously modified the target table column name when it contained the target table name, due to a misinterpretation of the target table name aliases.
Updated ELT SCD2 Snap to address different feature requests and issues raised by multiple customers. These changes provide more flexibility in configuring your SCD2 operations using this Snap.
Removed Check for nulls and duplicates in source field and added two dropdown lists - Null Value Behavior and Invalid Row Handling.
Introduced a new Snap ELT Router to enable routing input SQL queries into multiple output views based on the given conditional expressions.
Subquery Pushdown Optimization: SnapLogic now optimizes SQL queries before they are passed to the CDW to ensure the queries are performant and cost-efficient in the respective CDW. An SQL subquery means a query inside a query. Pushdown optimization refers to rewriting these incremental (nested) SQL queries produced in your ELT Pipeline to form a more optimal/performant version.
ELT Math Functions: Snap to perform mathematical—arithmetic, logarithmic, trigonometric, exponent, root, rounding, and truncation—operations on data.
Enhanced all the expression-enabled fields in ELT Snaps to display suggestions from the Input Schema (emanating from the upstream Snaps) in addition to the existing standard SQL expressions and functions list.
HAVING clause within GROUP BY clause, when the WHERE clause cannot be used.
GROUP BY ROLLUP.
New aggregate functions for DLP: ANY, SOME, KURTOSIS, and STDDEV.
Enhanced the ELT Load Snap to support loading data into BigQuery targets from S3 buckets and Redshift CDW. These load operations use the BigQuery Data Transfer Service (DTS) client libraries and are carried out in asynchronous mode.
Enhanced the ELT Load and ELT Insert Select Snaps with a new fieldset Table Options List to support defining the Table Options for creating a new table in your target CDW.
Enhanced the ELT Select Snap to support Common Table Expression (CTE)-based SQL queries that contain a WITH clause inside the SQL Query Editor field, when your target CDW is Azure Synapse.
Fixed the issue with ELT SCD2 Snap where the Snap did not equate null values in the corresponding cause-historization rows of both the source and target tables (with no other changes to data in the remaining fields) as the same and produced duplicate rows in the target table, as a result. After this fix, the Snap does not cause any new duplicate rows in the target table.
Fixed the issue with ELT Load Snap where the Snap fails with the error Database encountered an error during Bulk Load process when you specify a CSV file to load data from, with the Load Action as Alter Table. The Snap now performs the specified ALTER TABLE actions—ADD/DROP columns—and loads the data into the target table accordingly (without the need to manually modify the source or target tables beforehand).
Fixed the issue with ELT SCD2 Snap where the Snap failed when you define more than one TargetTable Natural Key in the Snap configuration to load SCD2 data into the target CDW instance.
Fixed an issue with the ELT SCD2 Snap where the Snap failed to update the previous current rows in the target SCD2 table to historical rows when you define an End Date of Historical Row in Target Table Temporal Fields.
Fixed an issue with the ELT SCD2 Snap where the Snap failed to insert new rows when you define the values that exist in the most recent historical rows of the target SCD2 table as the cause-historization values.
Fixed the following issues with the ELT SCD2 Snap:
Where the Snap failed to get the right data type due to column name case mismatches between what is used in the Snap and what is actually used in the Azure Synapse tables (returned by the JDBC driver). You no longer need to type the column names in the exact case that Azure Synapse expects.
The Snap failed with the error—start_date does not exist—while writing SCD2 data to a Redshift table column start_date that is specified as the StartDate of Current Row in the Target Table Temporal Field(s) field set.
The Snap failed with the error—Reference 'END_DATE' is ambiguous—while merging SCD2 updates into DLP tables.
Where the Snap failed due to lack of required access privileges on the target database (for example, create table rights to create temporary tables as needed). The Snap now runs the input SQL statement and the elaborate sub-queries instead of attempting to create a temporary table in such scenarios.
Fixed the issue with the ELT SCD2 and ELT Load Snaps that fail to perform an add/drop operation involving multiple columns on a Redshift target table.
Enhanced the ELT Aggregate Snap to support COUNT_IF aggregate function for Redshift and Azure Synapse target databases. Eliminating duplicates with COUNT_IF aggregate function Note: Selecting the Eliminate Duplicates checkbox while using COUNT_IF aggregate function does not eliminate duplicate records in case of Snowflake and BigQuery databases, as there is no native support for this feature. However, for Redshift, Azure Synapse and Databricks Lakehouse Platform (DLP), the duplicates are eliminated from the list of records when you select this checkbox for COUNT_IF function.
Updated the Expressions and Functions Supported for ELT in the Snap and Account configuration sections. This list is common to all target CDWs supported. You can use these expressions to define your Snap or Account settings with the Expression symbol = enabled, where available.
Enhanced the ELT Load Snap to ensure that the Snap uses the default S3 Folder name specified in the Snap's account to accurately resolve the defined File Name Pattern.
Enhanced the ELT Select and ELT Execute Snaps to allow SQL comments inside the SQL Query Editor and SQL Statement Editor fields respectively.
Enhanced the ELT Transform Snap to display the exact data types of fields listed in the Input Schema and Target Schema in case of Azure Synapse.
Enhanced the ELT Merge Into Snap to to support MERGE INTO ALL option and automatic source table aliasing.
Enhanced the ELT Load Snap by adding the Source File to Target Table Columns Map field set to enable mapping of columns between the source file and the target table. In case of Databricks Lakehouse Platform (DLP) the Snap is enhanced further to support delimiters other than the comma in the source CSV files.
Enhanced the ELT Join Snap to support Left Anti and Left Semi join types in BigQuery though it does not natively support these join types.
Enhanced the underlying load mechanism for ELT SCD2 Snap from Insert-and-Update mode to Merge-into mode to substantially improve the Snap's performance while working with large and very large volumes of data (upwards of 500M rows or 50GB size).
Fixed an issue withELT TransformSnap where it may display incorrect schema only in the previews (during Pipeline validation). This occurs especially when the incoming SQL statement (defined in theSQL Statement Editorof the upstream Snap) contains one or more of the WHERE, GROUP BY, HAVING , ORDER BY, LIMIT, LIMIT followed by OFFSET, and SAMPLE clauses. Here are a few Pipeline scenarios where this issue might surface:
Fixed an issue where the ELT Load Snap connecting to a Databricks Lakehouse Platform (DLP) instance failed to perform the load operation. Ensure that you provide a valid DBFS Folder path in the Snap's account settings as the Snap requires this folder path.
Enhanced the ELT Database Account to support token-based authentication (Source Location Session Credentials) to S3 locations for Snowflake and Redshift target databases.
GROUP BY Fields Listfield set > Output Field to GROUP BY Field.
ORDER-By Fields to ORDER-BY Fields (Aggregate Concatenation Functions Only).
Removed the Suggestion option for Field Name field under General Aggregate Functions List field.
Made the Alias Name fields in the Aggregate Concatenation Functions List and the Percentile Distribution Functions List field sets mandatory.
If your target database is a Databricks Lakehouse Platform (DLP) instance, then the ELT Load Snap supports loading data from source CSV files that contain only comma as the separator between values.
Enhanced the ELT Database Account to allow parameterization of field values using Pipeline Parameters. You can define and use these parameters in expression-enabled fields to pass values during runtime.
Fixed the following Known Issues recorded in the 4.26 GA version:
For a Snowflake target instance, the ELT Insert Select Snap does not suggest column names to select for the Insert Column field in the Insert Expression List.
Intermittent null-pointer exceptions in the ELT Load Snap on Databricks Lakehouse Platform (DLP).
The ELT Insert Select Snap attempts to create the target table even when it exists in the Snowflake database.
When loading data from a JSON file into a target Databricks Lakehouse Platform (DLP) instance using an ELT Load Snap, if you choose the Drop and Create Table option as the Load Action and specify an additional column (that is not available in the JSON file) for the new table, it results in one more column null added to the new target table.
When you use the SQL editor in the ELT Select Snap configuration to define your SQL query, the Pipeline validation fails due to a syntax error in the following scenarios. However, the Pipeline execution works as expected. The only workaround is to drop the LIMIT clause and the optional OFFSET clause from the SQL query during Pipeline validation.
The query contains a LIMIT clause on a Snowflake, Redshift or Databricks Lakehouse Platform target instance: The SQL query created during Pipeline validation includes an additional LIMIT clause, for example: SELECT * FROM "STORE_DATA"."ORDERS" LIMIT 10 LIMIT 990.
The query contains an OFFSET clause (supported in case of Snowflake and Redshift): The SQL query created during Pipeline validation looks like SELECT * FROM "STORE_DATA"."ORDERS" LIMIT 10 OFFSET 4 LIMIT 990.
Enhanced the ELT Snap preview to support the following Snowflake data types:array,object,variant, andtimestamp.
The Snaps convert the values to hexadecimal (HEX) equivalents—the default setting for the session parameter BINARY_OUTPUT_FORMAT in Snowflake. SeeSession Parameters for Binary Valuesfor more information.
If this setting is different from hexadecimal (such as base64) in the Snowflake table, the Snaps still convert the values to hexadecimal equivalents for rendering them in the Snap preview.
Enhanced all ELT Snaps to display the Get preview datacheckbox below the Snap'sLabelfield.
Starting with the 4.26 release, all Snaps in the ELT Snap Pack(except the ELT Copy Snap) require an account to connect to the respective target database. Your existing Pipelines that do not use an account may fail. We recommend you to associate an ELT Database Account to each of the ELT Snaps (except ELT Copy Snap) for your Pipelines.
Enhanced theELT AggregateSnap to support Linear Regression functions on Redshift and Azure Synapse. The Snap also supports these functions on Databricks Lakehouse Platform.
Enhanced theELT ExecuteSnapto enable running multiple DML, DDL, and DCL SQLstatements from the same Snap instance.
Support LEFT ANTI JOIN and LEFT SEMI JOIN types on all supported databases.
Display or hide the Resultant Column Names Prefix Typefield based on the target database selected in the Snap's account.
Enhanced the ELT Load and ELT SCD2 Snaps to provide a list of suggested data types, while adding columns to or creating a table.
Updated the ELT SCD2 Snap to replace End date of historical row option in the Meaning field of Target Table SCD2 Fields field set with End Date of Current Row.
This may cause the existing Pipelines to fail as the End date of historical row option no longer exists.
You need to make the following update in the ELT SCD2 Snap's settings across your Pipelines after upgrading your Snap Pack to this patch:
Select End Date of Current Row from the Meaning drop-down list in the second entry (highlighted in the image).
Fixed the issue with the ELT Insert-Select Snap containing an open output preview that fails to retrieve output preview data in case of Redshift and Azure Synapse databases, though the Pipeline runs work as expected.
Fixed an issue where the ELT Execute Snap does not error out (Snap turns Green) even when running an SQL query to drop a non-existent table from a Snowflake or Azure Synapse database.
[Update on ]: Enhanced the ELT Snap previews to support the following data types: array, object, variant, and timestamp.
The Snaps convert the values to hexadecimal (HEX) equivalents—the default setting for the session parameter BINARY_OUTPUT_FORMAT in Snowflake. See Session Parameters for Binary Values for more information.
If this setting is different from hexadecimal (such as base64) in the Snowflake table, the Snaps still convert the values to hexadecimal equivalents for rendering them in the Snap previews.
Enhanced the ELT Snap preview to display the exact binary and varbinary values from Snowflake database during Pipeline validation, by converting the values to hexadecimalequivalents—the default setting in Snowflake. If the setting is different from hexadecimal in the Snowflake table, then the Snaps still convert the values to hexadecimal for rendering the Snap preview.
Enhanced theELT TransformSnap to display the appropriate data type (binaryorvarbinary) for the column names populated in the output schema.
Enhanced theELT Window FunctionsSnap to address potential issues due to an incorrect definition for MINUS function in case of Redshift and Azure Synapse databases.
Support for Google Cloud Storage as a storage location (source)in addition to AWS S3 and Azure Data Lake Storage (ADLS) when your target database is Snowflake.
Automatic downloadof the JDBC driver required for the selectedDatabase Type using the new Download JDBC Driver Automaticallycheck box.
Enhanced theELT LoadSnap to prevent changes to existing tablesduring Pipeline validation.If you set the Load Action as Drop and Create table, and the target table does not exist,the Snap creates a new (empty) target table based on the schema specified in its settings.
Enhanced theELT Window FunctionsSnap to support Covariance, Correlation, and LinearRegression Functions on Snowflake, Redshift, and Azure Synapse databases. The Snap uses function-specific query re-writes to support these functions on Redshift and Azure Synapse databases.
Enhanced theELT Merge IntoandELT Insert SelectSnaps to support up to one output view, and addedtheGet Preview Datacheck box to these Snaps. You can now connectdownstreamELT Snaps to these Snaps.
Fixes the issue of production job failures due to ELT Insert Select Snap after upgrading to 4.24 GA by updating the ELT Transform Snap to continue allowing duplication of fields in the Expression list for the Pipeline to complete successfully.
No changes are needed to your existing Pipelines.
Fixes the column name collision issue in the Snap's output when the two tables being joined have columns with the same/identical names. You can specify the extent of prefix (that is, to prefix all columns, only duplicate columns, or no prefix) using the Resultant Column Names Prefix Type drop-down list. Based on the prefix you choose, a table alias name is prefixed to the identical columns in the output.
The behavior of ELT Load Snap for Load Action during Pipeline validation across the supported databases is as follows:
Append rows to existing table: Does not append the data from the source files into the target table.
Overwrite existing table: Does not overwrite the data.
Drop and Create table: Does not drop the target table even if it exists, but the Snap creates a new target table if a table does not exist.
Alter table: Does not modify the schema of the target table.
Adds support for Azure Synapsedatabase. You can now use theELT Snap Packto transform tables in the Snowflake, Redshift as well as Azure Synapse databases.
Updates the Snap Pack with the following features:
Suggest appropriate column names to select from, in the Snap fields.
Create Hash-distributed tables using the Target Table Hash Distribution Column (Azure Synapse Only) field when theLoad Actionis selected asDrop and Create tableand a condition like WHEN NOT MATCHED BY TARGET.
Enhances the Snap to support Natural JOINS (NATURAL INNER JOIN, NATURAL LEFT OUTER JOIN, NATURAL RIGHT OUTER JOIN, and NATURAL FULL OUTER JOIN) in addition to the INNER, LEFT OUTER, RIGHT OUTER, FULL OUTER, and CROSS Joins in Azure Synapse Database. This enhancement also makes account configuration mandatory when using this Snap.
Fixes the column name collision issue in the Snap's result set when the two tables being joined have columns with the same/identical names. You can specify the Resultant Column Names Prefix Type drop-down list. Based on theprefix typeyou choose, a table alias name is prefixed to identical columns in the output.
Suggest appropriate column names to select from, in the Snap fields. This applies to Snowflake, Redshift, and Azure Synapse databases.
Include theTarget Table Hash Distribution Column (Azure Synapse Only) field for the Snap to create hash-distributed tables always.
Include theUpdate Expression List - When Not Matched By Source field set to allow defining one or more Update Expressions for the WHEN clause - WHEN NOT MATCHED BY SOURCE. This applies toAzure Synapse database.
Include theTarget Table Aliasfield to specify the alias name required for the target table. The Snap is also equipped with the ability to auto-replace the actual table names (with the alias name), if any, used in the ON clause condition, secondary AND conditions, Update Expression list, or Insert Expression list. This applies to Snowflake, Redshift, and Azure Synapse databases.