LLMs Are Good at SQL: Querying CI Logs at Scale

Imagine an AI agent that traces a flaky test to a dependency change three weeks prior—by writing its own SQL queries, scanning billions of log lines, and delivering results in seconds. This is now possible with a system that combines large language models (LLMs) and a high-performance SQL interface for continuous integration (CI) log analysis.

Why LLMs Excel at SQL for CI Logs

LLMs process natural language questions into precise SQL queries, leveraging their training on vast datasets. For CI logs, this means:

• Flexibility: No rigid APIs—agents ask questions we never anticipated.
• Speed: 1.5 billion log lines and 700K jobs processed weekly, all queryable in milliseconds.
• Depth: 52,000 queries across 8,500 investigations reveal patterns humans might miss.

How the SQL Interface Works

The system exposes two primary datasets:

Job Metadata

• 63% of queries use this compact, pre-aggregated view for questions like “What’s the failure rate?”
• Median rows scanned: 47K (fast, low-cost queries).

Raw Log Lines

• 37% of queries dive into raw logs for error messages, stack traces, and historical patterns.
• Median rows scanned: 1.1M (expensive but necessary for root-cause analysis).

Storage and Compression Magic

Denormalizing 48 metadata columns onto every log line seems risky—but ClickHouse’s columnar compression makes it efficient:

• Commit messages: 301:1 compression (same value repeated across thousands of lines).
• Job names: 48:1 compression (repeated metadata).
• Total storage: 154 GB on disk for 5.31 TB of raw data.

Query Performance at Scale

ClickHouse optimizations ensure speed:

• Primary key sorting: Data is physically ordered by organization and time, skipping irrelevant rows.
• Skip indexes: Bloom filters and ngram indexes avoid full-table scans for error searches.
• Materialized views: Pre-computed aggregations answer common questions instantly.

Real-World Impact

Agents follow a “broad-to-narrow” pattern:

1. Start with job metadata: “When did this workflow start failing?”
2. Drill into raw logs: “Show me the error stack trace.”
3. Scan billions of rows to trace patterns across months.

Key Takeaways

• LLMs paired with SQL interfaces unlock novel insights from CI logs.
• Denormalization and columnar compression make metadata-rich logs feasible.
• ClickHouse’s architecture handles 4.3 billion rows per session at P95.