Skip to main content

Decision Models in the Query LoopModel Answers Go Back into the SQL, and the Controls Check What Changed

In the MerRec case study, we rank 5,000 brands by betweenness centrality in a graph of shared fans to find the brands that connect different shopping communities. But when you run this on real marketplace data, you run into an immediate catalog problem: twelve of the top twenty entries aren't independent brands at all. They are retailers (Walmart, JCPenney, Macy's), store brands (Mainstays), or catch-all bucket labels that sellers select from the brand dropdown (As Seen on TV, Customized & Personalized, Eyeshadow).

Because shoppers across every category buy from Walmart, Walmart creates artificial shortcuts between worlds that no actual manufacturer provides. If you want an honest picture of brand affinity, you have to get those catalog artifacts out of the graph. This post walks through how we pull them out, why naive filtering fails, and how the ranking shifts once the graph is rebuilt.

A decision model does part of the work. Distinguishing a catch-all label from a real brand requires subjective judgment about messy catalog text, and a hosted decision model (Jev 1.13) can answer that for a fraction of a cent. But the heavy lifting still belongs to the query engine: we have to rebuild the entire graph without the rejected nodes, and we need rigorous controls to verify what the model actually changed. On Algeon, recomputing the graph across all 182.9 million like events takes just nine seconds, making it practical to run iterative passes, evaluate alternative time windows, and benchmark against baseline controls.

Deleting from the output is not enough​

The most tempting shortcut is post-filtering: just run the query and add a WHERE brand NOT IN (...) on the final results. But graph metrics do not work that way.

Betweenness centrality measures how often a node sits on the shortest paths between pairs of other nodes. When Walmart is in the graph, millions of shortest paths route straight through it. That inflates Walmart's score, but it also depresses or distorts the centrality of every other brand around it. Simply deleting Walmart from the final output table leaves everyone else's distorted scores untouched. To get true shortest paths, you must remove the node from the graph itself and let the network reroute.

Because of that, the model's decisions have to feed back into the query. The original query uses a CTE to construct the fan graph; the rebuilt query joins that CTE against a decisions table, pruning any node that the policy rejected or flagged for review.

WITH fans AS (
SELECT -(e.user_id + 1) AS src, CAST(i.brand_id AS BIGINT) AS dst
FROM merrec_events e JOIN merrec_items i ON i.item_id = e.item_id
LEFT JOIN brand_ontology o ON o.brand_id = CAST(i.brand_id AS BIGINT)
WHERE e.event_type = 'item_like' AND i.brand_id <> 0
AND (o.brand_id IS NULL OR o.band = 'accept')
AND e.event_time >= TIMESTAMP '2023-05-01' AND e.event_time < TIMESTAMP '2023-11-01'
GROUP BY e.user_id, i.brand_id

The loop​

We structure the workflow as a five-step loop, where two of the steps are standard SQL queries.

registry: one call per distinct staterowstyped answersbandsregistered relationthe recompute is the next probeGPU QUERYProbegraph UDTFs over all rowsbounded candidate set + contextHOSTED MODELDecision modelfixed question set per candidatechoice · probability · scoreAPPLICATION RULEPolicyaccept ≥ 0.70 with the kindreject < 0.30 · else reviewPARQUET · CREATE EXTERNAL TABLEDecision tableone row per candidateband · kind · probabilitiesGPU QUERYRecomputesame query joined to the tableall rows, no host boundary
A probe query bounds the set of entries worth asking about and collects their context. A hosted model answers a fixed question set per entry. The application's policy turns the answers into bands, the bands become a Parquet table registered with the server, and the recompute is the original query joined to that table.

Calling an LLM on every one of the 5,000 brands would be wasteful and slow. Instead, we use a SQL probe, which is just the original bridges query, to identify the top 120 nodes dominating centrality. A second SQL query then gathers catalog context for each candidate: active listings, distinct categories, top category paths, mean price, and four representative titles. The model only inspects this catalog text, never the graph topology or centrality scores.

For each candidate, the model answers three structured questions:

  1. Classification into one of four kinds (standalone brand, multi-brand retailer, superstore house brand, generic catch-all) with a confidence probability.
  2. Direct probability for a binary check: is this a standalone brand?
  3. A score from 0 to 3 indicating whether the entry conflates unrelated communities.

Our application policy cross-checks these answers deterministically: a probability of 0.70 or higher paired with standalone_brand is accepted, anything below 0.30 is rejected, and ambiguous cases (such as high probability alongside an unexpected kind) route to a human review queue. Items in the review queue remain excluded from the graph until manually confirmed.

To keep queries deterministic and inexpensive, every decision is cached in a registry keyed by the hash of the catalog context. Because LLM responses can fluctuate across runs, caching ensures that subsequent recomputes are stable, reproducible, and incur zero model latency.

Two passes rebuild the ranking​

When we ran the first pass and dropped the rejected entries, the graph recomputed, and 59 new brands surfaced into the top 120 that were previously masked by the superstores. A second pass judged those newcomers and recomputed once more, leaving only 26 unjudged entries at the tail of the top 120 (which a third pass could clean up if needed).

PassEntriesNewAcceptReviewRejectModel callsCostWall
11201206330270$0.00545.4 s
2120593413120$0.00266.3 s
ORIGINALPOST-FILTERPURIFIED611Samsung2382Avon1853Nicole Miller65304Conair31145Laura Ashley1546Ellen Tracy827IZODnot in top 120not in top 1208Bella66319Black & Decker10310Gibson19611Charming Charlie261012Crayola412013Rue21271114Warner Bros.21715L'Oreal
The purified top 15 and where each brand stood in the published ranking and in the published ranking with the rejected entries deleted after the fact. Post-filtering keeps 11 of the 15 but in another order, and it cannot place Bella, which was not in the original top 120.

The structural impact on the ranking is striking. Samsung jumps from sixth to first place, multiplying its betweenness centrality by 2.0x. Even more telling is what happens to personal care and tool makers: Conair leaps from rank 65 to 4, and Black & Decker climbs from 66 to 9. Once artificial retail hubs are cleared out, practical household and hardware brands emerge as the true bridges connecting disparate shopping circles.

The review queue catches borderline cases where automatic decisions would be risky. For instance, Claire's received a standalone brand probability of 0.89, yet its classification picked multi_brand_retailer at 0.98. Because the signals conflicted, our policy safely held it out of the graph and flagged it for human review. Across both passes, 43 entries entered this queue.

Validating with a hand list and a second window​

Whenever you insert an LLM into an analytical pipeline, healthy engineering skepticism demands two sanity checks:

  1. Could we have achieved the same result with a simple hand-curated denylist?
  2. Does the model generalize across time, or is it brittle to changing data?

To test the first question, we compared the model against a curated list of forty-nine retailers and catch-all labels. Recomputing with either approach yields almost the same macro outcome: 13 of the top 15 purified brands are identical, and hardware brands rise in both. But the differences highlight where automation shines: the model caught 12 subtle catch-alls that the human list missed (such as Bioworld, Custom Accessories, and Pink Rose), while explicitly flagging uncertain brands in the review queue rather than forcing a silent guess.

The second check tested temporal generalization using a September–October window. Because shopping behavior evolves, the probe surfaced a different top 120. Here, our cached registry paid off: 186 of 240 lookups were instant cache hits, and the remaining 54 calls cost just $0.0024. Meanwhile, the static human list began to decay immediately—it contained 7 names absent from the new window and failed to flag 18 seasonal retailers like Amazon, Sephora, and QVC. A static list quickly goes stale; the query loop adapts dynamically to whatever the probe returns.

Solving entity fragmentation with the same loop​

The exact same loop pattern tackles another classic data engineering headache: duplicate and fragmented entities. In marketplace catalogs, brands frequently exist under multiple variations, such as TXT vs. Tomorrow X Together, Bape vs. A Bathing Ape, Dior vs. Christian Dior. Every analytical metric computed on those brands is splintered across fragments.

Pure string matching cannot resolve acronyms or rebrands, while prompting a model on all 199,990,000 possible pairs across 20,000 brands is economically out of the question, costing roughly $10,277.

Once again, a SQL probe makes the problem tractable. We run a pairing query that computes fan Jaccard similarity across the top 2,000 brands, narrowing the candidate pool down to the 120 highest-overlap pairs. The model then evaluates only those 120 candidates using their catalog metadata, completing in 5.6 seconds for just $0.0062. The policy accepts 18 pairs, flags 7 for review, and rejects 95. Crucially, the model avoids merging related entities that share heavy fan overlap but are distinct commercial entities, such as Hello Kitty and Sanrio, or Konami and Yu-Gi-Oh!.

We treat the accepted pairs as edges in a graph. Running cugraph_weakly_connected_components in SQL assigns each cluster a canonical identifier, and an aggregation over like events calculates the true deduplicated audience size.

BrandMost fans on one idBoth ids summedDistinct fansGain
Christian Dior38,88376,50957,906+48.9%
Yves Saint Laurent23,89546,35535,012+46.5%
Children's Place22,07239,62031,954+44.8%
Ann Taylor LOFT25,21136,02330,029+19.1%
A Bathing Ape13,24525,86519,591+47.9%
Tomorrow X Together13,88022,14715,425+11.1%

The correction is substantial. If an analytics report only tracked the primary ID for Dior, it missed nearly a third of the brand's total followers. On the other hand, if a naive script summed both IDs without deduplication, it double-counted a quarter of the audience. The full table of 18 unified clusters is available on the case study page.

As a baseline comparison, standard trigram similarity across all 20,000 brands only recovers 9 of the 18 merged pairs, completely missing non-textual aliases like TXT, Bape, Dior, and LOFT. Furthermore, the trigram heuristic produces false positives by eagerly merging sub-brands like Air Jordan with Jordan, or Crystal with Crystal Rock.

Practical lessons for decision loops​

The design pattern here reflects a clear separation of concerns between models and engines: let the model handle fuzzy semantic judgment on small, bounded batches, and let the GPU query engine handle large-scale filtering, graph algorithms, and recomputation.

  • Use SQL probes to bound LLM costs. Evaluating 120 targeted pairs cost less than a cent; brute-forcing 20,000 names would have cost ten thousand dollars. The query engine acts as an intelligent funnel.
  • Fast recomputes enable honest validation. Because rebuilding the 183M-event graph takes only nine seconds on the GPU without host data transfers, running multi-pass refinements, historical comparisons, and sanity-check controls is cheap enough to be standard practice.
  • Keep decisions typed and deterministic. Models should return structured classifications and confidence scores, not freeform text. Caching decisions by context hash eliminates nondeterministic query drift, while a review band keeps human oversight where uncertainty is high.

When the database engine is fast enough to make graph recomputation trivial, you do not need to compromise with inaccurate post-query hacks. You can feed verified model judgments directly back into the query loop and compute exact answers.

Reproduce​

Both workflows are documented in depth on the MerRec case study page with their full SQL, data tables, and provenance. The dataset files (bridges and twins) contain all question sets, decision policies, executed queries, and pipeline metrics under CC BY-NC terms. You can find the runnable runner in the repository under fixture/demo/decision_models. Running it live requires your own model endpoint; you can also use replay mode to rerun the exact SQL queries against pre-recorded decisions without making external API calls.