How to Capture & Export Rejected Rows in Snowflake

This guide explains how to capture rows that fail validation during a Snowflake COPY INTO or INSERT operation and export them to a separate table or file for review. You’ll learn to use the VALIDATION_MODE option, the REJECTED_RECORD_EXTRACTOR, and query the COPY_HISTORY view—so you can fix bad data without losing it.

What You’ll Need

• A Snowflake account with access to a database and schema where you have CREATE TABLE and CREATE STAGE privileges.
• An existing internal or external stage containing the source data files (CSV, JSON, Parquet, etc.).
• Basic familiarity with running SQL statements in Snowflake (Worksheet or any SQL client).

Step-by-Step

1. Create a table to hold rejected rows. This table must match the structure of the rows that fail—typically the same columns as your target table, but you can add extra columns for error details if you plan to use the ERROR_INTEGRATION approach. For simplicity, start with the same schema as your target table.
2. Run a COPY INTO command with VALIDATION_MODE set to RETURN_ERRORS. This performs a dry run that identifies errors without loading data. Example:

   COPY INTO my_table
   FROM @my_stage
   FILE_FORMAT = (TYPE = CSV)
   VALIDATION_MODE = RETURN_ERRORS;

   This returns a result set with columns like file, line, category, column_name, and error_message—but does not store them anywhere.

3. Capture the validation result into a temporary table. Use CREATE TABLE AS SELECT (CTAS) to save the output:

   CREATE OR REPLACE TABLE rejected_rows AS
   SELECT * FROM TABLE(
     RESULT_SCAN(LAST_QUERY_ID())
   );

   This stores the error details in a queryable table.

4. Optionally, load the actual rejected data rows. If you want the raw row content, use the REJECTED_RECORD_EXTRACTOR in your COPY statement. First, create a stage for rejected files:

   CREATE STAGE IF NOT EXISTS rejected_stage;

   Then run the COPY with the extractor:

   COPY INTO my_table
   FROM @my_stage
   FILE_FORMAT = (TYPE = CSV)
   REJECTED_RECORD_EXTRACTOR = (TYPE = CSV)
   ON_ERROR = CONTINUE;

   This writes each rejected row to a file in @rejected_stage (or the path you specify). You can then query those files with SELECT from the stage.

5. Query the COPY_HISTORY view for a summary. After a load, run:

   SELECT * FROM INFORMATION_SCHEMA.COPY_HISTORY
   WHERE TABLE_NAME = 'MY_TABLE'
   ORDER BY LAST_LOAD_TIME DESC;

   This shows rows loaded, errors, and the first error message.

6. Export the rejected rows to a file. If you have rejected rows in a table, use COPY INTO to unload them:

   COPY INTO @my_stage/rejected_output
   FROM rejected_rows
   FILE_FORMAT = (TYPE = CSV);

   Or use GET to download from an internal stage to your local machine.

Common Mistakes

• Not using VALIDATION_MODE before the actual load. This causes bad rows to be silently skipped or abort the load, and you lose the error details.
• Forgetting to include the FILE_FORMAT in the COPY command. This often leads to parsing errors that are not the real data issues.
• Assuming REJECTED_RECORD_EXTRACTOR works with all file types. It works with CSV and JSON, but not with Parquet or Avro—check Snowflake documentation for your format.
• Overwriting the rejected table on every run. Use CREATE OR REPLACE only if you want the latest run; otherwise, use INSERT INTO to accumulate history.
• Not setting ON_ERROR correctly. If ON_ERROR = ABORT_STATEMENT, the load stops at the first error and you get no rejected rows. Use CONTINUE or SKIP_FILE as appropriate.

Tips

• Use a dedicated schema for rejected data to keep your main tables clean.
• Include a load timestamp column in your rejected table to track when each error occurred.
• For large files, test with a small sample first using VALIDATION_MODE = RETURN_ERRORS and a LIMIT clause if possible.
• Combine the error table with the raw rejected rows by joining on file and line number—this gives you both the error message and the offending data.
• Set up a scheduled task to automatically run the validation and store results for recurring loads.

Troubleshooting

• No rejected rows appear even though you know there are errors. Check that ON_ERROR is set to CONTINUE or SKIP_FILE. Also verify that VALIDATION_MODE is not still active—it must be unset for the actual load.
• The REJECTED_RECORD_EXTRACTOR creates files but they are empty. This can happen if the errors are at the file level (e.g., malformed JSON) rather than row level. Try using VALIDATION_MODE to see the error category.
• RESULT_SCAN returns no data. Make sure you run it in the same session and immediately after the COPY command. If you ran another query in between, the LAST_QUERY_ID changes.
• Permission errors when creating stages or tables. Ensure your role has USAGE on the database and schema, and CREATE STAGE on the schema.
• COPY_HISTORY shows errors but you cannot see the actual rows. Use the ERROR_FILE_NAME column to locate the rejected file in the stage, then query it directly.

Frequently Asked Questions

Can I capture rejected rows during INSERT (not COPY)?

No, the methods described here work only with COPY INTO. For INSERT statements, you need to validate data beforehand using constraints or a separate validation query.

Does REJECTED_RECORD_EXTRACTOR work with JSON files?

Yes, it supports JSON. You need to specify the extractor type as JSON in the COPY command.

How long are rejected files kept in the stage?

Internal stages retain files until you delete them. External stages depend on your cloud provider’s lifecycle rules.

Can I automate the export of rejected rows?

Yes, you can create a Snowflake task that runs after each load, queries the error table, and unloads to a stage.

What is the difference between VALIDATION_MODE and ON_ERROR?

VALIDATION_MODE is a dry run that returns errors without loading. ON_ERROR controls what happens during an actual load—continue, skip file, or abort.

Is there a limit to the number of rejected rows I can capture?

Snowflake does not impose a specific limit, but your storage and query performance will degrade with very large numbers. Use partitioning or periodic cleanup.

Related

• Intel Mobile Chipset vs ARM: Key Differences
• Duet Night Abyss AFK Farming: Wedges & XP
• FC Champions Results: Boost Rank Without Changing Playstyle