lakehouse-to-agent

A customer's lakehouse data, end to end to an AI agent: public data lands as Parquet, is registered as an Apache Iceberg table, is queried with DuckDB SQL, and is served to Claude over MCP with per-caller tokens, read-only enforcement and an audit log.

Built in a day as the integration pattern a data provider's solutions team runs after signature: catalog access, identity-bound consumption, an agent on top. Everything runs locally and free; no cloud account.

What it shows

Related MCP server: MCP Server for Snowflake

Results from the hand-written queries (queries.sql, data through Sep 2026)

• Fastest year-over-year growth: gasoline stations, +28.1% ($68.0B vs $53.1B); health and personal care is the only decline, -1.2%.

• Trailing-12-month share: nonstore (e-commerce) retailers 22.3%, the largest category.

• Nonstore sales first exceeded department stores in April 2020.

• Time travel: snapshot 1 holds 3,320 rows through Jul 2026; the current snapshot holds 3,336 through Sep 2026. Asked through MCP, Claude reported gasoline stations as fastest-growing in both: +17.4% YoY as of Jul 2026 (snapshot 1), +28.1% as of Sep 2026.

Run it

python3 -m venv .venv && .venv/bin/pip install "pyiceberg[sql-sqlite,pyarrow]" duckdb "mcp[cli]==2.3.0" pytest
.venv/bin/python ingest.py
PYTHONPATH=. .venv/bin/python -m pytest -q          # 10 tests
.venv/bin/python server.py add-token acme-analyst    # prints the token once; only its hash is stored

Connect Claude Code (the token goes in the server's environment, never in a prompt):

claude mcp add lakehouse --env LAKE_TOKEN=<token> -- "$PWD/.venv/bin/python" "$PWD/server.py"
claude "Which U.S. store category grew fastest year over year in the latest month, and how did it differ in the earlier Iceberg snapshot? Show the SQL and cite the rows."

Revoke a caller without touching anyone else: .venv/bin/python server.py revoke acme-analyst.

Integration runbook (how a customer would run this against their own lake)

1. Catalog: point lake.catalog() at the customer's Iceberg REST catalog (Polaris, Glue, Unity) instead of the local SQLite one; table identifiers stay the same.

2. Identity: issue one token per consuming user or service, never a shared one. In production, swap the static tokens for the customer's IdP (OIDC) so access follows their SSO and offboarding.

3. Least privilege: the server is read-only at two layers (statement guard, and an in-memory DuckDB over an Arrow scan that cannot write back to the lake).

4. Audit: ship audit.jsonl to the customer's SIEM; each line has caller, tool, SQL, snapshot and row count, and rejected statements are logged too.

5. Reproducibility: answers cite a snapshot_id, so a number in a report can be re-run against the exact data it came from.

Found while testing

• Iceberg snapshot ids (e.g. 7514425504626583672) exceed JSON's exact-integer range (2^53). Sent as a number over MCP, the id was rounded to ...583552, and time travel missed. The server now returns and accepts snapshot ids as strings (regression test included).

• SQL that starts with a -- comment was rejected by the read-only guard; agents write that often. Comments are stripped before the check.

Limits

• Local catalog and files; not tested against a hosted Iceberg catalog, Snowflake or Databricks.

• Static tokens over stdio stand in for OAuth; a hosted deployment would put the server behind the customer's IdP.

• Public, monthly, national data; not row-level or store-level.

MIT license.