I'll take a shot at this... but I haven't had any experience with NVidia's new Cg stuff.
With the more recent GPUs, and all of the future GPUs it seems, there is the ability to configure the graphics pipeline through software to produce different effects in hardware, as opposed to software. To do this, developers write code to run on the GPU. My experience with this is a little limited, as I've only done a little work with custom pixel shaders, but here's what I know... The GPU understands certain commands. It is possible write your shader routine in assembly and then send it to the GPU to be used later. To do this, your game code basically says "Hey, Shader, here's the code for this thing... use it". This can be done with DirectX calls... actually, it can be done with one function call IIRC.
The Cg language, on the other hand, is supposed to give the developers a more powerful method of writing the routines to be run on the GPU. Instead of hand-coded assembly/machine language, now it can be a compiled version of a higher level language, similar to C. The code for the GPU is still sent to the GPU in the same way.
So, the compiler for the game code doesn't do anything with the code for the GPU. It doesn't even realize it IS code. It just looks like a regular chunk of data. As for the compiler for Cg needing to change for each card... yes, it might, and NVidia has mentioned this, although the shaders are supposed to recognize the same languages, in theory, I believe. Most likely the compiler for Cg will change based on the maker of the card, and not each individual card, however.
Hopefully that helped shed some light on things a little. I may not have a ton of experience to answer the questions with, but I told you what I know, and it didn't seem like anyone else was really forthcoming with the info.
