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

[the-davidsn]

+1

[fclesio]

+1

[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

[tomasoak]

+1 for dry run

[d1manson]

for those that haven't seen, in v1.8 there is the --empty flag which i believe does what's needed.
#8980

[oakromulo]

not really, it'll still attempt to create 0 rows views/tables in the target warehouse even though that might be highly undesirable depending on development constraints/policies

[iamtodor]

@oakromulo could you please elaborate on why it's still not an option? Is it because of performance issues - we waste time creating 0 rows views/tables or something else? Currently, my company is also thinking about adding dry-run as a part of our CI/CD and I stumbled upon your comment. I'd like to understand better your perspective

[oakromulo]

@oakromulo could you please elaborate on why it's still not an option? Is it because of performance issues - we waste time creating 0 rows views/tables or something else? Currently, my company is also thinking about adding dry-run as a part of our CI/CD and I stumbled upon your comment. I'd like to understand better your perspective

it's not an option in sensitive scenarios in which you deliberately don't want any tables/views/schemas being created or replaced at all but you would still like to see target/run code for it, i.e. the exact statements dbt would try to run against the target warehouse without actually running them

[asafalina]

+1 for dry run

[dbrtly]

Give us a `dbt compile` command that is equivalent to sdf compile​ and we don't ever need a dry run. Thanks, Daniel

…

________________________________ From: Romulo Carvalho ***@***.***> Sent: Monday, March 24, 2025 9:18:57 PM To: dbt-labs/dbt-core ***@***.***> Cc: Daniel Bartley ***@***.***>; Comment ***@***.***> Subject: Re: [dbt-labs/dbt-core] dbt should have a Dry-Run mode (Discussion #4456) @oakromulo<https://github.com/oakromulo> could you please elaborate on why it's still not an option? Is it because of performance issues - we waste time creating 0 rows views/tables or something else? Currently, my company is also thinking about adding dry-run as a part of our CI/CD and I stumbled upon your comment. I'd like to understand better your perspective it's not an option in sensitive scenarios in which you deliberately don't want any tables/views/schemas being created or replaced at all but you would still like to see target/run code for it, i.e. the exact statements dbt would try to run against the target warehouse without actually running them — Reply to this email directly, view it on GitHub<#4456 (reply in thread)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/ADLXPTKIN2NCT3GDQPR3LRD2V7LZDAVCNFSM6AAAAABZPPEGKCVHI2DSMVQWIX3LMV43URDJONRXK43TNFXW4Q3PNVWWK3TUHMYTENRQGAZTMMA>. You are receiving this because you commented.Message ID: ***@***.***>

[blevkovych]

• 1

[kraftaa]

I was desperately needing it, so I created a pretty simple bash script, which looks at the compiled models in target
(I do state comparison and can check only modified models),
then for each compiled model it creates file which changes all JOIN/FROM SELECT ... FROM database.schema.model_name into
SELECT * FROM database.schema.model_name limit 0
and run it agains the database very quickly due to limit 0 (it checks syntax/missed columns/wrong types and also it gets the duplicates in columns if any).
it runs in GHA and is very useful to check if it's going to break a run or not. Didn't figure out how to do it easier without real run.

would be great to have dry-run in dbt of course.

[morgan-dgk]

This sounds a lot like the --empty flag. Does this do what you're trying to achieve?

[kraftaa]

yes it does,
It's not exactly as --empty because in case of using --empty:

• it creates empty tables-views - I don't need it, I just need to check
• most important it doesn't work on {{ref('modelname')}} it would break on limit 0 added to the query - like it's not pulling stuff
  The caveat with my solution: I can't just create many new views at once and cross reference them - as they won't exist for the check, so I'm creating the bronze level first, then the silver one.

I'm looking at the idea of getting columns from compiled sqls for each model and compare them - but I don't have time for it now.

[morgan-dgk]

Seems like there are two distinct ideas of a dry run that folks are talking about here. Namely, one which validates query syntax and identifiers and another which would simply output the full DDL / DML statements produced by DBT.

Interestingly enough, the SQLAdapter base class defines a validate_sql method.

From docstring:

Submit the given SQL to the engine for validation, but not execution.

By default we simply prefix the query with the explain keyword and allow the
exceptions thrown by the underlying engine on invalid SQL inputs to bubble up
to the exception handler. For adjustments to the explain statement - such as
for adapters that have different mechanisms for hinting at query validation
or dry-run - callers may be able to override the validate_sql_query macro with
the addition of an __validate_sql implementation.

The Bigquery Adapter which our shop uses actually overrides this method to use the platform specific dry run flag when submitting a query with this method.

As far as I can tell though, this method is unused outside of the validate_sql macro defined in the global project installed with dbt.

[kraftaa]

Thank you for providing this info. I have Postgres. We are looking not only for syntax but also for the ability to check if the model uses existing columns from other models. (--compile only checks if the models exist and creates the full DDL statement).
Looking at validate_sql somehow I missed the adapters, thank you