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/.

[elyobo]

This misses stuff in the header.

[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.

[weichunnn]

@davidsr2r Don't think it's being implemented by dbt core yet or anytime soon, any solutions that you thought of since this issue was brought up the last time or any recommendations?

[y-tee]

+1

[DavidHuegel]

+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.

[ismailsimsek]

it will be very useful to get runtime sql with dbt compile without actually running it

[elyobo]

For what it's worth, this is what I came up with locally, which extracts the sql_header (which we're using to declare temporary functions) from the manifest and combines it with the compiled output in one file then copies that output to the clipboard for my convenience (pasting into BQ's UI, generally).

In this case

• the paths are hard coded but can presumably be generalised
• we're using poetry to run dbt
• jq is used to extract the entry from the manifest
• clipboard is an alias for xclip -selection clipboard which copies stdin or a provided file to the clipboard, works in linux, some sort of equivalent probably exists for other platforms

poetry run dbt compile --select your_model \
  && jq -r '.nodes."model.your_project.your_model".config.sql_header' < target/manifest.json > tmp.sql \
  && cat target/compiled/your_project/models/your_directory/your_model.sql >> tmp.sql \
  && clipboard tmp.sql

[elyobo]

(Not at all the same requirement, tbh, but useful if you want to extract the code that would be run for testing and tweaking before reimplementing in the model)

[ghost]

This would be useful for our dbt dry runner which evaluates wether a dbt project will be run. We had to get around not having the full run SQL by reimplementing bits of dbt (Wrapping views in CREATE OR REPLACE VIEW etc from the compiled_sql in the manifest.

[davidsr2r]

This tool is awesome, will be critical to our dbt development process & @connor-charles is a great maintainer, I'd highly recommend this to anyone considering using it.

[kelvinheng92]

Is this still being actively maintained?

[ghost]

Yes very much so, we are using it heavily production for all of our dbt projects at Auto Trader

[aaronsteers]

I've logged a similarly-named-but-different proposal proposal here, FYI:

• Schema-only dry-run feature - similar to a universal override of `materialize: view` or `LIMIT 0` #6989

Difference in that proposal is to still allow all the code to "execute" or "compile" in the SQL backend, but to do so without the cost of moving or transforming data.

[Gunnnn]

+1 for DryRun!

[xiangshiyin]

+1 to fully understand the plan details of the dbt run

[btello]

+1 for dry run. I hosed a prod database during a dbt migration today because dbt redshift 1.6 didn't render my custom schemas as expected. A command to validate my schemas are correct before building would have saved me a lot of time.

[zhangkuantian]

+1 for dry run.

[LexxaRRioo]

+1 for dry run