How does the COPY INTO command work and what file formats does it support?

COPY INTO is Snowflake's bulk data loading command. It reads files from a stage (internal or external) and loads them into a target table in a single transactional operation. The command tracks which files have already been loaded using a 64-day load metadata store — by default, re-running COPY INTO on the same files is a no-op, preventing accidental duplicates.

Supported file formats: CSV, JSON, Avro, ORC, Parquet, and XML. Format options are specified inline or via a named FILE FORMAT object. Key ON_ERROR options control how errors are handled:

• ON_ERROR = ABORT_STATEMENT (default) — rolls back the entire load on any error.
• ON_ERROR = CONTINUE — skips bad rows and loads the rest.
• ON_ERROR = SKIP_FILE — skips an entire file that contains any error.
• PURGE = TRUE — deletes source files from the stage after a successful load.
• FORCE = TRUE — reloads files even if already loaded (ignores load metadata).
• VALIDATION_MODE = RETURN_ERRORS — dry-run; validates files without loading.

COPY INTO orders
FROM @my_s3_stage/orders/
FILE_FORMAT = (
  TYPE = 'CSV'
  FIELD_OPTIONALLY_ENCLOSED_BY = '"'
  SKIP_HEADER = 1
  NULL_IF = ('NULL', 'null', ''))
ON_ERROR = 'CONTINUE'
PURGE = TRUE;

-- Validate without loading
COPY INTO orders FROM @my_s3_stage
VALIDATION_MODE = 'RETURN_ERRORS';