CI job failed due to non-exist schema

Help
1

I set up an CI job in my production environment. See this picture

image
image1297×1197 84.8 KB

However, the dbt Cloud run failure with the following message

15:09:43
dbt command failed15:09:43  Finished running 4 table models, 8 data tests in 0 hours 0 minutes and 34.15 seconds (34.15s).
15:09:43  Encountered an error:
Database Error
  002003 (02000): SQL compilation error:
  Schema 'REPORTING.DBT_CLOUD_PR_744164_671' does not exist or not authorized.

Does anybody know which setting was incorrect?
Is it related to the target name that I set?

2

This is from me <@U06146LCY9J>

Note: @Adrian originally posted this reply in Slack. It might not have transferred perfectly.

3

Where are you expecting the ci checks to be built? Is that the right DB?

Note: @Jeff Green (CHG Healthcare) originally posted this reply in Slack. It might not have transferred perfectly.

4

Yes the right DB is REPORTING . So the DB is correct, but schema is not

Note: @Adrian originally posted this reply in Slack. It might not have transferred perfectly.

5

By default, dbt is trying to create that schema because that’s what the default naming scheme is for CI checks. Do you want it to use the prod schema names? If so, you’ll need to intercept the default generate_custom_schema macro: https://docs.getdbt.com/docs/build/custom-schemas#understanding-custom-schemas

Note: @Jeff Green (CHG Healthcare) originally posted this reply in Slack. It might not have transferred perfectly.

6

I modified the generate_schema_name.sql in macro folder
from


    {%- set default_schema = target.schema -%}
    {%- if custom_schema_name is none -%}

        {{ default_schema }}

    {%- else -%}

        {{ custom_schema_name | trim }}

    {%- endif -%}

{%- endmacro %}```
into
```{% macro generate_schema_name(custom_schema_name, node) -%}

    {%- set default_schema = target.schema -%}
    {%- if custom_schema_name is none -%}

        {{ generate_schema_name_for_env(custom_schema_name, node) }}

    {%- else -%}

        {{ custom_schema_name | trim }}

    {%- endif -%}

{%- endmacro %}```
the CI job run but returned
```dbt build --select state:modified+

14:22:06  Running dbt...
14:22:07  Unable to do partial parsing because saved manifest not found. Starting full parse.
14:22:16  Found 582 models, 12 snapshots, 5 seeds, 1 operation, 1597 data tests, 387 sources, 562 macros
14:22:16  The selection criterion 'state:modified+' does not match any enabled nodes
14:22:16  The selection criterion 'state:modified+' does not match any enabled nodes
14:22:16  The selection criterion 'state:modified+' does not match any enabled nodes
14:22:16
14:22:16  Nothing to do. Try checking your model configs and model specification args```
So I don’t think it’s correct yet because it didn’t compare the PR with the old model.
What do you think?

<sub>Note: `@Adrian` originally [posted this reply in Slack](https://getdbt.slack.com/archives/CBSQTAPLG/p1730903434410329?thread_ts=1730832526.422859&cid=CBSQTAPLG). It might not have transferred perfectly.</sub>