We haven't been able to figure out the exact cause, but we found solution around it. If you precalculate the datediff of the joins you don't get this error and the query runs significantly faster.

    inner join dates d 
        on p.activity_date between dateadd(day, -7, d.end_date) 
        AND d.end_date

    inner join dates d 
        on p.activity_date between d.end_date_m_7
        AND d.end_date

I'm suspecting it has something to do with distributing data and that it does it in a smarter way when it already has the result of dateadd(day, -7, d.end_date) . Maybe it doesn't realise that it will be the same for each day.

We're running a medium SQL Pro warehouse with cost optimised spot policy. I don't see the version, but I guess it is the current one for 2/3 2023