Test the keys and values of every model

How to do it

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 does this job. In the warehouse, tests take its place.

Examples

Python example

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

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);
}

How to 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.

Sources

• dbt Data tests

Related rules

• DAT-1 Enforce rules with constraints in Data and databases
• DEN-15 Publish only tables that pass their tests in Data engineering