-- =====================================================
-- TABLE 1: USERS_ANALYTICS_SUMMARY
-- Comprehensive user activity and access analytics
-- =====================================================
CREATE OR REPLACE TABLE USERS_ANALYTICS_SUMMARY AS
WITH user_query_stats AS (
SELECT
q.USER_NAME,
COUNT(*) as TOTAL_QUERIES,
COUNT(CASE WHEN q.EXECUTION_STATUS = 'SUCCESS' THEN 1 END) as SUCCESSFUL_QUERIES,
COUNT(CASE WHEN q.EXECUTION_STATUS = 'FAIL' THEN 1 END) as FAILED_QUERIES,
COUNT(CASE WHEN q.EXECUTION_STATUS = 'RUNNING' THEN 1 END) as RUNNING_QUERIES,
SUM(q.TOTAL_ELAPSED_TIME) as TOTAL_EXECUTION_TIME,
AVG(q.TOTAL_ELAPSED_TIME) as AVG_EXECUTION_TIME,
SUM(q.CREDITS_USED_CLOUD_SERVICES) as TOTAL_CREDITS_USED,
SUM(q.BYTES_SCANNED) as TOTAL_BYTES_SCANNED,
SUM(q.ROWS_PRODUCED) as TOTAL_ROWS_PRODUCED,
COUNT(CASE WHEN q.BYTES_SPILLED_TO_LOCAL_STORAGE > 0 OR q.BYTES_SPILLED_TO_REMOTE_STORAGE > 0 THEN 1 END) as SPILLED_QUERIES,
COUNT(DISTINCT q.WAREHOUSE_NAME) as WAREHOUSES_USED,
COUNT(DISTINCT q.DATABASE_NAME) as DATABASES_ACCESSED,
COUNT(DISTINCT q.SCHEMA_NAME) as SCHEMAS_ACCESSED,
MIN(q.START_TIME) as FIRST_QUERY_TIME,
MAX(q.START_TIME) as LAST_QUERY_TIME
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY q
WHERE q.START_TIME >= CURRENT_DATE - 1
AND q.USER_NAME IS NOT NULL
GROUP BY q.USER_NAME
),
user_login_stats AS (
SELECT
l.USER_NAME,
COUNT(*) as TOTAL_LOGIN_ATTEMPTS,
COUNT(CASE WHEN l.IS_SUCCESS = 'YES' THEN 1 END) as SUCCESSFUL_LOGINS,
COUNT(CASE WHEN l.IS_SUCCESS = 'NO' THEN 1 END) as FAILED_LOGINS,
COUNT(DISTINCT l.CLIENT_IP) as UNIQUE_IPS,
COUNT(DISTINCT l.REPORTED_CLIENT_TYPE) as CLIENT_TYPES_USED,
MIN(l.EVENT_TIMESTAMP) as FIRST_LOGIN_ATTEMPT,
MAX(l.EVENT_TIMESTAMP) as LAST_LOGIN_ATTEMPT
FROM SNOWFLAKE.ACCOUNT_USAGE.LOGIN_HISTORY l
WHERE l.EVENT_TIMESTAMP >= CURRENT_DATE - 1
AND l.USER_NAME IS NOT NULL
GROUP BY l.USER_NAME
)
SELECT
-- User Information
u.USER_ID,
u.NAME as USER_NAME,
u.LOGIN_NAME,
u.DISPLAY_NAME,
u.FIRST_NAME,
u.LAST_NAME,
u.EMAIL,
u.TYPE as USER_TYPE,
u.CREATED_ON,
u.DELETED_ON,
u.DISABLED,
u.SNOWFLAKE_LOCK,
-- Authentication Details
u.HAS_PASSWORD,
u.MUST_CHANGE_PASSWORD,
u.HAS_MFA,
u.BYPASS_MFA_UNTIL,
u.HAS_RSA_PUBLIC_KEY,
u.PASSWORD_LAST_SET_TIME,
u.LAST_SUCCESS_LOGIN,
u.EXPIRES_AT,
u.LOCKED_UNTIL_TIME,
-- Default Settings
u.DEFAULT_WAREHOUSE,
u.DEFAULT_NAMESPACE,
u.DEFAULT_ROLE,
u.DEFAULT_SECONDARY_ROLE,
u.OWNER,
-- Database Context (for database users)
u.DATABASE_NAME as USER_DATABASE,
u.SCHEMA_NAME as USER_SCHEMA,
-- Query Activity (Last 24 hours)
COALESCE(qs.TOTAL_QUERIES, 0) as QUERIES_LAST_24H,
COALESCE(qs.SUCCESSFUL_QUERIES, 0) as SUCCESSFUL_QUERIES_24H,
COALESCE(qs.FAILED_QUERIES, 0) as FAILED_QUERIES_24H,
COALESCE(qs.RUNNING_QUERIES, 0) as RUNNING_QUERIES_24H,
COALESCE(qs.TOTAL_EXECUTION_TIME, 0) as TOTAL_EXEC_TIME_24H,
COALESCE(qs.AVG_EXECUTION_TIME, 0) as AVG_EXEC_TIME_24H,
COALESCE(qs.TOTAL_CREDITS_USED, 0) as CREDITS_USED_24H,
COALESCE(qs.TOTAL_BYTES_SCANNED, 0) as BYTES_SCANNED_24H,
COALESCE(qs.TOTAL_ROWS_PRODUCED, 0) as ROWS_PRODUCED_24H,
COALESCE(qs.SPILLED_QUERIES, 0) as SPILLED_QUERIES_24H,
COALESCE(qs.WAREHOUSES_USED, 0) as WAREHOUSES_USED_24H,
COALESCE(qs.DATABASES_ACCESSED, 0) as DATABASES_ACCESSED_24H,
COALESCE(qs.SCHEMAS_ACCESSED, 0) as SCHEMAS_ACCESSED_24H,
qs.FIRST_QUERY_TIME as FIRST_QUERY_24H,
qs.LAST_QUERY_TIME as LAST_QUERY_24H,
-- Login Activity (Last 24 hours)
COALESCE(ls.TOTAL_LOGIN_ATTEMPTS, 0) as LOGIN_ATTEMPTS_24H,
COALESCE(ls.SUCCESSFUL_LOGINS, 0) as SUCCESSFUL_LOGINS_24H,
COALESCE(ls.FAILED_LOGINS, 0) as FAILED_LOGINS_24H,
COALESCE(ls.UNIQUE_IPS, 0) as UNIQUE_IPS_24H,
COALESCE(ls.CLIENT_TYPES_USED, 0) as CLIENT_TYPES_24H,
ls.FIRST_LOGIN_ATTEMPT as FIRST_LOGIN_24H,
ls.LAST_LOGIN_ATTEMPT as LAST_LOGIN_24H,
-- Activity Classifications
CASE
WHEN COALESCE(qs.TOTAL_QUERIES, 0) = 0 THEN 'INACTIVE'
WHEN COALESCE(qs.TOTAL_QUERIES, 0) <= 10 THEN 'LOW_ACTIVITY'
WHEN COALESCE(qs.TOTAL_QUERIES, 0) <= 100 THEN 'MEDIUM_ACTIVITY'
WHEN COALESCE(qs.TOTAL_QUERIES, 0) <= 500 THEN 'HIGH_ACTIVITY'
ELSE 'VERY_HIGH_ACTIVITY'
END as ACTIVITY_LEVEL,
CASE
WHEN COALESCE(qs.FAILED_QUERIES, 0) = 0 THEN 'NO_FAILURES'
WHEN COALESCE(qs.FAILED_QUERIES, 0) <= 5 THEN 'LOW_FAILURES'
WHEN COALESCE(qs.FAILED_QUERIES, 0) <= 20 THEN 'MEDIUM_FAILURES'
ELSE 'HIGH_FAILURES'
END as FAILURE_RATE,
CASE
WHEN COALESCE(qs.TOTAL_CREDITS_USED, 0) = 0 THEN 'NO_COST'
WHEN COALESCE(qs.TOTAL_CREDITS_USED, 0) <= 1 THEN 'LOW_COST'
WHEN COALESCE(qs.TOTAL_CREDITS_USED, 0) <= 10 THEN 'MEDIUM_COST'
ELSE 'HIGH_COST'
END as COST_CATEGORY,
-- Security Indicators
CASE
WHEN u.HAS_MFA = TRUE THEN 'MFA_ENABLED'
ELSE 'MFA_DISABLED'
END as MFA_STATUS,
CASE
WHEN u.DISABLED = TRUE OR u.SNOWFLAKE_LOCK = TRUE THEN 'LOCKED'
WHEN u.EXPIRES_AT IS NOT NULL AND u.EXPIRES_AT < CURRENT_TIMESTAMP THEN 'EXPIRED'
ELSE 'ACTIVE'
END as ACCOUNT_STATUS,
-- Analysis metadata
CURRENT_TIMESTAMP as ANALYSIS_TIMESTAMP,
CURRENT_DATE - 1 as ANALYSIS_DATE
FROM SNOWFLAKE.ACCOUNT_USAGE.USERS u
LEFT JOIN user_query_stats qs ON u.NAME = qs.USER_NAME
LEFT JOIN user_login_stats ls ON u.NAME = ls.USER_NAME
WHERE u.DELETED_ON IS NULL
ORDER BY COALESCE(qs.TOTAL_QUERIES, 0) DESC, u.NAME;
-- =====================================================
-- TABLE 2: DATABASE_ANALYTICS_SUMMARY
-- Comprehensive database usage and storage analytics
-- =====================================================
CREATE OR REPLACE TABLE DATABASE_ANALYTICS_SUMMARY AS
WITH database_query_stats AS (
SELECT
q.DATABASE_NAME,
COUNT(*) as TOTAL_QUERIES,
COUNT(CASE WHEN q.EXECUTION_STATUS = 'SUCCESS' THEN 1 END) as SUCCESSFUL_QUERIES,
COUNT(CASE WHEN q.EXECUTION_STATUS = 'FAIL' THEN 1 END) as FAILED_QUERIES,
COUNT(DISTINCT q.USER_NAME) as UNIQUE_USERS,
COUNT(DISTINCT q.WAREHOUSE_NAME) as WAREHOUSES_USED,
COUNT(DISTINCT q.SCHEMA_NAME) as SCHEMAS_ACCESSED,
SUM(q.TOTAL_ELAPSED_TIME) as TOTAL_EXECUTION_TIME,
AVG(q.TOTAL_ELAPSED_TIME) as AVG_EXECUTION_TIME,
SUM(q.CREDITS_USED_CLOUD_SERVICES) as TOTAL_CREDITS_USED,
SUM(q.BYTES_SCANNED) as TOTAL_BYTES_SCANNED,
SUM(q.ROWS_PRODUCED) as TOTAL_ROWS_PRODUCED,
MIN(q.START_TIME) as FIRST_QUERY_TIME,
MAX(q.START_TIME) as LAST_QUERY_TIME
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY q
WHERE q.START_TIME >= CURRENT_DATE - 1
AND q.DATABASE_NAME IS NOT NULL
GROUP BY q.DATABASE_NAME
),
database_storage_stats AS (
SELECT
ds.DATABASE_NAME,
AVG(ds.AVERAGE_DATABASE_BYTES) as AVG_DATABASE_BYTES,
AVG(ds.AVERAGE_FAILSAFE_BYTES) as AVG_FAILSAFE_BYTES,
AVG(ds.AVERAGE_HYBRID_TABLE_STORAGE_BYTES) as AVG_HYBRID_STORAGE_BYTES,
MAX(ds.USAGE_DATE) as LATEST_STORAGE_DATE
FROM SNOWFLAKE.ACCOUNT_USAGE.DATABASE_STORAGE_USAGE_HISTORY ds
WHERE ds.USAGE_DATE >= CURRENT_DATE - 7 -- Last 7 days for storage trends
AND ds.DELETED IS NULL
GROUP BY ds.DATABASE_NAME
),
database_table_counts AS (
SELECT
t.TABLE_CATALOG as DATABASE_NAME,
COUNT(*) as TOTAL_TABLES,
COUNT(CASE WHEN t.TABLE_TYPE = 'BASE TABLE' THEN 1 END) as BASE_TABLES,
COUNT(CASE WHEN t.TABLE_TYPE = 'VIEW' THEN 1 END) as VIEWS,
COUNT(CASE WHEN t.IS_TRANSIENT = 'YES' THEN 1 END) as TRANSIENT_TABLES,
COUNT(CASE WHEN t.IS_ICEBERG = 'YES' THEN 1 END) as ICEBERG_TABLES,
COUNT(CASE WHEN t.IS_HYBRID = 'YES' THEN 1 END) as HYBRID_TABLES,
SUM(t.ROW_COUNT) as TOTAL_ROWS,
SUM(t.BYTES) as TOTAL_TABLE_BYTES,
COUNT(DISTINCT t.TABLE_SCHEMA) as UNIQUE_SCHEMAS
FROM SNOWFLAKE.ACCOUNT_USAGE.TABLES t
WHERE t.DELETED IS NULL
GROUP BY t.TABLE_CATALOG
)
SELECT
-- Database Information (Note: Database metadata might be limited in ACCOUNT_USAGE)
dq.DATABASE_NAME,
-- Table and Schema Statistics
COALESCE(dt.TOTAL_TABLES, 0) as TOTAL_TABLES,
COALESCE(dt.BASE_TABLES, 0) as BASE_TABLES,
COALESCE(dt.VIEWS, 0) as TOTAL_VIEWS,
COALESCE(dt.TRANSIENT_TABLES, 0) as TRANSIENT_TABLES,
COALESCE(dt.ICEBERG_TABLES, 0) as ICEBERG_TABLES,
COALESCE(dt.HYBRID_TABLES, 0) as HYBRID_TABLES,
COALESCE(dt.UNIQUE_SCHEMAS, 0) as TOTAL_SCHEMAS,
COALESCE(dt.TOTAL_ROWS, 0) as TOTAL_ROWS,
COALESCE(dt.TOTAL_TABLE_BYTES, 0) as TOTAL_TABLE_BYTES,
-- Storage Statistics
COALESCE(ds.AVG_DATABASE_BYTES, 0) as AVG_DATABASE_BYTES,
COALESCE(ds.AVG_FAILSAFE_BYTES, 0) as AVG_FAILSAFE_BYTES,
COALESCE(ds.AVG_HYBRID_STORAGE_BYTES, 0) as AVG_HYBRID_STORAGE_BYTES,
ds.LATEST_STORAGE_DATE,
-- Query Activity (Last 24 hours)
COALESCE(dq.TOTAL_QUERIES, 0) as QUERIES_LAST_24H,
COALESCE(dq.SUCCESSFUL_QUERIES, 0) as SUCCESSFUL_QUERIES_24H,
COALESCE(dq.FAILED_QUERIES, 0) as FAILED_QUERIES_24H,
COALESCE(dq.UNIQUE_USERS, 0) as UNIQUE_USERS_24H,
COALESCE(dq.WAREHOUSES_USED, 0) as WAREHOUSES_USED_24H,
COALESCE(dq.SCHEMAS_ACCESSED, 0) as SCHEMAS_ACCESSED_24H,
COALESCE(dq.TOTAL_EXECUTION_TIME, 0) as TOTAL_EXEC_TIME_24H,
COALESCE(dq.AVG_EXECUTION_TIME, 0) as AVG_EXEC_TIME_24H,
COALESCE(dq.TOTAL_CREDITS_USED, 0) as CREDITS_USED_24H,
COALESCE(dq.TOTAL_BYTES_SCANNED, 0) as BYTES_SCANNED_24H,
COALESCE(dq.TOTAL_ROWS_PRODUCED, 0) as ROWS_PRODUCED_24H,
dq.FIRST_QUERY_TIME as FIRST_QUERY_24H,
dq.LAST_QUERY_TIME as LAST_QUERY_24H,
-- Size Classifications
CASE
WHEN COALESCE(dt.TOTAL_TABLES, 0) = 0 THEN 'EMPTY'
WHEN COALESCE(dt.TOTAL_TABLES, 0) <= 10 THEN 'SMALL'
WHEN COALESCE(dt.TOTAL_TABLES, 0) <= 100 THEN 'MEDIUM'
WHEN COALESCE(dt.TOTAL_TABLES, 0) <= 1000 THEN 'LARGE'
ELSE 'VERY_LARGE'
END as DATABASE_SIZE_CATEGORY,
CASE
WHEN COALESCE(ds.AVG_DATABASE_BYTES, 0) = 0 THEN 'NO_STORAGE'
WHEN COALESCE(ds.AVG_DATABASE_BYTES, 0) <= 1073741824 THEN 'SMALL_STORAGE' -- 1GB
WHEN COALESCE(ds.AVG_DATABASE_BYTES, 0) <= 107374182400 THEN 'MEDIUM_STORAGE' -- 100GB
WHEN COALESCE(ds.AVG_DATABASE_BYTES, 0) <= 1099511627776 THEN 'LARGE_STORAGE' -- 1TB
ELSE 'VERY_LARGE_STORAGE'
END as STORAGE_SIZE_CATEGORY,
CASE
WHEN COALESCE(dq.TOTAL_QUERIES, 0) = 0 THEN 'INACTIVE'
WHEN COALESCE(dq.TOTAL_QUERIES, 0) <= 100 THEN 'LOW_USAGE'
WHEN COALESCE(dq.TOTAL_QUERIES, 0) <= 1000 THEN 'MEDIUM_USAGE'
WHEN COALESCE(dq.TOTAL_QUERIES, 0) <= 10000 THEN 'HIGH_USAGE'
ELSE 'VERY_HIGH_USAGE'
END as USAGE_LEVEL,
-- Analysis metadata
CURRENT_TIMESTAMP as ANALYSIS_TIMESTAMP,
CURRENT_DATE - 1 as ANALYSIS_DATE
FROM database_query_stats dq
FULL OUTER JOIN database_storage_stats ds ON dq.DATABASE_NAME = ds.DATABASE_NAME
FULL OUTER JOIN database_table_counts dt ON COALESCE(dq.DATABASE_NAME, ds.DATABASE_NAME) = dt.DATABASE_NAME
WHERE COALESCE(dq.DATABASE_NAME, ds.DATABASE_NAME, dt.DATABASE_NAME) IS NOT NULL
ORDER BY COALESCE(dq.TOTAL_QUERIES, 0) DESC, COALESCE(dq.DATABASE_NAME, ds.DATABASE_NAME, dt.DATABASE_NAME);
-- =====================================================
-- TABLE 3: TABLES_ANALYTICS_SUMMARY
-- Comprehensive table usage and metadata analytics
-- =====================================================
CREATE OR REPLACE TABLE TABLES_ANALYTICS_SUMMARY AS
WITH table_query_stats AS (
SELECT
ao.REFERENCED_DATABASE,
ao.REFERENCED_SCHEMA,
ao.REFERENCED_OBJECT_NAME as TABLE_NAME,
COUNT(DISTINCT ao.QUERY_ID) as QUERIES_ACCESSING_TABLE,
COUNT(DISTINCT qh.USER_NAME) as UNIQUE_USERS_ACCESSING,
COUNT(DISTINCT qh.WAREHOUSE_NAME) as WAREHOUSES_USED,
MIN(qh.START_TIME) as FIRST_ACCESS_TIME,
MAX(qh.START_TIME) as LAST_ACCESS_TIME
FROM SNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORY ao
JOIN SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY qh ON ao.QUERY_ID = qh.QUERY_ID
WHERE ao.QUERY_START_TIME >= CURRENT_DATE - 1
AND ao.REFERENCED_OBJECT_DOMAIN = 'Table'
AND qh.START_TIME >= CURRENT_DATE - 1
GROUP BY ao.REFERENCED_DATABASE, ao.REFERENCED_SCHEMA, ao.REFERENCED_OBJECT_NAME
)
SELECT
-- Table Identity
t.TABLE_ID,
t.TABLE_NAME,
t.TABLE_SCHEMA_ID,
t.TABLE_SCHEMA,
t.TABLE_CATALOG_ID,
t.TABLE_CATALOG as DATABASE_NAME,
t.TABLE_OWNER,
t.OWNER_ROLE_TYPE,
-- Table Properties
t.TABLE_TYPE,
t.IS_TRANSIENT,
t.IS_ICEBERG,
t.IS_DYNAMIC,
t.IS_HYBRID,
t.IS_INSERTABLE_INTO,
t.IS_TYPED,
t.CLUSTERING_KEY,
t.AUTO_CLUSTERING_ON,
-- Storage and Size
t.ROW_COUNT,
t.BYTES,
t.RETENTION_TIME,
CASE
WHEN t.BYTES > 0 AND t.ROW_COUNT > 0 THEN
ROUND(t.BYTES::FLOAT / t.ROW_COUNT::FLOAT, 2)
ELSE 0
END as AVG_BYTES_PER_ROW,
-- Timestamps
t.CREATED,
t.LAST_ALTERED,
t.LAST_DDL,
t.LAST_DDL_BY,
t.DELETED,
-- Usage Statistics (Last 24 hours)
COALESCE(tq.QUERIES_ACCESSING_TABLE, 0) as QUERIES_LAST_24H,
COALESCE(tq.UNIQUE_USERS_ACCESSING, 0) as UNIQUE_USERS_24H,
COALESCE(tq.WAREHOUSES_USED, 0) as WAREHOUSES_USED_24H,
tq.FIRST_ACCESS_TIME as FIRST_ACCESS_24H,
tq.LAST_ACCESS_TIME as LAST_ACCESS_24H,
-- Column Information (from COLUMNS table)
(SELECT COUNT(*)
FROM SNOWFLAKE.ACCOUNT_USAGE.COLUMNS c
WHERE c.TABLE_ID = t.TABLE_ID AND c.DELETED IS NULL) as TOTAL_COLUMNS,
-- Size Classifications
CASE
WHEN t.ROW_COUNT = 0 THEN 'EMPTY'
WHEN t.ROW_COUNT <= 1000 THEN 'TINY'
WHEN t.ROW_COUNT <= 100000 THEN 'SMALL'
WHEN t.ROW_COUNT <= 10000000 THEN 'MEDIUM'
WHEN t.ROW_COUNT <= 1000000000 THEN 'LARGE'
ELSE 'VERY_LARGE'
END as SIZE_CATEGORY,
CASE
WHEN t.BYTES = 0 THEN 'NO_STORAGE'
WHEN t.BYTES <= 1048576 THEN 'TINY_STORAGE' -- 1MB
WHEN t.BYTES <= 104857600 THEN 'SMALL_STORAGE' -- 100MB
WHEN t.BYTES <= 1073741824 THEN 'MEDIUM_STORAGE' -- 1GB
WHEN t.BYTES <= 107374182400 THEN 'LARGE_STORAGE' -- 100GB
ELSE 'VERY_LARGE_STORAGE'
END as STORAGE_CATEGORY,
CASE
WHEN COALESCE(tq.QUERIES_ACCESSING_TABLE, 0) = 0 THEN 'UNUSED'
WHEN COALESCE(tq.QUERIES_ACCESSING_TABLE, 0) <= 10 THEN 'LOW_USAGE'
WHEN COALESCE(tq.QUERIES_ACCESSING_TABLE, 0) <= 100 THEN 'MEDIUM_USAGE'
WHEN COALESCE(tq.QUERIES_ACCESSING_TABLE, 0) <= 1000 THEN 'HIGH_USAGE'
ELSE 'VERY_HIGH_USAGE'
END as USAGE_LEVEL,
-- Age Classifications
CASE
WHEN t.CREATED >= CURRENT_DATE - 1 THEN 'BRAND_NEW'
WHEN t.CREATED >= CURRENT_DATE - 7 THEN 'RECENT'
WHEN t.CREATED >= CURRENT_DATE - 30 THEN 'NEW'
WHEN t.CREATED >= CURRENT_DATE - 90 THEN 'ESTABLISHED'
ELSE 'OLD'
END as AGE_CATEGORY,
-- Maintenance Status
CASE
WHEN t.LAST_ALTERED >= CURRENT_DATE - 1 THEN 'RECENTLY_MODIFIED'
WHEN t.LAST_ALTERED >= CURRENT_DATE - 7 THEN 'RECENTLY_UPDATED'
WHEN t.LAST_ALTERED >= CURRENT_DATE - 30 THEN 'UPDATED_THIS_MONTH'
ELSE 'STABLE'
END as MAINTENANCE_STATUS,
-- Performance Indicators
CASE
WHEN t.CLUSTERING_KEY IS NOT NULL THEN 'CLUSTERED'
ELSE 'NOT_CLUSTERED'
END as CLUSTERING_STATUS,
CASE
WHEN t.AUTO_CLUSTERING_ON = TRUE THEN 'AUTO_CLUSTERING_ON'
ELSE 'AUTO_CLUSTERING_OFF'
END as AUTO_CLUSTERING_STATUS,
-- Analysis metadata
CURRENT_TIMESTAMP as ANALYSIS_TIMESTAMP,
CURRENT_DATE - 1 as ANALYSIS_DATE,
t.COMMENT
FROM SNOWFLAKE.ACCOUNT_USAGE.TABLES t
LEFT JOIN table_query_stats tq ON t.TABLE_CATALOG = tq.REFERENCED_DATABASE
AND t.TABLE_SCHEMA = tq.REFERENCED_SCHEMA
AND t.TABLE_NAME = tq.TABLE_NAME
WHERE t.DELETED IS NULL
ORDER BY COALESCE(tq.QUERIES_ACCESSING_TABLE, 0) DESC, t.BYTES DESC, t.TABLE_NAME;
-- =====================================================
-- REFRESH PROCEDURES
-- =====================================================
CREATE OR REPLACE PROCEDURE REFRESH_USERS_ANALYTICS()
RETURNS STRING
LANGUAGE SQL
AS
$$
BEGIN
CREATE OR REPLACE TABLE USERS_ANALYTICS_SUMMARY AS
WITH user_query_stats AS (
SELECT
q.USER_NAME,
COUNT(*) as TOTAL_QUERIES,
COUNT(CASE WHEN q.EXECUTION_STATUS = 'SUCCESS' THEN 1 END) as SUCCESSFUL_QUERIES,
COUNT(CASE WHEN q.EXECUTION_STATUS = 'FAIL' THEN 1 END) as FAILED_QUERIES,
COUNT(CASE WHEN q.EXECUTION_STATUS = 'RUNNING' THEN 1 END) as RUNNING_QUERIES,
SUM(q.TOTAL_ELAPSED_TIME) as TOTAL_EXECUTION_TIME,
AVG(q.TOTAL_ELAPSED_TIME) as AVG_EXECUTION_TIME,
SUM(q.CREDITS_USED_CLOUD_SERVICES) as TOTAL_CREDITS_USED,
SUM(q.BYTES_SCANNED) as TOTAL_BYTES_SCANNED,
SUM(q.ROWS_PRODUCED) as TOTAL_ROWS_PRODUCED,
COUNT(CASE WHEN q.BYTES_SPILLED_TO_LOCAL_STORAGE > 0 OR q.BYTES_SPILLED_TO_REMOTE_STORAGE > 0 THEN 1 END) as SPILLED_QUERIES,
COUNT(DISTINCT q.WAREHOUSE_NAME) as WAREHOUSES_USED,
COUNT(DISTINCT q.DATABASE_NAME) as DATABASES_ACCESSED,
COUNT(DISTINCT q.SCHEMA_NAME) as SCHEMAS_ACCESSED,
MIN(q.START_TIME) as FIRST_QUERY_TIME,
MAX(q.START_TIME) as LAST_QUERY_TIME
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY q
WHERE q.START_TIME >= CURRENT_DATE - 1
AND q.USER_NAME IS NOT NULL
GROUP BY q.USER_NAME
),
user_login_stats AS (
SELECT
l.USER_NAME,
COUNT(*) as TOTAL_LOGIN_ATTEMPTS,
COUNT(CASE WHEN l.IS_SUCCESS = 'YES' THEN 1 END) as SUCCESSFUL_LOGINS,
COUNT(CASE WHEN l.IS_SUCCESS = 'NO' THEN 1 END) as FAILED_LOGINS,
COUNT(DISTINCT l.CLIENT_IP) as UNIQUE_IPS,
COUNT(DISTINCT l.REPORTED_CLIENT_TYPE) as CLIENT_TYPES_USED,
MIN(l.EVENT_TIMESTAMP) as FIRST_LOGIN_ATTEMPT,
MAX(l.EVENT_TIMESTAMP) as LAST_LOGIN_ATTEMPT
FROM SNOWFLAKE.ACCOUNT_USAGE.LOGIN_HISTORY l
WHERE l.EVENT_TIMESTAMP >= CURRENT_DATE - 1
AND l.USER_NAME IS NOT NULL
GROUP BY l.USER_NAME
)
SELECT
u.USER_ID, u.NAME as USER_NAME, u.LOGIN_NAME, u.DISPLAY_NAME, u.FIRST_NAME, u.LAST_NAME, u.EMAIL, u.TYPE as USER_TYPE,
u.CREATED_ON, u.DELETED_ON, u.DISABLED, u.SNOWFLAKE_LOCK, u.HAS_PASSWORD, u.MUST_CHANGE_PASSWORD, u.HAS_MFA, u.BYPASS_MFA_UNTIL,
u.HAS_RSA_PUBLIC_KEY, u.PASSWORD_LAST_SET_TIME, u.LAST_SUCCESS_LOGIN, u.EXPIRES_AT, u.LOCKED_UNTIL_TIME,
u.DEFAULT_WAREHOUSE, u.DEFAULT_NAMESPACE, u.DEFAULT_ROLE, u.DEFAULT_SECONDARY_ROLE, u.OWNER, u.DATABASE_NAME as USER_DATABASE, u.SCHEMA_NAME as USER_SCHEMA,
COALESCE(qs.TOTAL_QUERIES, 0) as QUERIES_LAST_24H, COALESCE(qs.SUCCESSFUL_QUERIES, 0) as SUCCESSFUL_QUERIES_24H,
COALESCE(qs.FAILED_QUERIES, 0) as FAILED_QUERIES_24H, COALESCE(qs.RUNNING_QUERIES, 0) as RUNNING_QUERIES_24H,
COALESCE(qs.TOTAL_EXECUTION_TIME, 0) as TOTAL_EXEC_TIME_24H, COALESCE(qs.AVG_EXECUTION_TIME, 0) as AVG_EXEC_TIME_24H,
COALESCE(qs.TOTAL_CREDITS_USED, 0) as CREDITS_USED_24H, COALESCE(qs.TOTAL_BYTES_SCANNED, 0) as BYTES_SCANNED_24H,
COALESCE(qs.TOTAL_ROWS_PRODUCED, 0) as ROWS_PRODUCED_24H, COALESCE(qs.SPILLED_QUERIES, 0) as SPILLED_QUERIES_24H,
COALESCE(qs.WAREHOUSES_USED, 0) as WAREHOUSES_USED_24H, COALESCE(qs.DATABASES_ACCESSED, 0) as DATABASES_ACCESSED_24H,
COALESCE(qs.SCHEMAS_ACCESSED, 0) as SCHEMAS_ACCESSED_24H, qs.FIRST_QUERY_TIME as FIRST_QUERY_24H, qs.LAST_QUERY_TIME as LAST_QUERY_24H,
COALESCE(ls.TOTAL_LOGIN_ATTEMPTS, 0) as LOGIN_ATTEMPTS_24H, COALESCE(ls.SUCCESSFUL_LOGINS, 0) as SUCCESSFUL_LOGINS_24H,
COALESCE(ls.FAILED_LOGINS, 0) as FAILED_LOGINS_24H, COALESCE(ls.UNIQUE_IPS, 0) as UNIQUE_IPS_24H,
COALESCE(ls.CLIENT_TYPES_USED, 0) as CLIENT_TYPES_24H, ls.FIRST_LOGIN_ATTEMPT as FIRST_LOGIN_24H, ls.LAST_LOGIN_ATTEMPT as LAST_LOGIN_24H,
CASE
WHEN COALESCE(qs.TOTAL_QUERIES, 0) = 0 THEN 'INACTIVE'
WHEN COALESCE(qs.TOTAL_QUERIES, 0) <= 10 THEN 'LOW_ACTIVITY'
WHEN COALESCE(qs.TOTAL_QUERIES, 0) <= 100 THEN 'MEDIUM_ACTIVITY'
WHEN COALESCE(qs.TOTAL_QUERIES, 0) <= 500 THEN 'HIGH_ACTIVITY'
ELSE 'VERY_HIGH_ACTIVITY'
END as ACTIVITY_LEVEL,
CASE
WHEN COALESCE(qs.FAILED_QUERIES, 0) = 0 THEN 'NO_FAILURES'
WHEN COALESCE(qs.FAILED_QUERIES, 0) <= 5 THEN 'LOW_FAILURES'
WHEN COALESCE(qs.FAILED_QUERIES, 0) <= 20 THEN 'MEDIUM_FAILURES'
ELSE 'HIGH_FAILURES'
END as FAILURE_RATE,
CASE
WHEN COALESCE(qs.TOTAL_CREDITS_USED, 0) = 0 THEN 'NO_COST'
WHEN COALESCE(qs.TOTAL_CREDITS_USED, 0) <= 1 THEN 'LOW_COST'
WHEN COALESCE(qs.TOTAL_CREDITS_USED, 0) <= 10 THEN 'MEDIUM_COST'
ELSE 'HIGH_COST'
END as COST_CATEGORY,
CASE
WHEN u.HAS_MFA = TRUE THEN 'MFA_ENABLED'
ELSE 'MFA_DISABLED'
END as MFA_STATUS,
CASE
WHEN u.DISABLED = TRUE OR u.SNOWFLAKE_LOCK = TRUE THEN 'LOCKED'
WHEN u.EXPIRES_AT IS NOT NULL AND u.EXPIRES_AT < CURRENT_TIMESTAMP THEN 'EXPIRED'
ELSE 'ACTIVE'
END as ACCOUNT_STATUS,
CURRENT_TIMESTAMP as ANALYSIS_TIMESTAMP,
CURRENT_DATE - 1 as ANALYSIS_DATE
FROM SNOWFLAKE.ACCOUNT_USAGE.USERS u
LEFT JOIN user_query_stats qs ON u.NAME = qs.USER_NAME
LEFT JOIN user_login_stats ls ON u.NAME = ls.USER_NAME
WHERE u.DELETED_ON IS NULL
ORDER BY COALESCE(qs.TOTAL_QUERIES, 0) DESC, u.NAME;
RETURN 'USERS_ANALYTICS_SUMMARY table refreshed successfully at ' || CURRENT_TIMESTAMP;
END;
$;
CREATE OR REPLACE PROCEDURE REFRESH_DATABASE_ANALYTICS()
RETURNS STRING
LANGUAGE SQL
AS
$
BEGIN
CREATE OR REPLACE TABLE DATABASE_ANALYTICS_SUMMARY AS
WITH database_query_stats AS (
SELECT
q.DATABASE_NAME,
COUNT(*) as TOTAL_QUERIES,
COUNT(CASE WHEN q.EXECUTION_STATUS = 'SUCCESS' THEN 1 END) as SUCCESSFUL_QUERIES,
COUNT(CASE WHEN q.EXECUTION_STATUS = 'FAIL' THEN 1 END) as FAILED_QUERIES,
COUNT(DISTINCT q.USER_NAME) as UNIQUE_USERS,
COUNT(DISTINCT q.WAREHOUSE_NAME) as WAREHOUSES_USED,
COUNT(DISTINCT q.SCHEMA_NAME) as SCHEMAS_ACCESSED,
SUM(q.TOTAL_ELAPSED_TIME) as TOTAL_EXECUTION_TIME,
AVG(q.TOTAL_ELAPSED_TIME) as AVG_EXECUTION_TIME,
SUM(q.CREDITS_USED_CLOUD_SERVICES) as TOTAL_CREDITS_USED,
SUM(q.BYTES_SCANNED) as TOTAL_BYTES_SCANNED,
SUM(q.ROWS_PRODUCED) as TOTAL_ROWS_PRODUCED,
MIN(q.START_TIME) as FIRST_QUERY_TIME,
MAX(q.START_TIME) as LAST_QUERY_TIME
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY q
WHERE q.START_TIME >= CURRENT_DATE - 1
AND q.DATABASE_NAME IS NOT NULL
GROUP BY q.DATABASE_NAME
),
database_storage_stats AS (
SELECT
ds.DATABASE_NAME,
AVG(ds.AVERAGE_DATABASE_BYTES) as AVG_DATABASE_BYTES,
AVG(ds.AVERAGE_FAILSAFE_BYTES) as AVG_FAILSAFE_BYTES,
AVG(ds.AVERAGE_HYBRID_TABLE_STORAGE_BYTES) as AVG_HYBRID_STORAGE_BYTES,
MAX(ds.USAGE_DATE) as LATEST_STORAGE_DATE
FROM SNOWFLAKE.ACCOUNT_USAGE.DATABASE_STORAGE_USAGE_HISTORY ds
WHERE ds.USAGE_DATE >= CURRENT_DATE - 7
AND ds.DELETED IS NULL
GROUP BY ds.DATABASE_NAME
),
database_table_counts AS (
SELECT
t.TABLE_CATALOG as DATABASE_NAME,
COUNT(*) as TOTAL_TABLES,
COUNT(CASE WHEN t.TABLE_TYPE = 'BASE TABLE' THEN 1 END) as BASE_TABLES,
COUNT(CASE WHEN t.TABLE_TYPE = 'VIEW' THEN 1 END) as VIEWS,
COUNT(CASE WHEN t.IS_TRANSIENT = 'YES' THEN 1 END) as TRANSIENT_TABLES,
COUNT(CASE WHEN t.IS_ICEBERG = 'YES' THEN 1 END) as ICEBERG_TABLES,
COUNT(CASE WHEN t.IS_HYBRID = 'YES' THEN 1 END) as HYBRID_TABLES,
SUM(t.ROW_COUNT) as TOTAL_ROWS,
SUM(t.BYTES) as TOTAL_TABLE_BYTES,
COUNT(DISTINCT t.TABLE_SCHEMA) as UNIQUE_SCHEMAS
FROM SNOWFLAKE.ACCOUNT_USAGE.TABLES t
WHERE t.DELETED IS NULL
GROUP BY t.TABLE_CATALOG
)
SELECT
dq.DATABASE_NAME,
COALESCE(dt.TOTAL_TABLES, 0) as TOTAL_TABLES, COALESCE(dt.BASE_TABLES, 0) as BASE_TABLES, COALESCE(dt.VIEWS, 0) as TOTAL_VIEWS,
COALESCE(dt.TRANSIENT_TABLES, 0) as TRANSIENT_TABLES, COALESCE(dt.ICEBERG_TABLES, 0) as ICEBERG_TABLES, COALESCE(dt.HYBRID_TABLES, 0) as HYBRID_TABLES,
COALESCE(dt.UNIQUE_SCHEMAS, 0) as TOTAL_SCHEMAS, COALESCE(dt.TOTAL_ROWS, 0) as TOTAL_ROWS, COALESCE(dt.TOTAL_TABLE_BYTES, 0) as TOTAL_TABLE_BYTES,
COALESCE(ds.AVG_DATABASE_BYTES, 0) as AVG_DATABASE_BYTES, COALESCE(ds.AVG_FAILSAFE_BYTES, 0) as AVG_FAILSAFE_BYTES,
COALESCE(ds.AVG_HYBRID_STORAGE_BYTES, 0) as AVG_HYBRID_STORAGE_BYTES, ds.LATEST_STORAGE_DATE,
COALESCE(dq.TOTAL_QUERIES, 0) as QUERIES_LAST_24H, COALESCE(dq.SUCCESSFUL_QUERIES, 0) as SUCCESSFUL_QUERIES_24H,
COALESCE(dq.FAILED_QUERIES, 0) as FAILED_QUERIES_24H, COALESCE(dq.UNIQUE_USERS, 0) as UNIQUE_USERS_24H,
COALESCE(dq.WAREHOUSES_USED, 0) as WAREHOUSES_USED_24H, COALESCE(dq.SCHEMAS_ACCESSED, 0) as SCHEMAS_ACCESSED_24H,
COALESCE(dq.TOTAL_EXECUTION_TIME, 0) as TOTAL_EXEC_TIME_24H, COALESCE(dq.AVG_EXECUTION_TIME, 0) as AVG_EXEC_TIME_24H,
COALESCE(dq.TOTAL_CREDITS_USED, 0) as CREDITS_USED_24H, COALESCE(dq.TOTAL_BYTES_SCANNED, 0) as BYTES_SCANNED_24H,
COALESCE(dq.TOTAL_ROWS_PRODUCED, 0) as ROWS_PRODUCED_24H, dq.FIRST_QUERY_TIME as FIRST_QUERY_24H, dq.LAST_QUERY_TIME as LAST_QUERY_24H,
CASE
WHEN COALESCE(dt.TOTAL_TABLES, 0) = 0 THEN 'EMPTY'
WHEN COALESCE(dt.TOTAL_TABLES, 0) <= 10 THEN 'SMALL'
WHEN COALESCE(dt.TOTAL_TABLES, 0) <= 100 THEN 'MEDIUM'
WHEN COALESCE(dt.TOTAL_TABLES, 0) <= 1000 THEN 'LARGE'
ELSE 'VERY_LARGE'
END as DATABASE_SIZE_CATEGORY,
CASE
WHEN COALESCE(ds.AVG_DATABASE_BYTES, 0) = 0 THEN 'NO_STORAGE'
WHEN COALESCE(ds.AVG_DATABASE_BYTES, 0) <= 1073741824 THEN 'SMALL_STORAGE'
WHEN COALESCE(ds.AVG_DATABASE_BYTES, 0) <= 107374182400 THEN 'MEDIUM_STORAGE'
WHEN COALESCE(ds.AVG_DATABASE_BYTES, 0) <= 1099511627776 THEN 'LARGE_STORAGE'
ELSE 'VERY_LARGE_STORAGE'
END as STORAGE_SIZE_CATEGORY,
CASE
WHEN COALESCE(dq.TOTAL_QUERIES, 0) = 0 THEN 'INACTIVE'
WHEN COALESCE(dq.TOTAL_QUERIES, 0) <= 100 THEN 'LOW_USAGE'
WHEN COALESCE(dq.TOTAL_QUERIES, 0) <= 1000 THEN 'MEDIUM_USAGE'
WHEN COALESCE(dq.TOTAL_QUERIES, 0) <= 10000 THEN 'HIGH_USAGE'
ELSE 'VERY_HIGH_USAGE'
END as USAGE_LEVEL,
CURRENT_TIMESTAMP as ANALYSIS_TIMESTAMP,
CURRENT_DATE - 1 as ANALYSIS_DATE
FROM database_query_stats dq
FULL OUTER JOIN database_storage_stats ds ON dq.DATABASE_NAME = ds.DATABASE_NAME
FULL OUTER JOIN database_table_counts dt ON COALESCE(dq.DATABASE_NAME, ds.DATABASE_NAME) = dt.DATABASE_NAME
WHERE COALESCE(dq.DATABASE_NAME, ds.DATABASE_NAME, dt.DATABASE_NAME) IS NOT NULL
ORDER BY COALESCE(dq.TOTAL_QUERIES, 0) DESC, COALESCE(dq.DATABASE_NAME, ds.DATABASE_NAME, dt.DATABASE_NAME);
RETURN 'DATABASE_ANALYTICS_SUMMARY table refreshed successfully at ' || CURRENT_TIMESTAMP;
END;
$;
CREATE OR REPLACE PROCEDURE REFRESH_TABLES_ANALYTICS()
RETURNS STRING
LANGUAGE SQL
AS
$
BEGIN
CREATE OR REPLACE TABLE TABLES_ANALYTICS_SUMMARY AS
WITH table_query_stats AS (
SELECT
ao.REFERENCED_DATABASE,
ao.REFERENCED_SCHEMA,
ao.REFERENCED_OBJECT_NAME as TABLE_NAME,
COUNT(DISTINCT ao.QUERY_ID) as QUERIES_ACCESSING_TABLE,
COUNT(DISTINCT qh.USER_NAME) as UNIQUE_USERS_ACCESSING,
COUNT(DISTINCT qh.WAREHOUSE_NAME) as WAREHOUSES_USED,
MIN(qh.START_TIME) as FIRST_ACCESS_TIME,
MAX(qh.START_TIME) as LAST_ACCESS_TIME
FROM SNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORY ao
JOIN SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY qh ON ao.QUERY_ID = qh.QUERY_ID
WHERE ao.QUERY_START_TIME >= CURRENT_DATE - 1
AND ao.REFERENCED_OBJECT_DOMAIN = 'Table'
AND qh.START_TIME >= CURRENT_DATE - 1
GROUP BY ao.REFERENCED_DATABASE, ao.REFERENCED_SCHEMA, ao.REFERENCED_OBJECT_NAME
)
SELECT
t.TABLE_ID, t.TABLE_NAME, t.TABLE_SCHEMA_ID, t.TABLE_SCHEMA, t.TABLE_CATALOG_ID, t.TABLE_CATALOG as DATABASE_NAME,
t.TABLE_OWNER, t.OWNER_ROLE_TYPE, t.TABLE_TYPE, t.IS_TRANSIENT, t.IS_ICEBERG, t.IS_DYNAMIC, t.IS_HYBRID,
t.IS_INSERTABLE_INTO, t.IS_TYPED, t.CLUSTERING_KEY, t.AUTO_CLUSTERING_ON, t.ROW_COUNT, t.BYTES, t.RETENTION_TIME,
CASE
WHEN t.BYTES > 0 AND t.ROW_COUNT > 0 THEN
ROUND(t.BYTES::FLOAT / t.ROW_COUNT::FLOAT, 2)
ELSE 0
END as AVG_BYTES_PER_ROW,
t.CREATED, t.LAST_ALTERED, t.LAST_DDL, t.LAST_DDL_BY, t.DELETED,
COALESCE(tq.QUERIES_ACCESSING_TABLE, 0) as QUERIES_LAST_24H, COALESCE(tq.UNIQUE_USERS_ACCESSING, 0) as UNIQUE_USERS_24H,
COALESCE(tq.WAREHOUSES_USED, 0) as WAREHOUSES_USED_24H, tq.FIRST_ACCESS_TIME as FIRST_ACCESS_24H, tq.LAST_ACCESS_TIME as LAST_ACCESS_24H,
(SELECT COUNT(*) FROM SNOWFLAKE.ACCOUNT_USAGE.COLUMNS c WHERE c.TABLE_ID = t.TABLE_ID AND c.DELETED IS NULL) as TOTAL_COLUMNS,
CASE
WHEN t.ROW_COUNT = 0 THEN 'EMPTY'
WHEN t.ROW_COUNT <= 1000 THEN 'TINY'
WHEN t.ROW_COUNT <= 100000 THEN 'SMALL'
WHEN t.ROW_COUNT <= 10000000 THEN 'MEDIUM'
WHEN t.ROW_COUNT <= 1000000000 THEN 'LARGE'
ELSE 'VERY_LARGE'
END as SIZE_CATEGORY,
CASE
WHEN t.BYTES = 0 THEN 'NO_STORAGE'
WHEN t.BYTES <= 1048576 THEN 'TINY_STORAGE'
WHEN t.BYTES <= 104857600 THEN 'SMALL_STORAGE'
WHEN t.BYTES <= 1073741824 THEN 'MEDIUM_STORAGE'
WHEN t.BYTES <= 107374182400 THEN 'LARGE_STORAGE'
ELSE 'VERY_LARGE_STORAGE'
END as STORAGE_CATEGORY,
CASE
WHEN COALESCE(tq.QUERIES_ACCESSING_TABLE, 0) = 0 THEN 'UNUSED'
WHEN COALESCE(tq.QUERIES_ACCESSING_TABLE, 0) <= 10 THEN 'LOW_USAGE'
WHEN COALESCE(tq.QUERIES_ACCESSING_TABLE, 0) <= 100 THEN 'MEDIUM_USAGE'
WHEN COALESCE(tq.QUERIES_ACCESSING_TABLE, 0) <= 1000 THEN 'HIGH_USAGE'
ELSE 'VERY_HIGH_USAGE'
END as USAGE_LEVEL,
CASE
WHEN t.CREATED >= CURRENT_DATE - 1 THEN 'BRAND_NEW'
WHEN t.CREATED >= CURRENT_DATE - 7 THEN 'RECENT'
WHEN t.CREATED >= CURRENT_DATE - 30 THEN 'NEW'
WHEN t.CREATED >= CURRENT_DATE - 90 THEN 'ESTABLISHED'
ELSE 'OLD'
END as AGE_CATEGORY,
CASE
WHEN t.LAST_ALTERED >= CURRENT_DATE - 1 THEN 'RECENTLY_MODIFIED'
WHEN t.LAST_ALTERED >= CURRENT_DATE - 7 THEN 'RECENTLY_UPDATED'
WHEN t.LAST_ALTERED >= CURRENT_DATE - 30 THEN 'UPDATED_THIS_MONTH'
ELSE 'STABLE'
END as MAINTENANCE_STATUS,
CASE
WHEN t.CLUSTERING_KEY IS NOT NULL THEN 'CLUSTERED'
ELSE 'NOT_CLUSTERED'
END as CLUSTERING_STATUS,
CASE
WHEN t.AUTO_CLUSTERING_ON = TRUE THEN 'AUTO_CLUSTERING_ON'
ELSE 'AUTO_CLUSTERING_OFF'
END as AUTO_CLUSTERING_STATUS,
CURRENT_TIMESTAMP as ANALYSIS_TIMESTAMP,
CURRENT_DATE - 1 as ANALYSIS_DATE,
t.COMMENT
FROM SNOWFLAKE.ACCOUNT_USAGE.TABLES t
LEFT JOIN table_query_stats tq ON t.TABLE_CATALOG = tq.REFERENCED_DATABASE
AND t.TABLE_SCHEMA = tq.REFERENCED_SCHEMA
AND t.TABLE_NAME = tq.TABLE_NAME
WHERE t.DELETED IS NULL
ORDER BY COALESCE(tq.QUERIES_ACCESSING_TABLE, 0) DESC, t.BYTES DESC, t.TABLE_NAME;
RETURN 'TABLES_ANALYTICS_SUMMARY table refreshed successfully at ' || CURRENT_TIMESTAMP;
END;
$;
-- =====================================================
-- MASTER REFRESH PROCEDURE
-- =====================================================
CREATE OR REPLACE PROCEDURE REFRESH_ALL_ANALYTICS()
RETURNS STRING
LANGUAGE SQL
AS
$
DECLARE
result_message STRING DEFAULT '';
BEGIN
CALL REFRESH_USERS_ANALYTICS();
result_message := result_message || 'Users analytics refreshed. ';
CALL REFRESH_DATABASE_ANALYTICS();
result_message := result_message || 'Database analytics refreshed. ';
CALL REFRESH_TABLES_ANALYTICS();
result_message := result_message || 'Tables analytics refreshed. ';
RETURN result_message || 'All analytics tables refreshed successfully at ' || CURRENT_TIMESTAMP;
END;
$;
-- =====================================================
-- EXAMPLE USAGE QUERIES
-- =====================================================
-- USERS ANALYTICS EXAMPLES:
-- 1. Find most active users
-- SELECT USER_NAME, QUERIES_LAST_24H, ACTIVITY_LEVEL, CREDITS_USED_24H, COST_CATEGORY
-- FROM USERS_ANALYTICS_SUMMARY
-- WHERE ACTIVITY_LEVEL IN ('HIGH_ACTIVITY', 'VERY_HIGH_ACTIVITY')
-- ORDER BY QUERIES_LAST_24H DESC;
-- 2. Find users with security issues
-- SELECT USER_NAME, MFA_STATUS, ACCOUNT_STATUS, FAILED_LOGINS_24H, UNIQUE_IPS_24H
-- FROM USERS_ANALYTICS_SUMMARY
-- WHERE MFA_STATUS = 'MFA_DISABLED' OR ACCOUNT_STATUS != 'ACTIVE' OR FAILED_LOGINS_24H > 3;
-- 3. Find users with high failure rates
-- SELECT USER_NAME, QUERIES_LAST_24H, FAILED_QUERIES_24H, FAILURE_RATE, SUCCESSFUL_QUERIES_24H
-- FROM USERS_ANALYTICS_SUMMARY
-- WHERE FAILURE_RATE IN ('MEDIUM_FAILURES', 'HIGH_FAILURES')
-- ORDER BY FAILED_QUERIES_24H DESC;
-- DATABASE ANALYTICS EXAMPLES:
-- 4. Find largest databases by storage
-- SELECT DATABASE_NAME, TOTAL_TABLES, TOTAL_ROWS, TOTAL_TABLE_BYTES, STORAGE_SIZE_CATEGORY
-- FROM DATABASE_ANALYTICS_SUMMARY
-- ORDER BY TOTAL_TABLE_BYTES DESC;
-- 5. Find most active databases
-- SELECT DATABASE_NAME, QUERIES_LAST_24H, UNIQUE_USERS_24H, USAGE_LEVEL, CREDITS_USED_24H
-- FROM DATABASE_ANALYTICS_SUMMARY
-- WHERE USAGE_LEVEL IN ('HIGH_USAGE', 'VERY_HIGH_USAGE')
-- ORDER BY QUERIES_LAST_24H DESC;
-- TABLES ANALYTICS EXAMPLES:
-- 6. Find unused large tables
-- SELECT DATABASE_NAME, TABLE_SCHEMA, TABLE_NAME, ROW_COUNT, BYTES, USAGE_LEVEL, SIZE_CATEGORY
-- FROM TABLES_ANALYTICS_SUMMARY
-- WHERE USAGE_LEVEL = 'UNUSED' AND SIZE_CATEGORY IN ('LARGE', 'VERY_LARGE')
-- ORDER BY BYTES DESC;
-- 7. Find most accessed tables
-- SELECT DATABASE_NAME, TABLE_SCHEMA, TABLE_NAME, QUERIES_LAST_24H, UNIQUE_USERS_24H, USAGE_LEVEL
-- FROM TABLES_ANALYTICS_SUMMARY
-- WHERE USAGE_LEVEL IN ('HIGH_USAGE', 'VERY_HIGH_USAGE')
-- ORDER BY QUERIES_LAST_24H DESC;
-- 8. Find tables that need clustering
-- SELECT DATABASE_NAME, TABLE_SCHEMA, TABLE_NAME, ROW_COUNT, BYTES, CLUSTERING_STATUS, AUTO_CLUSTERING_STATUS
-- FROM TABLES_ANALYTICS_SUMMARY
-- WHERE CLUSTERING_STATUS = 'NOT_CLUSTERED' AND SIZE_CATEGORY IN ('LARGE', 'VERY_LARGE')
-- ORDER BY BYTES DESC;
-- REFRESH ALL TABLES:
-- CALL REFRESH_ALL_ANALYTICS();
-- VIEW SUMMARY OF ALL TABLES:
SELECT 'USERS_ANALYTICS_SUMMARY' as TABLE_NAME, COUNT(*) as ROW_COUNT FROM USERS_ANALYTICS_SUMMARY
UNION ALL
SELECT 'DATABASE_ANALYTICS_SUMMARY' as TABLE_NAME, COUNT(*) as ROW_COUNT FROM DATABASE_ANALYTICS_SUMMARY
UNION ALL
SELECT 'TABLES_ANALYTICS_SUMMARY' as TABLE_NAME, COUNT(*) as ROW_COUNT FROM TABLES_ANALYTICS_SUMMARY;
Armaan Khan
Posted on
DATABS AND TABLES
For further actions, you may consider blocking this person and/or reporting abuse
Top comments (0)