As others have said it is not a terribly useful question and will need to be qualified a lot. You also have the ROM hacking vs homebrew made from scratch concept. Theoretically I guess you could also have something with emulators in there at some level, I would struggle to find something like this for the SNES though; typically you are looking at things like N64 hi res textures, though the related concept of externally loaded palettes might apply here.
What was the largest commercial cart seen? That can be answered. However flash carts for each of the consoles will tend to exceed this.
What is the largest raw value without mappers, pages, bankswitching, memory bank controllers (GB/GBC) and similar tricks. Typically this is the address window for cart reads though it can be smaller. In the case of the megadrive/genesis most games stuck with the raw read value, some later games did have bankswitching though.
What is the largest value I can easily expand a ROM to and how would I set about it? This can also be answered though that is where homebrew vs hacking comes back in. Games are not terribly predictable programs and can read and write to arbitrary locations at arbitrary points in time. This is why mapper changes are considered one of the hardest hacks to do on the NES, made doubly so by games often doing (or, worse, having the potential to do it) odd pointer maths and related things (you can not just disassemble and search for all reads to a location as the game could add a value to a smaller value and use that as a the basis for a read, this means hunting down those edge cases or doing something and hoping it works which clashes with the aspirations towards perfection that hackers tend to need to succeed in this).
What is the largest value that can easily be made in a cart? As others have said give it enough logic and you could read exabytes on a NES. However more reasonably the things are the sum of the address window (whether mapped to the main memory bus or not) and basic onboard IO to speak to the carts. If you have a 256 kilobyte window split in two for mappers you then have 128 bytes plus 128 multiplied by the number stored in the mapper register or sent via the change mapper command (say 4 bits or 16 combinations giving you 2176 kilobytes, assuming every combination was given to storage and not say the result of some on cart logic to increase the base processing power of a console).
You could stick some crazy logic onboard the cart that says if you write this value 4 times then do some maths on the following command, indeed the electronics to do it is possibly quite basic if you left it as something like a shift register. A related concept can be seen in the 360 DVD emulators that present a series of pictures which the 360's picture viewer can read, as the emulator controls the USB it reads from it knows what is read and thus can present the relevant game. There was also a SNES video reader/playback device I saw a video of once, I imagine it did something like this.
There is also the concept of sector addressing. See anything that covers optical media for more on this, the concept is not terribly difficult though (where you might have a 1:1 relationship between bytes of data and the read here you do not and instead it will resemble something like banks).The
short version. summary
For the system you are looking at look up the specs.
The address window for the cart is your baseline unless those same specs say it is less; owing to the way binary numbers work a CD might have a sector read system capable of gigabytes but even with overburn and disabling error protection you are not going to be able to go above 850 megs or so on the physical disc. This might not be a limitation though as anybody that burned GC games (just over a gig) for the Wii (full DVD size, though in practice I think most stuck to single layer) will tell you.
For the methods of expansion most consoles are reasonably well known. The NES mappers and SNES special chips are very well documented for example, the GB/GBC memory bank controllers (MBCs) are covered nicely enough in http://nocash.emubase.de/pandocs.htm#memorybankcontrollers
, seen as there are relatively few games compared to the PC and consoles the MAME/MESS lot have a lot of the more niche arcade stuff covered quite nicely, https://wiki.neogeodev.org/index.php?title=Category:Cartridge_systems
has nice stuff for the neogeo, the megadrive/genesis stuff is a bit disparate when it comes to the various things used in practice but Toy Story, probably one of the more noted cases of megadrive bankswitching, has been dissected quite well by a few people. GBA homebrew (see pogoshell) and flash carts/clone multigames did exceed the 32 megabyte window, they used a cart specific page/remap and reset system, but other than an as yet undumped game (Shrek Video I believe) nothing went beyond that 32, code via the link port aka multiboot is also possible though it needs to fit in memory. The DS has a 32 bit read command, though things like the pokewalker were seen to use the "save" bus' SPI to read in data, DS homebrew has DLDI, network loaded code (download play) is common (though it has to be all in RAM) and the DS also has the 32 megs of GBA cart mapped in (read/write) memory as well.
If you are doing homebrew then the same expansion methods will probably give you the best way to set about making something. If not then you get to hardcode your assembler/compiler to deal with banks* and figure out a way to get some signal out to the cart/read controller to present a different location to the onboard reading.
*for the vast majority of things it makes sense to have one static window containing at least the main loop or an idle loop and one or more windows that can vary. This is not mandatory by any means though, you could even stick a copy of the main/idle loop in each bank and have the game never know any better (Final Fantasy 7 on the PS1 basically did this and the whole game is on each disc with only cutscenes differing). You could make a game use bounds checking to make sure it is reading from the appropriate bank/window/page, such checks naturally did not exist in normal ROMs (why NES mapper changes are hard) and would be a serious drain on CPU cycles if you tried it. The alternative of forcing a read to a hacked bank and then immediately reverting is less resource intensive but you are still at the mercy of interrupts, though that is probably more easily dealt with than hunting down every edge case in a ROM.
If you wanted to get fancy there are also things like serial ports, networking (various console hacks used this) and more for many consoles you could use to slide a bit more code in somewhere.
It appears I am in danger of waffling so I will stop here for the time being.