Automatically Identify & Delete Orphaned Tables and Schemas — dbt macro

Orphaned tables — tables that were created at some point but are no longer tied to any active dbt models. Abandoned tables not only take up space but can also lead to confusion and inefficiencies when managing your data infrastructure. You’re not alone. Hunting these down manually? No way. Not with a database packed with hundreds or thousands of tables. 
We decided to develop a macro that could automate the process, making it easier for all of us to maintain a clean tables and schemas no longer managed by dbt.

Tested on #Snowflake and #Databricks + #GoogleCloud!

What this Macro Does?

The macro works by comparing the current structure of your dbt models with the actual tables in your database. It identifies tables that exist in the database but are not represented in your dbt models and generates DROP commands to remove these orphaned tables and, if needed, the entire schemas.

Before Using the Macro

1. Run a Full Refresh: Before using the macro, run a full refresh of your dbt models to make sure they accurately reflect the current state of your data warehouse. This ensures the macro doesn’t accidentally target tables that are still in use.
2. Check Your Connections: Make sure your database connection settings in dbt are correct and tested. The macro relies on these connections to compare your dbt models with what’s actually in your database.
3. Verify Model Consistency: Double-check that all dbt models are consistent and without errors. Any discrepancies could lead to incorrect identification of orphaned tables.
4. Understand the Parameters: Familiarize yourself with the macro’s parameters, like dry_run, tables_to_exclude, and schemas_to_exclude. These let you control what gets deleted and what doesn’t. Set these up thoughtfully to avoid accidentally deleting something important.
5. Dry Run First: To avoid any nasty surprises, always start with the dry_run mode set to true. This logs what the macro would delete, giving you a chance to review everything before it actually happens. It’s your safety net against accidental deletions.

Key Parameters for Customization

• dry_run (default: ‘true’): Logs the DROP commands without executing them, allowing you to review what will be deleted. Setting this to ‘false’ will execute the deletion.
• tables_to_exclude: Allows you to specify tables that should not be deleted, even if they aren’t tied to a current dbt model.
• delete_custom_schemas (default: ‘true’): Deletes entire schemas that aren’t managed by dbt, including all the tables within them. Setting this to ‘false’ will only remove tables within dbt-managed schemas.
• schemas_to_exclude: Provides a list of schemas that should be excluded from deletion.

The Code

Here’s the code for the macro that I’ve been using to clean up orphaned tables and schemas:

-- MACRO DESCRIPTION
-- This macro is designed to clean up orphaned tables and schemas from your database that are no longer managed by dbt.
-- It offers flexibility to either log the drop commands for review or execute them directly.

{%- macro cleanup(objects_type, dry_run=true, tables_to_exclude=[''], delete_custom_schemas=false, schemas_to_exclude=['']) -%}

-- PARAMETERS:
-- dry_run (default: 'true'): If set to 'true', gitthe macro logs the drop commands without executing them. If set to 'false', the macro executes the drop commands.
-- tables_to_exclude: A list of table names to exclude from being dropped.
-- delete_custom_schemas (default: 'false'): If set to 'true', the macro will drop schemas that are not managed by dbt. If set to 'false', it will only drop orphaned tables.
-- schemas_to_exclude: A list of schema names to exclude from being dropped.
-- objects_type: Mandatory parameter, A list of object type to be dropped, available objects types are VIEW, BASE TABLE, MANAGED 

-- TODO: objects_type  if not defined DROP ALL object types

    {% if not objects_type %}
        {% do exceptions.raise_compiler_error('objects_type not provided') %}
    {% endif %}

    {%- set schemas_to_exclude = schemas_to_exclude + ['monitoring', 'information_schema'] -%}
    {%- set tables_to_exclude = tables_to_exclude + ['config_table','mapping_table'] -%}
    {%- if execute -%}

        -- Create empty dictionary that will contain the hierarchy of the models in dbt
        {%- set current_model_locations = {} -%}

        -- Insert the hierarchy database.schema.table in the dictionary above
        {%- for node in graph.nodes.values() | selectattr("resource_type", "in", ["model", "seed", "snapshot"]) -%}

            {% if target.type == 'bigquery' %}
              {%- set database_name = node.database -%}
            {%- else -%}
              {%- set database_name = node.database.upper() -%}
            {% endif %}
            {%- set schema_name = node.schema.upper() -%}
            {%- set table_name = node.alias if node.alias else node.name -%}

            -- Add db name if it does not exist in the dict
            {%- if not database_name in current_model_locations -%}
                {% do current_model_locations.update({database_name: {}}) -%}
            {%- endif -%}

            -- Add schema name if it does not exist in the dict
            {%- if not schema_name in current_model_locations[database_name] -%}
                {% do current_model_locations[database_name].update({schema_name: []}) -%}
            {%- endif -%}

            -- Add the tables for the db and schema selected
            {%- do current_model_locations[database_name][schema_name].append(table_name.upper()) -%}

        {%- endfor -%}

        {{ log(current_model_locations) }}
        {%- do log(current_model_locations, True) -%}

    {%- endif -%}

    {% set target_table %}
      {% if target.type == 'bigquery' %}
          {{ target.project }}.{{ target.dataset }}.INFORMATION_SCHEMA.TABLES
      {%- else -%}
        {{ target.database }}.INFORMATION_SCHEMA.TABLES
      {% endif %}
    {% endset %}

    -- Query to retrieve the models to drop
    {%- set cleanup_query -%}

        WITH models_to_drop AS (
            {%- for database in current_model_locations.keys() -%}

                SELECT
                    CASE
                        WHEN table_type not in (
                            {%- for object_type in objects_type -%}
                            '{{ object_type|upper }}'
                            {%- if not loop.last %} , {%- endif %}
                            {%- endfor %} ) THEN NULL
                        WHEN table_type in ('BASE TABLE', 'MANAGED') THEN 'TABLE'
                        WHEN table_type = 'VIEW' THEN 'VIEW'
                        ELSE NULL
                    END AS relation_type,
                    table_catalog,
                    table_schema,
                    table_name,
                    table_catalog || '.' || table_schema || '.' || table_name as relation_name
                FROM {{ target_table }}
                WHERE 1=1

                    AND LOWER(table_schema) IN ('{{ "', '".join(current_model_locations[database].keys())|lower }}')
                    AND NOT (
                        {%- for schema in current_model_locations[database].keys() -%}
                            LOWER(table_schema) = LOWER('{{ schema }}') AND LOWER(table_name) IN ('{{ "', '".join(current_model_locations[database][schema])|lower }}')
                            {% if not loop.last %} OR {% endif %}
                        {%- endfor %}
                    )


-- Exclude tables containing any of the keywords
                    AND NOT (
                        {%- for keyword in tables_to_exclude -%}
                            LOWER(table_name) IN ('{{ keyword|lower }}')
                            {% if not loop.last %} OR {% endif %}
                        {%- endfor %}
                    )


                {% if not loop.last -%} UNION ALL {%- endif %}

        ),

        drop_schemas AS (
            SELECT DISTINCT table_catalog, table_schema
            FROM {{ target_table }}
            WHERE 1=1
            AND LOWER(table_schema) NOT IN ('{{ current_model_locations[database].keys() | join("', '") | lower }}')
            AND LOWER(table_schema) NOT IN ('{{ schemas_to_exclude | join("', '") | lower }}')
-- Exclude schemas from macro settings
                AND NOT (
                    {%- for schema in schemas_to_exclude -%}
                        LOWER(table_schema) IN ('{{ schema|lower }}')
                        {% if not loop.last %} OR {% endif %}
                    {%- endfor %}
                )
            {%- endfor -%}
        )

        -- Create the DROP statments to be executed in the database
                SELECT 'DROP ' || relation_type || ' IF EXISTS ' || table_catalog || '.' ||table_schema || '.' || table_name || ';' AS drop_commands
                FROM models_to_drop
                WHERE relation_type IS NOT NULL

                {% if delete_custom_schemas == true %}
                UNION ALL

                SELECT 'DROP SCHEMA IF EXISTS ' || table_catalog || '.' || table_schema || ' CASCADE;' AS drop_commands
                FROM drop_schemas
                {% endif %}

    {%- endset -%}


    {{ log('\nGenerating cleanup queries.\n', info=True) }}

    -- Execute the DROP statments above
    {%- if dry_run == false -%}
        {%- set drop_commands = run_query(cleanup_query).columns[0].values() -%}
        {%- for drop_command in drop_commands -%}
            {%- do run_query(drop_command) -%}
            {%- do log(drop_command, True) -%}
        {%- endfor -%}
    {%- else -%}
        -- Log the drop commands in dry run mode
        {%- set drop_commands = run_query(cleanup_query).columns[0].values() -%}
        {%- for drop_command in drop_commands -%}
            {%- do log(drop_command, True) -%}
        {%- endfor -%}
    {%- endif -%}


{%- endmacro -%}