TDCB-1263: Snowflake Retrieves all Primary Keys if Database is not Specified

[londonhanson]

Issue

Snowflake retrieves all primary keys if the database is not specified.

Environment Details

• Alteryx Designer

  • Versions < 2021.4

• Snowflake

• Simba Snowflake ODBC Driver

Cause

Querying the metadata in Snowflake can be slow at times. When a database is not specified, primary keys of all tables are requested in order to find the proper table. This can lead to slower read/write times in Designer when using Snowflake.

Here is an example of a bulk loading DSN connection without the database name:

snowbl:DSN=Simba Snowflake;UID=cs_snf-00; PWD=__EncPwd1__;Staging=local; Method=user

Here is an example of the same connection string with a database specified ("Database1"):

snowbl:DSN=Simba Snowflake;UID=cs_snf-00; PWD=__EncPwd1__;Staging=local; Method=user|||Database1

TDCB-1263/DE20744 has been logged to address this.

Resolution

 

Solution A:

1. If you have already created a Snowflake ODBC connection within the ODBC Administrator, proceed to step 2. Otherwise, create the ODBC connection according to this documentation.

2. In Designer, configure the ODBC connection in one of the following tools:

   • Input Data 

   • Output Data 

   • In-DB Data Stream In

   • In-DB Data Stream Out 

3. Edit the DSN connection string by clicking into the text box containing the string.

4. Add three pipe symbols "|||" (quotes removed) to the end of the connection string.

5. Type the database name after the three pipes. The connection should look similar to the image below (where "Customer" is the database name):

odbc:DSN=Snowflake_NEW;UID=cs_snf-00;PWD=__EncPwd1__|||Customer

image.png
 

Solution B:

Upgrade to Designer version 2021.4 or newer.

Additional Resources

• Alteryx Snowflake Help Documentation
• How to Troubleshoot Slow Database Read/Write Speed