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:

  • Review the required tables and columns for the SpotApp.

  • Ensure that your columns match the required column type listed in the schema for your SpotApp.

  • Sync all tables and columns from Snowflake to your cloud data warehouse. You can sync only the required tables and columns, but ThoughtSpot recommends syncing all tables and columns from Snowflake to your CDW. The columns can be Snowflake’s out-of-the-box columns, or any custom columns that you are using instead of the out-of-the-box columns.

    If you are using an ETL/ELT tool or working with another team in your organization to move data, our recommendation is that you sync all columns from the tables listed in the SpotApp.
  • Obtain credentials and SYSADMIN privileges to connect to your Snowflake environment. Your Snowflake environment must contain the data you would like ThoughtSpot to use to create Answers, Liveboards, and Worksheets. Refer to the connection reference for Snowflake for information about required credentials.

  • The connection name for each new SpotApp must be unique.

  • ACCOUNTADMIN access to Snowflake.

  • Access to the SNOWFLAKE database in your Snowflake environment.

  • Access to the following Snowflake tables in your Snowflake environment. 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. Note that you have two options to set up the SpotApp; only choose one of the options.

Run SQL commands

You can set up the SpotApp in one of two ways:

  • Option 1: Copy the data from the SNOWFLAKE database to a different database using Snowflake tasks. This option has faster performance and a customizable cost. Refer to Option 1 SQL commands.

  • Option 2: Query on the system database directly. This option has slower performance and may be more expensive. Refer to Option 2 SQL commands.

Option 1

The following SQL commands grant permission for the role you use in your Snowflake connection to use the Snowflake database. They create a separate database and schema for the data in the Snowflake database, and create the tables with the data. Then, they grant permission for the role you use in your Snowflake connection to use the Snowflake database. Replace <YOUR_ROLE> and <YOUR_WAREHOUSE> with your specific information. The role should be either ACCOUNTADMIN or a custom SpotApps role. You can also modify the schedule. The following commands set the schedule to refresh the table data monthly. Changing the schedule may have performance and cost implications.

ThoughtSpot recommends you create the database and schema with the suggested names, using the first two commands in the SQL script. However, you can also use your own names. If you use different names, you must replace ThoughtSpot’s suggested names with the names you used.

Run these commands as the ACCOUNTADMIN. If you don’t have account admin access, you can create a custom role with the permissions required to execute tasks. See the Snowflake documentation.

Make sure to be consistent in your SQL script. If you use double quotes as object identifiers for one object, you must use double quotes for all objects. If you run all the commands at once, use semicolons to separate the commands.

SQL commands:

Open the dropdown menu to view the SQL commands.
CREATE DATABASE  "SNOWFLAKE_USAGE_TS";
CREATE SCHEMA “SNOWFLAKE_USAGE_TS”."SNOWFLAKE";
CREATE OR REPLACE TASK UPDATE_QUERY_HISTORY
WAREHOUSE = <YOUR_WAREHOUSE>
SCHEDULE = 'USING CRON 0 0 1 * * UTC'
AS CREATE OR REPLACE TABLE "SNOWFLAKE_USAGE_TS"."SNOWFLAKE"."QUERY_HISTORY" AS (SELECT
"QUERY_ID",
"QUERY_TEXT",
"DATABASE_ID",
"DATABASE_NAME",
"SCHEMA_ID",
"SCHEMA_NAME",
"QUERY_TYPE",
"SESSION_ID",
"USER_NAME",
"ROLE_NAME",
"WAREHOUSE_ID",
"WAREHOUSE_NAME",
"WAREHOUSE_SIZE",
"WAREHOUSE_TYPE",
"QUERY_TAG",
"EXECUTION_STATUS",
"ERROR_CODE",
"ERROR_MESSAGE",
"START_TIME",
"ROWS_PRODUCED",
"PARTITIONS_SCANNED",
"PARTITIONS_TOTAL",
"PERCENTAGE_SCANNED_FROM_CACHE",
"BYTES_SPILLED_TO_LOCAL_STORAGE",
"BYTES_SPILLED_TO_REMOTE_STORAGE",
"QUEUED_REPAIR_TIME",
"QUEUED_OVERLOAD_TIME",
"QUEUED_PROVISIONING_TIME",
"TOTAL_ELAPSED_TIME",
"COMPILATION_TIME",
"EXECUTION_TIME" FROM "SNOWFLAKE"."ACCOUNT_USAGE"."QUERY_HISTORY");
CREATE OR REPLACE TASK UPDATE_WAREHOUSE_METERING_HISTORY
WAREHOUSE = <YOUR_WAREHOUSE>
SCHEDULE = 'USING CRON 0 0 1 * * UTC'
AS CREATE OR REPLACE TABLE "SNOWFLAKE_USAGE_TS"."SNOWFLAKE"."WAREHOUSE_METERING_HISTORY" AS (SELECT
"WAREHOUSE_ID",
"WAREHOUSE_NAME",
"START_TIME",
"CREDITS_USED",
"CREDITS_USED_COMPUTE",
"CREDITS_USED_CLOUD_SERVICES",
"END_TIME" FROM "SNOWFLAKE"."ACCOUNT_USAGE"."WAREHOUSE_METERING_HISTORY");
CREATE OR REPLACE TASK UPDATE_STAGES
WAREHOUSE = <YOUR_WAREHOUSE>
SCHEDULE = 'USING CRON 0 0 1 * * UTC'
AS CREATE OR REPLACE TABLE "SNOWFLAKE_USAGE_TS"."SNOWFLAKE"."STAGES" AS (SELECT
"STAGE_ID",
"STAGE_NAME",
"STAGE_SCHEMA_ID",
"STAGE_SCHEMA",
"STAGE_CATALOG_ID",
"STAGE_CATALOG",
"STAGE_TYPE",
"STAGE_OWNER",
"DELETED" FROM "SNOWFLAKE"."ACCOUNT_USAGE"."STAGES");
CREATE OR REPLACE TASK UPDATE_STORAGE_USAGE
WAREHOUSE = <YOUR_WAREHOUSE>
SCHEDULE = 'USING CRON 0 0 1 * * UTC'
AS CREATE OR REPLACE TABLE "SNOWFLAKE_USAGE_TS"."SNOWFLAKE"."STORAGE_USAGE" AS (SELECT
"USAGE_DATE",
"FAILSAFE_BYTES",
"STAGE_BYTES",
"STORAGE_BYTES" FROM "SNOWFLAKE"."ACCOUNT_USAGE"."STORAGE_USAGE");
CREATE OR REPLACE TASK UPDATE_DATABASE_STORAGE_USAGE_HISTORY
WAREHOUSE = <YOUR_WAREHOUSE>
SCHEDULE = 'USING CRON 0 0 1 * * UTC'
AS CREATE OR REPLACE TABLE "SNOWFLAKE_USAGE_TS"."SNOWFLAKE"."DATABASE_STORAGE_USAGE_HISTORY" AS (SELECT
"USAGE_DATE",
"DATABASE_ID",
"DATABASE_NAME",
"DELETED",
"AVERAGE_DATABASE_BYTES",
"AVERAGE_FAILSAFE_BYTES" FROM "SNOWFLAKE"."ACCOUNT_USAGE"."DATABASE_STORAGE_USAGE_HISTORY");
alter task UPDATE_DATABASE_STORAGE_USAGE_HISTORY RESUME;
alter task UPDATE_STORAGE_USAGE RESUME;
alter task UPDATE_STAGES RESUME;
alter task UPDATE_WAREHOUSE_METERING_HISTORY RESUME;
alter task UPDATE_QUERY_HISTORY RESUME;
execute task UPDATE_DATABASE_STORAGE_USAGE_HISTORY;
execute task UPDATE_STORAGE_USAGE;
execute task UPDATE_STAGES;
execute task UPDATE_WAREHOUSE_METERING_HISTORY;
execute task UPDATE_QUERY_HISTORY;
GRANT USAGE ON DATABASE "SNOWFLAKE_USAGE_TS" TO ROLE <YOUR_ROLE>;
GRANT USAGE ON SCHEMA "SNOWFLAKE_USAGE_TS"."SNOWFLAKE" TO ROLE <YOUR_ROLE>;
GRANT SELECT ON ALL TABLES IN SCHEMA "SNOWFLAKE_USAGE_TS"."SNOWFLAKE" TO ROLE <YOUR_ROLE>;;

Option 2

The following SQL commands grant permission for the role you use in your Snowflake connection to use the Snowflake database. They prepare you to query on the system database directly. Replace YOUR_ROLE with your specific information, either the ACCOUNTADMIN or a custom SpotApps role.

Run these commands as the ACCOUNTADMIN. If you don’t have account admin access, you can create a custom role with the permissions required to execute tasks. See the Snowflake documentation.

Make sure to be consistent in your SQL script. If you use double quotes as object identifiers for one object, you must use double quotes for all objects. If you run all the commands at once, use semicolons to separate the commands.

SQL commands:

Open the dropdown to view the SQL commands.
GRANT USAGE ON DATABASE "SNOWFLAKE" TO ROLE <YOUR_ROLE>;
GRANT USAGE ON SCHEMA "SNOWFLAKE"."ACCOUNT_USAGE" TO ROLE <YOUR_ROLE>;
GRANT SELECT ON "DATABASE_STORAGE_USAGE_HISTORY" IN SCHEMA "SNOWFLAKE"."ACCOUNT_USAGE" TO ROLE <YOUR_ROLE>;
GRANT SELECT ON "QUERY_HISTORY" IN SCHEMA "SNOWFLAKE"."ACCOUNT_USAGE" TO ROLE <YOUR_ROLE>;
GRANT SELECT ON "STAGES" IN SCHEMA "SNOWFLAKE"."ACCOUNT_USAGE" TO ROLE <YOUR_ROLE>;
GRANT SELECT ON "STORAGE_USAGE" IN SCHEMA "SNOWFLAKE"."ACCOUNT_USAGE" TO ROLE <YOUR_ROLE>;
GRANT SELECT ON "WAREHOUSE_METERING_HISTORY" IN SCHEMA "SNOWFLAKE"."ACCOUNT_USAGE" TO ROLE <YOUR_ROLE>;

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