To preserve malformed rows from a Snowflake bulk load, inspect the target table’s COPY history, validate the affected files with VALIDATION_MODE = RETURN_ALL_ERRORS, then unload the validation output’s REJECTED_RECORD values to a stage file. Validation checks the files without loading rows; the exported file gives you records to investigate and correct.
What COPY load errors tell you—and what they leave out
Start with the load’s file-level history to find the affected files, their status, and the reported first error. Snowflake’s bulk-load troubleshooting guide explains that status distinguishes files that loaded, partially loaded, or failed. The first-error field is only a clue: if a file has multiple issues, it reports only the first.
As an Amazon Associate I earn from qualifying purchases.
With ON_ERROR = CONTINUE, Snowflake keeps processing despite detected errors. The COPY result reports at most one error per data file. Comparing rows parsed with rows loaded can indicate rows with detected errors, but it does not count every error: a single row may contain more than one. For fuller row-level diagnostics, validate the files or use the VALIDATE table function. See the COPY INTO reference and the troubleshooting guide.
Windows Errors? Fix Them Before They Spread
Repair common Windows errors and clear accumulated junk for a smoother, more stable PC - no reinstall needed.Free scan · no reinstallCrashes, No Sound, or Screen Glitches?
Random freezes, missing sound and display glitches usually trace back to one bad driver. Find and replace yours safely.Free scan · under a minuteCapture and offload rejected records
Use the same file set that produced the load issue. Adapt the table, stage, file path, and output path to your account and load attempt.
#1 Best Overall
- Validate without loading. Run a COPY validation with
VALIDATION_MODE = RETURN_ALL_ERRORS.RETURN_ERRORSreturns errors across the specified files;RETURN_ALL_ERRORSalso includes errors from files partially loaded earlier withON_ERROR = CONTINUE. - Save the validation query ID immediately. The result is the input to the unload step. Snowflake cautions that when using
LAST_QUERY_ID(), the statements need to run in succession so the ID refers to the validation result. - Unload the rejected-record field. Select
REJECTED_RECORDfrom the validation result usingRESULT_SCAN, then write it to a stage location withCOPY INTO <location>. - Reconcile and remediate. Keep the output tied in your own pipeline records to the original load attempt and file set, then correct the source or apply an explicit remediation process. Snowflake documents the validation-and-unload sequence; the reconciliation practice is an operational safeguard, not a platform guarantee.
COPY INTO mytable
FROM @mystage/myfile.csv.gz
VALIDATION_MODE = RETURN_ALL_ERRORS;
SET qid = LAST_QUERY_ID();
COPY INTO @mystage/errors/load_errors.txt
FROM (SELECT rejected_record FROM TABLE(RESULT_SCAN($qid)));This pattern follows Snowflake’s documented bulk-load troubleshooting example. Validation does not insert rows into the target table. The unload writes the problematic records to a text file so they can be analyzed and fixed in the original data files.
Choose an error policy for the load itself
Error policy controls what happens during loading; it does not replace a rejected-record preservation workflow. The behaviors below are described in Snowflake’s COPY INTO reference.
| Policy | Load behavior | Diagnostic and operational trade-off |
|---|---|---|
ABORT_STATEMENT |
Default behavior; stops when an error is encountered. | Useful when you want the statement to stop rather than continue loading valid rows after an error. Plan how to inspect and recover the batch. |
CONTINUE |
Continues loading good rows despite detected errors. | The COPY summary reports no more than one error per file, so follow with validation or VALIDATE when fuller diagnostics are needed. |
SKIP_FILE |
Discards a file when an error is found. | Snowflake buffers the entire file; this may be slower than CONTINUE or ABORT_STATEMENT, especially if a large file contains only a few bad rows. |
Know when validation is not a fit
- Transformed COPY loads:
VALIDATION_MODEdoes not support COPY statements that transform data, and theVALIDATEfunction also does not support those transformation statements. Use a diagnostic path designed for the transformed pipeline instead of assuming this workflow captures every failure. Snowflake’s transform-data-during-load guide also describes limitations in error handling involving scalar SQL UDFs. - Iceberg tables:
VALIDATION_MODEis not supported for Iceberg tables. - Parquet conversions: COPY does not validate data type conversions for Parquet files, so validation is not a universal semantic data-quality check.
- Special load patterns: Snowflake documents possible inconsistent or unexpected
ON_ERRORbehavior in some cases, includingDISTINCTin a SELECT and clustered tables. It also notes anON_ERRORcaveat for CSV loads when a stream is on the target table. Check the COPY reference if these apply to your load.
Keep load history in context
File history is useful for identifying the load and its first reported error, but it is not a substitute for exporting row-level validation results. Snowflake’s S3 loading guide says historical data for COPY commands is retained for the previous 14 days. That figure is stated in the context of that guide; confirm the applicable history view and account context rather than treating it as a universal retention guarantee.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
