As I wrote the other day, I'd like to help out people that are having trouble understanding the content pipeline, and I'm planning on writing some blog posts about it. I don't have a lot of time right now to dedicate to this, but I was digging around the other day trying to see what information was already out there, and I found several links that might be useful.
Michael Klucher wrote a high level overview of the content pipeline for our team blog back when we shipped v1. (memories....) Shawn also wrote several useful blog posts explaining various concepts about the content pipeline, including:
From the help pages, we've got a good overview and architecture page. One of the best pages is this How to, which is a walkthrough that adds a custom type to the pipeline and creates a custom importer and processor. It imports pixel shaders as an example, but don't be afraid to read this even if you're not interested in importing pixel shaders! There's a gold mine of info in there.
Ok, that's it. Hope this helps - hopefully in the next few weeks I'll find some time to sit down and give this topic a bit more detail.