[CT-2272] [Feature] Export/list DAG execution plan

[timle2]

Is this your first time submitting a feature request?

• I have read the expectations for open source contributors
• I have searched the existing issues, and I could not find an existing issue for this feature
• I am requesting a straightforward extension of existing dbt functionality, rather than a Big Idea better suited to a discussion

Describe the feature

dbt runs workflow orchestration internally without exposing much of what is happening to the user. There is currently no way to see a execution plan (a.k.a. what tasks of the DAG are to be run, and in what order) based on a given dbt command. This information is only apparent during the run via console output, and after the run via the run command.

Why do we want the execution plan before a run happens?
There are many situations where having access dbt's execution plan, before the run happens, is of value.

Having an execution plan before a job runs is valuable because it allows for better planning, validation, and optimization of the job execution.

Firstly, an execution plan outlines the tasks and their dependencies in a pipeline, providing a roadmap for job execution. This allows teams to plan their resources and schedule their jobs in advance, ensuring that there are no resource conflicts or bottlenecks during execution.

Secondly, an execution plan can be used to validate the pipeline and its individual tasks before running the job. By analyzing the execution plan, teams can identify any potential issues or errors that may arise during job execution, such as missing dependencies or circular references. This validation step helps to catch errors early on and avoid costly job failures or data corruption down the line.

Finally, an execution plan can be used to optimize job execution by identifying areas for improvement or potential bottlenecks in the pipeline.

While many of these scenarios are handled manually by visualizing the DAGs in docs, it's not simple to programmatically generate a comparable output for analysis before a run occurs.

Additionally, there are users that wish to export the dbt DAG to a different workflow orchestration platform, and run each task individually there. There can be great values in pairing of dbt's powerful capabilities for building and compiling complex SQL-based workflows with an external workflow orchestration tool that offers an equally rich set of features for workflow orchestration. The goal there being to leverage the strengths of both tools to create a more robust and flexible data transformation workflow that can be easily managed and maintained over time.

What about dbt list
An obvious entry point to extract the dbt DAG for a run would be the dbt list command. The dbt list command is used to display a list of available dbt resources (models, tests, and macros) in a project. When you run dbt list, it will print out a table that shows the names and descriptions of all available resources in your dbt project. It will return the results based on a --models/--select input, but does not order the models in the same way as they would run during execution. The dbt list command does not output an execution plan, but it can be useful to see a summary of all available resources in your project.

How dbt runs tasks based on the DAG
dbt converts the DAG for a given run into an internal graph_queue construct. It performs a topological sort to score each task, and then orders them into a queue. Each item in the queue is then run, and marked done on completion.
Ideally we'd be able to get the tasks and order that exist in this queue in order to have an accurate understanding of what dbt will run.
e.g.
ideally being able to take a run command (seen here in dbt docs)

(Please note that in my example model_e is ephemeral)

and generate an execution_plan (without running any models!) like so:
(Please note that in my example model_e is ephemeral and so excluded from the plan)

[{'id': 'model.my_new_package.model_a',
  'score': 0,
  'name': 'model_a',
  'refs': []},
 {'id': 'model.my_new_package.model_b',
  'score': 1,
  'name': 'model_b',
  'refs': ['model_a']},
 {'id': 'model.my_new_package.model_c',
  'score': 2,
  'name': 'model_c',
  'refs': ['model_b']},
 {'id': 'model.my_new_package.model_f',
  'score': 2,
  'name': 'model_f',
  'refs': ['model_a']},
 {'id': 'model.my_new_package.model_g',
  'score': 2,
  'name': 'model_g',
  'refs': ['model_a']},
 {'id': 'model.my_new_package.model_h',
  'score': 2,
  'name': 'model_h',
  'refs': ['model_a']},
 {'id': 'model.my_new_package.model_c2',
  'score': 3,
  'name': 'model_c2',
  'refs': ['model_a', 'model_c']}]

Describe alternatives you've considered

dbt list command

Who will this benefit?

Power users looking to:

• plan their resources and schedule their jobs in advance
• validate the pipeline and its individual tasks before running the job
• optimize job execution by identifying areas for improvement or potential bottlenecks in the pipeline by generating the plan ahead of the run
• export the dbt DAG/execution plan, at the task level, to a different workflow execution platform

For more, here is a series of articles I've written on this topic!

• Who Let the DAGs out? From dbt into Flyte: Exporting and Running a DAG
• Who Let the DAGs out? Getting the DAG out of dbt (Chapter 2)
• Who Let the DAGs out? Register an External DAG with Flyte (Chapter 3) [less relevant to this discussion]

Are you interested in contributing this feature?

yes

Anything else?

No response

[timle2]

Updated description with links to the fuller version of this concept and proof of work:

• Who Let the DAGs out? From dbt into Flyte: Exporting and Running a DAG
• Who Let the DAGs out? Getting the DAG out of dbt (Chapter 2)

[dbeatty10]

Thanks for such a thorough write-up here and within the series of posts @timle2 !

This is a Big Idea 💡 that I'm going to promote to a Discussion.