The article doesn't provide any good example of misuse. And that's exactly how you use it. It's clean and simple, no hidden pitfalls.
Schema migration tools are overhead when you have a few tables.
The pitfall is pretty simply stated: "It masks the problem with logic, adding some risks of anomalies." When one puts a bandaid over bad data, the problem isn't solved, it's masked. Depending on the issue, it could bite you later, in unexpected ways, at the worst possible times.
In this particular case, the "bad data" is a table/column/view that exists (or doesn't) when it should(n't). Why does the table exist when it shouldn't yet exist? Did it fail to get dropped? Is the existing one the right schema? Is the same migration erroneously running twice?
After each migration, your schema should be in a precise state. If the phrase "IF [NOT] EXISTS" is in your migration, then after a previous migration, it wasn't left in a precise state. Being uncertain about the state of your schema isn't great.
We use IF NOT EXISTS to bring non prod environments in sync with prod. The size of prod requires some migrations to be done separately over course of days, in separate transactions. The IF NOT EXISTS clause then brings dev and non prod envs in sync.
In many cases you can still be ok just using IF NOT EXISTS everywhere, or occasionally IF EXISTS when trying to delete a no-longer-used col or index. Whether you start with a blank DB or any intermediate state, you'd end up with the same thing. But it gets messy after a long enough time.
>The article doesn't provide any good example of misuse.
It did describe the misuse pretty well, though. The idea is that out-of-band schema modifications are a process/workflow issue that needs to be directly addressed. As stated by OP, this is an easy way for anomalies to creep in - what if the already present table has different columns than the one in the migration? IF EXISTS lets a migration succeed but leaves the schema in a bad state. This is an example of where you would prefer a migration to "fail fast".
The article doesn't provide any good example of misuse. And that's exactly how you use it. It's clean and simple, no hidden pitfalls. Schema migration tools are overhead when you have a few tables.