Superset text-to-SQL: what works and what breaks

What works well

On a clean schema, text-to-SQL on Superset handles most everyday business questions reliably:

• Aggregates on one table: "Total revenue last quarter", "Average order value by channel".
• Filters: "Orders over $500 in the West region".
• Rankings: "Top 10 customers by revenue this year".
• Simple time series: "Signups by week for the last 12 weeks".
• Counts and distincts: "Unique customers by month".

These questions map cleanly to a single SELECT ... GROUP BY, and the model rarely gets them wrong when column names are clear.

What breaks, and how to fix it

1. Joins that change the grain

Question: "Revenue by product category."

What goes wrong: revenue lives on orders, category lives on order_items. Joining them repeats each order's total once per line item, so revenue is multiplied.

-- Wrong: order_total repeated for every line item
SELECT i.category, SUM(o.order_total)
FROM orders o JOIN order_items i ON i.order_id = o.id
GROUP BY 1;

-- Right: sum at the line-item grain
SELECT i.category, SUM(i.line_total)
FROM order_items i
GROUP BY 1;

Fix: record each table's grain and which measures belong to which grain in the schema profile. Tell the model to aggregate measures at their own grain.

2. Date grains and averages

Question: "Average daily signups per month."

What goes wrong: the model averages signups across rows instead of first counting per day, or averages monthly totals when you wanted daily averages.

Fix: state the rule in the prompt: aggregate to the requested grain first, then average. Include a worked example in the prompt for "average X per Y".

3. Ambiguous columns

Question: "Revenue last month."

What goes wrong: the table has gross_amount, net_amount and amount_after_refunds. The model picks one.

Fix: mark the default measure for each business term in the profile, and let the model return a clarifying question when a term has no default.

4. NULLs and status codes

Question: "How many active subscriptions?"

What goes wrong: "active" means canceled_at IS NULL, but the model filters on status = 'active', which your billing system never updates. Or status = 3 means cancelled and the model has no way to know.

Fix: write business definitions as SQL once (active: canceled_at IS NULL) and map code values to labels during profiling.

5. Time zones

Question: "Orders yesterday."

What goes wrong: timestamps are stored in UTC; "yesterday" for a team in India or California starts at a different moment. Daily counts do not match the store dashboard.

Fix: set a workspace time zone and have the prompt convert timestamps before truncating to dates.

6. Business definitions that differ by team

Question: "Churn rate by plan."

What goes wrong: finance means revenue churn, product means logo churn, and some count downgrades. Every answer is "right" by one definition.

Fix: agree on one named definition per metric, store it, and have the answer state which definition was used.

7. Very wide or many tables

Question: anything, on a schema with hundreds of tables.

What goes wrong: the whole schema does not fit in the prompt, or the model picks a similarly named table from the wrong domain.

Fix: retrieve only the relevant tables for each question (by name, description and past usage), and prefer curated, pre-joined views for common topics.

Quick reference

Measure it

Collect 50 to 100 real questions, write the correct SQL or answer for each, and rerun the set after every change to prompts, profiles or models. Track correct, clarified and wrong separately. A drop in "wrong" matters more than a rise in "correct".

How DashboardGPT handles these

DashboardGPT profiles columns on upload or connection, detects types and roles, runs queries through Apache Superset and shows the query behind every chart, so you can check the grain, filters and definitions used.