dbt Tests

• Write generic tests in YAML for columns
• Write a singular test with raw SQL
• Interpret a test failure and decide between fix data or fix model

• Tests convert a 'request' for clean data into a checked contract.
• Failures point at the exact offending rows, fixing is fast.
• Tests run the same in dev, CI, and prod, so trust is portable.

Each part ships only after its checks pass; you do not discover a missing bolt after the car is sold.

• Declare generic tests in the model's YAML: unique, not_null, accepted_values, relationships.
• Write singular tests as SELECTs that must return no rows.
• dbt test compiles each test to SQL (COUNT over the failing set).
• Failures are stored, reported, and, in CI, gate the promotion.

• A unique test on order_id catches pipeline retries duplicating loads.
• An accepted_values test on a status enum catches every new code before dashboards break.
• Relationship tests verify every order row resolves to a known customer.

• Test the contract, not the whole table: keys, money, status enums.
• Run tests on every build and in CI, and page on failures in production.
• Name tests and give failing ones descriptions so the alert is actionable at 3am.

• Declaring a uniqueness test on a column that is not unique at the model grain.
• Hand-writing a singular test for something a generic test already covers.
• Letting a long-failing test fester instead of pruning or fixing it.

• A unique failure on 0.01% of rows is nearly always a retry duplicate — inspect them before rewriting the pipeline.
• If a relationship test fails after a join change, the join multiplied rows: fix the grain, not the test.