Organization-Level Management¶

-- Switch to ORGADMIN role
USE ROLE ORGADMIN;

-- List all accounts in the organization
SHOW ORGANIZATION ACCOUNTS;

-- Create a new account
CREATE ACCOUNT dev_account
  ADMIN_NAME = 'admin'
  ADMIN_PASSWORD = 'StrongPassword123!'
  EMAIL = 'admin@company.com'
  EDITION = 'ENTERPRISE'
  REGION = 'AWS_US_EAST_1';

-- View organization usage
SELECT * FROM SNOWFLAKE.ORGANIZATION_USAGE.WAREHOUSE_METERING_HISTORY
WHERE start_time >= DATEADD('month', -1, CURRENT_TIMESTAMP());

-- Create account
CREATE ACCOUNT staging_account
  ADMIN_NAME = 'staging_admin'
  ADMIN_PASSWORD = 'Password123!'
  EMAIL = 'staging@company.com'
  EDITION = 'ENTERPRISE'
  REGION = 'AWS_US_WEST_2';

-- Rename account
ALTER ACCOUNT staging_account RENAME TO staging_v2;

-- Drop account (25 days to recover)
DROP ACCOUNT old_test_account;

-- Undrop account (within 25 days)
UNDROP ACCOUNT old_test_account;

-- View all account parameters
SHOW PARAMETERS IN ACCOUNT;

-- Common account parameters
ALTER ACCOUNT SET
  STATEMENT_TIMEOUT_IN_SECONDS = 172800        -- 48 hours max query time
  STATEMENT_QUEUED_TIMEOUT_IN_SECONDS = 0      -- No queue timeout
  NETWORK_POLICY = 'corp_policy'               -- Default network policy
  REQUIRE_STORAGE_INTEGRATION_FOR_STAGE_CREATION = TRUE  -- Security
  ENABLE_ACCOUNT_DATABASE_REPLICATION = TRUE    -- Allow replication
  MIN_DATA_RETENTION_TIME_IN_DAYS = 1;         -- Minimum Time Travel

-- Set at different levels
ALTER ACCOUNT SET STATEMENT_TIMEOUT_IN_SECONDS = 172800;  -- Account
ALTER USER analyst SET STATEMENT_TIMEOUT_IN_SECONDS = 3600;  -- User
ALTER SESSION SET STATEMENT_TIMEOUT_IN_SECONDS = 1800;  -- Session
ALTER WAREHOUSE my_wh SET STATEMENT_TIMEOUT_IN_SECONDS = 7200;  -- Object

-- Monthly credit breakdown by service
SELECT service_type, SUM(credits_used) AS total_credits
FROM SNOWFLAKE.ACCOUNT_USAGE.METERING_HISTORY
WHERE start_time >= DATE_TRUNC('month', CURRENT_TIMESTAMP())
GROUP BY service_type
ORDER BY total_credits DESC;

-- Daily credit trend
SELECT DATE_TRUNC('day', start_time) AS day,
       warehouse_name,
       SUM(credits_used) AS credits
FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY
WHERE start_time >= DATEADD('month', -1, CURRENT_TIMESTAMP())
GROUP BY 1, 2
ORDER BY 1, 3 DESC;

-- Storage breakdown
SELECT DATE_TRUNC('month', usage_date) AS month,
       AVG(storage_bytes)/1024/1024/1024/1024 AS avg_tb,
       AVG(stage_bytes)/1024/1024/1024/1024 AS avg_stage_tb,
       AVG(failsafe_bytes)/1024/1024/1024/1024 AS avg_failsafe_tb
FROM SNOWFLAKE.ACCOUNT_USAGE.STORAGE_USAGE
WHERE usage_date >= DATEADD('month', -6, CURRENT_DATE())
GROUP BY 1
ORDER BY 1;

-- Organization-level billing (requires ORGADMIN)
USE ROLE ORGADMIN;

SELECT account_name, service_type,
       SUM(credits_used) AS total_credits
FROM SNOWFLAKE.ORGANIZATION_USAGE.WAREHOUSE_METERING_HISTORY
WHERE start_time >= DATE_TRUNC('month', CURRENT_TIMESTAMP())
GROUP BY 1, 2
ORDER BY 3 DESC;

-- Enable replication from ORGADMIN
ALTER ACCOUNT prod_account ENABLE REPLICATION TO ACCOUNTS staging_account;

-- From source account: enable database replication
ALTER DATABASE analytics ENABLE REPLICATION TO ACCOUNTS org.staging_account;

-- From target account: create replica
CREATE DATABASE analytics AS REPLICA OF org.prod_account.analytics;

-- Monitor replication
SELECT * FROM TABLE(INFORMATION_SCHEMA.DATABASE_REPLICATION_USAGE_HISTORY());