Loading CSV Into Snowflake and Handling Errors in COPY

Snowflake's way of loading a CSV has more parts than most, and the parts are what make it dependable. Instead of one command that reads a file, you define a file format, which says what a CSV looks like, a stage, which says where files sit, and a COPY INTO statement, which moves them into a table with a policy for errors. Each part can be reused, inspected and tested on its own. The same structure explains most of the errors people hit, because each error belongs to one of the parts. This article sets up a load, shows the error options and the two tools for finding what went wrong, and ends with the behavior that surprises almost everyone the first time a file is re-sent. It is the Snowflake chapter of loading CSV into cloud warehouses.

The three objects

A file format records the dialect. A load against a stage with this format reads a quoted CSV with a header and treats empty strings and the word NULL as NULL:

CREATE OR REPLACE FILE FORMAT orders_csv
  TYPE = CSV
  FIELD_DELIMITER = ','
  SKIP_HEADER = 1
  FIELD_OPTIONALLY_ENCLOSED_BY = '"'
  NULL_IF = ('', 'NULL')
  ENCODING = 'UTF8';

SKIP_HEADER = 1 ignores the first line (it does not use it for column names; PARSE_HEADER and MATCH_BY_COLUMN_NAME exist for that and need care). FIELD_OPTIONALLY_ENCLOSED_BY is the quote character, and without it a quoted field containing a comma is split. NULL_IF lists strings that become NULL. Then a stage, here an internal one that holds files inside Snowflake and carries the format:

CREATE OR REPLACE STAGE orders_stage FILE_FORMAT = orders_csv;

Files get into an internal stage with PUT, which runs from the SnowSQL client, not from the web interface:

snowsql -q "PUT file:///data/orders-2026-10-02.csv @orders_stage AUTO_COMPRESS=TRUE"

PUT gzips the file by default, so the staged object is orders-2026-10-02.csv.gz. For files already in S3, GCS or Azure Blob, create an external stage that points at the bucket through a storage integration, which keeps credentials out of the SQL, and skip the PUT (loading a CSV from S3 into a database discusses the pattern). Last, the load:

COPY INTO orders
FROM @orders_stage/orders-2026-10-02.csv.gz
ON_ERROR = 'ABORT_STATEMENT';

The target table should exist with typed columns, in the order of the file. As on every warehouse, declare the schema yourself: a zip code column typed as a number loses its zero, so make it VARCHAR. The result of a successful COPY INTO is a row per file with the number of rows parsed and loaded and the first error if any.

What happens on a bad row

ON_ERROR is the policy, and the default for COPY INTO <table> is ABORT_STATEMENT: the first error cancels the statement and nothing from that statement loads. That is the right default for a feed that should be clean, because the failure is loud. The alternatives:

CONTINUE is the one to be careful with. It loads what it can and reports the count, and it is easy to ship a table that is missing 3 percent of its rows because nobody read the result. If you use it, read the errors_seen column of the result and alert when it is not zero, and capture the rejected rows with VALIDATE. SKIP_FILE suits a feed made of many independent files where a bad one should wait for a fix and the rest should flow.

Finding the errors

Two tools find what went wrong without guessing. VALIDATION_MODE checks a file and returns what would have gone wrong, without loading anything:

COPY INTO orders
FROM @orders_stage/orders-2026-10-02.csv.gz
VALIDATION_MODE = RETURN_ERRORS;

The result lists each error with the file, line, column and a message. That is the safest first step with a new feed: validate the file against the real table, read the list and fix the file or the format, then load. After a COPY INTO that skipped rows, the VALIDATE table function returns the errors from that load:

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

_last means the most recent COPY in the session, and a query ID works for an earlier one. The output has the rejected record text, which you can write to a table for review (logging rejected rows from a CSV import for review). The COPY_HISTORY view and table function report file-level results: the status of each file, rows loaded and the first error message, which is the place to look for a feed that has been running for weeks.

The errors you will meet

Most failures fall into a few groups, and each points at one of the three objects.

A column count mismatch. Snowflake reports that the number of columns in the file does not match the table, and mentions the ERROR_ON_COLUMN_COUNT_MISMATCH option, which you can set to FALSE. Do not, at first. The usual causes are a header you did not skip (SKIP_HEADER), a delimiter or quote that does not match the file, or a line with a stray quote that swallowed the next lines. Fix the format, then look at the file (checking row and column counts in a CSV). Setting the option to FALSE makes the load pad or truncate silently, which is the quickest way to put shifted columns into a table.

A value that will not convert. A message that a numeric value such as abc is not recognized, or that a date or timestamp string cannot be parsed, comes from the table's column type meeting a bad value. The file format cannot fix it. Either the data is wrong, in which case reject the row, or the type is, in which case change the column. For dates, DATE_FORMAT and TIMESTAMP_FORMAT in the file format name the layout, and AUTO does a guess you may not want (handling time zones in CSV timestamps).

Encoding. Snowflake expects UTF-8 and the ENCODING option names others. A Windows-1252 file loaded as UTF-8 either errors on an invalid byte or loads wrong characters, so convert it first (converting a CSV from Latin 1 or Windows 1252 to UTF8).

A field too long for the column. A VARCHAR(50) column and a 51-character value fails the row. Declare lengths from the data, or leave the default maximum, since Snowflake does not store padded strings.

The file that is silently skipped

The behavior that surprises everyone is this. Snowflake keeps load metadata for each table, recording which staged files have been loaded, and it keeps it for 64 days. A COPY INTO that sees a file it has already loaded skips it and reports it as loaded zero rows. That is a deduplication feature, and it works against you in one situation: the provider sends a corrected file with the same name, you PUT it over the old one and run COPY, and nothing loads. The status says the file was already loaded.

Three ways forward, each a decision. Give the corrected file a new name, which keeps the history honest and is my preference. Run COPY INTO ... FORCE = TRUE, which loads every file again regardless of history and doubles data for any file that was loaded before, so combine it with a specific file path and with a delete of the earlier rows. Or truncate and reload the table for a snapshot feed. After the 64 days, a file with no history loads again without complaint, so a very old file that you copy back into the stage will load a second time. Your own ledger, keyed by content hash, is the protection that does not expire (idempotent CSV imports that are safe to run twice).

When COPY INTO is not the right load

COPY INTO is for batches of files. For continuous arrival, Snowpipe loads files as they land in a stage, with the same file format and the same options but different defaults (it skips a file on error by default). For rows produced by an application, the Snowpipe Streaming API or a connector sends rows without files. For a one-off with a few thousand rows, the web interface's load wizard is quicker than defining objects. And if the file is Parquet, COPY INTO with MATCH_BY_COLUMN_NAME reads the types from the file and removes most of the CSV questions above.

When the CSV is the only option, treat the file format as part of the contract with the provider, store its definition in version control and test it against a sample whenever a column changes. The references are Snowflake's COPY INTO table page and its guide to loading data from staged files.