Period vs Period Comparisons in LLM Chat Contexts

[Angel-RC]

Context

I'm building a chat interface powered by an LLM where users can ask natural language questions about their data. BSL + MCP is working great for standard metric queries, but I'm hitting a wall with period-over-period comparisons.

Users naturally ask things like:

• "How did sales compare to last month?"
• "Show me this week vs the same week last year"
• "Revenue vs previous quarter by store"

The Problem

BSL's query() interface is intentionally constrained (which is a feature for LLM reliability), but period-over-period comparisons inherently require either:

1. Two separate queries + client-side comparison, or
2. A self-join / window function over time — which falls outside BSL's current abstraction

Neither option is clean when the LLM needs to resolve arbitrary period comparisons dynamically (the user might ask for WoW, MoM, YoY, or same-week-last-year depending on context).

What I've Considered

Option A — Two queries, LLM computes the delta
Instruct the LLM via system prompt to make two query_model calls and compute the variation itself. Simple, but adds latency and relies heavily on prompt engineering to get the period offsets right.

Option B — Hardcoded period_label dimension
Add a computed dimension like current_month / prev_month / prev_year. Works for fixed periods but breaks for arbitrary user requests.

Option C — Native offset_window support
Something like dbt MetricFlow's offset_window, where a derived metric can reference another metric shifted by a time offset. This would let you define revenue_prev_month as a first-class measure.

Option D — A dedicated compare_periods() query method
A higher-level API that accepts two date ranges and returns a unified result with current, previous, and delta columns — designed to be LLM-friendly.

Question

What's the recommended approach for period-over-period comparisons in an LLM chat context with BSL? Is there a pattern the team has in mind, or is this a gap you're looking to address?

[hachej]

Hi @Angel-RC, yes, this is doable today with chained expressions and mutate(). The harder part is MCP / the constrained query() interface. Option A works as a workaround today, but I agree a native solution would be better, likely
somewhere around Options C or D.
My concern is that this may push us toward building a custom DSL on top of Ibis. An alternative could be exposing a more generic query_python() method, where the LLM can pass native Python query expressions directly.
What do you think?
@hussainsultan