Tableau & Snowflake: Missing Warehouse when Opening Tableau Workbook

Posted by

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

Error Message
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.

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

One comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s