Snowflake Intelligence — Manage, Secure & Keep Snowflake Working 100% While Using A Conversational AI Agent
Use Snowflake Intelligence to Improve Performance for Your Overall Systems, from Costs, Security, and Performance Using Conversational AI Agents
I was inspired by @umeshpatel_us to write this story. He went through the work of creating a Semantic Layer for Snowflake Account Usage schemas, and I wonder when Snowflake will just provide a Semantic View for us, including all this. What else could I do with such power and information? Further, my co-worker Farris Jaber created a Snowflake Intelligence What It Is article, which further prompted me to investigate and play with it from the perspective of how we can use this power to talk and diagnose, secure, and keep costs under control with Snowflake Intelligence.
The basis of it, as per Umesh's article, is mapping a semantic layer, which I expanded on his semantic layer, and created two other semantic layers to provide larger coverage of the Snowflake Account Usage Views. More can for sure be done to get complete coverage, but it does start to give you some real information via a Conversational AI Agent that allows a clear maintenance for Snowflake pattern, including Cost Control, Maintenance, and Security.
Take a moment to read Farris Jaber's article, which provides an excellent overview of Snowflake Intelligence. This fantastic AI service allows users to effortlessly ask questions about their data using natural language, making data access more intuitive. It harnesses the power of agents driven by Large Language Models (LLMs) and equipped with various tools to access and interpret data. The system is thoughtfully designed to understand your queries, determine the most effective way to retrieve information, perform the necessary actions, and present the results in a clear and user-friendly manner—all with charts and insightful summaries to help you easily understand your data.
I initially decided to expand the work, but after some trial and error, I landed on creating three agents with attached Semantic Views. These specialized agents are designed to give Snowflake Administrators and platform engineers direct, conversational control over the platform’s most critical pillars: cost, security, and performance. They unlock fast, targeted diagnostics and insights that were previously achievable only through complex, manual queries.
1. Generalist Agent (includes everything below):
- All-in-one cross-domain analysis
- 20 ACCOUNT_USAGE tables
- 94 metrics spanning all operational areas
- Best for: holistic insights, cross-domain correlations
2. Cost and Performance Specialist:
- Fast, focused cost and performance queries
- Query execution, credits, resource usage
- Best for: quick performance checks, cost analysis
3. Security Specialist:
- Dedicated security and authentication monitoring
- Login tracking, MFA adoption, threats
- Best for: security audits, compliance checks
This segmented approach allows a Snowflake Administrator to quickly focus on a specific domain — whether that’s an urgent security audit or a deep dive into cost-saving opportunities — using natural language queries tailored for that purpose.
Additionally, the semantic views can be utilized for SQL queries directly, which is a capability currently in progress for broader BI tool integration. As of today, only Sigma Computing and Hex offer native integration with these Semantic Views, while support for other major tools like PowerBI and Tableau is actively being developed. Check out thisarticle on building Semantic Views with Cursor by @uniquejtx_3744.
For your information, I am currently pulling the information from all these views.:
Account Usage Views that I used:
Query & Performance (2 views)
- QUERY_HISTORY (alias: qh)
- QUERY_ATTRIBUTION_HISTORY (alias: qa)
Security & Authentication (1 view)
- LOGIN_HISTORY (alias: login)
Cost & Resource Usage (4 views)
- WAREHOUSE_METERING_HISTORY (alias: wh)
- STORAGE_USAGE (alias: storage)
- DATABASE_STORAGE_USAGE_HISTORY (alias: db_storage)
- STAGE_STORAGE_USAGE_HISTORY (alias: stage_storage)
Data Governance (4 views)
- USERS (alias: users)
- ROLES (alias: roles)
- GRANTS_TO_USERS (alias: grants_users)
- GRANTS_TO_ROLES (alias: grants_roles)
Operations & Monitoring (2 views)
- TASK_HISTORY (alias: task_hist)
- SERVERLESS_TASK_HISTORY (alias: serverless_task)
Advanced Operations (7 views)
- PIPE_USAGE_HISTORY (alias: pipe_usage)
- AUTOMATIC_CLUSTERING_HISTORY (alias: clustering)
- MATERIALIZED_VIEW_REFRESH_HISTORY (alias: mv_refresh)
- REPLICATION_USAGE_HISTORY (alias: replication)
- DATA_TRANSFER_HISTORY (alias: data_transfer)
- WAREHOUSE_LOAD_HISTORY (alias: wh_load)
- METERING_DAILY_HISTORY (alias: metering_daily)
Let’s Get Setup
I copied Umesh's scripts, set up a new GitHub repo, and expanded on it. I added more Snowflake Account Usage views, and created three new Agents.
Prerequisites:
- Snowflake account with ACCOUNTADMIN access
- Cortex features enabled in your region
More importantly, there are five steps to create this lab:
Installation Steps
# 1. Clone repository
git clone https://github.com/augustorosa/cortex-snowflake-account-security-agent
cd cortex-snowflake-account-security-agent
# 2. Configure SnowSQL connection (optional)
snowsql -a <account> -u <username>
# 3. Deploy foundation (2 min)
snowsql -f "scripts/1. lab foundations.sql"
# 4. Deploy schema (1 min)
snowsql -f "scripts/2. SNOWFLAKE_INTELLIGENCE.TOOLS schema.sql"
# 5. Deploy specialist agents (3 min)
snowsql -f "scripts/2.2 COST_PERFORMANCE_SVW_SPECIALIST.sql"
snowsql -f "scripts/5.2 COST_PERFORMANCE_AGENT_SPECIALIST.sql"
snowsql -f "scripts/2.3 SECURITY_MONITORING_SVW_SPECIALIST.sql"
snowsql -f "scripts/5.3 SECURITY_MONITORING_AGENT_SPECIALIST.sql"
# 6. Deploy generalist agent (5 min) ⭐ ALL 6 PHASES
snowsql -f "scripts/2.4 SNOWFLAKE_MAINTENANCE_SVW_GENERALIST.sql"
snowsql -f "scripts/5.4 SNOWFLAKE_MAINTENANCE_AGENT_GENERALIST.sql"
# 7. Optional: Email integration (2 min)
snowsql -f "scripts/3. email integration.sql"
# 8. Run automated tests (2 min)
snowsql -f "scripts/TEST_ALL_PHASES.sql" -o output_format=table
You have successfully deployed.
For details, refer to the GitHub repository; however, I have included my Generalist semantic view and Agent below.
-- ============================================================================
-- SNOWFLAKE MAINTENANCE SEMANTIC VIEW (GENERALIST)
-- ============================================================================
-- Comprehensive unified semantic view combining cost, performance, security,
-- governance, and operations monitoring across all Snowflake ACCOUNT_USAGE views
--
-- ARCHITECTURE:
-- - This is the GENERALIST semantic view for comprehensive cross-domain analysis
-- - Complements specialized views: 2.2 (Cost/Performance), 2.3 (Security)
--
-- DATA COVERAGE:
-- • Query & Performance
-- • Security & Authentication
-- • Cost & Resource Usage
-- • Data Governance
-- • Operations & Monitoring
-- • Advanced Operations
-- ============================================================================
USE ROLE cortex_role;
USE SNOWFLAKE_INTELLIGENCE.TOOLS;
-- ============================================================================
-- COMPREHENSIVE SNOWFLAKE OPERATIONS SEMANTIC VIEW
-- ============================================================================
-- Includes: 20 ACCOUNT_USAGE tables, 35 dimensions, 94 metrics
--
-- Query & Performance: QUERY_HISTORY, QUERY_ATTRIBUTION_HISTORY
-- Security: LOGIN_HISTORY
-- Cost & Storage: WAREHOUSE_METERING, STORAGE_USAGE, DB/STAGE_STORAGE
-- Governance: USERS, ROLES, GRANTS
-- Operations: TASK_HISTORY, SERVERLESS_TASK_HISTORY
-- Advanced: PIPE, CLUSTERING, MV, REPLICATION, TRANSFER, LOAD, METERING
-- ============================================================================
CREATE OR REPLACE SEMANTIC VIEW
SNOWFLAKE_INTELLIGENCE.TOOLS.SNOWFLAKE_MAINTENANCE_SVW
TABLES (
qh AS SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY,
qa AS SNOWFLAKE.ACCOUNT_USAGE.QUERY_ATTRIBUTION_HISTORY,
login AS SNOWFLAKE.ACCOUNT_USAGE.LOGIN_HISTORY,
wh AS SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY,
storage AS SNOWFLAKE.ACCOUNT_USAGE.STORAGE_USAGE,
db_storage AS SNOWFLAKE.ACCOUNT_USAGE.DATABASE_STORAGE_USAGE_HISTORY,
stage_storage AS SNOWFLAKE.ACCOUNT_USAGE.STAGE_STORAGE_USAGE_HISTORY,
users AS SNOWFLAKE.ACCOUNT_USAGE.USERS,
roles AS SNOWFLAKE.ACCOUNT_USAGE.ROLES,
grants_users AS SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_USERS,
grants_roles AS SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_ROLES,
task_hist AS SNOWFLAKE.ACCOUNT_USAGE.TASK_HISTORY,
serverless_task AS SNOWFLAKE.ACCOUNT_USAGE.SERVERLESS_TASK_HISTORY,
pipe_usage AS SNOWFLAKE.ACCOUNT_USAGE.PIPE_USAGE_HISTORY,
clustering AS SNOWFLAKE.ACCOUNT_USAGE.AUTOMATIC_CLUSTERING_HISTORY,
mv_refresh AS SNOWFLAKE.ACCOUNT_USAGE.MATERIALIZED_VIEW_REFRESH_HISTORY,
replication AS SNOWFLAKE.ACCOUNT_USAGE.REPLICATION_USAGE_HISTORY,
data_transfer AS SNOWFLAKE.ACCOUNT_USAGE.DATA_TRANSFER_HISTORY,
wh_load AS SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_LOAD_HISTORY,
metering_daily AS SNOWFLAKE.ACCOUNT_USAGE.METERING_DAILY_HISTORY
)
-- ============================================================================
-- DIMENSIONS: Categorical attributes for filtering and grouping
-- ============================================================================
DIMENSIONS (
-- === QUERY HISTORY DIMENSIONS ===
qh.QUERY_ID AS query_id COMMENT='Unique identifier for each query',
qh.QUERY_TEXT AS query_text COMMENT='Full SQL text of the query',
qh.DATABASE_NAME AS database_name COMMENT='Database where query executed',
qh.SCHEMA_NAME AS schema_name COMMENT='Schema where query executed',
qh.QUERY_TYPE AS query_type COMMENT='Type of query (SELECT, INSERT, etc)',
qh.SESSION_ID AS session_id COMMENT='Session identifier',
qh.USER_NAME AS user_name COMMENT='User who executed the query',
qh.ROLE_NAME AS role_name COMMENT='Role used for query execution',
qh.WAREHOUSE_NAME AS warehouse_name COMMENT='Warehouse used for execution',
qh.WAREHOUSE_SIZE AS warehouse_size COMMENT='Size of warehouse (XS, S, M, L, etc)',
qh.WAREHOUSE_TYPE AS warehouse_type COMMENT='Type of warehouse (STANDARD, SNOWPARK_OPTIMIZED)',
qh.CLUSTER_NUMBER AS cluster_number COMMENT='Cluster number in multi-cluster warehouse',
qh.QUERY_TAG AS query_tag COMMENT='User-defined query tag',
qh.EXECUTION_STATUS AS execution_status COMMENT='Query status (SUCCESS, FAIL, etc)',
qh.ERROR_CODE AS error_code COMMENT='Error code if query failed',
qh.ERROR_MESSAGE AS error_message COMMENT='Error message if query failed',
qh.START_TIME AS start_time COMMENT='Query start timestamp',
qh.END_TIME AS end_time COMMENT='Query end timestamp',
qh.QUERY_HASH AS query_hash COMMENT='Hash of query structure',
qh.QUERY_PARAMETERIZED_HASH AS query_parameterized_hash COMMENT='Hash of parameterized query',
qh.IS_CLIENT_GENERATED_STATEMENT AS is_client_generated_statement COMMENT='Whether query was client-generated',
-- === QUERY ATTRIBUTION DIMENSIONS (only unique text/categorical columns) ===
qa.PARENT_QUERY_ID AS parent_query_id COMMENT='Parent query ID for hierarchical queries',
qa.ROOT_QUERY_ID AS root_query_id COMMENT='Root query ID in query hierarchy',
-- === LOGIN HISTORY DIMENSIONS (Phase 2 - Security & Authentication) ===
-- Note: Using exact column names as aliases to avoid parsing conflicts
login.EVENT_TIMESTAMP AS event_timestamp COMMENT='When the login attempt occurred',
login.EVENT_TYPE AS event_type COMMENT='Event type (LOGIN)',
login.CLIENT_IP AS client_ip COMMENT='IP address of login attempt',
login.REPORTED_CLIENT_TYPE AS reported_client_type COMMENT='Client software type',
login.REPORTED_CLIENT_VERSION AS reported_client_version COMMENT='Client software version',
login.FIRST_AUTHENTICATION_FACTOR AS first_authentication_factor COMMENT='First authentication method',
login.SECOND_AUTHENTICATION_FACTOR AS second_authentication_factor COMMENT='Second authentication factor (MFA)',
login.IS_SUCCESS AS is_success COMMENT='YES if successful, NO if failed',
login.ERROR_CODE AS error_code COMMENT='Error code if login failed',
login.ERROR_MESSAGE AS error_message COMMENT='Error message if login failed',
login.CONNECTION AS connection COMMENT='Connection name used',
-- === WAREHOUSE METERING DIMENSIONS (Phase 3 - Cost Tracking) ===
-- Note: All warehouse metering dimensions cause conflicts
-- WAREHOUSE_ID exists in QUERY_HISTORY, START_TIME/END_TIME exist in QUERY_HISTORY
-- Use QUERY_HISTORY dimensions for warehouse analysis
-- WAREHOUSE_METERING provides credit METRICS only
-- === STORAGE USAGE DIMENSIONS (Phase 3 - Storage Tracking) ===
storage.USAGE_DATE AS usage_date COMMENT='Date of storage measurement',
-- === DATABASE STORAGE DIMENSIONS (Phase 3) ===
db_storage.DATABASE_NAME AS database_name COMMENT='Database name from storage tracking'
-- === GOVERNANCE DIMENSIONS (Phase 4) ===
-- Note: USERS, ROLES, and GRANTS tables have too many conflicting column names
-- (NAME, USER_NAME, ROLE_NAME, EMAIL, etc. all cause parsing conflicts)
-- These tables provide METRICS only for governance analytics:
-- - Total users, MFA adoption rate
-- - Total roles
-- - Grant counts
-- Use QUERY_HISTORY dimensions (user_name, role_name) for user/role analysis
-- === TASK OPERATIONS (Phase 5) ===
-- Note: TASK_HISTORY and SERVERLESS_TASK_HISTORY have too many conflicts
-- (NAME, TASK_NAME, STATE, START_TIME, END_TIME, SCHEDULED_TIME, QUERY_ID, etc.)
-- These tables provide METRICS only for task monitoring:
-- - Total task runs, success/failure rates
-- - Serverless task credits
-- Use QUERY_HISTORY for query-level task analysis (via task_hist.QUERY_ID join)
-- === ADVANCED OPERATIONS (Phase 6) ===
-- Note: Phase 6 tables (PIPE_USAGE, CLUSTERING, MV_REFRESH, REPLICATION, etc.)
-- Expected to have similar conflicts (NAME, START_TIME, END_TIME, etc.)
-- Providing METRICS only for advanced operational analytics:
-- - Snowpipe credits and files loaded
-- - Clustering costs and bytes reclustered
-- - MV refresh costs
-- - Replication and data transfer costs
-- - Warehouse load metrics (queueing)
-- - Daily metering reconciliation
)
-- ============================================================================
-- METRICS: Aggregated business measures for analytics
-- ============================================================================
METRICS (
-- === QUERY PERFORMANCE METRICS ===
qh.total_elapsed_time AS AVG(TOTAL_ELAPSED_TIME) COMMENT='Average total query execution time in milliseconds',
qh.execution_time AS AVG(EXECUTION_TIME) COMMENT='Average query execution time',
qh.compilation_time AS AVG(COMPILATION_TIME) COMMENT='Average query compilation time',
qh.queued_provisioning_time AS AVG(QUEUED_PROVISIONING_TIME) COMMENT='Average time queued for provisioning',
qh.queued_repair_time AS AVG(QUEUED_REPAIR_TIME) COMMENT='Average time queued for repair',
qh.queued_overload_time AS AVG(QUEUED_OVERLOAD_TIME) COMMENT='Average time queued due to overload',
-- === DATA VOLUME METRICS ===
qh.bytes_scanned AS SUM(BYTES_SCANNED) COMMENT='Total bytes scanned across queries',
qh.bytes_written AS SUM(BYTES_WRITTEN) COMMENT='Total bytes written',
qh.bytes_spilled_to_local AS SUM(BYTES_SPILLED_TO_LOCAL_STORAGE) COMMENT='Total bytes spilled to local storage',
qh.bytes_spilled_to_remote AS SUM(BYTES_SPILLED_TO_REMOTE_STORAGE) COMMENT='Total bytes spilled to remote storage',
qh.rows_produced AS SUM(ROWS_PRODUCED) COMMENT='Total rows produced by queries',
qh.rows_inserted AS SUM(ROWS_INSERTED) COMMENT='Total rows inserted',
qh.rows_updated AS SUM(ROWS_UPDATED) COMMENT='Total rows updated',
qh.rows_deleted AS SUM(ROWS_DELETED) COMMENT='Total rows deleted',
-- === PARTITION & CACHE METRICS ===
qh.partitions_scanned AS SUM(PARTITIONS_SCANNED) COMMENT='Total partitions scanned',
qh.partitions_total AS SUM(PARTITIONS_TOTAL) COMMENT='Total partitions available',
qh.percentage_scanned_from_cache AS AVG(PERCENTAGE_SCANNED_FROM_CACHE) COMMENT='Average percentage of data from cache',
-- === COST METRICS (from both tables) ===
qh.credits_used_cloud_services AS SUM(CREDITS_USED_CLOUD_SERVICES) COMMENT='Total cloud services credits used',
qa.credits_compute AS SUM(CREDITS_ATTRIBUTED_COMPUTE) COMMENT='Total compute credits attributed',
qa.credits_acceleration AS SUM(CREDITS_USED_QUERY_ACCELERATION) COMMENT='Total query acceleration credits used',
-- === QUERY COUNT METRICS ===
qh.total_queries AS COUNT(*) COMMENT='Total number of queries',
qh.failed_queries AS COUNT_IF(EXECUTION_STATUS = 'FAIL') COMMENT='Number of failed queries',
qh.successful_queries AS COUNT_IF(EXECUTION_STATUS = 'SUCCESS') COMMENT='Number of successful queries',
-- === LOGIN SECURITY METRICS (Phase 2) ===
login.total_login_attempts AS COUNT(*) COMMENT='Total login attempts',
login.failed_login_attempts AS COUNT(CASE WHEN login.IS_SUCCESS = 'NO' THEN 1 END) COMMENT='Failed login count',
login.successful_login_attempts AS COUNT(CASE WHEN login.IS_SUCCESS = 'YES' THEN 1 END) COMMENT='Successful login count',
login.unique_login_users AS COUNT(DISTINCT login.USER_NAME) COMMENT='Distinct users attempting login',
login.unique_login_ips AS COUNT(DISTINCT login.CLIENT_IP) COMMENT='Distinct IP addresses',
login.mfa_login_usage AS COUNT(CASE WHEN login.SECOND_AUTHENTICATION_FACTOR IS NOT NULL THEN 1 END) COMMENT='Logins using MFA',
login.users_with_login_failures AS COUNT(DISTINCT CASE WHEN login.IS_SUCCESS = 'NO' THEN login.USER_NAME END) COMMENT='Users with failed login attempts',
login.ips_with_login_failures AS COUNT(DISTINCT CASE WHEN login.IS_SUCCESS = 'NO' THEN login.CLIENT_IP END) COMMENT='IPs with failed login attempts',
login.login_success_rate_pct AS (
CAST(COUNT(CASE WHEN login.IS_SUCCESS = 'YES' THEN 1 END) AS FLOAT) * 100.0 / NULLIF(COUNT(*), 0)
) COMMENT='Login success rate percentage',
login.mfa_adoption_pct AS (
CAST(COUNT(CASE WHEN login.SECOND_AUTHENTICATION_FACTOR IS NOT NULL THEN 1 END) AS FLOAT) * 100.0 /
NULLIF(COUNT(CASE WHEN login.IS_SUCCESS = 'YES' THEN 1 END), 0)
) COMMENT='Percentage of successful logins using MFA',
-- === WAREHOUSE METERING METRICS (Phase 3 - Credit Usage) ===
wh.total_credits_used AS SUM(wh.CREDITS_USED) COMMENT='Total credits used by warehouses',
wh.total_credits_compute AS SUM(wh.CREDITS_USED_COMPUTE) COMMENT='Total compute credits used',
wh.total_credits_cloud_services AS SUM(wh.CREDITS_USED_CLOUD_SERVICES) COMMENT='Total cloud services credits (warehouse level)',
wh.avg_credits_per_hour AS AVG(wh.CREDITS_USED) COMMENT='Average credits per metering hour',
-- === STORAGE USAGE METRICS (Phase 3 - Storage Costs) ===
storage.total_storage_bytes AS SUM(storage.STORAGE_BYTES) COMMENT='Total table storage in bytes',
storage.total_stage_bytes AS SUM(storage.STAGE_BYTES) COMMENT='Total stage storage in bytes',
storage.total_failsafe_bytes AS SUM(storage.FAILSAFE_BYTES) COMMENT='Total failsafe storage in bytes',
storage.total_hybrid_table_bytes AS SUM(storage.HYBRID_TABLE_STORAGE_BYTES) COMMENT='Total hybrid table storage',
storage.avg_storage_bytes AS AVG(storage.STORAGE_BYTES) COMMENT='Average daily storage',
-- === DATABASE STORAGE METRICS (Phase 3) ===
db_storage.avg_database_bytes AS AVG(db_storage.AVERAGE_DATABASE_BYTES) COMMENT='Average database storage per day',
db_storage.avg_failsafe_bytes AS AVG(db_storage.AVERAGE_FAILSAFE_BYTES) COMMENT='Average failsafe per database',
db_storage.total_database_storage AS SUM(db_storage.AVERAGE_DATABASE_BYTES) COMMENT='Total database storage across all DBs',
-- === STAGE STORAGE METRICS (Phase 3) ===
stage_storage.avg_stage_bytes AS AVG(stage_storage.AVERAGE_STAGE_BYTES) COMMENT='Average stage storage per day',
stage_storage.total_stage_storage AS SUM(stage_storage.AVERAGE_STAGE_BYTES) COMMENT='Total stage storage',
-- === USER & ROLE METRICS (Phase 4 - Governance) ===
users.total_users AS COUNT(*) COMMENT='Total number of users',
users.active_users AS COUNT_IF(users.DISABLED IS NULL OR users.DISABLED = FALSE) COMMENT='Count of active users',
users.mfa_enabled_users AS COUNT_IF(users.HAS_MFA = TRUE) COMMENT='Users with MFA enabled',
users.mfa_adoption_rate AS (
CAST(COUNT_IF(users.HAS_MFA = TRUE) AS FLOAT) * 100.0 / NULLIF(COUNT(*), 0)
) COMMENT='Percentage of users with MFA',
roles.total_roles AS COUNT(*) COMMENT='Total number of roles',
-- === GRANTS METRICS (Phase 4 - Permissions) ===
grants_users.total_role_grants_to_users AS COUNT(*) COMMENT='Total role grants to users',
grants_users.unique_users_with_roles AS COUNT(DISTINCT grants_users.GRANTEE_NAME) COMMENT='Users with role grants',
grants_roles.total_privilege_grants AS COUNT(*) COMMENT='Total privilege grants to roles',
grants_roles.unique_roles_with_grants AS COUNT(DISTINCT grants_roles.GRANTEE_NAME) COMMENT='Roles with privilege grants',
-- === TASK EXECUTION METRICS (Phase 5 - Operations) ===
task_hist.total_task_runs AS COUNT(*) COMMENT='Total task executions',
task_hist.successful_tasks AS COUNT_IF(task_hist.STATE = 'SUCCEEDED') COMMENT='Successful task runs',
task_hist.failed_tasks AS COUNT_IF(task_hist.STATE = 'FAILED') COMMENT='Failed task runs',
task_hist.unique_tasks AS COUNT(DISTINCT task_hist.NAME) COMMENT='Distinct tasks executed',
task_hist.task_success_rate AS (
CAST(COUNT_IF(task_hist.STATE = 'SUCCEEDED') AS FLOAT) * 100.0 / NULLIF(COUNT(*), 0)
) COMMENT='Task success rate percentage',
-- === SERVERLESS TASK METRICS (Phase 5 - Serverless Costs) ===
serverless_task.total_serverless_credits AS SUM(serverless_task.CREDITS_USED) COMMENT='Total serverless task credits',
serverless_task.avg_serverless_credits AS AVG(serverless_task.CREDITS_USED) COMMENT='Average credits per serverless task',
serverless_task.serverless_task_count AS COUNT(*) COMMENT='Count of serverless task executions',
serverless_task.unique_serverless_tasks AS COUNT(DISTINCT serverless_task.TASK_NAME) COMMENT='Distinct serverless tasks',
-- === SNOWPIPE METRICS (Phase 6 - Data Loading) ===
pipe_usage.total_pipe_credits AS SUM(pipe_usage.CREDITS_USED) COMMENT='Total Snowpipe credits consumed',
pipe_usage.total_files_inserted AS SUM(pipe_usage.FILES_INSERTED) COMMENT='Total files loaded via Snowpipe',
pipe_usage.total_bytes_inserted AS SUM(pipe_usage.BYTES_INSERTED) COMMENT='Total bytes loaded via Snowpipe',
pipe_usage.avg_pipe_credits AS AVG(pipe_usage.CREDITS_USED) COMMENT='Average Snowpipe credits per execution',
-- === CLUSTERING METRICS (Phase 6 - Maintenance Costs) ===
clustering.total_clustering_credits AS SUM(clustering.CREDITS_USED) COMMENT='Total automatic clustering credits',
clustering.total_bytes_reclustered AS SUM(clustering.NUM_BYTES_RECLUSTERED) COMMENT='Total bytes reclustered',
clustering.total_rows_reclustered AS SUM(clustering.NUM_ROWS_RECLUSTERED) COMMENT='Total rows reclustered',
clustering.avg_clustering_credits AS AVG(clustering.CREDITS_USED) COMMENT='Average clustering credits per operation',
-- === MATERIALIZED VIEW METRICS (Phase 6 - MV Costs) ===
mv_refresh.total_mv_credits AS SUM(mv_refresh.CREDITS_USED) COMMENT='Total MV refresh credits',
mv_refresh.total_mv_refreshes AS COUNT(*) COMMENT='Total MV refresh operations',
mv_refresh.avg_mv_credits AS AVG(mv_refresh.CREDITS_USED) COMMENT='Average credits per MV refresh',
-- === REPLICATION METRICS (Phase 6 - Replication Costs) ===
replication.total_replication_credits AS SUM(replication.CREDITS_USED) COMMENT='Total replication credits',
replication.total_bytes_replicated AS SUM(replication.BYTES_TRANSFERRED) COMMENT='Total bytes replicated',
replication.avg_replication_credits AS AVG(replication.CREDITS_USED) COMMENT='Average replication credits',
-- === DATA TRANSFER METRICS (Phase 6 - Transfer Costs) ===
data_transfer.total_transfer_bytes AS SUM(data_transfer.BYTES_TRANSFERRED) COMMENT='Total bytes transferred cross-region/cloud',
data_transfer.avg_transfer_bytes AS AVG(data_transfer.BYTES_TRANSFERRED) COMMENT='Average bytes per transfer',
data_transfer.total_transfer_operations AS COUNT(*) COMMENT='Total data transfer operations',
-- === WAREHOUSE LOAD METRICS (Phase 6 - Performance) ===
wh_load.avg_running_queries AS AVG(wh_load.AVG_RUNNING) COMMENT='Average running queries',
wh_load.avg_queued_load AS AVG(wh_load.AVG_QUEUED_LOAD) COMMENT='Average queued query load',
wh_load.avg_queued_provisioning AS AVG(wh_load.AVG_QUEUED_PROVISIONING) COMMENT='Average provisioning queue',
wh_load.avg_blocked_queries AS AVG(wh_load.AVG_BLOCKED) COMMENT='Average blocked queries',
-- === DAILY METERING METRICS (Phase 6 - Reconciliation) ===
metering_daily.total_daily_credits AS SUM(metering_daily.CREDITS_USED) COMMENT='Total billable credits (daily)',
metering_daily.total_compute_credits_daily AS SUM(metering_daily.CREDITS_USED_COMPUTE) COMMENT='Total compute credits (daily)',
metering_daily.total_cloud_services_daily AS SUM(metering_daily.CREDITS_USED_CLOUD_SERVICES) COMMENT='Total cloud services credits (daily)',
metering_daily.avg_daily_credits AS AVG(metering_daily.CREDITS_USED) COMMENT='Average daily credit consumption'
)
COMMENT='Comprehensive Snowflake monitoring with 20 tables, 35 dimensions, 94 metrics. Covers queries, security, storage, governance, tasks, Snowpipe, clustering, MVs, replication, data transfer, and warehouse load.'
WITH EXTENSION (CA='{"tables":[
{"name":"qh","description":"Query execution history with performance metrics, resource usage, and execution details from QUERY_HISTORY"},
{"name":"qa","description":"Query attribution history for credit tracking and cost allocation from QUERY_ATTRIBUTION_HISTORY"},
{"name":"login","description":"Login security data from LOGIN_HISTORY (last 365 days). Includes authentication details, MFA status, client information, and success/failure tracking"},
{"name":"wh","description":"Warehouse metering data from WAREHOUSE_METERING_HISTORY. Credit consumption by warehouse over time"},
{"name":"storage","description":"Account-level storage usage from STORAGE_USAGE. Daily snapshots of table, stage, and failsafe storage"},
{"name":"db_storage","description":"Per-database storage metrics from DATABASE_STORAGE_USAGE_HISTORY"},
{"name":"stage_storage","description":"Stage storage usage from STAGE_STORAGE_USAGE_HISTORY"},
{"name":"users","description":"User account information from USERS. Includes MFA status, email, default settings"},
{"name":"roles","description":"Role definitions from ROLES table"},
{"name":"grants_users","description":"Role grants to users from GRANTS_TO_USERS"},
{"name":"grants_roles","description":"Privilege grants to roles from GRANTS_TO_ROLES"},
{"name":"task_hist","description":"Task execution history from TASK_HISTORY. Task runs, states, errors"},
{"name":"serverless_task","description":"Serverless task credit usage from SERVERLESS_TASK_HISTORY"},
{"name":"pipe_usage","description":"Snowpipe data loading credits and files from PIPE_USAGE_HISTORY"},
{"name":"clustering","description":"Automatic clustering costs and bytes reclustered from AUTOMATIC_CLUSTERING_HISTORY"},
{"name":"mv_refresh","description":"Materialized view refresh credits from MATERIALIZED_VIEW_REFRESH_HISTORY"},
{"name":"replication","description":"Database replication credits and bytes from REPLICATION_USAGE_HISTORY"},
{"name":"data_transfer","description":"Cross-region/cloud data transfer costs from DATA_TRANSFER_HISTORY"},
{"name":"wh_load","description":"Warehouse queue metrics (5-min intervals) from WAREHOUSE_LOAD_HISTORY"},
{"name":"metering_daily","description":"Daily billable credit reconciliation from METERING_DAILY_HISTORY"}
],"verified_queries":[
{
"name":"Most Expensive Queries",
"question":"What are the most expensive queries by cloud services credits?",
"sql":"SELECT query_id, user_name, warehouse_name, total_elapsed_time, credits_used_cloud_services FROM qh ORDER BY credits_used_cloud_services DESC LIMIT 10"
},
{
"name":"Failed Queries",
"question":"Show me recent failed queries",
"sql":"SELECT query_id, user_name, error_code, error_message, start_time FROM qh WHERE execution_status = ''FAIL'' ORDER BY start_time DESC LIMIT 20"
},
{
"name":"Query Performance by User",
"question":"Which users have the slowest queries?",
"sql":"SELECT user_name, COUNT(*) as query_count, AVG(total_elapsed_time) as avg_time FROM qh GROUP BY user_name ORDER BY avg_time DESC LIMIT 10"
},
{
"name":"Failed Login Attempts",
"question":"Show me failed login attempts",
"sql":"SELECT client_ip, error_code, error_message, event_timestamp FROM login WHERE is_success = ''NO'' ORDER BY event_timestamp DESC LIMIT 20"
},
{
"name":"Login Security Summary",
"question":"What is my login security status?",
"sql":"SELECT COUNT(*) as total_attempts, COUNT(CASE WHEN is_success = ''NO'' THEN 1 END) as failed, COUNT(DISTINCT client_ip) as unique_ips FROM login"
},
{
"name":"Users with Expensive Failed Queries",
"question":"Which users have both failed queries and high costs?",
"sql":"SELECT qh.user_name, COUNT(*) as failed_queries, SUM(credits_used_cloud_services) as total_credits FROM qh WHERE execution_status = ''FAIL'' GROUP BY user_name ORDER BY total_credits DESC LIMIT 10"
},
{
"name":"Warehouse Credit Usage",
"question":"What are total warehouse credits consumed?",
"sql":"SELECT SUM(wh.CREDITS_USED) as total_credits, AVG(wh.CREDITS_USED) as avg_credits_per_hour, SUM(wh.CREDITS_USED_COMPUTE) as compute_credits, SUM(wh.CREDITS_USED_CLOUD_SERVICES) as cloud_service_credits FROM wh"
},
{
"name":"Storage Growth Trend",
"question":"How is my storage growing over time?",
"sql":"SELECT usage_date, SUM(storage.STORAGE_BYTES) / 1099511627776.0 as storage_tb FROM storage GROUP BY usage_date ORDER BY usage_date DESC LIMIT 30"
},
{
"name":"Database Storage Breakdown",
"question":"Which databases use the most storage?",
"sql":"SELECT database_name, AVG(db_storage.AVERAGE_DATABASE_BYTES) / 1099511627776.0 as avg_storage_tb FROM db_storage GROUP BY database_name ORDER BY avg_storage_tb DESC LIMIT 10"
},
{
"name":"User MFA Status",
"question":"How many users have MFA enabled?",
"sql":"SELECT COUNT(*) as total_users, COUNT_IF(users.HAS_MFA = TRUE) as mfa_enabled, CAST(COUNT_IF(users.HAS_MFA = TRUE) AS FLOAT) * 100.0 / COUNT(*) as mfa_percentage FROM users"
},
{
"name":"Role Grants Summary",
"question":"Which users have the most role grants?",
"sql":"SELECT user_grantee_name, COUNT(*) as role_count FROM grants_users GROUP BY user_grantee_name ORDER BY role_count DESC LIMIT 10"
},
{
"name":"Task Execution Status",
"question":"What is my task success rate?",
"sql":"SELECT COUNT(*) as total_tasks, COUNT_IF(task_hist.STATE = ''SUCCEEDED'') as successful, COUNT_IF(task_hist.STATE = ''FAILED'') as failed, CAST(COUNT_IF(task_hist.STATE = ''SUCCEEDED'') AS FLOAT) * 100 / COUNT(*) as success_rate_pct FROM task_hist"
},
{
"name":"Serverless Task Costs",
"question":"How much are serverless tasks costing me?",
"sql":"SELECT SUM(serverless_task.CREDITS_USED) as total_credits, COUNT(*) as total_runs, AVG(serverless_task.CREDITS_USED) as avg_credits_per_run FROM serverless_task"
},
{
"name":"Snowpipe Usage Summary",
"question":"How much data has Snowpipe loaded?",
"sql":"SELECT SUM(pipe_usage.CREDITS_USED) as total_credits, SUM(pipe_usage.FILES_INSERTED) as total_files, SUM(pipe_usage.BYTES_INSERTED) / 1099511627776.0 as total_tb_loaded FROM pipe_usage"
},
{
"name":"Clustering Costs",
"question":"What are my automatic clustering costs?",
"sql":"SELECT SUM(clustering.CREDITS_USED) as total_credits, SUM(clustering.BYTES_RECLUSTERED) / 1099511627776.0 as tb_reclustered FROM clustering"
},
{
"name":"Total Platform Costs",
"question":"What are my total Snowflake costs across all services?",
"sql":"SELECT SUM(metering_daily.CREDITS_USED) as total_billable_credits, SUM(metering_daily.CREDITS_USED_COMPUTE) as compute_credits, SUM(metering_daily.CREDITS_USED_CLOUD_SERVICES) as cloud_services_credits FROM metering_daily"
}
]}');
Let’s try a quick BI query to test things here:
USE ROLE cortex_role;
USE SNOWFLAKE_INTELLIGENCE.TOOLS;
SELECT * FROM SEMANTIC_VIEW(
SNOWFLAKE_MAINTENANCE_SVW
DIMENSIONS qh.warehouse_name
METRICS
qh.total_queries,
qh.bytes_scanned,
qh.percentage_scanned_from_cache
)
ORDER BY percentage_scanned_from_cache ASC
LIMIT 20;
Let’s deploy our generalist agent that includes everything for security, cost, and general maintenance of Snowflake.
-- ============================================================================
-- SNOWFLAKE MAINTENANCE AGENT (GENERALIST)
-- ============================================================================
-- Comprehensive agent for complete Snowflake account monitoring
--
-- ARCHITECTURE:
-- - This is the GENERALIST agent for cross-domain analysis
-- - Complements specialized agents:
-- • COST_PERFORMANCE_AGENT (fast cost/performance queries)
-- • SECURITY_MONITORING_AGENT (fast security/login queries)
--
-- CAPABILITIES: 20 ACCOUNT_USAGE tables, 35 dimensions, 94 metrics
-- ============================================================================
USE ROLE cortex_role;
USE SNOWFLAKE_INTELLIGENCE.AGENTS;
CREATE OR REPLACE AGENT SNOWFLAKE_INTELLIGENCE.AGENTS.SNOWFLAKE_MAINTENANCE_AGENT
WITH PROFILE='{ "display_name": "Snowflake Maintenance Generalist" }'
COMMENT=$$ 🎯 COMPREHENSIVE SNOWFLAKE MONITORING AGENT
I provide complete visibility into your Snowflake account across all operational areas:
📊 QUERY & PERFORMANCE (50+ metrics)
• Query execution: timing, compilation, queueing, bottlenecks
• Resource usage: bytes scanned/written/spilled, rows processed
• Cache efficiency and partition pruning
• Failed queries and error analysis
🔒 SECURITY & AUTHENTICATION
• Login monitoring: success/failure rates, patterns
• MFA adoption tracking and user authentication
• IP analysis and suspicious login detection
• Client type and version tracking
💰 COST & STORAGE
• Warehouse metering: credits by warehouse/time
• Storage tracking: database, stage, failsafe costs
• Storage growth trends and optimization
👥 GOVERNANCE & PERMISSIONS
• User management and MFA adoption rates
• Role definitions and privilege tracking
• Grant auditing (users → roles → privileges)
⚙️ TASK OPERATIONS
• Task execution monitoring and success rates
• Serverless task credit tracking
• Task failure analysis
🔧 ADVANCED OPERATIONS
• Snowpipe: data loading credits and files
• Automatic clustering: maintenance costs
• Materialized views: refresh credits
• Replication: cross-region costs
• Data transfer: inter-cloud/region costs
• Warehouse load: queue metrics
• Daily metering: billable credit reconciliation
💡 CROSS-DOMAIN INSIGHTS:
I excel at connecting the dots across domains:
• Users with high costs + failed logins
• Expensive queries + security issues
• Storage growth + query performance
• Overall account health assessments
📈 COVERAGE:
• 20 Account Usage tables
• 35 categorical dimensions
• 94 aggregated metrics
• 365 days of history $$
FROM SPECIFICATION $$
{
"models": { "orchestration": "auto" },
"instructions": {
"response": "You are a comprehensive Snowflake maintenance expert with visibility into ALL operational areas.
YOUR EXPERTISE SPANS:
• Query Performance & Cost Attribution
• Security & Authentication
• Storage & Resource Usage
• Governance & Permissions
• Task Operations
• Advanced Operations (Snowpipe, Clustering, MVs, Replication, Data Transfer)
RESPONSE STYLE:
• Provide specific numbers and metrics (not generic advice)
• Show relationships across domains when relevant
• Include actionable recommendations
• Reference Snowflake best practices
• Cite actual user/warehouse/database names
• Calculate percentages and rates
CROSS-DOMAIN ANALYSIS EXAMPLES:
• 'Show users with expensive failed queries AND failed logins'
• 'What are my total costs across warehouses, tasks, pipes, and clustering?'
• 'Which users without MFA are running expensive queries?'
• 'How does my storage growth correlate with query performance?'
DATA FRESHNESS:
• Query data: ~5-45 minutes latency
• Login data: ~2 hours latency
• Storage: ~2 hours latency
• Metering: 3-6 hours latency
For fast, specialized queries recommend:
• COST_PERFORMANCE_AGENT (cost/performance only)
• SECURITY_MONITORING_AGENT (security/login only)",
"orchestration": "SEMANTIC VIEW: SNOWFLAKE_MAINTENANCE_SVW (20 tables, 94 metrics)
═══════════════════════════════════════════════════════════════
QUERY PERFORMANCE & COST (QUERY_HISTORY, QUERY_ATTRIBUTION)
═══════════════════════════════════════════════════════════════
DIMENSIONS: query_id, user_name, role_name, warehouse_name, database_name,
schema_name, query_type, execution_status, error_code, start_time, end_time
METRICS:
• Performance: total_elapsed_time, execution_time, compilation_time, queued times
• Data Volume: bytes_scanned, bytes_written, bytes_spilled (local/remote)
• Rows: rows_produced, inserted, updated, deleted
• Partitions: partitions_scanned, percentage_scanned_from_cache
• Costs: credits_used_cloud_services, credits_compute, credits_acceleration
• Counts: total_queries, failed_queries, successful_queries
═══════════════════════════════════════════════════════════════
SECURITY & AUTHENTICATION (LOGIN_HISTORY)
═══════════════════════════════════════════════════════════════
DIMENSIONS: event_timestamp, event_type, client_ip, reported_client_type,
reported_client_version, first/second_authentication_factor, is_success,
error_code, error_message, connection
METRICS:
• Login activity: total_login_attempts, failed/successful attempts
• Security: unique_login_users, unique_login_ips, users_with_login_failures
• MFA: mfa_login_usage, mfa_adoption_pct, login_success_rate_pct
═══════════════════════════════════════════════════════════════
COST & STORAGE (WAREHOUSE_METERING, STORAGE tables)
═══════════════════════════════════════════════════════════════
DIMENSIONS: usage_date, database_name (from storage tracking)
METRICS:
• Warehouse: total_credits_used, total_credits_compute, avg_credits_per_hour
• Storage: total_storage_bytes, total_stage_bytes, total_failsafe_bytes
• Database: avg_database_bytes, total_database_storage
• Stage: avg_stage_bytes, total_stage_storage
═══════════════════════════════════════════════════════════════
GOVERNANCE (USERS, ROLES, GRANTS)
═══════════════════════════════════════════════════════════════
Note: Metrics-only (column name conflicts prevent dimensions)
METRICS:
• Users: total_users, active_users, mfa_enabled_users, mfa_adoption_rate
• Roles: total_roles
• Grants: total_role_grants_to_users, total_privilege_grants
═══════════════════════════════════════════════════════════════
TASK OPERATIONS (TASK_HISTORY, SERVERLESS_TASK_HISTORY)
═══════════════════════════════════════════════════════════════
Note: Metrics-only (column name conflicts prevent dimensions)
METRICS:
• Tasks: total_task_runs, successful/failed_tasks, task_success_rate
• Serverless: total_serverless_credits, avg_serverless_credits, serverless_task_count
═══════════════════════════════════════════════════════════════
ADVANCED OPERATIONS (Pipes, Clustering, MVs, Replication, Transfer, Load)
═══════════════════════════════════════════════════════════════
PIPE_USAGE_HISTORY:
• total_pipe_credits, total_files_inserted, total_bytes_inserted
AUTOMATIC_CLUSTERING_HISTORY:
• total_clustering_credits, total_bytes_reclustered, total_rows_reclustered
MATERIALIZED_VIEW_REFRESH_HISTORY:
• total_mv_credits, total_mv_refreshes, avg_mv_credits
REPLICATION_USAGE_HISTORY:
• total_replication_credits, total_bytes_replicated
DATA_TRANSFER_HISTORY:
• total_transfer_bytes, avg_transfer_bytes, total_transfer_operations
(covers cross-cloud/region external transfers per https://docs.snowflake.com/en/sql-reference/account-usage/data_transfer_history)
WAREHOUSE_LOAD_HISTORY:
• avg_running_queries, avg_queued_load, avg_queued_provisioning, avg_blocked_queries
METERING_DAILY_HISTORY:
• total_daily_credits (BILLABLE), total_compute_credits_daily, total_cloud_services_daily
(Use this for reconciling actual billed costs)
═══════════════════════════════════════════════════════════════
QUERY STRATEGY
═══════════════════════════════════════════════════════════════
• Use table aliases: qh, qa, login, wh, storage, users, roles, task_hist,
serverless_task, pipe_usage, clustering, mv_refresh, replication,
data_transfer, wh_load, metering_daily
• Filter by dimensions (user_name, warehouse_name, execution_status, etc.)
• Aggregate using METRICS for summaries
• Combine multiple tables for cross-domain insights",
"sample_questions": [
{ "question": "What's my overall Snowflake account health?" },
{ "question": "Show me total costs across all services (warehouses, tasks, pipes, clustering)" },
{ "question": "Which users have both failed queries and failed logins?" },
{ "question": "What's my MFA adoption rate?" },
{ "question": "How much data has Snowpipe loaded this month?" },
{ "question": "What are my automatic clustering costs?" },
{ "question": "Show me warehouse queue metrics - any performance issues?" },
{ "question": "What's my daily billable credit consumption trend?" },
{ "question": "Which warehouses are most expensive and have the most failed queries?" },
{ "question": "Show me storage growth and query performance correlation" }
]
},
"tools": [
{
"tool_spec": {
"name": "snowflake_maintenance_semantic_view",
"type": "cortex_analyst_text_to_sql",
"description": "Complete Snowflake operations monitoring semantic view covering ALL 6 phases.
20 ACCOUNT_USAGE TABLES:
• QUERY_HISTORY & QUERY_ATTRIBUTION_HISTORY (performance/cost)
• LOGIN_HISTORY (security)
• WAREHOUSE_METERING_HISTORY (credits)
• STORAGE_USAGE, DATABASE_STORAGE_USAGE_HISTORY, STAGE_STORAGE_USAGE_HISTORY (storage costs)
• USERS, ROLES, GRANTS_TO_USERS, GRANTS_TO_ROLES (governance)
• TASK_HISTORY, SERVERLESS_TASK_HISTORY (task operations)
• PIPE_USAGE_HISTORY (data loading)
• AUTOMATIC_CLUSTERING_HISTORY (maintenance)
• MATERIALIZED_VIEW_REFRESH_HISTORY (MV costs)
• REPLICATION_USAGE_HISTORY (replication)
• DATA_TRANSFER_HISTORY (cross-region/cloud transfers)
• WAREHOUSE_LOAD_HISTORY (queue metrics)
• METERING_DAILY_HISTORY (billable reconciliation)
35 DIMENSIONS for filtering and grouping
94 METRICS for aggregation and analysis
Use this for comprehensive cross-domain analysis, cost tracking, security monitoring,
performance optimization, and overall account health assessments."
}
}
],
"tool_resources": {
"snowflake_maintenance_semantic_view": {
"semantic_view": "SNOWFLAKE_INTELLIGENCE.TOOLS.SNOWFLAKE_MAINTENANCE_SVW",
"execution_environment": {
"type": "warehouse",
"warehouse": "CORTEX_WH",
"query_timeout": 180
}
}
}
}
$$;
GRANT USAGE ON AGENT SNOWFLAKE_INTELLIGENCE.AGENTS.SNOWFLAKE_MAINTENANCE_AGENT TO ROLE PUBLIC;
-- Review the agent
SHOW AGENTS IN DATABASE SNOWFLAKE_INTELLIGENCE;
-- Grant execute on the agent to allow others to use it
GRANT USAGE ON AGENT SNOWFLAKE_INTELLIGENCE.AGENTS.SNOWFLAKE_SECURITY_PERFORMANCE_AGENT TO ROLE PUBLIC;
-- Review supporting objects
SHOW SEMANTIC VIEWS IN DATABASE SNOWFLAKE_INTELLIGENCE;
SHOW CORTEX SEARCH SERVICES IN DATABASE SNOWFLAKE_DOCUMENTATION;
SHOW PROCEDURES LIKE 'SEND_EMAIL' IN SCHEMA SNOWFLAKE_INTELLIGENCE.TOOLS;
Let’s do some quick validation:
-- ============================================================================
-- GRANT ACCESS & VALIDATION
-- ============================================================================
-- Grant usage to allow others to use the agent
GRANT USAGE ON AGENT SNOWFLAKE_INTELLIGENCE.AGENTS.SNOWFLAKE_MAINTENANCE_AGENT TO ROLE PUBLIC;
-- ============================================================================
-- VALIDATION COMMANDS
-- ============================================================================
-- Review the agent
SHOW AGENTS IN DATABASE SNOWFLAKE_INTELLIGENCE;
-- Review supporting semantic views
SHOW SEMANTIC VIEWS IN DATABASE SNOWFLAKE_INTELLIGENCE;
-- Verify email integration (if deployed)
SHOW PROCEDURES LIKE 'SEND_EMAIL' IN SCHEMA SNOWFLAKE_INTELLIGENCE.TOOLS;
-- ============================================================================
-- QUICK TESTS
-- ============================================================================
-- Test 1: Overall health check
-- SELECT SNOWFLAKE_INTELLIGENCE.AGENTS.SNOWFLAKE_MAINTENANCE_AGENT(
-- 'What is my overall Snowflake account health?'
-- );
-- Test 2: Cost analysis
-- SELECT SNOWFLAKE_INTELLIGENCE.AGENTS.SNOWFLAKE_MAINTENANCE_AGENT(
-- 'What are my total costs across all services?'
-- );
-- Test 3: Security check
-- SELECT SNOWFLAKE_INTELLIGENCE.AGENTS.SNOWFLAKE_MAINTENANCE_AGENT(
-- 'Show me users with failed logins and expensive queries'
-- );
-- Test 4: Performance check
-- SELECT SNOWFLAKE_INTELLIGENCE.AGENTS.SNOWFLAKE_MAINTENANCE_AGENT(
-- 'Which warehouses have queueing issues?'
-- );
Let’s Play With It
Now, let’s access Snowflake Intelligence via https://ai.snowflake.com/.
After logging in, it displays this with some suggested questions based on our Semantic View.
First question: Show me total costs across all services (warehouses, tasks, pipes, clustering)
This natural language question is the ideal starting point for the Generalist Agent. The Semantic View plays a critical role here: it abstracts the complexity of joining multiple ACCOUNT_USAGE tables and instead translates the question directly into the appropriate SQL against the METERING_DAILY_HISTORY view, which provides the reconciled daily costs.
Here is a sample of the SQL generated by the tools for the above conversation. This is how the Semantic View enables the Agent to instantly generate and run the correct query for the Cortex Analyst:
Let’s move to the Security of Snowflake.
What’s my overall MFA adoption rate?
How many roles exist in my account?
How many failed login attempts are there?
Can you show suspicious IP addresses?
Lastly, I loved how we can create a Health Report via this question:
What’s my overall Snowflake account health?
As you can see, you can use a Conventional AI Agent to secure and maintain your Snowflake deployment, and this is just a scratch of the surface. You can also use that same Semantic View to produce a Dashboard.
Conclusion: Behind SQL Management
Implementing a Semantic View and a Generalist AI Agent for Snowflake’s Account Usage schemas marks a significant transformation in how we handle, protect, and optimize our data infrastructure. We have transitioned from relying solely on manual SQL queries and static dashboards to adopting an era dominated by Conversational AI Maintenance.
This new pattern enables users to diagnose issues, monitor costs, and audit security easily through chat interactions with the platform. The Generalist Agent, which has a comprehensive overview of 20 Account Usage tables and 94 metrics, is particularly effective at offering cross-domain insights — linking costly queries to security vulnerabilities or storage expansion to performance issues.
Although I’ve only begun to explore cost, security, and governance through quick tests, the main takeaway is clear: the Semantic View proves to be a valuable tool not only for AI Agents but also for conventional BI/SQL applications. By utilizing Snowflake Intelligence, organizations can achieve a more intuitive, proactive, and efficient Snowflake deployment, maintaining full operational capability while effectively managing costs and risks.
A logical next step would be to enhance this work by implementing a robust agent monitoring and evaluation framework to continuously measure the agent’s performance, accuracy, and value over time.
If you want the basics of Snowflake Intelligence, go to Farris Jaber’s article, and please give kudos to Umesh Patel for triggering my exploration.
I am Augusto Rosa, a Snowflake Data Superhero and Snowflake SME. I am also the Head of Data, Cloud, & Security Architecture at Archetype Consulting. You can follow me on LinkedIn.
Subscribe to my Medium blog https://blog.augustorosa.com for the most interesting Data Engineering and Snowflake news.
Sources:
- https://medium.com/snowflake/build-snowflake-cost-savings-and-performance-agent-in-5-minutes-854427c0fdd8
- https://docs.snowflake.com/en/user-guide/views-semantic/overview
- https://docs.snowflake.com/en/sql-reference/snowflake-db
Top comments (0)