[Bug] "Schema does not exist" error during on-end-run hooks with dbt build

[ljhopkins2]

Is there an existing issue for this?

• I have searched the existing issues

Current Behavior

When using dbt build, the database is throwing a "schema does not exist" error during on-run-end hooks that invoke our grant_select_on_schemas macro. The error does not occur when using dbt run.

It seems like ...dbt_test__audit is being included in the schemas variable during build, even though I haven't used the --store_failures flag.

Expected Behavior

Consistent behavior between build & run; only schemas that are known to exist at the end of the run should be included in the schemas variable.

Steps To Reproduce

1. DROP all dev schemas
2. Ensure on-run-end hook is set to run a grant macro like

on-run-end:
- "{{ grant_select_on_schemas(schemas, '<groupname>') }}"
- "{{ grant_select_on_schemas(schemas, '<othergroupname>') }}"

{% macro grant_select_on_schemas(schemas, group) %}
  {% for schema in schemas %}
    grant usage on schema {{ schema }} to group {{ group }};
    grant select on all tables in schema {{ schema }} to group {{ group }};
    alter default privileges in schema {{ schema }}
        grant select on tables to group {{ group }};
  {% endfor %}
{% endmacro %}

2. Invoke dbt build on all or a subset of models; note the error during hook run

20:49:53 | Running 2 on-run-end hooks
20:49:53 | 1 of 2 START hook: ae_dw.on-run-end.0................................ [RUN]
Database error while running on-run-end
Encountered an error:
Database Error
  Schema "dbt_jhopkins_dbt_test__audit" does not exist.

3. Invoke dbt run on the same subset of models; note no error

21:12:18 |
21:12:18 | Running 2 on-run-end hooks
21:12:18 | 1 of 2 START hook: ae_dw.on-run-end.0................................ [RUN]
21:12:18 | 1 of 2 OK hook: ae_dw.on-run-end.0................................... [ALTER DEFAULT PRIVILEGES in 0.78s]
21:12:18 | 2 of 2 START hook: ae_dw.on-run-end.1................................ [RUN]
21:12:19 | 2 of 2 OK hook: ae_dw.on-run-end.1................................... [ALTER DEFAULT PRIVILEGES in 0.92s]

Relevant log output

No response

Environment

- OS: Mac 11.6
- Python: 3.9.2
- dbt: 0.21.0

What database are you using dbt with?

redshift

Additional Context

No response

[jtcohen6]

@ljhopkins2 Thanks for the detailed report!

I think this could be a very quick fix, in line with the previous changes in #3716 + #3922. We added an is_relational check for whether a node maps to a real database object. Much like how the letter y is sometimes a vowel and sometimes a consonant, tests are either relational (when --store-failures is enabled) or not (all other times).

So the condition in database_schema_set:

dbt-core/core/dbt/task/run.py

Lines 421 to 428 in 8a10a69

	database_schema_set: Set[Tuple[Optional[str], str]] = {
	(r.node.database, r.node.schema) for r in results
	if r.status not in (
	NodeStatus.Error,
	NodeStatus.Fail,
	NodeStatus.Skipped
	)
	}

Should become:

        database_schema_set: Set[Tuple[Optional[str], str]] = {
            (r.node.database, r.node.schema) for r in results
            if r.node.is_relational and r.status not in (
                NodeStatus.Error,
                NodeStatus.Fail,
                NodeStatus.Skipped
            )
        }

I confirmed that works locally. I think the test for it would be quite simple, too—just a quick log of schemas in an on-run-end hook, asserting that <schema>_dbt_test__audit is there when --store-failures is enabled, and isn't when it isn't.

Is that a fix you'd be interested in contributing? :)

[ljhopkins2]

@jtcohen6 Sounds fun. I'll give it a whirl.

[ljhopkins2]

FWIW, a workaround here is to run the build once with the --store-failures flag. That will create the test audit schema (so it will be present even if future builds don't include the flag), which can be dropped if desired later once #4077 is merged.