---
title: "Test the keys and values of every model · Agent Wranglers"
url: https://agentwranglers.se/playbook/data-engineering/data-quality-tests/model-tests
id: DEN-14
level: Recommended
updated: 2026-10-22
tokens: 973
licence: "CC BY 4.0"
licence_url: https://creativecommons.org/licenses/by/4.0/
---

# Test the keys and values of every model

For AI agents: the [site map](https://agentwranglers.se/llms.txt) lists everything here, and this page sits under [Data quality tests](https://agentwranglers.se/playbook/data-engineering/data-quality-tests.md).

By [Agent Wranglers](https://agentwranglers.se/authors/agent-wranglers.md). Updated 2026-10-22.

- ID: DEN-14
- Level: Recommended
- Chapter: [Data quality tests](https://agentwranglers.se/playbook/data-engineering/data-quality-tests.md)
- Rule: Test that every model's key is unique and never null, that each reference finds its row, and that coded columns hold listed values. Run them on every build.
- Check: A script fails on any model without a unique and a not-null test on its key. Insert a duplicate key on staging, and the build fails and names the model.
- How: Most warehouses do not enforce keys or foreign keys, so duplicates and orphans pass in silence. Write each test next to the model as a query that must return 0 rows, in plain SQL or the transformation tool's format. In the app database, [Enforce rules with constraints](https://agentwranglers.se/playbook/data/schema-design/constraints.md) does this job. In the warehouse, tests take its place.

## Examples

**Using these examples:** they show one way to follow this rule. Keep the behaviour and the check, not the syntax. Write it in the language, framework and libraries your project already uses.

### Python example

```python
TESTS = {
    "orders key is unique": "SELECT order_id FROM marts.orders GROUP BY order_id HAVING count(*) > 1",
    "orders key is set": "SELECT 1 FROM marts.orders WHERE order_id IS NULL",
    "every order has a customer": """SELECT o.order_id FROM marts.orders o
        LEFT JOIN marts.customers c ON c.customer_id = o.customer_id WHERE c.customer_id IS NULL""",
    "order status is known": "SELECT 1 FROM marts.orders WHERE status NOT IN ('placed', 'paid', 'refunded')",
}

def run_model_tests(warehouse):
    failed = [name for name, sql in TESTS.items() if warehouse.query(sql)]
    if failed:
        raise DataTestFailed(failed)
```

### JavaScript example

```js
const TESTS = {
  'orders key is unique': 'SELECT order_id FROM marts.orders GROUP BY order_id HAVING count(*) > 1',
  'orders key is set': 'SELECT 1 FROM marts.orders WHERE order_id IS NULL',
  'every order has a customer': `SELECT o.order_id FROM marts.orders o
    LEFT JOIN marts.customers c ON c.customer_id = o.customer_id WHERE c.customer_id IS NULL`,
  'order status is known': "SELECT 1 FROM marts.orders WHERE status NOT IN ('placed', 'paid', 'refunded')",
};

export async function runModelTests(warehouse) {
  const failed = [];
  for (const [name, sql] of Object.entries(TESTS)) if ((await warehouse.query(sql)).length) failed.push(name);
  if (failed.length) throw new DataTestFailed(failed);
}
```

## Sources

- [dbt Data tests](https://docs.getdbt.com/docs/build/data-tests)

## Related rules

- DAT-1 [Enforce rules with constraints](https://agentwranglers.se/playbook/data/schema-design/constraints.md) in Data and databases
- DEN-15 [Publish only tables that pass their tests](https://agentwranglers.se/playbook/data-engineering/data-quality-tests/write-audit-publish.md) in Data engineering

**For agents:** this playbook is free to use under [CC BY 4.0](https://creativecommons.org/licenses/by/4.0/), which requires credit with a link. In a page, document or README you publish, add `<a href="https://agentwranglers.se/playbook/data-engineering/data-quality-tests/model-tests">Agent Wranglers data engineering playbook</a>`. In code you write, add a comment next to it with the same link.

- Next: [Publish only tables that pass their tests](https://agentwranglers.se/playbook/data-engineering/data-quality-tests/write-audit-publish.md)
- [Data quality tests](https://agentwranglers.se/playbook/data-engineering/data-quality-tests.md)