Databricks Community

ravikr1 · a week ago

1. The problem and the opportunity

Retailers run hundreds of trade promotions every month across many stores and SKUs. Discounts, buy one get one offers, and bundle deals are expensive levers, and most teams still evaluate them in silos. A category manager asking a simple business question usually waits days for an analyst to write SQL, reconcile numbers across spreadsheets, and produce a report that is already stale by the time it arrives.

Three blind spots make this worse:

Cross SKU cannibalization. A promotion on one product often steals demand from its substitutes, so headline lift overstates the real gain.
Localized competitor pricing. Named competitors such as DMart, Blinkit, and Zepto move prices daily, and a promotion that looks good in isolation may simply be matching a competitor who already discounted.
Quick commerce stockouts and external signals. Weather, social sentiment, and out of stock events on quick commerce platforms shift demand in ways that a promo report never captures.

TPO Genie turns this from a slow, analyst gated workflow into a conversation. A category manager types a question in plain English and gets a data backed answer inside the app in seconds, with the exact SQL and the underlying rows shown, never invented.

The synthetic scenario models a retailer we call Company X, an Indian grocery and general retail chain, benchmarked against seven real world competitors: Walmart, DMart, Reliance Retail, Amazon, Flipkart, Blinkit, and Zepto.

2. Target audience

The app is designed around three personas:

Category Manager. Plans promotions and wants lift predictions and cannibalization risk before committing budget.
Trade Marketing Analyst. Wants a holistic post promotion analysis that attributes results to promo mechanics, competitor moves, and external factors.
Store Operations. Wants a quick view of what is working per store and per region

3. Genie at the core

The contest asks one central question: if Genie were removed, would the main experience change significantly? For TPO Genie the answer is yes. Genie is the primary interface and the reasoning engine, not a side feature.

The main screen, Ask Genie, is a conversational assistant. Every answer is generated by the Databricks Genie Agent and grounded in audited Unity Catalog views. The app shows three things for each answer:

The natural language response.
The exact SQL Genie generated.
The actual rows returned from the warehouse.

If Genie is removed, there is no answer engine, no natural language to SQL, and no grounded response. The dashboards would remain as static charts, but the product itself, the thing that takes a manager from a question to a certified answer, would be gone.

To make Genie do more than a single query, the assistant exposes three modes:

Standard. Natural language to SQL, grounded in the Gold views. Powered by the Genie Conversation API.
Reasoning. Before answering, the assistant plans which views or functions it will use and shows that plan as a numbered trace, then answers from the grounded result. Powered by the Mosaic AI Foundation Model API.
Deep Research. Decomposes a question into sub queries, runs several grounded lookups (historical performance, external drivers, graph cannibalization, and a forward looking simulation), then synthesizes a cited brief. Powered by the Mosaic AI Agent Framework pattern orchestrating Genie, the prediction function, and the knowledge graph.

4. Genie Agent capabilities

The Genie Space is configured for enterprise grade grounding:

Data assets. The Space points only at the Gold semantic views (the mv_* views) and one table valued function. It never sees raw transaction tables, so it cannot wander into ungoverned data.
Instructions. A custom instruction block tells Genie the domain rules: all money is in INR, margin is always post discount and post COGS, lift means incremental units versus a counterfactual baseline, and cannibalization questions must use the graph derived views.
Curated example queries. A set of few shot examples teaches Genie the correct join patterns and which view answers which intent (historical post mortems, competitor comparisons, and forward looking simulations).
Column level annotations. Every column in every Gold view carries a comment, which is the semantic contract Genie reads so it resolves business terms correctly.
Tool calling. For forward looking what if questions, Genie calls a SQL function, predict_promo_impact, inline. Because Genie only accepts table valued functions as tools, the function returns a one row table with predicted lift, cannibalization risk, and net margin delta.

The routing behaves like this:

User intent Genie action

How did promo X perform?	Queries mv_promo_performance and mv_margin_performance
Did it cannibalize nearby products?	Uses mv_cannibalization and mv_substitute_network
How do we price versus DMart or Blinkit?	Uses mv_competitor_landscape
Did rain or competitors or a stockout hurt sales?	Uses mv_external_factors
What if I run 15 percent off on SKU X?	Calls predict_promo_impact(...)

5. Application architecture and data flow

TPO Genie is a full stack Databricks App. The data layer, the semantic layer, the model, the Genie Space, and the web app all run inside one Free Edition workspace.

Knowledge graph and ontology

Instead of a GPU trained graph neural network, which Free Edition cannot serve, we built a governed property graph that delivers the same relational intelligence and reinforces Genie grounding:

tpo_graph.ontology, a table that documents every node type and edge type in plain language.
tpo_graph.kg_nodes, nodes for SKU, Store, Promo, Category, Brand, and Competitor.
tpo_graph.kg_edges, typed and weighted edges: substitutes, sold_at, priced_against, co_promoted, similar_to, belongs_to, and made_by.

Two Gold views sit on top of the graph. mv_substitute_network answers what products compete with a given SKU, and mv_cannibalization answers whether a promotion stole sales from its substitutes during the promo window. These are the views Genie uses for cannibalization questions.

Prediction function

predict_promo_impact(sku_id, store_id, mechanic, discount_pct) is a Unity Catalog SQL table valued function. It returns predicted lift percentage, a cannibalization risk score, and net margin delta. The logic is grounded in each SKU's elasticity, the mechanic response, and the number of substitute edges in the graph. Because it is a native SQL function, Genie can call it inline and it runs on Free Edition with no model serving endpoint required.

To justify the design, we also trained a LightGBM regressor on the synthetic promo response labels and tracked it with MLflow. The model is the evidence that the elasticity and mechanic relationships are real and learnable, and the SQL function is the deployable inference path.

The web application

The app is a single deployable unit:

Backend: FastAPI. It exposes the Genie chat endpoint, the prediction endpoint, and the dashboard and review endpoints. All non Genie data endpoints run real SQL against the Gold views through the Databricks SDK statement execution API, with a graceful mock fallback so a cold warehouse or a transient error never breaks the demo.
Frontend: React with Vite, using TanStack React Query for data fetching, Recharts for visualizations, Framer Motion for animation, and Lucide for icons. The interface is a dark, modern analytics design with four screens.
Serving: FastAPI serves both the API and the built React single page app, and the whole thing is deployed as a Databricks App.

Demo Video: https://drive.google.com/file/d/121d-Al3YCj_l8doGeXAzEjwN84TrjKB7/view?usp=drivesdk

The four screens are:

Ask Genie, the conversational hero with the three modes, a live grounding panel that shows exactly which views back every answer, and a history rail.
Promo Planner, a what if sandbox where you pick a SKU, a mechanic, and a discount, then call predict_promo_impact and see predicted lift, cannibalization risk, and the top substitutes at risk.
Performance Dashboard, KPIs, a revenue and margin trend, category performance, a competitor price gap index, and an external signal overlay.
Promo Review, a per promotion retrospective with actual versus baseline, external factor attribution, and graph derived cannibalization.

6. Complete technology list

Every technology used in the build:

Databricks Free Edition as the entire runtime.
Unity Catalog for governance, the medallion schemas, views, functions, and lineage.
Delta Lake for all Bronze, Silver, and graph tables.
Databricks serverless SQL Warehouse for the Gold views and app queries.
Databricks Genie and the Genie Conversation API as the central agent.
Databricks Apps for hosting the full stack application.
Mosaic AI Foundation Model API (default endpoint databricks-meta-llama-3-3-70b-instruct) for the Reasoning mode.
Mosaic AI Agent Framework pattern for the Deep Research mode.
Unity Catalog SQL functions, specifically a table valued function for the prediction tool.
MLflow for tracking the LightGBM benchmark model.
LightGBM as the evidence model for promo lift.
Databricks SDK for Python for statement execution and the Genie and serving calls.
PySpark for the data load, including a custom schema inference helper.
FastAPI and Uvicorn for the backend.
React, Vite, TanStack React Query, Recharts, Framer Motion, and Lucide for the frontend.
Python synthetic data generators for a realistic Indian retail dataset with seasonality, elasticity, cannibalization, weather, competitor, social, and quick commerce dynamics.

7. Evaluation and grounding discipline

We built a 15 question ground truth evaluation set covering historical performance, cannibalization, competitor pricing, external factor attribution, and forward looking simulation. Each question is scored on whether the answer routes to the correct view or function and whether the numbers are consistent. The evaluation checks for the red flags that matter most: invented figures, the wrong margin definition, estimating a prediction by hand instead of calling the function, and ignoring named competitors or substitutes.

Groundedness is enforced structurally, not just by prompting. Genie can only see the Gold views and the function, every column is documented, and the app displays the generated SQL and the returned rows next to every answer, so a reviewer can verify each number.

8. Learnings from building and testing

Building on Free Edition forced good engineering decisions and produced several concrete lessons.

Match the model to the platform. The original design called for a GPU trained GraphSAGE graph neural network. Free Edition has no GPU serving, and a heavy model would have earned little against a contest that scores Genie centrality. Replacing it with a governed knowledge graph plus a CPU LightGBM benchmark gave the same relational story, a stronger Genie grounding narrative, and a design that actually deploys.
Loading 10 million rows on a serverless driver needs care. Converting a 10 million row Python list to a Spark DataFrame in one call overflows the driver. We fixed this with chunked writes, an explicit schema for the large table, and a resumable loader that skips tables already populated.
Schema inference will fight you. Synthetic generators emit an integer in one row and a float in another for the same column, for example a discount of 0 versus 0.15. Spark refuses to merge Long and Double. We wrote a small schema inference helper that scans each column and widens the type once, so Spark never has to merge conflicting types.
Genie tools must return a table. A scalar function that returns a struct is rejected by the Add SQL Functions dialog. Rewriting predict_promo_impact to a table valued function that returns one row made it selectable as a Genie tool.
Mock to real wiring is a real task. The prototype looked complete but the chat never called Genie and the dashboards read from a local mock file. Productionizing meant wiring the frontend to real endpoints, adding a SQL execution helper, and gating every screen behind a real query with a mock fallback, so the app degrades gracefully instead of failing.
Honesty in fallback. Foundation Model and Agent serving can be limited on Free Edition. The Reasoning and Deep Research modes run with a realistic canned trace when serving is unavailable, and the real serving call is already wired behind a flag, so the demo never breaks and the production path is clear.

9. Future work

Point the Reasoning and Deep Research modes at live Foundation Model and Agent serving endpoints in a workspace that has them enabled.
Train the graph neural network in a trial GPU workspace and register it behind the same predict_promo_impact function slot, keeping the app unchanged.
Add Vector Search over promotional playbooks so Genie can ground qualitative recommendations as well as numeric ones.

TPO Genie shows that a governed semantic layer plus Genie can turn trade promotion analysis from a slow analyst workflow into a fast, trustworthy conversation, entirely on Databricks Free Edition.