Profile and explore a dataset to understand its shape, quality, and patterns. Use when encountering a new table or file, checking null rates and column distributions, spotting data quality issues like duplicates or suspicious values, or deciding which dimensions and metrics to analyze.

/explore data Profile and Explore a Dataset If you see unfamiliar placeholders or need to check which tools are connected, see [CONNECTORS.md](../../CONNECTORS.md). Generate a comprehensive data profile for a table or uploaded file. Understand its shape, quality, and patterns before diving into analysis. Usage Workflow 1. Access the Data If a data warehouse MCP server is connected: 1. Resolve the table name (handle schema prefixes, suggest matches if ambiguous) 2. Query table metadata: column names, types, descriptions if available 3. Run profiling queries against the live data If a file is provided (CSV, Excel, Parquet, JSON): 1. Read the file and load into a working dataset 2. Infer column types from the data If neither: 1. Ask the user to provide a table name (with their warehouse connected) or upload a file 2. 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): String columns (dimensions, text): Date/timestamp columns: Boolean columns: 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 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: Schema Exploration Queries When connected to a data warehouse, use these patterns to discover schema: Lineage and Dependencies When exploring an unfamiliar data environment: 1. Start with the "output" tables (what reports or dashboards consume) 2. Trace upstream: What tables feed into them? 3. Identify raw/staging/mart layers 4. Map the transformation chain from raw data to analytical tables 5. 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

explore-data