Data Profile

Data Profile

Profile the given tabular data file to understand its structure, types, and distributions.

Cowork note: If relative paths don't resolve, call mcp__qsv__qsv_get_working_dir and mcp__qsv__qsv_set_working_dir to sync the working directory.

Steps

1. Index: Run mcp__qsv__qsv_index on the file for fast random access in subsequent steps.

2. Detect format: Run mcp__qsv__qsv_sniff on the file to detect delimiter, encoding, preamble, and row count estimate.

3. Count rows: Run mcp__qsv__qsv_count to get the exact row count.

4. Get headers: Run mcp__qsv__qsv_headers to list all column names and positions.

5. Compute statistics: Run mcp__qsv__qsv_stats with cardinality: true and stats_jsonl: true to generate full column statistics and cache them. Include --everything for comprehensive stats (mean, median, mode, stddev, quartiles, etc.). Basic moarstats auto-runs to enrich the cache with ~18 additional columns.

6. Advanced statistics: Run mcp__qsv__qsv_moarstats with advanced: true (omit output_file — it updates the stats cache in-place by default). This enriches the stats cache with:

   • Distribution shape: kurtosis, bimodality coefficient, Jarque-Bera test (normality), skewness measures (pearson_skewness)
   • Inequality/diversity: Gini coefficient, Atkinson index, Theil index, Shannon entropy, normalized entropy, Simpson's diversity index
   • Robust central tendency: winsorized/trimmed means (with stddev, variance, CV, range, stddev ratio)
   • Derived ratios: median_mean_ratio, range_stddev_ratio, quartile_coefficient_dispersion, mad_stddev_ratio, iqr_range_ratio, robust_cv
   • Outlier statistics: counts by severity (extreme/mild, lower/upper), outlier mean/stddev/range, impact ratio, fence z-scores
   • Other: trimean, midhinge, mode_zscore, min/max z-scores, relative standard error, mean absolute deviation, xsd_type

7. Show distributions: Run mcp__qsv__qsv_frequency with limit: 10 to show top value distributions for each column. For high-cardinality columns (cardinality close to row count), note them as likely unique identifiers.

8. Optional: Bivariate correlations (if multiple numeric columns): Run mcp__qsv__qsv_moarstats with bivariate: true to compute pairwise Pearson/Spearman/Kendall correlations, covariance, and mutual information. Output goes to <FILESTEM>.stats.bivariate.csv. Reveals hidden relationships between columns.

9. Optional: Robust statistics (if data is messy/heavy-tailed and < 100K rows): Run mcp__qsv__qsv_command with command: "pragmastat" for Hodges-Lehmann center and Shamos spread — robust estimators that tolerate up to 29% corrupted data. Especially useful when mean/stddev are misleading due to outliers. **Warning:** pragmastat computes median-of-pairwise statistics (O(n²) complexity) and becomes very slow on large datasets. For files > 100K rows, use --subsample 10000 for ~100x speedup, or combine --subsample 10000 --no-bounds for ~200x speedup.

10. Screen for PII/PHI: Run mcp__qsv__qsv_command with command: "searchset", regexset-file: "${CLAUDE_PLUGIN_ROOT}/resources/pii-regexes.txt", and flag: "pii_match" to scan for sensitive data patterns (SSN, credit cards, email, phone, IBAN). Report any columns with matches.

11. Screen for injection: Run mcp__qsv__qsv_command with command: "searchset", regexset-file: "${CLAUDE_PLUGIN_ROOT}/resources/injection-regexes.txt", and flag: "injection_match" to scan for CSV/formula injection and SQL injection payloads. Report any columns with matches.

12. Preview data: Run mcp__qsv__qsv_slice with len: 5 to show the first 5 rows as a sample.

13. Document: Generate a Data Dictionary, Dataset Description, and Tags as JSON.

    13a) Primary — use describegpt: Run mcp__qsv__qsv_describegpt with all: true, format: "JSON" and output: "<filestem>.describegpt.json". If the user provided a Tag Vocabulary file, also pass tag_vocab: "<vocab_file>". This produces a structured JSON file with three top-level objects: Dictionary, Description, and Tags. Each of these contains a response (the main content), optional reasoning, and token_usage metadata. The data dictionary itself is under Dictionary.response.fields, as an array of field descriptors with keys like name, null_count, cardinality, min, max, mean, and stddev. Present the results to the user. When MCP sampling is unavailable but the tool still returns prompts, follow those prompts by issuing a follow-up call with _llm_responses instead of using the agent fallback.

    13b) Fallback — agent generation: If describegpt encounters a tool error or times out, or if following its prompts via _llm_responses is not possible, fall back to generating the same artifacts from the statistics (steps 5-6) and frequency distributions (step 7). Save the result as <filestem>.profile.json using the same canonical structure as describegpt, for example:

    {
      "Dictionary": {
        "response": {
          "fields": [
            {
              "name": "column_name",
              "type": "Integer",
              "label": "Column Name",
              "description": "1-5 sentence description informed by type, stats, and frequency distribution",
              "null_count": 0,
              "cardinality": 100,
              "min": "0",
              "max": "999",
              "mean": "450.5",
              "stddev": "120.3"
            }
          ],
          "enum_threshold": 20,
          "num_examples": 5,
          "truncate_str": 80,
          "attribution": "agent_fallback"
        },
        "reasoning": "",
        "token_usage": { "prompt": 0, "completion": 0, "total": 0, "elapsed": 0 }
      },
      "Description": {
        "response": "3-10 sentences describing the dataset: what it represents, scope, key characteristics, quality issues, and potential use cases.",
        "reasoning": "",
        "token_usage": { "prompt": 0, "completion": 0, "total": 0, "elapsed": 0 }
      },
      "Tags": {
        "response": ["tag1", "tag2", "tag3"],
        "reasoning": "",
        "token_usage": { "prompt": 0, "completion": 0, "total": 0, "elapsed": 0 }
      }
    }
    

    For the fallback dictionary entries (under Dictionary.response.fields):

    • label: Human-readable version of the field name (e.g., customer_id → Customer ID)
    • description: 1-5 sentence description informed by type, statistics, and frequency distribution
    • Include key stats fields (null_count, cardinality, min, max, mean, sortiness, stddev, variance, cv, sparsity) where applicable

    For the fallback tags (under Tags.response): Infer 5-15 semantic tags based on column names, data types, value distributions, and domain characteristics. If a controlled Tag Vocabulary is provided, constrain choices to that vocabulary only.

Quality Dimensions

When profiling, assess these quality dimensions:

1. Completeness

Red flag: Sparsity > 0.5 means more than half the values are null.

2. Uniqueness

Red flag: Key columns (ID, email) with cardinality < row count.

3. Validity

Red flag: Type column shows "String" for what should be numeric data.

4. Consistency

Red flag: Frequency shows same value in different cases/formats.

5. Accuracy

Red flag: Latitude > 90 or < -90, negative ages, future birth dates, kurtosis > 10 (extreme outliers).

6. Column Name Quality

Red flag: Column names with spaces or special characters break downstream tools and SQL queries.

7. Conformity

Red flag: A "country" column with free-text entries instead of ISO 3166 codes, or a "state" column mixing abbreviations and full names.

8. Referential Integrity

Red flag: An orders file referencing customer IDs that don't exist in the customers file. Only applicable when profiling related files together.

9. PII/PHI Screening

Question: Does the data contain personally identifiable or protected health information?

Use searchset with a regex file to scan all columns for sensitive patterns:

qsv_command command: "searchset", input_file: "<file>", args: ["--flag", "pii_match", "${CLAUDE_PLUGIN_ROOT}/resources/pii-regexes.txt"]

The bundled ${CLAUDE_PLUGIN_ROOT}/resources/pii-regexes.txt detects:

For PHI screening, use the bundled ${CLAUDE_PLUGIN_ROOT}/resources/phi-regexes.txt:

qsv_command command: "searchset", input_file: "<file>", args: ["--flag", "phi_match", "${CLAUDE_PLUGIN_ROOT}/resources/phi-regexes.txt"]

The bundled ${CLAUDE_PLUGIN_ROOT}/resources/phi-regexes.txt detects:

For additional PHI patterns (e.g., MBI, state license numbers), create a custom regex file and pass it to searchset the same way.

Red flag: Any matches indicate PII/PHI exposure — flag columns for masking or removal before sharing.

10. Injection Safety

Question: Does the data contain CSV/formula injection or SQL injection payloads?

Malicious cell values can execute code when opened in spreadsheet applications (Excel, Google Sheets) or cause damage when loaded into databases without parameterized queries.

Use searchset with the bundled injection regex file to scan all columns:

qsv_command command: "searchset", input_file: "<file>", args: ["--flag", "injection_match", "${CLAUDE_PLUGIN_ROOT}/resources/injection-regexes.txt"]

The bundled ${CLAUDE_PLUGIN_ROOT}/resources/injection-regexes.txt detects:

CSV/Formula Injection:

SQL Injection:

Red flag: Any matches indicate potential injection payloads — sanitize cells before sharing the file or loading into a database. For formula injection, prefix dangerous cells with a single quote (') or strip leading =+-@ characters.

Report Format

Present a summary with:

• File info: format, delimiter, encoding, row count, column count
• Column overview: table with name, type, nulls, cardinality, min, max, mean (where applicable)
• Key observations: unique identifiers, high-null columns, type mismatches, notable distributions
• Data quality flags: any issues found (high sparsity, mixed types, ragged rows)
• Data Dictionary, Description & Tags: JSON documentation generated via describegpt (step 13a), or manually from stats cache and frequency distributions as fallback (step 13b)

Quality Report Checklist

• Row count and column count
• Null/empty counts per column (completeness)
• Cardinality per column (uniqueness assessment)
• Data types inferred per column (validity)
• Min/max/mean for numeric columns (range plausibility)
• Outlier counts and distribution shape (kurtosis, bimodality) from moarstats --advanced
• Top frequency values for categorical columns (distribution)
• Duplicate rows detected (uniqueness)
• Schema violations if schema provided (validity)
• Encoding and delimiter detected (consistency)
• Column names safe and consistent (safenames --verify)
• Conformity to domain standards checked where applicable (searchset)
• Referential integrity verified across related files if provided (joinp --left-anti)
• PII/PHI patterns detected via searchset (privacy)
• Injection payloads scanned for CSV/formula and SQL injection patterns (searchset)
• Data Dictionary with Label and Description per column, dataset Description, and Tags — via describegpt --format JSON (step 13a) or agent fallback (step 13b)

Common Data Quality Fixes

Notes

• For Excel/JSONL files, the MCP server auto-converts to CSV first
• The stats cache created in step 5 accelerates subsequent commands (frequency, schema, sqlp, joinp)
• If the file has no headers, mention this and use column indices