Author(s): Preethi Kaluva

Originally published on Towards AI.

Beyond ‘COPY INTO’: Capturing, Logging, and Offloading Bad Data in Snowflake

If you’ve ever ingested raw CSVs from S3 into Snowflake for more than a few weeks, you’ve probably had a version of this moment where you load say 100 records and maybe 5-10 of them go missing.

I learned the hard way that Snowflake’s COPY INTO can quietly report a successful load while silently dropping hundreds of thousands of schema-mismatched records without raising an error. If you regularly ingest raw CSVs from S3 into Snowflake, you've likely lost data this way too. Here is a repeatable, production-grade pattern to fix it for good.

So let’s fix it properly, not with a Band-Aid, but with a repeatable, production-grade pattern you can drop into any pipeline.

The Core Problem

Here’s the naive ingestion flow most of us start with:

S3 (raw files) → COPY INTO target_table → Done ✅

Looks fine on a whiteboard. In reality, source files are messy, a vendor changes a date format, a text field has an unescaped comma, someone exports a NULL as the literal string "NULL". When COPY INTO hits one of these, its default behavior (ON_ERROR = ABORT_STATEMENT) kills the entire load. One bad row, zero rows loaded. Not great when you're loading a 5GB file at 2am.

So the real flow needs two more branches:

That’s the whole article, honestly. Let’s build each piece.

Step 1: Dry-Run First. Always.

Before you touch the target table, run a validation-only pass. Snowflake gives you VALIDATION_MODE for exactly this, and I treat it as non-negotiable in any new pipeline.

COPY INTO raw.customer_data
FROM @my_s3_stage/customers/2024-01-15/
FILE_FORMAT = (TYPE = CSV, SKIP_HEADER = 1)
VALIDATION_MODE = RETURN_ALL_ERRORS;
-- Nothing is actually inserted here. 
-- Snowflake just tells you what WOULD break.

There are three flavors of validation mode, and picking the right one matters:

• RETURN_ERRORS – returns errors only, stops after finding a batch. Good for a quick sanity check.
• RETURN_ALL_ERRORS – returns every error across the whole file set. This is what I use before any real load, because I want the full picture, not a sample.
• RETURN_N_ROWS – validates only the first N rows. Useful when you're dealing with a genuinely massive file and just want a fast smoke test.

Run this, and you’ll get a result set with the exact row number, column name, and reason for failure -things like "Numeric value 'N/A' is not recognized". This is gold. It tells you exactly what's wrong before you commit to anything.

Note: VALIDATION_MODE never writes data. Don't confuse it with ON_ERROR,they solve different problems. One is a rehearsal, the other is what happens during the real show.

Step 2: Choose Your Fault Tolerance with ON_ERROR

Once validation confirms roughly what’s broken, decide how the actual load should behave:

Here’s the pattern I actually run:

COPY INTO raw.customer_data
FROM @my_s3_stage/customers/2024-01-15/
FILE_FORMAT = (TYPE = CSV, SKIP_HEADER = 1, FIELD_OPTIONALLY_ENCLOSED_BY = '"')
ON_ERROR = CONTINUE;
-- Good rows land in the table. Bad rows are silently excluded — for now.

This is the part people stop at, and it’s dangerous to stop here. “Silently excluded” is still silent. You’ve fixed the crash, but you’ve reintroduced another problem -> data disappearing without anyone knowing.

Step 3: Actually See the Rejects with TABLE(VALIDATE(…))

This is the piece most tutorials skip, and it’s the one that saves you. Immediately after your COPY INTO, Snowflake lets you query the rejected rows from that exact load using the query ID:

SELECT *
FROM TABLE(VALIDATE(raw.customer_data, JOB_ID => '_last'));
-- or, more explicitly:
SELECT *
FROM TABLE(VALIDATE(raw.customer_data, STATEMENT_ID => LAST_QUERY_ID()));

Callout, the gotcha that costs people an hour: VALIDATE() only works against the COPY statement's query ID within the same session, and only if that statement didn't abort outright. If you re-run the COPY, close the session, or run another query in between and forget to capture LAST_QUERY_ID() immediately, you've lost your window. Capture it right away.

Now let’s persist this instead of just eyeballing it:

CREATE TABLE IF NOT EXISTS audit.faulty_records (
 file_name STRING,
 row_number NUMBER,
 column_name STRING,
 error_message STRING,
 rejected_record VARIANT,
 load_timestamp TIMESTAMP_NTZ DEFAULT CURRENT_TIMESTAMP()
);

INSERT INTO audit.faulty_records (file_name, row_number, column_name, error_message, rejected_record)
SELECT
 file,
 line,
 column_name,
 error,
 rejected_record
FROM TABLE(VALIDATE(raw.customer_data, STATEMENT_ID => LAST_QUERY_ID()));

Now every bad row has a paper trail: which file it came from, which line, which column, and why. This alone can save you multiple awkward client calls. I can say “row 4,502 in your file has a malformed phone number” instead of “something, somewhere, went wrong.”

Step 4: Ship the Bad Data Back to the Client

Internally logging rejects is good. But often the client (or upstream team) owns the fix, not you. So close the loop by exporting rejects back to S3, in a folder they already watch:

COPY INTO @rejected_s3_stage/customers/2024-01-15/rejects.csv
FROM (
 SELECT file_name, row_number, column_name, error_message, rejected_record
 FROM audit.faulty_records
 WHERE load_timestamp::date = CURRENT_DATE()
)
FILE_FORMAT = (TYPE = CSV, COMPRESSION = NONE)
SINGLE = TRUE
OVERWRITE = TRUE;

I like naming these files with the same date partition as the source load, it makes reconciliation trivial for whoever’s picking up the rejects on the other end.

Step 5: Wrap It All in a Stored Procedure

Doing this manually every time is how good intentions die. Here’s the procedure to reuse across projects, tweak stage names and table names as needed:

CREATE OR REPLACE PROCEDURE audit.load_with_error_handling(
 stage_path STRING,
 target_table STRING,
 rejected_stage STRING
)
RETURNS STRING
LANGUAGE SQL
AS
$$
DECLARE
 load_query_id STRING;
BEGIN
 -- Step A: Load valid rows, skip the rest
 EXECUTE IMMEDIATE
 'COPY INTO ' || :target_table ||
 ' FROM ' || :stage_path ||
 ' FILE_FORMAT = (TYPE = CSV, SKIP_HEADER = 1)' ||
 ' ON_ERROR = CONTINUE';
 
 load_query_id := LAST_QUERY_ID();

 -- Step B: Pull the rejects into the audit table
 EXECUTE IMMEDIATE
 'INSERT INTO audit.faulty_records (file_name, row_number, column_name, error_message, rejected_record)
 SELECT file, line, column_name, error, rejected_record
 FROM TABLE(VALIDATE(' || :target_table || ', STATEMENT_ID => ''' || :load_query_id || '''))';

 -- Step C: Offload today's rejects back to S3 for the client
 EXECUTE IMMEDIATE
 'COPY INTO ' || :rejected_stage ||
 ' FROM (SELECT * FROM audit.faulty_records WHERE load_timestamp::date = CURRENT_DATE())
 FILE_FORMAT = (TYPE = CSV) SINGLE = TRUE OVERWRITE = TRUE';

 RETURN 'Load complete. Valid rows committed, rejects logged and exported.';
END;
$$;

Call it like this:

CALL audit.load_with_error_handling(
 '@my_s3_stage/customers/2024-01-15/',
 'raw.customer_data',
 '@rejected_s3_stage/customers/2024-01-15/'
);

One call. Valid data lands cleanly, bad data gets a home in an audit table, and the rejects fly back to whoever needs to fix them- all without a single aborted pipeline run.

What I’d Tell My Past Self

The VALIDATE() function existed the entire time I was manually diffing row counts in spreadsheets to hunt for missing records. Snowflake was never silently losing data. I just wasn't asking it the right question.

If you take one thing from this: ON_ERROR = CONTINUE without VALIDATE() is a trap. It looks like resilience, but it's actually just moving the problem from "loud failure" to "quiet data loss," which is worse. Pair the two, log everything, and send the mess back to whoever's responsible for cleaning it up. Your pipeline stays green, your data stays trustworthy, and you get to leave the office on Friday without a surprise message.

Join thousands of data leaders on the AI newsletter. Join over 80,000 subscribers and keep up to date with the latest developments in AI. From research to projects and ideas. If you are building an AI startup, an AI-related product, or a service, we invite you to consider becoming a sponsor.

Published via Towards AI