device_id is not unique, and the join knows

device_id on sensor-telemetry has 12 distinct values across 8,000 rows.

That is 666.7 rows per device on average. Join anything to this table on device_id and every row on the other side is duplicated that many times. If the other side carries a number you then sum, the sum is wrong by roughly that factor.

The reason it is hard to catch is that nothing fails. The query runs, the join is valid, and the result is a plausible-looking number that is 666.7 times too large.

-- the check, before the join
select count(*) as rows, count(distinct device_id) as keys
from sensor_telemetry;
-- rows 8,000, keys 12 → not unique

-- so aggregate to the grain you want first
with per_key as (
  select device_id, sum(temp_c) as temp_c
  from sensor_telemetry
  group by 1
)
select * from per_key;

Aggregating before the join is the fix that generalises. The alternatives — joining and then deduplicating, or select distinct over the result — both work occasionally and hide the problem the rest of the time, because they remove duplicate rows rather than duplicate contribution.

The two-line cardinality check above is cheaper than the incident. It belongs in front of every join you did not write yourself. Longer version in Joins that do not fan out and the fan-out pattern.