Snowflake - Lookup

On this Page

Overview

You can use this Snap to look up records in the target Snowflake SQL Server table and returns a selected set of fields for every matched record. The Snap executes one request per multiple input documents to avoid making a request for every input record. The Snap ignores any duplicated lookup condition in the input document stream since it maintains a cache for lookup conditions internally. Queries produced by the Snap have the format:

SELECT [Output fields] FROM [Table name] WHERE [C1 = V11 AND C2 = V21 AND...[Cn = Vn1] OR [C1 = V12 AND C2 = V22 AND...[Cn = Vn2] OR ...................................... OR [Cn = V1n AND Cm = V2m AND...[Cn = Vnm]

The Snap ignores any duplicated lookup condition in the input document stream since it maintains a cache for lookup conditions internally. 

Snap Type

The Snowflake - Lookup Snap is a Read-type Snap that provides the ability to format WHERE clause from input document stream for a SQL Server query. 

Prerequisites

Security Prerequisites

You should have the following permissions in your Snowflake account to execute this Snap: 

  • Usage (DB and Schema): Privilege to use database, role, and schema.

The following commands enable minimum privileges in the Snowflake Console:

grant usage on database <database_name> to role <role_name>; grant usage on schema <database_name>.<schema_name>;

For more information on Snowflake privileges, refer to Access Control Privileges.

Internal SQL Commands

This Snap uses the LOOKUP command internally. It queries the database and retrieves a set of rows or defines the set of columns returned by a query.

Support for Ultra Pipelines

Works in Ultra Pipelines. However, we recommend that you not use this Snap in an Ultra Pipeline.

Known Issues

Because of performance issues, all Snowflake Snaps now ignore the Cancel queued queries when pipeline is stopped or if it fails option for Manage Queued Queries, even when selected. Snaps behave as though the default Continue to execute queued queries when the Pipeline is stopped or if it fails option were selected.

Snap Views

Type

Format

Number of Views

Examples of Upstream and Downstream Snaps

Description

Type

Format

Number of Views

Examples of Upstream and Downstream Snaps

Description

Input

Document

  • Min: 1

  • Max: 1

  • JSON Formatter

  • Mapper

This Snap allows exactly one input view and expects documents in the view.

Output

Document

  • Min: 1

  • Max: 1

  • CSV Parser

  • JSON Parser

This Snap has at most one document output view.

Error

Error handling is a generic way to handle errors without losing data or failing the Snap execution. You can handle the errors that the Snap might encounter when running the Pipeline by choosing one of the following options from the When errors occur list under the Views tab:

  • Stop Pipeline Execution: Stops the current pipeline execution if the Snap encounters an error.

  • Discard Error Data and Continue: Ignores the error, discards that record, and continues with the remaining records.

  • Route Error Data to Error View: Routes the error data to an error view without stopping the Snap execution.

Learn more about Error handling in Pipelines.

Snap Settings

  • Asterisk (*): Indicates a mandatory field.

  • Suggestion icon (): Indicates a list that is dynamically populated based on the configuration.

  • Expression icon (): Indicates whether the value is an expression (if enabled) or a static value (if disabled). Learn more about Using Expressions in SnapLogic.

  • Add icon (): Indicates that you can add fields in the field set.

  • Remove icon (): Indicates that you can remove fields from the field set.

Field Name

Field Type

Description

Label*

 

Default ValueSnowflake - Lookup
Example: Load Employee Tables

String

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

Schema name


Default Value: N/A
Example: schema_demo

String/Expression

Specify the database schema name. In case it is not defined, then the suggestion for the Table Name will retrieve all table names of all schemas. The property is suggestible and will retrieve available database schemas during suggest values.

The values can be passed using the pipeline parameters but not the upstream parameter.

Table name*

 

Default Value: N/A
Example: employees_table

String/Expression

Enter or select the name of the Snowflake SQL table to execute the lookup query.

The values can be passed using the pipeline parameters but not the upstream parameter.

Output fields

 

Default Value: N/A
Example: ID

String/Expression

Enter or select output field names for the Snowflake SQL SELECT statement.

Lookup conditions*

Use this field set to enter or select the lookup column name.

Value*

 

Default Value: N/A
Example: $email

String/Expression

Enter or select the JSON path of the lookup column value. The value will be provided by the input data field. 



Lookup column name*

 

Default Value: N/A
Example: $email

String/Expression

Specify the lookup conditions are created by using the lookup column name and the lookup column value. Each row will build a condition, such aslookupColumn1 = $inputField. Each additional row will be concatenated using a logical AND. All rows together build the lookup condition being used to look up records in the lookup table.

Pass-through on no lookup match

 

Default ValueDeselected

Checkbox

When there is no lookup matching an input document, the input document will pass through to the output view if this property is checked. Otherwise, it will be written to the error view as an error condition.

Number of Retries

 

Default Value0
Example: 3

Integer/Expression

Specify the maximum number of retry attempts when the Snap fails to read.

Minimum value: 0

Retry Interval (seconds)

 

Default value: 1
Example: 3

Integer/Expression

Specifies the minimum number of seconds the Snap must wait before each retry attempt.

Minimum value: 1

 

Input Date Format

 

Default ValueContinue to execute the snap with given input Date format
Example: Auto Convert the format to Snowflake default format

Dropdown list

The property has the following two options:

  • Select Continue to execute the snap with the given input Date format if you want the Snap to continue with the current date format. This option is selected by default. 

  • Select Auto Convert the format to Snowflake default format if you want the Snap to convert the provided date format to the default Snowflake date format. To know about the date formats supported by Snowflake, see Snowflake date formats

Handle Timestamp and Date Time Data

 

Default ValueDefault Date Time format in UTC Time Zone
Example: SnapLogic Date Time format in Regional Time Zone

Dropdown list

Specify how the Snap must handle timestamp and date-time data. The available options are:

  • Default Date Time format in UTC Time Zone: The Snowflake date time data are represented in UTC Time Zone.

  • SnapLogic Date Time format in Regional Time Zone: The Snowflake date time data are represented in the same regional time zone value, as provided in the Snowflake account.

Manage Queued Queries

 

Default Value: Continue to execute queued queries when the Pipeline is stopped or if it fails
Example: Cancel queued queries when the Pipeline is stopped or if it fails

Dropdown list

Select this checkbox to determine whether the Snap should continue or cancel the execution of the queued Snowflake Execute SQL queries when you stop the Pipeline.

 

Default Value: Validate & Execute
Example: Execute only

Dropdown list

Example


The following example shows how a Snowflake object record can be looked up using the Snowflake Lookup Snap and record the data using the Snowflake Execute Snap. In this pipeline, the Snowflake Execute Snap writes the data from the Snowflake table ADOBEDATA123 to the target Snowflake table, ADOBEDATA using the Snowflake Lookup Snap.  

1. The Execute Snap runs the select query to retrieve the data from the table ADOBEDATA123

2. The Snowflake Lookup Snap runs the lookup query on, and the lookup conditions specify the column names to be looked up in the look-up table ADOBEDATA123. 

Additionally, in the Output fields, select the names of the specific output field name(s) for the output preview to contain only those fields("ACCOUNT_C", "ADOBE_ROLE_C", "ID")  to be recorded on the target table, ADOBEDATA.

Successful execution of the Snap gives the following preview:

Note that the output preview has only those column names that were specified in the output fields. The column, original, displays all the column names present in the original table (ADOBEDATA123) as displayed below:

Snap Pack History