My favorite is my character from the playtest: a high Elf Rogue with the Knight background.
He takes advantage of the Hospitality feature (now Position of Privilege) to gain access to noble manors and balls, and uses that as an opportunity to case the joint. He then either robs the place then and there, or comes back later (after scouting ingress, targets, and egress).
The High Elf's cantrip fits his specialty: art theft. He slices paintings from canvases, then cuts the canvas into strips or even small squares. Much easier to transport or smuggle out that way (as stuffing for a doublet, for instance). An afternoon of Mending restores the artwork.
Another, recent favorite is a human Druid criminal. He was a burglar who escaped from jail and fled into the woods. Stumbled into a fairy circle and was compelled by the fairy queen to serve her, and cursed with a chaotic form (Circle of Moon). Mechanically, this works very well: the high Wisdom (plus the Observant feat) nets out at a 20-something passive Perception & Investigation at first level, which makes him a phenomenal trap-finder. Decent Dex (augmented by Guidance) combined with proficiency with Thieves Tools makes him a capable trap-disabler and lockpick, as well. And of course Observant is very useful when scouting as a bat or hawk, or keeping watch as a wolf.
I have a criminal cleric. After years as a lay priest in his original religion he found true calling in a foreign religion. Sadly the original religion was a LE religion who did not take kindly to those finding another faith, especially it's priest. He became a convicted apostate of the LE religion and into forced exile. His criminal contact is a priest in the LE church that was his friend for years. Lots of role playing fun. The paladin in the party really doesn't know what to do with him.
I made a Criminal protection fighting style Fighter. He was formerly a bodyguard and enforcer for a criminal organization until his partner was killed. Now he's trying to assuage his guilt by bodyguarding his dead partner's naive, lawabiding younger brother (played by another player).
Assassin Rogue with Sage background. While murdering people for money, he has also been ordered to kill rich nobles and some collectors that hoard old manuscripts. These loots he had found attracted his attention and within a short time he is interested in books, memoires, scrolls, parchments of old agreements, and cryptic writings, journals. Now while doing the dirty job, he collects such material to gain information about almost anything, and build his secret library, and sells information when he sees appropriate.
I'm running a PC with Sage background, who became a College of Lore bard.
If there's a song about an event, he wants to learn that song, and the background story too.
He hopes to get involved in events which will inspire songs!
Expertise in Perception and Stealth (former obvious, latter because sometimes Stealth gets you into position to observe/eavesdrop); knows Detect Magic, Speak with Animals, and Detect Thought; if he reaches 10th level, will use Magical Secrets to learn Legend Lore.
Combat and casting require CHA and DEX, so his INT and WIS are middling. Proficiencies make up for the middling ability checks. (Proficiencies in Arcana and History from Sage; Investigation and Insight, of course; Persuasion, to get people to "open up"; Nature, for natural lore, Religion similarly.)
Hm, thinking as I write this, perhaps he should eventually multi-class into Cleric with Knowledge domain!
Moral of the story: the Background feature is indeed as great as we all imagined it to be when we first saw it...
I've personally been kicking around a Folk Hero Lore Bard Aasimar who's basically a street preacher with an expertise in Religion – he focuses on cleric-style 'miraculous cure' spells – who disdains the organized faiths that have "fallen into shadow while taking the good peoples' money," generally combatting opponents via Vicious Mockerying them with preachifying about their sins and the "need to return to the Light." Of course, his celestial father just might have actually been a fallen planetar...