I just remembered my absolute favorite Deck story. This was a campaign I was running, and one of the players was playing a charismatic rogue named Claudia, who made friends everywhere she went.
During a solo session (the other players couldn't make it) Claudia was tasked with breaking into the prison holding Loki and releasing him. She owed a favor to another god (in this setting the gods frequently took human form, so the party had dealings with several of them), and he was a seasonal god of underworld and pasture who would annually be killed by Thor only to be revived when the seasons changed. This god was tired of being slaughtered, and figured that if Loki were free to cause his mischief, Thor might be otherwise occupied. Sadly for her, she ran afoul an elemental guardian within the prison and was smashed until she was a damp stain.
The next session, the party went to look for her and managed to identify her only due to her ruined equipment. They ultimately completed the mission, freeing Loki (which came back to haunt them in a later campaign) and Mina, an oathbreaker paladin joined the party. Despite that Mina was working with the good guys, she was more feared than loved. And wherever they went, NPCs would ask about Claudia and then would be saddened by news of her demise. They spent many an evening in various taverns toasting Claudia with the NPCs that they'd met.
They later defeated an insane beholder who was the bound guardian of the dungeon (technically it was a castle) that they wanted to delve. Since it was tied to the castle, the beholder couldn't be killed, but since it was defeated it would let them pass... plus it offered them draws from the Deck of Many Things. I only recall two of the draws, both by Mina. She drew Euryale, and then The Fates. I expected her to use The Fates to remove the curse of Euryale, but she surprised me.
She decided to use The Fates to avert Claudia's death. I admit, I was poleaxed. I talked to the player about it, and it turned out he wasn't looking to play Claudia again. Rather, Mina was so sick and tired of constantly hearing about how great Claudia was, that she wanted to bring her back so that she wouldn't have to constantly live in the shadow of a dead character. I called for a break because I really needed to give it some thought. Claudia had been dead for weeks, so retconning was out of the question - too much had happened. Then I had a eureka moment. I told the player that Mina senses that the card's power has taken effect, but that she doesn't see Claudia nor have any of her memories changed. She's certain the card worked though.
At that point we had started to build up towards a major villain in the story. A revenant named Hate, whose backstory was so filled with tragedy that his vendetta was with the world itself. When it finally came time to face off against this villain, guess who shows up to save the party's bacon? Claudia of course! The Fates had rewritten what happened in the prison. Rather than being killed by the elemental, she avoided it and freed Loki. Loki, grateful for his freedom, had foreseen that a great tragedy would befall Claudia unless she faked her own death (with Loki's help of course). With Claudia's help they succeeded in permanently destroying Hate, which wasn't originally the plan (since he was a revenant he was meant to be the epitome a recurring villain). Claudia had spent the time she was "dead" procuring the necessary materials to make sure Hate stayed dead.
Claudia, in turn became the mayor of the town the party was from in the next campaign, and she was one of my players favorite NPCs to interact with.
I'd say that one is my favorite use of the Deck because it was so unexpected, and it let me take the campaign in a direction that I otherwise wouldn't have. Which is, overall, what I love about the Deck. In a lot of ways, it embodies the golden moments of why I love this game so much.