...

Error

Reason

Resolution

Missing property value

You have not specified a value for the mandatory field where this message is displayed.

Ensure that you specify appropriate values for all mandatory fields in the Snap configuration.

Invalid placement of ELT Cast Function snap

ELT Cast Function Snap cannot be used at the start of a Pipeline.

Move the ELT Cast Function Snap to either the middle or the end of the Pipeline.

CAST (DEPT AS BINARY) is not a natural function for Snowflake. It displays the following error message. net.snowflake.client.jdbc.SnowflakeSQLException: The following string is not a legal hex-encoded value: 'operat'
(CDW: Snowflake)

In the SQL query, SELECT CAST(DEPT AS BINARY) AS dept_binary FROM BIGDATAQA.TEST_DATA.org10, CAST (DEPT AS BINARY) is not a natural function for Snowflake.

Instead of (DEPT AS BINARY), you can use:

to_binary
as_binary

Examples:

insert into TEST_DATA.demo_binary_hex (b) select to_binary(hex_encode('LOGO'), 'HEX');
select as_binary(binary1) as "Binary" from TEST_DATA.type_as_binary;

...

Snap Pack History

...

Release
Snap Pack Version
Date
Type
Updates
February 2023
main19844
09 Feb 2023
Stable
Upgraded with the latest SnapLogic Platform release.
December 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.

Versions Compared

Old Version 11

New Version 12

Key

Snap Pack History

New Snap

See Also

Release	Snap Pack Version	Date	Type	Updates
February 2023	main19844	09 Feb 2023	Stable	Upgraded with the latest SnapLogic Platform release.
December 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.

Page Comparison

Versions Compared

Old Version 11

New Version 12

Key

Snap Pack History

New Snap

See Also