ELT Window Functions

In this article

Overview

You can use this Snap to add window functions such as rank-relation functions, aggregate functions, and bitwise functions to the table's data. See Snowflake Window FunctionsRedshift Window FunctionsAzure Synapse Window Functions, and Azure Databricks Window Functions for details on these functions.

Prerequisites

None.

Limitations

None.

Known Issues

  • ELT Pipelines targeting a Databricks Lakehouse Platform (DLP) instance might fail due to a very long or complex SQL query that they build. As a workaround, you can set an advanced (URL) property useNativeQuery to 1 in your ELT Database Account configuration as shown below:
  • 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

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

Document

  • Min: 1
  • Max: 1
  • ELT Select
  • ELT Copy
The data on which to add the window functions.
Output

Document

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

The input data modified with the window functions. If the Pass through check box is selected, the output also includes the original input data.

Snap Settings

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 Window FunctionsAggregate Salary Data
Get preview dataCheck box

Select this checkbox to include a preview of the query's output. The Snap performs limited execution and generates a data preview during Pipeline validation.

In case of ELT Pipelines, only the SQL query flows through the Snaps but not the actual source data. Hence, the preview data for a Snap is the result of executing the SQL query that the Snap has generated in the Pipeline.

The number of records displayed in the preview (upon validation) is the smaller of the following:

  • Number of records available upon execution of the SQL query generated by the Snap.

  • The value set in the Preview Data Count setting (default is 50 records).

Rendering Complex Data Types in Databricks Lakehouse Platform

Based on the data types of the fields in the input schema, the Snap renders the complex data types like map and struct as object data type and array as an array data type. It renders all other incoming data types as-is except for the values in binary fields are displayed as a base64 encoded string and as string data type.

Not selectedSelected
Pass throughCheck boxSelect to include the original data along with the aggregated data in the output. Otherwise, the output has only aggregated data. Not selectedSelected

Rank Related Functions List

Use this field set to enter the rank-related function to apply to the table's data. You must also specify an alias for each rank-related function. Specify each rank-related function as a separate row. Click  to add a new row. 

This field set contains the following fields:

  • Rank Function
  • Alias Name
Rank FunctionString/Suggestion

Enter the rank-related function to apply to the table data. Alternatively, click  to view and select a rank function from the suggestion list. The list displays the functions supported by the database that you selected in the Account settings. An error is displayed if an account is not configured. See the Troubleshooting section for details.  

The following functions are available for Snowflake, Redshift, Azure Synapse and Databricks Lakehouse Platform:

  • CUME_DIST
  • DENSE_RANK
  • PERCENT_RANK
  • ROW_NUMBER
  • RANK

The Snap fails during Pipeline validation/execution if you specify an incorrect function name. 

For more information on the ranking functions, refer to the following: 

N/ACUME_DIST
Alias NameStringEnter the alias name to associate with the specified rank function.N/ATOTAL
General Window Functions List

Use this field set to apply the aggregate function on the specified field in the table's data. You must also specify an alias for each aggregate function. Specify each aggregate function as a separate row. Click  to add a new row. 

This field set contains the following fields:

  • Aggregate Function
  • Field
  • Alias Name
Aggregate FunctionString/Suggestion

Enter the name of the aggregate function to apply to the table data. Alternatively, click  to view and select an aggregate function from the suggestion list. 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.

The available functions are:

SnowflakeRedshiftAzure Synapse
  • COUNT
  • SUM
  • MIN
  • MAX
  • AVG
  • ANY_VALUE
  • MEDIAN
  • MODE
  • STDDEV_POP
  • STDDEV_SAMP
  • VAR_POP
  • VAR_SAMP
  • CONDITIONAL_CHANGE_EVENT
  • CONDITIONAL_TRUE_EVENT
  • COUNT_IF
  • RATIO_TO_REPORT
  • NTILE

See Snowflake Window Functions for more details. 

  • COUNT
  • SUM
  • MIN
  • MAX
  • AVG
  • MEDIAN
  • STDDEV_POP
  • STDDEV_SAMP
  • VAR_POP
  • VAR_SAMP
  • RATIO_TO_REPORT
  • NTILE

See Redshift Window Functions for more details. 

  • COUNT
  • COUNT_BIG
  • SUM
  • MIN
  • MAX
  • AVG
  • STDEV
  • STDDEV
  • VAR
  • VARP
  • NTILE

See Synapse Window Functions for more details. 

Databricks Lakehouse Platform
  • COUNT
  • SUM
  • MIN
  • MAX
  • AVG
  • STDDEV_POP
  • STDDEV_SAMP
  • VAR_POP
  • VAR_SAMP
  • NTILE

See Azure Databricks Window Functions for more details.


The Snap fails during Pipeline validation/execution if you specify an incorrect function name. 

N/ACOUNT
FieldString/ExpressionEnter the name of the field in the input table on which to apply the aggregate function.N/ASALARY
Alias NameStringEnter the alias name to associate with the specified aggregate function.N/ATOTAL_SALARY
Bitwise Aggregate Functions List (Snowflake Only)

Specify the bitwise aggregate function to apply to the specified field in the table's data. You must also specify an alias for each bitwise aggregate function. Specify each bitwise aggregate function as a separate row. Click  to add a new row. 

Applicable only to the Snowflake database.

This field set contains the following fields:

  • Bitwise Aggregate Function
  • Field
  • Alias Name
Bitwise Aggregate FunctionString/Suggestion

Enter the name of the bitwise aggregate function to apply to the table data. Alternatively, click the  button to view and select a bitwise aggregate function from the list of supported bitwise aggregate functions. 

Available options are:

The Snap fails during Pipeline validation/execution if you specify an incorrect function name. 

N/ABITOR_AGG
FieldString/ExpressionEnter the name of the field in the input table upon which to apply the bitwise aggregate function.N/ARANGE
Alias NameStringEnter the alias name to associate with the specified bitwise aggregate function.N/ABITOR_RANGE
Boolean Aggregate Functions List (Snowflake Only)

Specify the boolean aggregate function to apply to the specified field in the table's data. You must also specify an alias for each boolean aggregate function. Specify each boolean aggregate function as a separate row. Click  to add a new row. 

Applicable only to the Snowflake database.

This field set contains the following fields:

  • Boolean Aggregate Function
  • Field
  • Alias Name
Boolean Aggregate FunctionString/Suggestion

Enter the name of the boolean aggregate function to apply to the table data. Alternatively, click the  button to view and select a boolean aggregate function from the list of supported bitwise aggregate functions. 

Available options are:

The Snap fails during Pipeline validation/execution if you specify an incorrect function name. 

N/ABOOLOR_AGG
FieldString/ExpressionEnter the name of the field in the input table upon which to apply the boolean aggregate function.N/ARANGE
Alias NameStringEnter the alias name to associate with the specified boolean aggregate function.N/ABOOLOR_RANGE
Value Based Window Functions List

Use this field set to specify the columns for which to apply value-based Window functions. Each Window function must be specified as a new row. 
Click  to add a row.

This field set contains the following fields:

  • Value Window Function
  • Field
  • nth Value
  • Alias Name
Value Window FunctionString/Suggestion

Required. Enter the name of the window function to apply to the table data. You must ensure that the function is spelled correctly, else the Snap displays an error. See the Troubleshooting section for details.

Alternatively, click  to view and select a function from the list of window functions. The list displays the functions supported by the database that you select in the Account settings. 

The available functions are:

Snowflake
Redshift
Azure Synapse
  • FIRST_VALUE_IGNORE_NULLS
  • FIRST_VALUE_RESPECT_NULLS
  • LAST_VALUE_IGNORE_NULLS
  • LAST_VALUE_RESPECT_NULLS
  • NTH_VALUE_FROM_FIRST_IGNORE_NULLS
  • NTH_VALUE_FROM_FIRST_RESPECT_NULLS
  • NTH_VALUE_FROM_LAST_IGNORE_NULLS
  • NTH_VALUE_FROM_LAST_RESPECT_NULLS

See Snowflake Window functions for more information.

  • FIRST_VALUE_IGNORE_NULLS
  • FIRST_VALUE_RESPECT_NULLS
  • LAST_VALUE_IGNORE_NULLS
  • LAST_VALUE_RESPECT_NULLS
  • NTH_VALUE_FROM_FIRST_IGNORE_NULLS
  • NTH_VALUE_FROM_FIRST_RESPECT_NULLS
  • NTH_VALUE_FROM_LAST_IGNORE_NULLS
  • NTH_VALUE_FROM_LAST_RESPECT_NULLS

See Redshift Window functions for more information.

  • FIRST_VALUE_RESPECT_NULLS
  • LAST_VALUE_RESPECT_NULLS

See Azure Synapse Window functions for more information.

Databricks Lakehouse Platform
  • FIRST_VALUE_IGNORE_NULLS
  • FIRST_VALUE_RESPECT_NULLS
  • LAST_VALUE_IGNORE_NULLS
  • LAST_VALUE_RESPECT_NULLS

See Azure Databricks Window Functions for more details. 


Redshift does not natively support the NTH_VALUE_FROM_LAST_RESPECT_NULL and NTH_VALUE_FROM_LAST_IGNORE_NULL functions. But, the ELT Window Functions Snap supports them through special rewrites (using internal SQL rewrites) on Redshift.

N/A

FIRST_VALUE_IGNORE_NULLS

FieldString/ExpressionRequired. Apply the window function to the target column.N/AQTY

nth Value

String/Expression

Enter a number for the nth value function.

This field is ignored by the FIRST_VALUE and the LAST_VALUE functions during the execution.

N/A3
Alias NameStringRequired. Specify the column alias name 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/A
LEAD and LAG Window Functions List

Use this field set to add LEAD and LAG Window functions. Specify each function as a new row. 

Click  to add a row.

This field set contains the following fields:

  • Lead Lag Window Function
  • Field
  • Offset Value
  • Default Value
  • Alias Name
Lead Lag Window FunctionString/Suggestion

Required. Enter the name of the Lead Lag function to use. You must ensure that it is spelled correctly, else the Snap displays an error. See the Troubleshooting section for details.
Alternatively, click  to view and select a function from the list of window functions. The list displays the functions supported by the database that you select in the Account settings. The available functions are:

Snowflake
Redshift
Azure Synapse
  • LEAD_IGNORE_NULLS
  • LEAD_RESPECT_NULLS
  • LAG_IGNORE_NULLS
  • LAG_RESPECT_NULLS

See Snowflake Lead and Lag Window Functions for more information.

  • LEAD_IGNORE_NULLS
  • LEAD_RESPECT_NULLS
  • LAG_IGNORE_NULLS
  • LAG_RESPECT_NULLS

See Redshift Lead and Lag Window Functions for more information.

  • LEAD_RESPECT_NULLS
  • LAG_RESPECT_NULLS

See Azure Synapse Lead and Lag Window Functions for more information.

Databricks Lakehouse Platform
  • LEAD_RESPECT_NULLS
  • LAG_RESPECT_NULLS

See Azure Databricks Lead and Lag window functions for more information.


Redshift does not natively support the following default value expressions in the LEAD and LAG syntax which is supported by Snowflake and Azure Synapse. But, the ELT Window Functions Snap supports these functions through special rewrites:

  • COALESCE ([LEAD Expression], [Default Value Expression])
  • COALESCE ([LAG Expression], [Default Value Expression])
N/A

LEAD_IGNORE_NULLS

FieldString/ExpressionRequired. Apply the Lead Lag Window function to the target column.N/AQTY
Offset ValueString/ExpressionEnter the number the LEAD or LAG function uses to fetch the leading or lagging value from the input table partition for the given input field.N/A2
Default ValueString/ExpressionEnter a SQL expression the LEAD or LAG functions use when the offset goes out of the bounds of the window.N/A5
Alias NameStringSpecify the column alias name 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/Alead_rn
Linear Regression Related Functions List

Use this field set to add Linear Regression Related Window functions. Specify each function as a new row. 

Click  to add a row.

This field set contains the following fields:

  • Window Function
  • First Field
  • Second Field
  • Alias Name
Window functionString/Suggestion

Required. Enter the name of the Linear Regression function to use. You must ensure that it is spelled correctly, else the Snap displays an error. See the Troubleshooting section for details.
Alternatively, click  to view and select a function from the list of window functions. The list displays the functions supported by the database that you select in the Account settings. The available functions are:

  • CORR
  • COVAR_POP
  • COVAR_SAMP
  • REGR_AVGX
  • REGR_AVGY
  • REGR_COUNT
  • REGR_INTERCEPT
  • REGR_R2
  • REGR_SLOPE
  • REGR_SXX
  • REGR_SXY
  • REGR_SYY

See Snowflake Window Functions for more information.

Linear Regression Window Functions in Redshift, Azure Synapse, and Databricks Lakehouse Platform

These databases do not natively support the window functions listed above. But, the ELT Window Functions Snap supports all of these functions on Redshift, Azure Synapse, and Databricks Lakehouse Platform databases through special rewrites.

Accuracy in Output Values

The Scale and Precision that the different databases (Snowflake/Redshift/Azure Synapse) allow for calculation of the values has a direct bearing on the accuracy of the Window Function values that the Snap returns in the output.

N/AREGR_AVGY
First FieldString/ExpressionRequired. Specify the first field (argument) for the selected function.N/AQTY
Second FieldString/ExpressionRequired. Specify the second field (argument) for the selected function.N/A2
Alias NameStringRequired. Specify the column alias name in which to display the result of the Window function. You can also reference this name in downstream Snaps to process the data further.N/AWinFunc_RegrValue
PARTITION BY Fields List

Enter the fields in the table to use to partition the output data. Specify each field in a separate row. Click  to add a new row. 

This field set contains the following fields:

  • Partition By Field

You cannot apply the window framing clause, window ordering clause, or window partitioning clause to different functions in the same Snap. You must add another ELT Window Functions Snap and configure it with the target functions to do so.

Partition By FieldString/ExpressionEnter the name of the field to partition the data with. N/ATOTAL_SALARY
Order By Fields List 

Specify the field to use to sort the output data along with the sort order preference and the placement for rows with null values in the specified field. Specify each field in a separate row. Click  to add a new row. 

This field set contains the following fields:

  • Order By Field
  • Sort Order
  • Null Value Sort Preference

You cannot apply the window framing clause, window ordering clause, or window partitioning clause to different functions in the same Snap. You must add another ELT Window Functions Snap and configure it with the target functions to do so.

Order By FieldStringEnter the name of the field/column in the table based on which to sort the rows.N/ATOTAL_SALARY
Sort OrderString

Select the order in which to sort the rows.

Available options are:

  • ASC
  • DESC
ASCDESC
Null Value Sort PreferenceDrop-down list

Select whether to keep rows with null values first or last after the sort operation.

Available options are:

  • NULLS FIRST
  • NULLS LAST
NULLS FIRSTNULLS LAST
Window Frame

Use this field set to specify the range of the rows in the input table upon which to apply the window functions. 

This field set contains the following fields:

  • Start of Window Frame
  • End of Window Frame
  • The window framing clause is not applicable to all the functions specified in the Snap configuration. It depends upon the functions and the database where the window framing clause is applied. 
  • You cannot apply the window framing clause, window ordering clause, or window partitioning clause to different functions in the same Snap. You must add another ELT Window Functions Snap and configure it with the target functions to do so.
Start of Window FrameDrop-down list

Select the policy/condition to indicate the first row in the window frame.

Available options are:

  • UNBOUNDED PRECEDING
  • BOUNDED VALUE PRECEDING. Activates the Bounded Start Value field.
  • CURRENT ROW
  • BOUNDED VALUE FOLLOWING. Activates the Bounded Start Value field.
  • UNBOUNDED VALUE FOLLOWING
UNBOUNDED PRECEDINGCURRENT ROW
Bounded Start ValueInteger

Activates when you select the BOUNDED VALUE PRECEDING or BOUNDED VALUE FOLLOWING option in the Start of Window Frame field.

Enter the row value from which to start the window frame.

N/A2
End of Window FrameDrop-down list

Select the policy/condition to indicate the last row in the window frame.

Available options are:

  • UNBOUNDED PRECEDING
  • BOUNDED VALUE PRECEDING. Activates the Bounded End Value field.
  • CURRENT ROW
  • BOUNDED VALUE FOLLOWING. Activates the Bounded End Value field.
  • UNBOUNDED VALUE FOLLOWING
UNBOUNDED FOLLOWINGBOUNDED VALUE FOLLOWING
Bounded End ValueInteger

Activates when you select the BOUNDED VALUE PRECEDING or BOUNDED VALUE FOLLOWING option in the End of Window Frame field.

Enter the row value at which to end the window frame.

N/A8

Troubleshooting

ErrorReasonResolution
Set an account for the Snap in the Accounts tab.Account configuration is mandatory in the ELT Window Function 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.

Examples

Calculating Aggregate Data for an Organization

We want to calculate the aggregate scores such as, MIX, MAX, and MEDIAN and also want to apply bitwise and boolean aggregate functions to a table in the Snowflake database. This Pipeline shows how you can use the ELT Windows Function Snap to accomplish this task.

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 the following functions on the above table:

  • Apply rank-related functions.
  • Calculate aggregate data for the columns RANGE and QTY.
  • Apply bitwise aggregate functions to the data in the RANGE and QTY columns.
  • Apply boolean aggregate functions to the data in the LOCAL column.
  • Partition the output by the data in the DEPT column.
  • Sort the output based on the data in the LOCAL in descending order and keep rows with null values in this column at the last. 

To do so, we add the ELT Window Functions Snap and configure it as follows: 

Upon successful execution, the ELT Window Functions Snap performs the specified calculations upon the input data and shows the following output:

We can add an ELT Insert-Select or ELT Merge Into Snap downstream of the ELT Window Function Snap and write the above output into another table.

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 ELTWindowFunctions_Example.slp

Oct 30, 2020 by Mohammed Iqbal

Snap Pack History

 Click here to expand...

Release

Snap Pack Version 

Date

Type

Updates

4.26-Patch426patches12021 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-Patch426patches11646 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-Patch426patches11323 

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-Patch426patches11262Latest
  • 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.26main11181 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-Patch425patches10017 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 ]: 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-Patch425patches9725 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 SnowflakeIf 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.25main9554 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-Patch424patches8793 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.24main8556 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.23main7430 StableIntroduces 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

 

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. 


See Also