Stages, COPY, and Snowpipe

Stages and file formats

Internal stages live inside Snowflake: a user stage (@~), a table stage (@%table_name), or a named stage you create. External stages point at Amazon S3, Azure Blob Storage, or Google Cloud Storage that you manage.

File formats cover CSV and other delimited text, JSON, Avro, ORC, Parquet, and XML. Name the format once and reuse it in COPY, so a skipped header or a quote character is not retyped in every load.

A directory table on a stage lists the files themselves. That is the door into unstructured data, covered again in the Iceberg lesson. Enabling it does not by itself parse CSV into columns.

COPY INTO is the batch load

COPY INTO a table reads staged files with a running warehouse. ON_ERROR chooses whether one bad row aborts the file or is skipped. VALIDATION_MODE can preview errors without committing a load. After a successful load, Snowflake remembers which files it already loaded for that table, unless you tell COPY to force them.

Unloading is the reverse: COPY INTO @stage FROM table. Bulk unload to delimited files and JSON is on every edition.

CREATE OR REPLACE FILE FORMAT ff_csv
  TYPE = CSV
  SKIP_HEADER = 1
  FIELD_OPTIONALLY_ENCLOSED_BY = '"';

CREATE OR REPLACE STAGE stg_orders
  FILE_FORMAT = ff_csv;

COPY INTO orders
FROM @stg_orders
FILE_FORMAT = (FORMAT_NAME = ff_csv)
ON_ERROR = ABORT_STATEMENT;

Snowpipe and Snowpipe Streaming

Snowpipe is continuous micro-batch loading and is listed for every edition. A pipe wraps a COPY statement. AUTO_INGEST = TRUE waits for a notification from the storage cloud. The notification service is different on AWS, Azure, and Google Cloud, so follow the auto-ingest page for the cloud you chose. Snowpipe compute is serverless. It is not the same bill as LEARN_WH.

Snowpipe Streaming loads rows, not files. Snowflake's current overview describes a high-performance architecture and says the older classic architecture is planned for future deprecation. Use the high-performance path for new work. Elastic Channels are the recommended start: Snowflake manages channels, delivery is at-least-once, and order is not guaranteed. Named Channels are the ordered, exactly-once option and use offset tokens. The docs describe throughput up to 20 GB/s per table and ingest-to-query latency as low as about 5 seconds. Treat those as documented ceilings, not a promise for every account and region.

Fail-safe does not protect tables that contain data loaded by the classic streaming architecture. If you still have classic channels, read that limitation before you count on Fail-safe.

Lab: format, stage, and a one-row load

This lab stays inside SQL. It creates a format and an internal stage, then loads a row you insert into a staged file using a COPY INTO from a SELECT, so you do not need a local file uploader. When you do have a CSV, upload it with SnowSQL PUT and point COPY at the stage.

USE WAREHOUSE LEARN_WH;
USE DATABASE LEARN_DB;
USE SCHEMA PUBLIC;

CREATE OR REPLACE FILE FORMAT ff_csv
  TYPE = CSV
  SKIP_HEADER = 1
  FIELD_OPTIONALLY_ENCLOSED_BY = '"';

CREATE OR REPLACE STAGE stg_orders
  FILE_FORMAT = ff_csv;

COPY INTO @stg_orders/orders.csv
FROM (SELECT 'order_id', 'region', 'amount'
      UNION ALL
      SELECT '4', 'west', '8')
FILE_FORMAT = (TYPE = CSV FIELD_OPTIONALLY_ENCLOSED_BY = '"')
OVERWRITE = TRUE;

CREATE OR REPLACE TABLE orders_loaded (
  order_id NUMBER,
  region STRING,
  amount NUMBER(10, 2)
);

COPY INTO orders_loaded
FROM @stg_orders/orders.csv
FILE_FORMAT = (FORMAT_NAME = ff_csv)
ON_ERROR = ABORT_STATEMENT;

SELECT * FROM orders_loaded;

Mark lesson complete

Concept summary

• Named, user, and table stages are internal. External stages point at your bucket.
• A file format freezes CSV or Parquet options for COPY and Snowpipe.
• COPY uses a warehouse. Snowpipe is serverless micro-batch. Streaming loads rows.
• Prefer the high-performance streaming architecture. Classic is on a deprecation path and has a Fail-safe gap.

Knowledge check

Official sources

Checked against Snowflake documentation in October 2026. Product behavior still depends on your edition, cloud, and region.

• Snowpipe
• Snowpipe Streaming
• COPY INTO table
• Fail-safe