Drop previous external tables prior to running tests

[jeremyyeo]

Describe the bug

On a recent PR #148 - there was a bug but the CI tests was passing because even if there's an error with our dbt run-operation ... commands, there still exist the same table that was created in previous CI tests - this causes the final dbt test command to pass and thus the CI test as a whole is passing.

dbt-external-tables/run_test.sh

Lines 37 to 40 in e38cc50

	dbt run-operation prep_external --target $1
	dbt run-operation stage_external_sources --var 'ext_full_refresh: true' --target $1
	dbt run-operation stage_external_sources --target $1
	dbt test --target $1

Steps to reproduce

Expected results

Actual results

Screenshots and log output

https://app.circleci.com/pipelines/github/dbt-labs/dbt-external-tables/229/workflows/7fc76d6f-28eb-48fc-ba82-77f77ba33a97/jobs/1052

14:30:55  Running with dbt=1.2.0-rc1
14:30:55  No prep necessary, skipping
14:30:58  Running with dbt=1.2.0-rc1
14:30:59  Unable to do partial parsing because config vars, config profile, or config target have changed
14:31:00  1 of 4 START external source dbt_external_tables_integration_tests_bigquery.people_csv_unpartitioned
14:31:01  1 of 4 (1) create or replace external table `************`.`dbt_external_tables_integration...  
14:31:02  Encountered an error while running operation: Database Error
  Syntax error: Unexpected "<" at [6:17]
14:31:05  Running with dbt=1.2.0-rc1
14:31:05  Unable to do partial parsing because config vars, config profile, or config target have changed
14:31:07  1 of 4 START external source dbt_external_tables_integration_tests_bigquery.people_csv_unpartitioned
14:31:07  1 of 4 SKIP
14:31:07  2 of 4 START external source dbt_external_tables_integration_tests_bigquery.people_csv_partitioned
14:31:08  2 of 4 SKIP
14:31:08  3 of 4 START external source dbt_external_tables_integration_tests_bigquery.people_csv_schema_auto_detect
14:31:08  3 of 4 SKIP
14:31:08  4 of 4 START external source dbt_external_tables_integration_tests_bigquery.people_csv_override_uris
14:31:08  4 of 4 SKIP
14:31:11  Running with dbt=1.2.0-rc1
14:31:11  Found 0 models, 4 tests, 0 snapshots, 0 analyses, 519 macros, 0 operations, 1 seed file, 5 sources, 0 exposures, 0 metrics
14:31:11  
14:31:12  Concurrency: 1 threads (target='bigquery')
14:31:12  
14:31:12  1 of 4 START test dbt_utils_source_equality_bigquery_external_people_csv_override_uris_id__first_name__last_name__email__ref_people_  [RUN]
14:31:16  1 of 4 PASS dbt_utils_source_equality_bigquery_external_people_csv_override_uris_id__first_name__last_name__email__ref_people_  [PASS in 4.42s]
14:31:16  2 of 4 START test dbt_utils_source_equality_bigquery_external_people_csv_partitioned_id__first_name__last_name__email__ref_people_  [RUN]
14:31:18  2 of 4 PASS dbt_utils_source_equality_bigquery_external_people_csv_partitioned_id__first_name__last_name__email__ref_people_  [PASS in 1.75s]
14:31:18  3 of 4 START test dbt_utils_source_equality_bigquery_external_people_csv_schema_auto_detect_id__first_name__last_name__email__ref_people_  [RUN]
14:31:20  3 of 4 PASS dbt_utils_source_equality_bigquery_external_people_csv_schema_auto_detect_id__first_name__last_name__email__ref_people_  [PASS in 2.08s]
14:31:20  4 of 4 START test dbt_utils_source_equality_bigquery_external_people_csv_unpartitioned_id__first_name__last_name__email__ref_people_  [RUN]
14:31:22  4 of 4 PASS dbt_utils_source_equality_bigquery_external_people_csv_unpartitioned_id__first_name__last_name__email__ref_people_  [PASS in 1.64s]
14:31:22  
14:31:22  Finished running 4 tests in 0 hours 0 minutes and 10.13 seconds (10.13s).
14:31:22  
14:31:22  Completed successfully
14:31:22  
14:31:22  Done. PASS=4 WARN=0 ERROR=0 SKIP=0 TOTAL=4
CircleCI received exit code 0

System information

The contents of your packages.yml file:

Which database are you using dbt with?

• redshift
• snowflake
• other (specify: ____________)

The output of dbt --version:

<output goes here>

The operating system you're using:

The output of python --version:

Additional context

Couple of options I suppose:

1. We can probably make sure to drop table if exists as a run-operation prior to the stage_external_sources op.
2. Drop the external tables AFTER the test (?).
3. Add some "dynamic" strings to the external table name (e.g. the PR number).

[github-actions]

This issue has been marked as Stale because it has been open for 180 days with no activity. If you would like the issue to remain open, please comment on the issue or else it will be closed in 7 days.