Data Doubling After Joining Multiple Transfer Tables in Dune: How to Debug Duplicate Rows

When you join multiple transfer tables and the row count doubles, it's usually because there's a one-to-many relationship between the two tables and you performed a JOIN without enforcing uniqueness in the join conditions. The debugging approach: start from the matching logic, locate the step that produces a Cartesian product, and then apply deduplication techniques.

1. First, understand why the data doubles

In Dune, common transfer tables (such as token_transfers, dex.trades, bridges_evms.flows) don't have a simple one-to-one correspondence.

Take the cross-chain bridge table bridges_evms.flows as an example. It already pairs deposits and withdrawals, but the official documentation explicitly lists a duplicate_index field—meaning even the team knows that data can be duplicated in certain cases. The pairing logic of a cross-chain bridge is that a single deposit can correspond to multiple withdrawals; joining them directly will duplicate the deposit rows.

transaction.hash is unique in the transactions table, but in logs and traces tables, the same hash can appear multiple times depending on how the transaction was executed. Not all join keys are unique across tables.

Precondition: You've already written a query with a JOIN in Dune's Query Editor and the result has significantly more rows than expected.

2. Step 1: Pinpoint which join step is causing the inflation

Step 1: Split the query and check row counts piece by piece

• What to do: Break apart the final JOIN and check the row count of each subquery or CTE separately.

• How to do it:

  • Case A (split into independent queries): Run SELECT COUNT(*) FROM (your subquery A) and B separately, record the counts. Then run SELECT COUNT(*) FROM A JOIN B ON ... and see if the joined row count equals A's count × B's count.

  • Case B (debug with CTEs): Write each intermediate result as a CTE in Dune, and run SELECT COUNT(*) for each one.

• When you're done: You've identified which JOIN step causes the row count explosion. If the joined row count is much larger than the sum of the two subquery counts, there is a Cartesian product or a one-to-many matching error.

Step 2: Check if the fields used in the JOIN condition are unique

• What to do: For each field used in the JOIN condition (e.g., tx_hash, block_time, token_address), check whether it has duplicate values in its table.

• How to do it:

  • Check left table: SELECT COUNT(*) AS total, COUNT(DISTINCT join_key) AS unique FROM left_table

  • Check right table: similarly check the unique count of the join_key.

  • If total > unique, the field is not unique in that table. Using a non-unique field as a JOIN condition leads to one-to-many matching.

• When you're done: You've confirmed that the field used in the JOIN condition has duplicates in at least one table.

Common pitfall: Directly treating tx_hash as a unique key to join logs or traces tables. A single transaction can emit multiple events and internal calls, so tx_hash appears multiple times in the logs table—this is normal, but if you are unaware of it, a direct JOIN will multiply rows.

3. Step 2: Choose an appropriate deduplication method

Step 3: Use ROW_NUMBER() to deduplicate before joining

• What to do: Before the JOIN, use a window function to pick a single row from the non-unique table for the matching key.

• How to do it:

  • Use ROW_NUMBER() OVER (PARTITION BY tx_hash ORDER BY log_index) and take only rows where rn = 1.

  • Join the deduplicated result with the other table.

• When you're done: The joined row count no longer exceeds expectations, and critical metrics (like total amount) are not incorrectly inflated.

Step 4: Use DISTINCT for final deduplication (more expensive)

• What to do: Add DISTINCT at the end of the SELECT to remove completely identical rows.

• How to do it:

  • If duplicate rows are literally identical results from the same matching logic, DISTINCT can remove them in one pass.

  • But note: Under Dune's Trino engine, COUNT(DISTINCT ...) OVER (PARTITION BY ...) can return 0 in some edge cases—this is a known Trino bug. Do not use this syntax for window-based deduplication.

• When you're done: The queried data no longer shows visible duplicate rows. However, DISTINCT is computationally expensive; use it sparingly on large datasets.

Risk warning: Relying solely on DISTINCT can mask an underlying matching logic error. There is a GitHub example of a Seaport trade matching duplicate issue—because the offer-to-consideration match is one-to-many, a simple DISTINCT can remove duplicates but at a very high computational cost, and the root cause remains. It's preferable to fix the JOIN logic before falling back on DISTINCT.

After these checks, how do you confirm you've fixed the issue?

Split your query into a "before deduplication" and an "after deduplication" version, and calculate a core metric (e.g., total transfer amount) for each. If the amount before deduplication is significantly inflated and the amount after matches the total you manually checked on Etherscan, you've correctly identified and removed the duplicate rows. Next, turn the deduplication logic into a reusable CTE so you don't have to rewrite the same logic every time you join.