Is health and magicka useless in Skyrim late game?

Solution 1:

Does anyone have a counter point to this?

Not really. The closest thing I have to a counterpoint is the general observation that late-game Skyrim is hilariously unbalanced to begin with, so any character build is viable, even if it's severely suboptimal.

In late-game Skyrim, it's fairly easy to hit the armor cap, the magic resist cap, all three elemental resistance caps, and still have a Standing Stone slot left over for the Atronach Stone (which gives 50% spell absorption, and stacks with all of the above). At that point, you're nearly impervious to all forms of damage and can tank almost any attack in the game. As you note, sneak is also broken, as is the "Fortify [spell school]" enchantment. There is also a well-known exploit that takes advantage of the fact that neither Fortify Enchanting nor Fortify Alchemy has a cap, and so they can buff each other to absurd heights and completely break the crafting system. As a result of all of these things, you can inflict damage with whatever means you like; it hardly matters as you will never run out of Health anyway.

And that's before we start doing really broken things, like becoming a Vampire Lord. Just look at these numbers for (the VL's version of) the Vampiric Drain spell. It's absurdly powerful even at level 1, provided that Serana is not following you, and it is clearly intended to be spammed (judging from how it is permanently equipped to your right hand, relatively cheap to cast, etc.).

Were there any changes to Special Edition that make health and magicka more viable? Last time I played was before special edition.

No. Special Edition's gameplay is almost perfectly identical to Legendary Edition. If you want a more interesting late game, you will need to install mods such as Vokrii or Ordinator (both of which are designed to make a larger set of character builds reasonable, and limit the overpowered-ness of some builds).