ELT Math Function

An account for the Snap

You must define an account for this Snap to communicate with your target CDW. Click the account specific to your target CDW below for more information:

Overview

You can use this Snap to perform mathematical operations/functions on your source dataset. This Snap provides multiple lists of supported mathematical functions based on the target database configured in its Account. Each list of functions is further segregated based on the number of operands/arguments it needs—unary (one) and binary (two).

Snap Type

ELT Math Function Snap is a TRANSFORM-type Snap that performs unary and binary mathematical operations on the dataset.

Prerequisites

  • Valid accounts and access permissions to connect to the following:

    • Source: AWS S3, Redshift, Azure Cloud Storage, or Google Cloud Storage

    • Target: Snowflake, Redshift, Azure Synapse, Databricks Lakehouse Platform, or BigQuery

Limitations and Known Issues

None.

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 Filter

  • ELT Join

  • ELT Transform

A document containing the SQL query that yields the data required to perform the mathematical (unary or binary) operations.

Output

Document

  • Min: 1

  • Max: 1

  • ELT Insert Select

  • ELT Join

  • ELT Transform

A document containing the incremental SQL query that includes the mathematical functions defined in the Snap.

Snap Settings

  • Click the = (Expression) button in the Snap's configuration, if available, to define the corresponding field value using expression language and Pipeline parameters. 

  • Field names marked with an asterisk ( * )  in the table below are mandatory. 

Field Name

Type

Description

Field Name

Type

Description

Label*

String

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

Default ValueELT Math Function
ExampleMathFunctions_Trigonometric

Get preview data

Checkbox

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

Default ValueNot selected
ExampleSelected

Pass through

Checkbox

Select this checkbox to specify that the Snap must include the incoming document (SQL query) in its output document.

Default ValueNot selected
ExampleSelected

Arithmetic unary functions

Specify your arithmetic unary function/operation to be performed on a single table column/its value, using this fieldset. Click to add a new row. Ensure that you specify each function in a separate row.

This fieldset consists of the following fields:

  • Arithmetic unary expression name

  • Argument

  • Alias

Arithmetic unary expression name*

String/Suggestion

Select an arithmetic unary expression/function to use. Click the Suggest icon 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.
You can use the following arithmetic unary functions:

  • NEGATIVE

  • POSITIVE

Default Value: None.
ExampleNEGATIVE

Argument*

String/Expression

Each of the arithmetic unary functions needs only one value/argument. Hence, specify an argument for the selected expression/function. This is typically a column name in your source table.
Default Value: None.
ExampleORDER_AMT

Alias*

String

Specify the column in which to display the result of the unary function. You can also reference this name in downstream Snaps to process the data further. 

Default Value: None.
ExampleA_OAMT

Arithmetic binary functions

Specify your arithmetic binary function/operation to be performed on any two table columns/their values, using this fieldset. Click to add a new row. Ensure that you specify each function in a separate row.

This fieldset consists of the following fields:

  • Arithmetic binary expression name

  • Argument

  • Argument

  • Alias

Arithmetic binary expression name*

String/Suggestion

Select an arithmetic binary expression/function to use. Click the Suggest icon to retrieve the list of the supported arithmetic binary 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.
You can use the following binary functions:

  • DIV

  • DIV0

  • PMOD

In addition to the above, you can use certain mathematical functions with specific target CDWs as mentioned below:

  • Databricks Lakehouse Platform

    • TRY_ADD

    • TRY_DIVIDE

  • Google BigQuery

    • IEEE_DIVIDE

    • SAFE_ADD

    • SAFE_DIVIDE

    • SAFE_MULTIPLY

    • SAFE_SUBTRACT

Default Value: None.
ExampleDIV

Argument*

String/Expression

Each of the arithmetic binary functions needs two values/arguments. Specify the first argument in this field for the selected expression/function. This is typically a column name in your source table.
Default Value: None.
ExampleORDER_ID

Argument*

String/Expression

Each of the arithmetic binary functions needs two values/arguments. Specify the second argument in this field for the selected expression/function. This is typically a column name in your source table.
Default Value: None.
ExampleORDER_IDS

Alias*

String

Specify the column in which to display the result of the binary function. You can also reference this name in downstream Snaps to process the data further. 

Default Value: None.
Example: A_ORDIDS

Rounding and truncation unary functions

Specify your rounding and truncation unary function/operation to be performed on a single table column/its value, using this fieldset. Click to add a new row. Ensure that you specify each function in a separate row.

This fieldset consists of the following fields:

  • Rounding and truncation unary expression name

  • Argument

  • Alias

Rounding and truncation unary expression name*

String/Suggestion

Select a rounding and truncation unary expression/function to use. Click the Suggest icon 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.
You can use the following rounding and truncation unary functions:

  • ABS

  • RINT

  • SIGN

  • FLOOR

In addition to the above, you can use certain mathematical functions with specific target CDWs as mentioned below:

  • Azure Synapse

    • CEILING

  • BigQuery

    • CEILING

  • Redshift

    • CEIL

  • Databricks Lakehouse Platform

    • CEIL

    • CEILING

Default Value: None.
ExampleSIGN

Argument*

String/Expression

Each of the rounding and truncation unary functions needs only one value/argument. Hence, specify an argument for the selected expression/function. This is typically a column name in your source table.
Default Value: None.
ExampleORDER_AMT

Alias*

String

Specify the column in which to display the result of the unary function. You can also reference this name in downstream Snaps to process the data further. 

Default Value: None.
ExampleA_OAMT

Rounding and truncation binary functions

Specify your rounding and truncation binary function/operation to be performed on any two table columns/their values, using this fieldset. Click to add a new row. Ensure that you specify each function in a separate row.

This fieldset consists of the following fields:

  • Rounding and truncation binary expression name

  • Argument

  • Argument

  • Alias

Rounding and truncation binary expression name*

String/Suggestion

Select a rounding and truncation binary expression/function to use. Click the Suggest icon to retrieve the list of the supported rounding and truncation binary 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.
You can use the following binary functions:

  • MOD

  • ROUND

In addition to the above, you can use certain mathematical functions with specific target CDWs as mentioned below:

  • Snowflake

    • CEIL

    • FLOOR

    • TRUNC

  • BigQuery

    • TRUNC

  • Redshift

    • TRUNC

  • Databricks Lakehouse Platform

    • BROUND

Default Value: None.
ExampleCEIL

Argument*

String/Expression

Each of the rounding and truncation binary functions needs two values/arguments. Specify the first argument in this field for the selected expression/function. This is typically a column name in your source table.
Default Value: None.
ExampleORDER_ID

Argument*

String/Expression

Each of the rounding and truncation binary functions needs two values/arguments. Specify the second argument in this field for the selected expression/function. This is typically a column name in your source table.
Default Value: None.
ExampleORDER_IDS

Alias*

String

Specify the column in which to display the result of the binary function. You can also reference this name in downstream Snaps to process the data further. 

Default Value: None.
Example: A_ORDIDS

Exponent and root unary functions

Specify your exponent and root unary function/operation to be performed on a single table column/its value, using this fieldset. Click to add a new row. Ensure that you specify each function in a separate row.

This fieldset consists of the following fields:

  • Exponent and root unary expression name

  • Argument

  • Alias

Exponent and root unary expression name*

String/Suggestion

Select an exponent and root unary expression/function to use. Click the Suggest icon 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.
You can use the following exponent and root unary functions:

  • CBRT

  • EXP

  • EXPM1

  • SQRT

  • SQUARE

In addition to the above, you can use certain mathematical functions with specific target CDWs as mentioned below:

  • Snowflake

    • FACTORIAL

  • Databricks Lakehouse Platform

    • FACTORIAL

  • Redshift

    • DEXP

Default Value: None.
ExampleCBRT

Argument*

String/Expression

Each of the exponent and root unary functions needs only one value/argument. Hence, specify an argument for the selected expression/function. This is typically a column name in your source table.
Default Value: None.
ExampleORDER_AMT

Alias*

String

Specify the column in which to display the result of the unary function. You can also reference this name in downstream Snaps to process the data further. 

Default Value: None.
ExampleA_OAMT

Exponent and root binary functions

Specify your exponent and root binary function/operation to be performed on any two table columns/their values, using this fieldset. Click to add a new row. Ensure that you specify each function in a separate row.

This fieldset consists of the following fields:

  • Exponent and root binary expression name

  • Argument

  • Argument

  • Alias

Exponent and root binary expression name*

String/Suggestion

Select an exponent and root binary expression/function to use. Click the Suggest icon to retrieve the list of the supported exponent and root binary 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.
You can use the following binary functions:

  • HYPOT

  • POWER

Default Value: None.
ExamplePOWER

Argument*

String/Expression

Each of the exponent and root binary functions needs two values/arguments. Specify the first argument in this field for the selected expression/function. This is typically a column name in your source table.
Default Value: None.
ExampleORDER_ID

Argument*

String/Expression

Each of the exponent and root binary functions needs two values/arguments. Specify the second argument in this field for the selected expression/function. This is typically a column name in your source table.
Default Value: None.
ExampleORDER_IDS

Alias*

String

Specify the column in which to display the result of the binary function. You can also reference this name in downstream Snaps to process the data further. 

Default Value: None.
Example: A_ORDIDS

Logarithmic unary functions

Specify your logarithmic unary function/operation to be performed on a single table column/its value, using this fieldset. Click to add a new row. Ensure that you specify each function in a separate row.

This fieldset consists of the following fields:

  • Logarithmic unary expression name

  • Argument

  • Alias

Logarithmic unary expression name*

String/Suggestion

Select a logarithmic unary expression/function to use. Click the Suggest icon 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.
You can use the following logarithmic unary functions:

  • LN

  • LOG1P

  • LOG2

In addition to the above, you can use certain mathematical functions with specific target CDWs as mentioned below:

  • Snowflake

    • LOG10

  • Databricks Lakehouse Platform

    • LOG10

  • Azure Synapse

    • LOG10

  • BigQuery

    • LOG10

  • Redshift

    • DLOG10

Default Value: None.
ExampleLOG2

Argument*

String/Expression

Each of the logarithmic unary functions needs only one value/argument. Hence, specify an argument for the selected expression/function. This is typically a column name in your source table.
Default Value: None.
ExampleORDER_AMT

Alias*

String

Specify the column in which to display the result of the unary function. You can also reference this name in downstream Snaps to process the data further. 

Default Value: None.
ExampleA_OAMT

Logarithmic binary functions

Specify your logarithmic binary function/operation to be performed on any two table columns/their values, using this fieldset. Click to add a new row. Ensure that you specify each function in a separate row.

This fieldset consists of the following fields:

  • Logarithmic binary expression name

  • Argument

  • Argument

  • Alias

Logarithmic binary expression name*

String/Suggestion

Select a logarithmic binary expression/function to use. Click the Suggest icon to retrieve the list of the supported logarithmic binary 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.
You can use the following binary functions:

  • LOG

Default Value: None.
ExampleLOG

Argument*

String/Expression

Each of the logarithmic binary functions needs two values/arguments. Specify the first argument in this field for the selected expression/function. This is typically a column name in your source table.
Default Value: None.
ExampleORDER_ID

Argument*

String/Expression

Each of the logarithmic binary functions needs two values/arguments. Specify the second argument in this field for the selected expression/function. This is typically a column name in your source table.
Default Value: None.
ExampleORDER_IDS

Alias*

String

Specify the column in which to display the result of the binary function. You can also reference this name in downstream Snaps to process the data further. 

Default Value: None.
Example: A_ORDIDS

Trigonometric general functions

Specify your trigonometric general function/operation to be performed on a single table column/its value, using this fieldset. Click to add a new row. Ensure that you specify each function in a separate row.

This fieldset consists of the following fields:

  • Trigonometric unary expression name

  • Argument

  • Alias

Trigonometric general expression name*

String/Suggestion

Select a trigonometric general expression/function to use. Click the Suggest icon 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.
You can use the following trigonometric general functions:

  • E

  • PI

Default Value: None.
ExamplePI

Alias*

String

Specify the column in which to display the result of the trigonometric general function. You can also reference this name in downstream Snaps to process the data further. 

Default Value: None.
ExampleA_OAMT

Trigonometric unary functions

Specify your trigonometric unary function/operation to be performed on a single table column/its value, using this fieldset. Click to add a new row. Ensure that you specify each function in a separate row.

This fieldset consists of the following fields:

  • Trigonometric unary expression name

  • Argument

  • Alias

Trigonometric unary expression name*

String/Suggestion

Select a trigonometric unary expression/function to use. Click the Suggest icon 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.
You can use the following trigonometric unary functions:

  • ACOS

  • ACOSH

  • ASIN

  • ASINH

  • ATAN

  • ATANH

  • COS

  • COSH

  • COT

  • DEGREES

  • RADIANS

  • SIN

  • SINH

  • TAN

  • TANH

Default Value: None.
ExampleDEGREES

Argument*

String/Expression

Each of the trigonometric unary functions needs only one value/argument. Hence, specify an argument for the selected expression/function. This is typically a column name in your source table.
Default Value: None.
ExampleORDER_AMT

Alias*

String

Specify the column in which to display the result of the unary function. You can also reference this name in downstream Snaps to process the data further. 

Default Value: None.
ExampleA_OAMT

Trigonometric binary functions

Specify your trigonometric binary function/operation to be performed on any two table columns/their values, using this fieldset. Click to add a new row. Ensure that you specify each function in a separate row.

This fieldset consists of the following fields:

  • Trigonometric binary expression name

  • Argument

  • Argument

  • Alias

Trigonometric binary expression name*

String/Suggestion

Select a trigonometric binary expression/function to use. Click the Suggest icon to retrieve the list of the supported trigonometric binary 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.
You can use the following binary functions:

  • ATAN2

Default Value: None.
ExampleLOG

Argument*

String/Expression

Each of the trigonometric binary functions needs two values/arguments. Specify the first argument in this field for the selected expression/function. This is typically a column name in your source table.
Default Value: None.
ExampleORDER_ID

Argument*

String/Expression

Each of the trigonometric binary functions needs two values/arguments. Specify the second argument in this field for the selected expression/function. This is typically a column name in your source table.
Default Value: None.
ExampleORDER_IDS

Alias*

String

Specify the column in which to display the result of the binary function. You can also reference this name in downstream Snaps to process the data further. 

Default Value: None.
Example: A_ORDIDS

Troubleshooting

Error

Reason

Resolution

Error

Reason

Resolution

Missing property value

You have not specified a value for the mandatory field where this message is displayed.

Ensure that you specify appropriate values for all mandatory fields in the Snap configuration.

Downloads