First, the "climate change" science could be argued, but for the purposes of this debate, I'm granting it as true as you describe and imply it.
Second, athletes that produce a significant "carbon footprint" only exist because they're better than others. 99% of athletes don't make it because they aren't good enough. Meaning, only the top 1% have a "cost" to the environment. Not only are these just a few thousand, maybe tens of thousands, in total, out of billions, but the numbers don't increase with exposure due to the nature of sports, competition, and what I've mentioned.
Third, being sustainable, "climate friendly", etc., isn't the main priority for anyone or anything to be considered "great" or a "role model" as you are assuming. The first metric is always merit or competence. However, it is the case that even if someone or something is the best or the greatest but they are polluting, let's say, it would immediately disqualify them to the "bottom ranking" regardless. In any case, the first consideration remains merit or competence "at solving the problem".
Fourth, I completely agree with you that scientists, engineers, physicians, etc., that contribute to better outcomes should be role models. However, this being true and "athletes should be role models" aren't mutually exclusive. You're implying that either athletes are role models or scientists are, which doesn't follow. Society, generally speaking, not "appreciating" scientists is a different problem that requires different solutions; it's beside the point.
Fifth, it's also true that "fame doesn't equal wisdom". If the prompt was "Should entertainers be role models?" I would agree with you, for the most part. However, athletes, in principle, only exist because they have certain values of determination, work ethic, a healthy lifestyle, etc. Without these qualities, they couldn't compete, and without competition, we wouldn't know who's the best. So they're famous as a consequence of some good values, inherently. Meaning fame isn't necessarily bad.
Sixth, hypothetically speaking, if society decides to completly shift the attention to role model scientists, it would also cost the enviroment from media attention, many more people moving to conventions and lectures, etc. I'm not suggesting the cost would be higher or lower in comparisson, just making the point that doing anything has a cost on the planet.
Seventh, role models aren't required to represent all good values. It's only natural that role models exist in every walks of life that represent different values, paitence, compassion, professionalism, determination, creativity, enginuity, efficiency, etc. To assume role models need to represent every or most positive values is unreasonable and impossible. No one is perfect and, again, i'm not saying certain negative traits can't outweigh certain positive values, they do. But, for example, if an athelete demonstrates good values and is responsible with his/her choices towards the enviroment in a normal way, then there's nothing wrong with this. The ahelete might not be the best role model regarding "climate change" but he/she represents other good values. Just the same as people who are role models for contributing for "climate change" solutions, don't possess every or most positive values. And it's illogical to expect them too.
In conclusion, you're incorrectly assuming or implying a "zero-some game" in some cases, "false binaries" in others and expecting perfection from "role models". As long as there is a significant positive balance between demonstration good values and being neutral towards others, then he/she, in principle, is a perfectly legitimate role model.