How to use a dbt macro to safely delete orphaned tables

Use Case

Let’s imagine a situation where we work for the finance department and we have seven models that are run for them on a regular basis.

We can see these seven models in our current dbt project, as well as their materialized forms in Snowflake in the schema: FINANCE:

current dbt project

Snowflake FINANCE schema

We agreed with our stakeholders that these three models are no longer needed and can be removed (revenue_demo, invoice_adhoc_analysis, invoice_old_system). However, when models are removed from the dbt project, this does not mean they are automatically deleted from the warehouse. They actually remain there untouched until you delete them manually. This is where our macro comes into play.

deleted models in dbt project

Now we can use our macro to delete objects from the database by running this command.

Note: object_type usually refers to the type of object stored in the database.

dbt run-operation cleanup --args '{objects_type: ["BASE TABLE", "VIEW"]}'

dry_run = true is default setting

Make sure that, the first time you run it, the dry_run parameter in the macro code is set to true.It is set to true by default, but it’s better to double-check on your end too.

When dry_run is set to true, the macro will only list all database objects that do not have a corresponding dbt resource and generate DROP commands for them in the logs (your terminal). These commands are not executed, so you can review what dbt would do before making any changes.

drop commands

Do not forget that the macro is fully parameterized, and you can define objects that should always be skipped from deletion, even if they do not have a related dbt resource.

If you want to delete all three of these models from dbt project but still keep one of them in your warehouse, you can use this parameter to adjust it: tables_to_exclude.

dbt run-operation cleanup --args '{
  "objects_type": ["BASE TABLE", "VIEW"],
  "tables_to_exclude": ["revenue_demo"]
}'

dbt will generate the drop command only for two models, and the one in tables_to_exclude will be excluded.

If you are happy with the result obtained from running the macro in dry run mode, you can proceed with the actual deletion.

This is how our Snowflake looks now. We can see multiple schemas there as well. The FINANCE schema contains the seven models mentioned above.

Snowflake schemas

The final dbt macro command can then look like this.

You can see that we adjusted many parameters to demonstrate everything the macro can do.

dbt run-operation cleanup --args '{
 "objects_type": ["BASE TABLE", "VIEW"],
 "dry_run": false,
 "delete_custom_schemas": true,
 "schemas_to_exclude": [
 "ELEMENTARY_MONITORING",
 "ELEMENTARY_DEMO",
 "DATA_SAMPLE",
 "FINANCE_BACK_UP",
 "<none selected>"
 ],
 "tables_to_exclude": ["revenue_demo"]
}'

We can see that dry_run is set to false. This means that dbt will not only generate drop commands but will also execute them. It will also delete schemas and all tables inside them that are not managed by dbt (delete_custom_schemas, which is false by default, is now set to true). Some schemas and tables are excluded from deletion and are specified in schemas_to_exclude and tables_to_exclude. 

So, you can see that in the Finance folder we initially had seven models. We then deleted three of them in the dbt project, and while running the final dbt drop command, we excluded one model, revenue_demo, from deletion. In the end, we ended up with five models in the FINANCE schema.

Also, the FINANCE_OLD_SYSTEM schema was dropped along with all its tables because it was not excluded in the dbt macro parameter schemas_to_exclude.

The information_schema was not deleted because it is specified in the dbt macro code to remain.

Executed commands

Snowflake after deletion happened

This part of the code ensures that the information_schema remains untouched. For any schema not created via dbt that we never want deleted (or have to remember while running this macro) we can adjust this specific part of the macro code accordingly.

%- set schemas_to_exclude = schemas_to_exclude + ['monitoring', 'information_schema'] -%}

Kateřina Protivenskyi

Katerina is a Senior Data Engineer at Revolt BI with a strong focus on modern data platforms and data architecture.

---

She has experience designing and implementing scalable data models, building ELT pipelines in dbt, and orchestrating workflows with tools like Apache Airflow. She specializes in data transformation and integration, ensuring data quality and consistency, and automating data processes. Katerina helps companies work with data effectively, set up robust data infrastructure, and prepare their data for advanced analytics and AI.

Want a cleaner dbt project — and a cheaper warehouse?

We help data teams modernize their dbt setup, cut warehouse costs, and ship trustworthy models faster. Tell us where you’re stuck.