sql versus nosql, when does the tradeoff actually matter

hey folks, ive been thinking a lot about database choices lately. weve got a new project kicking off and the team is split. some want to go full NoSQL for flexibility, others are leaning towards SQL for ACID guarantees. ive seen plenty of articles explaining the theoretical differences, but im curious about real-world scenarios. when have you found the choice between SQL and NoSQL to be a critical decision point that impacted your projects success or failure? what specific trade-offs became apparent, and how did you navigate them?