Most of the default Snowflake operations within Boomi require the warehouse name, database name, and schema to be defined. Yet, the Snow SQL operation will execute a statement as if it was within the Snowflake console. Therefore, a Snow SQL statement can be executed in a more dynamic fashion, if needed. The article outlines how to dynamically set the database name and schema within a Snow SQL.Below is the example Snow SQL statement that will used throughout this example. There are two Snowflake databases that are used within this example that are called account and contact. Both databases are located within the same warehouse. They will be joined within the statement below. Aliases are used for the account_name and contact_name tables so that the [database].[schema].[table] only needs to be defined once. The rest of the guide will focus on this SQL statement as an example.

SELECT
    a.account_id,
    a.name,
    c.contact_id,
    c.name as contact_name
FROM account.public.account_name a
JOIN contact.public.contact_name c
    ON a.account_id = c.account_id;

Overview of Implementation

The process starts with a map that will be used to populate the database names dynamically and the document used will contain the variables used within the Snowflake operation. The map contains functions to get Process Properties. These process properties can be extended per environment. Therefore, this will be used to modify the database name, schema, and table per environment through the use of environment extensions. The map will be set up last because it contains the profile that will be created within the Snowflake Snow SQL Operation.

First, add a Snowflake connector to the canvas and create a Snow SQL Operation. Once created, import a profile. A profile called Snowflake Dummy_Profile snowSQL Request will be created. This profile name can be updated to reflect your organization’s naming best practices. Once imported, go ahead, and update the profile to add two additional elements.

The account element will be used to define the account database within the Snow SQL statement. The contact element will be used to define the contact database within the Snow SQL statement.

Next, within the operation, update the SQL script with the statement below. The statement will use Snowflake’s INDENTIFIER() function to define it as a variable (e.g. identifier($my_table)). Boomi treats the dollar sign as a variable that matches the incoming JSON data. Below is the statement that is used within the SQL Script section of the Snowflake Operation. Notice that the $account and $contact match the element names within the JSON profile. Also, columns can not have the same name because the output would have duplicate JSON elements. To account for this, the c.name was renamed as contact_name.

SELECT
    a.account_id,
    a.name,
    c.contact_id,
    c.name as contact_name
FROM identifier($account) a
JOIN identifier($contact) c
    ON a.account_id = c.account_id

Next, set up the Map. In the source side profile use a flat file profile with a single element. On the destination side, use the Snowflake Dummy_Profile snowSQL Request that was created. Within the middle add two Get Process Properties functions. Those Process Properties contain the [database].[schema].[table] that will be used within the Snow SQL statement. Process Properties were used only as an example way to populate the data.

Below is the output of the map.

{
  "account" : "account.public.account_name",
  "contact" : "contact.public.contact_name"
}

Example return from the Snowflake connector:

Article originally posted at Boomi Community.