Skip to content

Account Objects

Organization

  • New accounts can be created only in the regions enabled for the organization (show regions)
  • Organizations receive a single bill for all the accounts
  • ORGADMIN role is enabled by Snowflake support at account level and can be assumed/granted by ACCOUNTADMIN
  • ORGADMIN can
    • create and view account properties
    • view historical usage of all accounts via ORGANIZATION_USAGE
    • enable account replication using SYSTEM$GLOBAL_ACCOUNT_SET_PARAMETER('<account1_locator>','ENABLE_ACCOUNT_DATABASE_REPLICATION', 'true')
  • ORGADMIN cannot
    • view account data
    • rename or delete accounts (yet). Contact Snowflake support
  • Region Group is a group of region with similar security controls, isolation and compliance
Group Regions
PUBLIC all multi-tenant snowflake regions across all clouds
VPS single tenant regions that belong to the customer (e.g. CMS)
gov region groups containing gov regions (e.g. AWS_US_GOV_EAST_1)

Account Identifier

  • Two options: Option 1 is Org and Acc Name, Option 2 is Account locator in region
  • Private connectivity: Either account-identifier or account-locator URL can be used
  • Replication/Failover:
    • [[<region-group>.]<snowflake_region>.]<account_locator>, qualifiers required for replicating across region, regrion-groups
    • <organization_name>.<account_name>

Organization and Account Name

  • Option 1:
    • <org-name>-<account-name>: Most URLs and general purpose
    • <org-name>_<account-name>: Where hyphens are not supported, E.g. Okta SSO/SCIM
    • <org-name>.<account-name>: SQL commands and operations
  • Account-name:
    • For a new account, name is chosen when it's created within an org
    • when existing account is added to an org, the name is, Account Locator in a Region (same as Option 2)

Using Account Locator in Region

  • Option 2:
    • <account_locator>: only if the account is in AWS US West (Oregon)
    • <account_locator>.<region_id>: e.g. if the account is in AWS US East (N. Virginia)
    • <account_locator>.<region_id>.<cloud>
  • account_locator is name given at account creation and cannot be changed.
    • Can be chosen by customer or may be a random string chosen automatically

Misc Objects

  • DATABASE: collection of schema and objects contained within schema
    • can be cloned, shared, UnDropped
  • ROLE:
    • system roles: AccountAdmin, SecurityAdmin, UserAdmin, SysAdmin, Public
    • UserAdmin can create users and roles, and manage the roles it owns.
    • SecurityAdmin is superset of UserAdmin and has MANAGE GRANTS privileges to manage any role
    • secondary role can be turned off USE SECONDARY ROLE {ALL | NONE}
    • only primary role provides authorization to execute CREATE <object> statements
  • SHARE: allow data sharing between consumers and providers
  • USER: default_{schema,warehouse,role}, default namespace <db>[.<schema>]

EXTERNAL VOLUME

  1. Create IAM policy to grant S3 permissions
    • unmanaged: GetBucketLocation, GetObject, GetObjectVersion, ListBucket
    • managed: above plus PutObject, DeleteObject
  2. Create IAM Role using above policy to grant to another Amazon account, and enable require External ID
  3. (optional) Create SSE-KMS for encryption, IAM -> Customer Managed Key -> Create Key
    • type: symmetric, usage: encrypt (kms:GenerateDataKey) and decrypt (kms:Decrypt), role: created above
  4. Create external volume with storage_locations
    • optional encryption=(type='AWS_SSE_KMS', kms_key_id='<key ARN>')
  5. Update role's trust relationship with storage_aws_iam_user_arn and storage_aws_external_id

RESOURCE MONITOR

  • control WH credit usage at warehouse or the account level
    • account level RM does not override WH level RM. If either one reaches limit, action will be triggered
  • RM cannot control credit consumed by serverless features (e.g. Snowpipe, auto-clustering, MV etc) or cloud services
  • A WH can have only one RM assigned, however, a RM can be assigned to 1+ WH
    • Total usage of all warehouses that share the RM counts towards quota
  • intended for monitoring resources over days or weeks, v/s of hourly
    • for more precise credit usage control, assign a single WH to each RM
  • options:
    • credit_quota
    • frequency (at which quota resets)
    • start_timestamp first date of period (frequency)
    • end_timestamp optional, stops WH regardless of credits used
    • triggers 1+ on <n> percent do {suspend,suspend immediate,notify}
  • use alter {warehouse | account} set resource_monitor = <rm> to assign resource monitor
  • can be created by ACCOUNTADMIN, and optionally granted to MONITOR (view only) and MODIFY (modify)
  • notifications:
    • Account level RM can only send notification to all users with AccountAdmin role
    • non-admin users can only receive notifications for WH level RM

Parameters

  • hierarchy
    • Session (ALTER SESSION) > User (ALTER USER) > Account level session parameters (ALTER ACCOUNT)
    • Warehouse > Account level WH parameters
    • Table > Schema > Database > Account level object parameters
  • show parameters [like '<pat>'] in {database <db>| warehouse <wh> | account}