Data Loading, Unloading, and Connectivity · Topic 2 of 7

Internal and External Stages, Encryption and Directory Tables

Compare user, table and named internal stages with external stages, pick the right server-side encryption type, and use directory tables for staged files.

  • 5 min
In a nutshell
  • User stage @~, table stage @%table, named stage @name. Only named stages are grantable objects.
  • User and table stages cannot be altered or dropped. Table stages do not support COPY transformations.
  • External stages point to S3, GCS or Azure; authenticate with a storage integration.

What it is

A stage is the place Snowflake reads files from when loading and writes files to when unloading. An internal stage keeps the files in storage that Snowflake manages. An external stage points at your own cloud storage: an Amazon S3 bucket, a Google Cloud Storage bucket or an Azure container.

Stages also carry settings: an optional file format, the encryption type for the files, and an optional directory table that catalogs every staged file so you can query the list with SQL.

FilesPUTInternal stageSnowflake storageCOPY INTOTableCloud bucketS3, GCS, Azurereferenced byExternal stageURL plus integrationCOPY INTOTable

How it works

The three internal stage types

StageReferenceCreated byBest forLimits
User stage@~Every user gets one automaticallyFiles one user loads into several tablesCannot be altered or dropped; no file format options on the stage
Table stage@%my_tableEvery table gets one automaticallyFiles many users load into one tableCannot be altered or dropped; no transformations during COPY; needs table ownership to list or remove files
Named internal stage@my_stageCREATE STAGERegular loads shared by many users and tablesA database object: grant privileges, transfer ownership

Named stages are the recommended choice for regular loads, and the only internal stage you can grant to roles.

CREATE STAGE my_stage
  FILE_FORMAT = (FORMAT_NAME = 'my_csv')
  ENCRYPTION = (TYPE = 'SNOWFLAKE_SSE')
  DIRECTORY = (ENABLE = TRUE);

External stages

An external stage stores a URL and the way to authenticate to it. The recommended way to authenticate is a storage integration, so no keys appear in the stage definition.

CREATE STAGE s3_stage
  URL = 's3://my-bucket/landing/'
  STORAGE_INTEGRATION = my_s3_int
  FILE_FORMAT = (TYPE = PARQUET);

The files stay in your cloud account. You manage them with cloud tools, because PUT and GET work only with internal stages.

Commands you use on any stage

  • LIST @stage shows staged files with size and last modified time.
  • SELECT $1, $2 FROM @stage (FILE_FORMAT => 'my_csv') queries staged files without loading them.
  • REMOVE @stage/path/ deletes staged files; PURGE = TRUE in COPY removes them after a successful load.

Server-side encryption

All files in internal stages are encrypted. You choose the type when you create the stage, and you cannot change it later.

StageEncryption typesDefault
InternalSNOWFLAKE_FULL (client side and server side), SNOWFLAKE_SSE (server side only)SNOWFLAKE_FULL
External on S3AWS_SSE_S3, AWS_SSE_KMS (optional KMS key ID), AWS_CSE (client side with a master key), NONENONE
External on GCSGCS_SSE_KMS (optional KMS key ID), NONENONE
External on AzureAZURE_CSE (client side with a master key), NONENONE

With SNOWFLAKE_FULL, files are also client side encrypted, so a file downloaded through a URL is unreadable. Use SNOWFLAKE_SSE when users or applications need to open staged files through file URLs, such as unstructured documents and images.

Watch out

The encryption type of an internal stage is fixed at creation. If you later need readable file URLs, you must create a new stage with SNOWFLAKE_SSE and move the files.

For external stages, the encryption setting tells Snowflake how the files are encrypted in your bucket, so it can read them and write unloaded files the same way.

Directory tables

A directory table is an implicit object stored with a stage. It holds one row per staged file with metadata such as RELATIVE_PATH, SIZE, LAST_MODIFIED, MD5, ETAG and FILE_URL. You enable it with DIRECTORY = (ENABLE = TRUE) on an internal or external stage.

SELECT relative_path, size, file_url
FROM DIRECTORY(@my_stage)
WHERE relative_path LIKE '%.pdf';

The metadata must be refreshed to see new files.

  • ALTER STAGE my_stage REFRESH updates it manually.
  • On external stages, AUTO_REFRESH = TRUE uses cloud event notifications to update it as files arrive. Auto refresh on internal stages is a preview feature limited to AWS accounts.
New filein bucketevent notificationDirectory tableauto refreshqueryDIRECTORY functionfile listbuild URLFile URLscoped or presigned

Directory tables are the basis of unstructured data work in Snowflake. Combine them with the file URL functions:

FunctionURL typeWho can use it
BUILD_SCOPED_FILE_URLScoped URL that expires after 24 hoursOnly the user who generated it
BUILD_STAGE_FILE_URLFile URL that does not expireAny role with privileges on the stage
GET_PRESIGNED_URLPresigned URL with a chosen expirationAnyone with the link, no Snowflake login

You can also create a stream on a directory table to process each new file once, which pairs well with tasks.

Exam tip

When a question says "query a list of staged files with SQL" or "track which documents arrived", the answer is a directory table. When it says "share a file with someone who has no Snowflake account", it is a presigned URL.

Common mistakes

Trying to drop a user or table stage. They exist for every user and table and cannot be altered or dropped. Drop the table and its stage goes with it.

Transforming data from a table stage. A COPY that uses a SELECT to reorder or cast columns does not work from a table stage. Use a named stage.

Expecting a directory table to update itself on an internal stage. Without auto refresh, run ALTER STAGE ... REFRESH after new uploads, or the new files will not appear.

Putting credentials in the stage. Stages can take raw keys, but a storage integration keeps secrets out of SQL and lets admins control allowed locations.

What to remember for the exam

  • User stage @~, table stage @%table, named stage @name. Only named stages are grantable objects.
  • User and table stages cannot be altered or dropped. Table stages do not support COPY transformations.
  • External stages point to S3, GCS or Azure; authenticate with a storage integration.
  • Internal stage encryption: SNOWFLAKE_FULL (default) or SNOWFLAKE_SSE; it cannot be changed after creation.
  • Directory tables catalog staged files; enable with DIRECTORY = (ENABLE = TRUE), refresh manually or with auto refresh on external stages.
  • Scoped URL: 24 hours, same user. File URL: permanent, needs privileges. Presigned URL: no login, set expiration.

Check yourself

  • Which internal stage should you use if many users load files into several tables?

    A named internal stage, because privileges on it can be granted to roles and it can load into any table.

  • A team needs readable file URLs for PDFs in an internal stage. What encryption type should the stage use?

    SNOWFLAKE_SSE, server side only, because SNOWFLAKE_FULL files are also client side encrypted and unreadable when downloaded.

  • How do you list all staged files with their sizes using a SQL query?

    Enable a directory table on the stage and query it with SELECT ... FROM DIRECTORY(@stage).

Topic 2 of 7 in Data Loading, Unloading, and Connectivity

Test yourself on Data Loading, Unloading, and Connectivity

The full Snowflake SnowPro Core COF-C03 question bank has 845 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.