Why is this explosion so important that it warrants a change of music?
The strategy for the attack on The Death Star in this battle was that the fighters would break into groups of three. The lead fighter would perform the trench run and attempt to score a direct hit on the thermal exhaust port with proton torpedoes to cause a chain reaction and destroy The Death Star. The other two fighters in the group would fly behind the lead fighter and attempt to draw the fire of the turbolasers and TIE fighters away from the lead fighter. Other fighters not currently in a trench run would attempt to destroy turbolaser batteries and TIE fighters to make the trench runs easier.
As the battle progressed, groups of three fighters would enter trench and most of the time, not all three would make it out, or the whole group would be destroyed. Darth Vader and his escorts were quite effective at taking out rebel fighters in the trenches - so much so that leading up to the moment in question no trench run actually made it to the point of even launching torpedoes. Also, fighters not currently in the trenches were being destroyed by turbolasers and TIE fighters.
Right before Red Leader (the pilot who screamed before his X-Wing crashed as referenced in the question) died, he completed the second to last trench run of the entire battle, and the first run that actually made it to the end of the trench where Red Leader launched torpedoes. Unfortunately, Red Leader's torpedoes did not score a direct hit:
RADIO VOICE: Did it hit?
RED LEADER: Negative. Negative. It didn't go in. Just impacted on the surface.
So the run was a failure, Red Leader was the only member of the three-fighter group who survived the run, and immediately after that Darth Vader scored a hit on Red Leader's starboard engine, which is what led to his screaming crash into the surface of the Death Star.
It is significant because the only rebel fighters left in the battle right before this death are Red Leader, Luke, Wedge, Biggs, and the mysterious Y-wing pilot. With Red Leader dead:
- The rebels have only one more chance to try to destroy the Death Star.
- Luke, in his first space battle of his life, will lead the final trench run in an all-or-nothing attempt to save the entire rebel alliance.
It's Luke, his childhood friend, and a new friend versus the entire Empire alone in the trench with Vader and two TIE fighters on their backs.
Here's a script excerpt that makes it a little clearer than watching the movie:
LUKE: (over headset) Red Leader, we're right above you. Turn to point...
INTERIOR: LUKE'S X-WING FIGHTER -- COCKPIT.
Luke tries to spot Red Leader. He looks down at the Death Star
surface.
LUKE: ...oh-five; we'll cover for you.
RED LEADER: (over headset) Stay there...
INTERIOR: RED LEADER'S COCKPIT.
A wary Red Leader looks about nervously.
RED LEADER: ...I just lost my starboard engine.
INTERIOR: LUKE'S X-WING FIGHTER -- COCKPIT.
Luke looks excitedly toward Red Leader's X-wing.
RED LEADER: (over headset) Get set to make your attack run.
INTERIOR: DARTH VADER'S COCKPIT.
Vader's gloved hands make contact with the control sticks, and he
presses their firing buttons.
INTERIOR: RED LEADER'S COCKPIT.
Red Leader fights to gain control of his ship.
EXTERIOR: SPACE AROUND THE DEATH STAR.
Laserbolts are flung from Vader's TIE fighter, connecting with Red
Leader's Rebel X-wing fighter. Red Leader buys it, creating a
tremendous explosion far below. He screams and is destroyed.
INTERIOR: LUKE'S X-WING FIGHTER -- COCKPIT.
Luke looks out the window of his X-wing at the explosion far below.
For the first time, he feels the helplessness of his situation. [This is where the musical moment happens]
The actual scene in the movie isn't exactly like the script or the novelization, and it's not clear exactly what is going on when Red Leader dies. One interpretation is that his starboard engine is out and he doesn't have enough engine power to avoid crashing into the Death Star, but his ship is otherwise intact. So he could have been deliberately diving into a turbolaser battery and issuing a kind of battle cry as he did so. Or it could have been a cry of fear and rage as he helplessly careered into the space station.