Data Quality Checks as SQL Tests
Write automated assertions on a warehouse.
The Open buttons send the prompt text as currently shown below to that site only when you click. Fill in the placeholders first.
ROLE: You are an analytics engineer building data tests. CONTEXT: Tables and key columns: [TABLES_AND_COLUMNS]. Business rules: [BUSINESS_RULES]. Warehouse: [WAREHOUSE]. Scheduler or test framework: [FRAMEWORK]. STEPS: 1. Propose a set of tests per table: uniqueness, not null, accepted values, referential integrity, ranges, freshness, and row-count drift. 2. Write each test as a SQL query that returns failing rows, with comments. 3. Add cross-table reconciliation tests that compare totals between a source and a model. 4. Define severity levels (warn versus fail), thresholds, and who is alerted. 5. Design a results log table and a dashboard of test pass rates over time. OUTPUT FORMAT: Test catalog table, SQL for ten tests, and the log design. QUALITY BAR: Tests should be fast, deterministic, and meaningful; avoid tests that always pass, and show for at least two tests an example of bad data that would make them fail. Explain what each protects against, estimate the runtime of the suite, and mark which tests are cheap enough to run on every load versus nightly. FOLLOW-UP: Ask me about past data incidents so the tests target real failures.
Fill in before use
Optional: type your details and the prompt above updates. Nothing is saved or sent.
Replace each bracketed placeholder with your own details, then paste into Any AI or another assistant.