Enable query optimizer hotfixes in dbt model

Help
1

The problem I’m having

I am trying to migrate code from at stored procedure in T-SQL to an incremental dbt model.

In the stored procedure, we are using

OPTION (USE HINT (‘ENABLE_QUERY_OPTIMIZER_HOTFIXES’))

in order to achieve good performance.

It seems I cannot include this code line in the dbt -model - it fails with error message “Encountered unknown tag ‘OPTION’.”.

Is there a way to solve this? Some macro that allows me to enable the query optimizer hotfixes, or some other workaround?

The context of why I’m trying to do this

We have a SQL Server database, and we use Polybase to connect to our source data, which are located in an Oracle database, through external tables.

Without the query optimizer hotfixes enabled, we get problems with the performance because the filter clause, i.e.

where date_day >= (select coalesce(max(date_day), ‘1900-01-01’) from {{ this }})

is not pushed down to the source database.

What I’ve already tried

I have tried to omit the ENABLE_QUERY_OPTIMIZER_HOTFIXES, to see if dbt somehow handles this in some other smart way. But the result is that the performance for an incremental load is like the performance for a full refresh of the model.

2

I found a solution where I do not need the query optimizer hotfix enabled. I used a macro to calculate the max(date_day), and then I assigned the max date to a jinja variable max_date_day using this macro in a set operation. Then my filter clause for incremental load would get a fixed date in the compiled sql, not a variable:

Instead of this:

where date_day >= (select coalesce(max(date_day), ‘1900-01-01’) from {{ this }})

I now have this:
{% if is_incremental() %}
where date_day > ‘{{max_date_day}}’
{% endif %}

Which compiles to something like this in an incremental load:
where date_day > ‘2025-08-13 07:33:29’

And since the problem with pushdown for Polybase occurs when there is a variable in the filter clause, I no longer have this problem.