[Bug] Update command-based logic in compiled_code context member, to match sql

[jtcohen6]

Is this a new bug in dbt-core?

• I believe this is a new bug in dbt-core
• I have searched the existing issues, and I could not find an existing issue for this bug

Current Behavior

In #9040, we added defer_relation for all tasks that perform deferral.

Previously, there was dynamic routing logic in the sql + compiled_code context members that checked for the existnece of defer_relation and overwrote the "compiled code" to use during clone.

#9040 updated the dynamic routing logic for the sql context member (based on the running task), but not the compiled_code context member.

These need to be the same. Otherwise, I might build a model with deferral and, depending on whether the materialization uses {{ sql }} or {{ compiled_code }}, get a different (inconsistent) result.

Expected Behavior

There should be no difference between {{ sql }} and {{ compiled_code }} for SQL models.

Steps To Reproduce

1. dbt-core main branch
2. Use a materialization that calls compiled_code instead of sql, e.g. dbt-snowflake's incremental materialization

Relevant log output

$ dbt build --debug
...
create or replace transient table ANALYTICS_DEV.dbt_jcohen.dim_certification_candidates
         as
        (select * from analytics.analytics.dim_certification_candidates
        );

That ain't right!

Environment

dbt: main

Which database adapter are you using with dbt?

No response

Additional Context

No response