[BUG] oneinch project_swaps counts the same intent-settled swap twice (classic + intent rows)

[mrAndreyIsachenko]

What's wrong

oneinch.project_swaps (and the per-chain oneinch_<chain>.project_swaps) contains two rows for the same swap when that swap is settled against a 1inch limit/Fusion order: one row with intent = false / mode = 'intra-chain: classic: direct' and no order attached, and one row with intent = true / mode = 'intra-chain: intents: auction' carrying a real order_hash.

Both rows carry the same tx_hash, the same call_trace_address and the same amount_usd, so any consumer that sums amount_usd — market-share reporting being the obvious one — double-counts that volume.

They differ in call_trade_id: the classic row uses tx_hash || call_trace_address, the intent row uses order_hash. Since call_trade_id is part of the model's merge unique_key

unique_key = ['blockchain', 'block_month', 'block_number', 'tx_hash', 'second_side', 'call_trace_address', 'call_trade_id']

the later write cannot replace the earlier one, and both survive.

Scale (ethereum, measured 2026-08-21)

Counting affected pairs per day over 2026-06-01 .. 2026-08-20:

• 52 affected days, 1,191 pairs, ~$3.15M of double-counted amount_usd — ethereum, project = '1inch', second_side = false only.
• Every pair has min(amount_usd) = max(amount_usd) within the pair, i.e. it is a plain double count, not two legs of one trade.
• Nothing before 2026-06-24; every day from 2026-06-24 onward is affected except a handful.
• Other chains on the same day (2026-08-19): base 12 pairs, bnb 1, polygon 1.

The striking part is when the pairs occur. On each day they all fall inside a ~10-minute window, and that window moves as a block over time:

period	window (UTC)
2026-06-24 .. 06-29	06:08 – 06:20
2026-06-30 .. 07-01	05:11 – 05:19
2026-07-05 .. 07-07	04:10 – 04:16
2026-07-08 .. 2026-08-20	03:01 – 03:18

(two days also show a single second cluster: 2026-07-07 at 14:16 and 2026-08-06 at 15:15)

Hypothesis — explicitly not verified

The clustering behaves like a scheduled job rather than like a property of the data, including the step changes in time-of-day. That fits a daily incremental run boundary: a swap whose lo order has not landed in oneinch_<chain>.lo at run time gets written as classic, and is written again as intent on a later run under a different call_trade_id, leaving the classic row behind.

I cannot verify this — it needs visibility into the scheduler that runs daily_spellbook. Everything above the line is measured; this paragraph is a guess that fits the measurements.

Verified against the source: for the affected rows, the intent-side order_hash values do exist in oneinch_ethereum.lo at the same block_number, so the order-attached row is the correct representation and the classic row is the stale one.

Reproduce

select
    date(block_time) as block_date,
    count(*) as pairs,
    round(sum(usd)) as usd_counted_twice,
    min(block_time) as first_pair,
    max(block_time) as last_pair
from (
    select
        block_number, tx_hash, call_trace_address,
        min(amount_usd) as usd,
        min(block_time) as block_time
    from oneinch_ethereum.project_swaps
    where project = '1inch'
      and second_side = false
      and block_time >= timestamp '2026-06-01'
      and block_time <  timestamp '2026-08-21'
    group by 1, 2, 3
    having count(*) > 1
       and count(distinct intent) > 1
)
group by 1
order by 1

To see a single pair side by side (2026-08-19):

select intent, mode, call_trade_id, order_hash, amount_usd, "user"
from oneinch_ethereum.project_swaps
where tx_hash = 0x041dccda14489aecd52fdd0ba51a6ec6aaaaedc3d863602c83fe0544452e0248

How this was found

While porting the project_orders / project_swaps tree into our own dbt project, we reconciled our ethereum build against the live Spellbook tables for 2026-08-19. Everything matched to the cent on 24 of 26 projects; the only unexplained gap was 1inch being 17 rows / $24,735 lower on our side, with the same distinct-tx count. Those 17 rows turned out to be these duplicates. A fresh full-window build produces only the order-attached row, which is what pointed at the incremental path rather than the decode logic.

Possible fixes

I did not open a PR because the right fix depends on intent (pun accepted) that maintainers know better than I do:

1. dedupe on (tx_hash, call_trace_address) after the merge, preferring the order-attached row;
2. drop call_trade_id from unique_key, so the intent row overwrites the classic one;
3. hold the incremental window back far enough that lo is always settled before the swap is written.

Happy to send a PR for whichever direction you prefer, and to add a dbt test that fails on a classic+intent pair so this cannot come back silently.

[mrAndreyIsachenko]

Retracting option 2 from my own list — "drop call_trade_id from unique_key" is wrong, and I should not have offered it. I measured it afterwards instead of before. Please disregard it; options 1 and 3 stand.

call_trade_id is the only thing separating multiple fills settled by a single call at a single trace address. Measured on oneinch_ethereum.project_swaps itself (not on my copy), 2026-08-19, with the classic+intent pairs this issue is about deliberately excluded:

select
    count(*) as groups,
    sum(rows) as rows_in_them,
    sum(rows) - count(*) as rows_that_would_be_lost,
    round(sum(usd)) as usd_in_them
from (
    select
        block_number, tx_hash, second_side, call_trace_address,
        count(*) as rows,
        sum(amount_usd) as usd
    from oneinch_ethereum.project_swaps
    where block_time >= timestamp '2026-08-19'
      and block_time <  timestamp '2026-08-20'
    group by 1, 2, 3, 4
    having count(distinct call_trade_id) > 1
       and count(distinct intent) = 1
)

groups	rows in them	rows that would be lost	amount_usd in them
229	490	261	$4,687,259

All of them are CoWSwap batch settlements (flags on: auction,direct,user 270 rows, auction,user 220 rows) — many orders filled by one call, each with its own order_hash and therefore its own call_trade_id, all sharing one call_trace_address. A unique_key without call_trade_id would keep one row per settlement call and silently drop the other 261 fills, on ethereum, on a single day.

So the fix has to leave the key alone. That leaves the two options that do:

1. dedupe after the merge on (tx_hash, call_trace_address), preferring the order-attached row;
2. hold the incremental window back until lo has settled.

One more, from reading dbt-trino rather than assuming — worth stating because the obvious guess is wrong in both directions:

• incremental_strategy = 'delete+insert' does not help: trino__get_delete_insert_merge_sql deletes by unique_key match plus the predicates, so a stale row whose key is absent from the new source survives it.
• incremental_strategy = 'microbatch' does: trino__get_incremental_microbatch_sql deletes the batch window by predicate alone, with no key matching, then reinserts. The stale row disappears and unique_key is untouched. The cost is a full rewrite per batch and an event_time config.

Still happy to send a PR for whichever of these you prefer, plus a test that fails on a classic+intent pair.