I like this idea a lot; it could give players a reason to build a sewer system (which is what a lot of the feces advocates really want) without the thematic downsides.
There would be three types of water; from good to bad, clean, tainted (unsafe for drinking or hospital use but not obviously dirty), and filthy. Wells and water source zones could have a "keep clean" flag, defaulting to yes for wells and no for other sources. Tasks for drinking water, bringing water to wounded, and cleaning wounds would prefer water from a "keep clean" source, while tasks for cleaning oneself and cleaning the fort would prefer a source not set to "keep clean", but any of these tasks would use a non-preferred source if no preferred source was available. Filthy water would be generated as a waste product of cleaning tasks, and clean water that comes into contact with a contaminant (blood, vomit, etc.) would become filthy. Introducing filthy water into clean should result in tainted water, and the taint would propagate through water, I'm not sure how. Pit/pond zones set to pond would have a few options; "any" and "clean only" seem obvious, and "not filthy" might be good enough for farming. Garbage zones would have a flag to permit or forbid dumping of water, with the default setting depending on whether the zone was adjacent to an open space.
A dwarf cleaning the fort would begin with a bucket full of clean or tainted water, and after cleaning a certain number of tiles (seven?) or running out of tiles to clean, the task would end and the water in the bucket would become filthy, generating a "Dump water" task. The cleaning dwarf would immediately take this task and dump the water in a pond accepting filthy water, a garbage zone set to allow water if there is no appropriate pond, or as a last resort, into a water source not set to "keep clean". I don't know if it's even possible currently for a bucket to hold water if it's not in a well or being held, but it would be nice if a bucket of water could be left sitting on the ground in case there is no suitable place to dump filthy water and no water source not set to "keep clean". As a bonus, if a dwarf was cleaning or hauling a water bucket and was interrupted by a non-emergency event, he could set the bucket down rather than dropping it.
Dwarves that clean themselves can also use a source that's either clean or tainted, and the task will have a fair chance (say 1 in 3) of generating 1/7 of filthy water. If there is a garbage zone that accepts water in very close proximity (3 tiles?), waste water from cleaning self would be dumped there instantly without generating a task or needing a bucket. In cases where such an arrangement is not feasible, the player could build a basin (a new furniture item that holds water and can be made of stone, wood, glass, or metal) by the water source to hold waste water. As the basin fills, it generates "Dump water from basin" tasks that require use of a bucket to complete. If there is no garbage zone and no basin with available capacity, the water just goes back in the source, tainting it and perhaps even making it filthy (thus nearly useless) if done often enough with a stagnant source.