Snowflake Stages – Internal vs External, Encryption & Directory Tables
Domain 3 starts here. Loading and unloading is 18% of the exam, and almost every question routes through a stage. Today defines what a stage actually is. The privilege-grant trap most candidates miss. And why your presigned URL silently returns “invalid format” when the stage was created with the wrong encryption setting.
Stages were introduced on Day 1 in a single line. They earn a full day here because every loading question on the exam mentions them, and four of the surrounding terms are non-obvious.
| Word you know | What it usually means | What it means in Snowflake |
|---|---|---|
| Stage | A platform at a train station | A named location where data files live before being loaded into a table, or after being unloaded from one. The cloud-storage equivalent of a holding tray. |
| Internal stage | Inside a building | Storage that Snowflake provisions and manages on your behalf. You never see the underlying S3 / Blob / GCS bucket. Three flavours: user, table, and named. |
| External stage | Outside a building | A pointer to a bucket you own in AWS, Azure, or GCP. Snowflake reads and writes files there using credentials you supply, ideally via a storage integration. |
| Directory table | A printed directory in a lobby | An implicit metadata layer on a stage that exposes file name, size, last-modified time, and checksum as queryable columns. Not a separate database object. It lives inside the stage. |
| Pre-signed URL | A pre-printed coupon | A time-limited HTTPS link to a single staged file. Anyone with the link can open it in a browser. No Snowflake login required. The mechanism for sharing files with tools that don’t speak Snowflake. |
| Server-side encryption (SSE) | Lock on the warehouse door | Encryption performed by the storage layer using keys it controls. Contrast with client-side encryption where the file is encrypted on your laptop before upload. The stored file is unreadable to anyone who isn’t a Snowflake client. |
Why the SSE distinction matters today: the difference between client-side and server-side encryption is the single most-tested concept in the URL-sharing portion of Domain 3. Two questions on the C03 exam can ride on whether you know which encryption mode an internal stage uses by default. (Spoiler: it’s not server-side.)
Today’s Concept
Micro-Concept 1: A Stage Is a Named Location for Files
A stage sits between cloud-storage files and Snowflake tables. Files land in a stage; COPY INTO reads them into a table. Unloads run in the other direction: COPY INTO @stage FROM table. Without a stage there is no documented path for bulk load or unload. Snowpipe (Day 32) and Snowpipe Streaming consume stages too.
Every stage falls into one of two top-level categories: internal (Snowflake manages the storage) or external (you point to your own bucket). Internal stages have three sub-flavours. External stages are flat. That is the full taxonomy.
Micro-Concept 2: The Three Internal Stages
Internal stages live inside Snowflake-managed cloud storage. You never see the bucket. There are three kinds, and the prefix in front of the name tells you which:
| Type | Prefix | Created by | Owned by | Best for |
|---|---|---|---|---|
| User stage | @~ | Automatic, one per user | The user | Private files for a single user. Cannot be altered or dropped. No file-format options. |
| Table stage | @%table | Automatic, one per table | Tied to the table | Files destined for a single table when multiple users need to load them. Same name as the table. Cannot be altered or dropped. Apache Iceberg tables don’t use the table-stage pattern (Iceberg ingestion goes through external stages and external volumes). |
| Named internal stage | @name | Explicit CREATE STAGE | A role | Anything that needs shared access across users or tables. The flexible, recommended option. Carries file-format defaults, can be granted to roles, supports directory tables. |
Named internal stages have one quietly important variant: the TEMPORARY stage.
CREATE TEMPORARY STAGE session_only_stage;A TEMPORARY stage exists only for the session that created it. When the session ends, the stage and any files inside are purged automatically. This is the answer to one of the more frequently re-skinned exam stems: “which of the following Snowflake objects can be created as TEMPORARY?” Stages can. Roles, users, and storage integrations cannot. Tables can (covered Day 11) but the stage variant catches more candidates because most people never use it in production.
Micro-Concept 3: External Stages
An external stage is a thin pointer to a bucket you own in AWS S3, Azure Blob Storage, or Google Cloud Storage. Snowflake reads and writes through it using credentials you supply. The recommended way to supply credentials is a storage integration (covered in detail on Day 34). For one-off use, inline credentials work too, but they put long-lived secrets into DDL.
CREATE STAGE my_ext_stage
URL = 's3://my-bucket/inbox/'
STORAGE_INTEGRATION = my_s3_int
FILE_FORMAT = (TYPE = CSV);External stages and named internal stages share most of the same surface area: file-format defaults, directory tables, COPY INTO compatibility. The two differences that show up on the exam are where the files actually live and which privileges you grant on them. The privilege split is in the next micro-concept. It is the highest-leverage fact of the day.
Micro-Concept 4: The Privilege Split (Exam Trap)
Stage privileges are not symmetric between internal and external. This is the standard trap.
| Stage type | Valid privileges | What’s NOT valid |
|---|---|---|
| External stage | USAGE (read or write the bucket) | No READ. No WRITE. Granting either errors out. |
| Internal stage | READ (download / SELECT files), WRITE (upload / PUT files) | No USAGE. Granting it errors out. WRITE requires READ to be granted first. |
The exam writes this as a multi-select. Three of the five distractors will look reasonable in isolation: “USAGE on internal stage” sounds right; it isn’t. “READ on external stage” sounds right; it isn’t either. The split: external = USAGE only, internal = READ/WRITE only. OWNERSHIP is always available and is also the only privilege that lets a role refresh a directory table.
Two operational notes worth tucking away. First, WRITE on an internal stage cannot be granted before READ; the order matters. Second, when revoking, WRITE must come off before or at the same time as READ. Both are documented quirks, both occasionally tested.
Micro-Concept 5: Server-Side Encryption (What It Is and Why It Matters)
Every file in every stage is encrypted. What differs is who holds the key and where the encryption happens. Two settings to know for internal stages:
| Encryption type | Where files are encrypted | External readability via URL | Tri-Secret Secure |
|---|---|---|---|
SNOWFLAKE_FULL (default) | Both client-side (on your machine before PUT) and server-side | ❌ Files appear corrupted to browsers or external tools | ✅ Supported |
SNOWFLAKE_SSE | Server-side only (Snowflake encrypts at rest) | ✅ Files open correctly via pre-signed URLs | ❌ Not supported |
The default for an internal stage is SNOWFLAKE_FULL. The C03 exam tests this with a scenario. A user creates a stage, uploads a file, generates a pre-signed URL, opens it in a browser, and sees an “invalid format” error. The almost-right distractors blame the URL syntax, the certificate, or the relative path. The actual cause is that the stage was created without ENCRYPTION = (TYPE = 'SNOWFLAKE_SSE'). The file is still client-side encrypted, so the browser can’t decrypt it.
-- Correct setup for sharing files via pre-signed URLs
CREATE STAGE my_int_stage
ENCRYPTION = (TYPE = 'SNOWFLAKE_SSE')
DIRECTORY = (ENABLE = TRUE);One more fact and you have the encryption table: you cannot change a stage’s encryption type after the stage is created. If you discover the wrong setting in production, the path is CREATE OR REPLACE STAGE (which drops the directory table state) or a brand-new stage. Plan it once, plan it right.
External stages get server-side encryption from the cloud provider directly (AWS SSE-S3 / SSE-KMS, Azure storage account SSE, GCS SSE-KMS). The ENCRYPTION = () parameter on an external stage tells Snowflake which scheme to use when uploading files. Its read/write logic then aligns with the bucket’s configuration.
Micro-Concept 6: Directory Tables
A directory table is not a separate object. It’s an implicit metadata view that sits on a stage and exposes one row per file. Enable it at stage-creation time, then query it like any other table:
CREATE STAGE files_with_dt
DIRECTORY = (ENABLE = TRUE)
ENCRYPTION = (TYPE = 'SNOWFLAKE_SSE');
-- Query the directory table for that stage
SELECT relative_path, size, last_modified, md5
FROM DIRECTORY(@files_with_dt);Three things to know about how the metadata stays current:
→ Manual refresh. ALTER STAGE files_with_dt REFRESH; walks the stage and rebuilds metadata. Requires OWNERSHIP on the stage.
→ Automated refresh on external stages. Set DIRECTORY = (ENABLE = TRUE, AUTO_REFRESH = TRUE) and configure cloud-side event notifications: S3 sends SQS messages, GCS sends Pub/Sub, Azure sends Event Grid notifications. Files added to the bucket appear in the directory table without manual intervention.
→ Automated refresh on internal stages is AWS-only. If your Snowflake account is hosted on Azure or GCP, internal-stage directory tables must be refreshed manually. This is an unintuitive limitation and a common stumbling point.
What directory tables unlock: a queryable catalog of staged files. The catalog joins to regular tables, feeds Snowpark pipelines, and powers shared views of unstructured data via the URL functions in the next micro-concept.
Micro-Concept 7: Three URLs to a Staged File
Snowflake exposes three flavours of URL to a staged file. They differ on who can open the URL, for how long, and from where.
| URL type | Function | Expires? | Snowflake auth needed to open? |
|---|---|---|---|
| File URL | BUILD_STAGE_FILE_URL | Permanent | Yes (role needs stage privileges) |
| Scoped URL | BUILD_SCOPED_FILE_URL | 24 hours, single user | Yes |
| Pre-signed URL | GET_PRESIGNED_URL | Configurable (default 3600 sec) | No (opens in any browser) |
Pre-signed URLs are the answer when the question mentions an “external reporting tool” or “user without Snowflake access” or “third-party application.” The HTTPS link contains the authorisation token, so anyone holding it can open the file directly.
-- Generate a pre-signed URL valid for 5 minutes
SELECT GET_PRESIGNED_URL(@my_int_stage, 'reports/monthly.pdf', 300);Three numbers to remember about GET_PRESIGNED_URL. Default expiry: 3600 seconds (60 minutes). Maximum: 604800 seconds (7 days) in most cases, capped at 3600 if the stage uses an AWS IAM role for storage access. The privilege requirement is the multi-select trap: the calling role needs USAGE on the external stage, or READ on the internal stage. Either privilege is sufficient on its own. OWNERSHIP also works because owners inherit everything. MODIFY does not exist as a stage privilege at all.
The final required ingredient ties the day together. Server-side encryption must be configured on the stage for any URL function to return a file the recipient can actually open. Default-encryption stages produce URLs that point to client-side-encrypted blobs, which look like “invalid format” to a browser.
Cheat Sheet
| Concept | What to remember | Exam keyword |
|---|---|---|
| Internal stage taxonomy | Three kinds: user (@~), table (@%t), named (@name) | “Three internal stages” |
| User stage | One per user, can’t alter or drop, no file-format options | “@~ user stage” |
| Table stage | One per table, same name as table, can’t alter or drop. Iceberg tables don’t have one | “@%TABLE” |
| TEMPORARY stage | Session-scoped, purged when session ends. Roles, users, and storage integrations have no TEMPORARY variant | “CREATE TEMPORARY STAGE” |
| External stage clouds | AWS S3, Azure Blob, GCS. Only these three | “External stage” |
| Privilege on external stage | USAGE only. No READ/WRITE | “USAGE on external” |
| Privilege on internal stage | READ + WRITE. No USAGE. WRITE requires READ first | “READ/WRITE on internal” |
| Default internal encryption | SNOWFLAKE_FULL (client-side encrypted, unreadable by browsers) | “SNOWFLAKE_FULL default” |
| SSE for unstructured-data URLs | ENCRYPTION = (TYPE = 'SNOWFLAKE_SSE') required for pre-signed URLs | “SNOWFLAKE_SSE” |
| Tri-Secret Secure | Use SNOWFLAKE_FULL. SNOWFLAKE_SSE does NOT support Tri-Secret Secure | “Tri-Secret Secure” |
| Encryption immutable | Encryption type cannot be changed after stage creation | “Can’t change after CREATE” |
| Directory table | Implicit on stage. Enabled with DIRECTORY = (ENABLE = TRUE). Query with DIRECTORY(@stage) | “DIRECTORY(@stage)” |
| Directory refresh | Manual: ALTER STAGE … REFRESH (needs OWNERSHIP). Auto: event notifications, internal-stage auto-refresh is AWS-only | “AUTO_REFRESH” |
| URL types | File (permanent, auth), Scoped (24h, auth), Pre-signed (configurable, no auth) | “Three URL functions” |
| GET_PRESIGNED_URL default | 3600 seconds. Max 604800 (or 3600 with AWS_ROLE). Needs USAGE (external) or READ (internal) | “3600 seconds default” |
Exam Tip
The Domain 3 stages questions are pattern-driven, not recall-heavy. Three patterns surface again and again, and each maps to a specific trap.
First: “Which object can be TEMPORARY?” The answer set always includes Role, User, Storage Integration, and Stage. Only the stage qualifies. Roles, users, and storage integrations are account-level securables with no TEMPORARY variant. If the question asks about load-related objects only, the answer is still Stage.
Second: “Which privileges does role X need to call GET_PRESIGNED_URL?” The trap distractors are MODIFY on stage and OWNERSHIP on the wrong stage type. The documented requirement is USAGE on external stage or READ on internal stage. Both work, and the exam usually expects you to select both as a multi-select. MODIFY doesn’t exist as a stage privilege at all.
Third: “Why does my pre-signed URL fail?” The answer is almost always missing server-side encryption on the stage. Wrong distractors point at certificates, relative paths, or directory-table mismatches. The single sentence: pre-signed URLs require SNOWFLAKE_SSE on the stage; default SNOWFLAKE_FULL is client-side encrypted and breaks browser access.
Hands-On Lab
lab_xs warehouse (Day 1) and day10_orders table (Day 10). Both should still be present.Inspect your user stage and a table stage. Both exist already. You’ve had them since account creation and Day 10 respectively.
USE WAREHOUSE lab_xs;
-- Your personal user stage (always exists, can't drop)
LIST @~;
-- The table stage tied to day10_orders (created automatically with the table)
LIST @%day10_orders;@~ prefix is the user stage; @%table is that table’s stage. Practice Q2 below tests recognition of this @% syntax directly.Create a TEMPORARY internal stage with server-side encryption. Watch how short the DDL is. Internal stages need almost no configuration.
CREATE TEMPORARY STAGE day29_temp
ENCRYPTION = (TYPE = 'SNOWFLAKE_SSE')
COMMENT = 'Session-scoped lab stage for Day 29';
-- Confirm it exists and inspect its properties
SHOW STAGES LIKE 'DAY29_TEMP';
DESC STAGE day29_temp;DESC STAGE shows STAGE_FILE_FORMAT, STAGE_COPY_OPTIONS, STAGE_LOCATION, and the ENCRYPTION property values. Note the URL field starts with s3://, azure://, or gcs:// depending on your cloud. This is the Snowflake-managed bucket you never touch directly. The TEMPORARY scope means this stage will vanish the moment you close this worksheet session.Create a permanent named stage with a directory table. This is the configuration you’d use for sharing unstructured data via pre-signed URLs.
CREATE OR REPLACE STAGE day29_int_dt
DIRECTORY = (ENABLE = TRUE)
ENCRYPTION = (TYPE = 'SNOWFLAKE_SSE')
COMMENT = 'Internal stage with directory table for Day 29';
-- Querying the directory table on an empty stage returns zero rows but the syntax is what matters
SELECT relative_path, size, last_modified
FROM DIRECTORY(@day29_int_dt);
-- Manually refresh the directory metadata (no-op while empty, but proves the command works)
ALTER STAGE day29_int_dt REFRESH;DIRECTORY(@stage) syntax is what the exam writes, not SELECT * FROM @stage and not LIST @stage. The ALTER STAGE … REFRESH command requires OWNERSHIP on the stage. Since you created it, you have OWNERSHIP automatically. On internal stages, automatic event-driven refresh works on AWS only. Azure and GCP internal stages always need manual refresh.Demonstrate the privilege split. Try to grant USAGE on an internal stage and watch Snowflake reject it. This is the single highest-leverage habit-building exercise of the day.
-- Create a throwaway role to test grants against
CREATE OR REPLACE ROLE day29_test_role;
-- This will FAIL: USAGE doesn't apply to internal stages
GRANT USAGE ON STAGE day29_int_dt TO ROLE day29_test_role;
-- Error: SQL compilation error: Unsupported privilege ...
-- This is the correct order: READ first, then WRITE
GRANT READ ON STAGE day29_int_dt TO ROLE day29_test_role;
GRANT WRITE ON STAGE day29_int_dt TO ROLE day29_test_role;
-- Verify the grants landed
SHOW GRANTS ON STAGE day29_int_dt;GRANT USAGE statement errors out with an “unsupported privilege” message. This is the privilege-split trap from Micro-Concept 4 made concrete. For an external stage the situation reverses. The READ and WRITE grants would error and you’d use USAGE instead. Once the rule is known, the exam questions become trivial pattern-matches.Inspect the pre-signed URL syntax. The stage is empty, so the URL won’t resolve to a real file. The function returns a valid-looking URL string regardless, useful for building habit on the signature.
-- 60-second expiry, demonstrating the three positional arguments
SELECT GET_PRESIGNED_URL(@day29_int_dt, 'placeholder.csv', 60) AS url_60s;
-- Default expiry (omit the third argument): 3600 seconds = 60 minutes
SELECT GET_PRESIGNED_URL(@day29_int_dt, 'placeholder.csv') AS url_default;placeholder.csv doesn’t exist. The function does not verify file presence (this is documented behaviour). If you opened the URL in a browser you’d get a NoSuchKey error in XML. The point of this step is the signature: stage reference, relative path string, optional expiry in seconds. Day-30’s COPY INTO commands use the same stage-reference syntax.Sanity-check the encryption immutability rule.
-- This will FAIL: encryption type cannot be altered after creation
ALTER STAGE day29_int_dt SET ENCRYPTION = (TYPE = 'SNOWFLAKE_FULL');
-- Error: cannot modify encryption settingCREATE OR REPLACE STAGE, which drops the directory-table state and breaks any external-table references to the stage. Pick the encryption mode at creation time and move on.Cleanup. Drop the permanent stage and the test role. The TEMPORARY stage from Step 2 will vanish when this session closes, but you can drop it manually to be tidy.
DROP STAGE IF EXISTS day29_temp;
DROP STAGE IF EXISTS day29_int_dt;
DROP ROLE IF EXISTS day29_test_role;
-- Sanity-check that day10_orders is still intact (Day 31, 32, 35 will use it)
SELECT COUNT(*) AS row_count FROM day10_orders;SELECT COUNT(*) should return ~1.5 million rows, confirming Day 10’s persistent table is still in place. Day 31 unloads data from this table to a stage, and Day 32’s Snowpipe lab reuses the table-stage pattern from Step 1.Snowflake Docs
Authoritative references for every fact in today’s post. Bookmark each one. The privilege table and the encryption table are the two pages most worth re-reading the day before the exam.
External References
Two community resources to reinforce today’s stage taxonomy and directory-table mechanics.
Practice Questions
Options:
A. Role
B. Stage
C. User
D. Storage integration
Why B: A stage supports CREATE TEMPORARY STAGE. The stage and any files inside it exist only for the duration of the session that created them. They are purged automatically when the session ends. This is useful for ad-hoc loads where leaving a permanent stage around would be wasteful or create cleanup obligations.
Why not A, C, D: Roles, users, and storage integrations are all account-level securables with no TEMPORARY variant. They persist until explicitly dropped. Tables also support TEMPORARY (Day 11), but in this answer set the only valid choice is the stage.
Options:
A. LIST @T1;
B. LIST @%T1;
C. LIST @~/T1;
D. LIST @~;
Why B: Files unloaded for a specific table live in that table’s implicit table stage, which is referenced using the @% prefix followed by the table name. So @%T1 is the table stage for table T1, and LIST @%T1; lists those unloaded files.
Why not A: @T1 (no prefix character) refers to a named stage called T1, not the table’s implicit stage. If no named stage T1 exists, the command errors.
Why not C and D: @~ is the prefix for the current user’s user stage. @~ alone lists the user stage; @~/T1 just looks for files under a “T1” subpath within that user stage. Neither touches the table stage.
Options:
A. USAGE on external stage
B. MODIFY on internal stage
C. READ on internal stage
D. OWNERSHIP on external stage
E. OWNERSHIP on internal stage
Why A and C: Per the documented privilege model, GET_PRESIGNED_URL requires USAGE on an external stage or READ on an internal stage. These are the minimum required privileges. Either one is sufficient on its own depending on which kind of stage holds the file.
Why not B: MODIFY isn’t even a valid privilege on stages. The stage-specific privilege set is USAGE (external), READ + WRITE (internal), and OWNERSHIP. A “MODIFY on stage” grant errors out at the GRANT statement.
Why not D and E: OWNERSHIP does grant access to call the function (owners inherit everything), but the question asks for the minimum required privileges. USAGE and READ are the documented minimums; OWNERSHIP is stronger than necessary.
Options:
A. There is a mismatch with the server-side security certificate.
B. The server-side encryption is not configured correctly for the stage.
C. The format of the stage’s relative_path that is passed to the URL function is invalid.
D. The relative_path value being passed does not match the value on the directory table.
Why B: A pre-signed URL only opens the file when the stage has server-side encryption configured. The same applies to any URL function output. For internal stages this means ENCRYPTION = (TYPE = 'SNOWFLAKE_SSE'); for external S3 stages it means appropriate SSE settings (SSE-S3 or SSE-KMS) aligned with the bucket. Without the right SSE configuration, the stored file is still client-side encrypted. The recipient’s browser or SFTP tool sees what looks like a corrupted binary, the standard “invalid format” error.
Why not A: A TLS certificate mismatch produces a connection-level error (certificate validation failed, hostname mismatch), not a file-format error.
Why not C: An invalid relative_path produces a NoSuchKey error in S3 XML format, not “invalid format.”
Why not D: A mismatch with the directory table would return an empty result from the URL-generating query. It wouldn’t produce a downstream “invalid format” error on open.
Options:
A. BUILD_SCOPED_FILE_URL
B. GET_PRESIGNED_URL
C. BUILD_STAGE_FILE_URL
D. GET_STAGE_LOCATION
Why B: GET_PRESIGNED_URL generates a time-limited HTTPS URL with the access token baked into the URL itself. Anyone with the URL can open it in any web browser or HTTP client. No Snowflake authentication required. This is precisely what’s needed when an external reporting tool has no Snowflake login.
Why not A: BUILD_SCOPED_FILE_URL produces a scoped URL that requires the calling user to be authenticated to Snowflake. External tools without Snowflake access cannot use it.
Why not C: BUILD_STAGE_FILE_URL produces a permanent file URL that also requires Snowflake authentication and appropriate stage privileges on the recipient’s side.
Why not D: GET_STAGE_LOCATION just returns the cloud-storage location string for the stage (the s3://, azure://, or gcs:// URI). It produces no file URL at all.
Today you learned: Stages are the entry and exit points for bulk data in Snowflake. Internal stages come in three flavours (user @~, table @%t, named @name) and can be TEMPORARY. External stages point at S3, Azure Blob, or GCS via a storage integration. The privilege split (USAGE on external, READ/WRITE on internal) is the highest-leverage habit of the day. Server-side encryption (SNOWFLAKE_SSE) is required for pre-signed URLs to work; the default SNOWFLAKE_FULL is client-side encrypted and breaks browser access. Directory tables are an implicit metadata layer on a stage queried with DIRECTORY(@stage). GET_PRESIGNED_URL defaults to 3600 seconds and is the only URL function that works without Snowflake authentication.
Key takeaway: Almost every loading question references a stage. Three traps account for most of those questions: the TEMPORARY-only object trap (stage), the privilege-grant asymmetry (USAGE vs READ/WRITE), and the SSE-required-for-URLs trap. Get those three right and the rest is detail.
Tomorrow (Day 30): File formats and COPY INTO loading. We’ll cover the format types Snowflake reads (CSV, JSON, Parquet, Avro, ORC, XML). The FILE FORMAT object that bundles parsing options. The ON_ERROR defaults that differ between COPY and Snowpipe (one of the more frequently tested Day 30 facts). And how PURGE, FORCE, and LOAD_UNCERTAIN_FILES change copy behaviour. The stages from today become the source side of every COPY example.
