What size/ type of output monitor really demands HD? In other words, when does DV image quality visibly deteriorate?
 That's an interesting question. my 4th short film was shot on my(SD) Flash media camera which is a little bigger than my hand. I then ran it through After Effects(stablization and Color Correction) and Vegas(actual editing). Then burned to DVD. Then I entered it in a local film festival.
When I showed up that night in front of the big screen, it never occurred to me that my image might not be great. It was, oddly enough! I don't remember any problems in video quality from watching my film on a big screen.  The judges even said I had good image quality. Still didn't win anything(long story), but the image quality was great from a camera no bigger than my hand after going through two software and being burned to DVD. And this was all SD!Â
Maybe I am being too cautious doing all in HD?
Maybe. LOL!!!
I imagine there are articles on this forum on optimising final rendering of film for different mediums such as web/ tv/ larger screens etc?
Not that I know of right this minute, but go ahead and check around. You will find some useful and some interesting stuff.
Here's some quick tips: for DVD, render to mpeg2, for the web, render to whatever you need, mpeg 2 or wmv or avi or quicktime or whatever. You'll use different formats depending on how compressed you need it. Avi is not all that compressed, but mpeg 2 can get really compressed.
In answer to your top question, I don't think HD is ever needed! SD is easier, faster and for what I've used it for, as good as HD.