SnowPro Advanced - Administrator - Fact Sheet¶

-- Set account parameter
ALTER ACCOUNT SET STATEMENT_TIMEOUT_IN_SECONDS = 172800;

-- Set session parameter
ALTER SESSION SET USE_CACHED_RESULT = FALSE;

-- Set object parameter
ALTER TABLE my_table SET DATA_RETENTION_TIME_IN_DAYS = 30;

-- View parameters
SHOW PARAMETERS IN ACCOUNT;
SHOW PARAMETERS LIKE 'STATEMENT_TIMEOUT%' IN ACCOUNT;

-- Create database role
CREATE DATABASE ROLE analytics_db.reader;
CREATE DATABASE ROLE analytics_db.writer;

-- Grant privileges
GRANT USAGE ON ALL SCHEMAS IN DATABASE analytics_db TO DATABASE ROLE analytics_db.reader;
GRANT SELECT ON ALL TABLES IN DATABASE analytics_db TO DATABASE ROLE analytics_db.reader;

-- Grant database role to account role
GRANT DATABASE ROLE analytics_db.reader TO ROLE analyst_role;

-- Auto-grant SELECT on future tables in schema
GRANT SELECT ON FUTURE TABLES IN SCHEMA my_db.public TO ROLE analyst_role;

-- Auto-grant USAGE on future schemas in database
GRANT USAGE ON FUTURE SCHEMAS IN DATABASE my_db TO ROLE analyst_role;

-- View future grants
SHOW FUTURE GRANTS IN SCHEMA my_db.public;

-- Account-level monitor
CREATE RESOURCE MONITOR company_monthly
  WITH CREDIT_QUOTA = 10000
  FREQUENCY = MONTHLY
  START_TIMESTAMP = IMMEDIATELY
  TRIGGERS
    ON 50 PERCENT DO NOTIFY
    ON 75 PERCENT DO NOTIFY
    ON 90 PERCENT DO SUSPEND
    ON 100 PERCENT DO SUSPEND_IMMEDIATE;

ALTER ACCOUNT SET RESOURCE_MONITOR = company_monthly;

-- Warehouse-level monitor
CREATE RESOURCE MONITOR team_weekly
  WITH CREDIT_QUOTA = 500
  FREQUENCY = WEEKLY
  START_TIMESTAMP = IMMEDIATELY
  TRIGGERS
    ON 80 PERCENT DO NOTIFY
    ON 100 PERCENT DO SUSPEND;

ALTER WAREHOUSE team_wh SET RESOURCE_MONITOR = team_weekly;

-- Long-running queries
SELECT query_id, query_text, user_name, warehouse_name,
       execution_time/1000 AS exec_seconds,
       bytes_scanned/1024/1024/1024 AS gb_scanned,
       rows_produced
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE start_time >= DATEADD('day', -1, CURRENT_TIMESTAMP())
  AND execution_time > 60000  -- > 60 seconds
ORDER BY execution_time DESC
LIMIT 20;

-- Warehouse utilization
SELECT warehouse_name,
       DATE_TRUNC('hour', start_time) AS hour,
       SUM(credits_used) AS credits,
       AVG(avg_running) AS avg_queries_running,
       AVG(avg_queued_load) AS avg_queued
FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY
WHERE start_time >= DATEADD('day', -7, CURRENT_TIMESTAMP())
GROUP BY 1, 2
ORDER BY 1, 2;

-- Failed logins
SELECT user_name, client_ip, reported_client_type,
       error_code, error_message, event_timestamp
FROM SNOWFLAKE.ACCOUNT_USAGE.LOGIN_HISTORY
WHERE is_success = 'NO'
  AND event_timestamp >= DATEADD('day', -7, CURRENT_TIMESTAMP())
ORDER BY event_timestamp DESC;

-- Set Time Travel retention
ALTER TABLE my_table SET DATA_RETENTION_TIME_IN_DAYS = 30;

-- Query historical data
SELECT * FROM my_table AT(TIMESTAMP => '2024-01-15 10:00:00'::TIMESTAMP);
SELECT * FROM my_table AT(OFFSET => -3600);  -- 1 hour ago
SELECT * FROM my_table BEFORE(STATEMENT => '<query_id>');

-- Undrop table
UNDROP TABLE accidentally_dropped_table;

-- Classify data with tags
CREATE TAG data_classification ALLOWED_VALUES 'PUBLIC', 'INTERNAL', 'CONFIDENTIAL', 'RESTRICTED';
ALTER TABLE customers SET TAG data_classification = 'CONFIDENTIAL';
ALTER TABLE customers MODIFY COLUMN ssn SET TAG data_classification = 'RESTRICTED';

-- View classification
SELECT * FROM TABLE(INFORMATION_SCHEMA.TAG_REFERENCES(
  'customers', 'TABLE'
));