Row-error quarantine

A streaming model realizes as a target table and a materialized view. When a view fails on one row, ClickHouse does not commit the Kafka offset, so the same block is read again indefinitely and the raw table collects a new copy each time. Setting on_row_error = "quarantine" diverts rows the model cannot convert into a dead-letter table and lets every other row land.

Enabling it

on_row_error accepts "fail" (the default) and "quarantine". The most specific value wins:

1. MODEL (...): on_row_error quarantine
2. pipeline.toml: [defaults.models], then on_row_error = "quarantine"
3. sqltide_project.toml: [defaults.models], then on_row_error = "quarantine"
4. The default, "fail"

MODEL (on_row_error quarantine, order_by ["order_id"]);

SELECT
  order_id::String AS order_id,
  amount::Int32 AS amount,
  _replay_partition::Int32 AS _replay_partition,
  _replay_offset::Int64 AS _replay_offset
FROM __ref("orders")

What it generates

SQLTide moves each authored expression into an inner query under a private name, then applies the authored casts outside it. A check marks a row invalid only when the authored CAST would have raised on it, so enabling quarantine does not change which other rows reach the target or their values: a value that CAST truncates or wraps stays a good row. The one exception is a fractional string such as '1.5' cast to DateTime64, which is diverted although CAST accepts it.

Quarantine diverts rows whose cast fails. Dates that ClickHouse clamps or adjusts rather than rejects, such as out-of-range values or 2026-02-31, are kept with the adjusted value, as under fail. Exactly which date strings are rejected follows your ClickHouse version; SQLTide is tested against 25.8.

• mv__<target> writes rows with no invalid column to the target table.
• mv__<target>__dead_letters writes rows with at least one invalid column to _sqltide_dead_letters.

Both views come from one rewrite, so they cannot disagree about which rows are valid. Replays use the kept-rows query: they never fail on invalid rows and never write dead letters.

In a virtual pipeline each deployment gets its own views with deployment-suffixed names. They record the model’s logical name, so dead letters survive promotion and rollback, and cleaning up a deployment drops its dead-letter view with its other relations. While two deployments are both attached to a source, overlapping deployments each write their own dead letter for the same bad row.

LowCardinality(T) columns are checked against T.

Some columns are not checked, so a failure in one of them still raises exactly as it does under fail, while the model’s other columns are checked:

• Array(...), Map(...) and Tuple(...)
• Enum, JSON, Variant, and any other type a check cannot evaluate without raising
• Nullable(T): no check is added, so a Nullable Enum, FixedString or Decimal can still raise

In a few rare, value-dependent cases the check itself raises, so the insert fails as it would under fail and the row is not diverted:

• a Float NaN or infinity cast to DateTime64
• a Decimal128 or Decimal256 value too large for the target integer type

Eligibility

Direct and virtual models follow the same rules. Only non-aggregating models can quarantine. A model is ineligible when:

• its outer query uses GROUP BY, an aggregate-state engine, or a window function
• a projection references another output column by its alias, unqualified, such as CAST(a + 1 AS Int32) AS b. Reference the source column or compute the value in a subquery. A self-reference inside the projection, such as CAST(v AS String) AS v, is allowed.
• a clause outside the projection, such as WHERE, references an output alias, including a self-reference
• its outer query uses DISTINCT or LIMIT BY
• it reads a scheduled refresh source
• a column type cannot be checked: Nested, Dynamic, AggregateFunction, and the geo types

A value set in the model’s own MODEL (...) header on an ineligible model is a compile error naming the column and the reason. A value inherited from pipeline.toml or the project leaves that model on fail silently.

Limits

Quarantine checks the final conversion of each outer projection. An error raised inside an expression still raises: a non-OrNull parse function, for example, or a failing inner subquery. Use the OrNull form so the failure reaches the check as a NULL.

Changing the policy

Switching a live model between fail and quarantine does not count as a model change: it does not drop the target table, create a deployment, or replay. Plain sqt build applies it to a virtual model’s live deployment, and sqt build --changed applies it to a direct model, in place by pausing the source’s consumer, recreating mv__<target>, creating or dropping mv__<target>__dead_letters, and resuming. sqt plan for virtual models and sqt plan --changed for direct models show it as a view-only change with no replay roots. A model that also has a real SQL change takes the normal rebuild, using the desired policy.

Adopted sources have no consumer to pause, so rows inserted while the views are being swapped are not processed by the model.

Existing rows are not reprocessed by a policy change. To reprocess them, force a rebuild yourself: destroy and rebuild the model, or run a build with --full-refresh.

sqt test runs the model’s authored query, so it does not exercise the generated split.

Reading dead letters

The Overview page’s Ingest panel shows counts per model for the last 24 hours and the latest failure when a project has quarantine models. To read the table directly:

SELECT model_name, failed_columns, payload, kafka_partition, kafka_offset, quarantined_at
FROM _sqltide_dead_letters
WHERE model_name = 'orders_enriched'
ORDER BY quarantined_at DESC
LIMIT 20

payload is JSON of the row’s projected values as strings. kafka_partition and kafka_offset are populated when the model projects _replay_partition and _replay_offset, and NULL otherwise. Rows expire after 30 days.