Explore Data

What This Skill Does

• The Explore Data skill generates a comprehensive data profile for a given table or uploaded file.
• It helps users understand the data's structure, quality, and patterns before diving into analysis.
• This skill is ideal for data analysts and scientists who need to quickly assess a dataset.

When to Use

• Profile a table in a connected data warehouse.
• Explore a data file (CSV, Excel, Parquet, JSON).
• Understand the structure of a dataset.
• Identify the primary key of a table.
• Classify columns by type (identifier, dimension, metric, etc.).
• Assess data quality and identify potential issues.

Key Features

• Connects to data warehouses via MCP servers.
• Handles schema prefixes and suggests table matches.
• Queries table metadata (column names, types, descriptions).
• Infers column types from data files.
• Calculates table-level metrics (row count, column count, size).
• Generates data profiles with detailed statistics.

/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)