427patches13030

• 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.

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.

• 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.

ELT Insert-Select, ELT Merge Into, and ELT SCD2 Snaps show the following statistics on execution.

• Records Added

• Records Updated

• Records Deleted

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.

4.29patches16287

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.

• 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.

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.

  • 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.

• 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.

427patches13923

• 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 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.

427patches13030

• 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).

  Now, you need not worry about typing

  • You no longer need to type the column names in the exact case that Azure

  Synapse expects.

• Fixed the issue with the ELT SCD2 Snap that fails with the error start_date does not exist while writing SCD2 data to Redshift tables based on the start and end dates for the current rows and the invalid historical row flag in the Target Table Temporal Field(s) field set.

• Fixed the issue with the ELT SCD2 and ELT Load Snaps that fail to alter Redshift target tables—add and drop more than one column using a single SQL statement.

• Fixed the issue with the ELT SCD2 Snap while loading data into DLP tables using more than one WHEN MATCHED condition (MERGE INTO WHEN MATCHED… AND… construct) by aliasing each of the target table column names in the SQL statement generated.

• Fixed the issue with the ELT SCD2 Snap where it fails

  • 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.

• 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).

• 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 > …

• 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.
• 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.

• 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. 

• 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.

• 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.

• 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.

• 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.

• 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.

• 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

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.