I have read Herodotus' History and can see he delivers some real whoppers in terms of things and events that are highly unlikely to have existed or happened. This leads many to opine that generally he can't be trusted as a reliable source. I'm not so sure. For instance his claim that Scythians used marijuana by burning its seeds inside felt tents on braziers has been borne out by archaeology. My question is how reliable is he?
You have actually asked two different questions here. The first question is whether Herodotos is reliable and the second question is whether he was writing fiction. These are two closely related, but distinct, questions.
I think that virtually all scholars who have studied Herodotos would acknowledge that there are some stories in his Histories that cannot be factually true in the way that Herodotos tells them. For instance, almost no scholar currently alive would seriously believe Herodotos's story that Arion the lyre player, when the crew of the ship he was on demanded that he throw himself overboard, sang for them beautifully while dressed in all his finery, threw himself overboard, and then was miraculously rescued by a dolphin that carried him safely to shore (Histories 1.23–24).
Likewise, no one would accept at face value Herodotos's statement that there are giant ants covered in fur that are the size of dogs that live in what is now Pakistan and dig in the sand for gold (Histories 3.102–105). No one seriously believes either that, when the Achaemenid forces were advancing upon Delphoi, some god struck them with a thunderbolt and caused two peaks of Mount Parnassos to come crumbling down to frighten them, and then two giant warriors appeared to drive the Achaemenid forces away (Histories 8.35–39).
On the other hand, despite acknowledging that these sorts of stories are obviously fanciful, no scholar would ever argue that there is no truth whatsoever in anything Herodotos says. On the contrary, virtually all scholars would agree that Herodotos's account of the Greco-Persian Wars is at least accurate in its broadest strokes.
No one would deny, for instance, that Darius's invasion happened in 490 BCE, that the Athenians defeated Darius's forces at Marathon, that Xerxes's invasion happened in 480 BCE, that the Achaemenid forces defeated the forces of the allied Greek poleis at Thermopylai, forcing the naval forces at Artemision to retreat, that the Achaemenid forces sacked Athens, that the allied Greek naval forces dealt the Achaemenid navy a decisive defeat in the Battle of Salamis, turning the tide of the war, and that the allied Greek forces defeated the Achaemenid forces in the Battles of Plataia and Mykale in the following year. There is plenty of evidence from outside Herodotos to substantiate that all these events I have just mentioned really happened.
When it comes the question of whether Herodotos was making stuff up, matters are similarly complicated. Virtually all scholars would agree that Herodotos makes up conversations and speeches. For instance, no one would argue that Herodotos had any way of knowing the exact words that Kroisos and Solon, who both died over a hundred years before the time when Herodotos was writing his Histories, might have really said to each other (Histories 1.30–33).
It is possible that the story Herodotos tells about the conversation between Kroisos and Solon is based on a preexisting oral tradition or folktale he heard, but it is abundantly clear that the speeches he attributes to them are his own invention. Herodotos probably saw nothing dishonest about doing this, since he was following Homer as his model. The Homeric characters often deliver speeches, but it would have been understood by Greek audiences that these speeches are the invention of the poet, not the actual, exact words of Hektor, Achilleus, Odysseus, Andromache, Helene, et cetera.
The greatest scholarly contention related to this question is over two issues. The first is whether, when Herodotos cites a source, he is citing an actual source that he really consulted or he is just making up nonexistent sources for literary purposes. The second issue is whether Herodotos really traveled to the places where he claims to have traveled (such as Egypt, the Black Sea, et cetera).
One school of Herodotos studies (which includes among its members scholars such as Detlev Fehling, O. Kimball Armayor, and Stephanie West) holds that Herodotos is primarily a literary artist, not someone who was really concerned with historical reality. This school holds that that all of Herodotos's source citations in his work are completely fictitious and untrustworthy and that he never really went to most of the places he claims to have gone. Opponents of this school have derisively nicknamed it the "Liar School" because the school essentially holds that Herodotos was a liar who fabricated sources.
On the far opposite end of the spectrum, there are some scholars who not only maintain that Herodotos did not lie about his own travels or fabricate sources, but who are even willing to defend many of his claims that seem ridiculous on the face of things. An example of this would be W. K. Pritchett in his book The Liar School of Herodotos (published in 1993 by Brill), which is an all-out assault on the school of Fehling, Armayor, and West, in which Pritchett even seems to argue for the historicity of the story of Arion and the dolphin that I mentioned earlier.
I would say that most scholars fall somewhere on a spectrum between these two extremes. How a scholar treats Herodotos is also inevitably going to depend on what they are using Herodotos's work for. Scholars who are more interested in reading Herodotos's work as literature are generally more likely to fall closer to the Fehling, Armayor, and West end of the spectrum, while scholars who are more interested in reading Herodotos's work to learn about actual events in Greek history are generally more likely to fall closer to the Pritchett end of the spectrum.
For further reading