Skip to content

Recipes

Data Sharing

Provider

  • create share <share-name>
  • grant <privilege> on <object> to share <share-name>
    • select for tables/views, usage for UDFs/database, reference_usage for views referencing multiple databases
  • alter share <share-name> add accounts = <consumer-account>
  • Optionally create a reader account: create managed account

Consumer

  • create database <x> from share <provider account>.<share-name>
  • grant imported privileges to role ...

Replication

Group based

  • primary 
    CREATE {REPLICATION | FAILOVER} GROUP myrg
        OBJECT_TYPES = DATABASES, SHARES
        ALLOWED_DATABASES = db1, db2
        ALLOWED_SHARES = s1
        ALLOWED_ACCOUNTS = myorg.myaccount2, myorg.myaccount3
        REPLICATION_SCHEDULE = '10 MINUTE';
  • secondary 
    CREATE {REPLICATION | FAILOVER} GROUP myrg AS REPLICA OF myorg.myaccount1.myrg;
ALTER {REPLICATION | FAILOVER} GROUP myrg REFRESH; -- failover to this secondary
ALTER FAILOVER GROUP myrg PRIMARY; -- failover, secondary

legacy

  • primary: alter database ... enable replication to accounts ...
  • secondary: create database ... as replica of <account>.<primary-db>
  • secondary: promote to primary alter database ... primary
    • previous primary then becomes RO and new primary becomes RW

S3 Storage Integration

  1. Create IAM policy, and in JSON tab add policy doc that allow:
    • GetObject and GetObjectVersion on all objects under desired prefix
    • ListBucket and GetBucketLocation for the bucket with condition that includes the desired prefix
  2. Create IAM Role of type "Another AWS Account" as type of trusted entity
    • set Account ID and External ID to temporary values for now
  3. Create storage integration, using the Role ARN from the previous step
  4. Use DESC INTEGRATION <integration_name> to retrieve STORAGE_AWS_IAM_USER_ARN and STORAGE_AWS_EXTERNAL_ID
  5. Update IAM Role using Edit trust relationship and modify the JSON doc to set Principal and sts:ExternalId values
  6. Optional: not BP use one IAM Role's trust policy to allow all external stages in your account (default one role per one SI)
    • External ID have a format <snowflake_account>_SFCRole=<snowflake_role_id>_<random-id>. Replace the <random-id> with *

S3 Notifications

  • for Auto Ingest, Directory Stage, External Table
  • If there is no other SQL event configured, configure SQS, otherwise use SNS

Using SQS

  1. create pipe ... auto_ingest=true as copy into ...
  2. get SQS ARN:
    • for pipes: show pipes and note notification_channel column
    • for directory stage: desc stage ... and note directory_notification_channel
    • for external table: show external tables and note notification_channel
  3. For the S3 bucket configure SQS using:
    • Events: ObjectCreate (All) and ObjectRemoved (optionally filter by path/extension)
    • Add SQS Queue ARN from the previous step

Using SNS

  1. AWS Console -> SNS -> Topics -> locate topic for the S3 bucket -> note the Topic ARN
  2. Get Snowflake account's SNS policy statement using system$get_aws_sns_iam_policy('<sns_topic_arn>')
  3. Edit the Access policy for the SNS Topic and add following policy statements:
    • subscribe Snowflake SQS using statement from the previous step
    • policy to allow S3 service to allow SNS:Publish action

Auto-refresh directory tables

  1. Configure Secure access to S3
  2. create stage ... directory = (enable= true auto_refresh = true [aws_sns_topic = <s3-sns-topic>])
  3. Configure Event notification
  4. manually refresh once directory table metadata
  5. Configure security: usage on Database, schema, named stage, named file-format, and read on named stage

External table

  1. Configure Secure access to S3
  2. create external table ... with location = @s3_stage/tpch_sf100/orders [auto_refresh = true]
  3. manually sync once metadata using alter external table <table-name> refresh
  4. Configure security: usage on Database, schema, named stage, named file-format, read on named stage, select on external table
  5. Optional: remove obsolete files using alter external table <table> remove files (<file1>, ...)

SCIM

  1. create role provisioner_role
  2. grant create user, create role on account to role provisioner_role
  3. create security integration okta_provisioning type = scim scim_client = 'okta' run_as_role = 'PROVISIONER_ROLE'
  4. select system$generate_scim_access_token('okta_provisioning')
  5. Okta
    1. Snowflake Application -> Settings -> Integration -> Enable API Integration
    2. For API token enter the value generated from Snowflake function
    3. Optional: For App -> Provisioning Features enable defaultRole, defaultSecondaryRoles and defaultWarehouse attribute values/expressions
  6. Optional To enable Snowflake initiated SSO: alter account set sso_login_page = true
  1. Contact support with the (12 digit) VPC account IDs and Snowflake account URLs
    • Snowflake supports supplies VPCE address (com.amazonaws.vpce.<region_id>.vpce-svc-xxxxxxxxxxxxxxxxx) or use SYSTEM$GET_PRIVATELINK_CONFIG()
  2. Create an endpoint for the VPCE address
    • authorize a VPCE security group to allow 443 (Snowflake) and 80 (OCSP)
    • authorize SG of services that connect to Snowflake to allow outgoing 443 and 80 of the VPCE CIDR
  3. Configure your VPC, create CNAME records for
    • locator and regionless: privatelink-account-url and regionless-privatelink-account-url (SYSTEM$GET_PRIVATELINK_CONFIG())
    • OCSP: privatelink-ocsp-url (SYSTEM$GET_PRIVATELINK_CONFIG())
    • SnowSight (and Data Marketplace): SNOWSIGHT_DEPLOYMENT from SYSTEM$WHITELIST_PRIVATELINK()

Private access for S3

  • Configure either VPC interface endpoint (recommended) for S3 or 1+ gateway endpoints
  • When using S3 gateway endpoint, allow following hosts:
    • sfc-*-stage.s3.amazonaws.com:443
    • sfc-repo.snowflakecomputing.com:443 (for auto-updating SnowSQL)
    • sfc-*-stage.s3{-,.}<region_id>.amazonaws.com:443 (except US East)
  • When using VPC interface endpoint for internal stages
    1. alter account set enable_internal_stages_privatelink = true; record privatelink_internal_stage (system$get_privatelink_config())
    2. create a VPC endpoint for AWS PrivateLink for S3; record VPCE DNS Name
    3. create DNS entry to resolve <bucket_name>.s3.<region>.amazonaws.com to the endpoint received from previous step by replacing wildcard * with bucket name