A brackish water aquarium is simply an aquarium where the water is more salty than freshwater, but not as salty as seawater.
In practise, a brackish water aquarium houses fish from habitats with intermediate levels of salinity between freshwater and the sea, for example estuaries and mangrove swamps. Fish from these habitats do not to do well in plain freshwater aquaria, despite often being sold as freshwater fish.
For many aquarists, the attraction of brackish water aquaria is the diversity of oddball and otherwise interesting species: archerfish, mudskippers, violet gobies, pufferfish, flatfish, etc. There are also any number of fish that are just pretty and fun to keep, like monos, scats, shark catfish, orange chromides, etc. Traditionally the brackish water aquarium has also been seen as a way to "practise" saltwater fishkeeping without risking the expense of marine fish and invertebrates, though as hardware and especially filtration has improved, this is much less of an issue than it was 30 or 40 years ago.
Cheers, Neale