A frequent cause of musty, earthy odors, especially toward the end of the summer, is naturally occurring organic compounds derived from the decay of plant material in lakes and reservoirs.
The USGS provides access to water-resources data collected at approximately 1.5 million sites in all 50 States, the District of Columbia, and Puerto Rico. Online access to this data is organized around the categories listed to the left.
Information is available through the Mercury Research in the USGS Web page. 
The best way to learn about your local drinking water quality is to read the annual drinking water quality report/consumer confidence report that water suppliers now send out by July 1 of each year.
The USGS Web site Water Resources of the United States can direct you to information about your local water body.
A good source of information on home drinking-water treatment technology and performance of specific products is NSF International, which offers a listings database for products on their
Water leaving our homes generally goes either into a septic tank in the back yard where it evaporates or seeps back into the ground, or is sent to a sewage-treatment plant through a sewer system.
This seems like a pretty straightforward question, but there are some interesting issues that come up in making a response.