Re-evaluate adding compile task when using ExecutionMode.AIRFLOW_ASYNC

[tatiana]

In Cosmos 1.7.0, we introduced experimental support to ExecutionMode.AIRFLOW_ASYNC, as discussed in this article and documentation page:

• https://medium.com/apache-airflow/introduction-to-asynchronous-dbt-bigquery-orchestration-with-apache-airflow-and-cosmos-3fd19f348585
• https://astronomer.github.io/astronomer-cosmos/getting_started/execution-modes.html#airflow-async-experimental

A fundamental characteristic of the approach implemented in 1.7.0 is that we'd pre-compute the SQL in a single first setup task, called dbt_compile, and the remaining tasks would only need to run the SQL statements, as illustrated in:

This approach had some problems:

1. The SQL run by Cosmos was incorrect, as discussed in [bug] Fix ExecutionMode.AIRFLOW_ASYNC query #1260
2. Need to pre-compile and upload SQL statements to remote storage (additional configuration / possible latency)
3. The fact it was not clearing out those files after

As part of fixing #1260 using a monkey-patch, in #1474, we noticed that the dbt_compile step did not have to happen beforehand since we could monkey-patch per run statement, leading to a refactor to remove the dbt_compile and run the monkey-patched dbt command with dbtRunner per task. While this is cleaner from a DAG topology perspective, it is unclear what is the best approach moving forward since to run the patched dbt version in every run task will require:

a) dbt and Airflow being installed in the same Python environment on every worker node
b) possible memory/CPU overhead of running dbtRunner for every task

An alternative approach we could consider is:

• Re-introduce the dbt_compile task, but identify the possibility of running it with Cosmos ExecutionMode.VIRTUALENV
• Upload SQL to remote object store
• No longer have the dependency of running dbtRunner per run task

The advantages of this approach would be:

• dbt and Airflow would not have to be installed in the same Python environment, potentially (some changes to how we monkey-patch may be needed)
• most worker nodes would not have to run dbt commands
• the memory and CPU usage per worker node should go down while just executing the transformation

The downsides would be

• We may have to change how we monkey-patch, and we would potentially not be running using dbtRunner
• Re-introduction of the dbt_compile task and have to implement the correspondent [async] Introduce teardown node when using ExecutionMode.AIRFLOW_ASYNC #1232 task

Ideally, we'd compare these two approaches with real dbt projects and evaluate the numbers before making a decision.