| # Course Project: DataCrew |
|
|
| **A CLI tool that uses local LLMs and multi-agent systems to transform spreadsheets into intelligent PDF reports.** |
|
|
| --- |
|
|
| ## Overview |
|
|
| ``` |
| ┌─────────────────────────────────────────────────────────────────┐ |
| │ DataCrew CLI │ |
| ├─────────────────────────────────────────────────────────────────┤ |
| │ $ datacrew ingest sales_2024.xlsx │ |
| │ $ datacrew ask "What were the top 5 products by revenue?" │ |
| │ $ datacrew report "Q4 Executive Summary" --output report.pdf │ |
| └─────────────────────────────────────────────────────────────────┘ |
| ``` |
|
|
| --- |
|
|
| ## Architecture |
|
|
| ``` |
| CSV/XLSX ──► SQLite ──► MCP Server ──► Multi-Agent Crew ──► PDF Report |
| │ |
| ▼ |
| Docker Model Runner |
| (Local LLM) |
| ``` |
|
|
| ### Data Flow |
|
|
| ``` |
| ┌──────────────┐ ┌──────────────┐ ┌──────────────┐ |
| │ CSV/XLSX │────►│ SQLite │────►│ MCP Server │ |
| │ Files │ │ Database │ │ (Tools) │ |
| └──────────────┘ └──────────────┘ └──────┬───────┘ |
| │ |
| ▼ |
| ┌──────────────┐ ┌──────────────┐ ┌──────────────┐ |
| │ PDF Report │◄────│ Agent Crew │◄────│ Local LLM │ |
| │ Output │ │ (CrewAI) │ │ (Docker) │ |
| └──────────────┘ └──────────────┘ └──────────────┘ |
| ``` |
|
|
| --- |
|
|
| ## Agent System |
|
|
| | Agent | Role | Tools | Output | |
| |-------|------|-------|--------| |
| | **Data Analyst** | Understands schema, writes SQL queries | MCP Database Tools | Query results, data summaries | |
| | **Insights Agent** | Interprets results, finds patterns | Python Analysis, Statistics | Key findings, trends, anomalies | |
| | **Report Writer** | Creates narrative sections | LLM Generation | Executive summary, section text | |
| | **PDF Composer** | Formats and assembles final report | ReportLab/WeasyPrint | Formatted PDF document | |
|
|
| ### Agent Workflow |
|
|
| ``` |
| User Request: "Generate Q4 Executive Summary" |
| │ |
| ▼ |
| ┌─────────────────────┐ |
| │ Data Analyst │ |
| │ "What data do we │ |
| │ need for Q4?" │ |
| └──────────┬──────────┘ |
| │ SQL Queries |
| ▼ |
| ┌─────────────────────┐ |
| │ Insights Agent │ |
| │ "What patterns │ |
| │ emerge from │ |
| │ this data?" │ |
| └──────────┬──────────┘ |
| │ Key Findings |
| ▼ |
| ┌─────────────────────┐ |
| │ Report Writer │ |
| │ "Write narrative │ |
| │ sections for │ |
| │ each finding" │ |
| └──────────┬──────────┘ |
| │ Text Sections |
| ▼ |
| ┌─────────────────────┐ |
| │ PDF Composer │ |
| │ "Assemble into │ |
| │ formatted PDF" │ |
| └──────────┬──────────┘ |
| │ |
| ▼ |
| report.pdf |
| ``` |
|
|
| --- |
|
|
| ## CLI Commands |
|
|
| ### `datacrew ingest` |
|
|
| Ingest CSV or XLSX files into the local SQLite database. |
|
|
| ```bash |
| # Ingest a single file |
| datacrew ingest sales_2024.xlsx |
| |
| # Ingest with custom table name |
| datacrew ingest sales_2024.xlsx --table quarterly_sales |
| |
| # Ingest multiple files |
| datacrew ingest data/*.csv |
| |
| # Ingest with schema inference options |
| datacrew ingest sales.csv --infer-types --date-columns "order_date,ship_date" |
| ``` |
|
|
| **Options:** |
|
|
| | Flag | Description | Default | |
| |------|-------------|---------| |
| | `--table` | Custom table name | Filename (sanitized) | |
| | `--if-exists` | Behavior if table exists: `fail`, `replace`, `append` | `fail` | |
| | `--infer-types` | Automatically infer column types | `true` | |
| | `--date-columns` | Comma-separated list of date columns | Auto-detect | |
| | `--db` | Database file path | `./data/datacrew.db` | |
|
|
| ### `datacrew ask` |
|
|
| Query the database using natural language. |
|
|
| ```bash |
| # Simple query |
| datacrew ask "What were the top 5 products by revenue?" |
| |
| # Query with output format |
| datacrew ask "Show monthly sales trends" --format table |
| |
| # Query with export |
| datacrew ask "List all customers from California" --export customers_ca.csv |
| |
| # Interactive mode |
| datacrew ask --interactive |
| ``` |
|
|
| **Options:** |
|
|
| | Flag | Description | Default | |
| |------|-------------|---------| |
| | `--format` | Output format: `table`, `json`, `csv`, `markdown` | `table` | |
| | `--export` | Export results to file | None | |
| | `--explain` | Show generated SQL query | `false` | |
| | `--interactive` | Enter interactive query mode | `false` | |
| | `--limit` | Maximum rows to return | `100` | |
|
|
| ### `datacrew report` |
|
|
| Generate PDF reports using the multi-agent system. |
|
|
| ```bash |
| # Generate a report |
| datacrew report "Q4 Executive Summary" |
| |
| # Specify output file |
| datacrew report "Q4 Executive Summary" --output reports/q4_summary.pdf |
| |
| # Use a template |
| datacrew report "Monthly Sales" --template executive |
| |
| # Include specific analyses |
| datacrew report "Product Analysis" --include trends,comparisons,recommendations |
| ``` |
|
|
| **Options:** |
|
|
| | Flag | Description | Default | |
| |------|-------------|---------| |
| | `--output`, `-o` | Output PDF file path | `./report.pdf` | |
| | `--template` | Report template: `executive`, `detailed`, `minimal` | `executive` | |
| | `--include` | Analyses to include | All | |
| | `--date-range` | Date range for analysis | All data | |
| | `--verbose`, `-v` | Show agent reasoning | `false` | |
|
|
| ### `datacrew config` |
|
|
| Manage configuration settings. |
|
|
| ```bash |
| # Show current config |
| datacrew config show |
| |
| # Set LLM model |
| datacrew config set llm.model "llama3.2:3b" |
| |
| # Set database path |
| datacrew config set database.path "./data/mydata.db" |
| |
| # Reset to defaults |
| datacrew config reset |
| ``` |
|
|
| ### `datacrew schema` |
|
|
| Inspect database schema. |
|
|
| ```bash |
| # List all tables |
| datacrew schema list |
| |
| # Show table details |
| datacrew schema describe sales |
| |
| # Show sample data |
| datacrew schema sample sales --rows 5 |
| ``` |
|
|
| --- |
|
|
| ## Configuration |
|
|
| Configuration is stored in `~/.config/datacrew/config.toml` or `./datacrew.toml` in the project directory. |
|
|
| ```toml |
| [datacrew] |
| version = "1.0.0" |
| |
| [database] |
| path = "./data/datacrew.db" |
| echo = false |
| |
| [llm] |
| provider = "docker" # docker, ollama, openai |
| model = "llama3.2:3b" |
| temperature = 0.7 |
| max_tokens = 4096 |
| base_url = "http://localhost:11434" |
| |
| [llm.docker] |
| runtime = "nvidia" # nvidia, cpu |
| memory_limit = "8g" |
| |
| [agents] |
| verbose = false |
| max_iterations = 10 |
| |
| [agents.analyst] |
| role = "Data Analyst" |
| goal = "Analyze data and write accurate SQL queries" |
| |
| [agents.insights] |
| role = "Insights Specialist" |
| goal = "Find meaningful patterns and trends in data" |
| |
| [agents.writer] |
| role = "Report Writer" |
| goal = "Create clear, compelling narrative content" |
| |
| [agents.composer] |
| role = "PDF Composer" |
| goal = "Assemble professional PDF reports" |
| |
| [reports] |
| output_dir = "./reports" |
| default_template = "executive" |
| |
| [reports.templates.executive] |
| include_charts = true |
| include_recommendations = true |
| max_pages = 10 |
| |
| [reports.templates.detailed] |
| include_charts = true |
| include_recommendations = true |
| include_raw_data = true |
| max_pages = 50 |
| |
| [observability] |
| enabled = true |
| provider = "langfuse" # langfuse, langsmith, console |
| trace_agents = true |
| log_tokens = true |
| ``` |
|
|
| --- |
|
|
| ## Docker Stack |
|
|
| ### docker-compose.yml |
|
|
| ```yaml |
| version: "3.9" |
| |
| services: |
| # Local LLM via Docker Model Runner |
| llm: |
| image: ollama/ollama:latest |
| runtime: nvidia |
| environment: |
| - OLLAMA_HOST=0.0.0.0 |
| volumes: |
| - ollama_data:/root/.ollama |
| ports: |
| - "11434:11434" |
| deploy: |
| resources: |
| reservations: |
| devices: |
| - driver: nvidia |
| count: 1 |
| capabilities: [gpu] |
| healthcheck: |
| test: ["CMD", "curl", "-f", "http://localhost:11434/api/tags"] |
| interval: 30s |
| timeout: 10s |
| retries: 3 |
| |
| # MCP Server for database access |
| mcp-server: |
| build: |
| context: ./src/datacrew/mcp |
| dockerfile: Dockerfile |
| environment: |
| - DATABASE_PATH=/data/datacrew.db |
| - MCP_PORT=3000 |
| volumes: |
| - ./data:/data |
| ports: |
| - "3000:3000" |
| depends_on: |
| - llm |
| |
| # Main application (for containerized usage) |
| datacrew: |
| build: |
| context: . |
| dockerfile: Dockerfile |
| environment: |
| - LLM_BASE_URL=http://llm:11434 |
| - MCP_SERVER_URL=http://mcp-server:3000 |
| - DATABASE_PATH=/data/datacrew.db |
| volumes: |
| - ./data:/data |
| - ./reports:/reports |
| - ./input:/input:ro |
| depends_on: |
| llm: |
| condition: service_healthy |
| mcp-server: |
| condition: service_started |
| profiles: |
| - cli |
| |
| volumes: |
| ollama_data: |
| ``` |
|
|
| ### Running the Stack |
|
|
| ```bash |
| # Start LLM and MCP server |
| docker compose up -d llm mcp-server |
| |
| # Pull the model (first time only) |
| docker compose exec llm ollama pull llama3.2:3b |
| |
| # Run DataCrew commands |
| docker compose run --rm datacrew ingest /input/sales.xlsx |
| docker compose run --rm datacrew ask "What is total revenue?" |
| docker compose run --rm datacrew report "Sales Summary" -o /reports/summary.pdf |
| |
| # Or run locally with Docker backend |
| datacrew ingest sales.xlsx |
| datacrew ask "What is total revenue?" |
| datacrew report "Sales Summary" |
| ``` |
|
|
| --- |
|
|
| ## Project Structure |
|
|
| ``` |
| datacrew/ |
| ├── pyproject.toml # pixi/uv project config |
| ├── pixi.lock |
| ├── docker-compose.yml # Full stack orchestration |
| ├── Dockerfile |
| ├── datacrew.toml # Default configuration |
| ├── README.md |
| │ |
| ├── src/ |
| │ └── datacrew/ |
| │ ├── __init__.py |
| │ ├── __main__.py # Entry point |
| │ ├── cli.py # Typer CLI commands |
| │ ├── config.py # TOML configuration loader |
| │ │ |
| │ ├── ingestion/ # CSV/XLSX → SQLite |
| │ │ ├── __init__.py |
| │ │ ├── readers.py # File readers (pandas, openpyxl) |
| │ │ ├── schema.py # Schema inference |
| │ │ └── database.py # SQLite operations |
| │ │ |
| │ ├── query/ # Natural language queries |
| │ │ ├── __init__.py |
| │ │ ├── nl2sql.py # NL to SQL conversion |
| │ │ ├── executor.py # Query execution |
| │ │ └── formatter.py # Result formatting |
| │ │ |
| │ ├── agents/ # CrewAI agents |
| │ │ ├── __init__.py |
| │ │ ├── crew.py # Crew orchestration |
| │ │ ├── analyst.py # Data Analyst agent |
| │ │ ├── insights.py # Insights Specialist agent |
| │ │ ├── writer.py # Report Writer agent |
| │ │ └── composer.py # PDF Composer agent |
| │ │ |
| │ ├── tools/ # Agent tools |
| │ │ ├── __init__.py |
| │ │ ├── sql_tools.py # SQL execution tools |
| │ │ ├── analysis.py # Statistical analysis tools |
| │ │ └── charts.py # Chart generation tools |
| │ │ |
| │ ├── mcp/ # MCP server |
| │ │ ├── __init__.py |
| │ │ ├── server.py # MCP server implementation |
| │ │ ├── tools.py # MCP tool definitions |
| │ │ └── Dockerfile # MCP server container |
| │ │ |
| │ ├── reports/ # PDF generation |
| │ │ ├── __init__.py |
| │ │ ├── generator.py # Report generation orchestrator |
| │ │ ├── pdf.py # PDF creation (WeasyPrint) |
| │ │ ├── charts.py # Chart rendering |
| │ │ └── templates/ # HTML/CSS templates |
| │ │ ├── executive.html |
| │ │ ├── detailed.html |
| │ │ ├── minimal.html |
| │ │ └── styles.css |
| │ │ |
| │ ├── llm/ # LLM integration |
| │ │ ├── __init__.py |
| │ │ ├── client.py # LLM client (Docker/Ollama/OpenAI) |
| │ │ └── prompts.py # Prompt templates |
| │ │ |
| │ └── observability/ # Logging & tracing |
| │ ├── __init__.py |
| │ ├── tracing.py # Distributed tracing |
| │ └── metrics.py # Token/cost tracking |
| │ |
| ├── tests/ |
| │ ├── __init__.py |
| │ ├── conftest.py # Pytest fixtures |
| │ ├── test_cli.py |
| │ ├── test_ingestion.py |
| │ ├── test_query.py |
| │ ├── test_agents.py |
| │ ├── test_reports.py |
| │ └── fixtures/ |
| │ ├── sample_sales.csv |
| │ ├── sample_products.xlsx |
| │ └── expected_outputs/ |
| │ |
| ├── data/ # Local data directory |
| │ └── .gitkeep |
| │ |
| ├── reports/ # Generated reports |
| │ └── .gitkeep |
| │ |
| └── docs/ # Documentation (Quarto) |
| ├── _quarto.yml |
| ├── index.qmd |
| └── chapters/ |
| ``` |
|
|
| --- |
|
|
| ## Technology Stack |
|
|
| | Category | Tools | |
| |----------|-------| |
| | **Package Management** | pixi, uv | |
| | **CLI Framework** | Typer, Rich | |
| | **Local LLM** | Docker Model Runner, Ollama | |
| | **LLM Framework** | LangChain | |
| | **Multi-Agent** | CrewAI | |
| | **MCP** | Docker MCP Toolkit | |
| | **Database** | SQLite | |
| | **Data Processing** | pandas, openpyxl | |
| | **PDF Generation** | WeasyPrint | |
| | **Charts** | matplotlib, plotly | |
| | **Observability** | Langfuse, OpenTelemetry | |
| | **Testing** | pytest, DeepEval | |
| | **Containerization** | Docker, Docker Compose | |
|
|
| --- |
|
|
| ## Example Usage |
|
|
| ### End-to-End Workflow |
|
|
| ```bash |
| # 1. Start the Docker stack |
| docker compose up -d |
| |
| # 2. Ingest your data |
| datacrew ingest quarterly_sales_2024.xlsx |
| datacrew ingest product_catalog.csv |
| datacrew ingest customer_data.csv |
| |
| # 3. Explore with natural language queries |
| datacrew ask "How many records are in each table?" |
| datacrew ask "What are the top 10 products by revenue in Q4?" |
| datacrew ask "Show me the monthly sales trend for 2024" |
| |
| # 4. Generate a comprehensive report |
| datacrew report "2024 Annual Sales Analysis" \ |
| --template detailed \ |
| --output reports/annual_2024.pdf \ |
| --include trends,top_products,regional_breakdown,recommendations \ |
| --verbose |
| |
| # 5. View agent reasoning (verbose mode) |
| # [Data Analyst] Analyzing schema... found 3 tables |
| # [Data Analyst] Executing: SELECT strftime('%Y-%m', order_date) as month, SUM(revenue) ... |
| # [Insights Agent] Identified trend: 23% YoY growth in Q4 |
| # [Insights Agent] Anomaly detected: December spike in electronics category |
| # [Report Writer] Generating executive summary... |
| # [PDF Composer] Assembling 12-page report... |
| # ✓ Report saved to reports/annual_2024.pdf |
| ``` |
|
|
| ### Sample Report Output |
|
|
| ``` |
| ┌────────────────────────────────────────────────────────────────┐ |
| │ 2024 Annual Sales Analysis │ |
| │ Executive Summary │ |
| ├────────────────────────────────────────────────────────────────┤ |
| │ │ |
| │ Key Findings: │ |
| │ • Total revenue: $4.2M (+23% YoY) │ |
| │ • Top product category: Electronics (38% of revenue) │ |
| │ • Strongest region: West Coast (42% of sales) │ |
| │ • Customer retention rate: 78% │ |
| │ │ |
| │ [Monthly Revenue Trend Chart] │ |
| │ │ |
| │ Recommendations: │ |
| │ 1. Expand electronics inventory for Q1 2025 │ |
| │ 2. Increase marketing spend in Midwest region │ |
| │ 3. Launch loyalty program to improve retention │ |
| │ │ |
| └────────────────────────────────────────────────────────────────┘ |
| ``` |
|
|
| --- |
|
|
| ## Learning Outcomes |
|
|
| By building DataCrew, learners will be able to: |
|
|
| 1. ✅ Set up modern Python projects with pixi and reproducible environments |
| 2. ✅ Build professional CLI tools with Typer and Rich |
| 3. ✅ Run local LLMs using Docker Model Runner |
| 4. ✅ Ingest and query data from spreadsheets using natural language |
| 5. ✅ Build MCP servers to connect AI agents to data sources |
| 6. ✅ Design multi-agent systems with CrewAI |
| 7. ✅ Generate PDF reports programmatically |
| 8. ✅ Implement observability for AI applications |
| 9. ✅ Test non-deterministic systems effectively |
| 10. ✅ Deploy self-hosted AI applications with Docker Compose |
|
|
