In general it seems a long forced carry over a dramatic hazard (ocean, alligators, sand dunes, trees, rock, etc) to an aesthetically tantalizing target qualifies - call it "the hero shot." The period of time spent watching the ball drop back to earth while simultaneously recalculating its relationship to the hazard below is usually pretty memorable.
Bank shots of all sorts (up and over, over and back down, and lateral) are always fun and memorable as well, particularly if the ball disappears for a short while during its route - this adds an element of suspense akin to the hero shot.
Any time you're required (or given the strong encouragement) to use the ground game and visualize a combination of bumps, twists, and turns to reach an intended target, that's always the most thrilling for me. It also usually is a longer-lasting shot, adding more to the suspense as you continually compare what the ball is doing to what you envisioned.