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
- 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.
How it works
The three internal stage types
| Stage | Reference | Created by | Best for | Limits |
|---|---|---|---|---|
| User stage | @~ | Every user gets one automatically | Files one user loads into several tables | Cannot be altered or dropped; no file format options on the stage |
| Table stage | @%my_table | Every table gets one automatically | Files many users load into one table | Cannot be altered or dropped; no transformations during COPY; needs table ownership to list or remove files |
| Named internal stage | @my_stage | CREATE STAGE | Regular loads shared by many users and tables | A 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 @stageshows 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 = TRUEin 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.
| Stage | Encryption types | Default |
|---|---|---|
| Internal | SNOWFLAKE_FULL (client side and server side), SNOWFLAKE_SSE (server side only) | SNOWFLAKE_FULL |
| External on S3 | AWS_SSE_S3, AWS_SSE_KMS (optional KMS key ID), AWS_CSE (client side with a master key), NONE | NONE |
| External on GCS | GCS_SSE_KMS (optional KMS key ID), NONE | NONE |
| External on Azure | AZURE_CSE (client side with a master key), NONE | NONE |
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.
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 REFRESHupdates it manually.- On external stages,
AUTO_REFRESH = TRUEuses cloud event notifications to update it as files arrive. Auto refresh on internal stages is a preview feature limited to AWS accounts.
Directory tables are the basis of unstructured data work in Snowflake. Combine them with the file URL functions:
| Function | URL type | Who can use it |
|---|---|---|
BUILD_SCOPED_FILE_URL | Scoped URL that expires after 24 hours | Only the user who generated it |
BUILD_STAGE_FILE_URL | File URL that does not expire | Any role with privileges on the stage |
GET_PRESIGNED_URL | Presigned URL with a chosen expiration | Anyone 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.
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) orSNOWFLAKE_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.