Data Movement · Topic 5 of 17

Troubleshooting Data Ingestion - Causes and Resolutions

Find why a Snowflake load failed or loaded nothing using VALIDATE, COPY_HISTORY and pipe status, then fix it with the right ON_ERROR or format option.

  • 6 min
  • Includes a diagram
In a nutshell
  • Use VALIDATION_MODE before loading and VALIDATE after a load with CONTINUE.
  • ON_ERROR values: ABORT_STATEMENT, CONTINUE, SKIP_FILE, SKIP_FILE_n, SKIP_FILE_n%; default ABORT_STATEMENT for COPY and SKIP_FILE for Snowpipe.
  • Already loaded files are skipped for 64 days (COPY) or 14 days (pipe); FORCE = TRUE overrides but duplicates data.

What it is

Troubleshooting ingestion means answering two questions in order: did the data load, and if not, why? Snowflake gives you several tools to see rows that were rejected, files that were skipped and pipes that never fired.

The causes fall into four groups: the data does not fit the table or format, the file was skipped on purpose, Snowflake cannot reach the file, or an automated pipe is not triggered.

Load looks wrongcheckCOPY output or COPY_HISTORYstatus, first_errorfindCausedata, metadata, access, pipeapplyFixformat option, ON_ERROR, privilege, event setup

How it works

Tools for finding the cause

Tools for a load problem: VALIDATION_MODE and ON_ERROR before or during COPY, VALIDATE and COPY_HISTORY after a COPY, and SYSTEM$PIPE_STATUS and VALIDATE_PIPE_LOAD for Snowpipe.

ToolWhat it showsWhen to use
COPY result setPer file: status, rows_parsed, rows_loaded, errors_seen, first_error, first_error_line, first_error_column_nameRight after a COPY statement
VALIDATION_MODEChecks staged files without loading: RETURN_n_ROWS, RETURN_ERRORS, RETURN_ALL_ERRORSBefore loading, to test files
VALIDATE table functionReturns all errors from a past COPY job, for example VALIDATE(orders, JOB_ID => '_last')After a COPY with ON_ERROR = CONTINUE
COPY_HISTORYPer file load status, error counts and first error; table function (14 days) and ACCOUNT_USAGE view (365 days)Find what loaded and when
SYSTEM$PIPE_STATUSWhether a pipe is running and how many files are pendingSnowpipe not loading
VALIDATE_PIPE_LOADErrors found by a pipe in a time windowSnowpipe loaded fewer rows than expected
LOAD_HISTORYCOPY INTO loads only, not Snowpipe; Information Schema view (14 days) and ACCOUNT_USAGE view (365 days)Audit

VALIDATION_MODE cannot be used with a COPY transformation. VALIDATE works only on COPY jobs that loaded into a table and does not cover Snowpipe.

Causes of ingestion errors and their resolutions

Symptom or messageCauseResolution
Number of columns in file does not match the tableExtra or missing fields; wrong delimiterFix FIELD_DELIMITER; use a column list; set ERROR_ON_COLUMN_COUNT_MISMATCH = FALSE if mismatches are acceptable
Numeric or date value is not recognizedText in a numeric column, or date text in another formatSet DATE_FORMAT or TIMESTAMP_FORMAT; use TRY_TO_NUMBER style casts in a transformation; load as text first
String is too long for the columnValue exceeds VARCHAR(n)Widen the column; TRUNCATECOLUMNS = TRUE cuts the value; ENFORCE_LENGTH = FALSE has the same effect
Found character instead of field delimiterUnescaped quote or embedded delimiterFIELD_OPTIONALLY_ENCLOSED_BY = '"'; fix the escape character
JSON parse error, or one giant rowFile is an array or has bad syntaxSTRIP_OUTER_ARRAY = TRUE; fix or skip bad files
Invalid UTF-8 or garbled textWrong encodingENCODING = ...; REPLACE_INVALID_CHARACTERS = TRUE
Nothing loaded, status says already loadedLoad metadata: same file seen within 64 daysRename or change the file, or use FORCE = TRUE knowing duplicates may result
Files skipped, load status uncertainMetadata expired for old filesLOAD_UNCERTAIN_FILES = TRUE
Access denied or file not foundMissing stage privilege, integration trust policy, wrong path, bad PATTERNCheck cloud role trust policy and STORAGE_ALLOWED_LOCATIONS; grant USAGE or READ on the stage; run LIST @stage
Fewer columns than expected with MATCH_BY_COLUMN_NAMENames do not matchUse CASE_INSENSITIVE; rename columns; use a transformation
Exam tip

The error text almost always names the file, line and column. Read first_error and first_error_line before changing any option.

Choosing ON_ERROR

ON_ERROR decides how much to tolerate.

ValueBehavior
ABORT_STATEMENTDefault for COPY; the whole statement fails and nothing is committed
CONTINUESkip bad rows and load the rest
SKIP_FILESkip any file that has an error; default for Snowpipe
SKIP_FILE_nSkip a file when it has n or more errors
SKIP_FILE_n%Skip a file when the percent of error rows reaches the number

A good repair pattern is to load with ON_ERROR = CONTINUE, then run VALIDATE for the rejected rows, fix the source data, and load only the corrected rows.

COPY INTO orders FROM @raw_stage/orders/
  FILE_FORMAT = (FORMAT_NAME = 'csv_ff')
  ON_ERROR = CONTINUE;

SELECT * FROM TABLE(VALIDATE(orders, JOB_ID => '_last'));

RETURN_FAILED_ONLY = TRUE trims the COPY output to files that had problems.

Troubleshooting Snowpipe

Snowpipe has no result set to read, so work through the path in order.

Event sent?yesQueue receives it?yesPipe running?yesCOPY_HISTORYrow errors?yesFix file or format
  1. Is the pipe running? SYSTEM$PIPE_STATUS shows executionState and pendingFileCount. A paused pipe loads nothing.
  2. Is the event configured? For auto ingest the bucket notification must point at the pipe's notification_channel (SQS) or topic, with a prefix that matches the stage path.
  3. Are the files in scope? The pipe's stage path must contain the file, and the file must have arrived after the pipe was created. Use ALTER PIPE ... REFRESH for files staged in the last 7 days.
  4. Were the files already loaded? Pipe load metadata lasts 14 days and dedups by file.
  5. Did rows fail? Default SKIP_FILE skips the whole file on one error. Use COPY_HISTORY or VALIDATE_PIPE_LOAD to see the error.
Watch out

CREATE OR REPLACE PIPE empties the pipe's load history, so files could load twice. Pause the pipe and confirm what already loaded before replacing it.

Common mistakes

Fixing the warehouse when the data is the problem. An error about a column or format needs a file format or table change, not a larger warehouse.

Using FORCE to retry. FORCE reloads everything and creates duplicates. Re-run without it so only the failed files load.

Running VALIDATE for Snowpipe. VALIDATE covers table COPY jobs. For pipes use VALIDATE_PIPE_LOAD or COPY_HISTORY.

Leaving ABORT_STATEMENT on a large mixed load. One bad row cancels the whole statement. Use CONTINUE or SKIP_FILE and review the errors afterwards.

Missing the notification prefix. An event for s3://bucket/other/ never reaches a pipe reading s3://bucket/orders/.

What to remember for the exam

  • Use VALIDATION_MODE before loading and VALIDATE after a load with CONTINUE.
  • ON_ERROR values: ABORT_STATEMENT, CONTINUE, SKIP_FILE, SKIP_FILE_n, SKIP_FILE_n%; default ABORT_STATEMENT for COPY and SKIP_FILE for Snowpipe.
  • Already loaded files are skipped for 64 days (COPY) or 14 days (pipe); FORCE = TRUE overrides but duplicates data.
  • COPY_HISTORY shows per file results; SYSTEM$PIPE_STATUS shows pipe state and backlog.
  • Access errors point at the stage privilege, integration trust policy or path.

Check yourself

  • Which option lets a load finish while recording bad rows for later review?

    ON_ERROR = CONTINUE, then query the rejected rows with the VALIDATE table function.

  • An auto ingest pipe loads nothing. What do you check first?

    SYSTEM$PIPE_STATUS for the execution state and pending files, then the bucket event notification and its prefix.

  • A COPY fails with a string too long error. What are two fixes?

    Widen the column, or set TRUNCATECOLUMNS = TRUE (or ENFORCE_LENGTH = FALSE) to cut the value.

Topic 5 of 17 in Data Movement

Test yourself on Data Movement

The full Snowflake DEA-C02 question bank has 709 questions, with timed practice exams and an explanation for every answer.

This study guide is independent and is not affiliated with, endorsed by, or sponsored by Snowflake. Vendor names, certification names and exam codes are trademarks of their respective owners and are used for identification only.