Quick answer: A cat water fountain keeps water circulating, which encourages cats to drink more — significantly reducing the risk of kidney disease and urinary tract issues common in cats that don't drink enough.
Cats instinctively associate still water with potential contamination. Moving water signals freshness. Many cats that ignore their water bowl will drink readily from a fountain. This is especially important for cats on dry food diets, which provide very little moisture.
Material (stainless steel and ceramic are easier to keep clean than plastic, which harbors bacteria), filter type and replacement cost, noise level (some pumps are audible at night), and how easy the unit is to disassemble fully for weekly cleaning.
Fountains need weekly cleaning and filter changes every 2-4 weeks. A neglected fountain develops biofilm quickly and can cause more health issues than a simple bowl. Factor in ongoing filter costs when comparing models — they add $20-50 per year.
A fountain is a worthwhile investment for cats that drink too little, especially those eating primarily dry food. Stainless steel or ceramic is worth the extra cost for hygiene and longevity.