DBT should have a Dry-Run mode

[mikekaminsky]

DBT should have a dry-run mode

Feature description

DBT should have a dry-run mode so that users can see all of the "commands" DBT will run for a given command without having DBT actually execute those commands against a database.

Who will this benefit?

This can be useful for both debugging as well as making sure that a given command is configured properly. This is especially useful when you're configuring a production setup and want to make sure you're not about to drop schemas or tables you didn't intend to.

It can also be useful for testing macros and other DBT configurations that are post-compilation but don't show up in the compiled SQL

[mikekaminsky]

So, I took a look at dbt/adapters/default/impl.py to see how hard this might be and I don't think I have enough context on why some things are implemented the way they are to take a stab at this myself without some guidance.

I can see that the add_query method is where the statements get executed (cursor.execute(sql, bindings) but there's a bunch of other stuff happening in here I don't understand (I suspect a lot of it is related to handling multiple connections).

In case it's helpful to see where my head is going, the way I've implemented this in the past is to have two functions for executing SQL. One for commands (no results returned) and one for queries (returning data) -- this way your read and write operations are split.

Then, for the dry-run mode, you can put some logic in the command-handling function that just logs the command instead of passing it to cur.execute(). Something like:

def run_command(sql):
    print(sql)
    if not DRY_RUN:
        self.cur.execute(sql)

def get_data(sql):
    print(sql)
    self.cur.execute(sql)
    result = cur.fetchall()
    return result

[hui-zheng]

yes, I vote to have the dry-run feature, which will be super helpful for debug and performance tuning

[thalesmello]

@cmcarthur What's the reasoning for this to be removed from the milestone?

[drewbanin]

Hi @thalesmello - this is a pretty involved change to dbt! Connor and I tried to prioritize it for the Wilt Chamberlain release (0.14.0), but we ended up deciding to cull it in favor of Archive-related functionality. This feature is definitely going to be a big improvement to dbt - I'm looking forward to re-prioritizing it!

[thalesmello]

Thanks for the explanation @drewbanin 👍

[christopher-tin]

+1 as well to adding a dry run feature such as a dbt compile --all command

[darrenhaken]

Any movement on this?

[giovanni-girelli-sdg]

Hello everyone!
Any progress on this? I think being able to see the actual sql that will be executed is quite the important feature.

[alepuccetti]

@giovanni-girelli-sdg To see the actual SQL that dbt will execute you can use dbt compile https://docs.getdbt.com/reference/commands/compile/.

[giovanni-girelli-sdg]

Hi @alepuccetti! thanks for the reply, but there you can only see the SELECT statement. What I'd like to see is the actual SQL that will run, such as a MERGE, INSERT or CREATE statement.

Edit at least it's what I can find. Is there a way to see the full materialization code?

Edit2: I had not seen the target/run folder, that's enough for debugging in DEV. I still think it would be great to be able to have a dry run mode where no sql is actually executed, but I can see how that would be much harder to do. Thanks for the great work!

[bhtucker]

@mikekaminsky you opened this on my birthday!

[davidsr2r]

+1 to this, especially if it allowed the calculation of the cost of the run, and allowed prevention of the run if the cost was too high unless a --force argument was used.

[y-tee]

+1

[the-davidsn]

+1

[fclesio]

+1

[giovanni-girelli-sdg]

The more I think about it, the less I think this is possible. There are way too many macros and materializations requiring active database querying to determine the flow - you can't keep it completely dry.

…

On Thu, May 27, 2021 at 4:40 PM Flavio Clesio ***@***.***> wrote: +1 — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#1059 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ANQZGB4JYSUUU5QJQSHCX6DTPZKV7ANCNFSM4F3KU4HQ> .

-- *Giovanni Girelli* Specialist Advanced Business Analytics SDG Consulting Italy Viale del Lavoro 33, 37135 Verona - Italy Mail : ***@***.*** - Mobile: +39 349 1045527 Website <http://www.sdggroup.com/it> | LinkedIn <https://www.linkedin.com/company/sdg-group> | Twitter <https://twitter.com/sdggroup> | YouTube <https://www.youtube.com/user/sdggroup> | Facebook <https://www.facebook.com/SDGGroup>

[rabidaudio]

For some reason when searching for this I found #281 but not this issue.

I'm posting about it here with the link so that a reference will show up on the old PR, in case someone else finds the old PR.