Lots of cities in Alameda, San Mateo, and Santa Clara counties get their water from San Francisco Public Utilities, which owns large water rights both in the Bay Area and large watersheds in the Sierra Nevadas such as Hetch Hetchy.
SFPUC water is so clean it is not legally required to be filtered though it does go through ozone treatment to eliminate microbes. Much of the Hetch Hechy watershed, especially the upper Tuolumne, is bare granite so the water doesn't even pick up sediment or surface contaminates. SFPUC enforced removal of all lead water lines in the 1980s so there shouldn't be that risk (which was a real one given some buildings date from the 1800s in SF).
In short if you live in SF or one of the cities that buys their water from SF you shouldn't need reverse osmosis or any other filtering.
Often as these business shutdown they built offices and homes on top of the property, for the ones that happened in the 70s there was no remediation until much later. For the ones that happened in the 90s-00s there was a level of remediation but I am still not trustworthy of it 1) lasting and 2) the developers doing the best work.
So its not a "in short" story unless you are completely ignoring the large amounts of trichloroethylene contamination that is in still so much of the ground soil in the bay area. Sure if you live inside of SF proper you are probably fine in most of the residential areas as there was no manufacturing but areas like Bayview can be just as bad and you have the radioactive contamination on top of chemical. If you live anywhere in the south bay or east bay, you probably should at the very least get an idea of what was built near your office/home. Maybe test your water or just throw an RO on it. So many of these companies were dumping trichloroethylene and perchloroethylene in the south/east bay and we just built homes on top of them. They are indeed generally localized hot spots but when I realized how many were near me it opened my eyes. I remember years ago this was an issue at one of the google campus offices because it too had significant ground contamination.
https://www.epa.gov/superfund-redevelopment/superfund-sites-... - Even the homes built in later years have had problems with vapors contamination even though it was built with prevention in mind. You can see the other super fund sites there.
I cannot find the map now but there are even a larger number of DTSC cleanup projects from the bay area. Kind of surprising someone would ask where is the contamination when it literally is dotting the whole bay area.
There was a investigative story some time back, about somebody that had bought a residence on one of those superfund sites.
It wasn't after his (or her? I don't remember) family started getting sick, and after investigation that he discovered the housing project was built on a superfund site. And even after paying for air measurements out-of-pocket, and with results off-the-chart, the promoter and the town were all saying everything was fine, with nothing to see. And I believe they were also trying to sue him for libel, as you normally do...
Edit: What I would add is I often ponder how much additional nanoplastics are getting added compared to what is being removed. I know some of the test suggest RO is adding more but I am not sure if it accounts for the complete life cycle in a bottling plant. For the near term I have just settled that nanoplastics are the lesser evil to me than PFAS and other chemicals within the water. It is scare mongering but I look at how that town in Oregon I believe had has wide spread PFAS contamination in ground water from the airport fire foam.