Most any engineer or developer above a junior level position should have at least one of these stories. It also gives you the chance to ask what they learned, how they fixed it, what resources they used, what they did to prevent it in the future, etc, etc, etc. It's also a really humbling moment to have to explain one of your failures and will show you a lot of the person's personality when they're explaining it.
The two big negatives I have seen with this question is either someone becoming combative as you ask more questions, or they go full 'used car salesman.' If either of those happen, you can safely run away screaming from the interview.
This is a gut/understanding question that is best given/asked by another technical user and has been a solid divining rod for interviews that I've done.