Dynamic task mapping vs many parallel operators

In our case the root cause was an implicit cast preventing pushdown.

Sometimes the real fix is a product change so you stop needing that join at all.

Quick plain-English version: the database is doing more work than it needs to because it can't tell in advance which rows actually match. An index is basically a shortcut list so it doesn't have to check every single row.

Document the grain decision, most BI bugs turn out to be grain bugs.

In simple terms: your job is asking for way more data than it actually needs, and that extra data has to travel across the network or between machines, which is slow. Ask for less, or ask smarter.