Skip to main content

System Functions for Account Management

Snowflake provides a suite of administrative commands called System Functions (which usually begin with SYSTEM$). These functions allow you to perform account-level operations that cannot be handled by standard SQL DDL or DML commands.

They are typically reserved for roles like ACCOUNTADMIN and ORGADMIN, and are essential for infrastructure management, security, and troubleshooting.

Topics Covered​

  • Key Context Functions (CURRENT_ACCOUNT, etc.)
  • Account Configuration Functions
  • Network and Security Validation Functions

Key Context Functions​

When writing dynamic scripts (e.g., in dbt or Python), you often need to know the current execution context. Snowflake provides context functions to return information about the active session.

Run these in your worksheet to see your current environment.

-- View the current account locator
SELECT CURRENT_ACCOUNT();

-- View the cloud provider region where this account is hosted
SELECT CURRENT_REGION();

-- View the organization name (Useful if your company has multiple Snowflake accounts)
SELECT CURRENT_ORGANIZATION_NAME();

-- View the currently active user and role
SELECT CURRENT_USER();
SELECT CURRENT_ROLE();

Account Configuration Functions​

System functions allow you to alter behaviors at a global level.

Enabling Behavior Change Bundles​

Snowflake frequently rolls out new features and updates. Before making them permanent, they bundle them into a "Behavior Change Bundle". Account administrators can opt-in to test these changes using a system function.

USE ROLE ACCOUNTADMIN;

-- Enable a specific behavior change bundle for testing
-- SELECT SYSTEM$ENABLE_BEHAVIOR_CHANGE_BUNDLE('2023_08');

-- Disable it if it breaks your existing pipelines
-- SELECT SYSTEM$DISABLE_BEHAVIOR_CHANGE_BUNDLE('2023_08');

Network and Security Validation Functions​

Network setup is a common failure point when configuring data pipelines. Snowflake provides several SYSTEM$ functions to help you diagnose and validate your setup.

1. Generating Network Whitelists​

If your company, perhaps an enterprise bank based in Mumbai, has strict egress firewalls, your network engineers need to know exactly which URLs Snowflake uses so they can whitelist them.

-- Generate a JSON list of all URLs required for standard public connectivity
SELECT SYSTEM$ALLOWLIST();

-- Generate a JSON list for AWS/Azure/GCP PrivateLink connectivity
SELECT SYSTEM$ALLOWLIST_PRIVATELINK();

2. Verifying External Stage Integrations​

When setting up an external stage (e.g., an S3 bucket or Azure Blob), you must configure a Storage Integration with an IAM role. Diagnosing IAM permissions is notoriously difficult.

Snowflake provides a specific system function to test whether the Snowflake IAM role has the correct read/write permissions on the bucket.

-- Verify if Snowflake can write to the S3 bucket using the storage integration
SELECT SYSTEM$VERIFY_EXTERNAL_STAGE_INTEGRATION_PRIVILEGES(
'S3_STORAGE_INT',
's3://beems-datalake-mumbai/inbound/'
);

If this function returns "OK", your IAM trust policy is correct. If it fails, it will provide the specific AWS/Azure error message, saving hours of debugging!

Hands-On Demonstration: Administrative Audit​

Let's simulate an admin script that logs the current environment state into an audit table.

USE ROLE SYSADMIN;

CREATE DATABASE IF NOT EXISTS BEEMS_ADMIN_DB;
CREATE SCHEMA IF NOT EXISTS AUDIT_LOGS;

CREATE OR REPLACE TABLE BEEMS_ADMIN_DB.AUDIT_LOGS.ENVIRONMENT_LOG (
LOG_TIMESTAMP TIMESTAMP DEFAULT CURRENT_TIMESTAMP(),
EXECUTED_BY STRING,
ACCOUNT_LOCATOR STRING,
CLOUD_REGION STRING,
ORGANIZATION STRING
);

-- Insert the context using system functions
INSERT INTO BEEMS_ADMIN_DB.AUDIT_LOGS.ENVIRONMENT_LOG (EXECUTED_BY, ACCOUNT_LOCATOR, CLOUD_REGION, ORGANIZATION)
SELECT
CURRENT_USER(),
CURRENT_ACCOUNT(),
CURRENT_REGION(),
CURRENT_ORGANIZATION_NAME();

SELECT * FROM BEEMS_ADMIN_DB.AUDIT_LOGS.ENVIRONMENT_LOG;

Summary​

  • Context Functions (CURRENT_USER(), CURRENT_ACCOUNT()) return session-level metadata.
  • SYSTEM$ALLOWLIST() is critical for network engineers to configure corporate firewalls.
  • SYSTEM$VERIFY_EXTERNAL_STAGE_INTEGRATION_PRIVILEGES() is the best tool for debugging AWS/Azure IAM permissions on storage integrations.
  • Most SYSTEM$ functions require the ACCOUNTADMIN role.