Failure in creating table "int_all_graph_resource" after creating an exception for tests

[mairamelo26]

Describe the bug

I am using this documentation in order to setup an exception for a source table that I wish not to have a primary_key_missing test. However after doing that and running the following command:

dbt build --select package:dbt_project_evaluator dbt_project_evaluator_exceptions

I guet this error in the logs and I can't bypass that:

21:31:14  17 of 64 ERROR creating sql table model dbt_dev_project_evaluator.int_all_graph_resources  [ERROR in 5.54s]
[all the rest is with SKIP]
21:31:36  Finished running 25 table models, 16 view models, 1 seed, 22 data tests, 6 project hooks in 0 hours 1 minutes and 21.86 seconds (81.86s).
21:31:37  
21:31:37  Completed with 1 error and 0 warnings:
21:31:37  
21:31:37    Database Error in model int_all_graph_resources (models/marts/core/int_all_graph_resources.sql)
  column name "is_" is duplicated
  compiled code at target/run/dbt_project_evaluator/models/marts/core/int_all_graph_resources.sql
21:31:37  
21:31:37  Done. PASS=16 WARN=0 ERROR=1 SKIP=47 TOTAL=64

I have all the dependencies up to date and the only alteration I've made is what is instructed in the documentation "Configuring exceptions to the rules" mentioned above.

Steps to reproduce

I have two new directories in my repository (under the root of the project):

├── tests
│   └── testing.yml
├── models
├── seeds
│   └── exceptions.csv

Inside testing.yml I have:

version: 2

models:
  - name: fct_missing_primary_key_tests
    description: Models that will not be tested for primary key.
    tests:
      - dbt_utils.is_empty:
          where: "table_name != 'my_table_source'"

Inside exceptions.csv there's:

resource_name,resource_type,rule,reason
my_table_source,source,primary_key_missing,This source does not require a primary key test.

And I also updated dbt_project.yml with this:

vars:
  dbt_project_evaluator:
    enforced_primary_key_node_types:
      - model
      - snapshot
      - seed
    primary_key_test_macros:
      - dbt_utils.unique_combination_of_columns
      - dbt_utils.not_null

And then I run the command below:

dbt build --select package:dbt_project_evaluator dbt_project_evaluator_exceptions

Expected results

I was hoping that the output would be without errors.

Actual results

I actually get the error below:

Database Error in model int_all_graph_resources (models/marts/core/int_all_graph_resources.sql)
  column name "is_" is duplicated
  compiled code at target/run/dbt_project_evaluator/models/marts/core/int_all_graph_resources.sql

Screenshots and log output

Paste above

System information

The contents of your packages.yml file:

Packages:
  - package: calogica/dbt_expectations
    version: 0.10.3
  - package: elementary-data/elementary
    version: 0.16.0
  - package: dbt-labs/codegen
    version: 0.12.1
  - package: dbt-labs/audit_helper
    version: 0.12.0
  - package: dbt-labs/dbt_project_evaluator
    version: 0.14.3
  - package: dbt-labs/redshift
    version: 0.9.0
  - package: dbt-labs/dbt_utils
    version: 1.3.0
  - package: calogica/dbt_date
    version: 0.10.1

Which database are you using dbt with?

• [ ] postgres
• [X] redshift
• [ ] bigquery
• [ ] snowflake
• [ ] trino/starburst
• [ ] other (specify: ____)

The output of dbt --version:

$ dbt --version
Core:
  - installed: 1.8.7
  - latest:    1.8.8 - Update available!

  Your version of dbt-core is out of date!
  You can find instructions for upgrading here:
  https://docs.getdbt.com/docs/installation

Plugins:
  - postgres: 1.8.2 - Up to date!
  - redshift: 1.8.1 - Up to date!

Additional context

Not at this moment

Are you interested in contributing the fix?

Yes please

[b-per]

Did you provide your own version for the variable primary_key_test_macros and if so, could you please what you provided?

[mairamelo26]

Hi @b-per thanks for your reply. I've included this in my dbt_project.yml under "vars":

vars:
  dbt_project_evaluator:
    enforced_primary_key_node_types:
      - model
      - snapshot
      - seed
    primary_key_test_macros:
      - dbt_utils.unique_combination_of_columns
      - dbt_utils.not_null

[b-per]

primary_key_test_macros should be a list of list and not just a list

Can you try one of the below:

    primary_key_test_macros: 
     - 
        - "dbt_utils.unique_combination_of_columns" 
        - "dbt_utils.not_null"

or

    primary_key_test_macros: 
     - ["dbt_utils.unique_combination_of_columns", "dbt_utils.not_null"]

Which should be equivalent

[mairamelo26]

Sure! Let's try

[mairamelo26]

Hi @b-per ,

Ok something funny seems to be happening. This is the error that I get when I run the dbt build command again. This is the command that I run: dbt build --select package:dbt_project_evaluator dbt_project_evaluator_exceptions

And this is the error I got just now:

1:45:19  37 of 62 ERROR creating sql view model dbt_project_evaluator.fct_missing_primary_key_tests  [ERROR in 4.21s]
[...]
11:46:03    Database Error in model fct_missing_primary_key_tests (models/marts/tests/fct_missing_primary_key_tests.sql)
  syntax error at or near "model" in context "resource_type in
      (
          '['model", at line 11, column 12
  compiled code at target/run/dbt_project_evaluator/models/marts/tests/fct_missing_primary_key_tests.sql
11:46:03  

This is the DDL of the view before running the command:

CREATE OR REPLACE VIEW fct_missing_primary_key_test
AS SELECT final.resource_name, final.resource_type, final."model_type", final.is_primary_key_tested, final.number_of_tests_on_model, final.number_of_constraints_on_model
   FROM ( SELECT tests.resource_name, tests.resource_type, tests."model_type", tests.is_primary_key_tested, tests.number_of_tests_on_model, tests.number_of_constraints_on_model
           FROM ( SELECT int_model_test_summary.resource_name, int_model_test_summary.resource_type, int_model_test_summary."model_type", int_model_test_summary.is_primary_key_tested, int_model_test_summary.number_of_tests_on_model, int_model_test_summary.number_of_constraints_on_model
                   FROM dbt_project_evaluator.int_model_test_summary
                  WHERE int_model_test_summary.resource_type::text = 'model'::text) tests
          WHERE NOT tests.is_primary_key_tested) final;

This is after:

CREATE OR REPLACE VIEW fct_missing_primary_key_test
AS Not a view;

What could be happening?

[mairamelo26]

There were some mistakes that I needed to fix to stop having the create table error:

Mistake 1: The documentation says that the seed dbt_project_evaluator_exceptions inside dbt_project.yml should be set as false, but in fact that prevents the build command to run. So I changed to True.

Mistake 2: I believe that the seed documentation could include some more examples so we can maybe be able to better create it ourselves according to our needs. In my case it was a source table that needed to be excluded from the fct_missing_primary_key_test. Maybe whoever gets this on their search learns from this mistake on how not to create the seed properly.

Mistake 3: Fixing column values inside the exception seed, especially the: fct_name: The actual fact table is the actual rule that we want the exception do be placed. The only possible values here are listed inside the rule list. Which in my case was "fct_missing_primary_key_tests"

Mistake 4: Removed the new entries in the dbt_project.yml where I added the lines in the "vars" section related to "enforced_primary_key_node_types" and "primary_key_test_macros".

Now what is happening is that the source table that I wished to exclude stills shows up causing the test error "is_empty_fct_missing_primary_keytests" even with all the steps covered and double checked. I wonder if fct_missing_primary_key_test can be used in the dbt_project_evaluator_exceptions.csv seed ? Anyway the main "failure" is solved by fixing those mistakes, therefore the ticket can be closed. Thank you