In this article

Overview

You can use this Snap to implement the Slowly Changing Dimension Type 2 (SCD2) function in your Snowflake, Redshift, Azure Synapse, Databricks Lakehouse Platform, or BigQuery table. Configure the database type and the access to corresponding tables in the Configuring ELT Database Accounts for the Snap.

Internal Change in Snap behavior

Starting from November 2021, 4.27 release, the ELT SCD2 Snap performs a MERGE INTO-based SCD2 operation instead of the previous Insert-and-then-Update approach. It is important that you take notice of this change in behavior. However, if you prefer to use the Insert-and-then-Update approach for your SCD2 operation, define the Pipeline parameter SCD2_INSERTUPDATE_FALLBACK_MODE with a value greater than zero (0), before running your Pipeline.

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, Databricks Lakehouse Platform, or BigQuery.

Limitation

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

If the last Snap in the Pipeline takes 2 to 5 seconds to update the runtime, the ELT Pipeline statistics are not displayed even after the Pipeline is completed. The UI does not auto-refresh to display the statistics after the runtime.
Workaround: Close the Pipeline statistics window and reopen it to see the ELT Pipeline statistics.
When you return to the Snap Statistics tab from the Extra Details tab in the Pipeline Execution Statistics pane, it contains the status bar (Pipeline execution status) instead of the Download Query Details hyperlink and the individual counts of Records Added, Records Updated, and Records Deleted.
ELT Pipelines built using an ELT SCD2 Snap fail to perform the DROP AND CREATE TABLE and ALTER TABLE operations on Delta tables when working with a Databricks SQL persona on the AWS Cloud with the error Operation not allowed: ALTER TABLE RENAME TO is not allowed for managed Delta tables on S3. However, the same action runs successfully with the Data Science and Engineering persona on the AWS Cloud.
- This is due to the limitation with the Databricks SQL Admin Console that does not allow you to add the configuration parameter spark.databricks.delta.alterTable.rename.enabledOnAWS true to its SQL Warehouse Settings.
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 using the ELT SCD2 Snap to load your source data into a Redshift target instance, the Snap fails if you use constant values such as ‘2022-01-01’ or SQL expressions such as GETDATE() and current_time() that evaluate to constant values instead of the target table column names in the Target Table Sort Fields.
In the case of Azure Synapse, if you are configuring the Start_Date column as a Target Table Natural Key, the Snap fails in each of the following scenarios:
- The source table/file contains one or more null values.
- The target table is empty.
- The End date of current row or End date of historical row has a static value, for example: '2021-01-01'.
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.

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)

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 Input and Output

View	Type of View	Number of Views	Examples of Upstream and Downstream Snaps	Description
Input	Document	Min: 0 Max: 1	ELT Join ELT Copy ELT Transform	A document containing an SQL query that yields the historization data to load.
Output	Document	Min: 0 Max: 1	ELT Select	A document containing the status of the SCD2 Load operation along with the SQL query that you can use in downstream ELT Snaps if any.

Snap Settings

SQL Functions and Expressions for ELT

You can use the SQL Expressions and Functions supported for ELT to 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.

Understanding the ELT SCD2 terminology

See ELT SCD2 Scenarios | Terminology for definitions of the terms used in this Snap's configuration.

Parameter Name	Data Type	Description	Default Value	Example
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.	N/A	Orders_SCD2_Data
Get preview data	Checkbox	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 the 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.	Not selected	Selected
Database Name	String/Expression	Specify the name of the database in which the target table exists. Leave this field blank if you want to use the database name specified in the Default Database Name field in the account settings.	N/A	TESTDB
Schema Name (Not applicable to Databricks Lakehouse Platform)	String/Expression/Suggestion	Required. Specify the name of the database schema. Alternatively, click to fetch a list of schemas associated with the database, and select the schema. Ensure that you include the exactly same schema name including the double quotes, if used, when you repeat the schema name in the Target Table Name field. Leave this field blank if your target database is Databricks Lakehouse Platform.	N/A	SALES
Target Table Name	String/Expression/Suggestion	Required. Specify the name of the SCD2 table or view in which you want to load the data. Alternatively, click to fetch a list of table names associated with the schema, and select the required name. Only views that can be updated (have new rows) are listed as suggestions. So, Join views are not included. This also implies that the Snap account user has the Insert privileges on the views listed as suggestions. Ensure that you include the exactly same schema name, if at all, including the double quotes as specified in the Schema Name field. If the target table does not exist during run-time, the Snap creates one with the name that you specify in this field and writes the data into it. During Pipeline validation, the Snap creates the new table but does not write any records into it. The new table thus created will not be dropped in the event of a subsequent/downstream Snap failure during validation. The target table or view should have the following columns for field historization to work: Required. One column to denote the starting date of the current row. For example, "START_DATE". Required. One column to denote when the row was historized. For example, "END_DATE". For an active row, it can be null. For a historical row it has the value that indicates it was effective until that date. Optional. One column to demarcate whether a row is a current row or not. For example, "CURRENT_ROW". For the current row, the value would be true or 1. Required when current row is specified. One column to demarcate whether a row is a historical row or not. For example, "HISTORICAL_ROW". For the historical row, the value would be false or 0. Use the ALTER TABLE command to add these columns in your target table if they are missing.	N/A	SALES_ORDERS
Input Source Alias	String/Expression	Specify an alias name for your source data table.	N/A	src
Target Table Action	Drop-down list	Select the SCD2 action to perform on the target table. Available options are: Merge data into target table. Loads the data from the source files into the specified target table. Drop and Create table. Drops the target table if it exists, creates a new table with the columns provided in the Table Columns field set, and then loads the data from the source files into the target table. Activates the Table Columns field set. Alter table. Modifies the schema of the target table based on the configuration of the Modifier field in the Table Columns field set and loads the data from the source files into the target table. Activates the Table Columns field set with the Column, Data Type, and Modifier fields. Overwrite existing table. Overwrites the target table with the data from the source table/files. During Pipeline Validation The Snap does not modify the existing tables during Pipeline validation, but if the specified target table does not exist, it creates a new target table. Selecting the Target Table Action Use the Drop and Create table option in this field only to create the target table for the first time. After the first execution of your Pipeline, ensure that you change the Target Table Action to Merge data into target table. We recommend your utmost discretion in the intermittent use of the Alter table option (schema changes to target table).	Merge data into target table	Alter table
Table Columns	This field set enables you to configure the schema of the target table. You can use this field set to add or drop columns in the target table if you select the Drop and Create table or Alter table in the Target Table Action field. Click to add a new row in the field set. This field set consists of the following fields: Column Data Type Modifier
Column	String/Suggestion	Specify the name of the column that you want to load in the target table. You can also specify the columns to drop if you select the Alter table option in the Target Table Action field.	N/A	ID FNAME
Data Type	String	Activates when you select the Drop and Create table or Alter table options in the Target Table* Action field*. Specify the data type for the values in the specified column.	N/A	INT VARCHAR
Modifier	Drop-down list	Activates when you select the Alter table option in the Target Table* Action field*. Select whether you want to add/drop the specified column. Available options are: Add Drop	Add	Drop
Null Value Behavior	Drop-down list	Select an option to specify how you want the Snap to treat rows with null values in Natural Key or Cause-Historization fields of the source table. The available options are: Honor nulls as distinct values: To treat each null as a value in the field. And as a result, equate all null values (as the same value) for all practical purposes. Ignore nulls: To treat any rows with nulls in Natural Key and Cause-Historization fields of the source table as absent and proceed with the SCD2 operation based on the values available in the remaining rows. Error on nulls: To abort the Snap execution when a null value is found in the source data. The Snap writes the error (message) to the Pipeline runtime statistics and displays a corresponding error message in the Snap. Refer to the ELT SCD2 scenarios to learn more about the impact of the selection you make in this field.	Honor nulls as distinct values	Ignore nulls
Invalid Row Handling	Drop-down list	Select an option to specify how you want the Snap to treat invalid rows, if any, in the source data. A row is considered invalid when it does not result in a new, valid, and coherent SCD2 entry/row in the target table. The available options are: Ignore invalid rows: To treat invalid rows as absent and proceed with the SCD2 operation based on the valid values available in the remaining fields. The Snap also ignores inserting any potentially invalid rows into the target table. Insert duplicate invalid rows: To treat all rows in a uniform manner—allowing an entry for each invalid row, in the target table— and optionally flagging such invalid entries in the target table as invalid (Invalid = true or 1 or a similar boolean value) based on the Invalid historical row flag specified in the Target Table Temporal Field(s) fieldset. Error on invalid rows: To abort the Snap execution when an invalid row is found in the source data. The Snap writes the error (message) to the Pipeline runtime statistics and displays a corresponding error message in the Snap. Refer to the ELT SCD2 scenarios to learn more about the impact of the selection you make in this field.	Ignore invalid rows	Insert duplicate invalid rows
Target Table Natural Key(s)	Required. This field set enables you to configure the names of fields that identify a unique row in the target table. The identity key cannot be used as the Natural key, since a current row and its historical rows cannot have the same identity value. Click to add a new row in the field set. Specify the value in the following field for each new row added in this field set. Target Table Natural Key(s)
Target Table Natural Key(s)	String/Expression/Suggestion	Specify the name of the Natural Key column from the target table.	N/A	CUSTOMER_ID
Target Table Cause-Historization Field(s)	Required. This field set enables you to configure the names of fields where any change in value causes the historization of an existing row and the insertion of a new 'current' row. Click to add a new row in the field set. Specify the value in the following field for each new row added in this field set. Target Table Cause-Historization Field(s)
Target Table Cause-Historization Field(s)	String/Expression/Suggestion	Required. Specify the name of the historization-causing column from the target table.	N/A	LAST_ORDER_AMT
Target Table Temporal Field(s)	Required. This field set enables you to configure the historical and updated information for the Cause-historization field. Click to add SCD fields. Specify the values in the following fields for each SCD field row in the field set. Meaning Field Value
Meaning	Drop-down list	Required. Select one from the following six permitted values for this field in each row. At the minimum, ensure that you configure appropriate values for the first three meanings, only one of the end dates, and the flag in this list. Current row (Required) Historical row (Required) Start date of current row (Required) End date of current row (Selected, by default) End date of historical row Remove/replace End date of current row with this item, if you want to specify this date. Invalid historical rows flag (Optional) Breaking change In 4.26 GA, we have updated the last option in this field—Invalid historical row flag—and it may cause your Pipelines to fail. See Snap Pack History for more information.	Five rows with one list item in each row (without repetition and End date of historical row). .	Start date of current row
Field	String/Expression/Suggestion	Required. Configure the field name from the target table that would contain the historical information. Click to select an item from the suggested list of items. For Current row: The name of the column in the target table that holds the flag for the historized field. For Historical row: The name of the column in the target table that holds the flag for the historized field. It has to be the same as the value configured for the Current row field. For Start date of current row: The name of the column in the target table for denoting the start date for the current row. For End date of current row: The name of the column in the target table for denoting the end date for the current row. In this case, the start date of the current row specified above is automatically filled in as the end/closing date for the matching historical record. For End date of historical row: The name of the column in the target table for denoting the end date for the historical rows. If you are specifying the End date of historical row, we recommend that you specify an appropriate expression in the corresponding Value or Expression field. See Expressions to use for End date of Historical Row to choose the right expression from a set of expression templates. For Invalid historical row flag: The name of a nullable column that holds the flag for an invalid historical row. If you specify a non-null value for this field: While inserting an invalid row in the target table, the Snap inserts the specified value in the Invalid Row flag column to indicate that the row is invalid. If no invalid rows are inserted, the Invalid Row flag column value remains empty for all valid rows. See the Troubleshooting section in this document and ELT SCD2 Scenarios to ensure that you have configured these fields in your Snap's settings properly. The date fields must be configured with a field and a corresponding value. Pass null as the value to indicate the field's value is null.	N/A	Start_Date End_Date Current_Row Historical_Row
Value or Expression	String/Expression	Required. Configure the value for each of the fields selected in the Field field. The date fields must be configured with a field and a corresponding value. Pass null as the value to indicate the field's value is null. For the End date of historical row, we recommend that you specify an appropriate expression in this field. See Expressions to use for End date of Historical Row to choose the right expression from a set of expression templates.	N/A	For date fields: src.start_date, '2021-12-31', CURRENT_DATE, null For row fields: 'true' or 'false'
Target Table Sort Field(s)	Required. This field set enables you to sort the records in the target table, by one or more historization fields (for example, by natural key). This mapping is for columns that are not part of the Target Table Temporal Fields. Click to add a sort order. Specify the values in the following fields for each sort order. Field Sort Order Null Value Sort Preference
Sort Field	String/Expression	Specify the column by which to sort the target table data set.	N/A	start_date GENDER
Sort Order	Drop-down list	Choose either of the sort orders—ASC (ascending) or DESC (descending)—for the target table data set.	ASC	DESC
Null Value Sort Preference	Drop-down list	Choose the position for the null values, if any, in the selected column while sorting—NULLS FIRST (at the beginning) or NULLS LAST (at the end). This field is not applicable if you are working with an Azure Synapse target database.	NULLS LAST	NULLS FIRST
Target-Source Column Mapping	This field set enables you to define a one-to-one mapping between the columns in the source and target tables for inserting new rows into the target table. Click to add a new mapping row. This field set contains the following fields: Target Table Column Name Source Column Mapping Expression
Target Table Column Name	String/Expression/Suggestion	Choose a column from the target table's schema to map with a column from the source data set (schema). Ensure that the target table column name you specify for mapping with a source column is not already configured as a target table SCD field.	N/A	ORD_AMT
Source Column Mapping Expression	String/Expression	Choose a column from the source data set (schema) that corresponds to the respective column in the target table's schema. You can alternatively specify an expression for the source column name. If you have specified the target table column as a target table natural key or a target table cause-historization field already in this Snap, ensure that you specify the corresponding source column mapping expression (not null nor left blank). All other columns not mentioned in the Snap can be mapped to null.	N/A	src.ORD_AMT

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.

Records Added
Records Updated
Records Deleted

You can view more information when clicking the Download Query Details link.

Troubleshooting

Error	Reason	Resolution
Ambiguous SCD column mapping	You cannot map a field that is configured in Target Table SCD Fields again in the Target-Source Column Mapping field set.	Ensure that the target table column name you specify for mapping with a source column is not already configured as a target table temporal field.
Invalid target column - source expression mapping	You cannot map a target table column to null or a blank source column value/expression.	If you have specified the target table column as a target table natural key or a target table cause-historization field already in this Snap, ensure that you specify the corresponding source column mapping expression (not null nor left blank). All other columns not mentioned in the Snap can be mapped to null.
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.
Invalid row flag not configured for target table	Invalid historical row flag is not defined in Target Table Temporal Field(s) field set.	Add an entry in Target Table Temporal Field(s) field set for the Meaning Invalid historical row flag with the corresponding field name in the target table and the boolean value (true or false) or an expression that resolves to a boolean true or false. Make sure that the target table contains the specified field.
Target table sort column %s not mapped to a source column or an expression	Target table sort fields should be mapped to a source column or an expression in one of the two places: Target Table Temporal Field(s) field set or Target-Source Column Mapping field set.	Map this column either in Target Table Temporal Field(s) field set or Target-Source Column Mapping field set before you can use it for sorting.
Invalid end date configuration for SCD2	You have provided both End date of current row and End date of historical row while one of them is adequate.	Remove one of End date of current row and End date of historical row from the Target Table Temporal Field(s) field set and try again.
Ambiguous source data (Only when you configure the Snap to check for nulls and duplicates.)	Source data contains nulls in one or more of columns you specified in the Target Table Natural Key(s) field set, Target Table Cause-Historization Field(s) field set, or the Start date of current row in Target Table Temporal Field(s) field set.	Peruse your source data to ensure that it is valid and contains valid values required to perform the SCD2 operation and try again.
Source data has ambiguous records (Only when you configure the Snap to check for nulls and duplicates.)	Source data contains duplicate keys in one or more of columns you specified in the Target Table Natural Key(s) field set, Target Table Cause-Historization Field(s) field set, or the Start date of current row in Target Table Temporal Field(s) field set.	Peruse your source data to ensure that it is valid and contains only unique entries required for the SCD2 operation in these columns and try again.
Incorrect start date in source data (Only when you configure the Snap to check for nulls and duplicates.)	The column you specified for the Start date of current row in Target Table Temporal Field(s) field set has one or more nulls.	Ensure that your source data or the start date SQL expression resolves to a valid start date and try again.
Incorrect start date(s) in source data (Only when you configure the Snap to check for nulls and duplicates.)	Multiple rows for one or more Target Table Natural Keys that you defined contain the same start date.	Ensure that your source data contains only unique start dates across the rows for the same natural key. Perform this check for all natural keys you specified, before trying again.
Scenario: Sort order by a date column in the target table is incorrect after the SCD2 operation is complete.	Sorting the date values in a column with date data type may yield different results when compared to sorting the same date values in a column with string data type.	Ensure that the values loaded in the target table column are of the same data type as defined for the respective column/field.
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.
[Simba][SparkJDBCDriver](500051) ERROR processing query/statement. Error Code: 0 Cannot create table ('<schema name>`.`<table name>`'). The associated location (`…<table name>`) is not empty but it's not a Delta table (Target CDW: Databricks Lakehouse Platform)	The specified location contains one of the following: A non-Delta table (such as CSV, ORC, JSON, PARQUET) A corrupted table A Delta table with a different table schema So, the Snap/Pipeline cannot overwrite this table with the target table as needed.	Ensure that you take appropriate action (mentioned below) on the existing table before running your Pipeline again (to create another Delta table at this location). Move or drop the existing table from the schema manually using one of the following commands: Access the DBFS through a terminal and run: `dbfs mv dbfs:/<current_table_path> dbfs:/<new_table_path>` to move the table or `dbfs rm -r dbfs:/<table_path>` to drop the table. OR Use a Python notebook and run: `dbutils.fs.mv(from: String, to: String, recurse: boolean = false): boolean` to move the table/file/directory or `dbutils.fs.rm(dir: String, recurse: boolean = false): boolean` to drop the table/file/directory.

Examples

Loading Historical Data From an S3 Bucket to a Snowflake Table

The following Pipeline is designed to view and capture Type 2 SCD data from a folder in S3 bucket and load it into the target table in Snowflake database.

We configure an ELT Select Snap to capture SCD2 data from the source table SCD2_NEW_DATA in S3. The image below depicts the data available in the source table.

ELT Select Snap

Snap Output

We use this Snap's output as the input to an ELT SCD2 Snap and configure this Snap to update the table SCD2_TRG_TEST5 residing in the Snowflake database.

The image below depicts the data available in the target table, before the data load.

Existing Data in Target Table

ELT SCD2 Snap

Notice the difference in the number of rows and the values in the END_DATE and the CURRENT_ROW columns. There is only one row with CURRENT_ROW = true in the updated table for each unique CUSTOMER_ID.

Downloads

Important Steps to Successfully Reuse Pipelines

Download and import the Pipeline into SnapLogic.
Configure Snap accounts as applicable.
Provide Pipeline parameters as applicable.

	File	Modified

No files shared here yet.

Snap Pack History

Click here to expand...

Release	Snap Pack Version	Date	Type	Updates
November 2024	main29029	13 Nov 2024	Stable	Updated and certified against the current SnapLogic Platform release.
August 2024	438patches28010	10 Sep 2024	Latest	The ELT Insert-Select Snap no longer fails to execute SQL statements that contain multiple multiline comment character pairs (/* and */) and/or multiple quoted substrings. Quoted substrings refer to schema, database, table, or column identifiers, which are delimited to allow special characters. We recommend that you upgrade your main27765 (August 2024 GA release) ELT Snap Pack to this latest version.
August 2024	main27765	21 Aug 2024	Stable	Upgraded the jOOQ library for the ELT Snap Pack from v3.9.1 to v3.17.x.
May 2024	437patches27372	23 Jul 2024	Latest	Enhanced the pipeline execution statistics of ELT Insert-Select Snap to be displayed in its output view and to allow downloading detailed stats as a JSON file that includes additional statistics (`extraStats`) on DML statement executions on target Databricks Lakehouse Platform (DLP) tables.
May 2024	437patches27246	16 Jul 2024	Latest	Enhanced the ELT Execute Snap to display SQL execution statistics in the pipeline execution statistics and the output view of the Snap for all SQL statements executed. The Snap also allows you to download detailed stats as a JSON file that includes additional statistics (`extraStats`) on DML statement executions on target Databricks Lakehouse Platform (DLP) tables.
May 2024	437patches26846	24 Jun 2024	Latest	Fixed the issue with the ADLS Gen 2 account connection where some conflicts between internally used Azure libraries prevented the ELT Load Snap from reading files.
				Fixed an issue with the ELT Merge Into Snap where the Snap’s SELECT SQL statement could not fetch the target tables information from the PG_TABLE_DEF catalog table of the Amazon Redshift instance. The SELECT SQL statement now uses lowercase for both non-delimited and delimited table names and excludes double quotes from delimited table names to fetch the values from the catalog table. PG_TABLE_DEF is a Redshift system catalog table that contains information about the tables including table names, column names, data types among their other metadata.
				Enhanced the ELT Load Snap to support loading data from nested AVRO, JSONLines, ORC, or Parquet files in Azure storage to the target tables in a Databricks Lakehouse Platform (DLP) instance. Only two Load actions are supported: Drop and create table and Append table.
May 2024	main26341	08 May 2024	Stable	Fixed an issue where the ELT Merge Into Snap failed to load data into the Google BigQuery target table because of the error: `Number of source column expressions in the input SQL is greater than the target table columns` (even after altering the target table schema to match the source table columns). The resolution was to modify an SQL statement that was used internally to achieve the merge action.
February 2024	436patches25953	12 Apr 2024	Latest	Enhanced the ELT Load Snap’s capabilities to allow loading flat and nested data sets (from canonical and non-canonical formats) from your Parquet files to the target tables in Snowflake. Learn more about the usage of this feature at Load data from Parquet files and in the following example Pipelines: Load data from a canonical format Parquet file Load data from a non-canonical format Parquet file
February 2024	main25112	14 Feb 2024	Stable	Updated and certified against the current SnapLogic Platform release.
November 2023	435patches24461	11 Jan 2024	Latest	Fixed an issue with the ELT Merge Into Snap that caused inaccurate aliases and table identifiers in its generated SQL statement.
November 2023	435patches23671	08 Nov 2023	Latest and Stable	Enhanced the ELT Merge Into Snap to support defining the update expressions for updating tables in your target Databricks Lakehouse Platform instance using the WHEN NOT MATCHED BY SOURCE clause, besides other supported clauses and expression lists.
November 2023	main23721	08 Nov 2023	Stable	Enhanced the ELT Merge Into and ELT SCD2 Snaps to display detailed pipeline execution statistics about the data loaded to target tables on a Databricks Lakehouse Platform instance. In addition to the individual counts of rows inserted, updated, and deleted that the ELT Merge Into Snap covers, the ELT SCD2 Snap also reports the count of source rows rejected.
August 2023	main22460	16 Aug 2023	Stable	Upgraded the JDBC driver support for Snowflake to snowflake-jdbc-3.13.33.jar. Updated the ELT Snowflake Account to append the connection parameter `application` in its JDBC URL with the value `SnapLogic_iPaaS`.
May 2023	N/A	31 May 2023	Stable	Fixed a null pointer exception so no 5XX errors can occur if you download non-existent query details from the Pipeline Execution Statistics of an ELT (write-type) Snap.
May 2023	main21015	10 May 2023	Stable	Upgraded with the latest SnapLogic Platform release.
February 2023	432patches20978	04 May 2023	Latest	Fixed an issue with the ELT SCD2 Snap where the `COLLATE` column constraint (used in the new target table definition for Snowflake) resulted in an incorrect syntax internally, causing the pipeline to fail. The load operation succeeds with this fix.
February 2023	main19844	09 Feb 2023	Stable	Upgraded with the latest SnapLogic Platform release.
November 2022	431patches19240	08 Dec 2022	Latest	SnapLogic upgraded the default JDBC Driver versions used to connect the ELT Snaps with the supported CDWs. The ELT Merge Into Snap displays the individual record counts inserted, updated, and deleted for Snowflake targets in the respective Records Inserted, Records Updated, and Records Deleted parameters of the Snap Statistics tab (on Pipeline execution). With Google deprecating the OAuth out-of-band (OOB) flow, the Refresh Token Accounts defined for connecting your ELT Snaps to the BigQuery instances start failing in a phased manner. We recommend that you immediately modify these Snap account configurations to switch to an Access Token Account or a Service Account from the Refresh Token Account.
November 2022	main18944	10 Nov 2022	Stable	ELT Insert-Select, ELT Merge Into, and ELT SCD2 Snaps show the following statistics on execution. Records Added Records Updated Records Deleted
September 2022	430patches18196	28 Sep 2022	Latest	New Snap 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. Enhancements 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: ELT Insert-Select ELT Load ELT Merge Into ELT SCD2 ELT Select 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 field set 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.
August 2022	main17386	11 Aug 2022	Stable	Upgraded with the latest SnapLogic Platform release.
4.29-Patch	429patches16665	17 Jun 2022	Latest	Enhanced the ELT Snap Pack to support the latest JDBC drivers across CDWs—Azure Synapse, BigQuery, DLP, Redshift, and Snowflake. See Configuring ELT Database Accounts or the respective Account page for the exact versions. Enhanced the ELT Pivot Snap to make the Value List field dynamic. Enhanced the ELT DLP Account to configure S3 Bucket, Azure Storage, and DataLake Storage Gen2 Mounts. Enhanced the ELT Snowflake Account with support for Key Pair Authentication. Enhanced the ELT SCD2 Snap: 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. The ELT Insert Select, ELT Merge Into, ELT Load, and the ELT SCD2 Snaps now run successfully even when the specified target table does not exist. These Snaps now create a new target table if it does not exist during Pipeline validation. The new table thus created will not be dropped in the event of a subsequent/downstream Snap failure during validation. Updated the field names in ELT Aggregate, ELT Cast Function, ELT String Function, and ELT Transform Snaps to maintain consistency.
4.29-Patch	4.29patches16287	28 May 2022	Latest	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.
4.29	main15993	14 May 2022	Stable	Introduced the 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. ELT Load ELT Merge Into ELT Insert Select ELT SCD2 Enhanced the ELT Database Account to support OAuth2-based authentication on the target Snowflake database. Enhanced the ELT Select, ELT Insert Select, ELT SCD2, ELT Merge Into, and ELT Load Snaps to display suggestions on the Schema Name field based on the Default Database Name provided in the Snap Account configuration when the Database Name is not specified in the respective Snap. 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 rows flag optional. Current row Historical row Enhanced the ELT Aggregate and ELT Window Functions Snaps to support the following functions across all supported CDWs: KURTOSIS MODE SKEW 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.
4.28-Patch	428patches15638	20 Apr 2022	Latest	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.
4.28-Patch	428patches15290	24 Mar 2022	Latest	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. Refer to the ELT SCD2 scenarios to learn more. Introduced a new Snap ELT Router to enable routing input SQL queries into multiple output views based on the given conditional expressions.
4.28	main14627	12 Feb 2022	Stable	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. Enhanced all Snaps in the ELT Snap Pack with the ability to push down optimized SQL subqueries along the ELT Pipelines. See Subquery Pushdown Optimization (SPDO) for details. Introduced the following new ELT Snaps: ELT Case Expression: Snap to return the action to perform on an event based on a list of events and respective expected actions. ELT Coalesce: Snap to return the first non-NULL value from a list of arguments. ELT Conditional Functions: Snap to perform unary and binary conditional operations on data. 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. Enhanced the ELT Aggregate Snap to support: 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 Database Account to support these operations. 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.
4.27-Patch	427patches13923	07 Jan 2022	Latest	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).
4.27-Patch	427patches13539	13 Dec 2021	Latest	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 issue with ELT Transform Snap where the Snap does not omit the source columns marked for removal from the output view—using an empty Target Path for one or more columns selected in the Expression field of the Snap’s Mapping Table. See Using Empty Target Paths to Omit Rows from the Snap Output to understand how to perform this operation.
4.27-Patch	427patches13030	20 Nov 2021	Latest	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 Start Date 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.
4.27	main12833	13 Nov 2021	Stable	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. Enhanced the ELT Database Account connecting to a Databricks Lakehouse Platform (DLP) to support two new options - Optimize Write and Auto Compact for creating/replacing a table using any of the ELT Insert Select, ELT Merge Into, ELT Load, and ELT SCD2 Snaps. 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).
4.26-Patch	426patches12534	28 Oct 2021	Latest	Fixed an issue with ELT Transform Snap where it may display incorrect schema only in the previews (during Pipeline validation). This occurs especially when the incoming SQL statement (defined in the SQL Statement Editor of 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: … > ELT Select (with one or more of these clauses used in the SQL Statement Editor) > ELT Transform > … … > ELT Select (with or without using the SQL Statement Editor) > ELT Filter > ELT Transform > …
4.26-Patch	426patches12021	30 Sep 2021	Latest	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.
4.26-Patch	426patches11646	22 Sep 2021	Latest	Enhanced the ELT Database Account to support token-based authentication (Source Location Session Credentials) to S3 locations for Snowflake and Redshift target databases. Enhanced the ELT Aggregate Snap with the following changes: Revised the field labels from: GROUP BY Fields List field 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.
4.26-Patch	426patches11323	17 Aug 2021	Latest	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.
4.26-Patch	426patches11262	16 Aug 2021	Latest	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. The Snaps—ELT Merge Into, ELT Select, ELT Join, and ELT Filter—do not prevent the risk of SQL injection when your target database is Databricks Lakehouse Platform (DLP). 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`.
4.26	main11181	14 Aug 2021	Stable	Enhanced the ELT Snap preview to support the following Snowflake 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 preview. Enhanced all ELT Snaps to display the Get preview data checkbox below the Snap's Label field. The ELT Database account is now mandatory for all Snaps in the ELT Snap Pack. Breaking change 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 the ELT Aggregate Snap to support Linear Regression functions on Redshift and Azure Synapse. The Snap also supports these functions on Databricks Lakehouse Platform. Enhanced the ELT Execute Snap to enable running multiple DML, DDL, and DCL SQL statements from the same Snap instance. Enhanced the ELT Join Snap to: Support LEFT ANTI JOIN and LEFT SEMI JOIN types on all supported databases. Display or hide the Resultant Column Names Prefix Type field 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.
4.25-Patch	425patches10017	17 Jun 2021	Latest	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. Breaking change 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 22 Jul 2021]: 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.
4.25-Patch	425patches9725	19 May 2021	Latest	Enhanced the ELT Snap preview to display the exact binary and varbinary values from Snowflake database during Pipeline validation, by converting the values to hexadecimal equivalents—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 the ELT Transform Snap to display the appropriate data type (binary or varbinary) for the column names populated in the output schema. Enhanced the ELT Window Functions Snap to address potential issues due to an incorrect definition for MINUS function in case of Redshift and Azure Synapse databases.
4.25	main9554	08 May 2021	Stable	Starting with the 4.25 release, SnapLogic has now certified the ELT Snap Pack to work with Snowflake hosted on Google Cloud Platform (GCP) as the target database, in addition to the other flavors of Snowflake hosted on AWS and Microsoft Azure. Introduced the ELT Execute Snap to enable you to run DML, DDL, and DCL SQL queries in Snowflake in Snowflake, Redshift, and Azure Synapse. Introduced the ELT SCD2 Snap to support Type 2 Slowly Changing Dimensions (SCD2) updates to the target databases—Snowflake, Redshift, and Azure Synapse. Enhanced the ELT Database Account to introduce: 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 download of the JDBC driver required for the selected Database Type using the new Download JDBC Driver Automatically check box. Enhanced the ELT Load Snap to prevent changes to existing tables during 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 the ELT Window Functions Snap to support Covariance, Correlation, and Linear Regression 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 the ELT Merge Into and ELT Insert Select Snaps to support up to one output view, and added the Get Preview Data check box to these Snaps. You can now connect downstream ELT Snaps to these Snaps.
4.24-Patch	424patches8793	04 Mar 2021	Latest	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. Behavior Change 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.
4.24	main8556	14 Nov 2020	Stable	Adds support for Azure Synapse database. You can now use the ELT Snap Pack to transform tables in the Snowflake, Redshift as well as Azure Synapse databases. Updates the Snap Pack with the following features: ELT Database Account: Enhances the ELT Database Account to support the Azure Synapse database. ELT Aggregate: Enhances the Snap to: Support Azure Synapse's T-SQL aggregate functions and the aggregate functions in Snowflake and Redshift databases. General Aggregate Function COUNT_IF in Snowflake. General Aggregate Functions in Snowflake. Linear Regression Aggregate Functions in Snowflake. Aggregate Concatenation Functions in Snowflake, Redshift, and Azure Synapse. Percentile Distribution Functions in Snowflake and Redshift. Suggest appropriate column names to select from, in the Snap fields. This applies to Snowflake, Redshift, and Azure Synapse databases. ELT Insert Select: Enhances the Snap to: 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 the Load Action is selected as Drop and Create table and a condition like WHEN NOT MATCHED BY TARGET. ELT Join: 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 the prefix type you choose, a table alias name is prefixed to identical columns in the output. ELT Load: Enhances the Snap to: Support the File Name Pattern option using Key Based Mechanism for Redshift database. Suggest appropriate column names to select from, in the Snap fields. This applies to Snowflake, Redshift, and Azure Synapse databases. Create Hash-distributed tables using the Target Table Hash Distribution Column (Azure Synapse Only) field when the Load Action is selected as Drop and Create table. ELT Merge Into: Enhances the Snap to: Suggest appropriate column names to select from, in the Snap fields. This applies to Snowflake, Redshift, and Azure Synapse databases. Include the Target Table Hash Distribution Column (Azure Synapse Only) field for the Snap to create hash-distributed tables always. Include the Update 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 to Azure Synapse database. Include the Target Table Alias field 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. ELT Transform: Enhances the Snap to: Display input schema and output schema based on the upstream and downstream Snaps connected to this Snap. Delete fields mentioned in the Expression field from the Snap's output when the mappings have an empty Target Path. ELT Window Functions: Enhances the Snap to support the following Window Functions in addition to the existing ones: Value Based Analytic Functions LEAD and LAG Analytic Functions Fixes the issue of displaying generic error messages for Triggered Task failures with ELT Pipelines by displaying detailed error messages for ease in debugging.
4.23	main7430	14 Nov 2020	Stable	Introduces the following Snaps: ELT Load: Loads data from AWS S3 buckets and Azure clusters into the Snowflake and Redshift tables. ELT Sample: Generates a data subset from the source table. ELT Pivot: Converts row data into column data. ELT Unpivot: Converts column data into row data. ELT Window Functions: Provides support for SQL Window Functions in ELT Pipelines.
4.22	main6403	12 Sep 2020	Stable	Introduces the ELT Snap Pack that provides you with the Extract, Load, and Transform (ELT) capabilities. Use the following Snaps to build SQL queries that are executed in the Snowflake database: ELT Aggregate : Builds SQL query to perform aggregate functions such as SUM, COUNT, MIN, and MAX. Also offers the GROUP BY functionality. ELT Copy: Creates copies of the input SQL query. ELT Filter: Adds a WHERE clause in the input SQL query. Use this capability to create filters/conditions for your data set. ELT Insert Select: Performs the INSERT INTO SELECT operation on the specified table. ELT Intersect: Adds an INTERSECT SQL operator in the input queries. ELT Join: Builds SQL query with a JOIN clause. ELT Limit: Adds a LIMIT clause in the incoming SQL query. ELT Merge Into: Performs the MERGE INTO operation on the specified table. ELT Minus: Adds a MINUS SQL operator in the input queries. ELT Select: Builds an SQL SELECT query and provides a built-in SQL query editor that enables you to construct complex queries. ELT Sort: Adds the ORDER BY keyword in the input query. ELT Transform: Builds transformation-based SQL queries for the specified table. ELT Union: Adds a UNION ALL or UNION DISTINCT operator in the input queries. ELT Unique: Builds a SELECT DISTINCT SQL query.

ELT SCD2