Memorable Moments

Every game, and every level needs memorable moment.
They are small nuggets of game goodness and I'm sure those old enough still remember the fight against Psycho Mantis in MGS the ascension of mount Hrothgar in Skyrim, or the first time you went "behind the scene" of Aperture laboratories in Portal.

As always, you should first ask yourself : what's the meaning of it? Does it support the game vision?
Memorable moment, should be considered as a tool to deliver the game experience (narrative or gameplay) and for maximum impact, it's always better if it comes in contrast with the previous game area.
i.e. You are winning the war...when the place is shattered by a nuclear explosion // You were crawling through dark tunnels...and you exit in a majestic valley // You were in total control...you loose contact with friendly NPC and you are forced to flee. etc.

POST CARD

One of the easiest MM (memorable moment) to set up is probably a post card, a place where the player will stop a few seconds to stare at a beautiful vista. (can be reinforced with a small cut scene)
I imagine the artists frown at reading the word "easiest" :) but I consider them "easy" in the sense that they usually only involve the Art dept and as long as you don't go crazy on tech constraints, it's not something you'll come back ten times on and you'll still be debugging a week before the gold master ;)
You should check with your art team (or work yourself to find some nice composition) but there are a few easy rules to remember to make it work

• Must be in a quiet place : you'll less likely enjoy the scenery while fighting for your life ^^ + no gameplay = more freedom for artists.
• It's a big reveal : not a vista that slowly reveals itself.
  Typically, you'll want it where : a door opens, you turn a corner, you reach the top of a climbing, etc.
• Send the player in the post card : you've presented a "big tasty cake" and chances are good that the player will want to touch it, visit it.
  The effect will always be stronger if later in game you can reach this beautiful vista you were shown earlier, even if it's just about playing in small chunk of it :)

MEMORABLE GAMEPLAY MOMENT

• Boss Fights First thing that comes in mind when talking about gameplay MM are boss fights.
  They are the best example of something memorable you'll remember and talk with your friends.
  But boss fights represent A LOT of work and they should not be treated lightly.
• Game ModifiersThis being said, you can create memorable gameplay moments with much cheaper ideas.
  Usually, the best is to twist normal setups by adding a momentary special rule (game modifier)
  i.e. You are in the middle of a fight, and when suddenly the lights go OFF. Now you can only count on your flashlight to orient yourself, and check the muzzle flashes of your enemies to spot them. This unexpected "dark level" could be a memorable experience for your player.
• Introduction of New NPC or new Player tools
  When you want to bring in a new NPC, it's always a good idea to give it a special treatment, highlighting its strength.
  Note : New enemies can often (should ?) be presented as Mini Boss the first time they appear, especially if the player doesn't have the proper tool to defeat it yet :)
  It can create a MM, and it will make the player feel even better when he finally masters the way to defeat them.

SPECIAL SETUPS

It's usually about going extreme, and at the opposite of what the "regular" action is in a section.
Note : be especially careful with that and as always, make sure it highlights and serves the game experience, as there's nothing worse than being forced to do the opposite of what the game you bought is supposed to deliver. (i.e. not sure anyone would like a stealth section on Serious Sam critical path, or some frantic shooting range gameplay in Thief ^^)

• Think of extreme layouts : you were racing at 300kph for the 1st position on a highway...you are temporarily forced to cross a small village with narrow twisty roads and chickens on the streets. (think Contrasts!)
• Think of interactive objects : you are used to fire your machine gun at hordes of enemies...when you enter a building full of highly explosive canisters. (again, think contrasts)
  You were used to take cover at walls corners, but you find yourself in an open space where walls are made of glass...
• Think of dynamic Layouts : you were running in ancient underground ruins to escape danger when a big explosion (an earthquake, or a powerful enemy, etc) shakes the area, tearing the walls and floors down, leaving you only with a narrow path and a few small islands of floors to move forward.
  A note about the previous example : it's must easier/cheaper to just "flip" the level in one big event (use a cut scene to make it more powerful) compared to building a full scripted escape scenes with triggered collapsing and dynamic path changes every 2 steps. (tip : keep the triggered scripted elements for visual event not affecting gameplay ;) )

NARRATIVE

Story twists and introduction of new characters are obviously memorable moments and should be treated accordingly.

And don't forget that passive narrative (when the setup itself let the player imagine what happened here) can be as strong as a cut scene.

MERGE IT ALL !

Of course, you can merge some or all of those categories and try to build super powerful moments but this will raise the complexity and difficulty level exponentially.
In fact, those incredible memorable moments are very complicated to build and they usually end up being the work of specialized multi talented strike teams spending months just to build a few minutes of those exceptional moments. Know your limits ;)