Quicksight Data sets - Unable to see snowflake schema and tables

Question

Successfully created a connection to snowflake from aws quicksight to fetch the data sets.When I connect to "SNOWFLAKE_SAMPLE_DATA" database , I am able to get the Schema dropdown list (like TPC_SF*) and table selection subsequently. However when I connect to my newly created snowflake database DEMO_DB (that contains 1 schema and new tables in it); I am not getting the schema dropdown in quicksight. It just says No tables found. Is there any configuration , I need to do to get the schema dropdown in quicksight;so that I can select tables for analysis. Provided all grant permissions as similar to the SNOWFLAKE_SAMPLE_DATA and tried many options. Couldn't get the schema & table list.Please advice.

Thanks, Sree

Answer 1

Sree,

for me, I finally got it to work by providing my parameters in all CAPS. For instance, if my parameters were:

database: foo_db
warehouse: foo_wh

I changed them to:

database: FOO_DB
warehouse: FOO_WH

This seemed to do the trick for me. it was endlessly frustrating that this is not documented in neither the QuickSight nor Snowflake documentation, but I digress.

Answer 2

You imply that you have granted USAGE access to the new SCHEMA and SELECT access to the tables in that SCHEMA. So you granted access to a ROLE that you have in your account.

Now, does your login USER have access to that ROLE?

It is not sufficient to configure a DEFAULT ROLE as this is just a USER text property, not a GRANT.
You also need to grant that ROLE to your USER explicitly:

GRANT ROLE NEW_DB_ACCESS TO USER NEW_USER.

Also, as Craig Robinson points out, there is the issue of keyword casing. SQL silently converts any unquoted identifier to upper case, but tools like Quicksight normally don't (and shouldn't) do that.

So if you enter eg the role name as new_db_access your tool might use "new_db_access" when it issues SQL commands, which is different from new_db_access (without the quotes), the latter gets converted to upper case.

Answer 3

I tried to connect Snowflake data to QuickSight and every time it showed no tables or schemas. Tried all the SQL I could find. And then I realised it's case sensitive!

You need to use capitals when configuring the connection with the database, for the following fields: Database name, Warehouse, Username.