Versions Compared

Old Version 15

changes.mady.by.user Anand Vedam

Saved on

compared with

New Version 16

changes.mady.by.user Anand Vedam

Saved on

Key

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

In this article

Table of Contents
maxLevel2
absoluteUrltrue

Overview

Use 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 Functions, Redshift Window Functions, and  Azure Synapse Window Functions, and Azure Databricks Window Functions for details . Image Removedon these functions.

Image Added

Prerequisites

None.

...

The Snap fails during Pipeline validation
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 and Synapse databases, 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

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. 

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



Note

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


N/ACOUNTFieldString/ExpressionEnter the name of the field in the input table on which to apply the aggregate function.N/ASALARYAlias NameStringEnter the alias name to associate with the specified aggregate function.N/ATOTAL_SALARYBitwise 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_AGGFieldString/ExpressionEnter the name of the field in the input table upon which to apply the bitwise aggregate function.N/ARANGEAlias NameStringEnter the alias name to associate with the specified bitwise aggregate function.N/ABITOR_RANGEBoolean 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_AGGFieldString/ExpressionEnter the name of the field in the input table upon which to apply the boolean aggregate function.N/ARANGEAlias NameStringEnter the alias name to associate with the specified boolean aggregate function.N/ABOOLOR_RANGEValue 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. 



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/A3Alias 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 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.



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/AQTYOffset 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/A2Default ValueString/ExpressionEnter a SQL expression the LEAD or LAG functions use when the offset goes out of the bounds of the window.N/A5Alias 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_rnLinear 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 Image Modified 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
and
, Azure Synapse
Redshift and Azure Synapse
, and Databricks Lakehouse Platform

These databases do not natively support the

Window

window functions listed above. But, the ELT Window Functions Snap supports all of these functions

on Redshift and

on Redshift, Azure Synapse, and Databricks Lakehouse Platform 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_AVGYFirst FieldString/ExpressionRequired. Specify the first field (argument) for the selected function.N/AQTYSecond FieldString/ExpressionRequired. Specify the second field (argument) for the selected function.N/A2Alias 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_RegrValuePARTITION 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_SALARYOrder 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_SALARYSort OrderString

Select the order in which to sort the rows.

Available options are:

  • ASC
  • DESC
ASCDESCNull 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 LASTWindow 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 ROWBounded 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/A2End 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 FOLLOWINGBounded 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/A8Get preview dataCheck box
Multiexcerpt include macro
namegetpreviewdata
pageELT Intersect
Not selectedSelected

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

...

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

Image RemovedImage Added

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.

...