I'm not sure if I understand how this cantrip works. I really want to use it for silly things, like having my fun-sized -1 strength rogue smack a table indignantly, sending sparks flying everywhere and leaving a black mark on the wood, or sneaking up to a guard and soiling their pants, but that doesn't really work, does it? My DM said that spellcasting breaks stealth, and that typically people can tell that I am casting something if I am casting something, so what would I even use this for, if I basically can't use it to actually trick people? It wouldn't even be possible to cheat at card games, since anyone could tell that I just did some magic. Am I missing something here?
It is just little tricks for entertainment/practice. So you could do the table smack, but it would be obvious you were casting a spell to do it, and the mark would fade after the spell wore off. It was never really meant to have serious utility. There are feats that can help remove component requirements, which could help with stealth, as it's usually the verbal/somatic bits that give you away. That being said, being able to douse/light candles and lamps is pretty handy. You can temporarily mark locations or routes for allies, or use the same to confuse or mislead someone, at least within the hour duration. There are probably tons of more creative uses out there if you do a little forum digging.
Our mage used it to create an image of a scale on a wall in an orc cave, which has distracted them, allowing another character to telepathically convince orcs' leader (an ogre) that he's a champion of Kelemvor and needs to kill those orcs
Prestidigitation is one of those spells that are more flavourful than impactful until the very moment is absolutely perfect and everyone is staring at their character sheets in a panic wondering what the hell they can do in a clutch situation.
Until then there are plenty of things you can do with it for fun and to encourage roleplay (depending on the style of table you're at): flavour someone who just upset you's food like something gross, snuff out the candle of your wizard copying spells in the middle of the night because you don't sleep well with a light on, sneak up behind someone and add to the fright when you say, "What are you doing?" by making an explosion of sparks.
Talk to your DM so that you both understand what each other are going for, but you will probably find that a lot of DMs are far more lenient about these things if they're used as flavour rather than for tactical advantages.
Use it in a crowd, It has a 10ft range, and I don't expect most people to know you are spell casting unless they are paying attention directly to you or are trained in magic in that situation. You are just energetic with hand movements whilst talking and the guard wet himself.
It's stage magic as a spell. It's not designed to be subtle, the opposite in fact (though this isn't to say that it can't be, it just requires extra resources). You misdirect observers with sleight of hand and captivating personal presence so that they don't quite catch on. It's not hard coded in the rules how to make that work so you'll have to work with your DM on that a bit, but it's probably an easier ask than to just not be noticed at all.
Great for making a guard think he shit his britches so he leaves his post with either the soiling or smell. Also for trolling your fellow adventurers in the same manner. At least that's what my group mainly used it for
I would allow you to trick people with parlor tricks, such as typical card tricks by real world "magicians." Though it might just be better to use a slight of hand check than casting Prestidigitation, depending on the actual action you're attempting.