Go into your inventory screen and look under liquids. Tab to change modes. What kind of water do you have? Do you have a well? What kind of water is listed as being in the well? Just plain water, or stagnant water? What about your buckets? I've done some experiments, and I'm not entirely sure what is going on yet, but I have a hypothesis.
In my current main fort, I have a reservoir connected to a stream. The water from the stream has to go through a carved fortification and a wall grate (at one point, I thought grates cleaned water) to get into the reservoir. From the reservoir, I have a floodgate leading to a tunnel leading down to a reservoir in my main meeting hall where the well is. This water has been through a grate, and a well, and it is still listed as stagnant, containing a "grime coating." I built a test fort with water pumped from a stream, and that water was clean.
Some people say that wells clean water. I really don't think that is currently the case. I think that only pumps clean water. Some people think only murky pools produce stagnant water. I don't think this is the case, either. I think the grime coating comes from any natural watercourse or murky pool floor, and acts like salt from a salt water biome. Underground water, coming off of a rock floor, probably won't have the grime coating and will start out clean. Cleaned water pumped back into a drained murky pool (or a dry river bed) would probably pick up more grime. This requires more testing...