/
ELT Window Functions

ELT Window Functions

May 09, 2024

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

Prerequisites

None.

Limitation

Known Issues

Snap Input and Output

Input/Output

Type of View

Number of Views

Examples of Upstream and Downstream Snaps

Description

Input/Output

Type of View

Number of Views

Examples of Upstream and Downstream Snaps

Description

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

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 Name

Data Type

Description

Default Value

Example 

Parameter Name

Data Type

Description

Default Value

Example 

Label

String

ELT Window Functions

Aggregate Salary Data

Get preview data

Check box

Not selected

Selected

Pass through

Check box

Select to include the original data along with the aggregated data in the output. Otherwise, the output has only aggregated data. 

Not selected

Selected

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 Function

String/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/A

CUME_DIST

Alias Name

String

Enter the alias name to associate with the specified rank function.

N/A

TOTAL

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 Function

String/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:

Snowflake

Redshift

Azure 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 Platform

BigQuery



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



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

N/A

COUNT

Field

String/Expression

Enter the name of the field in the input table on which to apply the aggregate function.

N/A

SALARY

Alias Name

String

Enter the alias name to associate with the specified aggregate function.

N/A

TOTAL_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 Function

String/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/A

BITOR_AGG

Field

String/Expression

Enter the name of the field in the input table upon which to apply the bitwise aggregate function.

N/A

RANGE

Alias Name

String

Enter the alias name to associate with the specified bitwise aggregate function.

N/A

BITOR_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 Function

String/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/A

BOOLOR_AGG

Field

String/Expression

Enter the name of the field in the input table upon which to apply the boolean aggregate function.

N/A

RANGE

Alias Name

String

Enter the alias name to associate with the specified boolean aggregate function.

N/A

BOOLOR_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 Function

String/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