Quicksight - "No tables found" for Snowflake data source

Question

I have a database with a schema, table and views in Snowflake, plus a warehouse to run queries. The public role has been granted SELECT and USAGE access on all.

But no schemas or tables are available when I connect Snowflake to AWS QuickSight.

To test the permissions I have checked I can query the views in a Snowflake worksheet using the public role and the warehouse, database and schema. As a further test I’ve also connected Redash to Snowflake and can query the views there.

Still, using the same credentials nothing is available in QuickSight. I have tried with capitalised and lowercase database and warehouse names (Quicksight Data sets - Unable to see snowflake schema and tables).

If I change QuickSight to connect to the SNOWFLAKE_SAMPLE_DATA database I can see a list of schema, but no tables are shown for any schema.

What could I be missing?

Some potential ideas to narrow it down. If you use the custom SQL option, can you get to those tables? If you test with a higher role (like ACCOUNTADMIN), can you see the tables? What is the query being brought back on the Snowflake side (is there a SHOW objects or equivalent for the sample data but not the other tables)? Are you using the latest version of the driver (I believe this is JDBC)? Any JDBC logging enabled to give clues? Amazon Support may also be able to give some ideas. — Suzy Lockwood
Thank you. When I SELECT * FROM MY_VIEW in custom SQL then the data is available in QuickSight — Ollie Glass
Interesting! So the query to SHOW the objects must not be successfully returning results (for whatever reason, be it privileges or other issues). — Suzy Lockwood

nicor88 nicor88 · Accepted Answer · 2020-05-13T14:40:30

I recommend you to create a new ROLE for Quicksight/Redash or this type of application, and grant this to your Quicksight user. Weird that you can see from redash.

Imagine that you have a role called REPORTING, here the minimum privileges that you need:

GRANT USAGE ON WAREHOUSE YOUR_WH TO ROLE REPORTING;

GRANT USAGE ON DATABASE YOUR_DB TO ROLE REPORTING;

GRANT USAGE ON SCHEMA YOUR_DB.PUBLIC TO ROLE REPORTING;

GRANT SELECT ON ALL TABLES IN SCHEMA YOUR_DB.PUBLIC TO ROLE REPORTING;
GRANT SELECT ON FUTURE TABLES IN SCHEMA YOUR_DB.PUBLIC TO ROLE REPORTING;

GRANT SELECT ON ALL VIEWS IN SCHEMA YOUR_DB.PUBLIC TO ROLE REPORTING;
GRANT SELECT ON FUTURE VIEWS IN SCHEMA YOUR_DB.PUBLIC TO ROLE REPORTING;

Also, be sure to run this:

GRANT ROLE REPORTING TO USER QUICKSIGHTS;

We always use CAPITAL case keywords with Snowflake.

Quicksight - "No tables found" for Snowflake data source

1 Answers