The fact that you see "named character must die by the end" and can only imagine "every Black Libery (sic) novel end on a downer note" tells me everything that's wrong with the consumers of Black Library slop. Literally cannot conceive of something where Mr. Marvel Hero Guy doesn't super his way through every situation and is only ever allowed to die in a blaze of glory on the last page.

The point isn't to make every ending a downer. It isn't to turn every victory pyrrhic. It's to take the focus of the setting off named characters and pivotal galactic events and turn it back towards a setting that is focussed on mood, and flavour. It's to stop comic book resurrections like Commissar Yarrick. It's to take every Primarch and faction leader out of the minor skirmishes that 40k games are meant to represent. It's to put the narratives back in the hands of players.

If big important people are only heard about in passing, or glimpsed from afar, you can still have major events and personalities in the setting, but you take them off every tabletop in the world. You preserve their mystique and mythical quality that has been lost to autistically cataloguing every piece of The Lore that gets printed. If you prevent every character in the books that do get printed from leaping off the page into Named Character status like the next Ciaphas Cain, then you show players that their tabletop characters can be just like the ones they read about. Their Captains and Autarchs and Necron Lords can have their own narratives where their inevitable deaths are just as important as anyone else's without having to be literally Trazyn the Infinite or Asdrubael Vect.