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
In a nutshell
  • Stage references: @~ user, @%table table, @name named. Table stages cannot use INFER_SCHEMA or directory tables.
  • A storage integration holds the cloud identity and STORAGE_ALLOWED_LOCATIONS; CREATE INTEGRATION is the required privilege.
  • Internal stage encryption: SNOWFLAKE_FULL (default) or SNOWFLAKE_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.

Cloud roletrust policytrustsStorage integrationallowed locationsreferenced byExternal stageURL and file formatread byCOPY INTO

How it works

Implementing stages and file formats

StageReferenceCreated howGood for
User stage@~Exists for every userPrivate files of one user; no file format options
Table stage@%table_nameExists for every tableFiles for one table; no transformations while loading, not usable with INFER_SCHEMA
Named internal stage@my_stageCREATE STAGEShared team files and Snowpipe REST loads
Named external stage@my_ext_stageCREATE 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.

Exam tip

End an external stage URL with a slash. Without it, s3://bucket/orders also matches s3://bucket/orders_archive/.

Storage integrations

An external stage points at a storage integration instead of holding keys; the integration assumes a cloud IAM role that trusts Snowflake, and only paths in STORAGE_ALLOWED_LOCATIONS can be reached, so a stage on another path is rejected. 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.

CREATE STORAGE INTEGRATIONin SnowflakeDESC returnsIAM user ARNand external IDadded toRole trust policyin AWSthenCREATE STAGEuses the integration
CloudSTORAGE_PROVIDERValues 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 INTEGRATION privilege (by default ACCOUNTADMIN) creates integrations.
  • STORAGE_ALLOWED_LOCATIONS is required; STORAGE_BLOCKED_LOCATIONS is optional and removes paths from the allowed set.
  • The stage URL must sit inside an allowed location, or creating the stage fails.
  • Granting USAGE on the integration lets another role create stages with it. Reading an external stage needs USAGE on the stage; an internal stage needs READ.
  • 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 TYPEBehavior
SNOWFLAKE_FULL (default)Files are encrypted on the client before upload and again on the server
SNOWFLAKE_SSEServer 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.

CloudOptions
Amazon S3AWS_CSE (client side, needs MASTER_KEY), AWS_SSE_S3, AWS_SSE_KMS, NONE
Google Cloud StorageGCS_SSE_KMS, NONE
AzureAZURE_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 typeFunctionLifetimeWho can open it
Scoped URLBUILD_SCOPED_FILE_URLUntil the query result cache expires (24 hours)Only the user who generated it
File URLBUILD_STAGE_FILE_URL, or the FILE_URL column of a directory tablePermanentRoles with USAGE (external) or READ (internal) on the stage, using the REST API
Pre-signed URLGET_PRESIGNED_URLSet by the expiration argument in secondsAnyone holding the URL, with no Snowflake login
Watch out

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, @%table table, @name named. Table stages cannot use INFER_SCHEMA or directory tables.
  • A storage integration holds the cloud identity and STORAGE_ALLOWED_LOCATIONS; CREATE INTEGRATION is the required privilege.
  • Internal stage encryption: SNOWFLAKE_FULL (default) or SNOWFLAKE_SSE, fixed at creation.
  • External stage encryption: AWS_CSE, AWS_SSE_S3, AWS_SSE_KMS, GCS_SSE_KMS, AZURE_CSE, or NONE.
  • 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.