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
- StagesWhere files wait, inside or outside Snowflake
- File formatsHow bytes become rows
- COPY INTO a tableBulk loading, errors, load history
- COPY INTO a stageUnloading rows back to files
- Semi-structured loadingJSON and Parquet into VARIANT
- SnowpipeFiles loaded as they arrive
- Snowpipe StreamingRows from apps, no files
- External tablesQuery files where they are
- Directory tablesA catalog of the files in a stage
- 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.
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
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
| Where the data is | What it is | Storage billed by | Protected by Time Travel? |
|---|---|---|---|
| Thomas's laptop | A local file | Nobody (his disk) | No |
| Internal stage | Files in Snowflake-managed storage | Snowflake, as stage storage | No: a removed staged file is gone |
| External stage | Files in your bucket; the stage is only a pointer | Your cloud provider | No |
| Table | Rows in micro-partitions | Snowflake, as table storage | Yes, 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.
| Stage | Reference | Scope | Notes |
|---|---|---|---|
| User stage | @~ | One user | Created automatically; can't be altered or dropped; no file format attached; only that user can reach it |
| Table stage | @%orders | One table | Created 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_ | Schema object | Created 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_intTYPE = EXTERNAL_STAGE STORAGE_PROVIDER = 'S3' ENABLED = TRUESTORAGE_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. ) -- csv_std is created in the next sectionraw. csv_ std' 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.
- 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.
- 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.

