...

In this article

Overview

You can use this Snap to...

Prerequisites

• Valid client ID.
• A valid account with the required permissions.

None.

Support for Ultra Pipelines

• Works in Ultra Pipelines. 
• Works in Ultra Pipelines if....
• Does not support Ultra Pipelines. 

...

...

• Always use title casing for Heading formats 1 and 2.
• Always use active voice.
• Do not use "Please" anywhere in the document.
• Screenshots
  • Always use the New Form UI.
  • Be optically similar. Max size 1000 px or corresponding gridline size as in the style guide.
  • Do not capture Snap borders when showing configurations in the Examples section. You can add a border in the editor here.
  • See Image Style Guide for details.
• Examples must always use first-person plural references. You can use the second-person if needed depending upon the example's content.

Table of Contents
minLevel 1 maxLevel 2 absoluteUrl true

Multiexcerpt include macro
name ME_NE_ELT_Snap_Accounts templateData [] page ELT Case Expression addpanel false

Overview

You can use this Snap to retrieve the first non-NULL expression among a set of arguments. The Snap returns NULL if all its arguments are NULL. If you expect a query to return null values for certain functions or columns, you can use this Snap to replace the NULLs with some other value—typically in the case of certain aggregate functions that return null values instead of zero when they have no rows to evaluate.

COALESCE Arguments	Result
coalesce(NULL, 2, NULL)	2
coalesce(1, NULL, NULL)	1
coalesce(NULL, NULL, 3)	3
coalesce(NULL, NULL, NULL)	NULL

Image Added

Snap Type

ELT Coalesce Snap is a TRANSFORM-type Snap that extracts non-NULL values from datasets containing NULL values.

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

None.

Known Issues

None.

Snap Input and Output

Input/Output	Type of View	Number of Views	Examples of Upstream and Downstream Snaps	Description
Input	Document

Binary

Binary or Document

...

• Mapper Snap
• Copy Snap
• ..

...

Binary

Binary or Document

...

• ..
• ..

...

...

**Delete Before Publishing**

Choose from the following sentences to document specific field types.

...

...

Check boxes:

• If selected, <Snap behavior>.
  If selected, an empty file is written when the incoming document has no data.
• If selected, <behavior>. If not selected/Otherwise, <behavior>
  Use "If not selected" if the first sentence is long.
  If selected, the Snap uses the file path value as is. Otherwise, the Snap uses the file path value in the URL.
  If selected, an empty file is written when the incoming document has empty data. If there is no incoming document at the input view of the Snap, no file is written regardless of the value of the property.

• Select to <action>
  Use this if the behavior is binary. Either this or that, where the converse behavior is apparent/obvious.
  Select to execute the Pipeline during validation.

Text Fields

• Describe what the user shall specify in this field. Additional details, as applicable, in a separate sentence. Include caveats such as the field being conditionally mandatory, limitations, etc.
  Enter the name for new account.
  Specify the account ID to use to log in to the endpoint.
  Required if IAM Role is selected.
  Do not use this field if you are using batch processing.
  

Numeric Text Fields

• Describe what the field represents/contains. Additional details, as applicable, in a separate sentence. Include caveats such as the field being conditionally mandatory, limitations, etc. Include special values that impact the field's behavior as a bullet list.
  The number of records in a batch.
  The number of seconds for which you want the Snap to wait between retries.
  The number of seconds for which the Snap waits between retries.
  Use the following special values:
  * 0: Disables batching.
  * 1: Includes all documents in a single request.

...

Field Name

...

Field Dependency

...

Description

Default Value: 

Example: 

...

Label*

...

None.

...

Specify a unique label for the Snap.

Default Value: Channel Operations
Example: Delete Member

...

Number of records

...

Sampling Type is Number of records.

...

Enter the number of records to output.

...

Field set

...

• Field 1

• Field 2

• Field 3

...

Field 1*

...

Field 2

...

None.

...

Examples

Excluding Fields from the Input Data Stream

We can exclude the unrequired fields from the input data stream by omitting them in the Input schema field set. This example demonstrates how we can use the <Snap Name> to achieve this result:

...

...

Batch execution failed

...

The Pipeline ended before the batch could complete execution due to a connection error.

Verify that the Batch size field is configured to handle the inputs properly. If you are not sure when the input data is available, configure this field as zero to keep the connection always open.

None.

Examples

Applying COALESCE Operation on a Dataset

In this Pipeline example, we apply the COALESCE operation on a dataset using the ELT Coalesce Snap.

Image Added

To begin with, we use an ELT Select Snap to read the dataset from COALESCE_SRC table in a source database (Redshift in this example). The Snap retrieves the data from the table. You can see this dataset in JSON format in this Snap’s preview shown below.

ELT Select Snap	Output
Image Added	Image Added

We connect the ELT Coalesce Snap to the ELT Select Snap to apply the COALESCE operation on this dataset.

ELT Coalesce Snap	Output
Image Added	Image Added

In this Snap, we provide an alias name for the COALESCE operation - test_coalesce and define the arguments—columns in the dataset to be considered for the operation—from the Coalesce Arguments fieldset. In the adjacent Snap Output (preview) image, we can see the results of the COALESCE operation on the selected arguments (I1, I2, and I3, in this case). The values returned are the first non-null values from each row in the data set.

We write these values to a different table - COALESCE_TC2 in the Redshift database using an ELT Insert Select Snap. Here we select the Overwrite checkbox to make sure that the data is written to a fresh(empty) table.

Image Added

Download this Pipeline. 

Downloads

Note

...

...

...

...

...

...

...