Data Movement · Topic 3 of 17
Stages, Storage Integrations and Encryption
Set up internal and external stages, storage integrations and stage encryption, and learn scoped, file and pre-signed URLs for staged files.
- 5 min
- Includes a diagram
- Stage references:
@~user,@%tabletable,@namenamed. Table stages cannot use INFER_SCHEMA or directory tables. - A storage integration holds the cloud identity and
STORAGE_ALLOWED_LOCATIONS;CREATE INTEGRATIONis the required privilege. - Internal stage encryption:
SNOWFLAKE_FULL(default) orSNOWFLAKE_SSE, fixed at creation.
What it is
A stage is a named location that holds data files before they are loaded or after they are unloaded. Internal stages live inside Snowflake storage; external stages point at your own cloud storage (Amazon S3, Google Cloud Storage or Azure Blob Storage).
A storage integration is an account level object that lets Snowflake reach a bucket through a cloud identity, so no keys or secrets are stored in stage definitions. Encryption settings decide how staged files are protected and whether URLs can open them.
How it works
Implementing stages and file formats
| Stage | Reference | Created how | Good for |
|---|---|---|---|
| User stage | @~ | Exists for every user | Private files of one user; no file format options |
| Table stage | @%table_name | Exists for every table | Files for one table; no transformations while loading, not usable with INFER_SCHEMA |
| Named internal stage | @my_stage | CREATE STAGE | Shared team files and Snowpipe REST loads |
| Named external stage | @my_ext_stage | CREATE STAGE ... URL = 's3://...' | Files that stay in your cloud storage |
Named stages can carry a default file format and copy options, and they are the only stages that support all features such as directory tables. User and table stages cannot be altered or dropped.
CREATE FILE FORMAT parquet_ff TYPE = PARQUET;
CREATE STAGE raw_ext_stage
URL = 's3://acme-raw/orders/'
STORAGE_INTEGRATION = s3_int
FILE_FORMAT = parquet_ff;
Useful commands: LIST @stage shows files with size, MD5 and last modified, REMOVE @stage/path deletes files, and PUT or GET move files between a client and an internal stage.
End an external stage URL with a slash. Without it, s3://bucket/orders also matches s3://bucket/orders_archive/.
Storage integrations
A storage integration stores a generated cloud identity plus the locations Snowflake may use. One integration can back many stages.
CREATE STORAGE INTEGRATION s3_int
TYPE = EXTERNAL_STAGE
STORAGE_PROVIDER = 'S3'
ENABLED = TRUE
STORAGE_AWS_ROLE_ARN = 'arn:aws:iam::111122223333:role/snowflake_load'
STORAGE_ALLOWED_LOCATIONS = ('s3://acme-raw/')
STORAGE_BLOCKED_LOCATIONS = ('s3://acme-raw/private/');
DESC STORAGE INTEGRATION s3_int;
The setup is a handshake between the two clouds.
| Cloud | STORAGE_PROVIDER | Values you copy from DESC into the cloud |
|---|---|---|
| Amazon S3 | 'S3' | STORAGE_AWS_IAM_USER_ARN and STORAGE_AWS_EXTERNAL_ID, placed in the role trust policy |
| Google Cloud Storage | 'GCS' | STORAGE_GCP_SERVICE_ACCOUNT, granted on the bucket |
| Azure Blob Storage | 'AZURE' (needs AZURE_TENANT_ID) | AZURE_CONSENT_URL to approve the app, and AZURE_MULTI_TENANT_APP_NAME for role assignment |
Key rules:
- Only a role with the global
CREATE INTEGRATIONprivilege (by default ACCOUNTADMIN) creates integrations. STORAGE_ALLOWED_LOCATIONSis required;STORAGE_BLOCKED_LOCATIONSis optional and removes paths from the allowed set.- The stage
URLmust sit inside an allowed location, or creating the stage fails. - Granting
USAGEon the integration lets another role create stages with it. Reading an external stage needsUSAGEon the stage; an internal stage needsREAD. - Integrations are the recommended alternative to embedding
CREDENTIALS(access keys) or a SAS token in the stage, because those would be visible to anyone who can describe the stage.
Managing encryption
Internal stages support two server side modes, chosen at creation and never changeable afterwards.
ENCRYPTION TYPE | Behavior |
|---|---|
SNOWFLAKE_FULL (default) | Files are encrypted on the client before upload and again on the server |
SNOWFLAKE_SSE | Server side encryption only; required if you want URLs to open the files; no Tri-Secret Secure support |
External stages use the settings of your cloud storage. The default is NONE.
| Cloud | Options |
|---|---|
| Amazon S3 | AWS_CSE (client side, needs MASTER_KEY), AWS_SSE_S3, AWS_SSE_KMS, NONE |
| Google Cloud Storage | GCS_SSE_KMS, NONE |
| Azure | AZURE_CSE (client side, needs MASTER_KEY), NONE |
For client side encryption, MASTER_KEY is a Base64 encoded 128 or 256 bit key, and Snowflake reads the files using that key. For server side options with a KMS key, KMS_KEY_ID is optional; the default KMS key is used if omitted, and it is ignored during loading, where the cloud service handles decryption.
Scoped, file and pre-signed URLs
URLs give access to staged files, mostly unstructured ones, without handing out stage privileges.
| URL type | Function | Lifetime | Who can open it |
|---|---|---|---|
| Scoped URL | BUILD_SCOPED_FILE_URL | Until the query result cache expires (24 hours) | Only the user who generated it |
| File URL | BUILD_STAGE_FILE_URL, or the FILE_URL column of a directory table | Permanent | Roles with USAGE (external) or READ (internal) on the stage, using the REST API |
| Pre-signed URL | GET_PRESIGNED_URL | Set by the expiration argument in seconds | Anyone holding the URL, with no Snowflake login |
On an internal stage with the default SNOWFLAKE_FULL encryption, none of these URLs can read the files. Create the stage with ENCRYPTION = (TYPE = 'SNOWFLAKE_SSE') if files must be served by URL.
Common mistakes
Putting access keys in the stage definition. Use a storage integration, so no secret sits in the object.
Stage URL outside STORAGE_ALLOWED_LOCATIONS. The stage creation fails. Widen the integration or fix the URL.
Forgetting the trust policy step. After CREATE STORAGE INTEGRATION, the cloud role must trust the IAM user and external ID from DESC, or the stage cannot read the bucket.
Trying to change stage encryption with ALTER. It is fixed at creation; create a new stage instead.
Expecting a scoped URL to be shareable. It works only for the user who built it and dies with the result cache.
What to remember for the exam
- Stage references:
@~user,@%tabletable,@namenamed. Table stages cannot use INFER_SCHEMA or directory tables. - A storage integration holds the cloud identity and
STORAGE_ALLOWED_LOCATIONS;CREATE INTEGRATIONis the required privilege. - Internal stage encryption:
SNOWFLAKE_FULL(default) orSNOWFLAKE_SSE, fixed at creation. - External stage encryption:
AWS_CSE,AWS_SSE_S3,AWS_SSE_KMS,GCS_SSE_KMS,AZURE_CSE, orNONE. - Scoped URL: 24 hours, creator only. File URL: permanent, privilege based. Pre-signed URL: anyone with the link until expiry.
Check yourself
What gives Snowflake access to an S3 bucket without storing keys in the stage?
A storage integration whose IAM user and external ID are trusted by an AWS role.
Which URL type needs no Snowflake authentication to open the file?
A pre-signed URL from GET_PRESIGNED_URL, valid until its expiration.
A team wants to serve images from an internal stage by URL. What must be true at stage creation?
The stage must use ENCRYPTION TYPE SNOWFLAKE_SSE, because the default client side encrypted files cannot be read by URLs.
Topic 3 of 17 in Data Movement
Test yourself on Data Movement
The full Snowflake DEA-C02 question bank has 709 questions, with timed practice exams and an explanation for every answer.
This study guide is independent and is not affiliated with, endorsed by, or sponsored by Snowflake. Vendor names, certification names and exam codes are trademarks of their respective owners and are used for identification only.