SQL That Reads Between the Lines

The Idea

What if you could write a SQL query that reads thousands of restaurant reviews and produces a structured operational dashboard — complete with severity-rated issues, trend analysis, and actionable recommendations? Not by counting keywords, but by actually understanding what customers are saying?

That's what 🚜 Query.Farm enables. Our VGI (Vector Gateway Interface) connects DuckDB to Python workers via Apache Arrow. We built qf_llm_summarize — an aggregate function that accumulates text during GROUP BY, then calls Claude to produce structured analysis in the finalize phase. It works with any GROUP BY, any data source, any prompt.

1 The Data

We used the Yelp Open Dataset — specifically, every review of Commander's Palace in New Orleans, one of America's most iconic restaurants. We sampled up to 20 reviews per month from January 2011 through January 2022.

2 The Pipeline

Everything runs from a single ./run.sh script. Five stages, each building on the last:

3 The SQL

The core of the pipeline is a single SQL pattern: GROUP BY + qf_llm_summarize(). The aggregate function accumulates review text, then calls Claude to analyze it.

-- Monthly analysis: one LLM call per month
SELECT month,
       qf_llm_summarize(
           review_text,
           'Analyze these reviews for the owner.
            Return JSON with momentum, strengths,
            issues, action_items, and summary.'
       ) AS analysis
FROM reviews
GROUP BY month;

4 Topic Distillation with qf_llm_distill

Here's the problem: 133 monthly LLM analyses produced ~700 unique category labels. The LLM called the same concept different things in different months — "weekend-brunch-slow-pacing", "brunch-service-delays", "sunday-brunch-understaffed" are all the same issue. Without normalization, the dashboard would be an unreadable wall of one-off labels.

We built qf_llm_distill — an aggregate function that takes messy labels and produces a clean multi-level taxonomy. One SQL call replaces the entire normalization pipeline:

-- One call: 700 labels → 30 categories → 6 themes
SELECT qf_llm_distill(label, [30, 6]) AS taxonomy
FROM all_labels;

The output is a JSON map. Every input label gets a complete path through all levels:

{
  "weekend-brunch-slow-pacing": ["service-timing", "Service & Staff"],
  "bread-pudding-souffle-hit": ["desserts", "Food & Menu"],
  "host-stand-rude": ["reservations-hosting", "Operations"],
  ...
}

How It Works Internally

The [30, 6] parameter means: first reduce to ~30 categories, then reduce those to ~6 themes. The function runs a pyramid of LLM calls with validation at each level:

A Simple Example

20 programming labels distilled into 5 categories and 2 themes:

SELECT qf_llm_distill(label, [5, 2])
FROM (VALUES
  ('python-web-framework'), ('javascript-react-hooks'),
  ('css-flexbox-layout'), ('python-data-science'),
  ('sql-query-optimization'), ('python-machine-learning'),
  ('css-grid-system'), ('javascript-vue-components'),
  ('html-semantic-markup'), ('sql-join-performance'),
  -- ... 20 labels total
) AS t(label);

Result:

20 labels → 5 categories → 2 themes. Every label mapped. One SQL call.

5 How qf_llm_summarize Works

Under the hood, qf_llm_summarize is a VGI aggregate function with three phases:

The prompt is a regular column, not a constant. This means different groups can get different prompts — critical for hierarchical summarization where each level needs a different analytical lens.

The Python Behind It

These aren't black boxes — they're Python classes you can read and extend. Here's the essence of qf_llm_summarize, stripped to its core:

class SummarizeFunction(AggregateFunction[SummarizeState]):

    class Meta:
        name = "qf_llm_summarize"

    @classmethod
    def update(cls, states, group_ids, text, prompt):
        # Just accumulate text. No LLM calls.
        for i in range(len(group_ids)):
            gid = group_ids[i].as_py()
            s = states[gid]
            states[gid] = SummarizeState(
                texts=s.texts + "\n---\n" + text[i].as_py(),
                prompt=prompt[i].as_py(),
            )

    @classmethod
    def combine(cls, source, target, params):
        # Merge two partial states from parallel workers
        return SummarizeState(
            texts=target.texts + "\n---\n" + source.texts,
            prompt=target.prompt,
        )

    @classmethod
    def finalize(cls, group_ids, states, params):
        # Now call the LLM — once per group, in parallel
        results = []
        for gid in group_ids:
            s = states[gid.as_py()]
            response = anthropic.Anthropic().messages.create(
                model="claude-sonnet-4-20250514",
                messages=[{"role": "user",
                           "content": s.prompt + "\n\n" + s.texts}],
            )
            results.append(response.content[0].text)
        return pa.record_batch({"result": pa.array(results)})

That's a real DuckDB aggregate function. update() accumulates rows, combine() merges parallel workers, finalize() calls Claude. DuckDB handles the GROUP BY, parallelism, and memory management. Your Python just defines the logic.

6 What the Dashboard Shows

7 Why This Matters

Traditional review analysis counts stars and extracts keywords. This approach reads and understands the reviews. A 4-star review that says "great food but I got sick afterward" gets flagged as a severity-5 safety issue despite the high rating. A 2-star review that's really about parking doesn't tank the food score.

The entire pipeline is SQL-native. No Python ML pipelines, no separate ETL jobs, no dashboarding tools. DuckDB reads the data, VGI aggregate functions call Claude, and Jinja2 renders the HTML. The same qf_llm_summarize() function works for monthly analysis, category normalization, and theme grouping — it's just different prompts.

And it costs about $3 total to analyze 11 years of reviews for one restaurant. Scale it to a restaurant group with 50 locations and you're looking at $150 for comprehensive operational intelligence across every location, every month, with actionable specifics.