Test passes if actual result contains duplicate rows but expected result doesn't

[prratek]

Describe the bug

Because of the use of DISTINCT in set operations when comparing the expected and actual outputs, a test will pass even if one of the two contains duplicate rows. I was writing a test specifically to ensure that my model handles duplicates properly and this seemingly isn't possible to do.

Steps to reproduce

1. Write a passing unit test
2. Duplicate the last row of the expected output seed file, so that this should now have one row more than the actual model results
3. Run the test again

Expected results

The test should fail because the outputs don't have the same row count. The expected output has one row more than the actual.

Actual results

The test passes

Screenshots and log output

N/A

System information

The contents of your packages.yml file:

packages:
  - package: dbt-labs/codegen
    version: 0.10.0
  - package: calogica/dbt_expectations
    version: 0.9.0
  - package: mjirv/dbt_datamocktool
    version: 0.3.2

Which database are you using dbt with?

• postgres
• redshift
• bigquery
• snowflake
• other (specify: ____________)

The output of dbt --version:

❯ dbt --version
Core:
  - installed: 1.6.0
  - latest:    1.6.1 - Update available!

Additional context

My first thought was to switch the except distinct to an except all but apparently BigQuery does not support that?

Are you interested in contributing the fix?