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';

Invest now in Acorns!!! 🚀
Join Acorns and get your $5 bonus!

Acorns is a micro-investing app that automatically invests your "spare change" from daily purchases into diversified, expert-built portfolios of ETFs. It is designed for beginners, allowing you to start investing with as little as $5. The service automates saving and investing. Disclosure: I may receive a referral bonus.

Invest now!!! Get Free equity stock (US, UK only)!

Use Robinhood app to invest in stocks. It is safe and secure. Use the Referral link to claim your free stock when you sign up!.

The Robinhood app makes it easy to trade stocks, crypto and more.

Webull! Receive free stock by signing up using the link: Webull signup.