Text-to-SQL Dashboards: Conversational Analytics Without a BI Team

Your team does not need a data warehouse migration or three new hires to answer “how did returns trend last month”. They need to ask in plain English and trust the number that comes back.

Text-to-SQL makes that possible, but only if you build it as a governed system. A model pointed at raw tables will confidently invent columns, botch joins and hand back numbers that are wrong in ways nobody notices. That is worse than no system at all.

Who This Is For

• Ecommerce and ops teams drowning in “quick data question” requests to one overloaded analyst.
• Founders who want self-service analytics but cannot justify a full BI stack or team.
• Analysts who want to stop being a human query queue and own the layer instead.
• Anyone who has tried a raw “ask your database” tool and been burned by silent wrong answers.

What You Will Need

• A read replica or read-only role on your production or warehouse database.
• A handful of well-named, well-joined curated views: the semantic layer.
• An LLM with structured output (any capable model; the model is not the hard part).
• A validation layer you control: SQL parsing, an allow-list, enforced row limits.
• A results log so you can see what was asked, what SQL ran and whether it was right.

If you have not built dashboards without heavy tooling before, my walkthrough on building dashboards without BI tools in Python is a good primer for the plumbing underneath.

The Architecture

The whole design is a funnel that narrows what the model can do at every step. The question is open; everything after it is constrained.

Notice the model only ever sees a curated schema, never the raw tables. And its output is treated as untrusted until it passes the validator and the result check. Trust is earned by the guardrails, not granted to the model.

Start With a Semantic Layer, Not Raw Tables

This is the decision that makes or breaks the system. If you let the model write SQL against orders, order_items, refunds_v2, refunds_legacy and eleven join keys, it will get the joins wrong and you will never know which answers are poisoned.

Instead, hand it a small set of curated views with obvious names, correct joins baked in, and clean grain. The view does the hard join once, correctly, and the model just filters and aggregates.

-- Curated view: one clean row per order, joins already correct.
CREATE VIEW analytics.orders_enriched AS
SELECT
  o.order_id,
  o.placed_at::date        AS order_date,
  o.channel,
  o.country,
  c.segment                AS customer_segment,
  o.gross_revenue,
  o.discount_amount,
  o.gross_revenue - o.discount_amount AS net_revenue,
  COALESCE(r.refund_amount, 0)        AS refund_amount,
  (r.refund_amount IS NOT NULL)       AS was_refunded
FROM sales.orders o
JOIN crm.customers c   ON c.customer_id = o.customer_id
LEFT JOIN sales.refunds r ON r.order_id = o.order_id;

Now a question like “net revenue by channel last month” becomes a trivial GROUP BY channel over one view. The model cannot get the refund join wrong because it never touches the refund table. This is the same principle behind a proper semantic layer for trustworthy dashboards. The layer is where correctness lives.

Document each view for the model: column names, types, units, allowed values for enums like channel. That documentation is your grounding.

Grounding the Model on the Schema

The model needs to know exactly what exists and nothing more. Feed it only the curated views’ schema in the system prompt: column names, types, descriptions, sample enum values. Never dump the raw catalogue.

Good grounding looks like a compact contract:

• Available views and their grain (“one row per order”).
• Every column with type and a one-line meaning.
• Units and gotchas (“gross_revenue is in GBP, excludes tax”).
• Allowed enum values (“channel is one of: web, marketplace, retail”).

With a tight schema in front of it, the model stops guessing. Most hallucinated columns come from the model filling a gap you left open. Close the gap and the guessing largely stops.

Validate the Generated SQL Before It Runs

Never execute what the model produced without checking it. Parse the SQL, confirm it is a single read-only statement, confirm every table and column is on the allow-list, and force a row limit. Reject anything else with a clear reason.

import sqlglot
from sqlglot import exp

ALLOWED_TABLES = {"analytics.orders_enriched", "analytics.daily_metrics"}
MAX_ROWS = 1000

def validate_sql(raw: str) -> str:
    tree = sqlglot.parse_one(raw, read="postgres")

    # 1. Single statement, SELECT only — no INSERT/UPDATE/DELETE/DDL.
    if not isinstance(tree, exp.Select):
        raise ValueError("Only SELECT statements are allowed.")

    # 2. Every referenced table must be on the allow-list.
    for table in tree.find_all(exp.Table):
        name = f"{table.db}.{table.name}" if table.db else table.name
        if name not in ALLOWED_TABLES:
            raise ValueError(f"Table not permitted: {name}")

    # 3. Ban anything that could mutate or escape (functions, CTEs to unknowns).
    for func in tree.find_all(exp.Func):
        if func.sql_name().upper() in {"PG_SLEEP", "DBLINK", "COPY"}:
            raise ValueError(f"Function not permitted: {func.sql_name()}")

    # 4. Force a hard row cap regardless of what the model wrote.
    tree = tree.limit(MAX_ROWS)

    return tree.sql(dialect="postgres")

Parsing beats regex here. sqlglot gives you the real structure, so you check intent, not string patterns. If validation fails, you do not silently fix it; you tell the user the question could not be answered safely and log it.

Read-Only Guardrails at the Database

Application-layer validation is your first fence, not your only one. The connection the query runs on must be physically unable to do harm. Belt and braces.

Concretely:

• A dedicated read-only database role with SELECT only, no write grants, no access to sensitive PII columns.
• Point it at a read replica, so a runaway query never touches production write load.
• A statement timeout (say 5 seconds) so a bad query dies instead of hanging the box.
• The enforced LIMIT from the validator, so nobody accidentally pulls ten million rows into a chat window.

If someone gets a malicious question past the model and the parser, the database role still says no. That layering is the point.

Show the SQL: Trust Comes From Visibility

Always display the generated SQL alongside the answer. Not hidden behind a toggle by default for power users. Visible. This does three things: it lets a technical user spot a wrong assumption, it teaches everyone what the data actually contains, and it makes the system honestly fallible instead of a black box.

An answer that says “net revenue was £412,900 last month” with the SQL shown is trustworthy. The same number with no SQL is a guess you are being asked to believe. Showing your work is how a text-to-SQL layer earns the right to be used for decisions.

Verify the Result Before You Show It

The query can be valid SQL and still return nonsense. Add cheap sanity checks between the database and the answer:

• Empty or single-null result where a number was expected → flag, do not present as “£0”.
• Wildly out-of-range values against known bounds (negative revenue, a conversion rate above 100%) → flag.
• Grain mismatch: the question asked “by month” but one row came back → flag.
• Freshness: if the view’s max date is stale, say so rather than implying it is current.

When a check trips, do not fabricate a confident answer. Say “this looks off, here is the SQL, please check”. A visible uncertainty beats an invisible error every time.

Cache Verified Queries

Once a question-to-SQL mapping has been validated and a human has confirmed the answer is right, cache it. The same questions get asked constantly: “revenue this week”, “top products”, “return rate”. A cache of blessed queries means those hit a known-good path instead of regenerating and revalidating every time. It is faster, cheaper, and it turns your most common questions into effectively pre-built metrics.

When to Fall Back to Pre-Built Metrics

Text-to-SQL should not answer everything. For your handful of load-bearing numbers, the KPIs a decision actually rests on, build them as fixed, tested metrics and route matching questions straight to them. The model is for the long tail of ad-hoc questions, not for recomputing your headline revenue figure a slightly different way each time. If you want the model to also orchestrate across those metrics, that is where agentic dashboards with MCP and A2A come in. But the governed metric stays the source of truth.

Naive vs Governed Text-to-SQL

Dimension	Naive text-to-SQL	Governed text-to-SQL
Schema exposed	Raw tables, all columns	Curated views only
Joins	Model guesses each time	Baked into the views
Generated SQL	Runs as-is	Parsed, allow-listed, row-capped
Database access	App’s normal role	Read-only role on a replica
Wrong answers	Silent and confident	Flagged by result checks
SQL visible to user	Usually hidden	Always shown
Common questions	Regenerated every time	Cached, blessed queries
Failure	Bad number in a decision	”This looks off, check the SQL”

Failure Modes to Avoid

Hallucinated columns. The model invents orders.profit_margin because your grounding was vague. Fix: tight schema, and validation that rejects any column not on the allow-list.

Wrong joins. Point it at raw tables and it will fan out rows on a one-to-many join and double-count revenue. Fix: joins live in the views; the model only filters and aggregates.

Silent wrong numbers. The query is valid, runs cleanly, and returns a number that is incorrect: a subtle filter the model added, a stale view, a grain mismatch. This is the dangerous one because nobody questions a confident figure. Fix: result verification, freshness checks, and always showing the SQL so a human can catch it.

What to Build First

1. Build 3-5 curated views that answer 80% of the questions you get asked. This is the work that pays off most. Do it before touching an LLM.
2. Stand up a read-only role on a replica with a statement timeout and no PII. Prove nothing can write.
3. Write the SQL validator: parse, allow-list tables and columns, force a LIMIT, reject the rest. Test it against malicious inputs.
4. Wire the LLM with tight schema grounding, and always render the generated SQL next to the answer.
5. Add result checks and a verified-query cache, then log every question so you can see real accuracy and improve the views.

Final Take

A text-to-SQL system that is confidently wrong is worse than having no system, because it launders bad numbers into decisions with a friendly interface. The way you make it trustworthy is not a bigger model. It is constraint. A governed schema so the model cannot get joins wrong. Validation so it cannot run anything unexpected. A read-only replica so it cannot do harm. Visible SQL and result checks so mistakes surface instead of hiding.

Get those right and you can genuinely give a non-technical team conversational access to their ecommerce data, without a BI team, and without lying to them. That is the whole game.