🍕 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.