I think my favourite most awe-inspiring moment was during the Siege of Windhelm. It wasn't necessarily brought on by the scenery or the destruction, but moreso by the determination of both sides. I was playing through the Civil War questline as an Imperial Legionnaire on Master as soon as I could for a more realistic feel; and doing so enhanced my experience in that battle tenfold. At one point, when we were fighting in that narrow pathway between the mansions in the wealthy district, it got to a point where it was us and them, loads of bodies, fierce fighting, no movement. Both of our forces were contending with every fiber in our existences to conquer the other for the cause we believed in. Eventually we did press through, but the fighting in that corridor in that state lasted for nearly an hour (I fully leveled up three times ) and I'll never forget that intense experience.
For a gorgeous moment: Luciana sitting and watching the Aurora from the watch tower of a stronghold. It was a very peaceful moment, like she had finally found a place to relax and breathe.
For an awe-inspiring moment, the Siege of Whiterun, as seen through the eyes of Balgruuf the Lesser.
1. fighting a dragon on the top of the tower south of whiterun.
2. Blackreach
3. walking up to Bleak falls barrow
4. getting swooped by a dragon on the top of the throat of the world at the break of dawn.
5. that wall that is covered in smoke north of riften (in that dryish area)
6. Krosis rising from his grave