As someone who didn’t play them back in the day, I feel like SotN holds up but Super Metroid doesn’t. Just as another opinion. I couldn’t really get into metroid fusion either. To me it feels like the moment-to-moment action gameplay is too clunky in the early metroid games I’ve played, even if the exploration element is neat. I did enjoy playing SotN for the first time a couple of years ago though. It’s been a while since I played either, so they’re not totally fresh in my memory - I guess it’s possible that I’m just more forgiving of clunky melee combat than clunky shooting.

Tangentially related, always amuses me how “metroidvania” has become the genre name, when originally it was just a way that reviewers poked fun at the big change between SotN and earlier castlevanias. They were like “this isn’t what I expect from a castlevania, it’s a great game but maybe they should have named it metroidvania”, and the name stuck. Another odd fact about that terminology is that according to interviews, the SotN designer never played metroid - they were inspired by the non-linear exploration with different routes opened up by items/upgrades in Zelda games (although obviously adding that to castlevania’s platformer gameplay makes it more closely resemble metroid). So it should probably be considered a zeldavania.