Data Validator

Overview

Perform comprehensive data quality checks on datasets — validate schemas, detect anomalies, find duplicates, and enforce data contracts. Essential for ETL pipelines where bad data silently corrupts downstream analytics and dashboards.

Instructions

1. Profile the dataset first

Before validating, understand the data:

• Row count and column count
• Data types per column (string, integer, float, date, boolean)
• Null rates per column
• Unique value counts and cardinality
• Min/max/mean for numeric columns
• Date ranges for temporal columns

Present as a data profile summary:

Dataset Profile: orders_export.csv
Rows: 142,847 | Columns: 12

| Column        | Type    | Nulls  | Unique  | Sample Values          |
|---------------|---------|--------|---------|------------------------|
| order_id      | string  | 0%     | 142,847 | ORD-20260217-001       |
| customer_id   | integer | 0.3%   | 28,491  | 10042, 10043           |
| amount         | float   | 0%     | 8,234   | 29.99, 149.00          |
| created_at    | date    | 0%     | 89,112  | 2026-02-17T14:23:01Z   |
| status        | string  | 0%     | 5       | completed, pending      |

2. Run validation checks

Apply these checks systematically:

Completeness — Are required fields populated?

• Check null/empty rates against thresholds (e.g., email must be <1% null)
• Flag columns with unexpected null spikes

Uniqueness — Are IDs actually unique?

• Check primary key columns for duplicates
• Report duplicate counts and sample duplicate rows

Type consistency — Do values match expected types?

• Dates that don't parse, numbers stored as strings
• Mixed types in the same column

Range validity — Are values within expected bounds?

• Negative amounts, future dates, ages over 150
• Enum columns with unexpected values

Referential integrity — Do foreign keys match?

• customer_id values that don't exist in the customers table
• Orphaned records

Freshness — Is the data up to date?

• Most recent record timestamp vs current time
• Gaps in time series data

3. Report findings

Structure results as a validation report:

## Data Validation Report
Dataset: orders_export.csv | Checked: 2026-02-17 14:30 UTC

### ❌ Failed Checks (3)
1. **Duplicate order_id** — 23 duplicate IDs found (0.016%)
   Sample: ORD-20260215-4421 appears 3 times
2. **Null customer_email** — 2.1% null (threshold: 1%)
   Spike on 2026-02-15 (bulk import batch)
3. **Future dates in created_at** — 7 rows have dates in 2027

### ⚠️ Warnings (2)
1. **Amount outliers** — 4 orders exceed $10,000 (review manually)
2. **Status enum drift** — New value "on_hold" not in expected set

### ✅ Passed Checks (8)
- Primary key uniqueness (order_id): PASS (99.98%)
- Amount non-negative: PASS
- Date range valid: PASS (excluding 7 future dates)
...

4. Suggest fixes

For each failed check, recommend a concrete action:

• SQL query to find and fix duplicates
• Transform to clean null values
• Filter to remove invalid rows
• Alert to set up for ongoing monitoring

Examples

Example 1: CSV Import Validation

Input: "Validate this customer export before importing to our new CRM"

Output:

## Validation Report: customers_export.csv
Rows: 8,234 | Columns: 9

### ❌ Failed (2)
1. **Email format invalid** — 142 rows (1.7%) have malformed emails
   Pattern: missing @ symbol, spaces in domain
   Fix: SELECT * FROM data WHERE email NOT LIKE '%_@_%.__%'

2. **Phone duplicates** — 34 phone numbers shared by multiple customers
   Likely data entry errors or shared business lines
   Fix: Review and merge duplicate customer records

### ✅ Passed (6)
- customer_id unique: PASS
- Required fields (name, email): PASS (99.8%)
- Country codes valid ISO-3166: PASS
- Created dates in range: PASS
- No PII in notes field: PASS
- UTF-8 encoding clean: PASS

Recommendation: Fix the 142 invalid emails before import.
The phone duplicates are non-blocking but should be reviewed.

Example 2: Pipeline Data Contract Check

Input: "Check if the analytics events from our tracking pipeline match the expected schema"

Output:

## Schema Contract Validation: events_2026_02_17.json
Events: 1.2M | Schema version: v3.2

### ❌ Contract Violations (1)
1. **Missing required field: session_id**
   - 12,847 events (1.07%) missing session_id
   - All from source: mobile-ios-sdk v4.1.0
   - Started: 2026-02-16 09:00 UTC (correlates with SDK release)

### ⚠️ Warnings (1)
1. **New field detected: device_model**
   - Not in schema v3.2 — likely added in SDK update
   - 100% populated on mobile events, absent on web

### ✅ Contract Compliance: 98.93%

Action: Pin mobile-ios-sdk to v4.0.x or update schema to v3.3
with session_id as optional for mobile sources.

Guidelines

• Always profile before validating — you need baselines to detect anomalies
• Distinguish between hard failures (blocks the pipeline) and warnings (review later)
• Include sample rows for every finding — abstract stats are hard to act on
• Suggest SQL or code fixes, not just descriptions of problems
• For time series data, check for gaps and seasonality, not just latest timestamp
• Track validation results over time to detect data drift
• Be specific about which rows/records are affected — "23 duplicates" beats "some duplicates"