But it's best to remember that villages and towns of the medieval period were not densely populated, and the resulting likelihood of contamination of even a closely located source water was low - it was more likely to happen from animal herds if at all, although animal herds weren't of the same size as today either.
The sources of potable water were generally wells, sometimes artesian, sometimes spring fed, and often in the center of the village as a common resource. These wells were more likely to run dry than to be infected with human feces. Large villages, cities and some castles would have cisterns to collect water, either through surviving aquaducts, from springs, or from rainwater. Again, the castle I am at has an underground medieval cistern, about 5 cubic meters large.
Beer/ale was not about water contamination issues - most ale was locally made and not subject to the rigors of testing before consumption; it could be a bad replacement for water. Beer/ale was a carbohydrate replacement, but often stronger in alcohol than our modern brews. Regardless, alcohol kills germs only at a certain percentage, and only of a certain exposure duration. Wine and beer don't do it, even at slightly higher formulations, but vodka or whiskey might. However, once mixed in with water, things like E. Coli still persist. Water and wine were mixed to cut the potency of wine, sometimes with wine being akin to a flavouring agent.
Urban Tigner Holmes' Daily Living in the Twelfth Century is good on details of eating, drinking, and hygiene, most taken from the journals of Alexander Neckham. It gives a deep flavour to daily life.
For those who want to get into medieval archaeology and the scientific findings, Food in Medieval England: Diet and Nutrition from Oxford (2006) will be of interest.