As I understand it, being able to use the SDK and Python allows unprecedent modding opportunities for us. My question though, is the following: what are the limits of modding in Civ 4? What is hardcoded that won't be able to be changed through the SDK/Python?

The graphics engine itself is hardcoded; so you can't mess with that (ie we are stuck with the gamebryo engine, and how it renders stuff, and the .nif/kfm model system). The starting game options are also hardcoded in the .exe and can't be messed with, along with the custom game starting menu. Though you can add new functional options to the custom game starting menu, you just can't rebuild or alter the custom game startup menu's format entirely.

That's it, everything else is exposed as far as I know.

Here's an example:
http://forums.civfanatics.com/showthread.php?p=8291149#post8291149

It's Sudoku in the Civ Engine.

The diplomacy screen is also not moddable, but all the other parts are accessible i guess.