Versions Compared

Old Version 15

changes.mady.by.user Anand Vedam

Saved on

compared with

New Version 20

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 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 for details. 

Image Removed

Prerequisites

None.

Limitations

None.

Known Issues

Multiexcerpt include macro
nameME_ELT_KI_Underscore_Refs
pageELT Snap Pack

Snap Input and Output

...

Document

...

  • Min: 1
  • Max: 1

...

  • ELT Select
  • ELT Copy

...

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

...

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 Image Removed to add a new row. 

This field set contains the following fields:

  • Rank Function
  • Alias Name

...

In this article

Table of Contents
maxLevel2
absoluteUrltrue

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.

Image Added

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.


ed  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:

  • CUME_DIST
  • DENSE_RANK
  • PERCENT_RANK
  • ROW_NUMBER
  • RANK
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 Image Added 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 Image Added 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 Image Added 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 Image Added 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 information. 

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

See Redshift Window Functions for more information. 

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

See Synapse Window Functions for more information. 

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

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

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 Image Added 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 Image Added 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 Image Added 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 Image Added 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 Image Added 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 Image Added 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

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_DISTAlias NameStringEnter the alias name to associate with the specified rank function.N/ATOTALGeneral 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 Image Removed 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 Image Removed 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

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 Image Removed to add a new row. 

NoteApplicable only to the Snowflake database

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/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 Image Added to add a row.

This field set contains the following fields:

Bitwise Aggregate
  • Lead Lag Window 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 Image Removed 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 Image Removed 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 Image Removed 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 Image Removed 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 Image Removed 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.

    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/ALEAD and LAG Window Functions List

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

    Click Image Removed 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 Image Removed 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.

    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 Image Removed to add a row.

    This field set contains the following fields:

    • Window Function
    • First Field
    • Second Field
    • Alias Name
    Window functionString/SuggestionRequired. 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 Removed to view and select a function from the list of window functions. The
    • 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 Image Added 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/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 Image Added 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 Added 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_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_RegrValueAggregate 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 Image Added 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 Image Added 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 Reference, Aggregate 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.

     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
    titleWindow Functions in Redshift and Azure Synapse

    Redshift and Azure Synapse do not natively support the Window functions listed above. But, the ELT Window Functions Snap supports all of these functions on Redshift and Azure Synapse 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

    Click Image Added 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 Image Added 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 Functions, Redshift SQL Functions Reference, or BigQuery Navigation Functions for more information on the respective aggregate function.
    N/AQTYSecond Field

    PERCENTILE_DISC

    PercentileString/ExpressionRequired.   Specify the second field (argument) for the selected functionpercentile value to be considered for applying the percentile distribution function.N/A20.8 (80th percentile)Alias NameStringRequired. Specify the column alias name the column in which to display the result of the Window functionpercentile distribution function. You can also reference this name in downstream Snaps to process the data further.N/AWinFuncREVENUE_RegrValueCPERCENTILEPARTITION 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 macronamegetpreviewdatapageELT IntersectNot 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.

    ...