DataSphere AI BI

Problem & responsibility

Business problem: a SQL query executing is not the same as the business answer being right — ambiguous metrics must be clarified and results must be reproducible. As a core developer on a small team, I owned the semantic layer and domain routing, query safety, and evidence binding, and supported the lightweight lakehouse.

System flow

1. Intent routing
2. Schema retrieval
3. Semantic parse
4. SQL generation
5. Safety checks
6. Evidence binding
7. Charts & reports

Engineering focus

Semantic Layer

Industry domain packs hold metric definitions and term aliases; a two-level intent contract separates business understanding from SQL implementation.

Query Safety

Layered defenses — permission context, governed tools, read-only SQL AST review, and output masking — with tenant isolation injected server-side.

Evidence Binding

Ambiguous metrics must be clarified; charts bind call_id + query_fingerprint + result_hash and degrade instead of blocking.

Evidence

Eval

end-to-end success ≈ +13pt (cross-industry set)

Recall

schema recall ≈ +14pt (fixed set)

Safety

all adversarial requests blocked · ~3% false blocks

Cost

analysis context tokens ≈ −80%

Trust

charts bind call_id + fingerprint + hash

Python · FastAPI · PostgreSQL · DuckDB · Parquet · R2 · Univer · MCP

Design tradeoffs

1. Workflow drives the main flow; the agent handles only local decisions

   Why

   A fully fixed pipeline cannot absorb the variation in natural language, while a fully autonomous agent loop makes permissions, cost, and business preconditions uncontrollable.

   Cost

   States and transitions must be designed explicitly, so changing the main flow is much slower than editing a prompt; cross-domain preconditions get written into code — flexibility traded for control.

2. Settle metric definitions in a semantic layer instead of re-deriving them per query

   Why

   A SQL query executing is not the same as the business answer being right — whether “revenue” means order value or paid value has to be defined once by the business, not guessed by the model every time.

   Cost

   Domain packs, metrics, dimensions, and term aliases all need ongoing maintenance; onboarding a new source starts with semantic assets, which is slower than pointing the model straight at a database.

3. Charts must bind evidence; a failed binding degrades rather than blocks

   Why

   Valid JSON only guarantees the fields exist, not that the numbers came from a real query or that the model did not fill them in. But killing an entire answer over one chart costs too much.

   Cost

   call_id, query_fingerprint, and result_hash all have to be threaded from tool call to render, and any change to one has to be mirrored; degradation also means users occasionally see a chart marked untrusted.

4. Fail closed on stale data instead of showing old results

   Why

   If you cannot prove the data is current, you should not produce a “current” conclusion. Silently showing stale numbers is far more dangerous than an explicit error.

   Cost

   Availability drops — when a source wobbles the user gets a failure reason instead of an answer, and extra caching and retry strategy is needed to keep that rare.

What this system does not do

Deliberate non-goals. A boundary that is written down is a boundary that can be held.

• Does not replace ERP, CRM, e-commerce, or finance systems for business transactions
• Does not replace building a full offline warehouse, real-time warehouse, or large-scale governance platform
• Does not automatically repair missing, wrong, or long-stale data in source systems
• Does not let the model bypass permissions to reach every database, system table, or sensitive field
• Does not give the model arbitrary write access to production databases
• Does not let AI unilaterally define revenue, active users, profit, or other official business definitions
• Does not treat model-generated explanation as a proven causal conclusion
• Does not force a confident answer when data quality, semantics, or permissions are insufficient

Open questions

What I do not have an answer for yet. Happy to be asked about any of these.

• Semantic asset maintenance grows linearly with the number of sources, and there is no good way yet to keep it in step with upstream schema changes automatically
• The system can detect and clarify conflicting metric definitions, but who has the authority to rule is an organizational question — technology can only surface the conflict
• Result sanity checks rely on structural signals (grain, join fan-out, unit consistency), so a query that is physically valid but business-nonsensical still passes
• For cross-source joins under read-only, time-limited, row-limited constraints, the balance between performance and safety is still unsettled