Yes, the scene tries to make it clear to the audience that Finn was going to fail. Both Poe and Rose say it’s not going to work. As Finn is making his final approach, he says, “weapons hot”, implying he needs to shoot inside of it in order to possibly destroy it. As he gets close, though, the cannons on his skimmer bend and melt off. Finn gives it a look of acceptance, seems like he has realized he is going to die.
So even though it might seem weird that the door blows up while Rose and Finn kiss, that was inevitable regardless if Finn died or not. But at least Finn didn’t throw his life away, and there’s still a chance he might make it out of this alive.
I’m not disagreeing though that maybe they could have done one extra thing to make that 100% clear, because there are still people who say, “Well maybe Finn crashing inside of it could have been enough to stop it”. The movie implies it won’t work by focusing on the skimmer’s guns. I’ve suggested this over on the fan edit threads, but if an AT-AT locked onto Finn near the last moment, and Rose knocked him out of the way just in time of an oncoming laser blast, I think it would have made it definitively clear for people.
Although this also overlooks the fact that there must be a smaller entryway through the trenches as well, since Poe and the other pilots apparently got back in without opening the door, and the same goes for all of the soldiers who were in the trenches when the First Order showed up. So the First Order getting into the base was inevitable, but defending potentially one small door could buy them more time for rescue than having to defending a massive hole in the wall.
Now, what makes Finn’s attempted sacrifice different from Paige or Holdo’s. The movie sets these sacrifices apart in two ways.
First, Paige and Holdo’s deaths are painted as inevitable. Both Paige and Holdo were going to die regardless of what they did. Paige was on near-destroyed bomber, and Holdo was on a ship running on fumes and “tied to the end of a string”. Finn, on the other hand, could have turned around and tried to survive. It wasn’t his only option.
Another way these sacrifices were differentiated was by mindset. One of the last things we see Paige do before she dies is clutch onto her necklace, implying that saving her sister is what is driving her to do this. She is driven by love.
Before the transports depart the Raddus, Leia and Holdo have a very personal goodbye and hold onto each other’s hands tightly. Holdo loves Leia and the Resistance.
Finn on at least two occasions shows that he isn’t necessarily driven by love or compassion. After the Fathier chase, Finn states, “It was worth it. To tear up that town. Make them hurt.” Which Rose replies by removing the saddle off of the Fathier and send it running off to freedom, then saying, “Now it’s worth it.” This illustrates how Finn and Rose are motivated by two different mindsets. Motivated by hate versus love.
During the Battle of Crait, when Poe orders Finn to retreat with Rose agreeing, Finn retorts with, “No! I won’t let them win!” Finn at this point seems driven by hate for the First Order, and it sort of made him oblivious to his death being unecessary. It also kind of culminates Rose’s story as well. She wasn’t able to save Paige, someone she loved, but she could save Finn, someone she has grown to care for. She doesn’t want Finn to die for something that’s not guaranteed to work, and she doesn’t want him to die for revenge.
The movie is trying to demonstrate a message about how the motivating force that drives our actions in everyday life can have a huge effect on ourselves. Acting out of love and compassion rather than hate and anger can make us more well-rounded people. Sort of like how hate and revenge can just be a viscious cycle, whereas love and forgiveness are better paths to healing.
But within the film, I also personally believe this lesson will have implications into Finn’s storyline in IX, but I won’t go into that here.
There are probably essays that describe this better than I have. Just my two cents.