Assuming the comprehensive exam is well-constructed, it should be the most valid data there is, and therefore the easiest to justify weighting highly. As many ways as students have to game the system, I trust a good exam much more than I do homework to tell me what a student really knows.
When students do well on earlier tests or quizzes and then crash and burn on the final, that tells me one of two things. One possibility is that students were using short-term methods such as cramming to achieve the temporary illusion of mastery. The other is that they have previously seen only one unit's worth of material at a time, so they've never learned to select the right concept from an entire semester's material. Either way, their success on earlier tests does not indicate that they truly understand the material at the end of the course. Therefore, I see little to entitle them to a good grade.
That being said, I also believe that some traditional course structures inadvertently enable these weaknesses. That means that before we tell students that only what they know at the end truly matters, we need to do a better job of preparing them for that final comprehensive exam.
For what it's worth, the way I have chosen to address this is to make all of my tests contain a lot of material from earlier units. That means that students don't forget as much since they keep using old concepts, and they spend the entire course learning to select the right ideas from more than just the most recently learned concepts. It also means that the final is really just a slightly larger version of what they've been doing all along. That makes it much less likely that conscientious students will do poorly on the final just because they didn't know what to expect.
A few years ago, I abandoned the traditional approach of testing one unit at a time (with a comprehensive final) in favor of the approach I just described, and I've been very happy with the results. The final exam scores now match students' previous test scores pretty closely. Under the previous system, all but the best students' exam scores tended to lag the unit test scores by at least a letter grade. (If it matters to you, I teach high school calculus and pre-calculus.)