– – Warehouse with credit used till date
SELECT WAREHOUSE_NAME, sum(CREDITS_USED) AS TOTAL_CREDIT_USED
FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY
GROUP BY 1
ORDER BY 2 DESC;
– – Warehouse load
SELECT WAREHOUSE_NAME, sum(AVG_RUNNING+AVG_QUEUED_LOAD) Avg_warehouse_load
FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_LOAD_HISTORY
GROUP BY 1
ORDER BY 2 DESC;
– – Warehouse Usage Month over month
SELECT WAREHOUSE_NAME, to_char(START_TIME, ‘MON-YYYY’) AS “MONTH”, sum(CREDITS_USED) AS TOTAL_CREDIT_USED
FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY
GROUP BY 1, 2
ORDER BY 1,3 DESC;
– – 2.4 Database Objects
SELECT
TABLE_CATALOG AS databasename,
CASE
WHEN IS_TRANSIENT = ‘YES’ THEN ‘TRANSIENT’
ELSE TABLE_TYPE
END tablekind,
COUNT(TABLE_NAME)
FROM
SNOWFLAKE.ACCOUNT_USAGE.”TABLES”
GROUP BY 1,2
ORDER BY 1;
– – 2.5 Database Volume
select TABLE_CATALOG AS databasename, cast (sum(ACTIVE_BYTES)/1024/1024/1024 as decimal(18,2))
from SNOWFLAKE.ACCOUNT_USAGE.TABLE_STORAGE_METRICS
GROUP BY 1
HAVING cast (sum(ACTIVE_BYTES)/1024/1024/1024 as decimal(18,2)) > 10;
– – 2.6 High Data Volume Tables, last DML/COPY INTO tables
SELECT t1.TABLE_CATALOG AS databasename,
t1.TABLE_NAME AS tablename,
t1.row_count AS num_rows,
cast (ACTIVE_BYTES/1024/1024/1024 as decimal(18,2)) AS currentsize_gb,
NULL AS ConstraintType,
NULL AS ConstraintText,
TO_CHAR(TO_TIMESTAMP(GET(GREATEST(ARRAY_CONSTRUCT(T1.LAST_ALTERED),
ARRAY_CONSTRUCT(L1.LAST_LOAD_TIME)),0)), ‘YYYY-MON-DD’) AS LAST_DML_COPY
FROM snowflake.account_usage.”TABLES” AS t1
JOIN SNOWFLAKE.ACCOUNT_USAGE.TABLE_STORAGE_METRICS AS t2
ON (t1.TABLE_ID = t2.ID)
LEFT JOIN SNOWFLAKE.ACCOUNT_USAGE.LOAD_HISTORY L1
ON (T1.TABLE_ID = L1.TABLE_ID)
WHERE table_type = ‘BASE TABLE’
AND T1.TABLE_CATALOG IN (‘NEWTPCDS’, ‘TEST_DB’) — Provide DATABASE names
ORDER BY 3 DESC ;
– – 2.7 Databases and Users
SELECT DATABASE_NAME, count(DISTINCT USER_NAME)
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
GROUP BY DATABASE_NAME;
– – 2.8 Warehouse details
SELECT
WAREHOUSE_NAME, to_char(“TIMESTAMP”, ‘MON-YYYY’) AS “MONTH”,
SUM(CASE WHEN EVENT_NAME = ‘SPINUP_CLUSTER’ THEN 1 ELSE 0 END ) AS NO_TIMES_SPINUP,
SUM(CASE WHEN EVENT_NAME = ‘RESIZE_CLUSTER’ THEN 1 ELSE 0 END ) AS NO_TIMES_RESIZED,
SUM(CASE WHEN EVENT_NAME = ‘RESUME_CLUSTER’ THEN 1 ELSE 0 END ) AS NO_TIMES_RESUMED
FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_EVENTS_HISTORY
WHERE EVENT_STATE LIKE ‘%COMPLETED’
GROUP BY 1,2
ORDER BY NO_TIMES_RESUMED DESC
;
Leaplogic