What challenges have you faced with automating database migrations and how did you resolve them

What challenges have you faced with automating database migrations, and how did you resolve them?

This question focuses on the general issues that are found with database migrations automation in CI/CD pipelines and how they are handled. Migrations of the database must be handled with great care since data inconsistencies, loss, or downtime might result when dealing with more than one environment and big databases. The answer should bring to the fore specific issues like schema conflicts, backward compatibility, dependency management, and handling downtime for production systems. Effective solutions ought to include; version control migration tools rollbacks of the database systems, which are snapshots automatically tested.