[Bug] Test for uniqueness passes when duplicates are present in BigQuery column

[steffan-roughsedge]

Is this a new bug in dbt-core?

• I believe this is a new bug in dbt-core
• I have searched the existing issues, and I could not find an existing issue for this bug

Current Behavior

When running the out-of-the-box 'unique' test the test will pass even when there are duplicates in the column if:

• The test is running against a BigQuery table
• The column being tested has exactly the same name as the table

Expected Behavior

The test should fail if the there are duplicates in the column being tested

Steps To Reproduce

Create table called my-project.my-dataset.orders with a single column named 'orders'. Insert duplicate values into the 'orders' column. Run the DBT 'unique' test against this column. The test passes.

Relevant log output

No response

Environment

- OS:Mac OSX Sequoia
- Python: 3.11.2
- dbt: 1.8.3
- Database: BigQuery

Which database adapter are you using with dbt?

bigquery

Additional Context

Looking at the generated code (shown below) for the test the issue occurs because the table in the first CTE is not aliased, therefore the subsequent GROUP BY in the main SELECT is grouping by the whole table.

A similar issue was previously logged here, however it looks like it was closed and the issue I am experiencing still remains.

with dbt_test__target as (

  select orders as unique_field
  from `my-project`.`my-dataset`.`orders` -- Add an alias for this table here
  where orders is not null

)

select
    unique_field,
    count(*) as n_records

from dbt_test__target
group by unique_field
having count(*) > 1

[dbeatty10]

Thanks for reaching out @steffan-roughsedge !

I haven't been able to reproduce this myself. Could you provide project files similar to below to help us reproduce the issue you are seeing?

The original issue (#2061) and draft PR (#2075) got closed as stale, but we'd review a new pull request if anyone is interested in solving this for dbt-bigquery for both unique and not_null. We'd just need to give care to make sure it doesn't break anyone, especially given how rare the bug scenario is.

The relevant default adapter code for each of the current implementations is located here:

• https://github.com/dbt-labs/dbt-adapters/blob/6c41bedf27063eda64375845db6ce5f7535ef6aa/dbt/include/global_project/macros/generic_test_sql/unique.sql
• https://github.com/dbt-labs/dbt-adapters/blob/6c41bedf27063eda64375845db6ce5f7535ef6aa/dbt/include/global_project/macros/generic_test_sql/not_null.sql

Depending on the details of the solution, we'd either choose to adopt it as the new default for all adapters or just provided targeted solution for dbt-bigquery.

Either way, any implementation should include relevant tests that include the scenario described above with the model name and column sharing the same name.

Project files

Create these files:

models/orders.sql

select struct(1 as id) as orders

models/_models.yml

models:
  - name: orders
    columns:
      - name: orders
        tests:
          - unique
          - not_null

Run these commands:

dbt build --full-refresh

Get this output:

$ dbt build --full-refresh

04:18:40  Running with dbt=1.8.8
04:19:05  Registered adapter: bigquery=1.8.3
04:19:06  Found 1 model, 2 data tests, 473 macros
04:19:06  
04:19:40  Concurrency: 10 threads (target='blue')
04:19:40  
04:19:40  1 of 3 START sql view model dbt_dbeatty.orders ................................. [RUN]
04:19:42  1 of 3 OK created sql view model dbt_dbeatty.orders ............................ [CREATE VIEW (0 processed) in 1.57s]
04:19:42  2 of 3 START test not_null_orders_orders ....................................... [RUN]
04:19:42  3 of 3 START test unique_orders_orders ......................................... [RUN]
04:19:46  3 of 3 PASS unique_orders_orders ............................................... [PASS in 3.50s]
04:19:46  2 of 3 PASS not_null_orders_orders ............................................. [PASS in 4.08s]
04:19:46  
04:19:46  Finished running 1 view model, 2 data tests in 0 hours 0 minutes and 39.89 seconds (39.89s).
04:19:46  
04:19:46  Completed successfully
04:19:46  
04:19:46  Done. PASS=3 WARN=0 ERROR=0 SKIP=0 TOTAL=3

[dbeatty10]

The same underlying behavior in BigQuery might be the underlying root cause of all of the following reports:

• union should explicitly specify table name in casts dbt-utils#173
• unique schema test will fail on BigQuery if tested on column with name identical to model #2061
• [CT-396] [Bug] Generic tests aren't valid in BigQuery when the struct and table name are the same  #4824
• [Bug] Test for uniqueness passes when duplicates are present in BigQuery column #11067