The best would be New Super Mario Bros 3 and New Metroid Bros, but there aren't that many DS Rom hacks to be honest with you. Most of them are just pokemon games, but they really didn't change much other than the pokemon you can catch

Short version- between cheats, save editors, hacking guides, tools and people mainly just reversing things and leaving it at that not all that much in the way of rom hacks actually has a patch you can download and apply compared to some of the earlier consoles.

Longer version-

As mentioned there are not that many rom hacks with most people choosing to focus on translation but in addition to those already linked (do note there are a few more NSMB and it seems Mario 64 DS is heading that way). DS pokemon has not hit some of the levels we saw with the GBA and older ones as well (almost game conversions/challenge modes), some nice tweaks to the games but still little more than pokemon catching location type things.

TetrisDS has a bunch of sound hacks but frankly it is trivial to do that so I might as well just link the method. http://gbatemp.net/index.php?showtopic=122534
Also http://jul.rustedlogic.net/thread.php?id=7518 and read the last few pages of the rom hacking docs up above as some sound work was going on there as well.
This applies to just about every DS game that has the SDAT sound format- a couple were done for pokemon as well.
On the matter of sound TWEWY and N+ (two of the most notable games not to use SDAT) also have a few hacks to sound and TWEWY even has some tools. Electoplankton even uses straight up windows grade wave files.
I will mention undubbing at this stage- for just about every SDAT using rom you can just swap out the US/EU release's sound for the Japanese one, a few roms do not like being rebuilt with the standard methods, even fewer have actual problems with different sound being there and a select few need proper hacking work done (I am tempted to merge the last two entries in this list).

You have the trainers but they are not so useful these days between cheats and stuff like DSATM.
On that line though you do have things like the zelda d pad hack (zelda was forced to use the touchscreen for most of it).
Not sure where the download is these days.
Another one of those was made for starfox commandhttp://crackerscrap.com/docs/sfchacktut.html
I think those also ended up as cheats, you should look at the cheats though. It is long past the "infinite lives/ammo" routine there with some seriously elaborate stuff going on.

A few people have done some basic sprite and 3d hacks (mainly mario kart which also had a few tweaks towards beta levels and Soma Bringer had some stuff done as well)

There are a few more but I am drawing a blank right now. I will say browse around http://www.warsworldnews.com/ if you like advance wars. Most of their stuff is aimed at the GBA series but they have a few DS related things.

That is not to say people are not interested- several games have had text pulled apart with an aim towards translation improvement but it has not yet happened.
Equally a fair bit of work has gone into reversing other formats and spriting as well not to mention I have had a few interesting conversations with people over the last few months that are looking at total conversions of a few things (some of these modern games have seriously malleable engines).

I should probably mention rom ripping- a few people did do things like level rearrangements (similar idea to some of the mario kart stuff).

Looks like I'm left with a handful of NSMB hacks.
I've just been all of a sudden getting into the "good" NES ones a lot lately (Holy crap is Zelda: Outlands good...) they tend to feel like the sequels the games never got. To be honest, the original (read: Japanese) SMB2 was basically an elaborate ROM hack.
Oh, and yeah the controller hack for LoZ is in the cheats. I'm not interested in Pokemon, so I'm not even going to look there.
Thanks for the info though!