Chat2DuckDB Data Analysis

The Core Problem Being Solved

Data analysis often begins with an unfamiliar file (e.g., CSV, Excel), where engineers face common pain points:
- Inefficient Manual Exploration: Obtaining basic statistics requires writing custom scripts or recalling cumbersome library functions.
- Error-Prone SQL Writing: Manual queries are susceptible to column name typos and syntax oversights (e.g., extra semicolons), leading to repeated debugging.
- Lack of Validation Workflow: Executing complex queries or working directly on large datasets carries risk, with no lightweight mechanism for sampling-based validation.

The Intelligent Data Analysis skill aims to provide an automated analysis flow—from "understanding data" to "gaining insights"—using the DuckDB engine and a natural language interface.

How the Skill Works: Core Capabilities and Key Steps

This skill is built around the SQL analytics capabilities of DuckDB. Its core workflow is as follows:

1. Data Registration and Exploration

• Data files in formats like CSV, JSON, Parquet, etc., are registered as a data table in DuckDB using Pandas.
• The describe mode generates a complete data profile, including schema, numerical statistics, categorical distributions, date ranges, and missing value counts.

2. Intelligent Query Generation and Execution

• Natural Language to SQL: Converts a user's analytical intent (e.g., "calculate average price by category") directly into a corresponding SELECT statement.
• Automatic SQL Correction Engine:
• Fixes syntax errors (e.g., extra semicolons, unmatched quotes).
• Intelligently corrects column name errors based on edit distance (e.g., matching categroy to category).
• Normalizes SQL keyword casing and Chinese punctuation.
• Retries and Sampling: Automatically retries failed queries (default 3 times). Supports quick logic validation on a small sample via the --sample_fraction parameter.

3. Result Analysis and Persistence

• Outputs the result table, execution time, and retry count.
• Generates business insights based on the results (e.g., percentage contributions, trends).
• Optionally persists analysis results or intermediate tables to a DuckDB database file (via --persist_db_path), facilitating subsequent multi-table joins.

Applicability Boundaries and Key Caveats

This skill is focused on the DuckDB SQL dialect and is not compatible with proprietary syntax from other databases like MySQL or PostgreSQL. Pay special attention to:
- Explore Before Querying: Always recommend using the describe mode to understand the data structure and quality before constructing queries.
- Validate Complex Queries: For complex queries involving heavy computation or joins, always validate first using sampling parameters (e.g., --sample_fraction 0.1).
- Resource Constraints: When working with very large datasets, combine queries with LIMIT clauses or aggregations to avoid memory issues. Pandas is used only for initial file reading—all analytical computations must be performed via DuckDB SQL to ensure performance and consistency.
- Handling Special Field Names: Fields containing spaces, hyphens, or Chinese characters must be enclosed in double quotes in SQL (e.g., "销售-额"). The skill engine will attempt to auto-correct cases where quotes are omitted.

Adhering to these practices enables an efficient and reliable end-to-end flow from raw data to analytical conclusions.