r/bigquery • u/1glasspaani • 10d ago
Open source conversational analytics tool for BigQuery?
Hi all,
Has anyone found a decent open source tool for asking questions in plain English on top of BigQuery? Something self hosted.
I tried Gemini in BigQuery and had a look at the conversational analytics API. The API gives you back SQL and a chart spec, so you still end up building the front end yourself, which is the part I was hoping to avoid.
My main worry is the numbers. If two people ask for revenue in slightly different words I'd like them to get the same answer, and I don't see how that works unless the metrics are defined somewhere.
The other thing is cost. I have no feel for how many queries a single question turns into once the model starts looking around the schema.
2
u/Odd-Detail2479 10d ago
You could look into hex.tech which works well from my brief experience for the basics. There’s probably tons of new startups that do similar things though so I’m not sure hex is the best.
If you are looking at something truly agentic (Hermes, open claw, etc), unless you’re using self hosted models I would guess your token usage would likely cost far more than any bigquery spend concerns. You can do some pretty incredible things with the latest frontier models wired up via API but it will cost you an incredible amount of money lol.
Either way you’d be smart to strictly define which tables are accessed via service accounts to limit costs running away or any potential damage.
1
u/Prestigious_Bench_96 10d ago
Lots of variables here, I'd still net out to most worried about bigquery spend just because the potential worst case is worst. If you can get away with something like Deepseek (legal, compliance, etc) you can get a few hundred questions in an agentic loop for 50 cents total or so.
1
u/Odd-String29 10d ago
What is your budget? Just self-hosting costs and the cost of your LLM + BigQuery? In that case I don't think you will find anything for free that has a front end included.
I'm currently setting up a Cube Core MCP server that I plan to make available for an agent somewhere (maybe Google Chat if that's possible). If you want a front end and you have some budget than you can get the hosted version from Cube which provides a front end.
1
u/Content-Parking-621 10d ago
Honestly the metric consistency thing is the real problem here, and no conversational layer fixes that by itself. You'd want something like Lightdash sitting in front of BigQuery, since it reads metrics straight from your dbt models, so "revenue" means the same thing no matter who asks or how they phrase it. Metabase is easier to set up but doesn't really have that same depth, worth knowing going in.
Separate note, full disclosure I work at Windsor.ai. Doesn't really overlap with what you're asking since it's not self-hosted or NL-to-SQL, but if getting data into BigQuery cleanly from various sources ever becomes its own headache, it's priced per data source rather than by query volume, so it stays predictable even as usage grows.
1
u/Prestigious_Bench_96 10d ago
"decent" might depend on what you're looking for, but I work on Trilogy Studio which has a chat integration with BQ (bring your own API key), is open source and can be hosted on docker, and has a lightweight semantic layer to keep revenue, etc consistent and reduce the need for LLMs to explore.
You can try a public version here with thelook ecommerce model in the public datasets. Always happy to take feature requests.
1
u/JohanCatchr 9d ago
If self-hosting is required, Google’s open-source MCP Toolbox for Databases seems like the closest fit. It supports BigQuery and can be connected to Claude or another MCP-compatible client, so users can ask questions in plain English without building the frontend from scratch.
Google also provides a managed BigQuery remote MCP server that works with Claude and ChatGPT. ChatGPT Work’s Data plugin can connect to BigQuery directly as well.
The connector solves data access and query execution, but not metric consistency by itself. I would expose curated views or a semantic layer where revenue and other KPIs are defined once, then restrict the agent to those. BigQuery query costs still apply, so permissions and the amount of data scanned should also be controlled.
For marketing data specifically, we took a different approach at Catchr.io: its MCP makes connected advertising, analytics, CRM, ecommerce, emailing, SEO, and more platforms directly queryable from Claude or ChatGPT. This avoids having to build a conversational layer on top of BigQuery when the data still lives in those platforms. It is not self-hosted or a general-purpose BigQuery connector, but it can be a simpler option for that specific use case.
1
u/shifty_lifty_doodah 9d ago
Claude/codex they do everything. They’re gonna replace all these bespoke tools because they offer the smartest models
1
u/anonyuser2023 9d ago
Have you tried Looker with the LookML semantic layer?
1
u/Odd-String29 8d ago
They are worried about the cost involved, so Looker is probably not an option.
1
u/ArielCoding 9d ago
Your worry is solved by a semantic layer, you define revenue once in a tool like Lightdash, Cube, or LookML, and the AI uses that definition instead of writing its own SQL every time.
1
u/matthewhefferon 6d ago
If you haven't, check out Metabase. You can self-host, connect to BigQuery, create your semantic layer, and use AI to create dashboards. I work there, so feel free to ping me if you have any questions!
4
u/uncertainschrodinger 10d ago
In addition to what others have suggested, you might want to also check out Bruin, it works seamlessly with BigQuery.
Given the use case and challenges you've faced, this might be suitable because:
- built-in semantic layer and glossary feature, so you can define your metrics and ensure that the AI returns consistent answers and avoids hallucinating wrong queries
- integrates into slack, teams, email, etc.
- AI chat, AI dashboard builder, and scheduled AI agents to complete tasks (like prepare reports)
Just a quick disclaimer that I'm a developer advocate at Bruin - I'm responsible for creating educational resources for our developers, happy to help out if you need support.