Skip to content

Values your destination cannot take

A value in your source can be one its destination column does not take: text longer than a varchar(20) holds, text that is not a valid uuid, a number wider than a numeric(10,2) keeps. A PostgreSQL destination refuses the whole row with an error. Each pipeline has a setting for what happens next to a value it can change to fit; a value nothing can change to fit stops its table, whatever the setting says.

One value stops only its own table. The table that holds it stops, the pipeline’s other tables keep syncing (unless you set When one table stops to Stop every table: see Schema changes), and the history and the Alerts page name the table, the column and the way back.

The setting is read by PostgreSQL destinations. What a Snowflake destination does is at the end.

  • New pipeline: step 6, Sync settings, under More settings: the question “Values your destination cannot take”, below “Rows your destination discards”. Change the value and keep syncing is selected for you.
  • An existing pipeline: the same question on the pipeline’s page, under Settings › Data handling. Choose, then Save. If the pipeline is running, it restarts on its own to use the new answer.
  • API: on_rejected_value on POST /v1/pipelines, or PUT /v1/pipelines/{id}/discarded-rows. See the API reference.
What Change the value and keep syncing (default) Stop the table (or every table, per When one table stops)
The value is changed (below): cut, written as NULL, or with a NUL character removed, and the row is written is not changed, and the row is not written
The table carries on stops at a row holding a value the first answer would change, and waits for you. The pipeline’s other tables carry on, unless When one table stops is set to Stop every table. A pipeline whose source is MySQL or Stripe syncs one table, so there the pipeline stops instead, and it does not restart on its own: change the column or this setting, then start the pipeline again. The platform history export syncs one table too, but a value refused from it is ours: the pipeline restarts (see below).
How you are told a history row, “Values changed when written”, naming each change, and a warning for 24 hours a history row, “A table stopped syncing”, naming the table and the column
The cost your destination holds a shorter value, NULL, or the value without its NUL character, where your source holds the original a PostgreSQL source keeps its change log (the WAL) from where the table stopped while it waits; a MySQL source keeps its binlog only by its own retention, and other sources hold nothing

What “change the value” changes, and what it never does

Section titled “What “change the value” changes, and what it never does”

The row is always sent as it is first. Only when your destination has refused it does the pipeline change a value and send the row again, once. The one exception is a NUL character (U+0000): PostgreSQL refuses it before it reads the row at all, so it is removed before the row is sent.

The value What is written
Text longer than its varchar(n) or char(n) column The first n characters. Characters, not bytes: a multi-byte character is never split.
Text that is not a valid uuid, in a uuid column NULL, where the column allows NULL
Text that is not valid JSON, in a json or jsonb column NULL, where the column allows NULL
Text holding a NUL character (U+0000), for any column but bytea The same text with each NUL removed. A MySQL, Stripe or platform history source can send one; a PostgreSQL source cannot, because PostgreSQL text never holds one.
  • A key column’s value is never changed.
  • A column that does not allow NULL is never given one. That row is still refused.
  • JSON is checked as its own column reads it. A json column stores the value it is given unchanged, including a \u0000 escape, an unpaired surrogate escape and a repeated key, so it gets NULL only for text that is not JSON at all. (From a PostgreSQL source that value is your source’s own text; a REST source’s JSON arrives rewritten as compact JSON, as the PostgreSQL destination guide says.) A jsonb column refuses those two escapes, so there they are written as NULL. It also refuses a number beyond numeric’s range and nesting deeper than the server allows; those are never written as NULL, and stop like any other refused value. (Earlier releases checked and wrote a json column as jsonb: a history line from one of them may say the NULL may have been our check’s, and it may.)
  • Any other refused row stops its table, under either answer: below.
  • A table kept in history or append is answered the same way. Its changes are written in one transaction, so when your destination refuses a value in it, the whole set is sent once more with each such value changed, and the report names each column. A later version that does not send that column keeps the changed value, and sending the same changes again writes the same values.

Each report on the pipeline’s history names the table, each column, what was done (cut, written as NULL, or a NUL character removed), in how many rows, and why. The warning on the Alerts page names the tables and columns for the 24 hours after the newest report.

It never shows a value. We do not store the values, cut or original. The report names columns and counts, not rows.

The original is kept in your own database. By default the pipeline records it in the table _reyansh_rejected_v1, in the schema it writes, with the row’s key. The report says where it was recorded, or that it was not and why. record_in_destination: false switches that record off, and what is not recorded while it is off cannot be recovered later.

An original that held a NUL character cannot be stored as text either, so for it, and only for it, the record’s reason is nul_removed and original_value holds its bytes in PostgreSQL’s hex form: \x and two hex digits a byte. decode(substr(original_value, 3), 'hex') gives them back exactly. The reason says which form an original is in, so a text that merely begins with \x is never read as hex. A value that was also cut has a second row, truncated, whose original is the value with its NUL removed.

This answer stops the table that holds the value, not the pipeline: the pipeline’s other tables keep syncing, unless When one table stops is set to Stop every table. A table stopped while it is copied stops alone, whatever that setting says. Until the pipeline has saved its place once (below), the whole pipeline stops instead. Nothing is cut or written as NULL. At a row holding a value the first answer would change, the table stops: rows of it written before that row stay in your destination, and nothing of it after that row is marked as synced. The history names the table, each column a change would have touched and the reason (too_long or not_a_value), never the value. The row is not tried again, so it may hold something else your destination refuses, which shows only once these columns are dealt with.

Where the destination database’s encoding refused a character (its history names the encoding), no column change helps, and neither does a re-sync: a PostgreSQL destination must be a UTF8 database, and a pipeline does not start on one in another encoding (the database’s encoding). Otherwise, to carry on, change the column in your destination so it takes the value (for example, a longer varchar), or choose the other answer. Then resume the table, and the row is written.

Some values are refused under either answer, because nothing the pipeline could change would make the column hold them:

  • a number wider than a numeric(p,s) column keeps before the point, or any number too wide for a smallint, integer or bigint column;
  • Infinity or -Infinity for a numeric column that declares a precision;
  • a NUL character in a key column, which is never changed, or bytes that are not UTF-8, for a text column;
  • a key value the column would store as a different value (more digits after the point than it keeps, for example), which could make two source rows one.

A character your destination database’s encoding cannot hold is not on this list, because it never reaches a column: a PostgreSQL destination must be a UTF8 database, and a pipeline does not start on one in another encoding (A PostgreSQL destination).

Such a row stops its table, and only its table (unless When one table stops says to stop every table). The pipeline’s other tables keep syncing, and the table waits for you:

  • What you are told: a history row, “A table stopped syncing”, and an alert, naming the table, the column and why, never the value.
  • What is kept: nothing of the table after that row is marked as synced, and the table keeps its place, so nothing is lost. For a pipeline that reads your source’s change log, your source keeps that log from the table’s place while it waits; if your source runs short of room for it, the place is let go, and resuming copies the table again. A table stopped while it was being copied lets its place go: resuming copies it again, and that copy is billed as any copy. Rows of that table written before the refused row, in the same source transaction, can already be in your destination meanwhile; resuming writes them again, by key, with the rest.
  • The way back: change the destination column to a type that holds the value (for example, numeric without a precision for a number too wide, or bigint for one too wide for integer), then Resume the table: it writes its changes from where it stopped. If no type of that column can hold the value, correct the value at your source, then Re-sync the table: it is copied again from your source, by the table’s mode (Re-sync and modes). For a pipeline that reads your source’s change log, correcting the source alone and resuming does not help: the resume writes the change as the log recorded it, the refused value with it.

A refusal that passes by itself restarts the pipeline, not the table. A lock not granted in time, a statement timeout or a lost connection is retried by restarting the pipeline on its own, and its history says so at each restart.

A timestamp not in PostgreSQL’s ISO form, or one with a UTC offset other than zero for a timestamp column (one without a time zone), is not one value: it stops the pipeline. Your source renders every timestamp by its DateStyle and TimeZone settings, which the pipeline sets to ISO and UTC on its own connections, so such a timestamp means one of those settings did not hold, for every table at once. The pipeline stops and restarts, and its history says what to change: for the form, set DateStyle to ISO on your source, or check that the pipeline’s connection still sets it; for the offset, make the destination column timestamptz, or have your source send UTC.

From the platform history export, a refused value is ours. That export is our own source, written into tables made from its guide’s statements, so a value one of them refuses is a fault on our side, never a value for you to correct: the pipeline stops and restarts rather than stopping the table, a run that ends in failure is not charged, and its history says so. If you changed a column from the guide’s statement, change it back; otherwise contact us.

A column’s fill keeps its own stop. When the existing values of a column added at your source are copied (a fill) and one of them is a value like these, the table stops as a column’s fill that failed: resuming copies that column’s existing values again, so changing the destination column, or correcting the value at your source, then resuming, is enough.

Before its first save, it stops the pipeline. A new pipeline that skips the copy of existing rows has no place to keep a table at until it has saved once. A row like this in its very first batch stops the pipeline for you, with the same words, rather than restarting into it. Change the column, then start the pipeline again.

A Snowflake destination does not read this setting. Under either answer:

  • A value Snowflake cannot hold (an infinite date or timestamp, a timestamp before the Common Era, the time 24:00:00, a numeric NaN or infinity) is written as NULL.
  • The row’s _REYANSH_META column keeps a note with the original text and the reason.
  • The pipeline’s history says so (“Values changed when written”), naming each column, how many rows and why, and a warning alert is raised. In a table kept in history or append the note is kept in the row, but the pipeline’s history does not report it yet, and no alert is raised.
  • Never in a key column. A key is never written as NULL: a row whose key Snowflake cannot hold is not written, and its table stops until you act. The pipeline’s other tables keep syncing.
  • A number is never rounded. One with more digits than a declared numeric(p,s) column’s NUMBER(p,s) holds is not written, and its table stops until you act.
  • A destination whose wide numerics land as NUMBER(38,s) (its own setting, chosen when it is created) writes a value of such a column with more digits than that holds as NULL in a table synced by a key, its exact text in _REYANSH_META under does_not_fit, told in the history as above. Copying the existing values of a column added to such a table (a fill) stops the table at such a value instead, as a fill that failed, and writes nothing as NULL: re-sync the table, and the copy writes such a value as NULL with its exact text kept. A table synced without a key (each row identified by a hash of all its values) keeps these columns as exact text, and a key column is never one: keys stay exact text. See Snowflake: wide numerics as numbers.

A Snowflake table stopped for a value is told the same way, with the way back that works for it:

  • A key Snowflake cannot hold in any column (or longer than its column holds): correct the value at your source, then Re-sync the table. Resuming would meet the same change again.
  • A number with more digits before the point than its NUMBER(p,s) keeps, where a precision of 38 or less at the same scale holds it: widen the column’s precision in Snowflake, which it does in place, then Resume the table.
  • Any other number: Snowflake never changes a NUMBER’s scale in place and holds 38 digits at most. A column your wide-numeric setting made NUMBER(38,s) takes such a value as NULL, its text kept, when you Re-sync the table; any other column needs the value corrected at your source, then a re-sync.

A value Snowflake refuses for a reason of ours (a value in a form the pipeline does not recognise, or an array or JSON value it could not read) stops the pipeline, not the table, as before: a defect on our side is not yours to fix.