DBT Temp Tables Left If Model Succeeds but Post-Hook Fails

[jrandrews]

Issue

If a model succeeds but the post-hook for the model fails, temp tables get left in the database until the next time the given model runs successfully.

Issue description

dbt run or dbt run --models model_with_bad_posthook. If the attached code is run, two tables will appear in the database -- DIM_DATE and DIM_DATE__DBT_BACKUP.

Results

I would expect to not see temp tables persist in the database in the case of a post-hook failure.

System information

The output of dbt --version:
installed version: 0.12.2
latest version: 0.13.0

The operating system you're running on:
Mac OS X Mojave - same problem occurs through dbt cloud.

The python version you're using (probably the output of python --version)
Python 2.7.10 -- but also occurs on dbt cloud

Steps to reproduce

Run the model and the associated post-hooks attached. Had to rename files with extension of ".txt" as github wouldn't allow upload of .sql files so will need to rename them back to .sql.

dim_date.txt
add_not_null_constraints.txt

[drewbanin]

@jrandrews thanks for the report! Did this issue cause your dbt runs to fail? Or was the problem that there were some orphaned __dbt_backup tables in the database that cluttered up your schema? If it doesn't lead to operational errors, then we might kick this out of 0.13.1 and into the 0.14.0 (Wilt Chamberlain) release.

We should fix this (for Snowflake) by using create or replace table instead of creating a temp table and then swapping. See #1101 for more information. We'll need to address this for other adapters on a case-by-case basis.

[jrandrews]

Hello @drewbanin . This issue only occurred when we aborted a dbt run mid-run, e.g. by Ctrl-C so by definition it couldn't cause the rest of the run to fail because we had already aborted. The issue was orphaned tables like __dbt_backup cluttering up the schema. Did not seem to lead to operational errors although potentially confusing for users with access to the schema. Not a big deal if delayed.

[drewbanin]

Cool! Check out this PR which implents the create or replace table approach described above: #1409

This should obviate the need for those backup tables. Thanks again for the report