How can you tell if the water pump is bad in a car?

The diaphragm breaks, and water leaks out the "weep" hole, assuming the belt is good, there is water in the system, and the thermostat isn't stuck closed. You usually get accompanying "bad bearing" noises, as the area around the pulley bearing overheats.