Loading Your Own Data: Stages and COPY INTO

In Snowflake, files don't go straight into tables — they land in a stage first, then COPY INTO loads them. Here's the model, the click-through wizard, and the SQL path that scales.

The first time you try to load a CSV into Snowflake, you’ll look for an INSERT-from-file command and not find one. That’s not an omission — it’s the whole design. Snowflake splits loading into two steps that most databases smear into one: first you get the file near the table, into a holding area called a stage, and then a single command reads from that stage into the table. Once you see why the split exists, everything about loading stops being mysterious.

The reason is the same separation of storage and compute you’ve already met. A stage is just a place files sit in cloud storage; loading them is a compute operation that runs when you say so, at whatever scale you choose. Decoupling the upload from the load means you can stage a hundred files over an afternoon and load them all in one transaction at midnight — or reload them, or validate them first, without re-uploading a byte. It also means the same file can feed two different loads, that a failed load leaves the file exactly where it was, and that Snowflake can parallelize the read across every core in your warehouse without caring how the bytes arrived.

The four kinds of stage

A stage is a named pointer to file storage. There are four flavors, and the only real decision is which one to reach for:

  • User stage (@~) — one per user, created automatically the moment your account exists. Your personal scratch space for files you’re about to load anywhere. No setup, nothing to grant, nothing to drop.
  • Table stage (@%orders) — one per table, also automatic. Files destined for a single table. No setup either, but it can’t be shared across tables and you can’t attach a default file format to it.
  • Named internal stage — one you CREATE STAGE my_stage. A first-class object you can grant privileges on, attach a default file format to, and reuse across loads and users. This is the one you’ll build pipelines on.
  • External stage — a named stage that points at your S3, GCS, or Azure bucket. The files never move into Snowflake-managed storage; Snowflake reads them where they already live, using credentials you configure once.

The first two exist so you can load something in the next five minutes without creating any objects. The last two exist so a team can run the same load repeatedly against a known location. Start with a user or table stage; graduate to named and external when a one-off becomes a habit. You reference a stage by name with the @ sigil — @my_stage, @my_stage/subdir/orders.csv — and you can always look before you load with LIST @my_stage (aliased ls @my_stage), which prints every file, its size, and its MD5.

Getting files in: PUT and GET

For an external stage you never upload anything through Snowflake — the bucket is filled by whatever already writes to it (an application, an ETL job, a partner’s export), and the stage just reads from there. For an internal stage (user, table, or named), you push files up from your machine with PUT:

PUT file:///data/orders.csv @my_stage;

Two things about PUT catch newcomers. First, it cannot run from a Snowsight worksheet — the browser has no access to your local filesystem, so PUT and its download cousin GET only work from SnowSQL, the Snowflake CLI, or a driver (JDBC, ODBC, the Python connector). We cover setting those up in a later chapter on connecting from outside the browser; for now, just know that “why won’t PUT run in the web UI” has a one-word answer: sandbox.

Second, PUT does more than copy. By default it compresses the file with gzip on the way up (AUTO_COMPRESS = TRUE) and encrypts it, so a 400 MB CSV lands as a much smaller .gz and COPY INTO transparently decompresses it on load. It also uploads files in parallel — the PARALLEL parameter (default 4) controls how many threads push at once, which matters when you’re staging a directory of hundreds of files. And PUT will silently skip a file that’s already in the stage unless you pass OVERWRITE = TRUE, so a re-run doesn’t clobber by accident:

PUT file:///data/orders*.csv @my_stage
  PARALLEL = 8
  AUTO_COMPRESS = TRUE
  OVERWRITE = TRUE;

GET is the reverse — it pulls files out of an internal stage down to a local directory, which is how you retrieve the results of an unload (more on that at the end). Same client requirement: not from the browser.

File formats as first-class objects

Snowflake needs to be told how to parse a file, and “how to parse” is a bundle of a dozen settings — delimiter, quote character, header rows, compression, null markers, date formats. You can spell that bundle out inline on every COPY INTO, or you can name it once with CREATE FILE FORMAT and reference it everywhere. Named formats are the source-of-truth move: when the finance team changes their export to pipe-delimited, you edit one object instead of hunting through fifteen copy statements.

CREATE FILE FORMAT csv_orders
  TYPE = CSV
  SKIP_HEADER = 1
  FIELD_OPTIONALLY_ENCLOSED_BY = '"'
  NULL_IF = ('', 'NULL', 'N/A')
  ERROR_ON_COLUMN_COUNT_MISMATCH = TRUE;

CSV is where everyone starts, but TYPE accepts a whole family: CSV, JSON, PARQUET, AVRO, ORC, and XML. The columnar and self-describing formats — Parquet, Avro, ORC — are the ones you actually want for anything large, because they carry their own schema and compress far better than text. Snowflake reads all of them natively.

The structured formats (CSV, Parquet, ORC with a defined schema) map field-by-field into a normal typed table. The semi-structured ones (JSON, Avro, XML, and Parquet when you want the whole record) are usually loaded into a single column of type VARIANT — Snowflake’s schema-flexible type that holds an entire JSON document, object, or array in one cell and lets you query into it with path syntax later. A minimal semi-structured load looks like this:

CREATE FILE FORMAT json_events TYPE = JSON;

CREATE TABLE raw_events (payload VARIANT);

COPY INTO raw_events
FROM @my_stage/events.json
FILE_FORMAT = (FORMAT_NAME = json_events);

That lands each JSON record as one VARIANT row, no columns pre-declared, no schema migration when the shape drifts. Querying into that payload — payload:customer.id, FLATTEN, and friends — is a big enough topic that it gets its own chapter later; for now the load itself is the point: semi-structured data goes in as VARIANT, and the file format tells Snowflake which dialect it’s reading.

The easy path: the wizard

For a single CSV you just want in a table, don’t write any of this. Snowsight has a Load Data into Table wizard that does the staging and the COPY INTO for you behind a few clicks. From a table’s page, hit Load Data, browse to (or drag in) your file, confirm the target table, set or pick a file format (CSV, header row or not, delimiter), and click Load.

Under the hood the wizard is doing exactly the two steps above — putting your file in the table’s stage and running a COPY INTO. It’s the right tool for a one-off import of a smallish file (there’s a size cap of a few hundred megabytes, since the browser is doing the upload). It’s the wrong tool the second time you need to load the same shape of file, because clicking isn’t repeatable and isn’t reviewable. The moment you catch yourself about to click through it twice, switch to SQL.

COPY INTO, in depth

COPY INTO <table> is the workhorse, and it has far more control surface than the one-line version suggests. The skeleton is: a target table, a FROM stage (optionally a specific path or a PATTERN regex to match a subset of files), a file format, and a set of copy options.

COPY INTO orders
FROM @my_stage/orders/
FILE_FORMAT = (FORMAT_NAME = csv_orders)
PATTERN = '.*orders_2026.*[.]csv[.]gz'
ON_ERROR = SKIP_FILE
PURGE = FALSE;

The single most important option is ON_ERROR, which decides what a malformed row does to the whole load:

  • ABORT_STATEMENT — the default for bulk COPY INTO. One bad row anywhere and the entire load rolls back. Nothing is loaded. Strict, and often what you want during development.
  • CONTINUE — load every good row, skip the bad ones, keep going. Maximally permissive; you get the data you can parse and a report of what you couldn’t.
  • SKIP_FILE — if a file has any error, skip that whole file but load the others. The right setting when a file is either good or garbage and you don’t want a half-loaded file.
  • SKIP_FILE_n / SKIP_FILE_n% — skip the file only once it exceeds n error rows, or n percent of its rows. SKIP_FILE_5% means “tolerate a little dirt, bail on a lot.” This is the grown-up option for messy production feeds.

Before you commit to a load, you can dry-run it. VALIDATION_MODE parses the files and reports what would happen without loading a byte:

COPY INTO orders
FROM @my_stage/orders/
FILE_FORMAT = (FORMAT_NAME = csv_orders)
VALIDATION_MODE = 'RETURN_ERRORS';

RETURN_ERRORS lists every row that would fail across all files; RETURN_ALL_ERRORS includes errors from files that were only partially loaded in prior runs; RETURN_n_ROWS just parses the first n rows and shows you what a clean load would produce, which is a fast sanity check on your format settings. After an actual load, the VALIDATE() table function replays the errors from a specific run by query ID:

SELECT * FROM TABLE(VALIDATE(orders, JOB_ID => '_last'));

_last means the most recent load into that table; you can also pass a specific query ID. Between VALIDATION_MODE before and VALIDATE() after, you never have to guess why a load misbehaved.

Idempotency and load metadata

Here’s the behavior that saves the most grief: COPY INTO is idempotent by load history. Snowflake keeps per-table load metadata recording every file it has already loaded — keyed on the file’s name and ETag (a content fingerprint) — and by default it will not load the same file twice. Rerun a COPY INTO after a partial failure and it picks up only files it hasn’t seen; it does not duplicate your rows. This is the bookkeeping table you’d otherwise build by hand, and it’s free.

Two caveats worth internalizing. First, that load metadata expires after 64 days. If you re-stage a file with the same name more than 64 days after its last load, Snowflake no longer remembers it and will load it again — so long-lived staged files can surprise you. Second, “same file” means same name and same ETag: overwrite a staged file with new contents under the same name and Snowflake treats it as new, because the fingerprint changed.

When you want to reload — reprocessing after a schema fix, say — override the guard with FORCE = TRUE, which loads every matched file regardless of history (and will happily create duplicates, which is the point). A few companion options round it out:

  • PURGE = TRUE deletes the staged files automatically after a successful load — handy for keeping a landing area clean, though it makes re-runs impossible since the files are gone.
  • RETURN_FAILED_ONLY = TRUE trims the load result set to just the files that failed, so a thousand-file load doesn’t bury three failures in a wall of “LOADED” rows.

Watching what happened: LOAD_HISTORY and COPY_HISTORY

Every load is recorded, and you can query the record two ways. INFORMATION_SCHEMA.LOAD_HISTORY is a per-database view covering roughly the last 14 days of loads — good for “did last night’s job run, and how many rows?” The COPY_HISTORY table function (and the account-level SNOWFLAKE.ACCOUNT_USAGE.COPY_HISTORY view, which retains about a year) gives the fuller picture, including error counts per file:

SELECT file_name, status, row_count, row_parsed, error_count, first_error_message
FROM TABLE(INFORMATION_SCHEMA.COPY_HISTORY(
  TABLE_NAME => 'ORDERS',
  START_TIME => DATEADD('hour', -24, CURRENT_TIMESTAMP())
));

That’s your audit trail: what loaded, what got skipped, and the first thing that went wrong in each file.

External stages, done properly

Pointing a stage at an S3 bucket takes one line — STORAGE_INTEGRATION = s3_orders — and that one line glosses over the only genuinely fiddly setup in this chapter. A storage integration is a Snowflake object that holds the trust relationship between your account and a cloud bucket, so you never paste an access key into SQL. You create it once, wire up IAM on the cloud side, and every external stage that references it inherits the credentials.

CREATE STORAGE INTEGRATION s3_orders
  TYPE = EXTERNAL_STAGE
  STORAGE_PROVIDER = 'S3'
  ENABLED = TRUE
  STORAGE_AWS_ROLE_ARN = 'arn:aws:iam::123456789012:role/snowflake-load'
  STORAGE_ALLOWED_LOCATIONS = ('s3://my-bucket/orders/');

The trick is that the trust is mutual and you set it up in two passes. After creating the integration, you run DESC INTEGRATION s3_orders and read two generated values back out: STORAGE_AWS_IAM_USER_ARN and STORAGE_AWS_EXTERNAL_ID. Those are Snowflake’s identity and a shared secret. You paste them into the trust policy of the AWS IAM role you named, so AWS will let Snowflake assume that role — and only Snowflake, only for that external ID. Then the external stage is credential-free:

CREATE STAGE my_ext_stage
  URL = 's3://my-bucket/orders/'
  STORAGE_INTEGRATION = s3_orders
  FILE_FORMAT = csv_orders;

GCS and Azure follow the same shape with provider-specific fields (a service account email for GCS, a tenant and consent grant for Azure). The payoff is that rotating or revoking access is a cloud-IAM operation, not a scramble to find every stage with a hardcoded key.

Transforming during the load

You don’t have to load files straight into an identically shaped table. COPY INTO accepts a SELECT over the stage, which lets you reorder columns, drop ones you don’t want, cast types, and compute values on the way in. Staged CSV columns are referenced positionally as $1, $2, and so on:

COPY INTO orders (order_id, customer_id, amount, loaded_at)
FROM (
  SELECT $1, $3, $2::NUMBER(10,2), CURRENT_TIMESTAMP()
  FROM @my_stage/orders.csv
)
FILE_FORMAT = (FORMAT_NAME = csv_orders);

Note the source file has amount in column 2 and customer in column 3, but the table wants them the other way around — the SELECT reorders them, casts the amount, and stamps a load timestamp the file never contained. This “COPY-with-transform” is Snowflake’s answer to the light reshaping you’d otherwise do in a staging table.

For the self-describing formats, there’s an even nicer shortcut. MATCH_BY_COLUMN_NAME tells COPY INTO to line up columns by name rather than position, reading the names out of the Parquet or JSON schema:

COPY INTO orders
FROM @my_stage/orders.parquet
FILE_FORMAT = (TYPE = PARQUET)
MATCH_BY_COLUMN_NAME = CASE_INSENSITIVE;

The CASE_INSENSITIVE versus CASE_SENSITIVE choice matters more in Snowflake than elsewhere, because of how it folds identifiers. Snowflake uppercases unquoted identifiers: a column you created as order_id is stored as ORDER_ID, while a Parquet file written by Spark probably has a lowercase order_id. Match those case-sensitively and nothing lines up; CASE_INSENSITIVE bridges the gap and is what you almost always want. (NONE, the default, falls back to positional matching.)

A worked load, rejected rows and all

Let’s run the whole thing with a file that isn’t clean. Say orders.csv has 1,000 rows, and three of them are broken: one has a non-numeric amount ("twelve"), one is missing a trailing column, and one has an unescaped quote. First, stage it and dry-run:

CREATE STAGE load_demo FILE_FORMAT = csv_orders;
-- from SnowSQL:  PUT file:///data/orders.csv @load_demo OVERWRITE = TRUE;

COPY INTO orders
FROM @load_demo/orders.csv
VALIDATION_MODE = 'RETURN_ERRORS';

VALIDATION_MODE loads nothing and returns three rows, each with an ERROR, the offending LINE, the COLUMN_NAME, and the raw REJECTED_RECORD — so you can see that line 118 has twelve where a number belongs and line 640 is short a field. You decide the file is mostly good and you’ll tolerate a little loss:

COPY INTO orders
FROM @load_demo/orders.csv
FILE_FORMAT = (FORMAT_NAME = csv_orders)
ON_ERROR = CONTINUE;

The result set reports 997 rows loaded, 3 errors, status PARTIALLY_LOADED. The 997 good rows are now in the table; the 3 bad ones were skipped. To see exactly which, replay them:

SELECT error, line, character, column_name, rejected_record
FROM TABLE(VALIDATE(orders, JOB_ID => '_last'));

You fix those three rows in the source, re-stage the file with OVERWRITE = TRUE (which changes its ETag), and re-run the COPY INTO. Because the ETag changed, Snowflake sees a “new” file and loads it — but now you’d double-load the 997 good rows. So instead, the clean pattern is to fix only the three rows into a small correction file and load that, letting load metadata keep the original 997 out of your way. This is the daily reality of loading: the metadata guard and ON_ERROR together let you converge on a complete, non-duplicated table without ever reloading the whole thing.

Continuous loading with Snowpipe

Everything so far is a load you trigger. When files arrive continuously — a stream of exports dropping into S3 every few minutes — you don’t want to babysit COPY INTO. Snowpipe wraps a copy statement in a pipe object that ingests files automatically as they land:

CREATE PIPE orders_pipe
  AUTO_INGEST = TRUE
AS
  COPY INTO orders
  FROM @my_ext_stage
  FILE_FORMAT = (FORMAT_NAME = csv_orders);

With AUTO_INGEST = TRUE, you wire your cloud bucket’s event notifications (S3 → SNS/SQS, GCS → Pub/Sub, Azure → Event Grid) to the pipe, and every new object triggers a load within a minute or so — no warehouse, no schedule, no cron. Snowpipe runs on Snowflake-managed serverless compute — it never touches a warehouse of yours, and it’s billed on its own meter, per GB ingested. (If you’ve read that Snowpipe charges per file, that model is retired; the next chapter covers the current one, and why it changes how big you should make your files.) (One difference from bulk copy worth flagging: Snowpipe’s default ON_ERROR is SKIP_FILE, not ABORT_STATEMENT.) It’s the same COPY INTO you already understand, just triggered by the arrival of a file instead of by you.

The reverse direction: unloading

COPY INTO also runs backward. COPY INTO <location> — a stage or bucket instead of a table — unloads query results to files, which is how you export from Snowflake:

COPY INTO @my_stage/export/
FROM (SELECT * FROM orders WHERE order_date >= '2026-01-01')
FILE_FORMAT = (TYPE = CSV HEADER = TRUE)
MAX_FILE_SIZE = 100000000
SINGLE = FALSE;

By default Snowflake splits the output across many files for parallelism; SINGLE = TRUE forces one file, MAX_FILE_SIZE caps each chunk, HEADER = TRUE writes column names, and PARTITION BY can fan the output into a folder tree by a column’s value. Then you GET the files down from an internal stage, or read them straight out of the external bucket. Load and unload are the same verb pointed in opposite directions — once you know one, you know both.

Final thoughts

The stage feels like a detour the first time — an extra place your data has to sit before it’s really loaded. It isn’t. It’s the seam that lets loading be repeatable, resumable, and reviewable, which is the difference between a data import you did once and a data pipeline your team can rely on. The pieces stack cleanly: a stage decides where files live, a file format decides how they’re read, COPY INTO decides what happens to the good and bad rows, load metadata keeps you from doubling up, and Snowpipe turns the whole thing continuous when files start arriving on their own. Use the wizard when you mean “just this once.” Reach for CREATE STAGE, a named file format, and a checked-in COPY INTO the moment you catch yourself about to click through the wizard a second time.

Next: Ingestion That Never Sleeps: Snowpipe, Streaming, and the Way Back Out

Comments