I have no idea on how to add such date logic to the generated dbt code - indeed, writing an custom materialization should be possible.
But,I would say in case of big tables you need to cluster the data for the right purpose, and if there are two purposes for the data set in mind, then split the data set in multiple tables per purpose. In this case I could imagine a following way of doing:
have a main table containing time invariant fields maintained with inserts only, clustered by your date field (for downstream consumption reason), then have a secondary table clustered by order-id where you maintain your order-updates. Downstream consuming should then of course make use of join logic - but then again the join logic makes use of the clustering key in the secondary table.
And, now that I am writing my first idea, I come to think of another one, that might work. I conclude/assume that your clustering key is currently the order_dt. What if you introduce a replacing unique key based on a combination of order_dt and order_id , like a concatenation of the two (date field first). Your filter key for downstream consuming will still be order_dt, but that field would be equally nicely clustered as the actual new cluster key.
Well I could not say which one would work better. The second one, I believe, comes the closest to your intended addition of a date filter logic.
As last comment, I would like to say that I personally favour a principle like ‘inserts-only’ DWH. So I would investigate solutions like separating time variant fields from the time invariant fields (like Data Vault does - all tables insert-only) and joining them during downstream consumption.