What is a stage in Snowflake?

A named location Snowflake can read files from. Internal stages live in storage Snowflake manages and are loaded with `PUT`; external stages point at your own S3, GCS or Azure bucket. Either way, `COPY INTO` reads from the stage into a table.

Does re-running COPY INTO load the same rows twice?

No. Snowflake records the files it has already loaded per table and skips them for 64 days, so a repeated `COPY INTO` reports zero rows loaded. Use `FORCE = TRUE` to override that, which is exactly how accidental duplicates get created.

What is the difference between COPY INTO and Snowpipe?

`COPY INTO` is a statement you run on your own warehouse, in batches. Snowpipe watches a stage and loads files continuously on Snowflake-managed compute, billed per file rather than per second. Use COPY for scheduled batches and Snowpipe for a steady trickle of files.

How do I handle bad rows in a file?

`ON_ERROR` decides: `ABORT_STATEMENT` fails the whole load, `SKIP_FILE` drops the file, and `CONTINUE` loads the good rows and reports the rest. Run `VALIDATION_MODE = RETURN_ERRORS` first to see what would break without loading anything.

Loading Data: Stages and COPY INTO

Loading is two steps in Snowflake: get files into a stage, then COPY INTO a table from that stage. Both steps are worth understanding separately, because most load problems are really staging problems.

Stages

Stage	Written with	Use for
User `@~`	`PUT`	ad-hoc files, one person
Table `@%orders`	`PUT`	files belonging to one table
Named internal	`PUT`	shared, reusable, with a file format attached
Named external	your own S3/GCS/Azure tooling	anything a pipeline writes

create or replace file format csv_standard
    type = csv
    field_delimiter = ','
    skip_header = 1
    field_optionally_enclosed_by = '"'
    null_if = ('', 'NULL', 'null')
    empty_field_as_null = true
    date_format = 'YYYY-MM-DD';

create or replace stage bookshop_stage
    file_format = csv_standard;

+-------------------------------------------------+
| status                                          |
|-------------------------------------------------|
| File format CSV_STANDARD successfully created.  |
+-------------------------------------------------+
1 Row(s) produced. Time Elapsed: 0.244s

+---------------------------------------------+
| status                                      |
|---------------------------------------------|
| Stage area BOOKSHOP_STAGE successfully created. |
+---------------------------------------------+
1 Row(s) produced. Time Elapsed: 0.288s

Defining the file format once and attaching it to the stage means every COPY INTO from that stage inherits it. The alternative — repeating the format options in every load statement — is how two pipelines end up parsing dates differently.

Getting a file in

# orders_2026_01.csv
order_id,customer_id,ordered_at,status,amount
1001,1,2026-01-04,completed,25.50
1002,2,2026-01-05,completed,12.00
1003,1,2026-01-07,returned,40.00

put file:///home/you/data/orders_2026_01.csv @bookshop_stage auto_compress = true;

+---------------------+------------------------+-------------+-------------+--------------------+--------------------+----------+---------+
| source              | target                 | source_size | target_size | source_compression | target_compression | status   | message |
|---------------------+------------------------+-------------+-------------+--------------------+--------------------+----------+---------|
| orders_2026_01.csv  | orders_2026_01.csv.gz  |      124500 |       31204 | NONE               | GZIP               | UPLOADED |         |
+---------------------+------------------------+-------------+-------------+--------------------+--------------------+----------+---------+
1 Row(s) produced. Time Elapsed: 1.842s

PUT runs from the client, not the server, so it works from SnowSQL and the drivers but not from the web UI worksheet. auto_compress gzips before upload — 124 KB became 31 KB, and you pay for the transfer either way.

list @bookshop_stage;

+-------------------------------------+-------+----------------------------------+------------------------------+
| name                                |  size | md5                              | last_modified                |
|-------------------------------------+-------+----------------------------------+------------------------------|
| bookshop_stage/orders_2026_01.csv.gz | 31216 | 8f14e45fceea167a5a36dedd4bea2543 | Wed, 9 Sep 2026 10:14:02 GMT |
+-------------------------------------+-------+----------------------------------+------------------------------+
1 Row(s) produced. Time Elapsed: 0.402s

Look before you load

A stage is queryable. This is the most under-used feature in Snowflake ingestion:

select $1, $2, $3, $4, $5
from @bookshop_stage/orders_2026_01.csv.gz
limit 3;

+------+-----+------------+-----------+-------+
| $1   | $2  | $3         | $4        | $5    |
|------+-----+------------+-----------+-------|
| 1001 | 1   | 2026-01-04 | completed | 25.50 |
| 1002 | 2   | 2026-01-05 | completed | 12.00 |
| 1003 | 1   | 2026-01-07 | returned  | 40.00 |
+------+-----+------------+-----------+-------+
3 Row(s) produced. Time Elapsed: 0.911s

Columns are positional — $1, $2 — because a staged file has no schema. Checking the column order here takes ten seconds and prevents the classic failure where amount and customer_id are swapped and every row loads happily with nonsense values.

COPY INTO

copy into orders
from @bookshop_stage/orders_2026_01.csv.gz;

+--------------------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+
| file                                 | status | rows_parsed | rows_loaded | error_limit | errors_seen | first_error | first_error_line |
|--------------------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------|
| bookshop_stage/orders_2026_01.csv.gz | LOADED |        5000 |        5000 |           1 |           0 | NULL        |             NULL |
+--------------------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+
1 Row(s) produced. Time Elapsed: 2.104s

rows_parsed and rows_loaded are equal, which is what you want. When they differ, the gap is your data quality problem.

Run it again:

+-------------------------------------------------+
| status                                          |
|-------------------------------------------------|
| Copy executed with 0 files processed.           |
+-------------------------------------------------+
1 Row(s) produced. Time Elapsed: 0.388s

Zero files. Snowflake remembers, per table, which files it has already loaded and skips them for 64 days. That makes COPY INTO naturally idempotent — a retried pipeline step does not duplicate rows.

The escape hatch is the thing to be careful with:

copy into orders from @bookshop_stage/orders_2026_01.csv.gz force = true;

| bookshop_stage/orders_2026_01.csv.gz | LOADED |        5000 |        5000 |

Five thousand duplicate rows, reported as success. FORCE = TRUE belongs in a deliberate reload after a truncate, never in a scheduled job.

Transforming during the load

copy into orders (order_id, customer_id, ordered_at, status, amount)
from (
    select
        $1::number,
        $2::number,
        $3::date,
        lower($4::string),
        $5::number(10,2)
    from @bookshop_stage/orders_2026_01.csv.gz
)
on_error = 'CONTINUE';

+--------------------------------------+--------+-------------+-------------+-------------+
| file                                 | status | rows_parsed | rows_loaded | errors_seen |
|--------------------------------------+--------+-------------+-------------+-------------|
| bookshop_stage/orders_2026_01.csv.gz | PARTIALLY_LOADED |  5000 |        4996 |           4 |
+--------------------------------------+--------+-------------+-------------+-------------+

PARTIALLY_LOADED — four rows failed and 4,996 went in. That status is easy to miss in a log, so check it explicitly in any pipeline that uses CONTINUE.

Finding the bad rows

Validate before loading anything:

copy into orders
from @bookshop_stage/orders_2026_01.csv.gz
validation_mode = 'RETURN_ERRORS';

+---------------------------------+-------------------------------+------+-----------+-------------+
| ERROR                           | FILE                          | LINE | CHARACTER | COLUMN_NAME |
|---------------------------------+-------------------------------+------+-----------+-------------|
| Numeric value 'n/a' is not recognized | orders_2026_01.csv.gz   |  412 |        38 | AMOUNT      |
| Date '2026-13-04' is not recognized   | orders_2026_01.csv.gz   |  877 |        22 | ORDERED_AT  |
| Numeric value '' is not recognized    | orders_2026_01.csv.gz   | 1203 |        38 | AMOUNT      |
| Numeric value 'n/a' is not recognized | orders_2026_01.csv.gz   | 3980 |        38 | AMOUNT      |
+---------------------------------+-------------------------------+------+-----------+-------------+
4 Row(s) produced. Time Elapsed: 1.688s

Line numbers, columns and reasons, with nothing written. Two of these are fixable in the file format — adding 'n/a' to null_if handles three of the four rows:

alter file format csv_standard set null_if = ('', 'NULL', 'null', 'n/a', 'N/A');

+----------------------------------+
| status                           |
|----------------------------------|
| Statement executed successfully. |
+----------------------------------+

`ON_ERROR`	Behaviour
`ABORT_STATEMENT` (default)	first error fails the whole load, nothing committed
`CONTINUE`	load good rows, report the rest
`SKIP_FILE`	abandon a file at the first error, keep other files
`SKIP_FILE_<n>`	abandon a file after n errors

Auditing loads

select
    file_name,
    status,
    row_count,
    error_count,
    last_load_time
from information_schema.load_history
where table_name = 'ORDERS'
order by last_load_time desc
limit 3;

+--------------------------+------------------+-----------+-------------+-------------------------------+
| FILE_NAME                | STATUS           | ROW_COUNT | ERROR_COUNT | LAST_LOAD_TIME                |
|--------------------------+------------------+-----------+-------------+-------------------------------|
| orders_2026_01.csv.gz    | PARTIALLY_LOADED |      4996 |           4 | 2026-09-09 10:22:41.221 -0700 |
| orders_2025_12.csv.gz    | LOADED           |      4812 |           0 | 2026-09-08 10:19:03.884 -0700 |
| orders_2025_11.csv.gz    | LOADED           |      4630 |           0 | 2026-09-07 10:18:55.102 -0700 |
+--------------------------+------------------+-----------+-------------+-------------------------------+
3 Row(s) produced. Time Elapsed: 0.522s

Alert on ERROR_COUNT > 0 here. It is the cheapest data-quality check available and it needs no extra tooling.

External stages and Snowpipe

create or replace stage bookshop_s3
    url = 's3://bookshop-data/orders/'
    storage_integration = bookshop_s3_int
    file_format = csv_standard;

create or replace pipe orders_pipe
    auto_ingest = true
as
    copy into orders from @bookshop_s3;

+---------------------------------------+
| status                                |
|---------------------------------------|
| Pipe ORDERS_PIPE successfully created.|
+---------------------------------------+
1 Row(s) produced. Time Elapsed: 0.402s

select system$pipe_status('orders_pipe');

+-------------------------------------------------------------------------------+
| SYSTEM$PIPE_STATUS('ORDERS_PIPE')                                             |
|-------------------------------------------------------------------------------|
| {"executionState":"RUNNING","pendingFileCount":0,"lastIngestedTimestamp":"...} |
+-------------------------------------------------------------------------------+
1 Row(s) produced. Time Elapsed: 0.288s

Snowpipe runs on Snowflake-managed compute billed per file, so it is cheaper than keeping a warehouse awake for a trickle of files and more expensive than one big batch. The rule of thumb: files arriving every few minutes suit Snowpipe, an hourly batch suits COPY INTO.

Use a storage integration rather than embedding keys in the stage definition — credentials in DDL end up in query_history, readable by anyone who can see it.

Unloading

copy into @bookshop_stage/export/revenue_
from (select country_code, sum(amount) as revenue from orders join customers using (customer_id) group by 1)
file_format = (type = csv compression = gzip)
header = true
overwrite = true;

+---------------------+-------------+--------------+
| rows_unloaded       | input_bytes | output_bytes |
|---------------------+-------------+--------------|
|                   2 |          64 |           98 |
+---------------------+-------------+--------------+
1 Row(s) produced. Time Elapsed: 0.911s

get @bookshop_stage/export/ file:///home/you/exports/;

+---------------------------+------+------------+---------+
| file                      | size | status     | message |
|---------------------------+------+------------+---------|
| revenue_0_0_0.csv.gz      |   98 | DOWNLOADED |         |
+---------------------------+------+------------+---------+
1 Row(s) produced. Time Elapsed: 0.688s

Practice

1. Query a staged file before loading it.

select $1 as order_id, $5 as amount
from @bookshop_stage/orders_2026_01.csv.gz
where try_cast($5 as number) is null;

+----------+--------+
| ORDER_ID | AMOUNT |
|----------+--------|
| 1412     | n/a    |
| 3980     | n/a    |
+----------+--------+
2 Row(s) produced. Time Elapsed: 0.844s

try_cast returns null instead of failing, which turns a staged file into something you can profile for bad values before a single row is committed.

2. Load a file twice without FORCE.

| bookshop_stage/orders_2026_01.csv.gz | LOADED | 5000 | 5000 |
Copy executed with 0 files processed.

The second run is a no-op. This is why a COPY INTO step is safe to retry after a network failure — a property worth relying on deliberately rather than rediscovering.

3. Break a row and run with VALIDATION_MODE.

+---------------------------------------+------+-----------+-------------+
| ERROR                                 | LINE | CHARACTER | COLUMN_NAME |
|---------------------------------------+------+-----------+-------------|
| Numeric value 'twelve' is not recognized |   3 |        38 | AMOUNT      |
+---------------------------------------+------+-----------+-------------+
1 Row(s) produced. Time Elapsed: 0.688s

Nothing was loaded. Running validation as a separate pipeline step, before the real load, turns a 3am failure into a morning ticket with the line number already in it.

4. Load with ON_ERROR = 'CONTINUE' and reconcile the counts.

select
    (select count(*) from orders) as loaded,
    (select sum(row_count) from information_schema.load_history where table_name='ORDERS') as expected;

+--------+----------+
| LOADED | EXPECTED |
|--------+----------|
|   4996 |     4996 |
+--------+----------+
1 Row(s) produced. Time Elapsed: 0.402s

Matching, but four rows short of the file. CONTINUE trades completeness for progress — acceptable only when something downstream reports the gap, otherwise the missing rows are invisible forever.

Next: time travel and zero-copy cloning — undoing mistakes and branching a database for free.