Errors and dead letters

Real data has records that don’t fit the mapping: a quantity of "three", a missing email in a column that can’t be null. dataferry never drops such a record silently. It rejects it, with a reason, and the run’s on_error policy decides what happens next: stop, or keep the record in a dead-letter table and carry on.

What rejects a record

A record is rejected, as a whole (none of its rows in any target are written), when:

• an expression fails for it: a strict conversion (int('ten')), a value that doesn’t fit a lossy typed column, an error in a where: predicate, a one join that finds two matches, or an unnest: of a value that isn’t an array;
• a value is null in a column that can’t be null (a key column, or one declared nullable: false);
• the source can’t read it as its entity: for MongoDB, a document whose field has a type the sampled schema doesn’t allow, or a field the sample didn’t see (MongoDB).

A record that a where: excludes is filtered, not rejected: that is the mapping working as written, and it is only counted.

on_error: fail

The default. The first rejected record fails its partition: nothing of that partition is committed, the run leases no new partitions, the ones in flight finish, and the run exits 6.

  # The default, written out: the first rejected record fails its partition
  # and the run.
  on_error: fail

With the guide’s signups example (five documents, two of which can’t be mapped):

run ba4e513cd87644dd8b44ad24a254e0e8
  flow `accounts`: TxnWithState
run ba4e513cd87644dd8b44ad24a254e0e8: not complete (0 done, 1 failed, 0 pending, 0 leased); 1 partitions; read 0, dead-lettered 0; wrote nothing
error[F0602]: flow `accounts`: partition `accounts/signups-0` failed: transform output broke its contract: record [Int(3)] was rejected for `accounts`: accounts.email can't be null, but `lower(email)` is null
verification of `warehouse` incomplete: the run isn't finished; report: ./.ferry/runs/ba4e513cd87644dd8b44ad24a254e0e8/report.md
run `ba4e513cd87644dd8b44ad24a254e0e8` has failed partitions; `dataferry retry --failed ba4e513cd87644dd8b44ad24a254e0e8` re-runs them
select count(*) from accounts;
 count 
-------
     0
(1 row)

Fix the data or the mapping, then start again. A changed flow is a new config hash, so it needs a new run; with the data fixed and the config unchanged, dataferry retry --failed <run-id> re-runs only the failed partitions.

on_error: dead_letter

  # Keep going: a rejected record goes to _ferry_dlq on the sink, until more
  # than 10 have been rejected in the run.
  on_error: dead_letter
  max_errors: 10

A rejected record is written to the table _ferry_dlq on the flow’s sink, in the partition’s own transaction, and the run carries on. The same five signups:

run 68abfa6b37944a45a814e4ce6adee339
  flow `accounts`: TxnWithState
run 68abfa6b37944a45a814e4ce6adee339: complete; 1 partitions; read 5, dead-lettered 2; wrote accounts 3
verification of `warehouse` passed; report: ./.ferry/runs/68abfa6b37944a45a814e4ce6adee339/report.md
select * from accounts order by id;
 id |      email      | plan | seats 
----+-----------------+------+-------
  1 | ana@example.dev | pro  |     5
  2 | bo@example.dev  | free |     1
  5 | di@example.dev  | free |     2
(3 rows)

select source_id, target, error from _ferry_dlq order by source_id;
 source_id |  target  |                             error                             
-----------+----------+---------------------------------------------------------------
 [3]       | accounts | accounts.email can't be null, but `lower(email)` is null
 [4]       | accounts | accounts.seats = `int(seats)`: int(): `ten` is not an integer
(2 rows)

select record from _ferry_dlq where source_id = '[4]';
                                           record                                            
---------------------------------------------------------------------------------------------
 {"_id": 4, "plan": "team", "email": "cy@example.dev", "seats": "ten", "_ferry_missing": []}
(1 row)

The run is complete and exits 0: three accounts are written, and two records are dead-lettered with the reason. The summary line and the report count them.

Only driver records can be dead-lettered. If a record of a joined entity can’t be read, its partition fails, whatever on_error says.

max_errors

max_errors is how many records the whole run may dead-letter. Once committing a partition would bring the run’s total above it, that partition is rolled back (its rows and its dead letters), marked failed, and the run stops with exit 6:

  # Two records are rejected, one more than this run accepts.
  on_error: dead_letter
  max_errors: 1

run cbfe8b46a34b4250ab799b56f725b2a3
  flow `accounts`: TxnWithState
run cbfe8b46a34b4250ab799b56f725b2a3: not complete (0 done, 1 failed, 0 pending, 0 leased); 1 partitions; read 0, dead-lettered 0; wrote nothing
error[F0602]: flow `accounts`: partition `accounts/signups-0` failed: runner: max_errors exceeded: this partition would bring the run to 2 dead-lettered records, more than max_errors (1); it was rolled back and the run stops
verification of `warehouse` incomplete: the run isn't finished; report: ./.ferry/runs/cbfe8b46a34b4250ab799b56f725b2a3/report.md
run `cbfe8b46a34b4250ab799b56f725b2a3` has failed partitions; `dataferry retry --failed cbfe8b46a34b4250ab799b56f725b2a3` re-runs them
select count(*) from accounts;
 count 
-------
     0
(1 row)

• The count is run-wide and includes the partitions committed by earlier attempts of the same run: a resumed run doesn’t get a fresh budget.
• max_errors is in the config hash, so you can’t raise it to resume a run: that is a new run.
• Without max_errors, nothing stops a dead-lettering run, and plan warns about it (W0401).
• In this release, on_error and max_errors are run-wide. A flow may repeat them in its runtime: only with the run’s values; a different value is refused by plan, so it is never silently ignored. Per-flow policies and budgets are planned for a later release.

A run that dead-letters records but stays within max_errors still exits 0. To fail a CI job when any record didn’t land, assert the count you expect: count(accounts) == 5 fails, with exit 7, as soon as one of the five signups is missing (Verification). count(accounts) == source.count() can’t catch it, because source. aggregates cover only the records the run wrote.

The _ferry_dlq table

Created in each sink’s schema for flows with on_error: dead_letter, with the DDL plan shows:

The primary key is (flow, source_id): one row per record and flow. A record dead-lettered again (by a re-run partition, or a later run) replaces its earlier row, so the table never fills up with duplicates.

Inspecting dead letters

It is an ordinary table:

-- what went wrong in a run, grouped
select flow, target, error, count(*)
from _ferry_dlq
where run = '<run-id>'
group by flow, target, error
order by count(*) desc;

-- the records themselves
select source_id, record
from _ferry_dlq
where flow = 'accounts' and error like '%seats%';

The report’s Dead letters section shows the same grouping, with an example source_id per reason, and checks that _ferry_dlq holds exactly the number of records the state store counted for the run.

Three things to know about record:

• For a record the mapping rejected, it includes the column _ferry_missing: the list of fields that were absent from a MongoDB document (as opposed to null). It is how the engine tracks missing values.
• For a MongoDB document the source itself rejected (it doesn’t fit the inferred schema), it is the document as relaxed Extended JSON, without _ferry_missing, and target is null.
• A MongoDB field read as json (because its type varies between documents) holds its value’s JSON text, as a string. In the quickstart’s dead letter, qty: "three" shows up as "qty": "\"three\"".

Re-running dead-lettered records

There is no command to replay dead letters in this release. Fix the records in the source (or the mapping), then run again: a new run reads every record again, writes those that now map, and dead-letters the rest again, replacing their rows in _ferry_dlq. Rows of records that now map stay in _ferry_dlq from the earlier run until you delete them:

delete from _ferry_dlq where run = '<old-run-id>';

Other failures

Not every failure is a record’s fault:

Troubleshooting goes through the common ones.