I run a decently large zfs setup (in the single digit petabyte range) and I have never actually had a spinning disk trigger a zfs checksum issue that wasn't shortly followed up by the disk failing completely in the next week or so. And this is multiple years of this pattern.

While it has to happen occasionally, it does seem to be a pretty rare event in my experience. Drive actually failing are way more likely to happen it seems.