+1 for Babbage/Lovelace history, however IMO, although the two facts are separately true:
(1) he was let down by precision machining not existing (Tim Robinson https://www.meccano.us/difference_engines/rde_1/ says that "I have no doubt that if the Meccano of the 1920's had existed 100 years earlier, Babbage would have been entirely successful in his quest"), and
(2) he designed a more complex system, tried Italy, etc,
I don't think it's fair to say that he decided that (2) was the best way forward from (1); it's rather that both were consequences of his ideas outpacing what was realistically feasible: he was a software guy thrust into hardware, coming with up ideas that seemed straightforward and discovering that manufacturing was impossible. Apart from his lack of business/project-planning sense (scope it down; don't aim for 10 digits etc), I think other complicating factors that went into the tragedy of Babbage were:
(1) He kept coming up with new/better ideas and pursued them (basically rabbitholing as mentioned),
(2) He had won a bunch of awards at a young age simply for proposing the Difference Engine (everyone could see it was a good idea and also seem to have expected it to be straightforward to build: a fait accompli) — so in the intervening decades he must have felt like he couldn't give up,
(3) He got entangled with the government. IMO the tragedy here is that he was just middle-class enough to have a romantic idea of government: while the nobles distrusted government/politics as they sort of looked down on it, and the lower classes distrusted government as it had never done anything much for them, he was of just the right class (his father came from humble origins and had made money in banking) to have patriotic notions of government and all that — he wanted to offer his invention to "the nation" (government), and conversely thought the government "ought to" reward him for it, rather than understanding the practical problems of government officials in funding his project. (The government offered to give his invention back to him, but he refused.)
(4) Possibly as a result of these awards, he seems to have been attached to the idea of being a "smart" person (many examples, e.g. the anecdote quoted in one of the appendices in Sydney Padua's wonderful book, where he refused to judge an award along with Faraday — he thought he "deserved" to be the sole judge) — this also probably got in the way of doing practical things rather than pie-in-the-sky "genius-type" ideas.
I think the government entanglement is probably a big part of the story (he asked them basically "I haven't completed the Difference Engine but I have a much better Analytical Engine that I could implement with more money, what should I do?" and they sat indecisively for twenty years!), and it's interesting to read his accounts (in his memoirs) vs others', e.g. Lord Playfair's account from the same appendix:
> "He was in chronic war with the Government because it refused to furnish supplies for his new machine, the ground of refusal being that he never completed the first. […] Babbage always considered himself a badly treated man, and this feeling at last produced an egotism which restricted the numbers of his friends. […] Babbage, who was delighted with the suggestion, but made it a condition that he alone should be appointed, as a reparation for all the neglect of the Government towards his inventions. Even the association of such a distinguished man as Faraday would take away from the recognition which was due to him."
Anyway Padua's book (The Thrilling Adventures of Lovelace and Babbage) seems very well-researched (I admit I haven't read much of it but read all the appendices in detail; would strongly recommend anyway).