The issue I was experiencing was not with the source or sink tables, but with a interim table that ADF creates in-between for the upsert activity. This table by default appears to be created with a columnstore index.

To solve for this, I switched from doing an upsert to bulk inserting into a interim table, then manually merging those tables with a script.