Building a Unified Data Platform and AI Agent Lessons from Processing 1B+ Events/Second

The Data Sprawl Problem Every Hyper-Growth Company Faces

If you've worked at a company that scaled rapidly, you know the pain. Data lives everywhere: production databases, ClickHouse clusters, Kafka streams, object storage, and a long tail of pipelines. Each system has its own credentials, query language, and retention policy. Asking a simple question like 'How many domains that signed up today are in the Top 100 by traffic?' requires tribal knowledge—knowing which system to query, what joins to make, and whether the data is fresh or sampled.

Cloudflare faced this exact challenge while processing over a billion events per second across 330+ cities. Their solution wasn't another BI tool—it was a complete rethinking of their data infrastructure. They built two things: Town Lake, a unified SQL interface to everything Cloudflare knows, and Skipper, an AI agent that lets anyone ask questions in plain English and get auditable answers in seconds.

This is a deep dive into how they built both, the architectural decisions that matter, and the hard-won lessons that apply to any serious data platform effort.

Town Lake: The Data Lakehouse Architecture

Town Lake is built on a data lakehouse pattern: a query engine reading from object storage, with a metadata layer making storage behave like a database. The core components are:

Query Engine: Apache Trino

Trino lets a single SQL query join a Postgres table, a ClickHouse table, and an Iceberg table on R2 without materializing intermediate results. A query like 'top 100 paying customers by Workers requests this week' compiles into a plan that pushes filters into ClickHouse, joins account dimensions in Postgres, and ranks against billing rollups in R2—all in one go.

Storage: R2 + Apache Iceberg

Cold and warm data lives in R2 via their managed Iceberg service. Iceberg provides schema evolution, time travel, and partition evolution. The key insight is tiered compaction: per-minute data becomes hourly, then daily, as it ages. Storage costs decrease with data age while maintaining queryability.

Metadata: DataHub

Every table, column, owner, and lineage edge lives in DataHub. When Skipper needs to understand dim.accounts, it pulls the schema, column descriptions, owning team, and upstream/downstream dependencies.

Access Control: Lifeguard

Lifeguard stores access rules in D1, dynamically pulls group membership, and renders a combined JSON policy that Trino reads over HTTP. The critical decision: default-closed governance. Tables are inaccessible until reviewed.

-- Example: Querying Town Lake with automatic PII redaction
-- PII columns are redacted by default unless session flag is enabled
SET SESSION townlake.redact_pii = TRUE;

SELECT account_id, email, usage_amount
FROM townlake.fct.billings_allocated
WHERE date >= CURRENT_DATE - INTERVAL '30' DAY
ORDER BY usage_amount DESC
LIMIT 10;
-- email column will be redacted (e.g., '***@***.com')

PII Detection: Skimmer

Skimmer continuously samples rows from every column and uses Workers AI to classify PII in two passes. First, a fast per-column classifier; then, if flagged, an agentic second pass with full table context. Findings flow into DataHub and Lifeguard's allowlist for human review.

Skipper: The AI Data Agent

Skipper is a conversational AI agent that goes from natural language to validated answers, grounded in actual data, code, and institutional knowledge. The architecture uses Workers, Workers AI, Durable Objects, and D1.

Five Layers of Context (The Secret Sauce)

The biggest challenge was preventing hallucinated joins and wrong answers. The solution is layered context:

1. Schema & usage metadata from DataHub
2. Human annotations like 'One row per account_id'
3. Code-derived knowledge: the actual SQL that produces a table (e.g., alloc_amount = billed_amount / 12 for annual)
4. Curated data models: human-written documents describing how to think about billing, customers, etc.
5. Runtime introspection: DESCRIBE table, SELECT DISTINCT col LIMIT 20 as a safety net

Code Mode: Rethinking MCP Tools

Instead of exposing 30 individual tools, Skipper exposes two: search and execute. The model writes JavaScript that calls the entire toolset programmatically:

// Example: Multi-step data analysis in a single round-trip
const datasets = await skipper.search_datasets({ query: "billing product revenue" });
const queryId = await skipper.start_query({ 
  sql: "SELECT region, SUM(usage_amount) FROM fct.billings_allocated GROUP BY region" 
});
const results = await skipper.fetch_results({ queryId, mode: "inject" });
return skipper.create_chart({ chartType: "bar", data: results.rows });

This JavaScript runs in a sandboxed Dynamic Worker isolate. Complex workflows happen in a single round-trip, are faster and cheaper, and are auditable as code.

Key Lessons and Limitations

What Worked

• Less prompting is more: Prescriptive system prompts made quality worse. High-level guidance lets the model reason better.
• Tool overlap is poison: Consolidate tools; every tool should have a single reason to exist.
• Code, not metadata, captures meaning: The biggest accuracy wins came from ingesting the actual SQL that produces tables.
• Memory matters: A memory layer for recurring corrections makes the agent monotonically better.

Limitations and Considerations

• Boring infrastructure is the hard part: Per-row access control, default-closed allowlisting, and audit logging are what make a data platform safe. Don't skip these.
• Runtime context is expensive: Live introspection queries cost money and time; use them sparingly.
• Security model = data model: Everything Skipper does runs as the calling user. No privilege escalation, period.

Next Steps for Your Learning

1. Start with a small lakehouse: Set up Trino + Iceberg on object storage. Get comfortable with the query engine.
2. Implement default-closed governance: Build automated PII detection and table allowlisting from day one.
3. Layer context for your AI agent: Start with schema metadata, then add human annotations, then code-derived knowledge.
4. Study the MCP pattern: The Code Mode approach is a novel way to reduce round-trips in agentic workflows.

For more on large-scale data infrastructure, check out this guide on migrating petabyte-scale ingestion systems. And if you're building AI agent interfaces, this analysis of Vercel's Chat SDK adapter directory offers useful patterns.