Alteryx to Snowflake output tool - do I need to specify the ODBC Schema?

Question

I am investigating how to connect Snowflake and Alteryx.

Using an ODBC connector that only specifies the Database I am able to access all data I need as an input.

However as an output this does not work.

If I create an ODBC connector which details the schema I can successfully output the data into a table in Snowflake. However this method means I need a unique connector created for each schema and Database I have.

Is there a better way I can approach this? I need to take bring data from alteryx to a table in Snowflake - Is the only way to use an ODBC connector that specifies the database and schema for the table?

mmichaelson · Answer

@apathetichell Thanks for sharing.  While this approach makes sense, most Alteryx Designer users (at least within our organization) don't have a strong grasp on macros or ODBC connection strings.  That said, this is something that I would likely centralize to make connecting to Snowflake as seamless as possible while complying with our org's governance & security standards.  But, this does solve the problem.

@MikeHowes  I have seen the same thing.  The ODBC connection string to read data from Snowflake has different requirements than the ODBC connection string to write data to Snowflake.  The idea presented by @apathetichell (using macros) is one way you can write data to multiple databases/schemas/tables without cluttering up your workflow with multiple input/output tools.

Ideally, Alteryx would have a "Snowflake Input" or "Snowflake Output" tool similar to what they've done with Tableau and Dropbox.  This would allow users to connect to Snowflake based on their specific level of access and avoid managing ODBC connections on multiple machines.  At the time of this post, these tools do not exist (to my knowledge), but hopefully these are something Alteryx is considering.

apathetichell · Answer

If you are using a named in-db connection - try this.

Notes - t

1) this assumes you need tables in quotes style so "db"."schema"table"

2) you would set up a db less/schema less connection in your odbc 64 and need to set up a named connection in your in-db.

3) ths would allow for mutliple named connections/tables in a single run.

4) Overwrite/drop allows you to create a new table. create does not allow you to overwrite/drop.

snowflake file writer.yxzp

mmichaelson · Answer

@apathetichell - I am using fully qualified names.