-- Create comprehensive warehouse analytics table
CREATE OR REPLACE TABLE WAREHOUSE_ANALYTICS_DASHBOARD AS
WITH warehouse_info AS (
-- Get warehouse metadata (note: warehouse details need to be extracted from query history and warehouse events)
SELECT DISTINCT
wh.WAREHOUSE_ID,
wh.WAREHOUSE_NAME,
wh.SIZE,
wh.WAREHOUSE_TYPE,
wh.CLUSTER_COUNT,
-- Note: SUSPEND_POLICY, MIN_CLUSTER_COUNT, MAX_CLUSTER_COUNT are not in the provided schema
-- These would typically come from SHOW WAREHOUSES command results
NULL as SUSPEND_POLICY,
NULL as MIN_CLUSTER_COUNT,
NULL as MAX_CLUSTER_COUNT
FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_EVENTS_HISTORY wh
WHERE wh.TIMESTAMP >= CURRENT_DATE - 1
),
query_buckets AS (
SELECT
q.WAREHOUSE_ID,
q.WAREHOUSE_NAME,
q.QUERY_ID,
q.TOTAL_ELAPSED_TIME,
q.EXECUTION_STATUS,
q.CREDITS_USED_CLOUD_SERVICES,
q.BYTES_SPILLED_TO_LOCAL_STORAGE,
q.BYTES_SPILLED_TO_REMOTE_STORAGE,
-- Query duration buckets (in milliseconds to seconds)
CASE
WHEN q.TOTAL_ELAPSED_TIME <= 10000 THEN '1-10 seconds'
WHEN q.TOTAL_ELAPSED_TIME <= 20000 THEN '10-20 seconds'
WHEN q.TOTAL_ELAPSED_TIME <= 60000 THEN '20-60 seconds'
WHEN q.TOTAL_ELAPSED_TIME <= 180000 THEN '1-3 minutes'
WHEN q.TOTAL_ELAPSED_TIME <= 300000 THEN '3-5 minutes'
ELSE '5+ minutes'
END as DURATION_BUCKET,
-- Query duration buckets for queued queries (in milliseconds)
CASE
WHEN (q.QUEUED_PROVISIONING_TIME + q.QUEUED_REPAIR_TIME + q.QUEUED_OVERLOAD_TIME) <= 120000 THEN '1-2 minutes'
WHEN (q.QUEUED_PROVISIONING_TIME + q.QUEUED_REPAIR_TIME + q.QUEUED_OVERLOAD_TIME) <= 300000 THEN '2-5 minutes'
WHEN (q.QUEUED_PROVISIONING_TIME + q.QUEUED_REPAIR_TIME + q.QUEUED_OVERLOAD_TIME) <= 600000 THEN '5-10 minutes'
WHEN (q.QUEUED_PROVISIONING_TIME + q.QUEUED_REPAIR_TIME + q.QUEUED_OVERLOAD_TIME) <= 1200000 THEN '10-20 minutes'
ELSE '20+ minutes'
END as QUEUED_BUCKET,
-- Credit utilization buckets
CASE
WHEN q.CREDITS_USED_CLOUD_SERVICES <= 0.2 THEN '0-20 cents'
WHEN q.CREDITS_USED_CLOUD_SERVICES <= 0.4 THEN '20-40 cents'
WHEN q.CREDITS_USED_CLOUD_SERVICES <= 0.6 THEN '40-60 cents'
WHEN q.CREDITS_USED_CLOUD_SERVICES <= 0.8 THEN '60-80 cents'
WHEN q.CREDITS_USED_CLOUD_SERVICES <= 1.0 THEN '80-100 cents'
ELSE '100+ cents'
END as CREDIT_UTILIZATION_BUCKET
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY q
WHERE q.START_TIME >= CURRENT_DATE - 1
AND q.WAREHOUSE_ID IS NOT NULL
),
warehouse_metrics AS (
SELECT
wm.WAREHOUSE_ID,
wm.WAREHOUSE_NAME,
SUM(wm.CREDITS_USED) as TOTAL_CREDITS_USED,
SUM(wm.CREDITS_USED_COMPUTE) as TOTAL_COMPUTE_CREDITS,
SUM(wm.CREDITS_USED_CLOUD_SERVICES) as TOTAL_CLOUD_SERVICES_CREDITS
FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY wm
WHERE wm.START_TIME >= CURRENT_DATE - 1
GROUP BY wm.WAREHOUSE_ID, wm.WAREHOUSE_NAME
)
SELECT
-- Warehouse Information
COALESCE(wi.WAREHOUSE_ID, qb.WAREHOUSE_ID, wm.WAREHOUSE_ID) as WAREHOUSE_ID,
COALESCE(wi.WAREHOUSE_NAME, qb.WAREHOUSE_NAME, wm.WAREHOUSE_NAME) as WAREHOUSE_NAME,
wi.SIZE as WAREHOUSE_SIZE,
wi.WAREHOUSE_TYPE,
wi.CLUSTER_COUNT,
wi.SUSPEND_POLICY,
wi.MIN_CLUSTER_COUNT,
wi.MAX_CLUSTER_COUNT,
-- Query Duration Buckets
COUNT(CASE WHEN qb.DURATION_BUCKET = '1-10 seconds' THEN 1 END) as QUERIES_1_10_SEC,
COUNT(CASE WHEN qb.DURATION_BUCKET = '10-20 seconds' THEN 1 END) as QUERIES_10_20_SEC,
COUNT(CASE WHEN qb.DURATION_BUCKET = '20-60 seconds' THEN 1 END) as QUERIES_20_60_SEC,
COUNT(CASE WHEN qb.DURATION_BUCKET = '1-3 minutes' THEN 1 END) as QUERIES_1_3_MIN,
COUNT(CASE WHEN qb.DURATION_BUCKET = '3-5 minutes' THEN 1 END) as QUERIES_3_5_MIN,
COUNT(CASE WHEN qb.DURATION_BUCKET = '5+ minutes' THEN 1 END) as QUERIES_5_PLUS_MIN,
-- Queued Query Buckets
COUNT(CASE WHEN qb.QUEUED_BUCKET = '1-2 minutes' THEN 1 END) as QUEUED_1_2_MIN,
COUNT(CASE WHEN qb.QUEUED_BUCKET = '2-5 minutes' THEN 1 END) as QUEUED_2_5_MIN,
COUNT(CASE WHEN qb.QUEUED_BUCKET = '5-10 minutes' THEN 1 END) as QUEUED_5_10_MIN,
COUNT(CASE WHEN qb.QUEUED_BUCKET = '10-20 minutes' THEN 1 END) as QUEUED_10_20_MIN,
COUNT(CASE WHEN qb.QUEUED_BUCKET = '20+ minutes' THEN 1 END) as QUEUED_20_PLUS_MIN,
-- Spilled Queries
COUNT(CASE WHEN qb.BYTES_SPILLED_TO_LOCAL_STORAGE > 0 THEN 1 END) as QUERIES_SPILLED_LOCAL,
COUNT(CASE WHEN qb.BYTES_SPILLED_TO_REMOTE_STORAGE > 0 THEN 1 END) as QUERIES_SPILLED_REMOTE,
SUM(qb.BYTES_SPILLED_TO_LOCAL_STORAGE) as TOTAL_BYTES_SPILLED_LOCAL,
SUM(qb.BYTES_SPILLED_TO_REMOTE_STORAGE) as TOTAL_BYTES_SPILLED_REMOTE,
-- Failed Queries
COUNT(CASE WHEN qb.EXECUTION_STATUS = 'FAIL' THEN 1 END) as FAILED_QUERIES,
COUNT(CASE WHEN qb.EXECUTION_STATUS = 'SUCCESS' THEN 1 END) as SUCCESSFUL_QUERIES,
COUNT(CASE WHEN qb.EXECUTION_STATUS = 'RUNNING' THEN 1 END) as RUNNING_QUERIES,
-- Credit Utilization Buckets
COUNT(CASE WHEN qb.CREDIT_UTILIZATION_BUCKET = '0-20 cents' THEN 1 END) as QUERIES_0_20_CENTS,
COUNT(CASE WHEN qb.CREDIT_UTILIZATION_BUCKET = '20-40 cents' THEN 1 END) as QUERIES_20_40_CENTS,
COUNT(CASE WHEN qb.CREDIT_UTILIZATION_BUCKET = '40-60 cents' THEN 1 END) as QUERIES_40_60_CENTS,
COUNT(CASE WHEN qb.CREDIT_UTILIZATION_BUCKET = '60-80 cents' THEN 1 END) as QUERIES_60_80_CENTS,
COUNT(CASE WHEN qb.CREDIT_UTILIZATION_BUCKET = '80-100 cents' THEN 1 END) as QUERIES_80_100_CENTS,
COUNT(CASE WHEN qb.CREDIT_UTILIZATION_BUCKET = '100+ cents' THEN 1 END) as QUERIES_100_PLUS_CENTS,
-- Overall Metrics
COUNT(qb.QUERY_ID) as TOTAL_QUERIES,
COALESCE(wm.TOTAL_CREDITS_USED, 0) as TOTAL_CREDITS_USED,
COALESCE(wm.TOTAL_COMPUTE_CREDITS, 0) as TOTAL_COMPUTE_CREDITS,
COALESCE(wm.TOTAL_CLOUD_SERVICES_CREDITS, 0) as TOTAL_CLOUD_SERVICES_CREDITS,
-- Analysis timestamp
CURRENT_TIMESTAMP as ANALYSIS_TIMESTAMP,
CURRENT_DATE - 1 as ANALYSIS_DATE
FROM warehouse_info wi
FULL OUTER JOIN query_buckets qb
ON wi.WAREHOUSE_ID = qb.WAREHOUSE_ID
FULL OUTER JOIN warehouse_metrics wm
ON COALESCE(wi.WAREHOUSE_ID, qb.WAREHOUSE_ID) = wm.WAREHOUSE_ID
GROUP BY
COALESCE(wi.WAREHOUSE_ID, qb.WAREHOUSE_ID, wm.WAREHOUSE_ID),
COALESCE(wi.WAREHOUSE_NAME, qb.WAREHOUSE_NAME, wm.WAREHOUSE_NAME),
wi.SIZE,
wi.WAREHOUSE_TYPE,
wi.CLUSTER_COUNT,
wi.SUSPEND_POLICY,
wi.MIN_CLUSTER_COUNT,
wi.MAX_CLUSTER_COUNT,
wm.TOTAL_CREDITS_USED,
wm.TOTAL_COMPUTE_CREDITS,
wm.TOTAL_CLOUD_SERVICES_CREDITS
ORDER BY TOTAL_QUERIES DESC;
-- Create a procedure to refresh the table data
CREATE OR REPLACE PROCEDURE REFRESH_WAREHOUSE_ANALYTICS()
RETURNS STRING
LANGUAGE SQL
AS
$$
BEGIN
-- Recreate the table with fresh data
CREATE OR REPLACE TABLE WAREHOUSE_ANALYTICS_DASHBOARD AS
WITH warehouse_info AS (
SELECT DISTINCT
wh.WAREHOUSE_ID,
wh.WAREHOUSE_NAME,
wh.SIZE,
wh.WAREHOUSE_TYPE,
wh.CLUSTER_COUNT,
NULL as SUSPEND_POLICY,
NULL as MIN_CLUSTER_COUNT,
NULL as MAX_CLUSTER_COUNT
FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_EVENTS_HISTORY wh
WHERE wh.TIMESTAMP >= CURRENT_DATE - 1
),
query_buckets AS (
SELECT
q.WAREHOUSE_ID,
q.WAREHOUSE_NAME,
q.QUERY_ID,
q.TOTAL_ELAPSED_TIME,
q.EXECUTION_STATUS,
q.CREDITS_USED_CLOUD_SERVICES,
q.BYTES_SPILLED_TO_LOCAL_STORAGE,
q.BYTES_SPILLED_TO_REMOTE_STORAGE,
CASE
WHEN q.TOTAL_ELAPSED_TIME <= 10000 THEN '1-10 seconds'
WHEN q.TOTAL_ELAPSED_TIME <= 20000 THEN '10-20 seconds'
WHEN q.TOTAL_ELAPSED_TIME <= 60000 THEN '20-60 seconds'
WHEN q.TOTAL_ELAPSED_TIME <= 180000 THEN '1-3 minutes'
WHEN q.TOTAL_ELAPSED_TIME <= 300000 THEN '3-5 minutes'
ELSE '5+ minutes'
END as DURATION_BUCKET,
CASE
WHEN (q.QUEUED_PROVISIONING_TIME + q.QUEUED_REPAIR_TIME + q.QUEUED_OVERLOAD_TIME) <= 120000 THEN '1-2 minutes'
WHEN (q.QUEUED_PROVISIONING_TIME + q.QUEUED_REPAIR_TIME + q.QUEUED_OVERLOAD_TIME) <= 300000 THEN '2-5 minutes'
WHEN (q.QUEUED_PROVISIONING_TIME + q.QUEUED_REPAIR_TIME + q.QUEUED_OVERLOAD_TIME) <= 600000 THEN '5-10 minutes'
WHEN (q.QUEUED_PROVISIONING_TIME + q.QUEUED_REPAIR_TIME + q.QUEUED_OVERLOAD_TIME) <= 1200000 THEN '10-20 minutes'
ELSE '20+ minutes'
END as QUEUED_BUCKET,
CASE
WHEN q.CREDITS_USED_CLOUD_SERVICES <= 0.2 THEN '0-20 cents'
WHEN q.CREDITS_USED_CLOUD_SERVICES <= 0.4 THEN '20-40 cents'
WHEN q.CREDITS_USED_CLOUD_SERVICES <= 0.6 THEN '40-60 cents'
WHEN q.CREDITS_USED_CLOUD_SERVICES <= 0.8 THEN '60-80 cents'
WHEN q.CREDITS_USED_CLOUD_SERVICES <= 1.0 THEN '80-100 cents'
ELSE '100+ cents'
END as CREDIT_UTILIZATION_BUCKET
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY q
WHERE q.START_TIME >= CURRENT_DATE - 1
AND q.WAREHOUSE_ID IS NOT NULL
),
warehouse_metrics AS (
SELECT
wm.WAREHOUSE_ID,
wm.WAREHOUSE_NAME,
SUM(wm.CREDITS_USED) as TOTAL_CREDITS_USED,
SUM(wm.CREDITS_USED_COMPUTE) as TOTAL_COMPUTE_CREDITS,
SUM(wm.CREDITS_USED_CLOUD_SERVICES) as TOTAL_CLOUD_SERVICES_CREDITS
FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY wm
WHERE wm.START_TIME >= CURRENT_DATE - 1
GROUP BY wm.WAREHOUSE_ID, wm.WAREHOUSE_NAME
)
SELECT
COALESCE(wi.WAREHOUSE_ID, qb.WAREHOUSE_ID, wm.WAREHOUSE_ID) as WAREHOUSE_ID,
COALESCE(wi.WAREHOUSE_NAME, qb.WAREHOUSE_NAME, wm.WAREHOUSE_NAME) as WAREHOUSE_NAME,
wi.SIZE as WAREHOUSE_SIZE,
wi.WAREHOUSE_TYPE,
wi.CLUSTER_COUNT,
wi.SUSPEND_POLICY,
wi.MIN_CLUSTER_COUNT,
wi.MAX_CLUSTER_COUNT,
COUNT(CASE WHEN qb.DURATION_BUCKET = '1-10 seconds' THEN 1 END) as QUERIES_1_10_SEC,
COUNT(CASE WHEN qb.DURATION_BUCKET = '10-20 seconds' THEN 1 END) as QUERIES_10_20_SEC,
COUNT(CASE WHEN qb.DURATION_BUCKET = '20-60 seconds' THEN 1 END) as QUERIES_20_60_SEC,
COUNT(CASE WHEN qb.DURATION_BUCKET = '1-3 minutes' THEN 1 END) as QUERIES_1_3_MIN,
COUNT(CASE WHEN qb.DURATION_BUCKET = '3-5 minutes' THEN 1 END) as QUERIES_3_5_MIN,
COUNT(CASE WHEN qb.DURATION_BUCKET = '5+ minutes' THEN 1 END) as QUERIES_5_PLUS_MIN,
COUNT(CASE WHEN qb.QUEUED_BUCKET = '1-2 minutes' THEN 1 END) as QUEUED_1_2_MIN,
COUNT(CASE WHEN qb.QUEUED_BUCKET = '2-5 minutes' THEN 1 END) as QUEUED_2_5_MIN,
COUNT(CASE WHEN qb.QUEUED_BUCKET = '5-10 minutes' THEN 1 END) as QUEUED_5_10_MIN,
COUNT(CASE WHEN qb.QUEUED_BUCKET = '10-20 minutes' THEN 1 END) as QUEUED_10_20_MIN,
COUNT(CASE WHEN qb.QUEUED_BUCKET = '20+ minutes' THEN 1 END) as QUEUED_20_PLUS_MIN,
COUNT(CASE WHEN qb.BYTES_SPILLED_TO_LOCAL_STORAGE > 0 THEN 1 END) as QUERIES_SPILLED_LOCAL,
COUNT(CASE WHEN qb.BYTES_SPILLED_TO_REMOTE_STORAGE > 0 THEN 1 END) as QUERIES_SPILLED_REMOTE,
SUM(qb.BYTES_SPILLED_TO_LOCAL_STORAGE) as TOTAL_BYTES_SPILLED_LOCAL,
SUM(qb.BYTES_SPILLED_TO_REMOTE_STORAGE) as TOTAL_BYTES_SPILLED_REMOTE,
COUNT(CASE WHEN qb.EXECUTION_STATUS = 'FAIL' THEN 1 END) as FAILED_QUERIES,
COUNT(CASE WHEN qb.EXECUTION_STATUS = 'SUCCESS' THEN 1 END) as SUCCESSFUL_QUERIES,
COUNT(CASE WHEN qb.EXECUTION_STATUS = 'RUNNING' THEN 1 END) as RUNNING_QUERIES,
COUNT(CASE WHEN qb.CREDIT_UTILIZATION_BUCKET = '0-20 cents' THEN 1 END) as QUERIES_0_20_CENTS,
COUNT(CASE WHEN qb.CREDIT_UTILIZATION_BUCKET = '20-40 cents' THEN 1 END) as QUERIES_20_40_CENTS,
COUNT(CASE WHEN qb.CREDIT_UTILIZATION_BUCKET = '40-60 cents' THEN 1 END) as QUERIES_40_60_CENTS,
COUNT(CASE WHEN qb.CREDIT_UTILIZATION_BUCKET = '60-80 cents' THEN 1 END) as QUERIES_60_80_CENTS,
COUNT(CASE WHEN qb.CREDIT_UTILIZATION_BUCKET = '80-100 cents' THEN 1 END) as QUERIES_80_100_CENTS,
COUNT(CASE WHEN qb.CREDIT_UTILIZATION_BUCKET = '100+ cents' THEN 1 END) as QUERIES_100_PLUS_CENTS,
COUNT(qb.QUERY_ID) as TOTAL_QUERIES,
COALESCE(wm.TOTAL_CREDITS_USED, 0) as TOTAL_CREDITS_USED,
COALESCE(wm.TOTAL_COMPUTE_CREDITS, 0) as TOTAL_COMPUTE_CREDITS,
COALESCE(wm.TOTAL_CLOUD_SERVICES_CREDITS, 0) as TOTAL_CLOUD_SERVICES_CREDITS,
CURRENT_TIMESTAMP as ANALYSIS_TIMESTAMP,
CURRENT_DATE - 1 as ANALYSIS_DATE
FROM warehouse_info wi
FULL OUTER JOIN query_buckets qb
ON wi.WAREHOUSE_ID = qb.WAREHOUSE_ID
FULL OUTER JOIN warehouse_metrics wm
ON COALESCE(wi.WAREHOUSE_ID, qb.WAREHOUSE_ID) = wm.WAREHOUSE_ID
GROUP BY
COALESCE(wi.WAREHOUSE_ID, qb.WAREHOUSE_ID, wm.WAREHOUSE_ID),
COALESCE(wi.WAREHOUSE_NAME, qb.WAREHOUSE_NAME, wm.WAREHOUSE_NAME),
wi.SIZE,
wi.WAREHOUSE_TYPE,
wi.CLUSTER_COUNT,
wi.SUSPEND_POLICY,
wi.MIN_CLUSTER_COUNT,
wi.MAX_CLUSTER_COUNT,
wm.TOTAL_CREDITS_USED,
wm.TOTAL_COMPUTE_CREDITS,
wm.TOTAL_CLOUD_SERVICES_CREDITS
ORDER BY TOTAL_QUERIES DESC;
RETURN 'WAREHOUSE_ANALYTICS_DASHBOARD table refreshed successfully at ' || CURRENT_TIMESTAMP;
END;
$$;
-- To refresh the data anytime, simply run:
-- CALL REFRESH_WAREHOUSE_ANALYTICS();
-- Query to view the results
SELECT * FROM WAREHOUSE_ANALYTICS_DASHBOARD
ORDER BY TOTAL_QUERIES DESC;
Armaan Khan
Posted on
new queries
For further actions, you may consider blocking this person and/or reporting abuse
Top comments (0)