Snowflake AI Data Cloud Features & Architecture · Topic 4 of 17
Stages, File Formats, Pipes and Sequences
Know user, table, named internal and external stages, named file formats, Snowpipe pipe objects and sequences, with the defaults COF-C03 questions test.
- 5 min
- Stage references:
@~user,@%tabletable,@namenamed internal or external. - External stages point to S3, GCS or Azure; use a storage integration for credentials.
- PUT uploads and GET downloads work only with internal stages.
What it is
Four schema-level objects support moving and identifying data. A stage is a location for data files. A file format describes how to parse those files. A pipe wraps a COPY INTO statement so Snowpipe can load files automatically. A sequence generates unique numbers, often for surrogate keys.
This page covers what each object is and how they relate. The loading process itself (COPY options, Snowpipe tuning, unloading) is covered in the data loading chapter.
How it works
Stages
| Stage | Reference | Created by | Key rules |
|---|---|---|---|
| User stage | @~ | Automatic, one per user | Only that user's files; cannot be altered or dropped |
| Table stage | @%table_name | Automatic, one per table | Loads only into that table; cannot be altered or dropped |
| Named internal stage | @stage_name | CREATE STAGE | Schema object, shareable across users and tables with grants |
| Named external stage | @stage_name | CREATE STAGE ... URL = 's3://...' | Points to Amazon S3, Google Cloud Storage or Azure storage |
Internal stages keep files in Snowflake-managed storage. You upload with PUT from a client (for example Snowflake CLI or a driver) and download with GET. PUT and GET do not work on external stages, because those files are managed in your own cloud storage.
External stages should authenticate through a storage integration, an account-level object that holds the cloud identity, so no access keys are stored in the stage definition. LIST @stage_name shows the files in any stage.
When a question says "files must be loaded into several tables by several users", choose a named stage. User and table stages are tied to one user or one table.
File formats
A named file format stores parsing options so you can reuse them across stages and COPY statements. The TYPE can be CSV (the default), JSON, AVRO, ORC, PARQUET or XML.
Common options include FIELD_DELIMITER, SKIP_HEADER, NULL_IF, FIELD_OPTIONALLY_ENCLOSED_BY and COMPRESSION (default AUTO).
File format options can be set in three places. If they conflict, the most specific wins:
Pipes
A pipe is a named object containing one COPY INTO statement. Snowpipe uses it to load new files from a stage shortly after they arrive, using serverless compute managed by Snowflake, not a user warehouse.
CREATE PIPE sales_pipe AUTO_INGEST = TRUE AS
COPY INTO sales FROM @sales_ext_stage FILE_FORMAT = (FORMAT_NAME = 'csv_fmt');
Snowpipe learns about new files in one of two ways:
- Auto-ingest: cloud event notifications (for example Amazon S3 event notifications, Azure Event Grid or Google Cloud Pub/Sub) tell the pipe a file has arrived. Set
AUTO_INGEST = TRUE. - REST API: an application calls the Snowpipe REST endpoint with the list of files to load.
Snowpipe keeps load metadata for each pipe for 14 days, so the same file is not loaded twice. You can pause a pipe with ALTER PIPE ... SET PIPE_EXECUTION_PAUSED = TRUE and check it with SYSTEM$PIPE_STATUS.
Sequences
A sequence returns unique, increasing (or decreasing) numbers. You read the next value with seq_name.NEXTVAL, often as a column default.
CREATE SEQUENCE order_seq START = 1 INCREMENT = 1;
CREATE TABLE orders (id NUMBER DEFAULT order_seq.NEXTVAL, item STRING);
Sequence values are unique but not guaranteed gap-free. Concurrent sessions, rolled back statements and caching can leave gaps. Snowflake does not support reading a "current value"; each call to NEXTVAL returns a new number.
An AUTOINCREMENT or IDENTITY column works the same way, using an internal sequence for that column.
How the exam asks about this
- "Files are stored in an S3 bucket the company controls, and credentials must not be embedded in SQL" -> named external stage with a storage integration.
- "New files must load within minutes of arrival without a running warehouse" -> a pipe with AUTO_INGEST and cloud event notifications.
- "The CSV delimiter set in COPY INTO differs from the stage definition" -> the COPY INTO setting wins.
Common mistakes
Running PUT against an external stage. PUT uploads only to internal stages. For external stages, files are written with the cloud provider's own tools.
Expecting a pipe to use your warehouse. Snowpipe runs on Snowflake-managed serverless compute and is billed separately from warehouses.
Relying on sequences for consecutive numbers. Sequences guarantee uniqueness, not continuity. Do not use them where an auditor expects no gaps.
Trying to drop a table stage. User and table stages are implicit. They exist as long as the user or table exists.
What to remember for the exam
- Stage references:
@~user,@%tabletable,@namenamed internal or external. - External stages point to S3, GCS or Azure; use a storage integration for credentials.
- PUT uploads and GET downloads work only with internal stages.
- File format TYPE defaults to CSV; also JSON, AVRO, ORC, PARQUET, XML.
- Format precedence: COPY INTO, then stage, then table.
- A pipe holds one COPY INTO statement; Snowpipe runs it on serverless compute via auto-ingest or the REST API.
- Snowpipe load metadata prevents reloading the same file for 14 days.
- Sequences use NEXTVAL; values are unique but may have gaps.
A table stage can load only into its own table and has no grantable privileges of its own. For shared, controlled access to files, the answer is a named stage.
Check yourself
Which stage type should be used when several users load the same files into different tables?
A named internal or external stage, because user and table stages are tied to one user or one table.
What two methods can trigger a Snowpipe load?
Cloud event notifications with AUTO_INGEST = TRUE, or calls to the Snowpipe REST API listing the files.
Can a sequence guarantee there are no gaps between values?
No. Sequence values are unique, but gaps can occur and are expected.
Topic 4 of 17 in Snowflake AI Data Cloud Features & Architecture
Test yourself on Snowflake AI Data Cloud Features & Architecture
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.