DCM and ODBC at scale - Looking for advice

[jensroy]

Hello Alteryx Community,

We currently have our Alteryx Server in production along with several legacy data connection configurations that we need to optimize. Our goal is to simplify our setup so that both server and Designer users only require three ODBC DSN configurations—or perhaps we might even move to an ODBC-less approach in the future.

The Challenge:

Snowflake is our primary data source. Business user access is controlled by both role and warehouse, and we operate in three environments (development, pre-production, and production). At the moment, we end up with multiple DSN configurations per department (for instance, Sales has three connections, Finance has three, and so on).

The Ideal Setup:

We’re aiming to reduce this to only three ODBC DSN setups—one per environment. Our wish is to use the Data Connection Manager (DCM) to dynamically inject the appropriate role and warehouse parameters into the connection at runtime. I’m comfortable managing several DCM configurations if it means we can consolidate our DSN profiles down to just three for Snowflake.

Has anyone implemented a similar solution or have suggestions on the best practices to achieve this? Any insights or recommendations would be greatly appreciated.

I am a bit new to DCM at scale, and i might be barking up the wrong three, dont hesitate to let me know if so.

Thank you!

[apathetichell]

Let's back up a step:

1) Can you confirm your local users are accessing Snowflake via externalbrowser/SAML?

2) How are you accessing Snowflake on Server (ie -> username password or JWT).

3) Do you have 3 distinct service accounts for Server (one for each team) or is the logical divisions for workflow collections/access sufficient?

Note a huge fan of DCM - but I think your architecture is a bit off here. 1) your users should be using externalbrowser/SAML. 2) your Server needs (AFAIK) - a Snowflake service account user. I would recommend JWT vs username/password because ---> stuff. 3) I have no idea (and don't really care) if Alteryx/DCM supports JWT -> it is the objectively correct way to auth a Service Account to Snowflake.

[Neffarious23]

While we are not a big Snowflake shop, our experience with DCM may have some parallels and value.  For our 23.2 environments, (2 sandbox and prod, multi-node, multi-worker) I have resisted creating DSN entries on worker nodes and we have been able to lean on DSN-less DCM connections to several SQL data sources, both Azure and on-prem.  As I understand it, I will need DSN if we begin to leverage the In-DB tools but we went from around a dozen DSN entries on each worker to zero using DSN-less.  The ODBC drivers still need to be installed on each worker but new DCM data sources can be configured without admin help after that (one of my favorite aspects of DSN-less).  The pattern which made DCM work the best was to configure the Gallery to have 1-way sync (from Server to Designer).  DCM data connections are built on the server by either a service account user, team lead, or curator, and shared for Collaboration to the appropriate Designer developers who sync to their Designer.  We also used a naming convention to indicate where the DCM has been created, e.g. CRMData-Dev, CRMData-Prod, as synchronizing to multiple server environments can quickly clutter up your Manage Connections page.  If that gets overwhelming, one can delete the DCM storage files saved locally (screenshot attached) and with the next sync these SQLite files get rebuilt with only single environment's worth of DCM definitions.  Finally, performance has been excellent, particularly when using the bulk output option.  This DCM approach also worked with the Dataverse connector. While not perfect in 23.2, we are looking forward to the DCM enhancements in 24.2.

[jensroy]

@apathetichell Hello and thank you for the reply. Sorry for the late reply, I was away on easter vacation.

1. The users authenitcate with browser, and uses azure application token.

2. How we access depends. In browser we use Azure AD. For standarized PowerBI reports we use username and password. For Alteryx, see point 1.

3. We do have 3 distinct service accounts yes, and roles are also spesific to the enviroments.