[BUG] Duplicate records in prices.usd_latest

[dknugo]

Description

Prices in prices.usd_latest have duplicate records

Current behavior

Duplicate records for contract_address / blockchain:
select contract_address, blockchain, count(*) from prices.usd_latest group by 1, 2 having count(*) > 1

Expected behavior

Only 1 record in table prices.usd_latest for a combination of contract_address & blockchain

Impacted model(s)

Possible solution

[imekss]

Hi!
I’ve investigated this issue and I think I’ve identified the underlying cause.

Problem

The model prices.usd_latest_historical currently defines the surrogate key using:
(blockchain, contract_address, decimals, symbol)

This becomes problematic when upstream providers assign an incorrect symbol to a contract address. I’m not sure how the mismatch occurred in this case, but around 80% of the incorrect symbols appeared on2025-09-23. When this occurs, the incremental model treats it as a new identity, which results in stale rows being preserved in prices_usd_latest_historical.

Examples of the issue (symbol value)

• contract 0xf94e7d0710709388bce3161c32b4eea56d3f91cc was DSYNC and later DSync
• contract 0xcc0733b7ba63b446624002441c4816d7fec8f4e0 was at one point DONUT and later Simpson

Proposed improvement

I propose updating the surrogate identity to use only:
(blockchain, contract_address)

Let me know if you’d like me to go ahead and open a PR, happy to submit it!