iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
To preserve malformed rows from a Snowflake bulk load, first identify the affected files in COPY history, validate those files with VALIDATION_MODE = RETURN_ALL_ERRORS, then unload the validation result’s REJECTED_RECORD values to a stage file. Validation does not load rows. The exported file gives you records to inspect and correct, while the load history helps connect those records to the original attempt.
Why a COPY result is not a complete error log
With ON_ERROR = CONTINUE, Snowflake keeps loading rows it can process despite detected errors. The COPY result is useful for a quick summary, but it reports at most one error per data file. The difference between rows parsed and rows loaded indicates that rows had detected errors; it does not count every error, since one row can contain more than one.
Use validation or the VALIDATE table function when you need fuller diagnostic detail. COPY history is useful for finding which files loaded, partially loaded, or failed, and for seeing the first reported error. That first-error field is a clue, not a complete account of a file with multiple issues. See Snowflake’s bulk-load troubleshooting guide and COPY INTO reference.
Capture and offload rejected records
1. Find the affected files
Check the target table’s COPY history. Identify the files and their statuses, then note the first error as an initial diagnostic clue. Revalidate the same file set so the row-level results correspond to the files you are investigating.
#1 Best Overall
2. Validate without loading
Run a COPY validation for those files with VALIDATION_MODE = RETURN_ALL_ERRORS. This returns validation errors instead of loading rows. Unlike RETURN_ERRORS, RETURN_ALL_ERRORS also includes errors from files that were partially loaded earlier using ON_ERROR = CONTINUE.
3. Save the validation result and unload it
Capture the query ID immediately after validation, then use RESULT_SCAN to select REJECTED_RECORD and unload those values with COPY INTO <location>. Snowflake’s documented sequence is:
Rank #2
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)));Replace the table, stage, file path, and output path with those for your account. Snowflake cautions that when you use LAST_QUERY_ID(), the statements must run in succession so the ID identifies the validation result you intend to scan. The offloaded text file can then be analyzed as part of correcting the original data. See the Snowflake troubleshooting example.
As an operational practice, keep the exported file associated with the original load attempt so you can reconcile the rejected records before retrying. The documentation describes the validation-and-unload sequence; it does not guarantee a particular reconciliation or retry policy. Decide how to correct the source and retry in line with your pipeline’s idempotency and load-history approach.
Choose an error policy for the load
Error-handling options govern what happens to rows or files during loading; they do not replace a workflow for preserving rejected records.
| Policy | Effect | Diagnostic and operational trade-off |
|---|---|---|
ABORT_STATEMENT |
The default cited in the COPY reference; stops when an error is encountered. | Useful when you want the statement to stop rather than continue through the batch. Plan how the pipeline will recover from the stopped load. |
CONTINUE |
Loads rows it can process despite detected errors. | Its COPY summary reports at most one error per file, so use validation or VALIDATE for fuller error details. |
SKIP_FILE |
Discards a file when an error is found. | Snowflake buffers the entire file; this may be slower than CONTINUE or ABORT_STATEMENT, particularly when a large file contains only a few bad rows. |
These behaviors and caveats are documented in the COPY INTO reference. Pick a policy based on whether valid rows should be retained and whether the batch or file should stop, then separately decide how errors will be inspected and preserved.
Where this workflow does not apply cleanly
- Transformed COPY loads:
VALIDATION_MODEdoes not support COPY statements that transform data, andVALIDATEdoes not support those transformation statements either. Snowflake’s transformation guidance also notes limitations in error handling with scalar SQL UDFs. Do not assume the validation-and-offload sequence captures all failures from a transformed pipeline; use a diagnostic path designed for that pipeline. See Transform data during a load. - 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.
- Other ON_ERROR edge cases: The COPY reference notes potentially inconsistent or unexpected behavior in some cases, including
DISTINCTin a SELECT and clustered tables. It also documents anON_ERRORcaveat for CSV loads when a stream is on the target table. Check the reference when one of these cases applies.
How long COPY history is available
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, whose publication year is not stated; confirm which history view and account context apply before relying on it as a general retention guarantee. See Copying data from an S3 stage.
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 reinstallOutdated Drivers Are Slowing You Down
One free scan finds every outdated or missing driver and matches the right update for your exact hardware.Free scan · exact hardware matchQuick Recap
Best Value
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.
