ELT Conditional Function

Overview

You can use this Snap to perform conditional—unary and binary—operations on your dataset. This Snap provides a list of expressions or functions based on the target database configured in its Account. Further, these functions are segregated into unary and binary conditional functions.

Snap Type

ELT Conditional Function Snap is a TRANSFORM-type Snap that performs conditional function 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 & 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 Transform

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

Output

Document

  • Min: 1

  • Max: 1

  • ELT Select

  • ELT Filter

  • ELT Transform

  • ELT Insert Select

A document containing the incremental SQL query that includes the conditional 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

Specify a unique label for the Snap.

Default ValueELT Conditional Function
ExampleUnary_Binary_Checks

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

Conditional Unary Functions

Specify your conditional 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 field set consists of the following fields:

  • Unary Conditional Expression Name

  • Argument

  • Alias

Unary Conditional Expression Name*

String/Suggestion

Select a unary expression/function to use. Click the Suggest icon to retrieve the list of the supported unary conditional 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 unary functions:

  • BOOLNOT

  • ISNOTNULL

  • ISNULL

  • NULLIFZERO

  • ZEROIFNULL

Default Value: None.
ExampleISNULL

Argument*

String/Expression

Each of the unary conditional 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_ID

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.
Example: A_OID

Conditional Binary Functions

Specify your conditional 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 field set consists of the following fields:

  • Binary Conditional Expression Name

  • First Argument

  • Second Argument

  • Alias

Binary Conditional Expression Name*

String/Suggestion

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

  • BOOLAND

  • BOOLOR

  • BOOLXOR

  • EQUAL_NULL

  • NULLIF

  • NVL

  • REGR_VALX

  • REGR_VALY

Default Value: None.
ExampleNVL

First Argument*

String/Expression

Each of the binary conditional 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

Second Argument*

String/Expression

Each of the binary conditional 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

Invalid placement of ELT Conditional Function snap

ELT Conditional Function Snap cannot be used at the start of a Pipeline.

Move the ELT Conditional Function Snap to either the middle or the end of the Pipeline.

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

  File Modified

File FEP1_ELT_Conditional_Function.slp

Feb 16, 2022 by Anand Vedam