Snowflake Visual Query Builder misbehaving

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!

Input

Database Connection

Accepted answers

CatheyH

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

All comments

CatheyH

Driver has been updated. This is not the issue.

Luke_C

Hi @CatheyH

Seems like this is a known issue - DE 17676. The release notes for Alteryx 2021.3 seem to indicate it might be fixed, but I don't use snowflake so can't confirm.

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Make-Snowflake-s-Views-discoverable-in-Visual-Query-Builder/td-p/518590

apathetichell

@Luke_C- I think that was specific to Views in VQB. @CatheyH is this views/tables/both? It does seem weird. Do you use username/password or SSO for snowflake? I just checked on a username/password/desktop most recent DSII driver/set up for tables and didn't have that issue.

I can try later on a DSII SSO set up. I don't use VQB all that often so TBH I've never really noticed. I do find it slow. I most often use if I'm' copying/pasting larger queries from Looker or equivalent and I want to fix any copy/paste errors. Sometimes toggling back and forth to VQB seems to help Alteryx sort out what the Query is asking.

CatheyH

@Luke_C @apathetichell . It is in the VQB only, though a strange bug that shows up suddenly and only on the desktop. I wonder if something has been changed in our Snowflake setup that has triggered it. (Yes we are on SSO).

Thanks for the pointers - I will go away and see what we can find!

apathetichell

Testing VQB on SSO View as we speak - looks fine.

Just to confirm - you are choosing a table first - and then going into VQB - right?

CatheyH

If I go into VQB (no previous selections) I should be able to see our DBs and tables on the right hand side to make a selection. (I am aware of ongoing issue with views, this is affecting all DBs, tables and views). The VQB is completely blank as shown above.

If I highlight a table in "Tables" it will come through to the VQB, but I still can't see the usual structured view on the right and I can't select more than one table this way.

I CAN still select from the Tables menu, or enter SQL directly.

We are now having an issue in Snowflake where we can't access worksheets, so I've got my fingers crossed that resolving that one will resolve this!

jrpaul

I just noticed this exact same issue that Cathey mentioned using Snowflake and Designer. I'm sure it all worked fine within the last week or two. Wondering if there was an update to something that is causing this?

jrpaul

Update -- we discovered if we set up the ODBC login to use a Service Account (vs my user account), then everything seems to work correctly. I see the databases/tables in the right-side pane. Is there some master index VQB accesses that might have different permissions for users vs service accounts? Not sure I can log into my user account with a password since we use MFA / external browser. If not, I could make a service account to mirror my permissions but that is just a workaround. Hopefully someone on Alteryx support +Snowflake can provide some insight on what is causing this behavior in the last month.

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

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.

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.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703