Finding and fixing what went wrong when a data load fails

Troubleshooting Snowflake data ingestion means using the right metadata views and functions to see why a COPY INTO or Snowpipe load failed, was skipped, or loaded unexpected data. Snowflake exposes load status through COPY_HISTORY, LOAD_HISTORY, and pipe-specific functions rather than traditional logs, so knowing where to look and what each source retains is essential for diagnosis.

1 · Learn the must-know

• 1Use the INFORMATION_SCHEMA.COPY_HISTORY table function (or ACCOUNT_USAGE.COPY_HISTORY for longer retention) to see file-level load status, row counts, and error messages for both COPY INTO and Snowpipe loads.
• 2SYSTEM$PIPE_STATUS() reports a pipe's execution state, pending file count, and last error, which is the first place to check when Snowpipe seems stalled.
• 3The VALIDATE function or VALIDATION_MODE copy option lets you preview which rows/files would fail before actually loading data, without writing to the table.
• 4Snowflake tracks loaded file names/checksums for 64 days per table (load metadata), so a previously loaded file with the same name/path is skipped unless FORCE=TRUE is specified.
• 5ON_ERROR (CONTINUE, SKIP_FILE, SKIP_FILE_<n>, ABORT_STATEMENT) determines whether a batch load stops or continues on row errors, and controls how much of the file is skipped versus loaded.
• 6For Snowpipe specifically, cloud provider notification failures, missing SQS/Event Grid/Pub-Sub permissions, or stage/notification integration misconfiguration are common root causes and are visible via PIPE_USAGE_HISTORY and pipe error notifications (if configured).

2 · Check your understanding