I didn't respond initially, because I thought it was a given that excitement and thrill were integral to a great design. Also, there are so many elements to thrill that it's hard to encapsulate them (as illustrated by the various examples given so far).
I don't believe these moments need to involve forced carries or significant penalties (it helps), but I think the common theme is that they are outside of the "standard" shot (i.e. beyond the simple metric of a distance). For example, a 30 foot putt where you have to aim 40 degrees away from the hole can be a thrill. One similar example for me is a chip from just short of #9 at Ballyhack to a front pin. I love the excitement of chipping past the flag, watching it crest on the green slope, and then start tracking back towards the hole.
The exciting shots can vary from the subtle to the extreme, but it's the deviation from the standard challenge that gives you the thrill of execution.
On the extreme side, you have examples like going for Tobacco Road's 11th in 2. Watching your ball hang in the air is a thrill as you contemplate whether you'll have an eagle putt or a sand shot from a 25 foot chasm. I particularly like this hole, because even if I come up short, I know I have the opportunity to pull off an exciting recovery (getting a birdie from the chasm was just as exciting for me as the time I hit the green in two). You already know how I feel about Ballyhack's 6th.
Somewhere in the middle of the continuum is the excitement of blind shots. I particularly love these because the thrill is extended beyond the initial execution. When I face an elevated approach, you have the thrill of watching whether it crests the hill safely onto the surface or bounced back towards you. But then you also experience the building anticipation as you near the green, ready for the reveal. There doesn't need to be a severe penalty, but a 130 yard uphill approach (playing like 150) is superior to a standardized flat shot of 150 yards.
A good course can also provide the exciting option, for which the degree of excitement is in the player's hand. On Ballyhack's 4th, you can try the "standard" aerial approach, but you're also given the exciting opportunity to hit the lower trajectory shot and watch it take the slopes.
There's also the thrill of execution when there's a bonus for pulling off a certain shot shape. For example, the tee shot at Dormie's 4th significantly rewards a running hook. If I can execute that shape, there is a definite "bonus" thrill beyond the standard drive execution. On top of that, the tee shot is blind, so if you execute the drive, you get that additional excitement of seeing just how far it ran. Dormie's 4th doubles down on the excitement, because you can take the standard high approach to the middle of the green, or aim for the area 15 yards left of the green and watch it kick towards your target and slowly run across the green. Cory Lewis suggested that option to me, and to this day, pulling it off is one of the most exciting shots I've hit in my career.
I could go on with many examples, but there's no doubt thrill & excitement are essential.
I don't think you can have too much excitement and thrill on a golf course, as long as it is varied. Where I think it becomes a problem is when a course only resorts to the extreme versions of thrill (i.e. severe penalties or forced execution). As we've seen, there are so many other non-penalty driven ways to provide excitement along a continuum, whether it be pure aesthetics, fun execution options, visual intimidation, bold fairway/green contours or blindness.
But once you are able to get away from the repetition of extreme thrills (the stereotype of forced carries), why would you ever want more "standard" shots?
I suppose this entire thread just affirms for me why I'm an Elevation Whore. Topography simply allows for a greater variety and deviation from the "standard." The potential for excitement (in varied forms) is so much higher when you have some movement. This topic also confirms why Ballyhack is one of the best designs I've played. There is so much excitement and it is in varied forms throughout the course from the extremes (driving the green on 6, getting home in two on 10) to the moderate (uphill approaches on #1, #5, #14), and then at the small feature level (multiple chipping / putting options on holes like #5, #9, #14 etc). I could go through hole-by-hole and find some exciting deviation from the "standard" without feeling like those thrills were contrived or repetitive.