Stages

Overview

A stage is a named storage location where you load or unload data files on their way into or out of Snowflake tables. It exists to simplify and streamline bulk loading and unloading — rather than every job inventing its own file-handling, files land in a stage and COPY INTO moves them.

A stage is either internal — stored inside Snowflake — or external, pointing at a location in a cloud provider's object storage. Snowflake's own stated best practice is to create a stage object to manage ingestion workload rather than improvising per job.

The three kinds

Two of the three are handed to you automatically and are always internal. Only the third is an object you create, and only the third can point outside Snowflake.

How to address each one

The prefix is not notation for the slide — it is the syntax. Every stage command takes one of these three forms:

Both automatic stages exist the moment their owner does, and both are empty until something puts a file in them — which is why the first two LIST calls in the walkthrough return nothing rather than an error. An empty result there means the stage is real and unused; an error would mean the table or user does not exist.

Table stages

• Linked to one specific table. When a table is created, a corresponding stage is created with it.
• Named after the table, and owned and managed by the table owner — whoever owns the table owns its stage. There is no separate grant to make.
• The convenient option when files need to be accessible to multiple users but copied into a single table.

User stages

• The default stage created automatically for each user. Create a user, and that user has one.
• The convenient choice for the mirror-image case: files accessed by a single user but copied into multiple tables.
• Referenced as @~, and cannot be altered or dropped. It is part of the user, not a thing beside them.

So the two automatic stages divide the work by which side is singular. A table stage is one table, many people. A user stage is one person, many tables. Neither can be both.

Named stages

A named stage is a proper database object — it lives in a schema, exactly like a table or a view — and it gives the greatest degree of flexibility for loading. Unlike a personal user stage or a table-specific stage, it can be accessed by multiple authorized users and used to load into several tables. It removes both restrictions at once, which is why it is the one you create deliberately.

It also has capabilities the two automatic stages do not, which the source leaves out and which are the real substance of "greatest flexibility": a named stage can carry a default FILE FORMAT and copy options so every load does not restate them, can have a directory table enabled over it, and supports transforming data during a load. Table and user stages support none of those.

Named and external

An external stage is a named stage whose storage is a location in a cloud provider rather than inside Snowflake. It works in both directions: load data from the external location into Snowflake tables, or export data from Snowflake tables out to an external destination.

External stages are supported on AWS S3, Azure Blob Storage and Google Cloud Storage — all three, as the slide says. The narration omits Azure; it is supported.

One thing the source defers rather than states: an external stage needs credentials to reach that bucket. In practice that means a storage integration object — a named, reusable trust relationship between the Snowflake account and the cloud account — rather than keys pasted into the stage definition. That is the part that takes a cloud administrator, and the reason external stages are usually set up once per environment rather than per job.

Setting up the walkthrough

USE ROLE ACCOUNTADMIN;
USE WAREHOUSE COMPUTE_WH;
USE SCHEMA MY_DB.MY_SCHEMA;

CREATE OR REPLACE TABLE CUSTOMER (
  ID    INT,
  NAME  VARCHAR(50),
  AGE   INT,
  STATE VARCHAR(50)
);

-- The table's own stage already exists, and is empty.
LIST @%CUSTOMER;

-- So does the current user's, and it is empty too.
LIST @~;

The file being loaded is a comma-delimited customer.csv whose header row is ID,NAME,AGE,STATE — the same shape as the table — followed by five rows.

Route one: the console

A stage is a schema-level object, so the console puts it where schema objects live: Data → Databases → MY_DB → MY_SCHEMA → Create → Stage.

1. Choose Snowflake Managed for an internal named stage, or External Stage to point at a bucket. This walkthrough takes the first.
2. Name it — CUSTOMER_STAGE — confirm the schema, and pick an encryption mode: client-side or server-side. The demo uses server-side.
3. Open the new stage, click + Files, and browse or drag customer.csv in.

Then load it from the table's side: open CUSTOMER → Load Data → Add from stage, pick the database, schema, stage and file, then Next. Snowflake inspects the file, infers that it is CSV, and proposes a column mapping — ID→ID, NAME→NAME and so on. Confirm and load: five rows.

Worth noticing: the console flow never asks you to skip the header. The inference step reads the header row to build the mapping, and then excludes it. The SQL route has no such inference, which is why SKIP_HEADER = 1 has to be written out by hand below.

Route two: SQL

The demo drops the console-made stage first — which also removes the file inside it — and rebuilds the same thing in SQL.

DROP STAGE CUSTOMER_STAGE;              -- takes the staged file with it

CREATE OR REPLACE STAGE CUSTOMER_STAGE; -- an internal named stage

-- Upload the file, then confirm what is actually in there:
LIST @CUSTOMER_STAGE;
-- name | size | md5 | last_modified

-- COPY INTO keeps per-file load metadata, so re-loading the same file
-- would be skipped as already loaded. TRUNCATE clears that metadata
-- along with the rows.
TRUNCATE TABLE CUSTOMER;

COPY INTO CUSTOMER
  FROM @CUSTOMER_STAGE
  FILE_FORMAT = (TYPE = CSV SKIP_HEADER = 1);
-- status LOADED, rows_loaded 5

SELECT * FROM CUSTOMER;                 -- the five rows from the file

The step the SQL route cannot do

The walkthrough creates the stage in SQL and then goes back to the console to put the file in it, without saying why. The reason is worth knowing: uploading into an internal stage is PUT, and PUT reads a file from the client's filesystem — so it runs from SnowSQL, a driver or a connector, and not from a browser worksheet, which has no access to your disk.

-- From SnowSQL or any driver session, not from a web worksheet:
PUT file:///path/to/customer.csv @CUSTOMER_STAGE AUTO_COMPRESS = TRUE;

So the file browser in the console is not a convenience alternative to SQL — for a browser-only session it is the only way in. Any automated pipeline uses PUT or writes to an external stage's bucket directly.

Directory tables

The demo clicks "enable directory table to see the file" and moves on. A directory table is a metadata layer over a stage that exposes its file names, sizes and last-modified times as something queryable — the catalogue of the stage rather than the contents of the files. The console's file browser reads it, which is why the file only appears after it is switched on, and you can query it yourself:

SELECT * FROM DIRECTORY(@CUSTOMER_STAGE);

The load metadata, precisely

This is the part of the walkthrough most likely to be mis-remembered, because the narration reaches the right action for a garbled reason — "truncate the table so that metadata of that table will be lost". Stated properly:

• COPY INTO records, per file per table, that a file has been loaded, and keeps that record for 64 days.
• A second COPY INTO of the same file into the same table is therefore skipped, not duplicated — which is a feature: re-running a load is safe.
• TRUNCATE TABLE clears that load metadata as well as the rows, which is what makes the demo's second load work. DELETE FROM does not — empty the table that way and the reload is still skipped.
• The other way through is FORCE = TRUE on the COPY INTO, which loads regardless. That duplicates rows if the table was not emptied, so it is a deliberate choice rather than a workaround.

Staged files are not temporary

The definition says files sit in a stage "temporarily", and that word is the one thing on the slide that can cost money. Nothing expires a staged file. A file uploaded to an internal stage stays there, and is billed as Snowflake storage, until something removes it:

REMOVE @CUSTOMER_STAGE/customer.csv;

-- or have the load clean up after itself:
COPY INTO CUSTOMER
  FROM @CUSTOMER_STAGE
  FILE_FORMAT = (TYPE = CSV SKIP_HEADER = 1)
  PURGE = TRUE;

"Temporary" describes the intent — a file is passing through on its way to a table — not the behaviour. An ingestion pipeline that never purges accumulates every file it ever loaded.

Choosing a stage

What this means for a migration

Every row this platform loads into Snowflake goes through a stage — but no generated code contains the word. The Snowflake target block in app/ai/etl/dialect_rules.py mandates one library and forbids hand-rolling around it:

Library: snowflake-connector-python + pandas_tools.write_pandas

`write_pandas` handles staging, chunked upload, and TRUNCATE+INSERT
automatically when `overwrite=True`.
Do NOT manually TRUNCATE or executemany — use write_pandas exclusively.

So the stage is real but invisible: write_pandas writes the DataFrame to Parquet, puts it into an internal stage, issues the COPY INTO and cleans up. The overwrite=True path is the same load-metadata problem as the walkthrough's, solved the same way — truncate, then load — one layer down.

The bug that lives in that hidden Parquet

Because the staging file is generated rather than written by hand, its format is a place bugs can hide. Rule TC-SF-7 exists for exactly one of them, and it is the worst-behaved failure recorded anywhere in the load rules:

At its default of use_logical_type=False, write_pandas stages a datetime64[ns] column into that internal Parquet as a plain INT64 nanosecond count with no TIMESTAMP annotation — so COPY INTO sees a number, not a time. What that costs depends only on the target column's scale:

That second row is why this page matters to the platform at all. The stage is where the source's types stop being the source's problem and become the file format's — and a reconciliation that counts rows cannot see through it. Every write_pandas call carrying a timestamp column must pass use_logical_type=True.

Two slips in the source

• External stages are said to support "AWS and GCP". The slide lists three and it is right: AWS, Azure and GCP. Azure Blob Storage is fully supported.
• "Truncate the table so that metadata of that table will be lost" — the action is right and the reason is muddled. What is being cleared is the load metadata of the file, which records that this file has already been copied into this table. See the section above.

Alongside those, the two additions worth carrying away are that staged files persist and are billed until removed, and that PUT — not the console — is how a real pipeline gets a file into an internal stage.

Where this page comes from

The three kinds, their properties, the reference prefixes and both walkthrough routes are Snowflake's own introductory material on stages, with the two misstatements above corrected. The external-stage setup itself is deferred in that source to a later session; the storage-integration note here is the one line needed to make sense of why. The customer.csv rows are not shown on screen, so only its shape is described.

The migration section is this platform's own: the library mandate and rule TC-SF-7, including the two failure modes verbatim, are in the Snowflake target block of app/ai/etl/dialect_rules.py.

See also

• Table Types — external tables read through an external stage, and are the read-only sibling of loading through one.
• Databases and Schemas — stages are in the schema's object list, which is why a named stage needs a schema and the other two do not.
• Snowflake — the rest of the load rules, and the identifier-case incident that came out of the same file.
• Official Docs — the verified link set, by topic.