Building the smallest sufficient subtree of DAG to run +state:modified+

[gastlich]

Background

Hello guys,

In our company we run the whole DBT build from scratch for every single PR. Pull requests share only sources with production, but the remaining models/seeds are unique and created ad-hoc for PRs.

As you can image this started to slow our process down, due to increasing number of models. Because of that we hoped, that using +state:modified+ is going to solve our problems. Sadly, we failed, because it looks like +state:modified+ doesn't handle all of the dependencies as we thought.

Example

In the following example:

after changing seed1 and running +state:modified+ on a clean database we get:

08:28:24  1 of 3 START seed file dp_dev.seed1 ............................................ [RUN]
08:28:29  1 of 3 OK loaded seed file dp_dev.seed1 ........................................ [INSERT 4 in 4.71s]
08:28:29  2 of 3 START sql table model dp_dev.model1 ..................................... [RUN]
08:28:31  2 of 3 OK created sql table model dp_dev.model1 ................................ [CREATE TABLE (4.0 rows, 86.0 Bytes processed) in 2.56s]
08:28:31  3 of 3 START sql table model dp_dev.model2 ..................................... [RUN]
08:28:32  3 of 3 ERROR creating sql table model dp_dev.model2 ............................ [ERROR in 0.43s]
08:28:32
08:28:32  Finished running 1 seed, 2 table models in 0 hours 0 minutes and 10.66 seconds (10.66s).
08:28:32
08:28:32  Completed with 1 error and 0 warnings:
08:28:32
08:28:32  Runtime Error in model model2 (models/example/model2.sql)
08:28:32    404 Not found: Table dp_dev.seed2 was not found in location EU

Conclusion

Based on what we've experienced, we think that DBT doesn't interpolate the selector +state:modified+ in the greedy enough way. We would hope, to have a simple way to rebuild the smallest sufficient subtree of our DAG, which satisfies all of the dependencies. We tried looking into indirect_selection, but without any luck. Also, we are aware of the defer functionality, but in our case it won't work out of the box. Maybe we missed something obvious or we are lacking some knowledge. I would appreciate for any suggestions or feedback :) Thanks!

[b-per]

Hi! Have you tried with the "at" operator?

The @ operator is similar to +, but will also include the parents of the children of the selected model. This is useful in continuous integration environments where you want to build a model and all of its children, but the parents of those children might not exist in the database yet.

[gastlich]

Thanks you @b-per for your help! This was what we've been looking for. Since DBT is a featureful tool, it's sometimes difficult to find what you are looking for. We gave the @ operator a go and it looks like it's working fine and reliably in our setup. Thanks again 🙇