Mothership Zeta provides very a nice change of pace when compared to wandering through the irradiated lands of Earth. Although the spaceship primarily consists of narrow hallways, there's enough variety in rooms and areas to keep things interesting. The main issue is that there isn't a whole lot of "off book" exploring, where players can take a short break from the main story to check out different parts of the ship. Once the campaign is completed, players might find that there isn't much that they missed. The content doesn't do a whole lot to change the world of Fallout 3, other than give you command of a spaceship, but it ends up being just another place to hang out.
Furthermore, the alien stasis pods can't be used by players to rest in, so there isn't much reason to stay. The main incentive in purchasing the DLC is in getting the shiny disintegrators to vaporize enemies with.