Design a Query Suggestion & Autocomplete for Analytics

Problem statement

Design a query suggestion and autocomplete service for a BI and data-exploration platform. As an analyst types into the SQL editor or a search-style query bar, the system must suggest table names, column names, metric names, dashboard names, function keywords, and whole query snippets such as JOIN clauses or GROUP BY templates. The service tracks metadata harvested from the warehouse or lake (schemas, column types, descriptions, lineage), tracks how users actually query (usage frequency, recency, accepted suggestions), and turns both into low-latency, context-aware, permission-aware suggestions on every keystroke.

This is not web search. Three properties make it distinctive. First, the vocabulary is private and volatile: table and column names are owned by the customer tenant, change as schemas evolve, and never appear in any public corpus. Second, suggestions are grammar-aware: after FROM the system should propose tables, after SELECT columns and aggregates, after JOIN ON plausible join keys. A flat prefix matcher over one dictionary cannot do that. Third, suggestions are security-sensitive: proposing a column the analyst is not allowed to see leaks its existence, so access control must participate in ranking, not just in query execution.

The four planes

1. Catalog plane: ingestion of schema and business metadata from warehouses, lakes, and BI models via snapshots plus change streams; synonym and description curation; versioned catalog artifacts.
2. Serve plane: the keystroke path. Debounced prefix and fuzzy matching over in-memory indexes, context detection, candidate merging, ranking, ACL filtering, and a sub-100ms response.
3. Learning plane: usage events (shown, clicked, accepted, edited) aggregated into popularity and per-user personalization features; mined join paths and snippet templates.
4. Governance plane: tenant isolation, column-level ACLs, PII scrubbing of usage logs, retention and deletion, audit, and index release control.

A strong interview answer keeps these planes separate: the serve path must degrade gracefully when the learning plane lags, and a catalog rebuild must never block keystroke latency.

Public context versus design assumptions

Public figures establish that suggestion systems operate at extreme scale: Google has publicly been credited with roughly 8.5 billion searches per day and launched Google Suggest in the mid-2000s using n-gram counts from query logs; LinkedIn open-sourced CLEO for type-ahead over its private vocabulary; Elasticsearch ships an FST-based completion suggester precisely because prefix matching must run in memory at millisecond cost; Algolia built a company on hosted search-as-you-type with typo tolerance. These are context, not requirements. For capacity planning this answer explicitly assumes one enterprise analytics platform with 100,000 daily active analysts, 24 million suggestion calls per day after debouncing, a catalog of 60,000 tables and 2 million columns, and a p99 keystroke budget of 100 ms. Unless a number is tied to a citation, it is a stated design assumption.