Covering index worth it for wide dimension table?

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

Our dimension is slowly changing, does that change the join order?

ELI5 version: it's not broken, it's just slow because it's checking way more stuff than it needs to. Narrowing what it checks is almost always the fix.

Any downside to this approach with incremental models?

Any downside to this approach with incremental models?

Small nit: the broadcast hint gets ignored once the table is over threshold, check the UI to confirm.