So, The Download play option on the main DS menu is used for two things at the moment.
1> A number of games will have, in the game menu, a wireless Single-Card Multi-player option. Like the Multi-player minigames offered by Super Mario 64x4, New Super Mario Bros, and Zelda: Phantom Hourglass.
For this option, the person with the game goes into the game and starts the single card multiplayer mode, then the other players go into Download play on the DS main screen, and it downloads the minigame client, so you can all play.
Often this is a limited version of the multi-card multi-player. Like Mario Kart. With multiple cards, you can choose you racers and any track, with single card, the people without cards can only play as Shyguy, and you can only choose from the first tier of tracks.
2> Download stations in gamestores. If you go to EB Games, Gamestop, Bestbuy, Toys R Us, or other similar stores, they will usually have a DS Download station set up. This constantly transmits. You go into Download play, it finds the Download station, and then gives you a list of options. Generally it will have 3 or 4 playable demos, 4 to 6 preview movies, and occasionally bonus content for a game.
The newest one I've seen, for example, had Demo's of Cake Mania, Cookie and Cream, and videos for Transformers on the Wii, and a bonus item download for the Chocobo Dungeon game.
Now, as for Homebrew...
There's several different types of flashable DS cartridges available.
An example would be M3 DS, which is an adapter in the shape of a DS cartridge, that takes a Micro-SD card. You plug the Micro-SD into your computer, save files onto it, then put it in the DS adapter, put it in the DS and run it.
Now, here's the catches.
1> There are about 4 major options at the moment, and the aren't wholly inter-compatible. Which is to say, games written for one card may not work on another adapter card.
2> Homebrew material is fairly rare as far as I can tell. There aren't a whole lot of games to look for, and they often turn out to be dead projects which are about 50% done and not going anymore.
3> A bad homebrew game, or a bad ROM can damage the operating system of your DS. I.E. This was a DS, now it are a Doorstop.
4> Also, Keep in mind that possession of a ROM for a game which you do not own a hard copy of, or distributing copies of ROMs to people who do not own a hard copy of the game, is HIGHLY illegal, and can result in fines of up to US$10,000, and 5 years in federal prison PER OFFENSE, in addition to being liable, on top of that, for damages equal to lost sales revenue to the publishing company of the game. NO ILLEGAL ROMs, The DMCA people will come for you.