Rows your destination discards
Sometimes a destination accepts a row and does not keep it, without an error. The usual
cause is something on the destination table itself: a BEFORE INSERT trigger that returns
NULL, or a rule that skips the row. The write succeeds, and the row is not there.
The same can happen to a delete: a BEFORE DELETE trigger that returns NULL, or a rule such as
ON DELETE DO INSTEAD NOTHING, keeps the row your source deleted. The delete succeeds, and the
row is still there.
We check every insert, update and delete of your rows, so we notice (one exception in a table kept in history, below). Each pipeline has a setting for what happens next.
Where to set it
Section titled “Where to set it”- New pipeline: step 6, Sync settings: open More settings below “Rows that are already there” for the question “Rows your destination discards”. 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_discarded_rowonPOST /v1/pipelines, orPUT /v1/pipelines/{id}/discarded-rows. See the API reference.
The two answers
Section titled “The two answers”Whichever you choose, every discarded row is reported. The pipeline’s history names the
table, the key columns and how many rows, and an alert stands for 24 hours. We do not store the
rows or their key values. By default the pipeline records each such row’s key in your own
database, in the table _reyansh_rejected_v1 in the schema it writes, and the report says so.
Its reason column tells the two kinds apart: discarded for an insert or update your
destination did not keep, not_deleted for a delete it did not apply (that row is still there).
| What | Keep syncing (default) | Stop the table (or every table, per When one table stops) |
|---|---|---|
| The table | carries on | stops and waits for you: the table that holds the row, not the pipeline. The pipeline’s other tables keep syncing, unless you set When one table stops to Stop every table (see Schema changes). A pipeline whose source is MySQL, Stripe or the platform history export syncs one table, so there the pipeline stops instead, and it does not restart on its own: fix the trigger or rule, then start the pipeline again. |
| The discarded row | is not in your destination, and carrying on does not bring it back (a discarded delete: the row is still there) | is not marked as synced, and neither is anything of its table after it |
| To get the row | look it up in your source by the table and key columns the history names (a discarded delete: remove the row it names yourself, if it should go) | fix the trigger or rule, then Resume the table. The row is written (a discarded delete is applied). |
| The cost | the row is missing (a discarded delete: a row your source deleted is still there) | 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 “stop” costs
Section titled “What “stop” costs”A stopped table keeps its place. A query-based pipeline, or a source with no change log, holds nothing for it; a MySQL source keeps its binlog only by its own retention. For a Postgres source the place is the replication slot, and the slot keeps the WAL from the table’s place onward, so the WAL on your source grows until you resume the table, even while the pipeline’s other tables keep syncing. If your source runs short of room for it, the place is let go, and resuming copies the table again.
Before its first save, the pipeline stops instead. A new pipeline that skips the copy of existing rows has no place to keep a table at until it has saved once, so a discarded row in its very first batch stops the pipeline, which does not restart on its own: fix the trigger or rule, then start the pipeline again.
The row can be replayed only while your source still holds those changes. If the slot is
dropped, or your server removes the WAL it holds (for example because of
max_slot_wal_keep_size), the changes are gone and resuming cannot write them.
So choose stop when a missing row is worse than a paused table, and fix the cause promptly when it fires.
A table kept in history or append
Section titled “A table kept in history or append”The same answer applies. A version of a history table, or a row of an append table, that your destination’s trigger skips is reported as a discarded row, named in the record by its key, and the answer you chose decides what happens next. In an append table a delete is a row of its own (the row that records it), so a skipped one is reported as a row not kept, never as a row still there; a truncate’s row holds no key, so a skipped one is counted in the report and not recorded.
In a history table a version is found by its key and its position, so a BEFORE INSERT trigger that
changes a key column (lowercasing an email, for example) writes the version under another key: it is
reported as a discarded row, although it is in the table under the key your trigger gave it, because
the pipeline cannot find it at its own key to close or replace it later. If that batch is written again
after a restart, your trigger moves the version onto the one already there, PostgreSQL refuses it as a
duplicate of the table’s unique key and position, and the table stops in PostgreSQL’s words; nothing is
doubled or lost.
What this setting does not cover
Section titled “What this setting does not cover”-
In a table kept in history, a trigger that skips the pipeline’s own update or delete of an earlier version is not detected. The pipeline updates an earlier version to close it when a newer one arrives, and deletes one a newer version of the same change replaced. It also updates a version it has already written: when one large source transaction’s changes to a row reach your destination in more than one batch, and when a batch is written again after a restart. A
BEFORE UPDATEorBEFORE DELETEtrigger that returnsNULLthere leaves that version as it was, so it can stay current beside the newer one, or keep the values an earlier batch wrote, and nothing is reported. A version itself that your trigger skips is reported, as above. -
A delete your destination did not apply is reported as a discarded row. A
DELETEstopped by aBEFORE DELETEtrigger or aDO INSTEAD NOTHINGrule reports 0 rows, which is also what a delete of a row that is already gone reports (for example when the pipeline replays a batch after a restart). So each delete also asks, in the same statement, whether the row was there before it; only when it was, and nothing was removed, do we look the key up again: if the row is gone (a trigger may remove it itself), nothing is reported; if it is still there, the delete is reported as discarded, in words that say the row is still in your destination. On a table with a rule onDELETEthe delete is sent on its own, as before, and its 0 is looked up; with aDO INSTEADrule we look up every delete, because the count then describes what the rule did. A row-level security policy that applies to the role the pipeline writes as is refused before anything is written, so a policy cannot hide a delete. Two things we cannot see: anAFTER DELETEtrigger that puts the row back (the delete itself removed it), and a row another session inserts with the same key in the moment between our delete and our look-up, which is reported as discarded. -
A value your destination refuses with an error (a string too long for its column, text that is not a valid
uuidor JSON) is a different event with a setting of its own: see Values your destination cannot take. By default the value is cut or written as NULL and the pipeline carries on. -
A row your destination refuses for another reason (a constraint it breaks, a
NOT NULLcolumn) stops the table that holds it, whatever either setting says; the pipeline’s other tables keep syncing, unless When one table stops says to stop every table. -
Snowflake destinations: the setting does not apply. Snowflake reports a row it cannot load as an error, never as a discard, so this situation does not arise there.