pizza-analytics

🍕 Pizza Analytics: Local dbt + DuckDB + FastMCP Pipeline

An end-to-end local data analytics stack featuring a dbt transformation pipeline on DuckDB, fully containerized with Docker, and exposed as a Model Context Protocol (MCP) server for local LLMs via LM Studio.

📐 Architecture Overview

Data Ingestion & Transformation: Raw CSV data is transformed using modular dbt models into Staging (stg_*), Fact (fct_*), and Dimension (dim_*) layers within a single-file DuckDB database (dev.duckdb).

MCP Tool Server: A Python-based FastMCP server connects to DuckDB in read-only mode to expose structured analytical tools over standard input/output (stdio).

LLM Orchestration: Local LLMs in LM Studio invoke these tools on demand to answer complex analytical questions with real-time database queries.

📁 Repository Structure

.

├── dbt_project/

│ ├── models/ # dbt transformation models (stg_*, fct_*, dim_*)

│ ├── seeds/ # Raw sample data CSVs

│ ├── dbt_project.yml # dbt project configurations

│ └── profiles.yml # DuckDB profile settings

├── Dockerfile # Container definition for dbt & Python environment

├── docker-compose.yml # Multi-container service orchestration

├── mcp_server.py # FastMCP server exposing database tools to LLMs

├── requirements.txt # Local Python dependencies for MCP host

├── .gitignore # Ignores compiled DuckDB binaries & temporary logs

└── README.md

⚡ Quickstart Guide

Prerequisites

Docker Desktop installed with WSL2 backend.

Python 3.10+ installed locally (for running the host MCP server).

LM Studio installed.

Step 1: Clone & Build Containers

Clone this repository to your local machine:

git clone https://github.com/YOUR\_USERNAME/pizza-analytics-mcp.git

cd pizza-analytics-mcp

Build and spin up the Docker services in the background:

docker compose up -d --build

Step 2: Build the dbt Data Pipeline

Run dbt seeds and models inside the container to build your local DuckDB database:

# Populate raw CSV seed files into DuckDB

docker compose exec dbt dbt seed

# Execute staging, fact, and dimension transformations

docker compose exec dbt dbt run

Note: This creates the database file at ./dbt_project/dev.duckdb.

Step 3: Install Local Dependencies for MCP

Install the required Python packages on your local host system so LM Studio can execute the MCP server script:

pip install -r requirements.txt

(Or install manually: pip install duckdb fastmcp pandas)

Step 4: Configure LM Studio

Open LM Studio and navigate to the MCP / Integrations settings.

Edit your mcp.json file to register the pizza-analytics server:

{

  "mcpServers": {

  "pizza-analytics": {

  "command": "python",

  "args": [

  "-u",

  "C:\\FULL\\PATH\\TO\\pizza-analytics-mcp\\mcp_server.py"

  ]

  }

  }

}

Note: Ensure you use the -u flag to disable Python's standard output buffering, and replace C:\\FULL\\PATH\\TO\\ with your exact absolute path.

Save the file and click Refresh in LM Studio. The tool indicators should turn green!

🛠️ Available MCP Tools

get_store_sales

Queries store-level performance metrics directly from fct_orders.

Parameters: store_id (optional string, e.g., "1")

Returns: JSON array with store_id, total_orders, and total_revenue.

get_total_sales

Queries overall performance metrics (total revenue and total pizza order counts across all stores) directly from fct_orders.

Parameters: None

Returns: JSON array with total_orders and total_revenue.

💡 Key Design & Troubleshooting Notes

DuckDB Concurrency & Locking: FastMCP connects to DuckDB using read_only=True and a Python with context manager. This ensures connections are released immediately after queries execute and prevents file lock deadlocks.

Serialization: Data types such as Decimal and Date from DuckDB queries are serialized using json.dumps(data, default=str) to ensure clean JSON transmission over stdio.

Speculative Decoding: When using local models (e.g., Gemma, Llama) with tool calling in LM Studio, disable Speculative Decoding / Draft Models in your model parameters to prevent context cache decoding crashes.

📄 License

This project is licensed under the MIT License - see the LICENSE file for details.