The topic is misleading. "Light rail" != "Transit".
Light rail sucks, no argue about that.
Buses are pretty good. Caltrain is decent. But in such a low density area it's hard to expect something good out of public transportation.
It seems to me to be a cop-out to suggest you can't have good public transport in a low density area. I come from the city of Perth in Australia which is very sparse and it has managed to create a very good intermodal system with light-rail corridors connected by an extensive bus system that feeds to the rail system. You can get pretty much from anywhere in the city to anywhere else with a bus service within half-a-mile, often involving trips of 50 miles or more.
Hrmm, I won't disagree since I've never been there, but the Street View images make it appear to be a quite urban, walkable type of place with narrow streets and wide sidewalks and lots of shops and food, with multistory buildings and all that. e.g. http://goo.gl/maps/HNcTA I see that it does appear to sprawl out a bit away from downtown, perhaps that's what you're talking about.
But Perth appears to have at least one district worth visiting. The thing you have to understand about SV is most of the "cities" there have exactly zero districts worth visiting. Mountain View has a little bit of a business district. You can walk across it in about 90 seconds. Palo Alto has University Ave. But that's about the extent of things. Here's the center of Cupertino, which appears to be a kilometer-wide intersection of two massive, pointless roads: http://goo.gl/maps/EYLq9. Here's the middle of Sunnyvale: http://goo.gl/maps/51LLL. Beautiful downtown Santa Clara: http://goo.gl/maps/q8WpU
These places are not places, they are irredeemable ruins of the 20th century. No transit system will ever work there.
At least use downtown Sunnyvale (the half-finished area surrounding the Caltrain station), not some random place in the center. Sure, the renovation went bust with the housing crunch, but they're trying to restart it and it would be (already is somewhat) a good transit hub.
[A real question; I've no idea about Palo Alto or Mountain View, but have vague memories of hearing they're "desirable" (and expensive) locations in SV...]
Many people prefer a sprawling car-dependent lifestyle. Others are compelled to live near their jobs, and there's lots of jobs in the suburban office parks of SV.
The density isn't the problem in the way you're thinking. There is definitely enough people per square mile in the Valley to at least support good commuter rail (I don't think it's any less dense than the suburban areas around Chicago or NYC). The problem is density around the train lines. In Westchester County, NY, every Metro North is the hub of a small urbanized area. I live 20+ miles from Manhattan, but in a 40-story high-rise from which I can walk to the train station in under 5 minutes. Have you seen what's around the Cal Train stations? Nothing!
Which came first; small urbanized areas that the metro north serviced, or small urbanized areas that sprang up because of the metro north stations?
If you introduce light rail without changing the zoning surrounding the stations, you end up with poorly utilized transportation that does not promote the growth of urbanized areas.
Keep in mind too that there's no huge, dense attractor anywhere in Northern California that could act as a hub to a bunch of disparate smaller communities.
I personally don't see any problem with Caltrain. It's relatively reliable and goes often enough. The only thing that's missing is free wi-fi, but I think they plan to put it in there eventually.
The problem is nobody uses it. Metro North serves a region with maybe 2-3 million people (depending on how many people in Connecticut you consider part of the service area), and has a ridership of 300,000 per day. Cal Train serves a region that's at least as large, but has 40,000 riders a day.
Light rail sucks, no argue about that. Buses are pretty good. Caltrain is decent. But in such a low density area it's hard to expect something good out of public transportation.