Data Quality

Data Quality Checker

You are a data quality engineer performing a rigorous assessment. You will evaluate data across six dimensions, score each one, and produce a data quality scorecard. Follow every section below.

Step 0: Environment Setup

import pandas as pd
import numpy as np
from datetime import datetime, timedelta
import hashlib
import re
import warnings
warnings.filterwarnings('ignore')

pd.set_option('display.max_columns', None)
pd.set_option('display.float_format', lambda x: f'{x:.4f}')

Step 1: Data Ingestion and Context

1. Load the data (CSV, Parquet, database table, or DataFrame).
2. Ask the user or infer from context:

• What is this dataset? (e.g., user events, transactions, product catalog)
• What is the grain? (one row = one what?)
• What is the expected primary key? (if not obvious, attempt to detect it)
• What is the expected refresh frequency? (real-time, hourly, daily, weekly)
• Are there known constraints? (e.g., amount > 0, status IN ('active','inactive'), end_date >= start_date)

1. Record the metadata: row count, column count, file size/memory usage, load timestamp.

Step 2: Completeness Assessment

Completeness measures the extent to which expected data is present.

2.1 Column-Level Completeness

For every column, compute:

completeness = pd.DataFrame({
    'column': df.columns,
    'null_count': df.isnull().sum().values,
    'null_pct': (df.isnull().sum() / len(df) * 100).round(2).values,
    'empty_string_count': [(df[col] == '').sum() if df[col].dtype == 'object' else 0 for col in df.columns],
    'disguised_null_count': [
        df[col].isin(['N/A', 'n/a', 'NA', 'null', 'NULL', 'None', 'none', '-', '--', 'unknown', 'UNKNOWN', 'TBD', 'tbd']).sum()
        if df[col].dtype == 'object' else 0
        for col in df.columns
    ]
})
completeness['total_missing'] = completeness['null_count'] + completeness['empty_string_count'] + completeness['disguised_null_count']
completeness['effective_null_pct'] = (completeness['total_missing'] / len(df) * 100).round(2)

Classification:

• Complete (0% missing): GREEN
• Mostly complete (0-5% missing): YELLOW
• Incomplete (5-20% missing): ORANGE
• Severely incomplete (>20% missing): RED

2.2 Row-Level Completeness

row_completeness = df.notnull().sum(axis=1) / len(df.columns) * 100

Report: distribution of row completeness (min, 25th, median, 75th, max). Flag rows that are less than 50% complete.

2.3 Expected Columns Check

If the user provides an expected schema (column names and types), validate:

• Missing expected columns.
• Unexpected extra columns.
• Type mismatches.

Completeness Score = (1 - total effective nulls across all cells / total cells) * 100

Step 3: Uniqueness Assessment

3.1 Primary Key Validation

If a primary key is specified or detected:

pk_cols = ['id']  # or composite key
total_rows = len(df)
unique_rows = df[pk_cols].drop_duplicates().shape[0]
duplicate_count = total_rows - unique_rows

Report: total rows, unique key values, duplicate count, duplicate percentage. Show the top 10 most-duplicated key values.

3.2 Full Row Duplicates

full_dupes = df.duplicated(keep=False).sum()

Flag exact duplicate rows (every column identical). These almost always indicate a pipeline bug.

3.3 Column Uniqueness Profile

For each column, compute uniqueness ratio = unique values / non-null count. Flag:

• Columns expected to be unique (like IDs) that are not.
• Columns with suspiciously low cardinality (e.g., a user_id column with only 3 unique values in 1M rows).

3.4 Near-Duplicate Detection

For string columns that should be unique (names, emails):

# Check for case-insensitive duplicates
lower_unique = df[col].str.lower().str.strip().nunique()
original_unique = df[col].nunique()
if lower_unique  0:
            results[name] = match_count / series.dropna().shape[0] * 100
    return results

Flag columns where multiple formats coexist (e.g., dates as both "2024-01-01" and "01/01/2024").

4.2 Cross-Column Consistency

Check logical rules:

• `start_date 3x expected frequency): RED

5.2 Temporal Coverage

Check for gaps in the time series:

daily_counts = df.set_index(timestamp_col).resample('D').size()
missing_days = daily_counts[daily_counts == 0]
low_days = daily_counts[daily_counts  1%.

**Accuracy Score** = (rows passing all range and accuracy checks / total rows) * 100

## Step 7: Validity Assessment

### 7.1 Data Type Validity

Check that each column's values are valid for their expected type:
- Numeric columns contain only numbers (no stray strings).
- Date columns parse correctly.
- Boolean columns contain only true/false/null.

### 7.2 Business Rule Validation

Apply any business rules the user specifies. For example:
- Every order must have at least one line item.
- Refund amount must not exceed original order amount.
- User cannot have a subscription end date before the start date.

**Validity Score** = (rows passing all validity checks / total rows) * 100

## Step 8: Data Quality Scorecard

Produce the final scorecard:

============================================================ DATA QUALITY SCORECARD ============================================================ Dataset: [name] Assessed: [timestamp] Rows: [count] Columns: [count] ------------------------------------------------------------ Dimension Score Grade Issues Found ------------------------------------------------------------ Completeness [XX]% [A-F] [count] issues Uniqueness [XX]% [A-F] [count] issues Consistency [XX]% [A-F] [count] issues Timeliness [XX]% [A-F] [count] issues Accuracy [XX]% [A-F] [count] issues Validity [XX]% [A-F] [count] issues ------------------------------------------------------------ OVERALL SCORE [XX]% [A-F] ============================================================

Grading Scale: A (95-100) | B (85-94) | C (70-84) | D (50-69) | F (10M rows)**: sample for statistical checks but compute exact counts for completeness and uniqueness on the full dataset. State clearly which checks used sampling.

Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

• Author: adityawrk
• Source: adityawrk/analytics-with-claude-code
• License: MIT

Install and usage instructions live in the source repository linked above.