ELT Aggregate

In this article

An account for the Snap

You must define an account for this Snap to communicate with your target CDW. Click the account specific to your target CDW below for more information:

Overview

Use this Snap to add aggregate functions such as COUNT, SUM, MIN, and MAX along with the GROUP BY clause in the incoming SQL query. GROUP BY clauses are used to group table records based on columns that contain classifying data and are optional in this Snap. This Snap also allows you to preview the result of the output query. You can validate the modified query using this preview functionality. The supported aggregate functions vary based on the account configuration. See the description of the Aggregate Function field in the Snap Settings section for details.

Prerequisites

None.

Limitations

  • Only those columns that are referenced in this Snap are passed to the output. The rest are dropped. 

  • 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 you define a HAVING predicate in the HAVING Predicate List field set of the ELT Aggregate Snap (to apply on the Snap’s output data), the Snap connected downstream of this ELT Aggregate Snap returns an error corresponding to this predicate during the Pipeline execution.

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.

  • Due to an issue with Databricks Runtime Version 11 and above, the Snap fails to calculate the value for the linear regression aggregate function REGR_R2 for the target DLP instance and returns a cast exception. As a workaround, you can revert your Databricks Runtime Version to 10.5 or below.

  • When running without Sub-Query Pushdown Optimization (SPDO), ELT Pipelines that contain an ELT Aggregate Snap and configured with one or more GROUP BY ROLLUP fields, do not verify the column data types while inserting the Snap output values in the target table. This may lead to incorrect data written to the target table. However, as long as SPDO is on, the same Pipeline runs without this issue.

Snap Input and Output


Input/OutputType of ViewNumber of ViewsExamples of Upstream and Downstream SnapsDescription
Input 

Document

  • Min: 1
  • Max: 1
  • ELT Select
  • ELT Transform
The SQL query in which to add the aggregate functions and the optional GROUP BY clause.
Output

Document

  • Min: 1
  • Max: 1
  • ELT Insert-Select
  • ELT Limit

The modified SQL query with the aggregate functions and GROUP BY clause. 

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.


Parameter NameData TypeDescriptionDefault ValueExample 
LabelString

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.

ELT AggregateAggregate Revenue
Get preview dataCheckbox

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 selectedSelected
General Aggregate Functions List

This field set enables you to specify the columns for which to add the aggregate functions. Each aggregate function must be specified as a new row. Click to add a row.

This field set contains the following fields:

  • Function
  • Argument
  • Eliminate duplicates
  • Alias Name
Function*String/Expression/Suggestion

Required. Select the aggregate function to use. Click  to retrieve the list of the supported aggregate functions. The list displays the functions supported by the database that you select in the Account settings. An error is displayed if an account is not configured. See the Troubleshooting section for details. 

Alternatively, you can also enter the name of the aggregate function to use; you must, however, ensure that it is spelled correctly. Otherwise, the Snap displays an error. 

You can use the following aggregate functions:

  • AVG
  • COUNT
  • COUNT_IF
  • MAX
  • MIN
  • SUM
  • SKEW
  • MODE
  • KURTOSIS

Additionally, the following aggregate functions are available based on the database type:

SnowflakeRedshiftAzure Synapse
  • ANY_VALUE
  • BITAND_AGG
  • BITOR_AGG
  • BITXOR_AGG 
  • BOOLAND_AGG 
  • BOOLOR_AGG
  • BOOLXOR_AGG
  • MEDIAN
  • STDDEV
  • STDDEV_POP
  • STDDEV_SAMP
  • VAR_POP
  • VAR_SAMP
  • APPROXIMATE.COUNT
  • BIT_AND 

  • BIT_OR

  • BOOL_AND

  • BOOL_OR

  • MEDIAN
  • STDDEV_POP
  • STDDEV_SAMP
  • VAR_POP
  • VAR_SAMP
  • APPROX_COUNT_DISTINCT
  • COUNT_BIG
  • GROUPING
  • STDEV

  • STDEVP
  • VAR
  • VARP
Databricks Lakehouse PlatformBigQuery
  • ANY
  • BIT_OR
  • BIT_XOR
  • BOOL_AND
  • BOOL_OR
  • COLLECT_LIST
  • COLLECT_SET
  • EVERY
  • FIRST_VALUE_RESPECT_NULLS
  • FIRST_VALUE_IGNORE_NULLS
  • LAST_VALUE_RESPECT_NULLS
  • LAST_VALUE_IGNORE_NULLS
  • SOME
  • SKEWNESS
  • STDDEV
  • STDDEV_POP
  • STDDEV_SAMP
  • VAR_POP
  • VAR_SAMP
  • ANY_VALUE
  • BIT_AND
  • BIT_OR
  • BIT_XOR 
  • LOGICAL_AND
  • LOGICAL_OR

See Snowflake Aggregate Functions, Redshift SQL Functions Reference, Aggregate Functions (Transact-SQL), Databricks Built-in Functions Reference, or BigQuery Aggregate Functions in Standard SQL for more information on the respective aggregate function.

N/AAVG
Argument*String/ExpressionRequired. Enter the field name or expression on which you want to apply the general aggregate function.N/AREVENUE
Eliminate duplicatesCheck box

Select to apply DISTINCT to the column specified in the Field field. This means that the aggregate function is applied only to the unique values in the column. 

Behavior with COUNT_IF aggregate function

Selecting this 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. However, it eliminates the duplicates from the list of records when used with a Redshift, Azure Synapse, or Databricks Lakehouse Platform (DLP) instance.

Not selectedSelected
Alias Name*StringRequired. Specify the column in which to display the result of the aggregate function. You can also reference this name in downstream Snaps to process the data further. N/ATOTAL_REVENUE
Linear Regression Aggregate Functions List 

This field set enables you to specify the columns for which to apply the linear regression aggregate functions.  Each function must be specified as a new row. Click  to add a row.

This field set contains the following fields:

  • Function
  • Argument 1
  • Argument 2
  • Alias Name
Function*String/Expression/Suggestion

Required. Select the aggregate function to use. Click  to retrieve the list of the supported linear regression aggregate functions. The list displays the functions supported by the database. An error is displayed if an account is not configured. See the Troubleshooting section for details. 

Alternatively, you can also enter the name of an aggregate function; you must, however, ensure that it is spelled correctly. Otherwise, the Snap displays an error. 

You can use the following aggregate functions:

  • CORR
  • COVAR_POP
  • COVAR_SAMP
  • REGR_AVGX
  • REGR_AVGY
  • REGR_COUNT
  • REGR_INTERCEPT
  • REGR_R2
  • REGR_SLOPE
  • REGR_SXX
  • REGR_SXY
  • REGR_SYY
  • MINHASH (for Snowflake only)
  • OBJECT_AGG (for Snowflake only)

See Snowflake Aggregate FunctionsDatabricks Built-in Functions Reference, or BigQuery Aggregate Functions in Standard SQL for more information on the respective aggregate function.

Though not supported natively

BigQuery does not natively support the above list of Linear Regression Aggregate Functions. However, SnapLogic provides you with the ability to use these functions with BigQuery through a series of internal query rewrites.

N/ACORR
Argument 1*
String/ExpressionRequired. Enter the first field name or expression on which you want to apply the Aggregate function.N/AREVENUE_LOC1

Argument 2*

String/ExpressionRequired. Enter the second field name or expression on which you want to apply the Aggregate function.N/AREVENUE_LOC2
Alias Name*StringRequired. Specify the column in which to display the result of the aggregate function. You can also reference this name in downstream Snaps to process the data further.N/ALINREGAGG_REVENUE
Aggregate Concatenation Functions List (Not valid for Databricks Lakehouse Platform)

This field set enables you to specify the list of aggregate concatenation functions for aggregation. The list displays the functions supported by the database that you select in the Account settings. Each function must be specified as a new row. Click  to add a row.

This field set contains the following fields:

  • Aggregate Concatenation Function
  • Field Name
  • Alias Name
  • Delimiter

Function*

String/Expression/Suggestion

Required. Select the aggregate concatenation function to use. Click  to retrieve the list of the supported functions. The list displays the functions supported by the database that you select in the Account settings. An error is displayed if an account is not configured. See the Troubleshooting section for details. 

Alternatively, you can also enter the name of the aggregate concatenation function to use; you must, however, ensure that it is spelled correctly. Otherwise, the Snap displays an error. 

You can use the following aggregate functions:

SnowflakeRedshiftAzure Synapse
  • LISTAGG
  • LISTAGG_DISTINCT
  • ARRAY_AGG
  • ARRAY_AGG_DISTINCT
  • LISTAGG
  • DISTINCT_LISTAGG
  • STRING_AGG
BigQuery

  • ARRAY_AGG
  • ARRAY_AGG_DISTINCT
  • ARRAY_CONCAT_AGG
  • STRING_AGG
  • STRING_AGG_DISTINCT


See Snowflake Aggregate Functions, Redshift SQL Functions ReferenceAggregate Functions (Transact-SQL), or BigQuery Aggregate Functions in Standard SQL for more information on the respective aggregate function.

N/ALISTAGG

Argument*

String/ExpressionRequiredEnter the field name or expression on which you want to apply the Aggregate Concatenation function.N/ANEW_LOCATIONS

Alias Name*

StringRequired. Specify the column in which to display the result of the concatenation function. You can also reference this name in downstream Snaps to process the data further.N/AUNIQUE_LOCS_LIST

Delimiter

String/ExpressionSpecify the delimiting character (string constant) to be used to separate the concatenated values., (comma); (semi-colon)
Percentile Distribution Functions List (Not valid for Azure Synapse, Databricks Lakehouse Platform, BigQuery)

This field set enables you to specify the list of percentile distribution functions to be used for aggregation. The list displays the functions supported by the database that you select in the Account settings. Each function must be specified as a new row. Click  to add a row.

This field set contains the following fields:

  • Function
  • Percentile
  • Alias Name

 Function*

String/Expression/Suggestion

Required. Select the percentile distribution function to use. Click  to retrieve the list of the supported functions. The list displays the functions supported by the database that you select in the Account settings. An error is displayed if an account is not configured. See the Troubleshooting section for details. 

Alternatively, you can also enter the name of the percentile distribution function to use; you must, however, ensure that it is spelled correctly. Otherwise, the Snap displays an error. 

You can use the following aggregate functions:

SnowflakeRedshift
  • PERCENTILE_DISC
  • PERCENTILE_CONT
  • APPROXIMATE_PERCENTILE_DISC
  • PERCENTILE_CONT

See Snowflake Aggregate Functions or Redshift SQL Functions Reference for more information on the respective aggregate function.

N/A

PERCENTILE_DISC

Percentile*String/ExpressionRequired. Specify the percentile value to be considered for applying the percentile distribution function.N/A0.8 (80th percentile)
Alias Name*StringRequired. Specify the column in which to display the result of the percentile distribution function. You can also reference this name in downstream Snaps to process the data further.N/AREVENUE_CPERCENTILE
Group By All The Input ColumnsCheckboxSelect this checkbox to group the output data by all columns in the input table. This disables the Group By Arguments List fieldset.Not SelectedSelected
GROUP BY Arguments List

This fieldset enables you to specify the columns for which to use the GROUP BY clause. Each column must be specified in a new row. Click to add a row.

This fieldset contains the following field:

  • Argument
ArgumentString/ExpressionSpecify the column in which to add the GROUP BY clause.N/A

GRADE

GENDER

GROUP BY CUBECheckbox

Select this checkbox to group the output data using the GROUP BY CUBE clause. This activates the GROUP BY CUBE Arguments List fieldset.

Not SelectedSelected
GROUP BY CUBE Arguments List

This fieldset enables you to specify the arguments for the GROUP BY CUBE clause. In addition to the GROUP BY ROLLUP, GROUP BY CUBE adds all the “cross-tabulations” rows and is equivalent to a series of grouping sets. Each argument must be specified in a new row. Click to add a row.

This fieldset contains the following field:

  • Argument
Argument*String/ExpressionSpecify the column in which to add the GROUP BY CUBE clause.N/A

CUST_ID

ORDERS

GROUP BY ROLLUPCheckbox

Select this checkbox to group the output data using the GROUP BY ROLLUP clause. This activates the GROUP BY ROLLUP Arguments List fieldset.

Not SelectedSelected
GROUP BY ROLLUP Arguments List

This fieldset enables you to specify the arguments for the GROUP BY ROLLUP clause that produces sub-total rows (in addition to the grouped rows). Each argument must be specified in a new row. Click to add a row.

This fieldset contains the following field:

  • Argument
Argument*String/ExpressionSpecify the column in which to add the GROUP BY ROLLUP clause.N/A

CATEGORY

REGION

GROUP BY GROUPING SETSCheckbox

Select this checkbox to group the output data using the GROUP BY GROUPING SETS clause. This activates the GROUP BY GROUPING SETS Arguments List fieldset.

Not SelectedSelected
GROUP BY GROUPING SETS Arguments List

This fieldset enables you to specify the arguments for the GROUP BY GROUPING SETS clause that that allows computing multiple GROUP BY clauses in a single statement. The group set is a set of dimension columns. Each argument must be specified in a new row. Click to add a row.

This fieldset contains the following field:

  • Argument
Argument*String/ExpressionSpecify the column in which to add the GROUP BY GROUPING SETS clause.N/A

CATEGORY

REGION

HAVING Predicate List

This fieldset enables you to specify the arguments for the HAVING predicates (conditions) over the result of applying a GROUP BY clause. Each predicate must be specified in a new row. Click to add a row.

This fieldset contains the following field:

  • Predicate
  • Boolean Operator
PredicateString/ExpressionSpecify the predicate (condition) to filter the results of a GROUP BY (CUBE/ROLLUP/GROUPING SETS) operation.N/AORD_COUNT(*) >100
Boolean OperatorDrop-down listSelect one boolean operator to apply another HAVING predicate in combination with the predicate selected.N/AAND
ORDER-BY Fields (Aggregate Concatenation Functions Only)

This field set enables you to specify the columns by which to sort the output data set. Each column must be specified in a new row. Click + to add a row.

This field set contains the following fields:

  • ORDER BY Field
  • Sort Order
  • Null Value Sort Preference
ORDER BY FieldString/ExpressionSpecify the column by which to sort the output data set.N/A

GRADE

GENDER

Sort OrderDrop-down listChoose one of the possible sort orders - ASC (ascending) or DESC (descending) for the output data set.ASCDESC
Null Value Sort PreferenceDrop-down listChoose where in the sort order do the null values, if any, in the ORDER BY Field be placed - NULLS FIRST (at the beginning) or NULLS LAST (at the end)NULLS FIRSTNULLS LAST

Troubleshooting

Error MessageReasonResolution
Account is required, please set in Accounts tabAccount configuration is mandatory in the ELT Aggregate Snap. An account has not been selected/configured in the Snap.Select or configure an account for the Snap. See Configuring the ELT Snap Pack Accounts for details.
Failure: Invalid Function Specified : <field value>The specified aggregate function is incorrect/not supported. This is likely to occur if you have entered the aggregate function's name in the Aggregate Function field manually.

Check whether the aggregate function specified in the error message is spelled correctly. You can avoid this error by selecting from the Snap's suggested aggregate functions. Click  and select the required aggregate function.

Examples

Performing Aggregate Calculations

We need a query with the appropriate aggregate functions along with a GROUP BY clause. This example shows how we can use the ELT Aggregate Snap to achieve this result. 

First, we use the ELT Select Snap to build a query to retrieve all records from the target table.

Upon execution, this Snap builds the query as shown below:

We want to perform aggregate functions for the values in the BYTE column. Accordingly, we add the ELT Aggregate Snap and configure it as required. In this example, we want to calculate the COUNT, SUM, MIN, and MAX. So, we configure the ELT Aggregate Snap as shown below:

Based on this configuration, the ELT Aggregate Snap builds a query as shown below:

 

Download this Pipeline.

Downloads

Important steps to successfully reuse pipelines

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

  File Modified

File ELT_Aggregate_Example.slp

Aug 20, 2020 by Mohammed Iqbal


Snap Pack History

 Click here to expand...

Release

Snap Pack Version 

Date

Type

Updates

May 2024437patches26846 LatestFixed 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 2024main26341 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 2024436patches25953  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:

February 2024main25112 StableUpdated and certified against the current SnapLogic Platform release.
November 2023435patches24461 LatestFixed an issue with the ELT Merge Into Snap that caused inaccurate aliases and table identifiers in its generated SQL statement.
November 2023435patches23671 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 2023main23721 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 2023main22460 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 2023N/A 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 2023main21015 StableUpgraded with the latest SnapLogic Platform release.
February 2023432patches20978 LatestFixed 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 2023main19844 StableUpgraded with the latest SnapLogic Platform release.
November 2022431patches19240

 

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 2022main18944 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 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:

  • 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 2022main17386 StableUpgraded with the latest SnapLogic Platform release.
4.29-Patch429patches16665 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

 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