Styxis
Company
Knowledge

Choose the survivor before relying on DuckDB INSERT OR IGNORE

For a batch with intersecting UNIQUE constraints, successful insertion leaves the winner question open. Define the survivor policy first, then approve INSERT OR IGNORE against the selected key set.

Suppression does not choose a business winner

Conflict suppression is not a business selection policy. An input batch with several intersecting UNIQUE constraints needs an approved winner before loading. A Truthound Core set check can then compare selected keys with those actually retained. [1, 2, 3]

Reported parallel survivors

DuckDB issue #28288 reports that rows conflicting through different UNIQUE keys can produce different survivors across thread counts and runs. It distinguishes the single-thread first-row result from parallel behavior. These are reported observations rather than a reproduction performed for this article. [1]

Preserve the complete conflict group

Preserve source order and every UNIQUE key, construct the conflict groups, and state the winner rule. Compare the resulting approved key set with the loaded survivors. A row-count match cannot establish that the same customer or event survived. [1, 3]

An explicit first-row fixture

Use the reported three-row conflict chain with an explicitly illustrative first-input-row policy. That row has id=1, so survivor key 1 passes. Survivor key 2 fails even if the output count is unchanged. First-row preference is the declared business rule here, not a promised engine guarantee. [1, 3]

Provide the chosen key set

DistinctValuesEqualSetValidator in Truthound Core Python SDK 3.3.2 compares distinct values with operator-supplied expected_values. Supply the selected key column itself, with the conflict-resolution decision already made. The SDK does not make DuckDB parallel aggregation deterministic or choose the right business winner. [1, 2, 3]

Resolve or reject ambiguity

Resolve conflicts before insertion using an approved priority and tie-break, or reject ambiguous batches. Retain the rejected rows and reasons so a later policy change does not require guessing which input the engine discarded. [1, 3]

Hand over the selection reason

A useful acceptance record includes original position, each constraint key, selected key, and selection reason. This makes a changed survivor a visible policy discrepancy instead of a seemingly harmless variation in an otherwise successful load. [1, 3]

Application conditions

Establish the approved survivor set from source order and all UNIQUE keys first.

Alternatives

Resolve conflicts by priority and tie-break, or reject ambiguous batches.

Limitations

  • The SDK does not choose a survivor policy or make parallel first aggregation deterministic.

Evidence

Event checked
Subject checked
Modified