deep-profile

Files (1)

• SKILL.md

SKILL.md (first lines)

# Skill: Data Profiling

## Model conventions

This skill is version-aware. Before starting, apply `skills/MODEL_CONVENTIONS.md` for the model you are: query data rather than inferring numbers (§B, §E), follow instruction scope literally (§A), let response length follow task complexity (§C), and run intelligence-sensitive analysis at high/xhigh effort (§D).

## Purpose
Deep-profile the active dataset to understand schema structure, value distributions, temporal patterns, correlations, completeness gaps, and anomalies. Produces a comprehensive profile report that serves as the foundation for analysis planning and data quality assessment.

## When to Use
- After connecting a new dataset (post-bootstrap, pre-analysis)
- Before the first analysis on any dataset
- When explicitly invoked by the user
- When the existing profile is stale (check `last_profiled` in manifest.yaml)

## Instructions

### Step 0: Check for an Existing Profile

Before profiling anything, check `.knowledge/datasets/{active}/last_profile.md` and
`last_profiled` in manifest.yaml. If a profile exists and is fresh (data hasn't been
refreshed since), report from it instead of re-profiling. If it's stale, scope the re-run:
profile only the tables that changed or that the user asked about, and carry the rest
forward from the existing report. Run the full unconditional profile only on a first run
or an explicit "re-profile everything."

### Step 1: Connect and Profile Schema

```python
from helpers.data_helpers import get_connection_for_profiling
from helpers.schema_profiler import profile_source

# Get connection (auto-detects DuckDB vs CSV from active dataset)
conn_info = get_connection_for_profiling()

# Run full schema profile — introspects all tables: column names, types,
# nullability, row counts, sample values, basic statistics, date detection
schema = profile_source(conn_info)
```

Record the output. `schema` contains the full table inventory with column-level metadata. Use this to identify:
- Which tables exist and their row counts
- Which columns are date columns (for temporal analysis in Step 2)
- Which columns are numeric (for distribution and correlation analysis)
- Which columns have nulls (for completeness deep-dive in Step 2)

### Step 2: Run Deep Profiling per Table

For each table in the schema, load the data and run the deep profiling functions. Prioritize tables with the most rows and the most date/numeric columns.

```python
from helpers.data_helpers import read_table
from helpers.deep_profiler import (
    profile_distributions,
    profile_temporal_patterns,
    profile_completeness,
)

for table_info in schema["tables"]:
    table_name = table_info["name"]
    df = read_table(table_name)

    # Distribution analysis on all numeric columns
    distributions = profile_distributions(df)

    # Completeness assessment — null rates, zeros, empty strings, constant cols
    completeness = profile_completeness(df)

    # Temporal pattern analysis (only if the table has date columns)
    temporal = None
    if table_info.get("date_columns"):
        primary_date = table_info["date_columns"][0]
        temporal = profile_temporal_patterns(df, primary_date, freq="D")
```

**Important:** For large tables (>50K rows), `profile_source()` already samples. But `read_table()` loads the full CSV. If a table has >50K rows, sample before running deep profiling:

```python
if len(df) > 50_000:
    df = df.sample(n=50_000, random_state=42)
```

Load each table **once** — run distributions, completeness, temporal (and Step 3's
correlations/anomalies, for key tables) on the same `df` in the same loop iteration.
Never `read_table()` the same table a second time in a later step.

### Step 3: Correlation and Anomaly Analysis on Key Tables

Run correlation and anomaly detection on tables that contain key business metrics (revenue, counts, rates). Identify these tables by looking for columns with names like `revenue`, `amount`, `total`, `count`, `rate`, `price`, `quantity`.

```python
from helpers.deep_profiler import profile_correlations, profile_anomalies

# Correlations — find relationships between numeric columns
correlations = profile_correlations(df, threshold=0.5)

# Anomaly detection — requires a date column and pre-aggregated data
# Aggregate to daily granularity first if the table has event-level rows
if table_info.get("date_columns"):
    primary_date = table_info["date_columns"][0]
    # Only run on tables with a clear date + metric pattern
    metric_cols = [c for c in df.select_dtypes(include="number").columns
                   if c not in ("id", table_name.rstrip("s") + "_id")]
    if metric_cols:
        # Aggregate to daily for anomaly detection
        daily = df.groupby(pd.to_datetime(df[primary_date]).dt.date)[metric_cols].sum().reset_index()
        daily.rename(columns={daily.columns[0]: primary_date}, inplace=True)
        anomalies = profile_anomalies(daily, date_col=primary_date,
                                       metric_cols=metric_cols, window=14)
```

### Step 4: Generate Profile Report

Write the full profile report to `<workspace>/knowledge/datasets/{active}/last_profile.md`. Use `schema_to_markdown()` for the schema portion, then append the deep profiling results.

```python
from helpers.data_helpers import schema_to_markdown, detect_active_source

source = detect_active_source()

From Amar1404/AI_ANALYST, MIT.

More from Amar1404/AI_ANALYST