Natural Language over Governed Data

Analytics process is: someone asks a business question, SQL hits the warehouse, a number shows up on a slide. The trap is valid SQL for the wrong meaning of “revenue.”

G06 covers one slice: map the question to an approved metric, then a read-only query with a paper trail. Not “chat with every table.”

End to end, as an exec asking “What’s Q3 revenue?”

1. We resolve who they are and which rows they may see.
2. We pin “revenue” to Finance’s versioned metric, not a guess.
3. If 30-day vs 90-day is unclear, we ask or send to an analyst — we do not pick.
4. We generate SQL, parse it, cap the scan, then run read-only.
5. We return the number, the SQL, lineage, freshness. Prose is checked against the numbers.
6. Next quarter we can replay who asked, which metric version, which SQL hash.

That’s it: metric first → permitted SQL → bounded run → explain from the result. Writes and inventing KPIs (key performance indicators) stay out.

The failure to design against is valid SQL that answers the wrong business question. “Revenue” can mean booked, recognised, or collected revenue; the model cannot choose that definition on the business’s behalf. Anchor the question to a versioned metric before exposing a physical schema, then bound and audit every query.

This is the anchor for the related executive-dashboard and retail-forecast explanation cases in the source study. Learn the governed query path once; change the final explanation and review workflow for each variant.

The easiest way to frame requirements in an interview is:

Functional = what the system does. Non-functional = how well it does it and what constraints it must satisfy.

Functional requirements — what the system must do

1. Resolve to a governed metric first — before any physical schema.
2. Retrieve only permitted tables, joins, and freshness context.
3. Ask on ambiguity — do not pick between two approved meanings.
4. Produce dialect-specific SQL.
5. Parse the AST (abstract syntax tree) — check tables, columns, joins, shape, and estimated scan.
6. Execute through a read-only gateway.
7. Return result, SQL, metric lineage, and caveats.
8. Generate prose from the structured result — check it against the numbers.
9. Audit the query run — actor, decision, SQL hash, bytes scanned, metric/schema/policy versions.

Non-functional requirements — how well / under what constraints

First release

• Ten owner-approved metrics.
• Golden question / SQL / result cases.
• Analyst shadow review, then a small executive audience.
• Not a general interface to every warehouse table, a write-query tool, or a machine that defines business metrics.

Interview shortcut

If asked “What are the requirements?”, say:

“Functionally, pin the metric, generate checked read-only SQL, and explain from the result. Non-functionally, warehouse ACLs, scan budgets, 3–8 second interactive asks, and a replayable query-run record.”

This is the interview-size view of the source architecture, §4. The LLM (large language model) interprets and explains; registry, policy, warehouse, and analysts keep authority.

Step-by-step architecture

• Step 1. Authenticate the user and resolve entitlements before any schema or data lookup.
• Step 2. Use an LLM-assisted resolver against the versioned metric registry. If “revenue” has multiple supported meanings, clarify or escalate; do not guess.
• Step 3. Retrieve only the allowed schema, join path, lineage, and freshness facts needed for the selected metric.
• Step 4. Generate dialect-specific SQL from that narrow context. The model proposes a query; it does not approve one.
• Step 5. Parse the SQL into an AST, enforce table/column/join and read-only rules, recheck entitlements, and estimate scan cost. Reject or narrow anything unsafe or over budget.
• Step 6. Execute through a bounded read-only gateway, running the query as the user (on-behalf-of) so warehouse-native row and column security applies to them, with timeout and row limits.
• Step 7. Summarise the structured result and verify every number against the table. On mismatch, return the table alone and alert reviewers.
• Step 8. Return the definition, SQL, lineage, freshness caveats, and answer; record the versions, decision, and bytes scanned for replay.

Agent role: the default design is a governed LLM query workflow. A supervisor/agent is useful only when one question needs multiple approved specialists, as in the insurer variant. It routes among Genie, a restricted SQL/RAG worker, deterministic analysis, and visualisation; neither that agent nor its LLMs may define metrics or bypass the query gate.

For mixed questions, route each subtask to its right source: semantic search finds related prose, structured queries compute counts and sums, direct lookup resolves IDs. A retail “why” answer computes drivers deterministically and lets the LLM narrate them; correlation is not proof of causation.

Keep metric resolution, authorisation, validation, and execution synchronous because they determine the one answer. Crawl catalogs, refresh caches, and replay evaluations asynchronously, partitioned by tenant or warehouse so background jobs do not stampede query traffic. Cache only with tenant, permission signature, metric/schema version, and freshness in mind.

Measure first-try semantic correctness, SQL retry rate, warehouse compute per answer (bytes scanned on BigQuery, DBU (Databricks Unit) or credit time on Databricks and Snowflake), token spend, and p95 by stage. The source flags >20% above the seven-day baseline as a cost warning. The cheapest levers are narrower metric context, approved templates, dry-run cost checks, bounded windows, and safe caching. Repeatedly asking a stronger model to repair invalid SQL raises both bills and can still answer the wrong question.

Release gate: semantic correctness against analyst-approved definitions. Query execution success is a separate, weaker measure. Golden cases pair question, approved SQL, and expected result shape; include ambiguity, wrong grain, join fan-out, hidden columns, scan explosions, and a summary that misstates a correct table. Zero escaped policy violations. The copilot variants add groundedness, citation and high-risk escalation review; their source examples cite ≥90% supported claims, ≥95% correct citations, and ≥95% correct high-risk escalation as goals to agree with the customer.

Roll out in order: ten governed metrics with owners → golden replay on every change → shadow analysts on live questions → small executive release → domain-by-domain expansion with a named metric owner. Stop or roll back on semantic mismatches, escaped policy, rising scan cost, or declining user trust. An answer users still ask analysts to recheck has not solved the workflow.

“I would first ask who owns the metric definitions, because valid SQL against the wrong meaning of revenue is the worst failure here. I would resolve each question to a versioned governed metric, clarify ambiguity, retrieve only permitted schema, and let the LLM propose SQL from that small context. An AST policy and cost gate rechecks entitlements before a read-only warehouse gateway runs it as the user, under native row and column controls. The answer includes the definition, SQL, lineage, and freshness; any prose is cross-checked against the table. I would prove ten metrics with golden cases and analyst shadowing before executives rely on it. At scale I would trim schema context, use reviewed routes, bound scans, and measure semantic correctness and cost per answer.”

Memory line: “The semantic layer defines the answer; the LLM translates and explains it.”

For parser mechanics, the insurer two-pivot agent story, the incident drills, and the detailed evaluation signals, use the Deep Dive. For a final-minute pass, use the Cheat Sheet.

The Main guide is the spoken design. This is the technical backup to the unchanged source study, especially §§3–14.

The warehouse catalog owns physical schema. Metric owners own business definitions in Metric(id, name, definition, dimensions, owner, version). The identity provider and policy store own permission decisions. Caches hold versioned, expiring schema and metric snippets but own neither. QueryRun(id, actor, sql_hash, policy_decision, bytes_scanned, result_ref) is append-only and stamps metric, schema, policy, prompt, and model versions so a changed number can be reconstructed. Question interpretation, candidate SQL, AST (abstract syntax tree), validation, and execution trace are request state.

The executive variant adds KPIValue, dashboard refresh time, and a cited Insight; the retail variant adds forecast, actual sales, and driver-signal records. Forecast drivers from promotions, weather, inventory, or events are computed from data and then narrated. The assistant cannot infer a causal claim from correlation. Feedback and analyst corrections feed evaluation only after review.

| Question | Route | Reason | |---|---| | “Revenue last week” | Versioned metric view and structured query | A financial definition and grain must be selected. | | “How many high-priority tickets?” | Structured count/filter | Semantic search does not count a complete set. | | “Status of ticket #4821?” | Exact ID lookup | Approximate retrieval is unnecessary. | | “Pattern in billing tickets this quarter?” | Structured filter, then summarise scoped prose | Filters establish the set; the LLM (large language model) summarises its content. |

If a word maps to multiple metrics, present the supported definitions, ask, refuse, or escalate according to the agreed policy. Use a default only when a context-specific canonical definition has been approved. The insurer supervisor’s <60% clarification threshold is an example from that engagement, not a universal value. Catalog staleness is also a gate: refresh, narrow the supported question set, or ask; do not silently generate against an unknown schema.

The LLM proposes SQL from a narrow, authorised metric and schema context. A dialect-aware parser builds an AST and policy checks the statement type, physical tables, columns, functions, joins, aggregate semantics, limits, date filters, and cross-join/fan-out risk. Regex cannot reliably detect nested queries, aliases, CTEs, comments, or obfuscation. A hidden-column request must fail even if the SQL is syntactically valid.

Authorisation has several independent controls: least-privileged read-only credentials, warehouse-native row and column policies evaluated for the end user (on-behalf-of), not a shared service account, entitlements rechecked at execution, filtered schema metadata, and redacted result/log handling. The model cannot rewrite access predicates. A cost estimator checks bytes, time, and row limits before admission; a live timeout cancels overruns. Auto-run only on a high-confidence, approved, low-cost pattern; otherwise show a draft or request privileged review. Do not retry policy denial with a stronger model.

Authorisation failure closes the request. A brief warehouse outage can queue or degrade if freshness permits. A summary outage can return the table. A business-definition conflict goes to the metric owner or analyst. Treat a suspicious row count or scan volume as a correctness and reliability problem, not merely a billing issue.

Golden cases pair natural-language question, approved SQL, and expected result shape, including paraphrases, grain and date traps, unauthorised columns, large scans, and ambiguity. Execution accuracy asks whether the SQL runs and matches the expected result; semantic correctness asks whether it answers the governed business question and is the release gate. Analysts judge subtle wrong answers in shadow mode. Zero escaped policy violations is a separate gate. The source uses a trust target of 4.2/5 or 80% positive as an example; falling trust matters even if usage rises. Compare warehouse compute per answer (bytes scanned or DBU/credit time) with the seven-day baseline and investigate >20% drift.

The rollout is ten owned metrics, then golden replay, then analyst shadowing, then executive release, then domain expansion. Metric owners approve definitions and cases; application owners run orchestration and generation; data owners manage warehouse and policy mappings; security reviews access and audit; support receives escalations. Rollback on semantic mismatch, policy leakage, scan-cost jump, or sustained trust decline.

The source §13 tells an engagement story, not the default architecture for every NL-to-SQL product. At a large Asian life insurer, a BI (business intelligence) question queue that took days and weeks for a dashboard led to a governed self-service assistant. A monolithic agent with 20+ tools and full history failed through context bloat and tool confusion. The shipped supervisor used a fixed eight-node LangGraph flow: classify → clarify below 60% → resolve endorsed assets from a 16-asset Context Index → route → compose. It routed to a managed Genie (now Genie One) text-to-SQL specialist, a narrower generated-SQL/RAG worker, deterministic analysis for Z-scores and trends, and a Lakeview visualisation worker.

Governance used a handful of governed metric views rather than raw fact tables, conversation state in a custom Delta-backed checkpointer with short retention (Databricks now documents a Lakebase checkpointer), versioned base-plus-overlay prompts with a five-minute cache, MLflow traces per node, and an AI Gateway (now Unity Gateway) for rate limits and PII (personally identifiable information) filtering on the model endpoints the agent calls; as of 2026 it can't guardrail the agent endpoint itself. As domains grew, the supervisor’s tool list approached the same bloat; an orchestrator delegated to customer, channels, policy, and claims domain agents, each with a small toolset, while a memory manager owned categorised long-term memory. The managed multi-agent feature (now Agent Bricks Supervisor Agent, GA (generally available) in 2026) wasn't an option for this customer at the time, so I hand-built the supervisor. In that engagement, the team reported time-to-insight falling from days to minutes, and adoption grew after rollout, though I can't attribute it cleanly. The source’s “if rebuilt” lesson is to measure resolution-time and accuracy from day one.

Measure SQL retries, context tokens, warehouse compute (bytes scanned or DBU (Databricks Unit) time), first-try success, p95, and cost per answer before changing models. Shrink context to metric definitions and a few permitted tables; use reviewed operations or managed SQL for common paths; dry-run cost; cache versioned metric metadata and recurring questions; cap scans and date windows. A stronger model retry loop cannot repair a missing business definition. Background catalog crawling and evaluation replay use partitioned, rate-limited queues; query-time authorisation and policy never move off the hot path.

Use the Main guide for the spoken answer, the Deep Dive for mechanisms, and the unchanged source for the full case.

The metric registry defines the business answer; the LLM (large language model) translates the question and explains a validated, read-only result.

Who owns the first ten metrics? Semantic layer exists? Warehouse and dialect? Native row/column security? What ambiguity needs clarification? Scan and latency budgets? Analyst review threshold? Audit evidence?

Identity → governed metric → ambiguity gate → permitted schema → LLM SQL proposal → AST (abstract syntax tree) policy + scan-cost check → read-only warehouse with native security → numeric cross-check → answer with SQL, lineage and caveats.

Agent role: default is a governed query workflow. Use a supervisor only for a real multi-specialist task; it routes, while policy and warehouse controls remain authoritative.

1. Valid SQL can answer the wrong business question; semantic correctness is the release gate.
2. Ask about “revenue” when more than one approved definition fits.
3. Parse the AST; do not rely on regex or prompt rules to stop unsafe SQL.
4. Read-only credentials, execution-time entitlement check, warehouse-native row/column security, scan budget.
5. A summary that contradicts the table is rejected; return the table alone.

MVP (minimum viable product): ten governed metrics, golden question/SQL/result cases, analyst shadowing. Copilot example: 3–8 s interactive. Asian life insurer example: clarify intent below 60%; a handful of governed metric views; eight-node supervisor; 16-asset index. Cost signal: warehouse compute per answer (bytes scanned or DBU/credit time) >20% above seven-day baseline. These are source examples to confirm with the customer.

Measure retries, tokens, and warehouse compute (bytes scanned or DBU (Databricks Unit) time). Send metric definitions rather than the full schema; prefer approved operations, dry-run SQL, cap scans, and cache only within tenant/permission/version scope. Do not retry a wrong definition with a stronger model.

“Ten governed metrics, a clarifying gate, AST and cost checks, read-only execution, and analyst-reviewed semantic correctness before executive rollout.”

Give executives an answer in seconds without ever letting the model decide what "revenue" means: the semantic layer, not the model, is the product.

The hard part of this system is not generating SQL. It is that a syntactically perfect query against the wrong definition of a metric is a governance failure dressed up as a working feature. So the design anchors every question on a governed definition before it touches a table. This page consolidates the group's prompts into one read for the day before. Everything else in the group is a delta on it.

Open with the failure, not the pipeline. Executives ask analysts the same handful of questions every week, and each time someone hand-translates English into SQL, runs it and sanity-checks the result. The naive pitch, "let an LLM (large language model) write the SQL", loses the interview immediately, because generation is not the hard part. "Revenue" might mean booked, recognised or cash collected. The system must never produce valid SQL that confidently answers the wrong business question. That failure is more dangerous than an outright error, because the SQL ran, the chart rendered, and the number looks plausible.

Reframe the ask from "build a text-to-SQL model" to "build a governed decision-support system that only speaks in terms the business has already agreed to". That framing implies three things from day one. A semantic layer of governed metric definitions. A safety boundary around the warehouse that is read-only and cost-bounded. And an explicit way to say "I don't know which definition you mean" instead of guessing.

The three answer tiers show what the reframe buys. The weak answer lets an LLM write SQL against the schema and run it. It ignores who owns metric definitions, allows an unapproved reading of revenue, and has no AST (abstract syntax tree) validation, cost bound or way to notice a confident wrong answer. The average answer adds a semantic layer, validates before running, executes read-only with row limits and logs every query. It still never says what happens when the question is ambiguous, when the schema drifts, or when the prose summary disagrees with the table. The strong answer consults the metric registry before any physical table and turns ambiguity into a clarifying question. It parses SQL into a tree and checks it against policy and a scan budget before a read-only gateway sees it. It re-checks entitlements at execution. And it proves the whole thing on ten governed metrics with golden cases before any executive sees output.

Then ask the questions that change the architecture, before sketching a box. Each one decides a component.

Map the people, because the three groups want different things and the architecture has to satisfy all three at once. That tension is exactly why the semantic layer and the policy and cost validator are first-class components rather than afterthoughts bolted onto a chat UI.

The same design arrives with two more personas from the practice worksheets. The Executive Dashboard Assistant user asks natural-language KPI (key performance indicator) questions. The assistant translates them to governed metrics, retrieves dashboard and data context, explains variance, cites metric definitions, and escalates uncertain answers to analysts. The Retail Demand Forecast Explanation Assistant user is a planner reviewing forecast variance who asks why demand changed. The assistant explains drivers using sales history, promotions, weather, inventory and events, and recommends human-reviewed planning actions. Neither changes the architecture. Both add a variance-explanation step after the query, which section 8 covers.

Declare the non-goals as a scope fence. This is not a system that lets users author write queries or modify data. It is not a general-purpose exploration tool for every table in the warehouse; it answers a governed set of business questions. And it is not responsible for defining what "revenue" means. That is a business decision the semantic layer records, not one the assistant makes on the fly.

Correctness dominates every other axis. A fast, confidently wrong answer is the single worst outcome, because the executive has no way to know it is wrong. So the system trades latency for safety every time. Asking, narrowing scope or refusing are all preferable to guessing. Depth on ten metrics beats shallow coverage of the whole warehouse.

The functional core is one pipeline, and the must-haves are its stages. Take a natural-language question. Resolve it against governed metric definitions before touching physical tables. Retrieve only the tables, joins and freshness context needed, never the whole schema. Detect ambiguity and ask rather than silently choosing one of several plausible metrics. Generate dialect-specific SQL from the narrowed context only. Parse it into an AST and enforce policy against the tree, not the text. Estimate cost and execute read-only with limits. Return the result with the exact SQL, metric lineage and caveats such as snapshot staleness.

The should-haves come from the copilot personas:

• show confidence, missing evidence and the escalation reason when uncertain
• collect user feedback and reviewer corrections for evaluation, never for ungoverned training
• give admins controls over source inclusion, freshness thresholds, policy rules, blocked actions and audit export

Richer query planning, human approval for ambiguous cases, learned query ranking, federated sources and semantic search over data dictionaries can wait.

"Good" for the MVP has one definition: the system reliably answers ten governed metrics with full lineage and explainability. If it cannot do that, it is not ready to become a general interface to the warehouse.

Every must-have then needs an owner in the architecture.

Three kinds of state exist here, and each has one owner. The warehouse catalog owns physical schema truth. The semantic metric registry owns business definitions. The identity provider owns authentication and the policy store owns access decisions. Caches hold schema snippets, metric lookups and recent plans only as a performance layer, with TTLs and version stamps so the system can explain what it used. Question interpretation, candidate SQL, the AST, the validation result and the execution trace are short-lived workflow state and must not become hidden durable state.

The core records follow. Metric(id, name, definition, dimensions, owner, version). SchemaAsset(id, engine, object, columns, sensitivity). QueryRun(id, actor, sql_hash, policy_decision, bytes_scanned, result_ref). The copilot personas add KPIValue(metric_id, period, dimension, value, source_table), Dashboard(id, owner, metric_ids, refresh_time), Insight(id, metric_id, explanation, supporting_queries, confidence) and UserAccess(user_id, metric_id, dimension_scope). The retail persona adds SKU (stock-keeping unit), Store, Forecast(id, sku_id, store_id, horizon, prediction, confidence), ActualSales, DriverSignal(id, type, value, source, timestamp) and Explanation(forecast_id, top_drivers, confidence, caveats).

State the versioning rules aloud. Metric versions are retained long enough to reconstruct why last quarter's dashboard produced a different number than today's. QueryRun is write-once and append-only, because query history is where incident review and adoption analysis start. Every artifact that influences behaviour is versioned, whether metric definitions, schema snapshots, policy bundles or generated plans, or the system works in the happy path and fails at rollout. Idempotency keys go on question submission and feedback, and optimistic concurrency on metric edits, so one team cannot silently overwrite another team's definition.

The architecture is a chain of trust boundaries, best explained in dependency order rather than as a flat list of boxes. One diagram carries the whole design; sections 5 to 8 zoom into its stages. The organising split is control plane against data plane: definitions, policies, schedules and evaluation rules are releases; every question is a request.

 ╔═══════════════════════════════ CONTROL PLANE (changes are releases) ═══════════════════════════════╗
 ║  semantic metric registry (versioned, owner-approved) · policy bundles (tables, columns, joins,     ║
 ║  scan budgets) · warehouse-native RLS/CLS · catalog crawl schedules · prompt + model versions ·      ║
 ║  golden question/SQL/result cases · dashboards of record                                             ║
 ╚══════════════════════════════════════╤═══════════════════════════════════════════════════════════════╝
                                        │ configures every box below
 ╔═══════════════════════════════ DATA PLANE (calls are requests) ════════════════════════════════════╗
 ║                                                                                                      ║
 ║  BACKGROUND — asynchronous, partitioned by tenant / warehouse / dataset family                       ║
 ║   catalog crawler ─> metadata refresh queue (rate-limited) ─> catalog cache (TTL + version stamp)    ║
 ║   evaluation replay queue ─> golden-set replay on every change ─> regression report                  ║
 ║                                                                                                      ║
 ║  QUERY — synchronous, ordered by risk                                                                ║
 ║   user ─> query API ─> AUTHZ ─> METRIC REGISTRY ─> SCHEMA RETRIEVER ─> AMBIGUITY? ─> SQL GEN ─>      ║
 ║           authN,      entitle-  governed         only the tables,   < threshold:  dialect-          ║
 ║           intent      ments     definition       joins, freshness   ASK, not      specific,          ║
 ║                                 first            needed; freshness  guess         narrowed context   ║
 ║                                                  gate                                                 ║
 ║        ─> AST PARSE ─> POLICY + COST VALIDATOR ─> READ-ONLY GATEWAY ─> WAREHOUSE ─> SUMMARIZER ─> answer
 ║           tree, not    tables, columns, joins,    narrowest creds,    RLS/CLS      table + SQL +     ║
 ║           text         writes, cross joins,       timeout, row        enforced     lineage + caveats;║
 ║                        scan budget; re-check      limit, cost         natively     summary cross-    ║
 ║                        entitlement at execution   estimate                          checked vs table  ║
 ║                              │ reject                    │ budget exceeded                │ mismatch   ║
 ║                              v                           v                               v            ║
 ║                        deny + reason           cancel + safe fallback        table only + review queue ║
 ║                                                                                                      ║
 ║  OBSERVABILITY — every stage writes                                                                  ║
 ║   append-only query runs (actor, sql_hash, policy decision, bytes scanned, metric + schema versions) ║
 ║   ─> dashboard: business outcome / model quality / system health · alerts on escaped policy, scan   ║
 ║      drift, trust decline                                                                            ║
 ╚══════════════════════════════════════════════════════════════════════════════════════════════════════╝

The tutorial's own component view, with its systems of record and queues, is the rendered form of the same flow.

Read the components in dependency order. Every box should answer who owns its state, how fresh it is, and why it exists; a box without those answers was premature.

Three boundaries are worth pointing at while the diagram is up. The sync/async boundary follows one rule: anything that must be correct before one answer is returned stays synchronous; anything that improves future answers can be asynchronous. The trust boundary sits at the read-only gateway, which holds the narrowest credentials and no write path; nothing generated by the model reaches the warehouse except through it. And consistency points are explicit. Entitlement and policy are re-checked at the moment of execution, not only at planning. A user who loses access between planning and execution is refused. A material registry version change never silently reuses an old interpretation. The partitioning key for queues preserves the natural isolation boundary, usually tenant, warehouse, region or dataset family. One noisy customer or one large catalog refresh then cannot starve unrelated work.

The semantic layer is the product, and the model is a translator into it. "Revenue last week" must anchor on the governed definition of revenue before the system starts searching for tables. A model cannot infer a business definition that the business has not written down. The registry is consulted first, before any physical table, and raw schema access is an escape hatch for approved exploratory workflows, never the default.

The insurer engagement in section 13 makes the same choice concrete: a handful of governed metric views, not raw fact tables. If the agent and a human analyst compute "claims by region" differently, with different date logic or different exclusions, trust in the whole system collapses. A metric view makes the KPI (key performance indicator) definition one versioned artefact every consumer shares, and the semantic-layer version of that sentence is the strongest line in this section.

Put a router in front of retrieval, because a real deployment mixes structured and semantic questions freely. Semantic search answers "which things are like this", never "how many", "which is most recent" or "sum this field".

The structured path itself is a choice between two mechanisms. Generate a query from natural language and run it against a small, fixed, well-documented view. That is fast, but the model can invent fields, so the query must be validated or its result sanity-checked before it is trusted. Or call a small set of fixed, well-defined operations. That is safer, because the surface of what can happen is a reviewed set of operations rather than an open-ended language. Fixed operations are usually the better default in an enterprise. The insurer design made that decision per specialist. A managed text-to-SQL service over curated tables is the safe default. One deliberately narrower place allows hand-generated SQL for ad-hoc questions outside the curated scope.

Two rehearsed answers live here. When asked how to handle revenue with three definitions, name the ambiguity and ask a clarifying question. Show the supported definitions. Default only where the business has an approved canonical meaning for that context. If users ask it often, put it in the semantic layer with explicit aliases. And when the interviewer pushes on the riskiest assumption, that the model can infer the right business meaning from the prompt:

"I would not rely on the model to infer business definitions from raw text. I would anchor every answer to a governed semantic layer, and treat any question that falls outside that layer as an explicit ambiguity to resolve, not a guess to make."

If a question could map to multiple governed metrics or grain levels, the system stops and asks. A valid SQL query that answers the wrong business question is still a failure, and a clarifying question costs one round trip where a guess costs an executive decision. The gate has three modes, agreed with the customer in discovery: ask a clarifying question, refuse, or escalate to an analyst.

Give the gate a number. The insurer supervisor classifies each question into simple_kpi, deep_analysis, document_lookup, visualization or conversational with a confidence score, and its clarify node fires only below 60%. "Show me the numbers" gets a clarifying question, not a guess. A threshold makes the behaviour testable and tunable; "the model will ask when unsure" does not.

Ambiguity is also a freshness signal. When the catalog is stale beyond a threshold, the retriever refreshes through the async queue before allowing generation, falls back to a narrower supported question set, or asks. If freshness is uncertain, do not pretend precision. And the failure policy for a business-definition conflict is human intervention, because a wrong answer is worse than a slower answer.

Valid SQL is not safe SQL. Regex can spot an obvious DROP TABLE string but cannot understand aliases, nested selects, CTEs, comments, obfuscation or a harmless-looking subquery that becomes unsafe after rewrite. A parser can inspect structure: statements allowed, tables allowed, columns allowed, joins allowed, functions allowed, limits present, aggregate semantics acceptable. Safety depends on syntax trees and policy rules, not pattern-matching theatre.

Four controls make up the boundary, and each reduces risk differently, so together they are defence in depth.

The hidden-column invariant is worth proving in front of the interviewer. It shows that a valid SQL string is still rejected when it requests a column outside the actor's permissions. A production version uses a dialect-aware parser and a policy source from the warehouse or a central registry, and logs the decision with redacted evidence.

class PolicyError(Exception):
    pass

@dataclass(frozen=True)
class Actor:
    name: str
    permitted_columns: frozenset[str]

marketing_user = Actor(name="marketing", permitted_columns=frozenset({"customer_id", "country", "plan"}))

def parse_selected_columns(sql: str) -> list[str]:
    normalized = " ".join(sql.strip().split())
    upper = normalized.upper()
    if not upper.startswith("SELECT ") or " FROM " not in upper:
        raise PolicyError("Only simple SELECT queries are allowed in this sketch")
    select_part = normalized[7 : upper.index(" FROM ")].strip()
    if select_part == "*":
        raise PolicyError("Wildcard selects are not allowed")
    columns = [c.strip() for c in select_part.split(",")]
    if any(not c for c in columns):
        raise PolicyError("Malformed column list")
    return columns

def approve(sql: str, actor: Actor) -> bool:
    columns = parse_selected_columns(sql)
    denied = [col for col in columns if col not in actor.permitted_columns]
    if denied:
        raise PolicyError(f"actor {actor.name} may not access columns: {', '.join(denied)}")
    return True

def test_rejects_hidden_column_exfiltration():
    sql = "SELECT email FROM customers"
    with pytest.raises(PolicyError):
        approve(sql, actor=marketing_user)

After policy, estimate cost. Approved queries get a cost estimate, a timeout and a row limit, then run through the read-only gateway. Prefer bounded date windows and pre-aggregations. When a request would scan beyond the configured threshold, ask the user to narrow it, propose a cheaper rewrite, or require approval from a more privileged role. Never let the model "just try it". The model must not rewrite access predicates either, and if the assistant needs schema metadata, that metadata is filtered to what the user can see.

Execution is risk-based. Auto-run only when confidence is high, the query fits an approved pattern, the user has permission and the estimated cost is below threshold. Otherwise show the draft SQL and ask for confirmation or analyst review. Full auto-execution feels magical and is dangerous when a query may touch sensitive rows, incur high cost or answer an ambiguous request.

The executive reads the prose, not the rows. So the summary is generated from the structured result and its key values are cross-checked against the table. Any summary whose numbers do not match is rejected. The table alone is returned, with an alert to the review queue. If the summary generator is down or untrusted, degrade to the table rather than fabricate confidence.

The two copilot personas extend the summary into an explanation, and the same rule holds: every driver named must trace to a query. The executive copilot explains variance against the governed definition, cites the metric definition it used, and escalates uncertain answers to analysts. The retail explainer attributes a forecast variance to drivers from sales history, promotions, weather, inventory and events, then recommends planning actions that a human reviews before any order changes. Its red-team list names the risks precisely: confusing correlation with causation, hallucinated drivers, stale promotions data, recommendations biased against stores or regions, and over-ordering because an explanation sounded confident.

Keep the statistics deterministic. The insurer design's analysis agent computes Z-score anomalies and trend statistics rather than reasoning about them, so the model cannot invent a plausible but wrong number. The explanation layer then narrates computed drivers; it never produces them. Show confidence, missing evidence and the escalation reason whenever the drivers are weak, and route board-level narratives through an analyst before they leave the system.

A strong answer says exactly what fails open, what fails closed, what degrades, what queues and what escalates to a human. Not every incident deserves the same reaction: a policy engine outage differs from a visualisation glitch, and a query scan that explodes cost differs from a benign cache miss. Memorise the pattern by category: authorisation closed, connectivity degrade or queue, ambiguity human, suspicious scan closed and alert.

Five drills cover the failures that survive unit tests. The headline drill is the wrong business question. The user asks for "active customers". The assistant quietly uses login activity when the definition requires paid activity. The SQL returns rows and the chart renders. Detect by comparing the generated query against governed metric metadata and approved aliases. Contain by blocking publication and marking the run. Preserve the prompt hash, SQL text, policy decision, asset versions and rejection reason. Recover by routing to a clarified metric choice or human review. Prevent by tightening the mapping, the test case or the disambiguation prompt. The blast radius is scoped by tenant, workflow and metric family, never "all analytics is broken".

Schema drift is the second drill. A column disappears, a table is renamed overnight, a join key shifts, and a previously good template still produces something that looks reasonable. Detect it during plan validation, not after a failed warehouse call. Fail closed on the affected template, refresh the schema cache, and queue a review if the business definition itself changed.

Join fan-out is the third. A join multiplies rows and inflates totals while remaining syntactically valid and passing every SQL check. Validate join cardinality assumptions, compare expected row counts against heuristics, and block suspicious fan-out unless the user explicitly asked for a detailed expansion. If the join cannot be proven safe, refuse or downgrade; never hope the aggregate still looks plausible.

Excessive scans are the fourth, and cost is a reliability issue because one query can exhaust concurrency and slow other tenants. Estimate before executing. Where the estimate exceeds the threshold, rewrite to a bounded window or a pre-aggregation if one exists; otherwise fail closed and ask the user to narrow or confirm.

The summary contradicting the table is the fifth, already drawn in section 8. Around all five sit the operational safeguards:

• a bounded timeout on every warehouse query
• limited, idempotent retries, only when the failure is likely transient
• no indefinite retry of a policy failure, because a bad prompt is not a network glitch
• a dead-letter path with enough redacted context for support when a queued request keeps failing
• a human escalation route for any ambiguous mapping or repeated semantic mismatch

Human escalation is part of the design, not a sign of weakness.

Backpressure belongs to the background lane. Crawler and registry refresh jobs sit behind a queue with rate-limited bursts, so they never stampede the warehouse during peak query traffic. The query API degrades gracefully when metadata confidence drops. The compact failure policy reads as one flow:

request received
  -> authenticate user
  -> authorize against tenant + role + metric policy
  -> parse SQL AST
  -> validate tables, columns, joins, limits, and query shape
  -> if policy or semantic check fails: reject + log redacted evidence
  -> execute read-only as the user (on-behalf-of) under warehouse-native RLS/CLS
  -> if scan/time budget exceeded: cancel + surface safe fallback
  -> if results pass checks: mask sensitive fields
  -> generate summary from structured result
  -> if summary mismatch detected: return table only, alert review queue

A query that runs is not a query that is right. So the evaluation separates two things. Execution accuracy is proved by the golden-set replay. Semantic correctness is proved only by analyst review against the governed definition, and that is the release gate. Golden cases pair the user's natural-language question, the approved SQL and the expected result shape. They include easy examples, ambiguous paraphrases, edge cases with filters and time windows, and known traps where a valid query answers the wrong question.

Measure in four buckets, so model quality is never confused with operational health or business value:

• technical health: bytes scanned, p95, availability
• model quality: execution accuracy, semantic correctness, clarification rate, policy violations
• adoption: trust score
• business outcome: answer turnaround before and after, analyst escalation volume, executive self-service rate

A successful assistant changes behaviour. If executives keep asking analysts to re-check, the product has not solved the problem. The dashboard is layered the same way: business outcome on top, model quality in the middle, system health at the bottom. Each business metric is wired to the component that can move it. A trust drop then points at slow answers, too many clarifications, broken mapping, or policy blocks that feel arbitrary.

Red-team the definitions, not only the SQL. Semantic substitution, where login activity stands in for paid activity. Schema drift that keeps a template plausible. Join explosions that pass every SQL check. Excessive scans that slow other tenants. Summary drift, where the table is right and the prose misstates the trend. And for the copilot personas: fabricated business reasons, leakage across regions or business units, stale dashboard refreshes presented as current, and an overconfident board-level narrative.

The rollout must prove three things in order. The assistant answers a narrow set of governed questions correctly. It does so safely under policy. Real users adopt it because it improves their workflow rather than impressing them in a demo. Left to right is increasing blast radius, not increasing ambition. The assistant advances only when the prior phase's exit criteria are met.

Name the owners, because a plan without owners is a hope. The metric owner approves definitions and golden cases. The application owner owns the runtime, prompt assembly, generation and API contracts. The data owner owns warehouse tables, the semantic layer and policy mappings. The security owner reviews access control, audit logging and blocked-query handling. The support owner receives escalations and coordinates rollback. Rollback triggers are a spike in policy violations, a drop in semantic correctness, a jump in bytes scanned, or users routing around the assistant because they stopped trusting it. Rollback is the mechanism that keeps a partial success from becoming an incident.

Decide what is configuration, adapter, shared service and core product, because an FDE (Forward Deployed Engineer) system creates leverage only when the next customer inherits the stable core and swaps the edges. Metric definitions, allowed domains and rollout thresholds are configuration. Warehouse and identity integrations are adapters. Query validation, policy enforcement, tracing and audit logging are shared services. Question understanding, explanation behaviour and safe query orchestration are the core. Five delivery items make it stick:

• a canary on a small user group or metric set
• staged migration of governed questions from analyst workflows
• user training on what the assistant can and cannot answer and how it signals uncertainty
• documented escalation paths
• a maintained metric catalog with known limitations

Begin where the customer is already hurt, executives wanting governed natural-language answers with correct definitions and no unsafe queries, not with a diagram. Then spend the minutes where the risk is.

The 90-second architecture summary:

"We're building a governed natural-language-to-SQL assistant for executives and analysts who need fast answers but cannot afford incorrect metrics or unsafe queries. I'd put a semantic layer between the model and the warehouse so the assistant generates SQL from approved business definitions rather than raw table names. The service would accept a user question, classify intent and risk, resolve the relevant metric definitions, generate a constrained draft query, validate it for policy and cost, and then either execute automatically or route for approval depending on sensitivity and confidence. Row-level security must be enforced in the warehouse, not in the prompt. I'd log the question, the generated SQL, the metric mapping, the cost estimate, the execution result, and the user identity for audit and debugging. The biggest trade-off is flexibility versus correctness: I'd optimise for governed answers first, then add templates and fallback pathways for the long tail. My first rollout gate would be a small set of executive metrics with a golden test corpus and a hard stop on ambiguous or expensive queries."

The 2-minute spoken answer:

I would not start with the model. The naive pitch here is to let an LLM (large language model) write the SQL, and that loses immediately, because generation is not the hard part. The hard part is that "revenue" might mean booked, recognised, or collected, and a syntactically perfect query against the wrong definition is a governance failure dressed up as a working feature. So I would reframe the problem as building a governed decision-support system that only speaks in terms the business has already agreed to. That means three things from day one: a semantic layer of governed metric definitions, a safety boundary around the warehouse that is read-only and cost-bounded, and an explicit way to say "I don't know which definition you mean" instead of guessing. Architecturally, the query API authenticates and resolves entitlements, the metric registry is consulted before any physical table, the schema retriever pulls only what is needed, ambiguity triggers a clarifying question, and the generated SQL is parsed into an AST (abstract syntax tree) and checked against policy and a scan budget before a read-only gateway executes it with timeouts and row limits. I would prove it on ten governed metrics with golden cases pairing question, approved SQL, and expected result, then shadow analysts before any executive sees output, then expand domain by domain with named metric owners. Success is not a fast answer; it is an answer nobody has to re-check.

The lines that carry the round:

1. "The danger isn't failing to produce SQL. It's producing valid SQL that confidently answers the wrong business question."
2. "Anchor on the governed metric before touching a table. The semantic layer is the product."
3. "Ask rather than guess. A clarifying question costs a round trip; a guess costs a decision."
4. "Parse, don't regex. Safety lives in the syntax tree, not in pattern matching."
5. "Row-level security is enforced in the warehouse, not in the prompt."
6. "Valid SQL is not safe SQL. Estimate cost before you execute."
7. "Anything that must be correct before one answer is returned stays synchronous; anything that improves future answers can be asynchronous."
8. "Ten metrics, golden cases, shadow analysts, then domain by domain. Trust is earned, not assumed."
9. "Success is not a fast answer; it is an answer nobody has to re-check."

Name the trade-offs with a balanced verdict each. Raw schema prompting versus semantic layer: default to the layer for governed metrics, allow raw schema only for approved exploratory workflows. Automatic execution versus approval: risk-based execution, auto-run only on high confidence, an approved pattern, permission and low cost, otherwise show the draft and ask. Flexibility versus templates: a hybrid, templates for common governed workflows, free-form for the long tail, and a policy gate deciding when free-form is allowed. Speed versus warehouse cost: bound the search space with the semantic layer and cache aggressively for repeat questions. Treat any large scan as a signal to narrow scope, not to optimise the query.

Repair the weak answers on the spot. "Let the LLM write the SQL" becomes registry first, AST policy, read-only gateway. "The database handles permissions" becomes warehouse-native policies reused plus entitlement re-check at execution. "We'll test the SQL" becomes golden cases judged for semantic correctness. "It will ask when unsure" becomes a confidence threshold with a number. "We'll just run it and see" becomes cost estimation before execution.

Score a dry run on six dimensions: estimation, architecture, depth (ambiguity, cost, failure paths), security (authorisation, RLS, validation, audit), delivery (gates, fallback, support) and communication. A miss in any one is a design gap to fix, not a phrase to memorise.

For "tell me about a project you led end to end", the same system exists as a production engagement. It is a governed data assistant for a large Asian life insurer, built in about two months on a short build engagement. Tell it as an architecture evolution with two real pivots. Lead with one sentence and then stop talking.

"At a large Asian life insurer, I built a multi-agent system to replace a BI (business intelligence) queue that took days with natural-language, self-serve answers, and my first design broke down live in testing, which is actually the more interesting part of the story."

The problem beat: actuaries, claims managers and regional analysts needed answers over governed data, but every question went through a BI queue. An ad-hoc question took days; a new dashboard took weeks.

The first-pivot beat is the failure that taught the architecture. One agent, one prompt, twenty-plus tools broke on two axes in real testing. Context bloat, because every tool schema sat in context on every turn. Tool confusion, because with that many tools competing the agent picked the wrong one often enough to be unusable. The fix was not a bigger model or better prompting. It was architectural: split deciding what to do from doing it. If asked why it broke specifically, say that stuffing 20-plus tool schemas plus full history into one prompt measurably degrades tool-selection accuracy. It is a context-bloat effect, not vague confusion.

The supervisor is an eight-node state machine.

classify_intent → clarify_or_disambiguate → resolve_assets_with_context_index
    → route_to_{genie | multi_tool | analysis | visualization} → compose_answer

Intent is classified with a confidence score. Clarification fires only below 60%. The supervisor, and only the supervisor, resolves assets against a 16-asset Context Index of Genie Spaces (now Genie Agents; Genie itself is now Genie One), metric views, tables and document indexes, with endorsed assets ranked first. It passes the resolved list to one of four specialists on shared state. Centralising resolution costs one hop of latency. It buys a single auditable source of truth for what data any answer is based on. Two workers resolving to different tables for the same question is a governance nightmare in insurance.

The governance beat sits underneath, in five pieces:

• a handful of governed metric views instead of raw fact tables
• short-term memory in a Delta table keyed by thread, short retention, so conversations survive a restart and stay auditable
• base-plus-overlay prompts in a governed table with a five-minute cache, so behaviour is tunable without a redeploy
• MLflow tracing on every node, so a wrong answer traces to the exact node and tool call
• an AI Gateway (now Unity Gateway) doing rate limiting, PII (personally identifiable information) filtering and guardrails on the model endpoints the agent calls (as of 2026 it can't guardrail the agent endpoint itself), because the raw endpoint is never exposed

The second-pivot beat is the same failure one level up. As domains grew, the supervisor's own tool list re-approached the original bloat. So the fix was applied again, as a deep-agent pattern. An orchestrator delegates to fully self-contained sub-agents, one per analytics domain: customer, distribution channels, policy and underwriting, claims. Each has its own prompt, small toolset, context window and Genie Space (now a Genie Agent). A memory-manager sub-agent owns long-term memory in a categorised table of preference, fact, decision, project and feedback. The cost was more infrastructure surface. The gain was a ceiling on tool-selection degradation that does not reappear as the system grows.

The platform-reality beat: the managed multi-agent feature (now Agent Bricks Supervisor Agent, GA (generally available) in 2026) wasn't an option for this customer at the time, so I hand-built the supervisor. More code to own, in exchange for a production path nobody outside the engagement controlled.

State the results honestly. In that engagement, the team reported time-to-insight going from days to minutes. Dashboard delivery went from weeks to governed self-serve. Adoption grew after rollout, though I can't attribute it cleanly, and it's worth saying exactly that before being asked. MVP (minimum viable product) in about two months. If rebuilt today: instrument resolution-time and accuracy metrics from day one rather than relying on tracing for post-hoc debugging, and invest earlier in the offline evaluation dataset.

Close on whichever thread the conversation ended on. On the pivots: the same failure showed up twice at two scales, and both times the fix was the same instinct, specialise and keep each unit's context small. On governance: the part that matters most at an insurer is something provably safe to hand to a regulated business user. On results: better to say exactly what can and cannot be claimed than to let a number go unquestioned.

The interviewer's pivot after a good design is "the warehouse bill and the model bill are both climbing". On this system the driver is retries on invalid or wrong SQL, and the full schema carried as input tokens on every call. The weak move is to retry with a stronger model until the SQL runs. The strong move is the design already on the board.

Add the warehouse side in the same breath. Warehouse compute per answer is the cost metric that matters: bytes scanned on BigQuery on-demand, DBU (Databricks Unit) or credit time on Databricks and Snowflake. Scan budgets and bounded date windows are the lever. A rising 7-day average is the alert. Every strong cost answer is generated by four verbs in order. Measure, by tracing and attributing first. Route, matching model and path to risk. Bound, with limits on steps, tokens, top-k, timeouts and budgets. Cache safely, with tenant, permission and version in the key.

On this design the levels separate on who owns the meaning of a number and how a wrong one gets caught, not on how good the generated SQL is.

• The failure to design against is valid SQL that confidently answers the wrong business question, named in the first two minutes.
• Requirements are stated so a test can fail them: correctness over speed, ten governed metrics as the definition of good, and an owner for every constraint.
• State has one owner each: the catalog owns schema truth, the registry owns definitions, caches are performance only, and every behaviour-changing artifact is versioned.
• One end-to-end diagram splits control plane from data plane, and the trust boundary is the read-only gateway with entitlements re-checked at execution.
• Every question anchors on a governed metric before touching a table, with a router deciding structured, semantic or both, and fixed operations preferred over open generation.
• Ambiguity triggers a clarifying question, with a numeric confidence threshold, never a guess.
• SQL is validated as a tree against policy and a scan budget, executed read-only under warehouse-native row and column security.
• Explanations are generated from the structured result, cross-checked against the table, and built on deterministic statistics.
• Authorisation fails closed and everything else degrades, with five rehearsed drills from wrong definition to summary drift.
• The release gate is semantic correctness judged by analysts, not execution success.
• Rollout runs ten metrics, golden cases, shadow analysts, then domain by domain with named owners.
• The fifty minutes are spent in proportion to risk, with the 90-second summary, the 2-minute answer and nine carrying lines ready.
• As a story, the same system is a two-pivot engagement: a monolith that failed on context bloat, a supervisor that shipped, a deep agent that scaled, with results stated as correlational.
• The cost pivot is answered by the design itself: the semantic layer and validation remove retries and the schema from every prompt.

1. Why is a wrong-definition answer more dangerous than a failed query? The SQL ran, the chart rendered and the number looks plausible, so the executive has no way to know it is wrong and acts on it.
2. What is consulted before any physical table, and why? The semantic metric registry, because "revenue" must anchor on the governed definition before the system searches for tables the model thinks look relevant.
3. What number makes "ask when unsure" testable? A confidence threshold on intent classification; the insurer supervisor clarifies below 60%.
4. Why parse SQL instead of regex-filtering it? Regex cannot understand aliases, nested selects, CTEs, comments or obfuscation; a parser inspects statements, tables, columns, joins, functions, limits and aggregate semantics.
5. Where is row-level security enforced, and what is re-checked at execution? In the warehouse, natively, with the read-only query run as the user so the policies apply to them, not to a shared service account; entitlement and policy are re-checked at the moment of execution so a user who lost access between planning and execution is refused.
6. How is a summary that contradicts its table handled? The summary is generated from the structured result and cross-checked; a mismatch returns the table alone and alerts the review queue.
7. State the fail-open versus fail-closed pattern by category. Authorisation closed; connectivity degrade or queue; ambiguity human; suspicious scan closed and alert.
8. What is the release gate, and what is it not? Semantic correctness against the governed definition judged by analyst review; not execution success on the golden set, which only proves the SQL ran.
9. Name the four rollout phases and the exit of the third. Ten governed metrics, golden cases, shadow analysts, domain-by-domain expansion; the assistant advances from shadowing when it consistently matches analyst-reviewed answers and escalation works.
10. Why did the insurer monolith fail, and why did the same fix apply twice? Context bloat and tool confusion from 20-plus tool schemas in one prompt; the supervisor's own tool list later re-approached the same bloat, so specialisation was applied again at the next level.
11. What is the sixty-second cost answer? The semantic layer and validation remove retries and the schema from every prompt, so the fix is not a stronger model retrying until the SQL runs.

Fact-check sources (checked 27 Sep 2026)

• Snowflake docs: Understanding row access policies — row policies evaluate the role running the query
• Databricks docs: Row filters and column masks — filters and masks evaluate the session user, so run queries as the user
• OpenAI API docs: Function calling — guidance to keep the tool count per turn small
• Gan & Sun 2025, RAG-MCP: Mitigating Prompt Bloat in LLM Tool Selection — tool-selection accuracy falls as the tool pool grows
• Create and manage a Genie Agent (Databricks) — analysts curate tables, instructions and example SQL
• Genie Agents concepts (Databricks) — generated queries are always read-only and use each user's permissions
• AI/BI and Genie One release notes 2026 (Databricks) — Genie renamed Genie One; Genie Spaces renamed Genie Agents
• Use the Genie Agents API (Databricks) — the Genie Conversation API for programmatic text-to-SQL
• Use dashboard APIs to create and manage dashboards (Databricks) — the Lakeview API still carries that name and can create and publish dashboards
• AI Gateway for serving endpoints (legacy), supported features (Databricks) — guardrails and rate limits not supported on agent endpoints
• Configure guardrails (legacy) (Databricks) — PII (personally identifiable information) and safety guardrails on model endpoints
• CrewAI docs: Flows — @router gives CrewAI deterministic branching
• Agent memory and sessions (Databricks) — the documented checkpointer is Lakebase (Postgres), not Delta
• databricks_langchain CheckpointSaver API — the Lakebase-backed LangGraph checkpointer
• Use Supervisor Agent to create a coordinated multi-agent system (Databricks) — the managed multi-agent option today
• Agent Bricks Supervisor Agent is Now GA (Databricks blog) — Supervisor Agent general availability
• BigQuery pricing — on-demand queries bill by bytes processed
• Databricks serverless billing — Databricks SQL bills compute time in DBUs