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:

Snowflake SpotApp Liveboard

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:

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