On this Page

1 Overview
- 1.1 Snap Type
- 1.2 Prerequisites
- 1.3 Support for Ultra Pipelines
- 1.4 Known Issues
2 Snap Views
3 Snap Settings
4 Examples
- 4.1 Lookup a Snowflake Object
5 Snap Pack History

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 Value: Snowflake - Lookup Example: Lookup 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 Value: Deselected		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 Value: 0 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 Value: Continue 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 Value: Default 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. Recommendation: If you use the Timestamp TZ and Timestamp LTZ in this Snap, we recommend you to use SnapLogic Date Time format in Regional TimeZone to ensure that you get the Timestamp data output of the target table in the same format as in the source table. Source Table Target Table
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 an option to determine whether the Snap should continue or cancel the execution of the queued Snowflake Execute SQL queries when you stop the pipeline.
Snap Execution Default Value: Validate & Execute Example: Execute only		Dropdown list	Select one of the following three modes in which the Snap executes: Validate & Execute: Performs limited execution of the Snap, and generates a data preview during Pipeline validation. Subsequently, performs full execution of the Snap (unlimited records) during Pipeline runtime. Execute only: Performs full execution of the Snap during Pipeline execution without generating preview data. Disabled: Disables the Snap and all Snaps that are downstream from it.

Examples

Lookup a Snowflake Object

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:

Snowflake - Lookup