Integrating with Snowflake
  • 04 Apr 2024
  • 2 Minutes to read
  • Dark
    Light

Integrating with Snowflake

  • Dark
    Light

Article summary

Overview

Snowflake is a data warehousing service that can support a high amount of workloads simultaneously. By integrating with Snowflake, users can access Snowflake's ability to store and share large amounts of data and Decisions' library of automated Workflows, Steps, Reports, and more. 

Prerequisites:


Configuration

  1. Navigate to Integrations > Databases and click CREATE CONNECTION
  2. In the Database Integration dialog, define a Connection Name. From the Database Type dropdown, select ODBC.
  3. Enter the Connection String to connect to the Snowflake database. Click PICK OR CREATE ODBC DB TYPE and select Pick Existing.

    Connection String Requirements
    • All values in the Connection String are case sensitive. This includes the Snowflake database name, schema, role, etc.
    • The Connection String should not contain single quotation marks (') because it will cause errors.  
    Driver={SnowflakeDSIIDriver};Server=abc12345.dataCenter.snowflakecomputing.com;account=abc12345;Database=SNOWFLAKE_SAMPLE_DATA;Uid=snowflakeUserID;Pwd=snowflakePassword;schema=public;warehouse=COMPUTE_WH;role=SYSADMIN;


    Driver={SnowflakeDSIIDriver};

    Reference the ODBC driver by name

    Server=abc12345.dataCenter.snowflakecomputing.com;

    account=abc12345;

    The account id and datacenter location provided by Snowflake.

    Database=SNOWFLAKE_SAMPLE_DATA;

    The database name

    Uid=snowflakeUserID;

    Pwd=snowflakePassword;

    Snowflake username and password

    schema=public;

    warehouse=COMPUTE_WH;

    role=SYSADMIN;

    Data usage details
  4. Navigate to ALL > DatabaseDefinitionWizard, select Snowflake, and click PICK.
  5. Click TEST to test the database connection. If the Test Status is Success, click OK to save.

Troubleshooting Tips
Please note that a successful connection test does not necessarily indicate that it is a proper connection string. If a user is getting an error when trying to add a new table, the user should check the logs for more details. It is most likely that there is a missing item from the connection string or the connection string is misconfigured. If there are no tables to select from the dropdown, it is likely that the Schema, Warehouse, or Role Values have been misconfigured in the connection string. 


Communicate with Integrated Table

A Flow can be created to demonstrate how to communicate with the Integrated Database Table using the System generated steps in a Workflow process. The steps for the integrated table can be found under Integration > Database > [Integrated Database Name] > [Integrated Table Name] in the Flow Designer.


Special Query Characters

Snowflake query commands can be viewed here.

CharacterFunction
?Specify a parameter. ? takes the order of the parameters so a user can re-order parameters accordingly. Each ? is a different parameter. The field must be specified in the INPUT PARAMETERS field. Example screenshots are below.
*The output should include all columns of the specified object, or all columns of all objects if * is not qualified with an object name or alias.
$$$$Unlimited non-negative integer constan.t
' 'Empty string treated as 0.

MATCH_RECOGNIZE 

CharacterFunction
<^>Start of partition
<$>End of partition
+Quantifier - 1 or more
*Quantifier - 0 or more
?Quantifier - 0 or 1
{n}Quantifier - Exactly n
{n, }Quantifier - n or more
{,m}Quantifier - 0 to m
{n,m}Quantifier - n to m
... ...Operators - Concatenation
{- ... -}Operators - Exclusion
( ... )Operators - Grouping
PERMUTE(..., ...)Operator - Permution
... | ...Operator - Alternative

WHERE

CharacterFunction
+Used to make joins into outer joins

Specify a Parameter 

This example returns the first three parameters. CALL sp.example(?,?,?)



For further information on Integrations, visit the Decisions Forum.

Was this article helpful?