I think tweaking can be a good thing, as long as its done right. But it can work both ways. Can a course be 'tweaked' and go from poor to great? I hesitate to say no, because nearly anything is possible, but very likely not. In most cases, poor courses are poor because of a very poor routing or poor terrain, not simple things like green contours and bunkering. However, I do think its possible to take a poor course and make it average or even good. When trying to tweak a great course, or even a very good course, however, it is more likely that tweaking will result in something that is not as good as the original, depending on what motives are used to justify the work.
--How would I define tweaking? Something like a moderate change to greenside and fairway bunkering and perhaps a minor change to green contours. I suppose a new turf in the fairways and new tee boxes could be tweaking as well. Anything that goes much beyond that and into re-routing holes, changing fairway contours, extensive lengthening or any number of other things would get more into remodeling and renovation, or even completely redesigning.