sql-data-validator

name	sql-data-validator
description	Generate SQL queries that validate data quality and integrity. Use when the user wants validation queries for a database.

SQL Data Validator

Generate SQL validation queries to check data quality, integrity, and consistency.

Description

This skill generates ready-to-run SQL queries that validate data quality across databases. Given a schema or table description, it produces a comprehensive set of validation queries covering completeness, uniqueness, referential integrity, and business rule compliance.

Instructions

When the user provides a database schema, table descriptions, or data quality concerns:

1. Analyze Schema: Understand tables, columns, types, relationships, and constraints
2. Generate Validation Queries: Produce SQL queries organized by category:
   • Completeness checks (NULL analysis)
   • Uniqueness checks (duplicate detection)
   • Referential integrity checks (orphan records)
   • Range and format validation
   • Business rule validation
   • Cross-table consistency
3. Format Output: Provide copy-paste-ready SQL with clear comments
4. Summary Query: Generate a single "health check" query that aggregates all metrics

Query Templates

Completeness

-- Check NULL rates for all columns in [table]
SELECT
  COUNT(*) as total_rows,
  COUNT(column_name) as non_null_count,
  ROUND(100.0 * COUNT(column_name) / COUNT(*), 2) as completeness_pct
FROM table_name;

Uniqueness

-- Find duplicate records based on [key columns]
SELECT column1, column2, COUNT(*) as duplicate_count
FROM table_name
GROUP BY column1, column2
HAVING COUNT(*) > 1
ORDER BY duplicate_count DESC;

Referential Integrity

-- Find orphaned records in [child_table]
SELECT c.*
FROM child_table c
LEFT JOIN parent_table p ON c.parent_id = p.id
WHERE p.id IS NULL;

Range Validation

-- Check for out-of-range values
SELECT *
FROM table_name
WHERE numeric_column < 0
   OR numeric_column > 999999
   OR date_column > CURRENT_DATE
   OR date_column < '2000-01-01';

Format Validation

-- Check email format
SELECT email, id
FROM users
WHERE email NOT LIKE '%_@_%.__%';

-- Check phone format
SELECT phone, id
FROM users
WHERE phone !~ '^\+?[0-9]{10,15}$';

Business Rules

-- Orders must have positive totals
SELECT order_id, total
FROM orders
WHERE total <= 0 AND status != 'cancelled';

-- End date must be after start date
SELECT id, start_date, end_date
FROM subscriptions
WHERE end_date <= start_date;

Health Check Summary

-- Data Quality Dashboard
SELECT
  'Total Records' as metric, COUNT(*)::text as value FROM table_name
UNION ALL
SELECT 'Null Email Rate', ROUND(100.0 * SUM(CASE WHEN email IS NULL THEN 1 ELSE 0 END) / COUNT(*), 2)::text || '%' FROM users
UNION ALL
SELECT 'Duplicate Count', COUNT(*)::text FROM (SELECT email FROM users GROUP BY email HAVING COUNT(*) > 1) d
UNION ALL
SELECT 'Orphaned Orders', COUNT(*)::text FROM orders o LEFT JOIN users u ON o.user_id = u.id WHERE u.id IS NULL;

Dialect Support

Generates queries compatible with:

• PostgreSQL (default)
• MySQL / MariaDB
• SQL Server
• SQLite
• Oracle

Specify your dialect and the skill will adjust syntax accordingly (e.g., ILIKE vs LIKE, LIMIT vs TOP, date functions).

Example Usage

Generate data validation queries for these tables:
- users (id, email, name, created_at, status)
- orders (id, user_id, total, status, created_at)
- order_items (id, order_id, product_id, quantity, price)

Write SQL checks to validate our migration from MySQL to PostgreSQL. Here's the schema...