Snowflake Performance and Consumption SpotApp
SpotApps are ThoughtSpot’s out-of-the-box solution templates built for specific use cases and data sources. They are built on ThoughtSpot Modeling Language (TML) Blocks, which are pre-built pieces of code that are easy to download and implement directly from the product.
The Snowflake Performance and Consumption SpotApp mimics the Snowflake data model. When you deploy it, ThoughtSpot creates several worksheets, answers, and Liveboards, based on your Snowflake data in your cloud data warehouse.
This is a sample Liveboard, created after you deploy the Snowflake Performance and Consumption SpotApp:

Use the Snowflake Performance and Consumption SpotApp to manage Snowflake costs and investigate query performance. Track how and where your users consume credits, and triage and resolve any performance bottlenecks.
Prerequisites
Before you can deploy the Snowflake Performance and Consumption SpotApp, you must complete the following prerequisites:
-
Obtain credentials and SYSADMIN privileges to connect to your cloud data warehouse. The cloud data warehouse must contain the data you would like ThoughtSpot to use to create answers, Liveboards, and worksheets. Refer to the connection reference for your cloud data warehouse for information about required credentials:
-
Administrator access to Snowflake
-
Access to the following Snowflake tables in your cloud data warehouse. Refer to Snowflake Performance and Consumption SpotApp schema for more details.
-
DATABASE_STORAGE_USAGE_HISTORY
-
QUERY_HISTORY
-
STAGES
-
STORAGE_USAGE
-
WAREHOUSE_METERING_HISTORY
-
-
Run the required SQL commands in your cloud data warehouse. Refer to Run SQL commands.
Run SQL commands
The following SQL commands help standardize data types and column names. Replace the database name and schema name with your specific information.
The following SQL example is for the Snowflake cloud data warehouse. You may need to modify the code for the SQL requirements of your specific cloud data warehouse. |
SQL commands:
Click on the dropdown to view the SQL commands.
create or replace view <database_name>.<schema_name>.DATABASE_STORAGE_USAGE_HISTORY as
select * from SNOWFLAKE.ACCOUNT_USAGE.DATABASE_STORAGE_USAGE_HISTORY;
create or replace view <database_name>.<schema_name>.QUERY_HISTORY as
select * from SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY;
create or replace view <database_name>.<schema_name>.STAGES as
select * from SNOWFLAKE.ACCOUNT_USAGE.STAGES;
create or replace view <database_name>.<schema_name>.STORAGE_USAGE as
select * from SNOWFLAKE.ACCOUNT_USAGE.STORAGE_USAGE;
create or replace view <database_name>.<schema_name>.WAREHOUSE_METERING_HISTORY as
select * from SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY;
Deploy the Snowflake Performance and Consumption SpotApp
After you complete the prerequisites, you are ready to deploy the Snowflake Performance and Consumption SpotApp and begin leveraging its pre-built content.
To deploy the Snowflake Performance and Consumption SpotApp, refer to Deploying SpotApps.
Snowflake Performance and Consumption SpotApp schema
The following table describes the schema for the Snowflake Performance and Consumption SpotApp.
Table | Column name | Column type | Required column |
---|---|---|---|
QUERY_HISTORY |
QUERY_ID |
VARCHAR |
Y |
QUERY_HISTORY |
QUERY_TEXT |
VARCHAR |
Y |
QUERY_HISTORY |
DATABASE_ID |
NUMBER |
Y |
QUERY_HISTORY |
DATABASE_NAME |
VARCHAR |
Y |
QUERY_HISTORY |
SCHEMA_ID |
NUMBER |
N |
QUERY_HISTORY |
SCHEMA_NAME |
VARCHAR |
Y |
QUERY_HISTORY |
QUERY_TYPE |
VARCHAR |
Y |
QUERY_HISTORY |
SESSION_ID |
NUMBER |
N |
QUERY_HISTORY |
USER_NAME |
VARCHAR |
Y |
QUERY_HISTORY |
ROLE_NAME |
VARCHAR |
Y |
QUERY_HISTORY |
WAREHOUSE_ID |
NUMBER |
Y |
QUERY_HISTORY |
WAREHOUSE_NAME |
VARCHAR |
Y |
QUERY_HISTORY |
WAREHOUSE_SIZE |
VARCHAR |
Y |
QUERY_HISTORY |
WAREHOUSE_TYPE |
VARCHAR |
N |
QUERY_HISTORY |
QUERY_TAG |
VARCHAR |
N |
QUERY_HISTORY |
EXECUTION_STATUS |
VARCHAR |
N |
QUERY_HISTORY |
ERROR_CODE |
VARCHAR |
Y |
QUERY_HISTORY |
ERROR_MESSAGE |
VARCHAR |
Y |
QUERY_HISTORY |
START_TIME |
TIMESTAMP |
Y |
QUERY_HISTORY |
ROWS_PRODUCED |
NUMBER |
N |
QUERY_HISTORY |
PARTITIONS_SCANNED |
NUMBER |
N |
QUERY_HISTORY |
PARTITIONS_TOTAL |
NUMBER |
N |
QUERY_HISTORY |
PERCENTAGE_SCANNED_FROM_CACHE |
FLOAT |
N |
QUERY_HISTORY |
BYTES_SPILLED_TO_LOCAL_STORAGE |
NUMBER |
N |
QUERY_HISTORY |
BYTES_SPILLED_TO_REMOTE_STORAGE |
NUMBER |
N |
QUERY_HISTORY |
QUEUED_REPAIR_TIME |
NUMBER |
Y |
QUERY_HISTORY |
QUEUED_OVERLOAD_TIME |
NUMBER |
Y |
QUERY_HISTORY |
QUEUED_PROVISIONING_TIME |
NUMBER |
Y |
QUERY_HISTORY |
TOTAL_ELAPSED_TIME |
NUMBER |
Y |
QUERY_HISTORY |
COMPILATION_TIME |
NUMBER |
N |
QUERY_HISTORY |
EXECUTION_TIME |
NUMBER |
Y |
WAREHOUSE_METERING_HISTORY |
WAREHOUSE_ID |
NUMBER |
Y |
WAREHOUSE_METERING_HISTORY |
WAREHOUSE_NAME |
VARCHAR |
Y |
WAREHOUSE_METERING_HISTORY |
START_TIME |
TIMESTAMP |
Y |
WAREHOUSE_METERING_HISTORY |
CREDITS_USED |
NUMBER |
Y |
WAREHOUSE_METERING_HISTORY |
CREDITS_USED_COMPUTE |
NUMBER |
Y |
WAREHOUSE_METERING_HISTORY |
CREDITS_USED_CLOUD_SERVICES |
NUMBER |
Y |
WAREHOUSE_METERING_HISTORY |
END_TIME |
TIMESTAMP |
N |
STAGES |
STAGE_ID |
NUMBER |
N |
STAGES |
STAGE_NAME |
VARCHAR |
Y |
STAGES |
STAGE_SCHEMA_ID |
NUMBER |
N |
STAGES |
STAGE_SCHEMA |
VARCHAR |
Y |
STAGES |
STAGE_CATALOG_ID |
NUMBER |
N |
STAGES |
STAGE_CATALOG |
VARCHAR |
Y |
STAGES |
STAGE_TYPE |
VARCHAR |
Y |
STAGES |
STAGE_OWNER |
VARCHAR |
Y |
STAGES |
DELETED |
TIMESTAMP |
Y |
STORAGE_USAGE |
USAGE_DATE |
DATE |
Y |
STORAGE_USAGE |
FAILSAFE_BYTES |
NUMBER |
Y |
STORAGE_USAGE |
STAGE_BYTES |
NUMBER |
Y |
STORAGE_USAGE |
STORAGE_BYTES |
NUMBER |
Y |
DATABASE_STORAGE_USAGE_HISTORY |
USAGE_DATE |
DATE |
Y |
DATABASE_STORAGE_USAGE_HISTORY |
DATABASE_ID |
NUMBER |
Y |
DATABASE_STORAGE_USAGE_HISTORY |
DATABASE_NAME |
VARCHAR |
Y |
DATABASE_STORAGE_USAGE_HISTORY |
DELETED |
TIMESTAMP |
Y |
DATABASE_STORAGE_USAGE_HISTORY |
AVERAGE_DATABASE_BYTES |
FLOAT |
Y |
DATABASE_STORAGE_USAGE_HISTORY |
AVERAGE_FAILSAFE_BYTES |
FLOAT |
Y |