data-validator

What this skill does

# 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"