A pilot (lead) hole is drilled before a screw goes in, and the sizing logic is about what the wood can do with the displaced material. Softwood compresses out of the thread’s way, so it needs only a small pilot; dense hardwood cannot compress, so it splits instead unless more material is removed first. The FPL Wood Handbook rule behind the published charts is a lead hole near 70% of the thread root diameter in softwood and 90% in hardwood, which is why the consensus chart sizes step with density — a #8 wood screw takes about a 7/64 in pilot in softwood but 1/8 in in hardwood, a #10 takes 1/8 in and 9/64 in respectively.
When one is needed: whenever the wood can split or the screw can snap — dense hardwoods, the ends of boards, and any gauge from about #8 up in hard stock. Modern structural screws are the exception, with self-drilling tips qualified in their evaluation reports. At the structural end of the scale, pilot holes become mandatory rather than advisory: deck-ledger lag screws must be installed into pilot holes — 1/2 in through the ledger and 5/16 in into the band joist — with the tip extending fully past the inside face of the band joist, and never driven with a hammer.