OmniQuery AI: Autonomous Real-Time Data Analyst

Natural language analytics across 30,000+ film and streaming telemetry records using ClickHouse Cloud on GCP and Gemini 3.6 Flash.

Comment

The Problem

Film studios and streaming platforms operate on fundamentally different datasets that rarely connect:

  • Theatrical box office financials (budgets, gross receipts, territory distribution).
  • Video platform telemetry (CDN latency percentiles, error rates, microservice health).
  • Audience feedback (unstructured text reviews, sentiment, pacing/VFX critiques).

When a distribution manager wants to know "Which movie genre yielded the highest net profit across European screens in Q2?" or a platform engineer needs to inspect "95th percentile streaming latency and error counts per service endpoint", they usually have to write dialect-specific SQL by hand or file a request with data engineering.

We wanted to see if we could combine ClickHouse's speed with Gemini's reasoning to make querying these operational datasets as simple as asking a question in plain English—with results and charts returned in roughly 100 to 200 milliseconds.

What We Built

OmniQuery AI is a lightweight analytics studio that translates natural language prompts into optimized ClickHouse SQL, executes them against a live ClickHouse Cloud instance on Google Cloud Platform, and renders publication-ready charts and summaries in real time.

Key capabilities:

  1. ClickHouse-Native SQL Generation: Uses Gemini 3.6 Flash and the Google Agent Development Kit (ADK) to generate valid ClickHouse SQL using native aggregation functions like quantileExact(0.95)(latency_ms), countIf(status_code >= 400), and vector distance metrics (cosineDistance).
  2. Schema Introspection via FastMCP: Introspects live database schemas through Model Context Protocol (FastMCP) tools before building queries.
  3. Autonomous Self-Healing: If a query fails because of a typo, missing column, or bad group-by, the backend captures the database exception, feeds the error back to the model, and automatically repairs the SQL in a 3-step loop.
  4. Defensive Dialect Transpiler: When LLMs accidentally write DuckDB or PostgreSQL functions (such as quantile_cont instead of ClickHouse's quantileExact), our database driver automatically detects and rewires the syntax before running the query so it never crashes.
  5. Unified Datasets (30,000+ Records on GCP):
    • box_office_revenue: Global box office, production budgets, territories, and profit margins.
    • streaming_platform_metrics: Real-time streaming service metrics across 14 microservice endpoints.
    • audience_reviews: Viewer reviews with 16-dimensional vector embeddings for semantic similarity search on themes like pacing, CGI, and cast chemistry.
    • user_churn_risk: Subscriber churn risk metrics by tier.
  6. Minimalist Studio Interface: Built in React 19, Vite, and Tailwind CSS v4. Features full light and dark mode, a dynamic [Chart ⇋ Table] switcher, CSV export, and a collapsible agent execution trace showing exact SQL, query latency, and rows scanned.

Architecture

  • Database: ClickHouse Cloud on Google Cloud Platform (asia-southeast1), running columnar storage with native quantiles, bit-slice aggregations, and vector distance operators.
  • Backend API: Python 3.10 with FastAPI on Render. Uses google-adk with gemini-3.6-flash, FastMCP for tool definitions, and clickhouse-connect with connection locking.
  • Frontend App: React 19, TypeScript, Tailwind CSS v4, and Recharts, hosted on Vercel.

Engineering Challenges & How We Solved Them

  1. LLM SQL Dialect Confusion: Most foundation models are trained predominantly on PostgreSQL and MySQL syntax. Gemini occasionally generated DuckDB's quantile_cont(0.95)(latency_ms) or Postgres window functions that ClickHouse rejects with DB::Exception: Code: 46. We solved this in two layers: strict prompt engineering rules and a regex-based bidirectional dialect transpiler in DatabaseEngine that translates quantile_cont to quantileExact before execution.
  2. Session Concurrency in clickhouse_connect: The Python ClickHouse HTTP driver raises an Attempt to execute concurrent queries within the same session exception if multiple queries or health checks arrive concurrently. We wrapped query execution in a threading.Lock() to serialize database requests safely.
  3. Cloud Proxy Streaming Drops: Cloudflare and cloud reverse proxies terminate idle HTTP SSE streams after 100 seconds. We implemented 2.5-second keep-alive comments (: keep-alive\n\n) to prevent connection timeouts during complex planning steps.
  4. Clean Error Masking: Raw database exceptions often contain internal cluster hostnames, ports, and C++ driver codes. We added sanitization filters on all SSE events and built an executive notice card in the UI to display clean, non-technical diagnostics with one-click retry.

Results & Performance

  • Typical analytical query latency on ClickHouse Cloud: 105 ms to 135 ms across 15,000+ telemetry rows and 30,000+ total records.
  • End-to-end response time (natural language -> schema inspection -> SQL planning -> ClickHouse execution -> chart synthesis): ~2 to 3 seconds.
  • 100% test coverage on dialect mappings and security guardrails via automated pytest suite.

What's Next

  • Direct streaming ingestion using ClickHouse Kafka connectors for live playback telemetry.
  • Cross-database federated queries combining ClickHouse operational metrics with historical BigQuery data lakes.

Built With

Share this project:

Updates

Submission history