CertSafari
    Snowflake SnowPro Advanced: Data Engineer (DEA-C02)· Lessons

    Domain 1 · Lesson 3/22

    Snowpipe Streaming errors: channels, offsets and error tables

    Troubleshoot data ingestion.

    10 min read
    4% of exam
    3 sources
    Published 20 Oct 2026
    Docs as of 18 Oct 2026

    What you will be able to do

    • Contrast how the classic and high-performance Streaming architectures handle bad rows
    • Read channel status and offset tokens to decide whether to retry, reopen a channel, rebuild the client or fix credentials
    • Turn on error logging, query rejected rows with ERROR_TABLE and reinsert corrected data

    1.How each Streaming architecture handles a bad row

    Snowpipe Streaming sends rows through channels instead of loading files, so file-load history doesn't help here. Troubleshooting depends on the architecture, because the two put bad-row handling in different places. In the classic architecture, your Java application handles it on the client through the SDK. In the high-performance architecture, Snowflake processes the rows on the server.

    The classic SDK gives each batch of rows a COPY-like error choice, and the default is the strict one. ABORT aborts the whole batch and throws an exception at the first error. SKIP_BATCH loads nothing from a batch that contains any error, but returns all of its errors. CONTINUE loads the acceptable rows and returns all the errors. Choose it when a batch should keep every row that parses and give you a full list of the ones that don't. The application checks the result in the response from insertRow or insertRows.

    The high-performance architecture gives you no such choice. It always behaves as ON_ERROR = CONTINUE: valid rows are ingested and problem rows are skipped. Skipped rows aren't lost if the target table has error logging on, which is covered below. Error tables exist only in this architecture.

    Bad-row handling in the two Streaming architectures
    AspectClassicHigh-performance
    Where errors are handledClient-side, through the SDKServer-side, in Snowflake
    Error behaviourCONTINUE, SKIP_BATCH or ABORT per batch; ABORT is the defaultImplicitly ON_ERROR = CONTINUE
    Rejected-row recoveryFrom the insertRow / insertRows response in your applicationError table on the target table (ERROR_LOGGING = TRUE)

    Checkpoint 1 of 3· Check yourself

    A team streams rows with the classic Snowpipe Streaming SDK. They run ALTER TABLE ... SET ERROR_LOGGING = TRUE on the target table so they can query rejected rows later with SQL. What should they expect?

    Sources12

    2.Reading channel status to choose a recovery

    In the high-performance architecture, the channel status endpoint is where diagnosis starts. Beyond the classic statusCode and persistedOffsetToken, it reports channel_status_code, rows_parsed, rows_inserted, rows_error_count, last_error_message, last_error_timestamp and last_error_offset_upper_bound. Two offsets matter for recovery. last_commited_offset_token marks the last row set that Snowflake committed to the table. last_error_offset_upper_bound shows approximately where in your stream the latest errors are. For trends over time, use the SNOWPIPE_STREAMING_CHANNEL_HISTORY view.

    A channel is healthy only when channel_status_code is SUCCESS. Any other value means the channel is invalid. The SDK retries transient failures (5XX, 429, 408) by itself but never reopens a channel, so your code has to close and reopen it. The persisted fatal codes are:

    Fatal channel codes and the required action
    Error codeContextAction
    ERR_PIPE_DOES_NOT_EXIST_OR_NOT_AUTHORIZEDTarget pipe missing or inaccessibleFix the pipe, then reopen the channel
    ERR_TABLE_DOES_NOT_EXIST_NOT_AUTHORIZEDTarget table missing or inaccessibleFix the table, then reopen the channel
    ERR_CHANNEL_HAS_INVALID_ROW_SEQUENCER / ERR_CHANNEL_HAS_INVALID_CLIENT_SEQUENCERRow or channel sequencing state isn't validReopen the channel
    ERR_CHANNEL_MUST_BE_REOPENED / ERR_CHANNEL_MUST_BE_REOPENED_DUE_TO_ROW_SEQ_GAPChannel unusable, or a gap in the row sequenceReopen the channel

    Schema evolution is the exception to always-CONTINUE. Suppose a batch carries column names that can't be mapped, or adds more columns than the configured limit allows in one batch. The channel is then invalidated instead of the rows being skipped, which stops data that would cause lasting schema problems. getChannelStatus() shows the reason.

    In the Java SDK, the HTTP status on a caught SFException (from getHttpStatusCode()) tells you which recovery to use. A 409 means the channel was invalidated, for example because another client superseded it. Close it, call openChannel, and resume from the last committed offset so no rows are skipped or sent twice. For a 429, back off exponentially and retry. A 500 or 503 can usually be retried after a short wait. A 401 or 403 needs the credentials, JWT setup or role permissions fixed before anything is reopened. Also tell the two exceptions apart. InvalidChannelException affects only one channel. InvalidClientException means the whole client is compromised: create a new client from the factory and reopen every channel.

    Checkpoint 2 of 3· Match them up

    Match each failure in a high-performance Snowpipe Streaming Java application to the recovery it needs.

    Tap a term, then the definition that fits it.

    Sources3

    3.Recovering rejected rows from error tables

    Channel status gives you counts and the latest error message, not the rejected data. To get the rows themselves, turn on error logging on the target table. Valid rows keep loading, and rows that fail server-side parsing or transformation go to an error table. That same table also collects DML errors.

    Turn on error logging for a streaming target tablesql
    -- 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;

    Each row in the error table holds the original payload the client sent, before any pipe transformation. If your pipe drops or reshapes fields, the error table still has the full input. You query it with ERROR_TABLE(<table>) and filter on error_metadata:service = 'snowpipe_streaming' to separate streaming errors from DML ones. The error_metadata:details object names the pipe_name and channel_name. Its offset_token_lower_bound and offset_token_upper_bound place the bad row in your stream. The raw row itself is in error_data:$1.

    Recent streaming errors with channel, pipe and raw payloadsql
    SELECT
        timestamp,
        error_code,
        error_metadata:error_message::STRING AS error_message,
        error_metadata:details:channel_name::STRING AS channel,
        error_metadata:details:pipe_name::STRING AS pipe,
        error_metadata:details:error_data_content_type::STRING AS content_type,
        error_data:"$1"::STRING AS raw_payload
    FROM ERROR_TABLE(my_streaming_table)
    WHERE error_metadata:service = 'snowpipe_streaming'
      AND timestamp >= DATEADD(hour, -1, CURRENT_TIMESTAMP())
    ORDER BY timestamp DESC;

    How you fix a row depends on the kind of failure. Syntactically valid JSON can still fail for logical reasons: a missing NOT NULL column, a value like "abc" bound for a NUMBER, or a transformation that divides by zero. For these, read error_metadata:error_message and error_source, parse the payload with PARSE_JSON, correct it and insert it back. For invalid JSON, the message describes the syntax error. Invalid UTF-8 is stored base64-encoded, and BASE64_DECODE_STRING shows the raw bytes. That kind of error usually means the source system produces the wrong encoding.

    Reinsert corrected rows from valid JSON payloadssql
    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 capture only server-side parsing and transformation errors. SDK validation failures, API failures and other asynchronous server errors still have to be read from getChannelStatus(). Payloads over 128 MB are truncated, and error_data_truncated flags when that happened. A high failure rate adds latency, and the error table is billed at normal storage rates. Ingestion itself costs nothing extra.

    Checkpoint 3 of 3· Check yourself

    In a high-performance Snowpipe Streaming setup, the pipe's transformation drops a debug_info field and casts amount to NUMBER. The target table has ERROR_LOGGING = TRUE. A row arrives with amount set to "abc" and is rejected. What will you find in error_data:$1 for that row in the error table?

    Sources2

    Practise it for real

    Capture, inspect, repair and clear rejected Snowpipe Streaming rows on a high-performance target table

    1. 1.Run ALTER TABLE my_streaming_table SET ERROR_LOGGING = TRUE;

      Why: Rows rejected server-side are only kept once error logging is on

      You should see: The statement succeeds, and from then on rejected rows land in the table's error table

    2. 2.Send a few rows through your channel, one with a string such as "abc" for a NUMBER column, then run SELECT * FROM ERROR_TABLE(my_streaming_table) WHERE error_metadata:service = 'snowpipe_streaming';

      Why: Filtering on the service separates streaming errors from DML errors in the same table

      You should see: One row for the bad record, with channel_name, pipe_name and the original payload in error_data:$1

    3. 3.Group the last 24 hours of streaming errors by error_code and error_metadata:error_message with COUNT(*)

      Why: A single dominant error usually points to one upstream defect, not scattered bad data

      You should see: A short list of error messages ordered by how often they occur

    4. 4.Reinsert the corrected rows with INSERT ... SELECT using PARSE_JSON(error_data:"$1") and TRY_CAST, filtered to error_data_content_type = 'json'

      Why: Valid-JSON payloads can be fixed in SQL without resending from the source

      You should see: The repaired rows appear in my_streaming_table

    5. 5.Run TRUNCATE ERROR_TABLE(my_streaming_table);

      Why: Clears reprocessed errors so they aren't reinserted twice and stop accruing storage

      You should see: ERROR_TABLE(my_streaming_table) returns no rows

    Stuck? Get a nudge

    If a payload is a base64 string, not JSON, it was invalid UTF-8. Decode it with BASE64_DECODE_STRING and fix the encoding at the source.

    Sources

    (3)

    Every claim above is drawn from one of these pages, quoted as it was written on the date shown.

    1. 1.
      “ABORT (default setting): Abort the entire batch of rows and throw an exception when the first error is encountered.”
      ↩︎ How each Streaming architecture handles a bad row
      “CONTINUE: Continue to load the acceptable rows of data and return all errors.”
      ↩︎ How each Streaming architecture handles a bad row
      “SKIP_BATCH: Skip loading and return all errors if any error is encountered in the entire batch of rows.”
      ↩︎ How each Streaming architecture handles a bad row
    2. 2.
      “The high-performance architecture implicitly operates in ON_ERROR = CONTINUE mode, meaning valid rows are ingested while problematic rows are skipped.”
      ↩︎ How each Streaming architecture handles a bad row
      “For the classic architecture, error handling is managed client-side through the SDK.”
      ↩︎ How each Streaming architecture handles a bad row
      “To turn on error logging for Snowpipe Streaming, set the ERROR_LOGGING property on the target table.”
      ↩︎ Recovering rejected rows from error tables
      “The error_metadata:service field is populated with snowpipe_streaming for errors from Snowpipe Streaming.”
      ↩︎ Recovering rejected rows from error tables
      “Parse the payload with PARSE_JSON(error_data:$1), correct the data, and reinsert it into the target table.”
      ↩︎ Recovering rejected rows from error tables
      “Error tables capture errors that occur during server-side data processing (parsing and transformation).”
      ↩︎ Recovering rejected rows from error tables
      “Payloads larger than 128 MB are truncated.”
      ↩︎ Recovering rejected rows from error tables
      “Error tables are available only for the Snowpipe Streaming high-performance architecture.”
      ↩︎ Exam trap 1
      “The data stored in error tables is the original payload sent to the API or SDK before any pipe transformations are applied.”
      ↩︎ Exam trap 3
      “Error tables are available only for the Snowpipe Streaming high-performance architecture. For the classic architecture, error handling is managed client-side through the SDK.”
      ↩︎ Checkpoint
      “The data stored in error tables is the original payload sent to the API or SDK before any pipe transformations are applied.”
      ↩︎ Checkpoint
    3. 3.
      “Indicates the offset token of the last row set that was successfully committed to the target table by Snowflake.”
      ↩︎ Reading channel status to choose a recovery
      “if the channel_status_code in the channel status response is anything other than SUCCESS”
      ↩︎ Reading channel status to choose a recovery
      “you must explicitly close and reopen the channel to continue ingestion.”
      ↩︎ Reading channel status to choose a recovery
      “the channel is invalidated if it encounters a schema evolution failure caused by user errors.”
      ↩︎ Reading channel status to choose a recovery
      “Close the current channel, call openChannel to create a new instance, and then resume from the last committed offset.”
      ↩︎ Reading channel status to choose a recovery
      “Implement an exponential back-off and retry strategy.”
      ↩︎ Reading channel status to choose a recovery
      “You must close the existing client, initialize a new one using the factory, and then reopen all associated channels.”
      ↩︎ Exam trap 2
      “because the new channel immediately encounters the same problem.”
      ↩︎ Prediction
      “InvalidClientException: The entire SnowflakeStreamingIngestClient is compromised. You must close the existing client, initialize a new one using the factory, and then reopen all associated channels.”
      ↩︎ Checkpoint

    Ready to test yourself?

    Practise the 15 questions on this subdomain.

    Spotted a mistake, or was something unclear? Tell us.