Search

Snowflake Lookup Configuration

Version 23.4.8839

Snowflake Lookup Configuration

The Lookup action retrieves values from Snowflake and inserts those values into an existing Arc message in the flow.

  1. In the CData Arc flow, drag an arrow from the connector that will provide input to the Snowflake connector.

  2. Optionally, choose a Sample File. A sample file is recommended because it allows the connector to provide more context about the file structure and helps you build filter conditions.

  3. Once you have completed the configuration steps below, in the Arc flow, drag an arrow from the Snowflake connector to the connector where you want your resulting XML or Header data to go.

Lookup Configuration

Use this section to define the table and columns to include in your query. Designer Mode helps you pick the table and columns you want to target with your query, instead of requiring you to build the query free form. However, you can use Query Mode if you prefer to write your query from scratch.

Designer Mode

Follow the steps below to use the Designer to configure a Lookup action for the Snowflake connector.

  1. Use the Table drop-down to select the table to include.

  2. Choose the set of Columns that you want to include from the table.

  3. Output Destination: select XPath to insert the query response values into an XML file or Header to insert the query response value into a message header.
    • XPath: When you choose XPath, the destination is in two parts. The Parent-Level XPath should already exist in the XML document. The New Node Name is the name of the node that is injected into the XML document to hold the result of the lookup. Each column in the query has its own XML element (leaf level) in the XML element provided here.
    • Header: When you choose Header, the connector adds one header to the output message for each column in the query. The values are comma-separated for each record that is returned from the database.
  4. Use Record Limit to specify what should happen when the query returns multiple results.
    • If you choose Use First Result from the dropdown, you can use the Error if multiple results to see an error when multiple results are returned.
    • If you choose Custom Limit, you must specify the limit in the field that appears.

  5. Use Order by to choose which column to order the results by.

  6. The Filters section lets you create filters to build the query WHERE clause, including references to headers or XML from the input file.

Filters

You can build complex filters by defining individual filters and/or groups of filters. For readability, expand the Filters section by clicking the expander icon expander on the right side of the bar.

To add a filter, click Add Filter or Add Group.

When you build your filters, use the dropdowns to make your selections and choose your operator. Then specify the value to filter by. You can choose from the following:

  • Message Header
  • Input XML
  • Static Value

When you select Input XML for a filter, if you have uploaded a sample file, the editor displays a tree representation of the document so you can easily find the XPath you need, as shown in the image below.

You can reorder filters and groups. Click the handle handle and drag the object to a new location.

Query Mode

Follow the steps below to use the Query mode to configure a Lookup action for the Snowflake connector.

  1. Output Destination: select XPath to insert the query response values into an XML file or Header to insert the query response value into a message header.
    • XPath: When you choose XPath, the destination is in two parts. The Parent-Level XPath should already exist in the XML document. The New Node Name is the name of the node that is injected into the XML document to hold the result of the lookup. Each column in the query has its own XML element (leaf level) in the XML element provided here.
    • Header: When you choose Header, the connector adds one header to the output message for each column in the query. The values are comma-separated for each record that is returned from the database.
  2. In Query, enter the SQL query to retrieve data from Snowflake tables.
    • XPath: Format the path as ${XPATH:/path/to/element}. For example, SELECT CustomerID FROM Customers WHERE CustomerName = ${XPATH:/Items/Invoices/Customer}.
    • Header: Format the path as ${HEADER:HeaderName}.
  3. The two checkboxes let you configure what should happen when the query returns multiple results.
    • Check Use First Result to always use the first result.
    • Check Error if multiple results to see an error when multiple results are returned.

Advanced

  1. Use No Matching Record Found Action to specify what should happen if no record is found that matches the query.

  2. The Snowflake connector can perform Lookup queries against a local cache instead of querying Snowflake directly for each Lookup. This can improve query performance, especially with larger databases. To enable this, check Enable cache and configure the rest of the fields.

    • Cache Tolerance: Controls how often the connector issues a query to the data source to refresh the cache.
    • Cache Tolerance Unit: The Cache Tolerance unit. Choose Minute, Hour, or Day.
    • Cache Provider: Choose which driver to use to facilitate the connection to the cache database.
    • Connection String When set, the connector uses the database specified here for caching.

    Notes:

    • If a query returns no results from the cache, the connector queries Snowflake directly.

    • If your query statement contains a limit, offset, or groupby parameter, the cache function does not work. These queries go directly to Snowflake.

Test Lookup

Use the Test Lookup button to check your configuration and see the result without having to process files manually. Use the Save Sample Data button to save the output as a sample for mapping later in the flow.