Other than vehicles and Squiggoths (hoping there aren't too many Greater Knarlocs in the Front), I'm not sure what massive, high-priority, high certainty of death if engaged target I'd hope my squad got to see Astartes take out ahead of them (and like some quick-time events, I also might feel cheated that the game didn't think I was good enough to manage), but once, or maybe twice, but well spread out, I think it could be very cool to be moving up on a critical objective, only to find that the enemy is way worse than planned, and now fear is running rampant in the Guard, but then a drop pod slams in, or Termies appear, as if sent by the Emperor, and take out some Hive Tyrant/Avatar analogue, and inspire the Guard to stand their ground, and mobilize on the objective. They might live, they might die, we might even get to see them walk off the field, and know that they were awesome (WE still won the battle, of course
)
I also wouldn't mind a little Space Marines reverse. If you've ever played Warhammer 40,000: Space Marine, a game I actually quite enjoyed, you might remember the plight of the Guard on Graia. Bereft of much of their leadership, and led by Lt. Mira (one of my favorite Guard NPCs in 40k), who must stand strong, if few, against the might of Orks AND Chaos, and then Captain Titus, and his Space Marines, land, and help to liberate the planet. A fun game, if a little hard in some places, and a smidge pandering to the Astartes fanboys out there, but a game where the Astartes tried, "succeeded", but couldn't withdraw, and are now pinned down, and then saved by the Guard, would be a fun option, too.
Still, at the risk of sounding silly, it is my opinion that one of the frequent ways to make Space Marines look better is to make everyone else look worse, so in an effort to keep the players cool, I'd also say keep the Astartes to a minimum, or else the players won't seem so much less, and then why do we need Astartes? or they'll suddenly look inept, and then it's less fun. My opinion only, of course.