Course contents

SnowPro Core COF-C03 Study GuidePart 4: Data loading and storageStages: Internal vs External

Stages: Internal vs External

Part 4

Data Loading & Storage

Data loading is about 18% of COF-C03. To answer its questions you need to know three things at every step: where the data is (a file on a laptop, a file in a stage, rows in a table), which component moves it, and who pays for that work. Defaults that nobody sets on purpose matter just as much. This part follows one CSV file from Thomas's laptop to a table Alice can query, then covers continuous loading and data that is never loaded at all.

What you'll be able to do after Part 4

  • Say where a file or a row lives at each step of a load, and who pays for the storage and the compute.
  • Write and debug a bulk load with COPY INTO, including error handling and reloads.
  • Choose between bulk COPY, Snowpipe, Snowpipe Streaming and an external table from a scenario.
  • Unload data to files, and catalog unstructured files with a directory table.

The route through this part

  1. StagesWhere files wait, inside or outside Snowflake
  2. File formatsHow bytes become rows
  3. COPY INTO a tableBulk loading, errors, load history
  4. COPY INTO a stageUnloading rows back to files
  5. Semi-structured loadingJSON and Parquet into VARIANT
  6. SnowpipeFiles loaded as they arrive
  7. Snowpipe StreamingRows from apps, no files
  8. External tablesQuery files where they are
  9. Directory tablesA catalog of the files in a stage
  10. Best practicesFile sizing and common mistakes

Key terms

Stage
A named location that holds data files. Files wait in a stage before they're loaded, or after rows are unloaded.
Internal stage
A stage whose files are stored by Snowflake, in Snowflake-managed storage.
External stage
A stage that points at a folder in your own cloud storage (Amazon S3, Azure Blob Storage, Google Cloud Storage). The files stay in your account.
Storage integration
An account-level object that lets Snowflake reach your bucket through a cloud identity, so no secret key is typed in SQL.
File format
The parsing instructions for a file: type (CSV, JSON, Parquet…), delimiter, header, compression.
COPY INTO
The SQL command that loads staged files into a table, or unloads a table or query into files.
Load metadata
Snowflake's record of which files were already loaded into a table, so the same file isn't loaded twice.
Pipe
A schema object that wraps one COPY statement. Snowpipe runs it on staged files, and high-performance Snowpipe Streaming sends rows through it.
Snowpipe
The service that runs a pipe's COPY on serverless compute each time new files are announced.
Snowpipe Streaming
The service that writes rows from an application straight into a table, with no files.
Channel
The connection through which Snowpipe Streaming receives rows. Named channels keep rows in order; elastic channels, managed by Snowflake, don't.
External table
A read-only table over files in an external stage. The data isn't loaded into Snowflake.
Directory table
A catalog of the files in a stage: one row per file, with path, size and URL.
VARIANT
The column type that stores a whole semi-structured value, such as one JSON document.
Every path data takes in and out of Snowflake. Sources outside Snowflake: a local file, a cloud bucket, an application or Kafka. A local file is uploaded with PUT into an internal stage. A cloud bucket is referenced by an external stage, which only points to it. From either stage, COPY INTO on your warehouse or Snowpipe on serverless compute loads the files into a table. Rows from an application skip files and stages and go through a Snowpipe Streaming channel into the table. Unloading reverses the file path: COPY INTO a stage writes files, GET downloads them to a client.OUTSIDE SNOWFLAKESTAGES · FILES WAITLOADED BYDESTINATIONUNLOAD · ROWS BACK TO FILESFILELocal fileThomas's laptopYOUR BUCKETCloud bucketS3 · Azure · GCSSOURCEApp or Kafkarows, no filesSTAGEInternal stagefiles in SnowflakeSTAGEExternal stagea pointer onlyYOUR WAREHOUSECOPY INTO tableyou run itSERVERLESSSnowpipeper GB loadedSERVERLESSStreaming channelrows in secondsTABLETablerows inmicro-partitionsYOUR WAREHOUSECOPY INTO @stagewrites filesFILESFiles in a stageabout 16 MB eachCLIENTGET to a clientinternal stage onlyPUTrows skip the stage

OUTSIDE SNOWFLAKE

FILELocal fileThomas's laptop

YOUR BUCKETCloud bucketS3 · Azure · GCS

SOURCEApp or Kafkarows, no files

PUT

STAGES · FILES WAIT

STAGEInternal stagefiles in Snowflake

STAGEExternal stagea pointer only

rows skip the stage

LOADED BY

YOUR WAREHOUSECOPY INTO tableyou run it

SERVERLESSSnowpipeper GB loaded

SERVERLESSStreaming channelrows in seconds

DESTINATION

TABLETablerows in micro-partitions

UNLOAD · ROWS BACK TO FILES

YOUR WAREHOUSECOPY INTO @stagewrites files

FILESFiles in a stageabout 16 MB each

CLIENTGET to a clientinternal stage only

Figure 4.1 The whole part on one page. Files reach a table through a stage, loaded by COPY on your warehouse or by Snowpipe on serverless compute. Rows from applications skip files and stages through a Snowpipe Streaming channel. Unloading reverses the file path. Each section below zooms into one arrow.

Stages, File Formats & COPY INTO

Stages: Internal vs External

In shortA stage is where files wait. Snowflake never loads a file straight from your disk or your bucket into a table: the file must be reachable through a stage first.

The problem

Tables hold rows. Disks and buckets hold files. Something has to sit between the two, with a name, permissions and a known location, so that COPY knows where to read and where to write. That something is the stage. Keep the two nouns apart (files live in stages, rows live in tables) and most questions in this part answer themselves.

How it works

Files and rows in their places. Outside Snowflake: orders.csv on Thomas's laptop, the bucket s3://ismafrost-landing and an IAM role in IsmaFrost's AWS account. Inside the Snowflake account: the internal stage @RAW_FILES, the external stage @S3_LANDING, the storage integration S3_INT and the table ORDERS. PUT uploads the laptop file into @RAW_FILES. @S3_LANDING points to the bucket and uses S3_INT, which assumes the IAM role. COPY INTO reads files from either stage and writes rows into ORDERS.OUTSIDE SNOWFLAKESNOWFLAKE ACCOUNTFILEorders.csvThomas's laptopCLOUD BUCKETismafrost-landingS3 bucketCLOUD IAM ROLEIAM roletrusts SnowflakeINTERNAL STAGE@RAW_FILESholds FILESEXTERNAL STAGE@S3_LANDINGURL + integrationSTORAGE INTEGRATIONS3_INTno secret in SQLTABLEORDERSholds ROWSPUTpoints toread at COPYCOPY INTOCOPY INTOusesassumes Files and rows in their places. Outside Snowflake: orders.csv on Thomas's laptop, the bucket s3://ismafrost-landing and an IAM role in IsmaFrost's AWS account. Inside the Snowflake account: the internal stage @RAW_FILES, the external stage @S3_LANDING, the storage integration S3_INT and the table ORDERS. PUT uploads the laptop file into @RAW_FILES. @S3_LANDING points to the bucket and uses S3_INT, which assumes the IAM role. COPY INTO reads files from either stage and writes rows into ORDERS.OUTSIDESNOWFLAKESNOWFLAKEACCOUNTFILEorders.csvThomas'slaptopINTERNALSTAGE@RAW_FILESholds FILESCLOUDBUCKETismafrost-landingS3 bucketEXTERNALSTAGE@S3_LANDINGURL +integrationCLOUD IAMROLEIAM roletrustsSnowflakeSTORAGEINTEGRATIONS3_INTno secret in SQLTABLEORDERSholds ROWSPUTpointstoread atCOPYusesassumesCOPY INTOCOPY INTO
Figure 4.2 Files and rows in their places. An internal stage stores files in Snowflake-managed storage. An external stage only points at your bucket, and a storage integration lets Snowflake assume an IAM role in your cloud account to reach it. COPY INTO is the step that turns files into rows.
Where the data isWhat it isStorage billed byProtected by Time Travel?
Thomas's laptopA local fileNobody (his disk)No
Internal stageFiles in Snowflake-managed storageSnowflake, as stage storageNo: a removed staged file is gone
External stageFiles in your bucket; the stage is only a pointerYour cloud providerNo
TableRows in micro-partitionsSnowflake, as table storageYes, for the table's retention period

Internal stages come in three kinds. You upload to them with PUT and download with GET. PUT and GET only run in a client such as SnowSQL, the Snowflake CLI or a driver. Snowsight can still upload files to a named internal stage and download files from internal stages.

StageReferenceScopeNotes
User stage@~One userCreated automatically; can't be altered or dropped; no file format attached; only that user can reach it
Table stage@%ordersOne tableCreated with every table; files here load only into that table; only the table's owner can use it, and nobody else can be granted access; dropped with the table
Named internal stage@raw_filesSchema objectCreated with CREATE STAGE; can carry a file format; READ and WRITE can be granted to roles, so it's the right choice for team pipelines

External stages point at a path in Amazon S3, Azure Blob Storage or Google Cloud Storage. Other systems write the files there with the cloud provider's own tools, so PUT and GET don't apply. Snowflake still needs permission to read the bucket. You could paste an access key into CREATE STAGE, but then the secret sits in DDL and query history. A storage integration avoids that: it stores a cloud identity reference (an IAM role ARN on AWS, a service principal on Azure, a service account on GCP) and the locations Snowflake may use. Snowflake assumes that identity, and no secret is ever typed in SQL. One integration can back many stages, so access is rotated and audited in one place.

Example: Thomas sets up both kinds of stage

-- Once, by ACCOUNTADMIN (or a role with CREATE INTEGRATION)
CREATE STORAGE INTEGRATION s3_int
  TYPE = EXTERNAL_STAGE
  STORAGE_PROVIDER = 'S3'
  ENABLED = TRUE
  STORAGE_AWS_ROLE_ARN = 'arn:aws:iam::123456789012:role/snowflake-landing'
  STORAGE_ALLOWED_LOCATIONS = ('s3://ismafrost-landing/');

DESC INTEGRATION s3_int;   -- note STORAGE_AWS_IAM_USER_ARN and STORAGE_AWS_EXTERNAL_ID
-- Paste both values into the IAM role's trust policy in AWS to finish the handshake.

GRANT USAGE ON INTEGRATION s3_int TO ROLE data_engineer;

USE ROLE data_engineer;
CREATE STAGE sales_db.raw.s3_landing          -- external stage
  URL = 's3://ismafrost-landing/orders/'
  STORAGE_INTEGRATION = s3_int;

CREATE STAGE sales_db.raw.raw_files;           -- named internal stage

LIST @sales_db.raw.s3_landing PATTERN = '.*2026-10.*[.]csv';
SELECT METADATA$FILENAME, METADATA$FILE_ROW_NUMBER, $1, $2, $3
FROM @sales_db.raw.s3_landing (FILE_FORMAT => 'sales_db.raw.csv_std')   -- csv_std is created in the next section
LIMIT 10;

The trust policy is the AWS rule that lets Snowflake's IAM user assume your role, and the external ID makes sure only your integration can use it.

Expected resultLIST returns one row per file with its name, size, MD5 hash and last-modified time. The SELECT reads file contents without loading them: columns are positional ($1, $2…), and METADATA$FILENAME and METADATA$FILE_ROW_NUMBER tell you where each row came from. The SELECT needs a running warehouse because the files are parsed.

Privileges in one sentence: creating a stage needs CREATE STAGE on the schema (plus USAGE on the database and schema) and, for an external stage, USAGE on the integration; using a stage needs USAGE on an external stage, or READ (and WRITE for PUT or unloading) on a named internal stage.

Watch out
  • PUT and GET work with internal stages only.
  • Table stages can't be altered, granted or dropped on their own, and they disappear with the table.
  • Staged files aren't covered by Time Travel. Removing a file from a stage, or dropping a table stage, is final.
  • An answer that types a cloud secret key into CREATE STAGE is the insecure option; a storage integration is the recommended one.

Which stage to use

Named internal stage

Files come from people or scripts that run PUT, and several roles share the pipeline. Grant READ and WRITE to roles.

External stage + integration

Files are already written to a bucket by other systems, or must stay in your cloud account.

User or table stage

Quick personal tests, or files that only ever load into one table. No grants, no sharing.

Exam takeaway
  • Stages hold files; tables hold rows; COPY INTO moves between them.
  • Internal: user @~, table @%t, named @s. External: a pointer to your bucket.
  • Storage integration = a cloud identity Snowflake assumes, so no credentials appear in SQL.