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
In a nutshell
  • Stage references: @~ user, @%table table, @name named 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.

FilesCSV, JSON, Parquetland inStageinternal or externalparsed byFile formatTYPE and optionsloaded byPipe or COPY INTOSnowpipeintoTablekey from sequence

How it works

Stages

StageReferenceCreated byKey rules
User stage@~Automatic, one per userOnly that user's files; cannot be altered or dropped
Table stage@%table_nameAutomatic, one per tableLoads only into that table; cannot be altered or dropped
Named internal stage@stage_nameCREATE STAGESchema object, shareable across users and tables with grants
Named external stage@stage_nameCREATE 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.

Exam tip

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:

Table definitionlowest priorityoverridden byStage definitionmiddleoverridden byCOPY INTO statementhighest priority

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, @%table table, @name named 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.
Watch out

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.