The biggest difference is that an improperly soldered leaded joint looks very obviously wrong, whereas a good lead-free joint can be pretty much indistinguishable from a bad one.
If you are just starting out - and will therefore by definition have a poor iron, cheap solder, and poor technique - leaded is definitely the way to go. Once your first spool of leaded runs out, it is probably time to switch to lead-free.
The "shiny" aspect is the only way an improperly soldered joint can look the same on lead-free, and as a lead dev on an open-source hardware project that attracts a lot of people new to soldering, I have never ever seen a non-shiny leaded solder joint that wasn't horrendously bad in many other ways.
When a joint is bad, you get obviously poor wetting and weird mushroom shapes, but even so a newbie will not really notice that even using leaded solder.
If you are starting out, and you have a poor iron, cheap solder, and poor technique, you have already made two grave mistakes. We always urge people starting out to shell out in the $50 range for something that won't actively make them suffer, and they do just fine with SAC305.
I often mix up the shininess of leaded solder with the shininess of tacly flux under a ring-lit microscope. Can be hard for me to know if the joint is complete or partly made of goo.
The cloudy diffuse look of lead-free SAC305 is more distinctive to my eye.
I learned from the start on lead-free RoHS solder (doing SMT work) and had zero issues with it. Honestly I've never tried leaded solder as I just use lead-free all the time, though I know people who swear by it.
Under good conditions lead-free solder works fine, but the difference in melting point really starts to hurt when you're working on large belly-pad ICs. The intended rework procedure for that kind of part involves several minutes of hot air from above and below, which is more patience than I have when I'm prototyping.
When I take production (lead-free) hardware off the line for dev work, the first step to removing a large part is to flood it with leaded solder to reduce the melting point.
Has there ever been an occupational study of lead levels among hardware engineers? I'd be interested to see it.
I really love my Hakko FR-830 hot air preheater for lead-free rework.
Took some effort with rollers to be able to pan/rotate the board while it's on the heater but does make lead-free feel like leaded for me (on dense but relatively small boards anyway.)
Most difficulties people have in using lead-free comes from one of three things:
1. a poor soldering iron
2. bad quality solder (the cheap stuff with bad flux is bad, duh)
3. poor technique (among other things, wipe your tip just before using, not before putting it away)
I like the Chipquik SAC305 with no-clean flux and other people I've recommended it to find it no harder to work with than Sn63Pb37.