Snowflake Visual Query Builder misbehaving

[CatheyH]

Hi,

We have 3 installations of Alteryx on: desktop, Windows Virtual Desktop and Server. All 2020.4

When using the Input Data tool we are running into the issue of the Visual Query Builder being completely blank when connecting to Snowflake. We can run queries against Snowflake with no issues using SQL editor, but it is very slow opening if we select VQB.

This is happening on our Desktop install only.

We do not have this issue at all on WVD or Server.

The Snowflake user in all instances is the same.

Snowflake OBDC drivers are different in each case, but the oldest of the drivers (The one on WVD) is working fine. Are there any issues with Snowflake DSIIDriver 2.22.03.00 specifically?

Thanks!

[CatheyH]

@jrpaul Good spot. There is definitely a difference with account type. Because I'm building end-user apps, most of my ODBC connections are with the service account. On laptop or virtual workspace: issues described as above

If I use the service account on the server, identical ODBC set-up, no issues at all.

If I use my own account we don't even get the table list populated - it is all blank.

We were having issues with Snowflake itself because we had changed some company-wide security standards and some Snowflake-related URLs needed whitelisting, this issue showed up at around the same time.

One of our network guys is looking into it and is hopeful of a fix based on an error message that we are now getting if we try to run queries with SQL only. Will update if we get a solution.

[jrpaul]

Thanks, Cathey, please provide details if your IT team figures it out.  

One of my colleagues claimed to have this working for personal accounts (he had no ROLE / WAREHOUSE set in the ODBC config, so it used his Snowflake account defaults), but as we tinkered with his settings, we ended up making it NOT work, and of course, we didn't write down his exact configuration at the beginning.  Trying to reproduce.   The only differences though were the Role and Warehouse, and I think it happened when we changed warehouse.  Not sure what permissions on that would impact seeing all databases, but maybe that gives your team some info to help.

[CatheyH · Accepted solution]

Problem solved. Not sure where the different roles come into this but I can confirm that it is now working. I asked our tech hero to explain what he did. (We have recently implemented Netskope):

"Sometimes the receiving end is expecting a set Cert but is seeing the Netskope cert and does not trust it. As NS re-encrypts with its own cert after Decryption and Inspection.

I created an SSL Inspection bypass in the Secure Web Gateway that decrypts HTTPS packets, inspects them for malware, Loss of PI data, GDPR, Data Loss Prevention etc and then when finished re-encrypts with the Netskope certificate and sends it on."

[jrpaul]

Just a quick follow-up...my issue with VQB appears to have a different cause.    Some roles work fine, one in particular did not.  After a few calls to Snowflake and Alteryx support, did a detailed ODBC trace and it appears the underlying SQL commands (from Alteryx Designer) are getting an error because one of our databases has over 10K tables (too much to display).   IT told me some old instances of Epic (EMR) data were archived to that database a month ago, so I believe that was the trigger.   For now, I removed that Role from mine and granted access to just the schemas/tables I need.   Waiting to see if Alteryx can develop a solution.   The error message says they could use a "limit" clause, but then won't see everything; short-term though, that would solve it.  

[rkerruso]

This issue still exists within version 2022.1, does not appear to be fixed as Alteryx stated in 2021.4. If tables are visible to account visual query builder will display as expected, if only views are visible visual query builder does not recognize the database exists.