Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

In this article

Table of Contents
maxLevel2
absoluteUrltrue

Multiexcerpt include
templateData[]
MultiExcerptNameME_OE_ELT_Snap_Accounts
addpanelfalse
PageWithExcerptELT Aggregate

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 FunctionsAzure Databricks Window Functions, or BigQuery Expressions, functions, and operators for details on these functions.

Prerequisites

None.

Limitation

Multiexcerpt include macro
nameME_ELT_GBQ_StandardSQL_UOI
pageELT Copy

Known Issues

Multiexcerpt include macro
nameME_ELT_KI_Underscore_Refs
pageELT Snap Pack

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

Info
titleSQL 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
Insert excerpt
File Writer
File Writer
nopaneltrue
ELT Window FunctionsAggregate Salary Data
Get preview dataCheck box
Multiexcerpt include macro
namegetpreviewdata
pageELT Intersect
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
Note

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
  • SKEW
  • KURTOSIS

See Snowflake Window Functions for more information. 

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

See Redshift Window Functions for more information. 

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

See Synapse Window Functions for more information. 

Databricks Lakehouse PlatformBigQuery
  • COUNT
  • SUM
  • MIN
  • MAX
  • AVG
  • STDDEV_POP
  • STDDEV_SAMP
  • VAR_POP
  • VAR_SAMP
  • NTILE
  • SKEW
  • MODE
  • KURTOSIS

See Azure Databricks Window Functions for more information.

  • ANY_VALUE
  • ARRAY_AGG
  • AVG
  • CORR
  • COUNT
  • COUNTIF
  • COVAR_POP
  • COVAR_SAMP
  • MAX
  • MIN
  • NTILE
  • STDDEV_POP
  • STDDEV_SAMP
  • STRING_AGG
  • SUM
  • VAR_POP
  • VAR_SAMP
  • SKEW
  • MODE
  • KURTOSIS

See BigQuery Aggregate Functions and Statistical Aggregate Functions for more information.



Note

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. 

Note

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:

Note

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. 

Note

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:

Note

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 PlatformBigQuery
  • FIRST_VALUE_IGNORE_NULLS
  • FIRST_VALUE_RESPECT_NULLS
  • LAST_VALUE_IGNORE_NULLS
  • LAST_VALUE_RESPECT_NULLS

See Azure Databricks Window Functions for more information. 

  • FIRST_VALUE_IGNORE_NULLS
  • FIRST_VALUE_RESPECT_NULLS
  • LAST_VALUE_IGNORE_NULLS
  • LAST_VALUE_RESPECT_NULLS
  • NTH_VALUE_IGNORE_NULLS
  • NTH_VALUE_RESPECT_NULLS

See BigQuery Navigation Functions for more information.



Info

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 PlatformBigQuery
  • LEAD_RESPECT_NULLS
  • LAG_RESPECT_NULLS

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

  • LEAD_RESPECT_NULLS
  • LAG_RESPECT_NULLS

See BigQuery Lead and Lag Window Functions for more information.



Info

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.

Info
titleLinear Regression Window Functions in Redshift, Azure Synapse, Databricks Lakehouse Platform, and BigQuery

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, Databricks Lakehouse Platform, and BigQuery databases through special rewrites.


Note
titleAccuracy 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
Aggregate Concatenation Functions List (Not valid for Azure Synapse, Databricks Lakehouse Platform)

This field set enables you to specify the list of aggregate concatenation 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:

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

Aggregate Concatenation 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:


Snowflake
Redshift
BigQuery
  • LISTAGG
  • LISTAGG_DISTINCT
  • ARRAY_AGG
  • ARRAY_AGG_DISTINCT
  • LISTAGG
  • DISTINCT_LISTAGG
  • ARRAY_AGG
  • STRING_AGG


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

Field Name

String/ExpressionRequired. Specify the column name to apply the 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)

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:

  • Percentile Distribution Function
  • Percentile
  • Alias Name

Percentile Distribution 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
BigQuery
  • PERCENTILE_CONT_IGNORE_NULLS
  • PERCENTILE_CONT_RESPECT_NULLS
  • PERCENTILE_DISC_IGNORE_NULLS
  • PERCENTILE_DISC_RESPECT_NULLS


See Snowflake Aggregate FunctionsRedshift SQL Functions Reference, or BigQuery Navigation Functions for more information on the respective aggregate function.
N/A

PERCENTILE_DISC

PercentileString/ExpressionRequired. Specify the percentile value to be considered for applying the percentile distribution function.N/A0.8 (80th percentile)
Alias NameStringRequired. 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
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
Note

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
Note

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
Note
  • 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 ELT Database 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

Note
titleImportant 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.

Attachments
patterns*.slp, *.zip

Insert excerpt
ELT Snap Pack
ELT Snap Pack
nopaneltrue


See Also