ELT Conditional Function
In this article
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 | Document |
|
| A document containing the SQL query that yields the data required to perform the conditional (unary or binary) operations. |
Output | Document |
|
| 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.
You can use the SQL Expressions and Functions supported for ELT for this purpose. This list is common to all target CDWs supported. You can also use other expressions/functions that your target CDW supports.
Field names marked with an asterisk ( * ) in the table below are mandatory.
Field Name | Type | Description |
---|---|---|
Label* | String | Specify a unique label for the Snap. Default Value: ELT Conditional Function |
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:
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 Value: Not selected |
Pass through | Checkbox | Select this checkbox to specify that the Snap must include the incoming document (SQL query) in its output document. Default Value: Not selected |
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* | 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.
Default Value: None. |
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. |
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. |
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* | 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.
Default Value: None. |
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. |
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. |
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. |
Troubleshooting
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. |