I have queries which unpivot billions of rows on ONLY 2 columns. I have refactored to NOT use unpivot. This has reduced processing time from 27 minutes to 9 minutes.

I did this by just using UNION ALL (the old fashioned way) instead of unpivot.

Based on my experience, I would not recommend using unpivot (ever) if you care about performance