data-profiler

What this skill does

# Data Profiler

Produce a complete data quality assessment for the source system data. The output is a data quality scorecard that drives cleansing priorities and determines which data issues must be fixed before migration and which can be resolved during transformation. No migration should proceed to production cutover without a completed data profile.

## Profiling Queries

Run these queries against the source system. Document the results for every entity being migrated.

### Completeness Analysis

```sql
-- Null rate per column (SQL Server)
SELECT
    'Clients' AS TableName,
    SUM(CASE WHEN ClientID IS NULL THEN 1 ELSE 0 END) * 100.0 / COUNT(*) AS ClientID_NullPct,
    SUM(CASE WHEN LastName IS NULL OR LastName = '' THEN 1 ELSE 0 END) * 100.0 / COUNT(*) AS LastName_NullPct,
    SUM(CASE WHEN FirstName IS NULL OR FirstName = '' THEN 1 ELSE 0 END) * 100.0 / COUNT(*) AS FirstName_NullPct,
    SUM(CASE WHEN SSN IS NULL THEN 1 ELSE 0 END) * 100.0 / COUNT(*) AS SSN_NullPct,
    SUM(CASE WHEN DateOfBirth IS NULL THEN 1 ELSE 0 END) * 100.0 / COUNT(*) AS DateOfBirth_NullPct,
    SUM(CASE WHEN AgentCode IS NULL THEN 1 ELSE 0 END) * 100.0 / COUNT(*) AS AgentCode_NullPct,
    COUNT(*) AS TotalRecords
FROM dbo.Clients;
```

**Null rate interpretation**:

| Null Rate | Severity | Action |
|-----------|----------|--------|
| 0% | None | No action needed |
| 1-5% | Low | Accept if field is optional in destination; investigate if required |
| 5-20% | Medium | Identify pattern (batch of records from specific period?), cleanse or default |
| > 20% | High | Must resolve before migration — either cleanse, derive, or confirm field is not required |

Document results:

| Table | Column | Null / Empty Count | Null % | Destination Required | Action |
|-------|--------|--------------------|--------|---------------------|--------|
| Clients | LastName | 0 | 0% | Yes | No action |
| Clients | DateOfBirth | 1,245 | 10% | No | Accept null — destination field is optional |
| Clients | AgentCode | 340 | 2.7% | No | Set ProducerId to null in destination |
| Clients | SSN | 8,200 | 66% | No (encrypted, optional) | Accept null |

### Uniqueness Analysis

```sql
-- Duplicate detection on natural key fields
SELECT 
    PolicyNumber,
    COUNT(*) AS DuplicateCount
FROM dbo.Policies
GROUP BY PolicyNumber
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC;

-- Total duplicates
SELECT COUNT(*) AS TotalDuplicateGroups
FROM (
    SELECT PolicyNumber, COUNT(*) cnt 
    FROM dbo.Policies 
    GROUP BY PolicyNumber 
    HAVING COUNT(*) > 1
) d;

-- Sample of duplicate records (to understand the pattern)
SELECT TOP 20 p.*
FROM dbo.Policies p
WHERE p.PolicyNumber IN (
    SELECT PolicyNumber FROM dbo.Policies GROUP BY PolicyNumber HAVING COUNT(*) > 1
)
ORDER BY p.PolicyNumber, p.CreateDate;
```

Document:

| Table | Key Field | Total Records | Duplicate Groups | Records in Duplicate Groups | Duplicate % |
|-------|-----------|--------------|-----------------|----------------------------|------------|
| Policies | PolicyNumber | 38,200 | 42 | 89 | 0.23% |
| Clients | SSN (non-null) | 4,085 | 7 | 16 | 0.39% |

**De-duplication strategy** (choose based on the pattern found):

| Strategy | When to Use |
|----------|------------|
| Keep latest by date | Duplicates are updates that did not properly replace the original |
| Keep highest status | Active > Pending > Cancelled — the more complete record wins |
| Merge fields | Both records have partial data that completes the full record — merge fields |
| Manual review | Duplicates cannot be programmatically resolved — flag for business review |

### Format Consistency

```sql
-- Date format distribution (for varchar date columns)
SELECT 
    CASE 
        WHEN EffectiveDate LIKE '[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]' THEN 'MM/DD/YYYY'
        WHEN EffectiveDate LIKE '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]' THEN 'YYYY-MM-DD'
        WHEN EffectiveDate IS NULL THEN 'NULL'
        ELSE 'OTHER: ' + LEFT(EffectiveDate, 20)
    END AS FormatPattern,
    COUNT(*) AS RecordCount
FROM dbo.OldPolicies -- tables with varchar date columns
GROUP BY 
    CASE 
        WHEN EffectiveDate LIKE '[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]' THEN 'MM/DD/YYYY'
        WHEN EffectiveDate LIKE '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]' THEN 'YYYY-MM-DD'
        WHEN EffectiveDate IS NULL THEN 'NULL'
        ELSE 'OTHER: ' + LEFT(EffectiveDate, 20)
    END;

-- Phone number format distribution
SELECT 
    CASE 
        WHEN Phone LIKE '[0-9][0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]' THEN 'NNN-NNN-NNNN'
        WHEN Phone LIKE '([0-9][0-9][0-9]) [0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]' THEN '(NNN) NNN-NNNN'
        WHEN Phone LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' THEN 'NNNNNNNNNN'
        WHEN Phone IS NULL THEN 'NULL'
        ELSE 'OTHER'
    END AS FormatPattern,
    COUNT(*) AS RecordCount
FROM dbo.Clients
GROUP BY 
    CASE 
        WHEN Phone LIKE '[0-9][0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]' THEN 'NNN-NNN-NNNN'
        WHEN Phone LIKE '([0-9][0-9][0-9]) [0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]' THEN '(NNN) NNN-NNNN'
        WHEN Phone LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' THEN 'NNNNNNNNNN'
        WHEN Phone IS NULL THEN 'NULL'
        ELSE 'OTHER'
    END;
```

Document the findings and the normalization rule:

| Field | Formats Found | Count per Format | Normalization Rule |
|-------|--------------|-----------------|-------------------|
| EffectiveDate | MM/DD/YYYY (35,100), YYYY-MM-DD (2,800), NULL (300) | — | Parse all formats, output ISO 8601 YYYY-MM-DD |
| Phone | NNN-NNN-NNNN (8,200), (NNN) NNN-NNNN (1,800), NNNNNNNNNN (950), NULL (1,500) | — | Strip all non-digits, store as 10-digit string |

**SSN masking verification**:
```sql
-- Verify SSN data is not exposed in other columns
SELECT TOP 10 Notes FROM dbo.Clients WHERE Notes LIKE '%[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]%';
-- If any rows returned: SSN embedded in notes — must be scrubbed before migration
```

### Referential Integrity

```sql
-- Orphaned policies (no matching client)
SELECT 
    COUNT(*) AS OrphanedPolicies,
    COUNT(*) * 100.0 / (SELECT COUNT(*) FROM dbo.Policies) AS OrphanPct
FROM dbo.Policies p
WHERE NOT EXISTS (SELECT 1 FROM dbo.Clients c WHERE c.ClientID = p.ClientID);

-- Show sample orphaned records
SELECT TOP 20 p.PolicyNumber, p.ClientID, p.CreateDate
FROM dbo.Policies p
WHERE NOT EXISTS (SELECT 1 FROM dbo.Clients c WHERE c.ClientID = p.ClientID)
ORDER BY p.CreateDate DESC;

-- Claims with no matching policy
SELECT COUNT(*) AS OrphanedClaims
FROM dbo.Claims cl
WHERE NOT EXISTS (SELECT 1 FROM dbo.Policies p WHERE p.PolicyID = cl.PolicyID);
```

Document:

| Parent Table | Child Table | Orphan Count | Orphan % | Recommended Action |
|-------------|-------------|-------------|----------|-------------------|
| Clients | Policies | 12 | 0.03% | Investigate — these are old records; likely client deleted without cascade. Exclude from migration or create placeholder client records. |
| Policies | Claims | 0 | 0% | No action |
| Agents | Policies | 7 | 0.02% | Agent left firm; create inactive placeholder agent record |

### Value Distribution

For code fields (status codes, LOB codes, state codes), profile all distinct values:

```sql
-- Status code distribution
SELECT StatusCode, COUNT(*) AS RecordCount, COUNT(*) * 100.0 / SUM(COUNT(*)) OVER() AS Pct
FROM dbo.Policies
GROUP BY StatusCode
ORDER BY RecordCount DESC;
```

Document and flag any codes not in the expected value set:

| Code Field | Value | Count | % | In Destination Lookup? |
|-----------|-------|-------|---|----------------------|
| StatusCode | A | 28,450 | 74.5% | Yes → Active |
| StatusCode | C | 8,100 | 21.2% | Yes → Cancelled |
| StatusCode | X | 1,550 | 4.1% | Yes → Expired |
| StatusCode | P | 98 | 0.3% | Yes → Pending |
| StatusCode | Z | 2 | 0.01% | **No — unknown code. Must investigate before migration.** |

**Unknown codes** require business owner confir