Snowflake Unloading with AWS

Unless otherwise noted, run the SQL snippets in this section against your Snowflake instance (as a user with the ACCOUNTADMIN role, since creating a storage integration requires it). The IAM steps are performed in your AWS account.

Step 1: Create AWS IAM Role

This role will be assumed by Snowflake to access the S3 bucket.

Follow the Snowflake IAM Role documentation to create a role named chalk-{organization}-snow-s3-access-role.

When setting the trust policy, use a temporary placeholder with your AWS account ID and external ID "0000". You will update this with actual Snowflake credentials in Step 3, once Snowflake’s identity is known.

Attach an IAM policy that grants S3 permissions to this role, scoped to the bucket/prefix you’ll unload to. The Snowflake storage integration role needs:

On the bucket ARN, for example arn:aws:s3:::chalk-data-bucket: s3:GetBucketLocation and s3:ListBucket.
On the unload prefix ARN, for example arn:aws:s3:::chalk-data-bucket/unload/*: s3:PutObject, s3:GetObject, s3:GetObjectVersion, s3:DeleteObject, and s3:DeleteObjectVersion.

The Chalk workload identity also needs access to the unload prefix so it can read the files Snowflake writes and clean them up. Grant it s3:GetBucketLocation and s3:ListBucket on the bucket, plus s3:GetObject and s3:DeleteObject on the unload prefix. Include s3:PutObject as well if that workload identity is also used for direct writes to this prefix.

Note: s3:ListBucket must target the bucket ARN (arn:aws:s3:::chalk-data-bucket), not the object/prefix ARN (arn:aws:s3:::chalk-data-bucket/unload/*). Scoping it to the object ARN produces anAccessDenied error on s3:ListBucket even though the other actions succeed.

Step 2: Create Storage Integration

Run against your Snowflake instance. This creates the storage integration object that Snowflake will use to authenticate to S3, referencing the real ARN of the role you just created:

-- Set variables (customize these for your environment)
SET ROLE_NAME='CHALK_ROLE';
SET INTEGRATION_NAME='S3_INTEGRATION_CHALK_DATA_BUCKET';
SET AWS_ROLE_ARN='arn:aws:iam::<aws_account_id>:role/chalk-offline-store-access-role';
SET S3_BUCKET='s3://chalk-data-bucket/';

-- Create storage integration if it doesn't exist
CREATE STORAGE INTEGRATION IF NOT EXISTS IDENTIFIER($INTEGRATION_NAME)
  TYPE = EXTERNAL_STAGE
  STORAGE_PROVIDER = 'S3'
  STORAGE_AWS_ROLE_ARN = $AWS_ROLE_ARN
  ENABLED = TRUE
  STORAGE_ALLOWED_LOCATIONS = ($S3_BUCKET);

-- Verify integration and get Snowflake credentials
DESCRIBE INTEGRATION IDENTIFIER($INTEGRATION_NAME);

Use underscores rather than hyphens in INTEGRATION_NAME.

From the DESCRIBE INTEGRATION output, record these values for Step 3:

STORAGE_AWS_IAM_USER_ARN - The Snowflake IAM user ARN
STORAGE_AWS_EXTERNAL_ID - The external ID for AWS trust policy

Step 3: Update IAM Role Trust Policy

Update the trust policy for the AWS IAM role you created in Step 1 (chalk-{organization}-snow-s3-access-role) with the actual Snowflake credentials from Step 2:

Replace the Principal’s AWS ARN with the STORAGE_AWS_IAM_USER_ARN value
Replace the External ID with the STORAGE_AWS_EXTERNAL_ID value

See the Snowflake trust policy documentation for detailed instructions.

Step 4: Create External Stage

Run against your Snowflake instance. The stage is the named object Chalk references when it unloads data; it points at a location inside your S3 bucket and writes through the storage integration created in Step 2.

-- Set variables (customize these for your environment)
SET STAGE_NAME='CHALK_UNLOAD_STAGE_S3';
SET S3_URL='s3://chalk-data-bucket/unload/';

-- Use the integration previously created (not a string)
CREATE STAGE IF NOT EXISTS IDENTIFIER($STAGE_NAME)
  STORAGE_INTEGRATION = <INTEGRATION_NAME>
  URL = $S3_URL;

If your database or schema name contains characters other than letters, digits, or underscores (e.g. my-database), it's a quoted, case-sensitive identifier. Reference it with quotes preserved wherever you use a fully-qualified name, e.g. `"my-database".PUBLIC.CHALK_UNLOAD_STAGE_S3`. Also make sure your session has a current database/schema set (`USE DATABASE` / `USE SCHEMA`) before running stage operations, or use a fully-qualified stage name.

Step 5: Grant Permissions

Run against your Snowflake instance. This grants the Chalk role access to the storage integration and stage so it can perform the unload:

-- Variables for reference (example values; use the same names as the earlier steps)
SET ROLE_NAME='CHALK_ROLE';
SET INTEGRATION_NAME='S3_INTEGRATION_CHALK_DATA_BUCKET';
SET STAGE_NAME='CHALK_UNLOAD_STAGE_S3';

-- Grant usage on storage integration
GRANT USAGE ON INTEGRATION <INTEGRATION_NAME> TO ROLE <ROLE_NAME>;

-- Grant stage permissions
GRANT USAGE ON STAGE <STAGE_NAME> TO ROLE <ROLE_NAME>;

Step 6: Validate

You can optionally validate the integration end-to-end using

SELECT SYSTEM$VALIDATE_STORAGE_INTEGRATION('S3_INTEGRATION_CHALK_DATA_BUCKET', 's3://chalk-data-bucket/unload/', 'test.csv', 'WRITE');

After completing these steps, continue to Configure Chalk environment.

​Step 1: Create AWS IAM Role

​Step 2: Create Storage Integration

​Step 3: Update IAM Role Trust Policy

​Step 4: Create External Stage

​Step 5: Grant Permissions