The most frustrating issue when working with Tableau and Snowflake together is opening a Tableau workbook or sending a Tableau workbook to a colleague and it results in a missing warehouse error “SQL compilation error: Object does not exist or operation cannot be performed.”.
Bad Connection: Tableau could not connect to the data source.
Error Code: 37CE01A3
SQL compilation error: Object does not exist or operation cannot be performed.
This error can further be confirmed in the Snowflake UI. Login to your Snowflake account and go to the History tab. Find the recent error query with the status red X and click on the Query ID to see the specific error message.
Why Does This Happen
The Warehouse object in Snowflake is a transient compute cluster due to the advanced architecture completely separating storage and compute. The rest of the databases Tableau supports combine storage with compute to a static physical warehouse. Because the Snowflake warehouse is not a physical container for data, only compute power, the warehouse can truly be abstracted. A Snowflake warehouse can be turned off, on, resumed, created and resized in milliseconds. More specifically the connection string in the Tableau workbook code (XML) has the warehouse name as an attribute in the static connection element instead of in the transient editable connection element.
A Snowflake DBA or SysAdmin alters a warehouse in one of several different ways. Removing usage rights for a role or user. Changing the owner of a warehouse. Altering the warehouse name or even dropping the warehouse.
Example of Creating and Solving the Problem
--Create a Warehouse with a Silly Name CREATE OR REPLACE WAREHOUSE CURT_LOVES_CUBES_WAREHOUSE WITH WAREHOUSE_SIZE = 'LARGE' WAREHOUSE_TYPE = 'STANDARD' AUTO_SUSPEND = 60 AUTO_RESUME = TRUE MIN_CLUSTER_COUNT = 1 MAX_CLUSTER_COUNT = 1 SCALING_POLICY = 'STANDARD';
A Snowflake warehouse called “CURT_LOVES_CUBES_WAREHOUSE” was created by the account admin in Snowflake. Tableau is then opened and a new workbook is connected to the dubious warehouse.
The Snowflake account admin now drops the warehouse because it did not match the company naming policy of no Curt’s allowed.
--Drop a Warehouse with a Silly Name DROP WAREHOUSE CURT_LOVES_CUBES_WAREHOUSE;
How to Fix It
The best fix for the DBA types would be to recreate the missing warehouse in Snowflake, restore the missing permissions, or get the warehouse back to working order. However, you might not have the permission to be able to do this.
Initial SQL Fix
Alter the Initial SQL on the Snowflake connection to use a known working warehouse.
USE WAREHOUSE KNOW_TO_WORK_WH;
User Default Warehouse Fix
If you are a user with high enough rights to alter a user (admin, security admin, sysadmin) you can alter the user to have a default warehouse. This would be a decent method at an enterprise to establish a known, working warehouse that all users have usage rights to, something like ‘DEFAULT_TABLEAU_WH’. Then you can monitor that warehouse usage for Tableau Workbooks that force the users to inherit the default tableau warehouse if they cannot use the one with Tableau Workbook.
ALTER USER <NAME> SET DEFAULT_WAREHOUSE = '<WAREHOUSE_NAME>';
Warning Hack the TWB Method
The method that always works, hack the workbook XML. WARNING! Standard not supported and make a backup first. Open the TWB file with the text editor of your choice. If you have a TWBX Unpackage and then open the TWB file with the text editor of your choice. Locate the name of the missing warehouse with a find command. It will be a <datasource> with a <named-connection> under the <connection> element the last attribute is warehouse=’MISSING_WH’, change that to a known working warehouse name, save and open in Tableau.
<datasources> <datasource caption='DATABASE.SCHEMA.OBJECT' inline='true' name='federated.1bk94z51ouqn8p1cup1jd1hal2bp' version='18.1'> <connection class='federated'> <named-connections> <named-connection caption='account.snowflakecomputing.com' name='snowflake.comnnection'> <connection authentication='Username Password' class='snowflake' dbname='TABLEAU' odbc-connect-string-extras='' one-time-sql='' schema='TABLEAU' server='account.snowflakecomputing.com' service='accountadmin' username='admin' warehouse='CURT_LOVES_CUBES_WAREHOUSE' /> </named-connection> </named-connections> <relation connection='snowflake.connection' name='VW_SNOWJACK' table='[TABLEAU].[VW_SNOWJACK]' type='table' />
Call to Action
It would sure be nice if you could address the warehouse in the edit connection dialogue after opening the workbook, but alas it is not there so you are forced to use workarounds. Upvote this fix on the Tableau Community: https://community.tableau.com/ideas/9755