Data Analysis

Data Analysis Skill (DuckDB Engine)

Wraps the Athena Data Engine (data_engine.py) for structured data analysis on large files.

Triggers

• User provides a JSON, CSV, or Parquet file for analysis
• "Analyze this data", "What's in this file", "Run some numbers on"
• Any file > 10MB that needs analytical queries
• /analyze workflow invocation

Dependencies

pip install duckdb

Core Scripts

Pipeline

Phase 1: Ingest

python3 .agent/scripts/data_engine.py ingest /path/to/data.json

This will:

1. Auto-detect format (JSON/CSV/Parquet)
2. For Telegram exports: flatten nested text fields, extract metadata
3. Convert to Parquet with ZSTD compression (cached alongside the original)
4. Print schema, row count, nulls, date range, value distributions

Cache behavior: If a Parquet cache already exists and is newer than the source file, ingestion is skipped and the cache is used directly (instant).

Phase 2: Query

python3 .agent/scripts/data_engine.py query /path/to/.athena_cache/parquet/data.parquet \
    "SELECT COUNT(*) FROM data WHERE text LIKE '%math%'"

The Parquet file is registered as table data. Use standard SQL.

Common patterns:

-- Row count
SELECT COUNT(*) FROM data

-- Date range
SELECT MIN(date), MAX(date) FROM data

-- Value distribution
SELECT column_name, COUNT(*) as cnt
FROM data
GROUP BY column_name
ORDER BY cnt DESC
LIMIT 20

-- Text search
SELECT date, text FROM data
WHERE text ILIKE '%keyword%'
LIMIT 10

-- Time series aggregation
SELECT strftime(date::TIMESTAMP, '%Y-%m') as month, COUNT(*) as volume
FROM data
GROUP BY month
ORDER BY month

-- Rate extraction (regex)
SELECT regexp_extract(text, '\$(\d+)', 1)::INT as rate, COUNT(*) as cnt
FROM data
WHERE rate IS NOT NULL
GROUP BY rate
ORDER BY cnt DESC

Phase 3: Extract & File

After analysis, file insights using:

python3 .agent/scripts/auto_file_insights.py \
    --title "Analysis Title" \
    --domain "Market Analysis" \
    --tags "#data-analysis #market" \
    --context "Brief context" \
    --findings "Finding 1: detail||Finding 2: detail" \
    --patterns "Pattern 1||Pattern 2" \
    --relevance "Active project relevance" \
    --base-dir "./"

Supported Formats

Parquet Cache

• Stored in .athena_cache/parquet/ alongside the source file
• ZSTD compression (5-10x smaller than JSON)
• Automatic cache invalidation if source file is modified
• Subsequent queries hit cache directly (millisecond latency)

Performance Characteristics

Anti-Patterns

• ❌ Loading entire JSON into Python memory (json.load() on 800MB files)
• ❌ Ad-hoc Python scripts for every new question
• ❌ Re-reading raw files on every query
• ❌ Regex extraction without profile step first

Reference

• CS-552: Tuition Market Data Archaeology — First use case that exposed the need for this skill