I seem well placed to answer your question, having a Q6600 overclocked to 3.3ghz and recently upgraded 560gtx1gb to a, yes you guessed it Nvidia 970
First ignore people who are quick to say yes or "No WAY!!" straight away off the bat, the answer is more complex and actually yes and no.
There are and will be games that are more cpu dependant, there are however a surprising amount of new games (RYSE, Shadow of mordor) that don't need a beefy CPU and the GPU does all the work. A lot of the new console ports of course should be an example of this.
see my benchmark data below of my Q6600 3.3ghz with 560 and with new 970:
Shadow of mordor, high settings 1920 res on 5601gb, 11fps. cpu usage 30%
shadow of mordor, high settigns 1920 res on 9704gb, 87fps. cpu usage 80%
Batman, everything pretty much on MAX 1920, 5601gb 38fps. cpu usage 75%
Batman, everything pretty much on MAX 1920, 5601gb 47fps. cpu usage 95%
mordor average max min
very high 970 87 300 44
very high 560 11 64 4
Just in those two exampels above you can see the massive change you will see game to game, shadow of mordor i am not evening maxxing my q6600 with a 970, however in Batman i am not pushing the 970 at all. General rule is older games are more cpu dependant where as newer titles are more GPU limtied.
Ryse son of rome is interesting, playing the game i have exactly 95%+ CPU and GPU usage, the q6600 is fast enough to max out the 970gtx, i think a lot of people would be surprised by that.
Remember these newer game engines are targeting mutliple lower clocked 1.6ghz cores, the q6600 is much faster then current gen ps4/xbox1 console CPUs.
What i would advize you of is where as the 970 needs only a 500w psu, it needs 42a (off top of head) on the 12rail, i had a 500w earthwatts PSU for my Q6600 ip35 pro and needed to purchase a new PSU to feed the 970.
seems like broadwell will not be out until 2015, i advize you todo what i did buy the 970, run some benchmarks, be impressed and if the games you play are maxxing your CPU, then upgrade to z97, i5 4790k. let me know if you need any more benchmarks run.