You are probably referring to a 'D - a - m* Inundation Area.' This refers to areas that may have flooding due to the failure of a d - a - m, a levy, etc. usually because of an earthquake. You are not required to buy flood insurance when you are in these areas.
There is no easy way to explain how minor of an issue this is. There is always a chance that some water may run down your street after the reservoir breaks in a major earthquake. These areas are usually in '500-year' flood zones or zones of indeterminate flooding.
A 500-year flood zone is an area where it is expected that it will flood once in 500 years. The same definition holds true for '100-year' flood areas. Once in 100 years - for sure! In those zones you will likely be required to get flood insurance. It could happen this year, it could happen next year, it could happen in 2082. You never know . . .
Two ways to go: 1) Stay away from there; let other people buy and be swept away in the potential 2-3 foot tsunami from a reservoir 5-7 miles up the road right after the giant earthquake. 2) Take the big risk and worry about more important things like radon gas, lead paint, asbestos in the air, fluoridated water, mercury in the soil, the next 9.0 earthquake, leaking underground tanks, neighbors with kids in garage bands, etc.
Our Valley is one of the most wonderful places in the world to live. There are always some drawbacks. How did your agent explain it?
Coldwell Banker - Premier, Top 2% Worldwide
Cupertino - De Anza Branch
President - PRDS, Contracts and Forms for Silicon Valley Real Estate 2008, 2009
President - Silicon Valley Association of Realtors 2007
Realtor of the Year - Silicon Valley Association of Realtors 2006
Chair - Region 9, California Association of Realtors Board of Directors 2009
DRE # 00896552 licensed since 1985
PS Don't take my word for it on this. Peace of mind for you may mean buying $900 worth of flood insurance every year along with $1500 worth of earthquake insurance. While you're at it, get a $2-$3 million umbrella policy for liability (this is only $400-$500 a year). That ought to do it . . .
*I spelled it that way because Trulia thought I was using profanity.