Ecommerce MCP Server (BigQuery)

Python MCP server using the MCP Python SDK and google-cloud-bigquery, with guarded dry_run_sql and execute_sql tools.

It ships with an A/B benchmark that measures what live schema access is actually worth: the same model answering the same questions with structured MCP tools versus wiki-style documentation search. Results and methodology: docs/BENCHMARK_RESULTS.md.

Setup

python -m venv .venv
.\.venv\Scripts\activate   # Windows
pip install -e .

Configuration

Settings come from optional YAML (MCP_CONFIG_PATH), then environment variables override YAML (see mcp_server.config.Settings). A .env file in the working directory is also loaded when present.

Variable	Required	Description
GCP_PROJECT_ID	Yes	Project that runs BigQuery jobs (billing) when querying data. Alias: GOOGLE_CLOUD_PROJECT.
GOOGLE_APPLICATION_CREDENTIALS or GCP_CREDENTIALS_PATH	No	Path to service account JSON; omit to use ADC (e.g. gcloud auth application-default login).
BIGQUERY_DEFAULT_DATASET	No	Primary ecommerce project.dataset for unqualified names and schema FQNs (default in code: bigquery-public-data.thelook_ecommerce; override in YAML for your own dataset).
BIGQUERY_SUPPLEMENTAL_DATASET	No	Supplemental dataset (e.g. US ZCTA zip_codes), default bigquery-public-data.geo_us_boundaries.
BIGQUERY_EXECUTE_MAX_ROWS	No	Row cap for execute_sql (default 500, max 10000).
BIGQUERY_MAX_BYTES_BILLED	No	Optional bytes billed cap per execute job.
MCP_CONFIG_PATH	No	Path to YAML; keys mirror the table above in snake_case (gcp_project_id, gcp_credentials_path, default_dataset, supplemental_dataset, execute_max_rows, max_bytes_billed).

Example YAML: config/config.example.yaml. Copy it to config/config.yaml and set MCP_CONFIG_PATH to that path.

Credentials: config/config.yaml and config/*.json are gitignored — config.yaml holds your real project id, and service account keys must never be committed. Keep the key file outside the repository (e.g. ~/.gcp/) and point gcp_credentials_path / GOOGLE_APPLICATION_CREDENTIALS at that absolute path. Prefer ADC over a key file where you can.

Run (stdio)

set GCP_PROJECT_ID=your-project
thelook-mcp

Or: python -m mcp_server. (Installed console script names are thelook-mcp / thelook-benchmark; see pyproject.toml.)

Cursor MCP (stdio)

Cursor connects to MCP servers over stdio. After pip install -e ., add a server entry that runs this repo’s venv Python with -m mcp_server.

1. Open Cursor Settings → MCP (or edit the MCP config file Cursor shows there—often project .cursor/mcp.json or user-level MCP config).
2. Use absolute paths on Windows for command and credential files.

Example .cursor/mcp.json (adjust paths and project id):

{
  "mcpServers": {
    "ecommerce-bigquery": {
      "command": "C:\\path\\to\\Ecommerce-MCP-Server\\.venv\\Scripts\\python.exe",
      "args": ["-m", "mcp_server"],
      "env": {
        "GCP_PROJECT_ID": "your-gcp-project-id",
        "GOOGLE_APPLICATION_CREDENTIALS": "C:\\path\\to\\your-service-account.json",
        "MCP_CONFIG_PATH": "C:\\path\\to\\Ecommerce-MCP-Server\\config\\config.yaml"
      }
    }
  }
}

Omit GOOGLE_APPLICATION_CREDENTIALS if you rely on ADC for that environment. You can pass the same variables via env or rely solely on MCP_CONFIG_PATH plus GCP_PROJECT_ID in env.

Claude Desktop: Edit claude_desktop_config.json (see Claude Desktop MCP); use the same command, args, and env shape under mcpServers.

OpenAI Codex (CLI / IDE extension)

Codex discovers MCP servers from config.toml: default ~/.codex/config.toml, or a project .codex/config.toml in trusted projects. Stdio servers use command, optional args, and optional env. See Model Context Protocol – Codex.

1. pip install -e . in this repo and set GCP env vars (same as Cursor).
2. Add a [mcp_servers.<name>] block. Prefer hyphens in <name> (e.g. ecommerce-bigquery).

Example ~/.codex/config.toml (adjust paths; use your real venv python.exe on Windows):

[mcp_servers.ecommerce-bigquery]
command = "C:\\path\\to\\Ecommerce-MCP-Server\\.venv\\Scripts\\python.exe"
args = ["-m", "mcp_server"]

[mcp_servers.ecommerce-bigquery.env]
GCP_PROJECT_ID = "your-gcp-project-id"
GOOGLE_APPLICATION_CREDENTIALS = "C:\\path\\to\\your-service-account.json"
MCP_CONFIG_PATH = "C:\\path\\to\\Ecommerce-MCP-Server\\config\\config.yaml"

Or use the CLI: codex mcp add --help and codex mcp add (see the doc above). In the Codex TUI, /mcp lists active servers.

Gemini CLI

The Gemini CLI reads mcpServers from settings.json (user or project scope). It spawns stdio MCP processes the same way: command, args, env, optional cwd / timeout / trust. See MCP servers with the Gemini CLI.

Example (project .gemini/settings.json or user ~/.gemini/settings.json):

{
  "mcpServers": {
    "ecommerce-bigquery": {
      "command": "C:\\path\\to\\Ecommerce-MCP-Server\\.venv\\Scripts\\python.exe",
      "args": ["-m", "mcp_server"],
      "env": {
        "GCP_PROJECT_ID": "your-gcp-project-id",
        "GOOGLE_APPLICATION_CREDENTIALS": "C:\\path\\to\\your-service-account.json",
        "MCP_CONFIG_PATH": "C:\\path\\to\\Ecommerce-MCP-Server\\config\\config.yaml"
      }
    }
  }
}

You can also run gemini mcp add (see the doc). After discovery, tools are exposed to the model with a namespace prefix (e.g. mcp_ecommerce-bigquery_<toolname>); use /mcp in the CLI to inspect connection and tools.

GCP IAM and BigQuery

BigQuery jobs are created in your GCP project (that project is billed for bytes processed). Tables may live in bigquery-public-data or in your own project.dataset (e.g. a linked or copied ecommerce dataset); you do not grant your service account on Google’s hosting project when querying public datasets—public datasets are queried from your project using your identity.

1. Enable the API: In Google Cloud Console, select your project → APIs & Services → enable BigQuery API.

2. Billing: Ensure the project has a billing account attached if your organization requires it for BigQuery jobs.

3. Service account (recommended for servers and benchmarks): IAM & Admin → Service Accounts → Create, then Keys → Add key → JSON for a key file used by GOOGLE_APPLICATION_CREDENTIALS / GCP_CREDENTIALS_PATH.

4. Roles on your project (principal = that service account; scope = your project):

   • BigQuery Job User (roles/bigquery.jobUser) — create and run query jobs in your project.
   • BigQuery Data Viewer (roles/bigquery.dataViewer) — read dataset metadata and tables your jobs need (including public datasets referenced from those jobs).

   For interactive development only, your user account can use the same roles instead of a key file, with Application Default Credentials after gcloud auth application-default login.

5. Least privilege: Do not grant bigquery.admin, owner, or write roles unless you have a separate requirement. This server only issues read-only SELECT paths after validation.

Official references: BigQuery IAM roles, Querying public datasets.

Tests

pip install -e ".[dev]"
pytest

The suite runs fully offline — no GCP credentials or network needed — and covers the read-only SQL guard (DML/DDL rejection, stacked-statement injection), benchmark scoring, and task/schema integrity. CI runs it on Python 3.10, 3.11, and 3.12.

Documentation

• docs/BENCHMARK_RESULTS.md — A/B results, methodology, and the benchmark defects found and fixed along the way.
• docs/PROJECT_GUIDE.md — repository layout, architecture, data flow, benchmark runner, and glossary (MCP, BigQuery, wiki baseline vs MCP tools).

Benchmarks (optional)

pip install -e ".[benchmark]"
set GEMINI_API_KEY=...   # default LLM; see table below for OpenAI
set GCP_PROJECT_ID=...
thelook-benchmark --condition both

See docs/PROJECT_GUIDE.md for architecture and glossary; benchmarks/README.md and benchmarks/report_template.md for runner details and summarizing a run.

Results summary

Three trials, 22 tasks, both arms, gemini-3.1-flash-lite-preview at temperature 0. Correctness is measured by executing the model's SQL and the task's golden_sql and comparing result sets — not by whether the query parses.

	baseline (wiki)	mcp (live tools)
correct	53/65 (82%)	63/66 (95%)
— staleness tasks	3/9 (33%)	9/9 (100%)
— structural tasks	42/45 (93%)	44/45 (98%)
context tokens (total)	287,137	183,601 (−36%)
context worst case	18,604	10,815
wall clock (median)	3.2s	6.0s

Read that as a narrow claim: on structural questions where the documentation is accurate, reading docs works about as well as querying the warehouse. The advantage concentrates in stale documentation — values that were right when written and since changed. MCP is also slower per task. Full methodology, per-task failures, caveats, and the benchmark defects found and fixed along the way: docs/BENCHMARK_RESULTS.md.

Reproducing A/B (baseline vs MCP) results

The runner compares baseline (BigQuery dialect + internal wiki simulation: search and fetch over in-memory pages built from the same schema definitions—no structured catalog API, no in-loop BigQuery tools) and MCP (list_tables, describe_table, suggest_joins, plus dry_run_sql and execute_sql, with prompts that require dry-run before finalizing). Gemini or OpenAI via --provider. Validation still dry-runs (and optionally executes) the model’s final SQL with the same BigQuery settings as the live server.

Prerequisites

• pip install -e ".[benchmark]" (installs google-genai and openai).
• Gemini (default): GEMINI_API_KEY or GOOGLE_API_KEY from Google AI Studio.
• OpenAI: set BENCHMARK_PROVIDER=openai and OPENAI_API_KEY, or pass --provider openai.
• Same GCP setup as above: GCP_PROJECT_ID and credentials unless BENCHMARK_SKIP_BIGQUERY=1 (then validation is mostly parse-only).

Environment variables

Variable	Purpose
GEMINI_API_KEY or GOOGLE_API_KEY	Gemini API key (default --provider gemini).
OPENAI_API_KEY	Required when --provider openai.
BENCHMARK_PROVIDER	gemini or openai (default gemini).
BENCHMARK_MODEL	Overrides default model if you omit --model (Gemini default: gemini-3.1-flash-lite-preview; OpenAI default: gpt-4o-mini).
BENCHMARK_SKIP_BIGQUERY	Set to 1 / true to skip constructing a BigQuery client (no dry-run/execute validation against GCP).
GCP_PROJECT_ID, credentials	Same as MCP server; used only when BigQuery is not skipped.

Example: full A/B with Gemini + validation

set GEMINI_API_KEY=your-key
set GCP_PROJECT_ID=your-gcp-project-id
set GOOGLE_APPLICATION_CREDENTIALS=C:\path\to\key.json
thelook-benchmark --condition both --temperature 0 --output-dir benchmarks\results

Example: OpenAI instead

set OPENAI_API_KEY=sk-...
set BENCHMARK_PROVIDER=openai
thelook-benchmark --condition both

Reproducibility notes

• Default --temperature is 0 for minimal sampling variance; some APIs may still show rare drift.
• Pin --model and --tasks to a fixed file so comparisons across machines use the same task set and endpoint.
• Each run writes a unique run_id (timestamp + short uuid) to benchmarks/results/<run_id>_results.json and <run_id>_summary.csv. To compare arms, filter the CSV by condition (baseline vs mcp) or load the JSON traces.
• For a deterministic, no-LLM check that tasks and BigQuery connectivity are valid, run:
  thelook-benchmark --validate-golden-only (uses golden_sql from benchmarks/tasks.yaml when present).
• golden_sql is written against {dataset} / {supplemental_dataset} placeholders, resolved at load time from default_dataset / supplemental_dataset. Point those at your warehouse (a copy or linked ecommerce dataset) and the golden queries validate without editing the task file.
• The plan suggests repeating each condition several times and taking a median for wall-clock if you need stable timing numbers under load.

Useful flags (full list: thelook-benchmark --help)

Flag	Meaning
--provider	gemini or openai (default: gemini; env BENCHMARK_PROVIDER).
--condition	baseline, mcp, or both (A/B uses both).
--temperature FLOAT	Passed through to the chat API (default 0).
--model NAME	Overrides BENCHMARK_MODEL.
--tasks PATH	Alternate task YAML.
--max-tasks N	Run only the first N tasks.
--max-tool-rounds N	Cap tool rounds per task for both baseline (wiki) and MCP (default 24).
--no-execute	Dry-run validation only; skip execute step.
--output-dir DIR	Where JSON and CSV are written.

License

MIT