drop dbt Cloud pull request schemas to cleanup your snowflake database organization

drop_dbt_cloud_pull_request_schemas.sql

--Run this command in your terminal: dbt run-operation drop_dbt_cloud_pull_request_schemas
--Write this as a file in your dbt project directory: macros/drop_dbt_cloud_pull_request_schemas.sql
--Original Source: https://github.com/randypitcherii/hashmap_randy_pitcher_workspace/blob/master/dbt/macros/cleanup/drop_old_relations.sql
{% macro drop_dbt_cloud_pull_request_schemas() %}

  {% set cleanup_query %}

      -- CTE to query the snowflake information schema to get all the tables and views in scope for cleanup
      WITH 
      
      MODELS_TO_DROP AS (
        SELECT
          CASE 
            WHEN TABLE_TYPE = 'BASE TABLE' THEN 'TABLE'
            WHEN TABLE_TYPE = 'VIEW' THEN 'VIEW'
          END AS RELATION_TYPE,
          CONCAT_WS('.', TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME) AS RELATION_NAME
        FROM 
          {{ target.database }}.INFORMATION_SCHEMA.TABLES
        WHERE 
          TABLE_SCHEMA  like '%DBT_CLOUD_PR_%' --default schema prefix created by dbt Cloud
      )

      --create the actual drop statement
      SELECT 
        'DROP ' || RELATION_TYPE || ' ' || RELATION_NAME || ';' as DROP_COMMANDS
      FROM 
        MODELS_TO_DROP

  {% endset %}
  
  --store the drop SQL statements as a list of values
  {% set drop_commands = run_query(cleanup_query).columns[0].values() %}

  {% if drop_commands %}
    {% for drop_command in drop_commands %}
      {% do log(drop_command, True) %} --display logs for each drop sql statement
      {% do run_query(drop_command) %}
    {% endfor %}
  {% else %}
    {% do log('No relations to clean.', True) %}
  {% endif %}
  
{% endmacro %}