The System Design Round
Explore how to design a text-to-SQL analytics system for a global retailer with scoped tables and strict performance, privacy, and cost constraints. Learn to trace queries through secure boundaries, ensure answer correctness, enforce data permissions, and defend architecture decisions with evidence. You'll practice managing rejection paths, verifying SQL execution, and evaluating managed solutions to meet complex customer requirements.
Discovery left you with a scoped mandate: sales and inventory over about 40 curated tables, BI-curated definitions, an 85% target on 300 real questions, and strict access, privacy, residency, latency, and cost constraints. The system design round asks you to turn that agreement into an architecture you can defend, tracing one question from the user's words to a checked, permitted answer. This lesson works through that design using ticket T17.
Try it first (20 minutes). Before reading the worked design, sketch your own using the scoped retailer record from the previous lesson. Cover the components, trace one question, mark trusted permission boundaries, and explain how answer correctness will be established. Include clarification, rejection, and failure paths. Afterward, classify differences as defensible alternatives, missing evidence, or actual gaps.
Certify T17’s meaning
The sales-and-inventory release covers about 40 curated tables. BI curates metric definitions; the agent clarifies or declines when unsure. These agreements now constrain retrieval, generation, execution, and release.
Start with the certified semantic contract:
Meaning: Each metric identifier resolves to one approved meaning and version. Genuinely distinct approved meanings receive distinct identifiers.
Required details: Specify source, grains, exclusions, units, calendar, and timezone.
Conflict handling: Gemini must not choose whichever definition sounds most common.
Carry the approved T17 contract forward unchanged from the previous lesson:
Scope and state: R17 corporate scope and frozen S17.
Period: September 2026 in Europe/Paris.
Grains: The declared sales and inventory grains.
Exact reference: One C1 row containing 100000 minor currency units and 240 whole stock units.
These are T17-specific inputs, not warehouse-wide rules.
An unresolved request for “fiscal last quarter” requires the approved fiscal-calendar mapping and metric contract. Without them, ask a targeted clarification or state that the question cannot yet be answered. Fine-tuning cannot authorize an accounting definition.
Retrieve T17’s context
The context package contains the certified contract, relevant approved schema descriptions, and verified question-and-SQL examples. Table and column descriptions may be embedded for retrieval; include grains, relationships, and approved uses alongside names. Verified examples illustrate composition patterns and remain subordinate to the contract.
For T17, retrieve the approved sales and closing-stock sources plus a separate-aggregation example. There is no reason to place descriptions of all about 4,000 warehouse tables in the prompt. A smaller selection still needs evidence that it contains the authoritative sources.
Use one agent with bounded retrieval and SQL generation. Application code owns trusted scope, admission, execution, and release. Splitting those responsibilities among collaborating agents would add coordination without resolving this case’s central semantic problem.
The diagram’s important distinction is between context supplied to Gemini and authority enforced ...