"What actually causes multiple car pile-ups?"
Basically, it almost ALWAYS boils down to one, single moron. You pretty much answered the question yourself, in that in cases of near zero visibility, a car ahead slows dramatically. It is almost always the case that when a low visibility condition occurs suddenly, one single nitwit will slow dramatically, causing the vehicle behind them to rear-end them. Then, as another suggested, it is a domino effect.
People panic or freak out when they come up on smoke or fog or heavy snow and instead of doing the right thing and immediately changing lanes to the right (in left hand drive/right side driving countries, anyway) and getting onto the shoulder, they slow down dramatically in the travel lane.
I've been driving professionally for well over 25 years, and though I have never been involved in such a large, multi vehicle pileup, I have had to sit in traffic behind many of them while they got cleaned up. It can, as I said above, almost always boil down to one single nitwit at the front who freaked out and stood on the brakes.
Comments about following distance don't always apply in many of these types of accidents because what was a perfectly safe following distance at one point can be entirely too close a mere 100 yards later.