Snowflake Storage Integrations: Secure Authentication for External Stages

A Storage Integration is a Snowflake object that centrally manages authentication when Snowflake accesses external cloud storage (S3, Azure Blob, or GCS). Instead of embedding access keys and secrets directly in stage definitions, you configure access delegation to an IAM role or service principal on the cloud provider side. This removes the need to distribute and rotate long-lived keys.

How Storage Integrations Work

When you create a Storage Integration, Snowflake internally provisions a service account specific to the cloud provider (an IAM user ARN in the case of AWS). You then configure a Trust Policy on the cloud side that allows this service account to assume the role, enabling Snowflake to access the storage securely.

Authentication Flow (AWS S3)

┌──────────────────────┐
│  Snowflake           │
│  Storage Integration │
│  (STORAGE_AWS_IAM_   │
│   USER_ARN を保持)   │
└────────┬─────────────┘
         │ 1. Assume Role リクエスト
         │    (External ID を付与)
         ▼
┌──────────────────────┐
│  AWS IAM Role        │
│  (Trust Policy で    │
│   Snowflake ARN +    │
│   External ID を許可)│
└────────┬─────────────┘
         │ 2. 一時クレデンシャル発行
         ▼
┌──────────────────────┐
│  S3 Bucket           │
│  /orders/            │
│  (IAM Roleの権限で   │
│   読み取り/書き込み)  │
└──────────────────────┘

CREATE STORAGE INTEGRATION (AWS S3)

-- AWS S3用 Storage Integration
CREATE OR REPLACE STORAGE INTEGRATION s3_orders_int
  TYPE = EXTERNAL_STAGE
  STORAGE_PROVIDER = 'S3'
  ENABLED = TRUE
  STORAGE_AWS_ROLE_ARN = 'arn:aws:iam::123456789012:role/snowflake-access-role'
  STORAGE_ALLOWED_LOCATIONS = (
    's3://company-data-lake/orders/',
    's3://company-data-lake/returns/'
  )
  STORAGE_BLOCKED_LOCATIONS = (
    's3://company-data-lake/orders/secrets/'
  );

CREATE STORAGE INTEGRATION (Azure Blob)

-- Azure Blob Storage用 Storage Integration
CREATE OR REPLACE STORAGE INTEGRATION azure_data_int
  TYPE = EXTERNAL_STAGE
  STORAGE_PROVIDER = 'AZURE'
  ENABLED = TRUE
  AZURE_TENANT_ID = 'a]1b2c3d4-e5f6-7890-abcd-ef1234567890'
  STORAGE_ALLOWED_LOCATIONS = (
    'azure://myaccount.blob.core.windows.net/data-container/input/'
  );

CREATE STORAGE INTEGRATION (GCS)

-- Google Cloud Storage用 Storage Integration
CREATE OR REPLACE STORAGE INTEGRATION gcs_analytics_int
  TYPE = EXTERNAL_STAGE
  STORAGE_PROVIDER = 'GCS'
  ENABLED = TRUE
  STORAGE_ALLOWED_LOCATIONS = (
    'gcs://analytics-bucket/raw/',
    'gcs://analytics-bucket/processed/'
  );

DESCRIBE INTEGRATION Output

After creating a Storage Integration, run DESCRIBE INTEGRATION to retrieve the service account information that Snowflake generated. You then plug these values into the Trust Policy on the cloud side.

DESC INTEGRATION s3_orders_int;

-- 主要な出力項目（AWSの場合）
-- ┌─────────────────────────────┬──────────────────────────────────────┐
-- │ property                    │ property_value                       │
-- ├─────────────────────────────┼──────────────────────────────────────┤
-- │ STORAGE_AWS_IAM_USER_ARN    │ arn:aws:iam::987654321098:user/abc123│
-- │ STORAGE_AWS_EXTERNAL_ID     │ ABC123_SFCRole=2_dGVzdA==            │
-- │ STORAGE_ALLOWED_LOCATIONS   │ s3://company-data-lake/orders/,...   │
-- │ STORAGE_BLOCKED_LOCATIONS   │ s3://company-data-lake/orders/sec... │
-- └─────────────────────────────┴──────────────────────────────────────┘

IAM Trust Policy Configuration (AWS)

Take the STORAGE_AWS_IAM_USER_ARN and STORAGE_AWS_EXTERNAL_ID returned by DESCRIBE INTEGRATION and apply them to the Trust Policy of the IAM role on the AWS side.

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Principal": {
        "AWS": "arn:aws:iam::987654321098:user/abc123"
      },
      "Action": "sts:AssumeRole",
      "Condition": {
        "StringEquals": {
          "sts:ExternalId": "ABC123_SFCRole=2_dGVzdA=="
        }
      }
    }
  ]
}

Grant the IAM role permissions for GetObject / ListBucket (read access) on the S3 bucket, and add PutObject / DeleteObject (write access) if needed.

Creating an External Stage

-- Storage Integrationを参照するExternal Stage
CREATE OR REPLACE STAGE ext_orders_stage
  URL = 's3://company-data-lake/orders/'
  STORAGE_INTEGRATION = s3_orders_int
  FILE_FORMAT = (TYPE = 'PARQUET');

-- ステージ上のファイル確認
LIST @ext_orders_stage;

-- データロード
COPY INTO ORDERS
  FROM @ext_orders_stage
  PATTERN = '.*\.parquet'
  ON_ERROR = 'CONTINUE';

-- データアンロード
COPY INTO @ext_orders_stage/export/
  FROM ORDERS
  FILE_FORMAT = (TYPE = 'PARQUET')
  HEADER = TRUE;

Direct Authentication vs Storage Integration

Privilege Model

-- 権限付与の例
-- ACCOUNTADMINがIntegrationを作成
USE ROLE ACCOUNTADMIN;
CREATE STORAGE INTEGRATION s3_orders_int ...;

-- データエンジニアロールにUSAGE権限を付与
GRANT USAGE ON INTEGRATION s3_orders_int TO ROLE DATA_ENGINEER;

-- DATA_ENGINEERがステージを作成
USE ROLE DATA_ENGINEER;
CREATE STAGE ext_orders
  URL = 's3://company-data-lake/orders/'
  STORAGE_INTEGRATION = s3_orders_int;

Troubleshooting

Sample Question

Frequently Asked Questions