I have seen that kind of thing, but not in a private home.

Just in case you'd be interested, here's a couple of sites that offer information about how they can be built:

www.myidealgarden.com/w/garden-waterfall.html

watergarden.com/.../Waterfall-Kits/building-disappearing-waterfall.htm