QUERIES = {
'warehouses': QueryConfig(
name='Warehouse Analytics',
sql="""
WITH warehouse_metrics AS (
SELECT
wm.WAREHOUSE_NAME,
wm.WAREHOUSE_ID,
wh.WAREHOUSE_SIZE,
wh.AUTO_SUSPEND,
wh.AUTO_RESUME,
wh.MIN_CLUSTER_COUNT,
wh.MAX_CLUSTER_COUNT,
wh.SCALING_POLICY,
-- Credit Usage (Last 3 Days)
ROUND(SUM(wm.CREDITS_USED), 2) as TOTAL_CREDITS_USED,
ROUND(SUM(wm.CREDITS_USED_COMPUTE), 2) as CREDITS_USED_COMPUTE,
ROUND(SUM(wm.CREDITS_USED_CLOUD_SERVICES), 2) as CREDITS_USED_CLOUD_SERVICES,
-- Query Performance Buckets
COUNT(CASE WHEN qh.TOTAL_ELAPSED_TIME <= 1000 THEN 1 END) as QUERIES_0_TO_1_SEC,
COUNT(CASE WHEN qh.TOTAL_ELAPSED_TIME > 1000 AND qh.TOTAL_ELAPSED_TIME <= 10000 THEN 1 END) as QUERIES_1_TO_10_SEC,
COUNT(CASE WHEN qh.TOTAL_ELAPSED_TIME > 10000 AND qh.TOTAL_ELAPSED_TIME <= 30000 THEN 1 END) as QUERIES_10_TO_30_SEC,
COUNT(CASE WHEN qh.TOTAL_ELAPSED_TIME > 30000 AND qh.TOTAL_ELAPSED_TIME <= 60000 THEN 1 END) as QUERIES_30_TO_60_SEC,
COUNT(CASE WHEN qh.TOTAL_ELAPSED_TIME > 60000 AND qh.TOTAL_ELAPSED_TIME <= 300000 THEN 1 END) as QUERIES_1_TO_5_MIN,
COUNT(CASE WHEN qh.TOTAL_ELAPSED_TIME > 300000 AND qh.TOTAL_ELAPSED_TIME <= 900000 THEN 1 END) as QUERIES_5_TO_15_MIN,
COUNT(CASE WHEN qh.TOTAL_ELAPSED_TIME > 900000 THEN 1 END) as QUERIES_15_MIN_PLUS,
-- Bad Practice Categories
COUNT(CASE WHEN qh.BYTES_SPILLED_TO_LOCAL_STORAGE > 0 OR qh.BYTES_SPILLED_TO_REMOTE_STORAGE > 0 THEN 1 END) as SPILLED_QUERIES,
COUNT(CASE WHEN qh.EXECUTION_STATUS = 'FAILED' THEN 1 END) as FAILED_QUERIES,
COUNT(CASE WHEN qh.ROWS_PRODUCED = 0 AND qh.EXECUTION_STATUS = 'SUCCESS' THEN 1 END) as ZERO_RESULT_QUERIES,
COUNT(CASE WHEN qh.COMPILATION_TIME > 5000 THEN 1 END) as HIGH_COMPILE_TIME,
COUNT(CASE WHEN qh.TRANSACTION_BLOCKED_TIME > 0 THEN 1 END) as BLOCKED_TRANSACTION_QUERIES,
-- Utilization Metrics
ROUND(AVG(CASE WHEN wl.AVG_RUNNING > 0 THEN (wl.AVG_RUNNING / GREATEST(wh.MAX_CLUSTER_COUNT, 1)) * 100 ELSE 0 END), 2) as AVG_UTILIZATION_PERCENT,
-- Core Metrics
COUNT(qh.QUERY_ID) as TOTAL_QUERIES,
COUNT(DISTINCT qh.DATABASE_NAME) as UNIQUE_DATABASES_ACCESSED,
COUNT(DISTINCT qh.USER_NAME) as UNIQUE_USERS,
COUNT(DISTINCT DATE(qh.START_TIME)) as ACTIVE_DAYS_COUNT,
ROUND(AVG(qh.TOTAL_ELAPSED_TIME), 2) as AVG_EXECUTION_TIME_MS,
ROUND(SUM(qh.BYTES_SCANNED) / (1024*1024*1024), 2) as TOTAL_DATA_SCANNED_GB
FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY wm
LEFT JOIN SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSES wh ON wm.WAREHOUSE_NAME = wh.WAREHOUSE_NAME
LEFT JOIN SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY qh ON wm.WAREHOUSE_NAME = qh.WAREHOUSE_NAME
AND qh.START_TIME >= DATEADD(day, -3, CURRENT_TIMESTAMP())
LEFT JOIN SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_LOAD_HISTORY wl ON wm.WAREHOUSE_NAME = wl.WAREHOUSE_NAME
AND wl.START_TIME >= DATEADD(day, -3, CURRENT_TIMESTAMP())
WHERE wm.START_TIME >= DATEADD(day, -3, CURRENT_TIMESTAMP())
GROUP BY 1,2,3,4,5,6,7,8,9
ORDER BY TOTAL_CREDITS_USED DESC
)
SELECT * FROM warehouse_metrics
""",
description='Comprehensive warehouse analytics with performance metrics and bad practices'
),
'users': QueryConfig(
name='User Analytics',
sql="""
WITH user_metrics AS (
SELECT
u.NAME as USER_NAME,
u.USER_ID,
u.LOGIN_NAME,
u.EMAIL,
u.DEFAULT_WAREHOUSE,
u.DEFAULT_ROLE,
u.DISABLED,
u.LAST_SUCCESS_LOGIN,
u.TYPE as USER_TYPE,
-- Credit Usage (Last 3 Days) - Aggregated from Query Level
ROUND(SUM(COALESCE(qh.CREDITS_USED_CLOUD_SERVICES, 0)), 2) as TOTAL_CREDITS_CONSUMED,
-- Query Performance Buckets
COUNT(CASE WHEN qh.TOTAL_ELAPSED_TIME <= 1000 THEN 1 END) as QUERIES_0_TO_1_SEC,
COUNT(CASE WHEN qh.TOTAL_ELAPSED_TIME > 1000 AND qh.TOTAL_ELAPSED_TIME <= 10000 THEN 1 END) as QUERIES_1_TO_10_SEC,
COUNT(CASE WHEN qh.TOTAL_ELAPSED_TIME > 10000 AND qh.TOTAL_ELAPSED_TIME <= 30000 THEN 1 END) as QUERIES_10_TO_30_SEC,
COUNT(CASE WHEN qh.TOTAL_ELAPSED_TIME > 30000 AND qh.TOTAL_ELAPSED_TIME <= 60000 THEN 1 END) as QUERIES_30_TO_60_SEC,
COUNT(CASE WHEN qh.TOTAL_ELAPSED_TIME > 60000 AND qh.TOTAL_ELAPSED_TIME <= 300000 THEN 1 END) as QUERIES_1_TO_5_MIN,
COUNT(CASE WHEN qh.TOTAL_ELAPSED_TIME > 300000 AND qh.TOTAL_ELAPSED_TIME <= 900000 THEN 1 END) as QUERIES_5_TO_15_MIN,
COUNT(CASE WHEN qh.TOTAL_ELAPSED_TIME > 900000 THEN 1 END) as QUERIES_15_MIN_PLUS,
-- Bad Practice Categories
COUNT(CASE WHEN UPPER(qh.QUERY_TEXT) LIKE '%SELECT *%' THEN 1 END) as SELECT_STAR_QUERIES,
COUNT(CASE WHEN qh.BYTES_SPILLED_TO_LOCAL_STORAGE > 0 OR qh.BYTES_SPILLED_TO_REMOTE_STORAGE > 0 THEN 1 END) as SPILLED_QUERIES,
COUNT(CASE WHEN qh.EXECUTION_STATUS = 'FAILED' THEN 1 END) as FAILED_QUERIES,
COUNT(CASE WHEN qh.ROWS_PRODUCED = 0 AND qh.EXECUTION_STATUS = 'SUCCESS' THEN 1 END) as ZERO_RESULT_QUERIES,
COUNT(CASE WHEN qh.COMPILATION_TIME > 5000 THEN 1 END) as HIGH_COMPILE_TIME,
COUNT(CASE WHEN qh.PARTITIONS_SCANNED = qh.PARTITIONS_TOTAL AND qh.PARTITIONS_TOTAL > 100 THEN 1 END) as UNPARTITIONED_SCANS,
COUNT(CASE WHEN qh.TRANSACTION_BLOCKED_TIME > 0 THEN 1 END) as BLOCKED_TRANSACTION_QUERIES,
-- Core Metrics
COUNT(qh.QUERY_ID) as TOTAL_QUERIES,
COUNT(DISTINCT qh.WAREHOUSE_NAME) as UNIQUE_WAREHOUSES_USED,
COUNT(DISTINCT qh.DATABASE_NAME) as UNIQUE_DATABASES_ACCESSED,
COUNT(DISTINCT DATE(qh.START_TIME)) as ACTIVE_DAYS_COUNT,
ROUND(AVG(qh.TOTAL_ELAPSED_TIME), 2) as AVG_EXECUTION_TIME_MS,
ROUND(SUM(qh.BYTES_SCANNED) / (1024*1024*1024), 2) as TOTAL_DATA_SCANNED_GB,
-- Repeated Queries Detection
COUNT(DISTINCT CASE WHEN qh.QUERY_HASH IS NOT NULL THEN qh.QUERY_HASH END) as UNIQUE_QUERY_PATTERNS
FROM SNOWFLAKE.ACCOUNT_USAGE.USERS u
LEFT JOIN SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY qh ON u.NAME = qh.USER_NAME
AND qh.START_TIME >= DATEADD(day, -3, CURRENT_TIMESTAMP())
WHERE u.DELETED_ON IS NULL
GROUP BY 1,2,3,4,5,6,7,8,9
ORDER BY TOTAL_QUERIES DESC
)
SELECT * FROM user_metrics
""",
description='User analytics with bad practices and performance metrics'
),
'queries': QueryConfig(
name='Query Analytics',
sql="""
SELECT
qh.QUERY_ID,
qh.QUERY_TEXT,
qh.USER_NAME,
qh.ROLE_NAME,
qh.WAREHOUSE_NAME,
qh.WAREHOUSE_SIZE,
qh.DATABASE_NAME,
qh.SCHEMA_NAME,
qh.QUERY_TYPE,
qh.EXECUTION_STATUS,
qh.ERROR_CODE,
qh.ERROR_MESSAGE,
qh.START_TIME,
qh.END_TIME,
qh.TOTAL_ELAPSED_TIME,
qh.COMPILATION_TIME,
qh.EXECUTION_TIME,
qh.QUEUED_PROVISIONING_TIME,
qh.QUEUED_REPAIR_TIME,
qh.QUEUED_OVERLOAD_TIME,
qh.TRANSACTION_BLOCKED_TIME,
-- Performance Metrics
ROUND(qh.BYTES_SCANNED / (1024*1024*1024), 4) as BYTES_SCANNED_GB,
qh.PERCENTAGE_SCANNED_FROM_CACHE,
ROUND(qh.BYTES_WRITTEN / (1024*1024*1024), 4) as BYTES_WRITTEN_GB,
qh.ROWS_PRODUCED,
qh.ROWS_INSERTED,
qh.ROWS_UPDATED,
qh.ROWS_DELETED,
qh.PARTITIONS_SCANNED,
qh.PARTITIONS_TOTAL,
-- Spill Detection
ROUND(qh.BYTES_SPILLED_TO_LOCAL_STORAGE / (1024*1024*1024), 4) as BYTES_SPILLED_LOCAL_GB,
ROUND(qh.BYTES_SPILLED_TO_REMOTE_STORAGE / (1024*1024*1024), 4) as BYTES_SPILLED_REMOTE_GB,
-- Credits
ROUND(COALESCE(qh.CREDITS_USED_CLOUD_SERVICES, 0), 4) as CREDITS_USED_CLOUD_SERVICES,
-- Performance Categories
CASE
WHEN qh.TOTAL_ELAPSED_TIME <= 1000 THEN '0-1 SEC'
WHEN qh.TOTAL_ELAPSED_TIME <= 10000 THEN '1-10 SEC'
WHEN qh.TOTAL_ELAPSED_TIME <= 30000 THEN '10-30 SEC'
WHEN qh.TOTAL_ELAPSED_TIME <= 60000 THEN '30-60 SEC'
WHEN qh.TOTAL_ELAPSED_TIME <= 300000 THEN '1-5 MIN'
WHEN qh.TOTAL_ELAPSED_TIME <= 900000 THEN '5-15 MIN'
ELSE '15+ MIN'
END as PERFORMANCE_BUCKET,
-- Bad Practice Flags
CASE WHEN UPPER(qh.QUERY_TEXT) LIKE '%SELECT *%' THEN 1 ELSE 0 END as IS_SELECT_STAR,
CASE WHEN qh.BYTES_SPILLED_TO_LOCAL_STORAGE > 0 OR qh.BYTES_SPILLED_TO_REMOTE_STORAGE > 0 THEN 1 ELSE 0 END as HAS_SPILL,
CASE WHEN qh.PARTITIONS_SCANNED = qh.PARTITIONS_TOTAL AND qh.PARTITIONS_TOTAL > 100 THEN 1 ELSE 0 END as IS_FULL_TABLE_SCAN,
CASE WHEN qh.COMPILATION_TIME > 5000 THEN 1 ELSE 0 END as HAS_HIGH_COMPILE_TIME,
CASE WHEN qh.ROWS_PRODUCED = 0 AND qh.EXECUTION_STATUS = 'SUCCESS' THEN 1 ELSE 0 END as IS_ZERO_RESULT,
qh.QUERY_HASH,
qh.QUERY_HASH_VERSION
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY qh
WHERE qh.START_TIME >= DATEADD(day, -3, CURRENT_TIMESTAMP())
ORDER BY qh.START_TIME DESC
LIMIT 10000
""",
description='Detailed query analytics with performance categorization'
),
'query_details': QueryConfig(
name='Query Detail Analytics',
sql="""
WITH query_performance AS (
SELECT
qh.QUERY_ID,
qh.QUERY_TEXT,
qh.USER_NAME,
qh.WAREHOUSE_NAME,
qh.DATABASE_NAME,
qh.SCHEMA_NAME,
qh.START_TIME,
qh.END_TIME,
qh.TOTAL_ELAPSED_TIME,
qh.EXECUTION_STATUS,
-- Detailed Performance Breakdown
qh.COMPILATION_TIME,
qh.EXECUTION_TIME,
qh.QUEUED_PROVISIONING_TIME,
qh.QUEUED_REPAIR_TIME,
qh.QUEUED_OVERLOAD_TIME,
qh.TRANSACTION_BLOCKED_TIME,
qh.LIST_EXTERNAL_FILES_TIME,
-- Data Movement
ROUND(qh.BYTES_SCANNED / (1024*1024*1024), 4) as BYTES_SCANNED_GB,
ROUND(qh.BYTES_WRITTEN / (1024*1024*1024), 4) as BYTES_WRITTEN_GB,
ROUND(qh.BYTES_SPILLED_TO_LOCAL_STORAGE / (1024*1024*1024), 4) as BYTES_SPILLED_LOCAL_GB,
ROUND(qh.BYTES_SPILLED_TO_REMOTE_STORAGE / (1024*1024*1024), 4) as BYTES_SPILLED_REMOTE_GB,
ROUND(qh.BYTES_SENT_OVER_THE_NETWORK / (1024*1024*1024), 4) as BYTES_NETWORK_GB,
-- Row Operations
qh.ROWS_PRODUCED,
qh.ROWS_INSERTED,
qh.ROWS_UPDATED,
qh.ROWS_DELETED,
qh.ROWS_UNLOADED,
-- Partition Information
qh.PARTITIONS_SCANNED,
qh.PARTITIONS_TOTAL,
CASE
WHEN qh.PARTITIONS_TOTAL > 0
THEN ROUND((qh.PARTITIONS_SCANNED::FLOAT / qh.PARTITIONS_TOTAL::FLOAT) * 100, 2)
ELSE 0
END as PARTITION_SCAN_PERCENTAGE,
-- Cache Performance
qh.PERCENTAGE_SCANNED_FROM_CACHE,
-- Credits and Cost
ROUND(COALESCE(qh.CREDITS_USED_CLOUD_SERVICES, 0), 6) as CREDITS_USED_CLOUD_SERVICES,
-- Query Categorization
qh.QUERY_TYPE,
qh.QUERY_TAG,
-- Error Information
qh.ERROR_CODE,
qh.ERROR_MESSAGE,
-- Optimization Indicators
CASE
WHEN qh.BYTES_SPILLED_TO_LOCAL_STORAGE > 0 OR qh.BYTES_SPILLED_TO_REMOTE_STORAGE > 0 THEN 'MEMORY_SPILL'
WHEN qh.PARTITIONS_SCANNED = qh.PARTITIONS_TOTAL AND qh.PARTITIONS_TOTAL > 100 THEN 'FULL_TABLE_SCAN'
WHEN qh.COMPILATION_TIME > 5000 THEN 'HIGH_COMPILE_TIME'
WHEN qh.PERCENTAGE_SCANNED_FROM_CACHE < 10 AND qh.BYTES_SCANNED > 1073741824 THEN 'POOR_CACHE_USAGE'
WHEN qh.TRANSACTION_BLOCKED_TIME > 10000 THEN 'TRANSACTION_BLOCKED'
ELSE 'NORMAL'
END as OPTIMIZATION_FLAG,
-- Access Pattern
ah.DIRECT_OBJECTS_ACCESSED,
ah.BASE_OBJECTS_ACCESSED,
ah.OBJECTS_MODIFIED
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY qh
LEFT JOIN SNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORY ah ON qh.QUERY_ID = ah.QUERY_ID
WHERE qh.START_TIME >= DATEADD(day, -3, CURRENT_TIMESTAMP())
)
SELECT * FROM query_performance
ORDER BY START_TIME DESC
LIMIT 5000
""",
description='Deep dive query performance analysis with optimization recommendations'
),
'databases': QueryConfig(
name='Database Analytics',
sql="""
WITH database_metrics AS (
SELECT
qh.DATABASE_NAME,
qh.DATABASE_ID,
-- Query Activity
COUNT(qh.QUERY_ID) as TOTAL_QUERIES,
COUNT(DISTINCT qh.USER_NAME) as UNIQUE_USERS,
COUNT(DISTINCT qh.WAREHOUSE_NAME) as UNIQUE_WAREHOUSES_USED,
COUNT(DISTINCT qh.SCHEMA_NAME) as UNIQUE_SCHEMAS_ACCESSED,
COUNT(DISTINCT DATE(qh.START_TIME)) as ACTIVE_DAYS_COUNT,
-- Performance Metrics
ROUND(AVG(qh.TOTAL_ELAPSED_TIME), 2) as AVG_EXECUTION_TIME_MS,
ROUND(SUM(qh.BYTES_SCANNED) / (1024*1024*1024), 2) as TOTAL_DATA_SCANNED_GB,
ROUND(SUM(COALESCE(qh.CREDITS_USED_CLOUD_SERVICES, 0)), 4) as TOTAL_CREDITS_CONSUMED,
-- Query Type Distribution
COUNT(CASE WHEN qh.QUERY_TYPE = 'SELECT' THEN 1 END) as SELECT_QUERIES,
COUNT(CASE WHEN qh.QUERY_TYPE = 'INSERT' THEN 1 END) as INSERT_QUERIES,
COUNT(CASE WHEN qh.QUERY_TYPE = 'UPDATE' THEN 1 END) as UPDATE_QUERIES,
COUNT(CASE WHEN qh.QUERY_TYPE = 'DELETE' THEN 1 END) as DELETE_QUERIES,
COUNT(CASE WHEN qh.QUERY_TYPE = 'CREATE' THEN 1 END) as CREATE_QUERIES,
COUNT(CASE WHEN qh.QUERY_TYPE = 'DROP' THEN 1 END) as DROP_QUERIES,
-- Performance Buckets
COUNT(CASE WHEN qh.TOTAL_ELAPSED_TIME <= 1000 THEN 1 END) as QUERIES_0_TO_1_SEC,
COUNT(CASE WHEN qh.TOTAL_ELAPSED_TIME > 1000 AND qh.TOTAL_ELAPSED_TIME <= 10000 THEN 1 END) as QUERIES_1_TO_10_SEC,
COUNT(CASE WHEN qh.TOTAL_ELAPSED_TIME > 10000 AND qh.TOTAL_ELAPSED_TIME <= 30000 THEN 1 END) as QUERIES_10_TO_30_SEC,
COUNT(CASE WHEN qh.TOTAL_ELAPSED_TIME > 30000 AND qh.TOTAL_ELAPSED_TIME <= 60000 THEN 1 END) as QUERIES_30_TO_60_SEC,
COUNT(CASE WHEN qh.TOTAL_ELAPSED_TIME > 60000 AND qh.TOTAL_ELAPSED_TIME <= 300000 THEN 1 END) as QUERIES_1_TO_5_MIN,
COUNT(CASE WHEN qh.TOTAL_ELAPSED_TIME > 300000 THEN 1 END) as QUERIES_5_MIN_PLUS,
-- Bad Practices
COUNT(CASE WHEN UPPER(qh.QUERY_TEXT) LIKE '%SELECT *%' THEN 1 END) as SELECT_STAR_QUERIES,
COUNT(CASE WHEN qh.BYTES_SPILLED_TO_LOCAL_STORAGE > 0 OR qh.BYTES_SPILLED_TO_REMOTE_STORAGE > 0 THEN 1 END) as SPILLED_QUERIES,
COUNT(CASE WHEN qh.EXECUTION_STATUS = 'FAILED' THEN 1 END) as FAILED_QUERIES,
COUNT(CASE WHEN qh.ROWS_PRODUCED = 0 AND qh.EXECUTION_STATUS = 'SUCCESS' THEN 1 END) as ZERO_RESULT_QUERIES,
COUNT(CASE WHEN qh.PARTITIONS_SCANNED = qh.PARTITIONS_TOTAL AND qh.PARTITIONS_TOTAL > 100 THEN 1 END) as UNPARTITIONED_SCANS
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY qh
WHERE qh.START_TIME >= DATEADD(day, -3, CURRENT_TIMESTAMP())
AND qh.DATABASE_NAME IS NOT NULL
GROUP BY qh.DATABASE_NAME, qh.DATABASE_ID
),
database_storage AS (
SELECT
ds.DATABASE_NAME,
ds.DATABASE_ID,
ROUND(AVG(ds.AVERAGE_DATABASE_BYTES) / (1024*1024*1024), 2) as AVG_STORAGE_GB,
ROUND(AVG(ds.AVERAGE_FAILSAFE_BYTES) / (1024*1024*1024), 2) as AVG_FAILSAFE_GB
FROM SNOWFLAKE.ACCOUNT_USAGE.DATABASE_STORAGE_USAGE_HISTORY ds
WHERE ds.USAGE_DATE >= DATEADD(day, -3, CURRENT_DATE())
GROUP BY ds.DATABASE_NAME, ds.DATABASE_ID
)
SELECT
dm.*,
COALESCE(ds.AVG_STORAGE_GB, 0) as AVG_STORAGE_GB,
COALESCE(ds.AVG_FAILSAFE_GB, 0) as AVG_FAILSAFE_GB
FROM database_metrics dm
LEFT JOIN database_storage ds ON dm.DATABASE_NAME = ds.DATABASE_NAME AND dm.DATABASE_ID = ds.DATABASE_ID
ORDER BY dm.TOTAL_QUERIES DESC
""",
description='Database-level analytics with storage and query patterns'
),
'tables': QueryConfig(
name='Table Analytics',
sql="""
WITH table_metrics AS (
SELECT
t.TABLE_NAME,
t.TABLE_SCHEMA,
t.TABLE_CATALOG as DATABASE_NAME,
t.TABLE_TYPE,
t.IS_TRANSIENT,
t.CLUSTERING_KEY,
t.ROW_COUNT,
ROUND(t.BYTES / (1024*1024*1024), 4) as SIZE_GB,
t.RETENTION_TIME,
t.AUTO_CLUSTERING_ON,
t.CREATED,
t.LAST_ALTERED,
t.LAST_DDL,
-- Query Activity (from ACCESS_HISTORY)
COUNT(DISTINCT ah.QUERY_ID) as QUERIES_ACCESSING_TABLE,
COUNT(DISTINCT ah.USER_NAME) as UNIQUE_USERS_ACCESSING,
-- Access Patterns
COUNT(CASE WHEN ah.DIRECT_OBJECTS_ACCESSED IS NOT NULL THEN 1 END) as DIRECT_ACCESS_COUNT,
COUNT(CASE WHEN ah.BASE_OBJECTS_ACCESSED IS NOT NULL THEN 1 END) as BASE_ACCESS_COUNT,
COUNT(CASE WHEN ah.OBJECTS_MODIFIED IS NOT NULL THEN 1 END) as MODIFICATION_COUNT,
-- Performance Impact
AVG(qh.TOTAL_ELAPSED_TIME) as AVG_QUERY_TIME_MS,
SUM(qh.BYTES_SCANNED) / (1024*1024*1024) as TOTAL_SCANNED_GB,
AVG(qh.PERCENTAGE_SCANNED_FROM_CACHE) as AVG_CACHE_HIT_RATE
FROM SNOWFLAKE.ACCOUNT_USAGE.TABLES t
LEFT JOIN SNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORY ah ON
CONTAINS(ah.BASE_OBJECTS_ACCESSED, ARRAY_CONSTRUCT(t.TABLE_CATALOG, t.TABLE_SCHEMA, t.TABLE_NAME))
AND ah.QUERY_START_TIME >= DATEADD(day, -3, CURRENT_TIMESTAMP())
LEFT JOIN SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY qh ON ah.QUERY_ID = qh.QUERY_ID
WHERE t.DELETED IS NULL
AND t.TABLE_TYPE IN ('BASE TABLE', 'VIEW')
GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13
)
SELECT
tm.*,
-- Utilization Categories
CASE
WHEN tm.QUERIES_ACCESSING_TABLE = 0 THEN 'UNUSED'
WHEN tm.QUERIES_ACCESSING_TABLE <= 10 THEN 'LOW_USAGE'
WHEN tm.QUERIES_ACCESSING_TABLE <= 100 THEN 'MEDIUM_USAGE'
ELSE 'HIGH_USAGE'
END as USAGE_CATEGORY,
-- Storage Efficiency
CASE
WHEN tm.SIZE_GB = 0 THEN 'EMPTY'
WHEN tm.SIZE_GB < 0.1 THEN 'VERY_SMALL'
WHEN tm.SIZE_GB < 1 THEN 'SMALL'
WHEN tm.SIZE_GB < 10 THEN 'MEDIUM'
WHEN tm.SIZE_GB < 100 THEN 'LARGE'
ELSE 'VERY_LARGE'
END as SIZE_CATEGORY
FROM table_metrics tm
ORDER BY tm.QUERIES_ACCESSING_TABLE DESC, tm.SIZE_GB DESC
LIMIT 1000
""",
description='Table-level analytics with access patterns and storage metrics'
)
}
Armaan Khan
Posted on
idnex4
For further actions, you may consider blocking this person and/or reporting abuse
Top comments (0)