Github PR Temporary Schema not removed at PR close

[CraigWilson-ZOE]

Describe the bug

Integration with GitHub PR's for temporary schemas should clean up those schemas once PR is closed, currently this does not always happen

Steps To Reproduce

Using Snowflake DB and dbt 0.19.0

Enable GitHub PR integration with dbt, where schemas are created per PR. These schemas are supposed to be dropped, according to the documentation, at PR closing time. However, we have lots and lots of them left over and have to manually delete them.

This is from the dbt documentation (https://docs.getdbt.com/docs/dbt-cloud/using-dbt-cloud/cloud-enabling-continuous-integration-with-github#understanding-ci-in-dbt-cloud):
The temporary schema created for the run will remain in your warehouse until the PR is closed, allowing you to inspect the relations built by dbt Cloud. Once the PR is closed, dbt Cloud will delete the temporary schema

Expected behavior

All temporary PR schemas to be cleaned up after PR is closed

Screenshots and log output

If applicable, add screenshots or log output to help explain your problem.

System information

Which database are you using dbt with?

• postgres
• redshift
• bigquery
• snowflake
• other (specify: ____________)

The output of dbt --version:

0.19.0

The operating system you're using:
dbtcloud

The output of python --version:
N/A

Additional context

This was run in dbtcloud account and all configuration within there

[jtcohen6]

@CraigWilson-ZOE Have you opened a support ticket within dbt Cloud to report this behavior?

I totally believe you, and I know this has been a problem in the past. Unfortunately, I don't believe there is any code change we can make in this repository to ameliorate the problem. dbt Cloud support would be able to take a look and see what specifically is going on in your account.

Of course, you can leverage dbt (Core) constructs to automate the dropping of errant PR schemas:

{%- set get_pr_schemas_sql -%}
    show schemas like 'dbt_cloud_pr_%'
{%- endset -%}

{%- set results = run_query(get_pr_schemas_sql) -%}

{%- if execute -%}
    {%- for schema in results -%}

        {%- set drop_schema_sql -%}
            drop schema if exists {{ schema['name'] }}
        {%- endset -%}
        
        {{ drop_schema_sql }};
        {# do run_query(drop_schema_sql) #}

    {%- endfor -%}
{%- endif -%}

drop schema if exists DBT_CLOUD_PR_1862_323;
drop schema if exists DBT_CLOUD_PR_1862_339;
drop schema if exists DBT_CLOUD_PR_1862_344;
drop schema if exists DBT_CLOUD_PR_1862_350;

[CraigWilson-ZOE]

Thanks @jtcohen6 for the info. I will raise a ticket with them.

I actually created something similar as you have above to delete the schemas myself, just wanted to raise it in case there is something that could be done centrally.

[rrivera-ut]

@CraigWilson-ZOE what was your fix/resolution for this - I'm encountering something very similar and feel it's a configuration item on our end, but wanted to see how others have solved it.

[CraigWilson-ZOE]

@rrivera-usertesting I have since moved companies and we solved it using the above dropping of the schemas. At my current company we also have this issue and have had to do the same work around, schemas are just not dropped at PR close.

[stevenkoppenol]

I know this is an old PR, leaving a comment here if anybody else faces the same issue.

We are on BigQuery and we configured a project-wide table expiration policy to make sure old objects in dev & test are cleaned up after some time.

In dbt_project.yml:

models:
  +hours_to_expiration: "{{ (24 * 33) if target.name == 'prod' else ((24 * 7) if target.name == 'ci' else (24 * 3)) }}"

[zrichardlee]

@CraigWilson-ZOE Have you opened a support ticket within dbt Cloud to report this behavior?

I totally believe you, and I know this has been a problem in the past. Unfortunately, I don't believe there is any code change we can make in this repository to ameliorate the problem. dbt Cloud support would be able to take a look and see what specifically is going on in your account.

Of course, you can leverage dbt (Core) constructs to automate the dropping of errant PR schemas:

{%- set get_pr_schemas_sql -%}
    show schemas like 'dbt_cloud_pr_%'
{%- endset -%}

{%- set results = run_query(get_pr_schemas_sql) -%}

{%- if execute -%}
    {%- for schema in results -%}

        {%- set drop_schema_sql -%}
            drop schema if exists {{ schema['name'] }}
        {%- endset -%}
        
        {{ drop_schema_sql }};
        {# do run_query(drop_schema_sql) #}

    {%- endfor -%}
{%- endif -%}

drop schema if exists DBT_CLOUD_PR_1862_323;
drop schema if exists DBT_CLOUD_PR_1862_339;
drop schema if exists DBT_CLOUD_PR_1862_344;
drop schema if exists DBT_CLOUD_PR_1862_350;

Apologies for the noob question - but where would I put this set of jinja commands in my dbt project such that it's run regularly? I'm on dbt core.

[CraigWilson-ZOE]

Personally, I created a macro with the above code, and then call this via the dbt run-operation... command as part of my PR workflow in GitHub Actions.