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

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

At a glanceRun order for the four data quality checksPostgreSQL shop database after a CSV import
  1. DQ1 – Duplicate customer emailsMerging duplicates first prevents new orphans later.
  2. DQ2 – Orders missing customerQuarantined orders cleared before FK checks.
  3. DQ3 – Orphaned order itemsConstraint added once orphans are gone.
  4. DQ4 – Impossible quantity or priceRange rules are independent and safest last.

Example for: a PostgreSQL shop database with customers, orders and order_items tables after a CSV import.

Summary table

Check IDTableTypeWhat a bad result means
DQ1customersDuplicateSame email on several accounts splits order history and inflates customer counts.
DQ2ordersNULLOrders without a customer_id cannot be billed or reported by customer.
DQ3order_itemsOrphaned FKLine items point to deleted orders, so revenue totals disagree between tables.
DQ4order_itemsOut of rangeZero or negative quantity or price distorts revenue and stock.

Checks

DQ1 – Duplicate customer emails

SELECT lower(email) AS email, COUNT(*) AS copies, array_agg(customer_id) AS ids
FROM customers
GROUP BY lower(email)
HAVING COUNT(*) > 1;
BEGIN;
-- Review: repoint orders to the oldest account before COMMIT
UPDATE orders o SET customer_id = d.keep_id
FROM (SELECT customer_id, MIN(customer_id) OVER (PARTITION BY lower(email)) AS keep_id FROM customers) d
WHERE o.customer_id = d.customer_id AND d.customer_id <> d.keep_id;
-- ROLLBACK; or COMMIT;

DQ2 – Orders missing customer

SELECT order_id, created_at FROM orders WHERE customer_id IS NULL;
BEGIN;
-- Review: moves rows to a quarantine table instead of deleting
CREATE TABLE orders_quarantine AS SELECT * FROM orders WHERE customer_id IS NULL;
DELETE FROM orders WHERE customer_id IS NULL;
-- ROLLBACK; or COMMIT;

DQ3 – Orphaned order items

SELECT oi.order_item_id, oi.order_id
FROM order_items oi
LEFT JOIN orders o ON o.order_id = oi.order_id
WHERE o.order_id IS NULL;
BEGIN;
-- Review count first, then add the missing constraint
DELETE FROM order_items oi WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.order_id = oi.order_id);
ALTER TABLE order_items ADD CONSTRAINT fk_items_order FOREIGN KEY (order_id) REFERENCES orders(order_id);
-- ROLLBACK; or COMMIT;

DQ4 – Impossible quantity or price

-- Assumed valid: quantity 1-1000, unit_price > 0
SELECT order_item_id, quantity, unit_price
FROM order_items
WHERE quantity NOT BETWEEN 1 AND 1000 OR unit_price <= 0;
BEGIN;
-- Review: fix at the source if possible; this only blocks new bad rows
ALTER TABLE order_items ADD CONSTRAINT chk_qty_price CHECK (quantity BETWEEN 1 AND 1000 AND unit_price > 0) NOT VALID;
-- ROLLBACK; or COMMIT;

Run order

  1. DQ1 – merging duplicates first prevents new orphans later.
  2. DQ2 – quarantined orders should be cleared before FK checks.
  3. DQ3 – constraint can only be added once orphans are gone.
  4. DQ4 – range rules are independent and safest last.

Assumptions to confirm: email is meant to be unique, quantity never exceeds 1000, and quarantining NULL-customer orders is acceptable.

Written by AI from this prompt. Your results will vary.

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 in Coding