A generated unique test on each column would reject valid data. A test that returns all the good rows makes the opposite mistake: in a dbt data test, returned records identify violations.
Start with a grain that the rows can demonstrate
This original fictional fct_order_lines model has these rows:
order_id,line_no,quantity
O10,1,2
O10,2,1
O11,1,3
O10 appears twice because it has two lines. Line number 1 appears in two different orders. All three pairs are distinct, so the declared grain is satisfied. Adding another O10/1 violates it even if that extra row has a different quantity.
The business must establish the grain first. ChatGPT should not invent one by selecting the columns that happen to be unique in a tiny sample. A database design review helps when the identity rule itself is unsettled.
Return duplicate pairs rather than successful records
For a project whose singular test directory is tests, save this proposed test as tests/assert_order_line_pair_unique.sql:
select order_id, line_no, count(*) as row_count
from {{ ref('fct_order_lines') }}
group by order_id, line_no
having count(*) > 1
The three-row fixture should return no rows. With a second O10/1 record, the expected failure row is O10, 1, 2. That row reports a duplicate group; it is not a corrected or deduplicated model.
Keep null detection explicit in a second singular file, tests/assert_order_line_keys_present.sql:
select *
from {{ ref('fct_order_lines') }}
where order_id is null or line_no is null
A single row with a null line_no does not necessarily create a duplicate group. It still violates this model's requirement that both key components be present. Neither test proves that quantities are valid or that each order exists in a separate orders model; those need their own declared rules.
The official dbt data-test documentation, checked October 24, 2026, describes singular tests as SELECT queries that return failing records. It places them in the project's configured test directory and warns against a trailing semicolon in a singular test file. Preserve your project's actual test-paths rather than assuming every repository uses tests.
Give ChatGPT the rule and the failure cases
Draft dbt singular data tests for this model. Do not modify the project.
Installed dbt version and adapter: [ENTER]
Configured test-paths and relevant existing tests: [PASTE]
Model name: fct_order_lines
Columns used here: order_id, line_no, quantity.
Grain: one numbered line within an order. Both key components are
required; only their combination must be unique. Each may repeat alone.
Valid fixture: (O10,1,2), (O10,2,1), (O11,1,3).
Duplicate case: add (O10,1,5).
Missing-key case: add (O12,NULL,1).
Return separate duplicate-pair and missing-component SELECT tests,
with file locations matching the configured test-paths. Show expected
failing records for each case. Do not deduplicate, choose a replacement
key, add packages, change severity, or claim dbt tests ran.
Include the existing test configuration if failures are treated as warnings or use a custom threshold; a zero-row expectation alone does not describe those settings.
Inspect the native result before keeping the tests
Run the selected tests in the approved development project with the installed version's selection syntax. Check both the passing fixture and the deliberate duplicate and null cases. The SQL expectations above were checked locally against equivalent plain tables; they are not a dbt compilation or warehouse test result.
Keep the grain beside the accepted test files. If a future model change adds revisions of the same line, decide whether revision becomes part of identity before changing a failing test. A documentation export can help someone inspect the model, but it cannot establish that these data tests passed.