Find and Fix Data Quality Issues with Ready-to-Run SQL Checks
Paste your schema and get SQL queries that catch duplicates, NULLs, orphaned foreign keys and out-of-range values, each with what a bad result means and a fix.
Prompt
You are a senior data engineer who audits production databases for data quality problems.
I will paste my table schema (CREATE TABLE statements or a column list) right after this prompt. If I have not said which SQL dialect I use, ask me once and wait. Then write data quality checks I can run as-is.
Rules:
1. Cover four check types for every relevant table: duplicates on natural or unique keys, NULLs in columns that should be required, orphaned foreign keys, and out-of-range or impossible values (negative amounts, future dates, invalid status codes).
2. Every detection query is read-only (SELECT only), returns the offending rows or a count, and uses only table and column names from my schema.
3. For each check, state in one sentence what a non-zero result means for the data or the business.
4. Give a fix query for each issue, wrapped in a transaction, with a comment telling me to review affected rows before COMMIT.
5. Where a valid range is unclear, write the threshold you assumed as an SQL comment.
Output format:
## Summary table with columns: Check ID | Table | Type | What a bad result means
## Checks: for each Check ID, the detection query, then the fix query, in sql code blocks
## Run order: numbered list with one-line reasons
End with one line listing assumptions about keys, ranges or constraints I should confirm.Example output
Tips and common edits
Why this works
- Naming four check types forces coverage of the issues that actually break reports, not just a quick NULL scan.
- Read-only detection plus transaction-wrapped fixes keeps you from changing data before you have seen the bad rows.
- A one-line meaning per check turns query output into a decision, and assumed ranges appear as comments you can verify.
Use before a migration, report launch or after a messy import. Have your CREATE TABLE statements and SQL dialect ready, then paste them into ChatGPT, Claude or Gemini after the prompt.
Common edits
- Name your dialect up front, such as BigQuery, MySQL or Snowflake, to skip the question.
- Ask for detection queries only, combined into one UNION ALL report that returns a count per check.
Prompt details
- By
- Prompts-GPT editors
- Published
Did this prompt work?
Keep exploring
More like this
Design a Normalized Database Schema from a Plain Feature Description
Turns a plain-language feature description into a normalized relational schema with typed columns, keys, constraints, ready-to-run CREATE TABLE SQL, example queries and trade-offs.
Optimize a Slow SQL Query Using Its EXPLAIN Plan Step by Step
Paste a slow query and its EXPLAIN output to get the costliest plan node named, exact CREATE INDEX or rewrite fixes, and a clear way to verify the speedup.
Translate SQL Between MySQL, PostgreSQL and SQL Server with a Change Table
Returns your query rewritten for the target database, plus a table flagging every function, date, quoting and LIMIT change and where behavior could differ.
Combine and Clean Multiple CSV or Excel Files into One Table with Power Query
You get a header mapping table, numbered Power Query clicks, commented M code that fixes mismatched columns and duplicates, and refresh steps for new files.
Write an Excel VBA Macro That Cleans and Formats Your Recurring Report
You get a commented VBA macro built from your own column headers, with backup steps, install instructions and a post-run checklist.
Explain a Complex SQL Query in Plain English, Step by Step
Get a plain-English walkthrough of any SQL query in execution order, with tiny sample tables, row counts after each clause, and the hidden gotchas.