One "on-run-end hook" is failing when running anomaly detection tests

[thisiscab]

Describe the bug I'm having a problem where on on-run-end fails during dbt test command related to freshness anomalies.

Error:

on-run-end failed, error:
 002003 (42S02): SQL compilation error: Object '_REDACTED_.ELEMENTARY_DBT__REDACTED__PRODUCTION.TEST_F11494FC03_ELEMENTARY_ALL_COLUMNS_ANOMALIES_DIM_FACEBOOK_ADS_ACCOUNTS___METRICS__TMP_20240125152310704561' does not exist or not authorized.

Full commands:

export DBT_ELEMENTARY_ENABLED=true;
dbt --log-format=json source freshness;
edr run-operation upload-source-freshness --project-dir .;
dbt --log-format=json seed;
dbt --log-format=json run -x;
dbt --log-format=json test;
edr monitor -st "$SLACK_TOKEN" -ch "$MONITORING_DEFAULT_SLACK_CHANNEL" --env prod --select resource_types:model --group-by table;

This is causing Elementary to never run any other on-run-end hooks during tests which makes edr report not update accordingly. We use day-back 7 days and it's now displaying "No test results" when loading the report, implying that this has been broken for at least 7 days.

The "Model runs" section of the report are still updating, which is good, but nothing else.

More information:

• The monitor outputs to Slack when models are failing. Confirming that this piece works.
• The Snowflake users has the relevant permissions: CREATE SCHEMA, SELECT - FUTURE TABLE, SELECT - FUTURE VIEW, USAGE, USAGE - FUTURE SCHEMA
• Based on Snowflake documentation, "Note that creating a temporary table does not require the CREATE TABLE privilege on the schema in which the object is created."

Here is a redacted version of the query that it tries to run: https://gist.github.com/thisiscab/d7b7f60d3ca2fde71947d2e561328a62

When I copy the query that failed, use the same user with the same role, it succeeds: Table "DATA_MONITORING_METRICS__tmp_20240125153410135928" successfully created.

It feels like there is some kind of chicken-egg problem.

To Reproduce I'm unclear how to explain how to reproduce it.

Expected behavior The hook shouldn't be failing and I should see test data updated in my elementary report.

Screenshots If applicable, add screenshots to help explain your problem.

Environment (please complete the following information):

Versions: EDR=0.13.x Elementary's dbt package=0.13.x dbt-snowflake=1.7.x

Additional context

• Original Slack Issue: https://elementary-community.slack.com/archives/C02CTC89LAX/p1700859615551049
• Similar issue: https://elementary-community.slack.com/archives/C02CTC89LAX/p1692603763973949