A variety of reasons. Sometimes, your application is growing too rapidly and the licence fees are killing you. Sometimes, you may need to augment your transaction DB with some non-SQL full-text searchable DB. Sometimes, you need a very large, sharded DB cluster where you need replication and eventual consistency, not ACID properties.

You often don’t. Moving between modern relational databases can often be done without re-writing your Java or dotNet or other code which holds the business logic. The stored procedures, if any, need to be edited or re-written. However, if you are moving some part of your data to a non-SQL database, then a lot of code re-write may be needed.

The first and simpler use-case is Redis as a cache. This is a very useful addition to a legacy application which knows its relational DB as its One True Source Of Truth. These caches boost performance, but code change is needed.
The second use-case is something like Solr or ElasticSearch for a full-text searchable database, which holds data other than transaction data. This shifts part of the general search and auto-complete load away from the transaction DB to a faster, lighter, clustered, replicated document.
The third use-case is something like CouchDB. Awesome distributed replication with eventual consistency. And for super-heavy scalable transaction loads, stick to relational but move to PlanetScale. They are re-defining what scalable cloud databases mean. We can help with all.

Bugs exist in every large piece of software. The real issue is: does it impact you if you see none, even after extensive testing?
All the mission-critical databases we use have commercial support available from the product maintainers in case your corporate IT policy requires such support.
We have used these databases internally in our mission-critical projects and have never encountered a bug we couldn’t work around or get a fix for. No druids necessary for this magic.