What is the point/benefit of the dog?
The main purpose is to trade objects between players. Say that a character gains the Revolver item, which allows them to use Speed to attack. If the character who found it has a low speed, they can use the dog to give it to another player with a higher speed, without having to take the time to meet up with them on their own.
Some scenarios require an object to be in a certain location. For instance, The Mummy Walks (#1) requires the book to be in the same room as the mummy in order for a character to attack it. If one of my allies is in the same room and I'm 6 spaces away, it would probably take me two turns to give it to them. But with the dog, I can give it the book and then have it travel the distance in one turn.
Also, some of the scenario setups instruct you to put items in rooms, such as The Stars are Right (#14), which tells you to place paint cans in several rooms. The dog can pick those up and give them to its owner.
I'm assuming it can only pick up dropped items? What items can it pick up, in what scenario?
The rules do indeed say that the dog can pick up a dropped item. I suppose technically you could have a character drop an item (a free action) and then have the dog pick it up.
My group has always ruled that a character can voluntarily give or take something from the dog, since that seems to make the most sense thematically and avoids the extra complexity of dropping and picking up things. It's basically the same as the Trade action, but at range through an intermediary.
The dog can carry any omen or item, aside from ones that slow movement (per the dog's description) or specifically say that they can't be transferred or carried by the dog. For instance, it can't carry the Girl or the Madman because those say they can't be transferred. Some scenarios will introduce new objects and will specifically say that the dog can't pick them up.
If it can explore tiles, and landing on a tile with an icon that makes you draw a card which ends your turn, and your dog can explore 6 of these freely each turn, how do you keep track of which tiles still need cards drawn when you land on them? Do you still draw cards when you land on these spaces?
The question is moot because the dog can't explore tiles. Per the description, the dog can only move to "any explored room up to 6 spaces away".
If a explorer finds a room that has to roll, does the dog sit outside or find a way around? Or does the final rule only apply for when it is exploring?
The last paragraph about one-way passages and rooms that require a roll is intended to be an extension of the rules of the second paragraph.
In other words, when the dog is sent out to a space 6 spaces away, it cannot go through one-way passages or rooms that require a roll. If it's not moving on its own, the dog is "along for the ride" and stays with the player just like any other omen or item.