In older times I would just use a hint to make joins run in a specific way to filter rows early, however today just shuffling join order was good enough!

    from {{ ref('stg_orders_tmp') }} as orders

    {% if is_incremental() %}
        left join {{ this }}
            on orders.request_id = {{ this }}.request_id
                and orders.__metadata_timestamp = {{ this }}.__metadata_timestamp
    {% endif %} 

        left join {{ ref('stg_zones_tmp') }} as pickup
            on ST_Intersects(
                ST_Point(orders.pickup_position_lon, orders.pickup_position_lat), pickup.geometry)

        left join {{ ref('stg_zones_tmp') }} as dropoff
            on ST_Intersects(
                ST_Point(orders.dropoff_position_lon, orders.dropoff_position_lat), dropoff.geometry)

{% if is_incremental() %}
    where {{ this }}.request_id is null
{% endif %}