I (and several of my friends) have ATV and LOVE it! It has a high level of geeky tech potential, but it is also extremely user friendly without any tech knowledge at all.
Out of the box you can buy anything from iTunes (including full seasons of TV shows at discounted prices), and search and watch iTunes videos. If you have another PC with iTunes loaded, you can stream all of your music and videos from your PC so in theory, you are only limited by the disk space on your computer (and money to purchase the content). I have over 1 TB of content available on demand on my TV at any time. I have loaded up tons of kids videos and music so the little ones are never bored.
On the slightly more technical side, there are free programs out there that let you convert your DVD collection to iTunes format so you can view them on the ATV.
Finally, one the super geeky side, you can "hack" the software on the ATV to do a ton of fun stuff... you can load up a full featured web browser, stream and duplicate full DVD functionality (including menus), and run the aforementioned Boxee software to stream content from Hulu and other video sites. Ths is not entirely difficult, but it's certainly not for the technically challenged.
It hooks up either through HDMI or component video and allows for either WiFi or wired ethernet to connect to the internet/other iTunes computers on your network.
There are other products other there that promise to do similar things, but the ATV is VERY user friendly and comes in at a fairly affordable price point. One other great thing is all the content will play on your iPod/iPhone if you have one!
I will keep an eye on this thread in case you have any other questions. I'm happy to answer.