Let's say there's a small piece of software you want to install. A command-line PDF-processing utility, or something. You use brew to install it.
brew decides to update everything. Let's say (not an arbitrary example) this includes OpenSSL. It removes the old OpenSSL.
Everything you built which somehow depends on OpenSSL (and that's a lot of stuff) is now broken. If it was just Homebrew installs, that would be fine, because they'd be upgraded too[1]. But it isn't. It's everything you've built from source which linked against the former, Homebrew-installed OpenSSL.
You now have to spend the rest of the afternoon rebuilding and reinstalling stuff because Homebrew decided to trash a library that it had previously installed... all because you wanted to install one tiny command-line tool.
This has bitten me, and many others, countless times. Here's two examples from a minute's Googling about the OpenSSL issue: https://github.com/kelaberetiv/TagUI/issues/635, https://github.com/kelaberetiv/TagUI/issues/86. But it's far from an isolated case - I've had stuff break on me because brew has decided to upgrade proj, for example, and the newer proj headers aren't backwards-compatible. (Pretty much all open-source geo stuff depends on proj somehow.)
In theory you can brew pin stuff. But either I brew pin everything, or I need a crystal ball to decide what brew will break next. OpenSSL? proj? Postgres? I honestly wouldn't have worked those out for myself.
I am not installing Homebrew on any Mac I own in the future.
[1] In theory. I've had Homebrew make an entire Postgres database unreadable due to an arbitrary, and unwanted, upgrade. Fortunately I was saved by recovering the old data files into Postgres.app, which can run different versions of Postgres.