When we create a model in a dbt project and execute it, a model is materialized in our target schema. If we later rename the model in our dbt project and execute it again, the old model is technically removed from our dbt project, but the old materialization persists in our Data Warehouse (DWH). Essentially, old models will remain in our target schema, consuming space in our DWH.

As a result, our target schema ends up with multiple deprecated tables and views, resources we no longer need. It’s a good idea to remove them because they consume memory space in our DWH solution, incurring unnecessary costs. We can efficiently clean our target schema using custom macros and the run-operation command.

The following is a macro that accomplishes this task. It is a version of dan’s answer on dbt’s Discourse page “Clean your warehouse of old and depricated models”. This version has been modified to work when using the dbt-synapse adapter.

macro drop_old_relations adapted for the context of Azure Synapse

In addition to the DWH context-specific commands, we have added square brackets to the relation’s name, in case the name contains white space.

For consistency, we suggest placing the above macro in the dbt project’s macros/ folder under the name drop_old_relations.sql. The file’s name can be of your choice, but ensure that the macro’s name and the file’s name (without an extension) match.

To clean the target schema (the target dictated by our current profile), we run the following from the command line:

dbt run-operation drop_old_relation

A dry run is also available, allowing us to preview tables and views that would potentially be dropped without actually executing the operation:

dbt run-operation drop_old_relation --args '{dry_run = true}'

Happy querying.

Cleaning Up Deprecated Models using dbt in Azure Synapse
Older post

Top 10 Books Read in 2023

Selected quotes from what I considered were the best books I read/listened to in 2023.

Newer post

Working with dbt in VS Code

A brief description of features from CSV Rainbow and dbt Power User, both extensions for VS Code

Cleaning Up Deprecated Models using dbt in Azure Synapse