Troubleshooting Snowpipe Streaming Errors: Channels and Error Tables

1.How bad rows are handled in each architecture

Snowpipe Streaming handles bad rows differently depending on the architecture, and that difference decides where you look for errors.

In the classic architecture, your own Java application sends rows through the ingest SDK, and it has to handle the errors that come back. For each batch of rows, the API supports the equivalent of three ON_ERROR settings:

The high-performance architecture processes rows on the server, in Snowflake. It behaves as if ON_ERROR = CONTINUE were always set: valid rows are ingested and bad rows are skipped. The batch doesn't fail, so you need other tools to find out what was dropped. Those tools are channel status and error tables, covered in the next two sections.

Sources12

2.Channel status and choosing the right recovery

In the high-performance architecture, the channel status endpoint shows a channel's current state. Alongside the classic fields (statusCode, persistedOffsetToken), it returns channel_status_code (the channel's health), last_commited_offset_token, rows_parsed, rows_inserted and rows_error_count. For the most recent error it returns last_error_message, last_error_timestamp and last_error_offset_upper_bound, which gives the approximate position of the error in the stream. For history rather than the current state, query the SNOWPIPE_STREAMING_CHANNEL_HISTORY view or the event table telemetry.

For transient failures (HTTP 5XX, 429 and 408), the SDK retries sending unflushed data by itself. Any channel_status_code other than SUCCESS means the channel is invalid and you must close and reopen it. If the code is ERR_PIPE_DOES_NOT_EXIST_OR_NOT_AUTHORIZED or ERR_TABLE_DOES_NOT_EXIST_NOT_AUTHORIZED, fix the pipe or table first, then reopen. Sequencer errors and row-sequence gaps (ERR_CHANNEL_MUST_BE_REOPENED_DUE_TO_ROW_SEQ_GAP, for example) only need a reopen.

There is one exception to the always-CONTINUE behaviour. A schema evolution failure caused by user error invalidates the channel even when ON_ERROR=CONTINUE. Two examples are column names that can't be mapped and adding more columns in one batch than the configured limit allows.

Authorization errors (401 and 403) work the other way. Reopening won't help, because the new channel hits the same problem. Stop ingestion and fix the pipe permissions, user role or credentials, then resume. In the Java SDK, catch SFException and check getHttpStatusCode():

Separate InvalidChannelException (HTTP 409), which affects only one channel and is fixed by reopening it, from InvalidClientException. The second means the whole SnowflakeStreamingIngestClient is compromised: close it, create a new client from the factory, and reopen all of its channels.

Sources3

3.Error tables: recovering the rejected rows

Channel status gives you counts and the most recent error message, not the rows that were rejected. To get those rows in the high-performance architecture, set ERROR_LOGGING on the target table. Valid rows keep loading. Rows that fail server-side parsing or transformation go to a dedicated error table, which also collects DML errors for that table. The error table stores the original payload sent to the API or SDK, before any pipe transformation. Even fields the pipe would drop are kept.

-- For a new table:
CREATE TABLE my_streaming_table (...) ERROR_LOGGING = TRUE;

-- For an existing table:
ALTER TABLE my_streaming_table SET ERROR_LOGGING = TRUE;

SELECT * FROM  ? (my_streaming_table)
WHERE error_metadata:service = 'snowpipe_streaming';

Each error row has a timestamp, query_id, error_code, error_metadata and error_data. error_data:$1 holds the raw payload. error_metadata:details names the pipe_name and channel_name, gives offset_token_lower_bound and offset_token_upper_bound, and flags error_data_truncated for payloads larger than 128 MB. The error_data_content_type field tells you which kind of failure occurred and how to fix it:

- Valid JSON with a logical error. A NOT NULL column is missing, a value fails type conversion ("abc" into a NUMBER, for example), or a pipe transformation fails (such as division by zero). Read error_metadata:error_message and error_metadata:error_source, parse with PARSE_JSON(error_data:$1), correct the data, and reinsert it. - Syntactically invalid JSON. Read the syntax error in error_metadata:error_message, correct the payload, and reinsert it. - Invalid UTF-8. The payload is stored base64-encoded. This usually means a format or encoding problem in the upstream source. Decode it to find the bad bytes.

INSERT INTO my_streaming_table (col1, col2, col3)
SELECT
    TRY_CAST(PARSE_JSON(error_data:"$1"):col1 AS NUMBER),
    PARSE_JSON(error_data:"$1"):col2::STRING,
    TRY_CAST(PARSE_JSON(error_data:"$1"):col3 AS TIMESTAMP)
FROM ERROR_TABLE(my_streaming_table)
WHERE error_metadata:service = 'snowpipe_streaming'
  AND error_metadata:details:error_data_content_type = 'json'
  AND timestamp >= DATEADD(hour, -24, CURRENT_TIMESTAMP());

Error tables have limits. They only capture errors from server-side parsing and transformation. SDK validation errors, API failures and other asynchronous server-side errors never reach them, so watch those with getChannelStatus(). A high failure rate can increase latency. Routing rows to the error table adds no ingestion charge, but the stored data is billed at standard storage rates. After reprocessing, TRUNCATE ERROR_TABLE(my_streaming_table) clears the error table. Error tables are available only in the high-performance architecture. In the classic architecture, errors are handled client-side through the SDK.

Sources2

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.Any Snowpipe Streaming error can be fixed by closing and reopening the channel.Why is that wrong?

   For 401 and 403 authorization errors you must fix the permission or credential problem first. A reopened channel would fail the same way.

   Covered in Channel status and choosing the right recovery

2. 2.Because the high-performance architecture runs with ON_ERROR=CONTINUE, bad data can never invalidate a channel.Why is that wrong?

   A schema evolution failure caused by user error, such as column names that can't be mapped, invalidates the channel even under CONTINUE.

   Covered in Channel status and choosing the right recovery

3. 3.Setting ERROR_LOGGING on a table fed by the classic Snowpipe Streaming SDK captures its rejected rows.Why is that wrong?

   Error tables exist only in the high-performance architecture. In the classic architecture, errors are handled client-side through the SDK.

   Covered in Error tables: recovering the rejected rows

Practise it for real

Capture, inspect and repair rejected Snowpipe Streaming rows using an error table

1. 1.Run ALTER TABLE my_streaming_table SET ERROR_LOGGING = TRUE; on a table loaded through the high-performance architecture.

   Why: Rows that fail server-side processing are only kept once error logging is on.

   You should see: The statement succeeds, and valid rows keep loading as before.

2. 2.Query SELECT * FROM ERROR_TABLE(my_streaming_table) WHERE error_metadata:service = 'snowpipe_streaming';

   Why: This separates streaming failures from DML errors in the same error table.

   You should see: One row per rejected row, with error_metadata and the raw payload in error_data:$1.

3. 3.Group the errors by error_code and error_metadata:error_message over the last 24 hours.

   Why: Counting by error type shows whether one upstream fault is causing most of the failures.

   You should see: A short list of error messages ranked by count.

4. 4.Run the INSERT … SELECT with PARSE_JSON and TRY_CAST for rows whose error_data_content_type is 'json'.

   Why: Payloads that are valid JSON can be corrected and reinserted with plain SQL.

   You should see: The corrected rows appear in my_streaming_table.

5. 5.Run TRUNCATE ERROR_TABLE(my_streaming_table);

   Why: Stored error rows are billed at standard storage rates, so clear them once they have been reprocessed.

   You should see: ERROR_TABLE(my_streaming_table) returns no rows.