/explore-data - Profile and Explore a Dataset

If you see unfamiliar placeholders or need to check which tools are connected, see CONNECTORS.md.

Generate a comprehensive data profile for a table or uploaded file. Understand its shape, quality, and patterns before diving into analysis.

Usage

/explore-data <table_name or file>

Workflow

1. Access the Data

If a data warehouse MCP server is connected:

• Resolve the table name (handle schema prefixes, suggest matches if ambiguous)

• Query table metadata: column names, types, descriptions if available

• Run profiling queries against the live data

If a file is provided (CSV, Excel, Parquet, JSON):

• Read the file and load into a working dataset

• Infer column types from the data

If neither:

• Ask the user to provide a table name (with their warehouse connected) or upload a file

• If they describe a table schema, provide guidance on what profiling queries to run

2. Understand Structure

Before analyzing any data, understand its structure:

Table-level questions:

• How many rows and columns?

• What is the grain (one row per what)?

• What is the primary key? Is it unique?

• When was the data last updated?

• How far back does the data go?

Column classification — categorize each column as one of:

• Identifier: Unique keys, foreign keys, entity IDs

• Dimension: Categorical attributes for grouping/filtering (status, type, region, category)

• Metric: Quantitative values for measurement (revenue, count, duration, score)

• Temporal: Dates and timestamps (created_at, updated_at, event_date)

• Text: Free-form text fields (description, notes, name)

• Boolean: True/false flags

• Structural: JSON, arrays, nested structures

3. Generate Data Profile

Run the following profiling checks:

Table-level metrics:

• Total row count

• Column count and types breakdown

• Approximate table size (if available from metadata)

• Date range coverage (min/max of date columns)

All columns:

• Null count and null rate

• Distinct count and cardinality ratio (distinct / total)

• Most common values (top 5-10 with frequencies)

• Least common values (bottom 5 to spot anomalies)

Numeric columns (metrics):

min, max, mean, median (p50) standard deviation percentiles: p1, p5, p25, p75, p95, p99 zero count negative count (if unexpected)

String columns (dimensions, text):

min length, max length, avg length empty string count pattern analysis (do values follow a format?) case consistency (all upper, all lower, mixed?) leading/trailing whitespace count

Date/timestamp columns:

min date, max date null dates future dates (if unexpected) distribution by month/week gaps in time series

Boolean columns:

true count, false count, null count true rate

Present the profile as a clean summary table, grouped by column type (dimensions, metrics, dates, IDs).

4. Identify Data Quality Issues

Apply the quality assessment framework below. Flag potential problems:

• High null rates: Columns with >5% nulls (warn), >20% nulls (alert)

• Low cardinality surprises: Columns that should be high-cardinality but aren't (e.g., a "user_id" with only 50 distinct values)

• High cardinality surprises: Columns that should be categorical but have too many distinct values

• Suspicious values: Negative amounts where only positive expected, future dates in historical data, obviously placeholder values (e.g., "N/A", "TBD", "test", "999999")

• Duplicate detection: Check if there's a natural key and whether it has duplicates

• Distribution skew: Extremely skewed numeric distributions that could affect averages

• Encoding issues: Mixed case in categorical fields, trailing whitespace, inconsistent formats

5. Discover Relationships and Patterns

After profiling individual columns:

• Foreign key candidates: ID columns that might link to other tables

• Hierarchies: Columns that form natural drill-down paths (country > state > city)

• Correlations: Numeric columns that move together

• Derived columns: Columns that appear to be computed from others

• Redundant columns: Columns with identical or near-identical information

6. Suggest Interesting Dimensions and Metrics

Based on the column profile, recommend:

• Best dimension columns for slicing data (categorical columns with reasonable cardinality, 3-50 values)

• Key metric columns for measurement (numeric columns with meaningful distributions)

• Time columns suitable for trend analysis

• Natural groupings or hierarchies apparent in the data

• Potential join keys linking to other tables (ID columns, foreign keys)

7. Recommend Follow-Up Analyses

Suggest 3-5 specific analyses the user could run next:

• "Trend analysis on [metric] by [time_column] grouped by [dimension]"

• "Distribution deep-dive on [skewed_column] to understand outliers"

• "Data quality investigation on [problematic_column]"

• "Correlation analysis between [metric_a] and [metric_b]"

• "Cohort analysis using [date_column] and [status_column]"

Output Format

Data Profile: [table_name]

Overview

• Rows: 2,340,891
• Columns: 23 (8 dimensions, 6 metrics, 4 dates, 5 IDs)
• Date range: 2021-03-15 to 2024-01-22

Column Details

[summary table]

Data Quality Issues

[flagged issues with severity]

Recommended Explorations

[numbered list of suggested follow-up analyses]

Quality Assessment Framework

Completeness Score

Rate each column:

• Complete (>99% non-null): Green

• Mostly complete (95-99%): Yellow -- investigate the nulls

• Incomplete (80-95%): Orange -- understand why and whether it matters

• Sparse (<80%): Red -- may not be usable without imputation

Consistency Checks

Look for:

• Value format inconsistency: Same concept represented differently ("USA", "US", "United States", "us")

• Type inconsistency: Numbers stored as strings, dates in various formats

• Referential integrity: Foreign keys that don't match any parent record

• Business rule violations: Negative quantities, end dates before start dates, percentages > 100

• Cross-column consistency: Status = "completed" but completed_at is null

Accuracy Indicators

Red flags that suggest accuracy issues:

• Placeholder values: 0, -1, 999999, "N/A", "TBD", "test", "xxx"

• Default values: Suspiciously high frequency of a single value

• Stale data: Updated_at shows no recent changes in an active system

• Impossible values: Ages > 150, dates in the far future, negative durations

• Round number bias: All values ending in 0 or 5 (suggests estimation, not measurement)

Timeliness Assessment

• When was the table last updated?

• What is the expected update frequency?

• Is there a lag between event time and load time?

• Are there gaps in the time series?

Pattern Discovery Techniques

Distribution Analysis

For numeric columns, characterize the distribution:

• Normal: Mean and median are close, bell-shaped

• Skewed right: Long tail of high values (common for revenue, session duration)

• Skewed left: Long tail of low values (less common)

• Bimodal: Two peaks (suggests two distinct populations)

• Power law: Few very large values, many small ones (common for user activity)

• Uniform: Roughly equal frequency across range (often synthetic or random)

Temporal Patterns

For time series data, look for:

• Trend: Sustained upward or downward movement

• Seasonality: Repeating patterns (weekly, monthly, quarterly, annual)

• Day-of-week effects: Weekday vs. weekend differences

• Holiday effects: Drops or spikes around known holidays

• Change points: Sudden shifts in level or trend

• Anomalies: Individual data points that break the pattern

Segmentation Discovery

Identify natural segments by:

• Finding categorical columns with 3-20 distinct values

• Comparing metric distributions across segment values

• Looking for segments with significantly different behavior

• Testing whether segments are homogeneous or contain sub-segments

Correlation Exploration

Between numeric columns:

• Compute correlation matrix for all metric pairs

• Flag strong correlations (|r| > 0.7) for investigation

• Note: Correlation does not imply causation -- flag this explicitly

• Check for non-linear relationships (e.g., quadratic, logarithmic)

Schema Understanding and Documentation

Schema Documentation Template

When documenting a dataset for team use:

Table: [schema.table_name]

Description: [What this table represents] Grain: [One row per...] Primary Key: [column(s)] Row Count: [approximate, with date] Update Frequency: [real-time / hourly / daily / weekly] Owner: [team or person responsible]

Key Columns

Relationships

• Joins to users on user_id
• Joins to products on product_id
• Parent of event_details (1:many on event_id)

Known Issues

• [List any known data quality issues]
• [Note any gotchas for analysts]

Common Query Patterns

• [Typical use cases for this table]

Schema Exploration Queries

When connected to a data warehouse, use these patterns to discover schema:

-- List all tables in a schema (PostgreSQL) SELECT table_name, table_type FROM information_schema.tables WHERE table_schema = 'public' ORDER BY table_name;

-- Column details (PostgreSQL) SELECT column_name, data_type, is_nullable, column_default FROM information_schema.columns WHERE table_name = 'my_table' ORDER BY ordinal_position;

-- Table sizes (PostgreSQL) SELECT relname, pg_size_pretty(pg_total_relation_size(relid)) FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC;

-- Row counts for all tables (general pattern) -- Run per-table: SELECT COUNT(*) FROM table_name

Lineage and Dependencies

When exploring an unfamiliar data environment:

• Start with the "output" tables (what reports or dashboards consume)

• Trace upstream: What tables feed into them?

• Identify raw/staging/mart layers

• Map the transformation chain from raw data to analytical tables

• Note where data is enriched, filtered, or aggregated

Tips

• For very large tables (100M+ rows), profiling queries use sampling by default -- mention if you need exact counts

• If exploring a new dataset for the first time, this command gives you the lay of the land before writing specific queries

• The quality flags are heuristic -- not every flag is a real problem, but each is worth a quick look