Clean your warehouse of old and deprecated models

Show and Tell
9

On Redshift, the drop query wasn’t throwing an error but it also wasn’t dropping the relations. Once I wrapped the drop statement(s) in a begin/commit, it worked for me.

Sharing that section of my code FWIW.

...
{% set drop_query = ['begin;'] %}
{%- for item in to_delete %}
  {%- set drop_statement %}  drop {{ item[2] }} if exists "{{ item[0] }}"."{{ item[1] }}" cascade;{%- endset %}
  {%- do drop_query.append(drop_statement) %}
{%- endfor %} 
{%- do drop_query.append('commit;') %}
{%- set drop_query = drop_query|join('\n') %}
{%- do log(
    modules.datetime.datetime.now().strftime('%H:%M:%S') 
    ~ ' | Executing the following statements:',
    info=true) %}
{%- do log(drop_query, info=true) %}
{%- do run_query(drop_query) %}
...

Running with dbt=0.21.0
22:39:23 | Finding vestigial tables
22:39:24 | Executing the following statements:
begin;
  drop view if exists "schema"."vestigial_table_1" cascade;
  drop view if exists "another_schema"."vestigial_table_2" cascade;
commit;