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

[MikeHowes]

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?

[apathetichell]

I believe you are specifying the schema somewhere in your input tool as well. Snowflake operates under a db.schema.table syntax. If you do not have both db and schema you cannot access your table for reading or writing.

and my prefered way is via an odbc 64 set up and in-db connection in Alteryx. I then add Datastream-In to a macro and feed in specific output locations (db.schema.table) via the macro. It's a pretty straight forward macro.

[mmichaelson]

@apathetichell - Are you able to share an example of this?  I am kicking around several ways to make configuring Snowflake connections more flexible.  Our setup utilizes many databases, schemas, roles, etc., and it would be nice to minimize the number of ODBC connections we need to maintain.

Thanks in advance.

[apathetichell]

are you using fully qualified names to output (ie db.schema.table) or just schema.table? i can create a quick macro but it's good to know what you'll be feed in.

[mmichaelson]

@apathetichell - I am using fully qualified names.

[apathetichell]

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.

[mmichaelson]

@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.