I have been building boards for a long time and can tell you that the best approach here is usually to use solder wick and a soldering iron to suck up the excess solder. Then if needed, retouch the component by heating it up with a soldering iron and gently move it. It will self align and will look great.
Also, just taking the soldering iron and moving it against the solder towards you does it as well.
I have also found that using a hot air gun, heat up the place where there's excess solder paste and using tweezers just move them between the pins. Because solder paste has metallic balls, it tends to ball up and you can pick it up.
You certainly can hand-solder down to "0402" parts with strand solder and a suitably narrow chisel bit: apply flux, place part, get a pad-sized bead of solder on the iron, apply to one end of part while holding part with tweezers, dab more solder on iron, do the other end.
A head-mounted magnifier will help you see what you're doing at that scale.
The next stage of neatness is to hand-apply solder paste to the pads before putting the parts on. The paste is slightly sticky and includes flux, so you get better joints and less risk of the part pinging off never to be seen again.
If your run is more than a few, get a solder paste mask made which turns applying paste into a single action: align a panel of PCBs, wipe paste across, remove.
Once you're using paste you can consider reflow. Reflowing both sides (one after the other, not both at once!) can be fiddly as you need to make sure the components on the bottom don't fall off.
The next stage is to go pick-and-place; there are people doing cottage-industry or homebrew pick-and-place, sometimes with secondhand equipment. The actual soldering is still pastemask and reflow oven.
The final stage is to buy the fully automated line as a single unit: boards go in one end, component reels in the side, finished boards come out the end with automatic inspection. Worth it if you can keep it busy.
I've also seen partial wave soldering used to add TH parts to SMT boards. This involved extra production jigs to hold the TH parts in place and shield areas of the board from wave soldering.
Best Answer
If you choose rectangular, the stencil will come back with exactly the geometry you've specified in your Gerber files. If you specify "home plate" the manufacturer will shrink the pads by 10% or thereabouts, but make the pads 5 sided, which improves soldering success rates in automated board stuffing. I've not heard of trapezoidal pads before, but I assume it's a similar tweak to improve solderability.
For hand soldering surface-mount parts, you can safely stick with rectangular. The biggest improvement you can make is to get access to a stereo microscope for soldering. It makes a huge difference. You'll probably find that the limiting factor on your soldering ability is not your hand-eye coordination, but your vision. Obviously, a microscope improves your vision substantially.
Update: I just ran into this today-- "trapezoidal" does not refer to the the footprint of the stencil hole. It refers to adding a slope to the cut, so if you were to look at the stencil from the side, you would see a trapezoid. "Laser cutting apertures with trapezoidal walls and also rounding corners will offer better paste release," according to a Texas Instruments datasheet.