His journey isn't about him coming face to face to our expectations of what failure is. It's about him coming face to face with his own expectations of himself and what he's done and the consequences for him, very much like we saw him do before in Tapestry. And he did sell the Romulans on the Federation not only saving them all but for everything working out great for everybody, and then when that didn't happen, in disappearing. The rescue had his face on it, so the repercussions we see and Picard feels are real.
There was nothing noble or selfless about resigning from Starfleet and hiding in his Chateaux for 15 years after making himself the face of a massive rescue offer that got recinded. He accused Starfleet of slinking away, but in truth, that was what he did and in facing the effects, is becoming to realize how horrible it was a mistake for him to make.
The problem was his pride/ego would not let him take back his resignation, and it kept him from facing what he did for 15 years.
All of those Romulans WERE saved though. They all owe him their lives. Here they are, alive 14 years later. So his need to atone is for the sins of others? That's what a Moral Mary Sue is. It was noble and selfless to stake his career on saving more of these people. But Starfleet said no. Not him. He didnt ban synths, cause the synth attack, the supernova, or the cancellation of the rescue mission. All that was wrong was done by others. Not him.
So without Starfleet, he should have saved the 900 million anyway? How? And this premises "redemption" on not being even more selfless, noble and morally Mary Sue perfect than he already is.