Building TileGenie : a Databricks App Where Genie Is the Entire Backend

[ashukasma]

The question no dashboard answers

“Why was production reduced yesterday?”

— every plant head, every morning

Four systems hold the answer and none of them are speaking. This is how we built a factory-floor intelligence app where every single insight — including the ones that look like a dashboard — is a natural-language question answered live by Genie.

1. THE PROBLEM

A mid-size tile manufacturer runs on four disconnected systems:

• a factory MES/IoT stack holding machine status, sensor readings and daily output;
• a WMS holding warehouse stock and inter-site transfers;
• an ERP holding orders, invoices and revenue;
• a CRM holding customer interactions and trade-show leads.

Every one of them already has a dashboard.

None of them answer the question leadership actually asks: "Why was production reduced yesterday?"

That is not a metric lookup. Answering it means joining daily production to the machine status timeline, filtering for Breakdown / Scheduled Cooldown / Idle, pulling the reason codes, and tying the shortfall back to specific machines. It is a four-table join that nobody pre-built, because there are a hundred variants of it and each one gets asked exactly once.

So the loop today is: leadership asks -> an analyst writes SQL -> the answer lands hours or a day later -> by then the shift is over.

The bottleneck was never the data. It is the translation layer between a business question and a SQL query.

The opportunity: "why"-shaped questions cannot be pre-built into a BI tool, but they are precisely what a text-to-SQL agent is good at. Describe the semantic layer well enough and the analyst stops being a query queue.

2. WHO IT IS FOR

Primary: Plant leadership (CEO, COO, Plant Head). Writes zero SQL, needs the answer inside the shift rather than the next morning, and asks "why" far more often than "what".

Secondary

Explicitly not the user: the data analyst. TileGenie exists so they stop answering the same four questions every week and get their time back for actual analysis.

That audience has one direct design consequence: no SQL box, no filter panel, no "select your dimension" dropdowns. A text field, and answers in plain sentences.

3. ARCHITECTURE AND DATA FLOW

The stack, top to bottom:

- Web frontend, hosted on Databricks Apps: executive cards (three Genie answers) and a multi-turn chat panel.
- App logic in a single app.py: ask_genie() to submit and poll one question, a ThreadPoolExecutor to run the three boot questions concurrently, conversation state, and a cached SDK client.
- Databricks SDK, calling the Genie Conversations API: start_conversation, create_message, get_message.
- Genie Space "TileGenie Production Intelligence": instructions, 10 sample questions, 4 verified answers. Natural language to SQL, executed on a serverless SQL warehouse.
- Unity Catalog: tile_production_demo.gold, 16 Delta tables (7 dimensions, 9 facts) with 180 days of history.

Total application code is one app.py. No backend service, no ORM, no caching tier, no chart library wiring. Genie is the backend.

A note on the top box: we used Streamlit for the frontend because it ships inside Databricks Apps with no extra plumbing. Nothing in this architecture depends on that choice. It is a UI convenience, not part of the Databricks solution, and any frontend that can call the Databricks SDK works identically.

3.2 BOOT (cold start, about 30 seconds)

1. Databricks Apps starts the app as its own service principal. app.yaml declares the Genie Space under resources: and injects GENIE_SPACE_ID as an env var — no ID hardcoded in Python, no credentials anywhere in the repo.
2. WorkspaceClient() authenticates with the service principal’s OAuth token. It is cached at process level, so one client is shared across every session rather than rebuilt per request.
3. Three executive questions are submitted in parallel through a ThreadPoolExecutor: yesterday’s total production, machines currently down, total inventory across warehouses.
4. Each thread calls genie.start_conversation(...), then polls genie.get_message(...) every 2 seconds — 60 attempts, a two-minute ceiling — until the status reads COMPLETED or FAILED.
5. Genie translates each question to SQL using the space instructions, runs it on the serverless SQL warehouse, and returns attachments. The app walks all attachments and pulls two things: attachment.text.content (the prose answer) and attachment.query.query (the generated SQL).
6. Results land in the session’s state, and the first successful conversation_id is promoted to the session’s shared conversation.

Why parallel matters: the three questions are latency-bound at roughly 30 seconds each. Run serially that is a 90-second boot; run concurrently it is 30. A progress bar reporting "Answered N of 3" as futures resolve makes the wait read as work happening rather than a hung page.

3.3 A CHAT TURN

1. The user types a question, or clicks one of four suggested prompts. Both paths go through the same handler — the suggestions are not shortcuts to canned reports.
2. ask_genie() is called with the existing conversation_id, so it uses create_message rather than start_conversation. Genie keeps the context, and follow-ups like “now break that down by factory” work.
3. The answer and its SQL are appended to the message list and rendered as a chat bubble, with a small </> icon that opens the exact query Genie wrote.

3.4 THE DATA MODEL

The data model, 7 dimensions: dim_factory (3 plants), dim_warehouse (5 sites), dim_machine (13 to 18 machines, each carrying max_continuous_run_hours and mandatory_cooldown_hours), dim_product (12 tile SKUs), dim_customer (50 accounts), dim_sales_rep (8 reps), and dim_event (6 trade shows).

And 9 facts: fact_machine_status_log, fact_machine_sensor_reading, fact_production_daily, production_forecast, fact_inventory_snapshot, fact_stock_transfer, fact_crm_interaction, fact_orders, and fact_event_attendance.

One business rule is deliberately baked into the generated data: machines have mandatory cooldown cycles. Status logs run Running to Scheduled Cooldown to Running, interrupted by occasional Breakdown and Idle states carrying real reason codes. That is what lets Genie answer "why was production reduced" from the database instead of guessing at a correlation.

4. WHAT USERS CAN ASK THE GENIE AGENT

• Production & downtime
  Why was production reduced yesterday?
  Why was production reduced on 2026-08-20 at Factory 1?
  Show me production trends for the last 7 days
  Which machines had the most downtime this week?
  What is the defect rate trend across our factories?
  What is the average machine efficiency by factory?
• Forecasting & planning
  What is the expected production of Porcelain-Glossy-24x24 for next quarter?
  What should our production plan be for Q4 2026?
• Inventory
  How much stock do we have in each warehouse right now?
  Which products have the lowest stock levels?
  Which items are below their reorder point?
• Sales & CRM
  How many orders came from the Mumbai Ceramics Expo?
  Which customers placed the largest orders this quarter?
  Which sales reps had the most customer interactions?

Follow-ups work in the same conversation ("break that down by product", "just Factory 2", "compare to last month") because every message after the first reuses the session's conversation_id.

Four of these are configured as verified answers, the ones the demo depends on: production reduction analysis, the single-product forecast, the full quarterly plan, and warehouse stock.

5. HOW GENIE POWERS THE MAIN EXPERIENCE

The gut check is: if you removed Genie, would the main experience break?

Yes, completely. There is nothing left. Not a degraded app, an empty one.

There are no static dashboards, not one hardcoded chart and not one pre-written query. app.py contains zero SQL. Even the three KPI cards are Genie answers: they look like a dashboard, but each card is a natural-language question answered live at boot, and without Genie they are empty boxes. The suggested-question buttons are not canned reports either, they simply prefill the chat box with a question string, taking the same path and the same API call as typing it yourself. The only data path in the entire app is w.genie.*, with no SQL connector, no warehouse client, and no cached result set.

Genie is not a feature bolted onto a BI app. It is the query engine, the semantic layer, and the presentation logic. The frontend is a thin shell around it: authenticate, ask, poll, render.

We also deliberately show Genie's work. Every answer, dashboard card or chat reply, carries an icon that opens the exact SQL Genie generated. That is there for trust: a plant head who cannot write SQL can still forward the query to an analyst and ask "is this right?" Making the reasoning inspectable is what moves an AI answer from a cute demo to something someone will act on.

6. CONFIGURING THE GENIE AGENT

This is the part that actually determined answer quality, and where most of the project’s thinking went. A Genie Space has three configuration surfaces, and they do different jobs:

6.1 Instructions

The block we configured, with the data model abridged and the business rules verbatim:

You are TileGenie, an AI assistant for a tile manufacturing company. You help
leadership answer operational questions about production, inventory, sales, and
machine performance.

## Data Model
You have access to 16 Delta tables in catalog `tile_production_demo`, schema `gold`:

**Dimensions:**
- dim_machine: Machines with max_continuous_run_hours and mandatory_cooldown_hours
- dim_product: Tile products with pricing, production time
... (dim_warehouse, dim_factory, dim_customer, dim_event, dim_sales_rep)

**IoT/Machine Facts:**
- fact_machine_status_log: Machine status over time (Running, Scheduled Cooldown,
  Breakdown, Idle) with reason codes
- fact_machine_sensor_reading: Hourly sensor data (temperature, vibration, power)

**Production Facts:**
- fact_production_daily: Daily production by factory and product
  (planned vs actual vs defects vs downtime)
- production_forecast: Pre-computed quarterly forecasts with confidence bounds

... (inventory facts, CRM/sales facts)

## Key Business Rules

### Production Reduction Analysis
When asked "Why was production reduced on [date/factory]?", you must:
1. Join fact_production_daily to fact_machine_status_log on machine_id and date
2. Look for machines in "Breakdown" or "Scheduled Cooldown" or "Idle" status
3. Sum downtime_minutes from fact_production_daily
4. Join to dim_machine to get machine names and cooldown rules
5. Provide specific reasons from the status log (e.g., "Bearing failure",
   "Mandatory maintenance cycle", "No production orders")



### Production Forecasting
When asked about "expected production" or "production plan":
- Query the production_forecast table which has pre-computed quarterly forecasts
- 
- The forecast includes forecast_units, lower_bound, upper_bound, confidence_level

### Inventory Queries
When asked "How much stock do we have in each warehouse?":
- Query fact_inventory_snapshot with the LATEST snapshot_date
- Use: WHERE snapshot_date = (SELECT MAX(snapshot_date) FROM fact_inventory_snapshot)
- Show stock_status (Low/Adequate) and compare to reorder_point

### Machine Cooldown Cycles
All machines have max_continuous_run_hours and mandatory_cooldown_hours.
Machines cycle: Running -> Scheduled Cooldown -> Running
Occasional Breakdowns and Idle periods interrupt the cycle.

## Answer Style
- Be concise and executive-friendly
- Lead with the answer, then supporting data
- Use specific numbers and dates
- When showing production drops, ALWAYS cite the actual machine status reasons
- Format large numbers with commas (e.g., 1,234,567)

Three things in there matter more than the rest.

• The negative instructions. "NEVER guess or infer reasons" and "DO NOT attempt to compute forecasts yourself" fixed more wrong answers than any positive instruction did. Left to itself, Genie will happily correlate a production drop against whatever numeric column is nearby and present it as a cause. Telling it where the ground truth lives, and that it is not allowed to reason around it, is what makes the answer trustworthy.
• The join recipe, written as steps. The production-reduction rule is effectively pseudocode. It does not describe the tables, it describes the procedure. That converted an unreliable question into a reliable one without a verified answer being involved at all.
• The answer style block. Executives do not want a result set, they want a sentence. "Lead with the answer, then supporting data" is the difference between an app that gets used and one that gets demoed once.

6.2 Sample questions, the ten we configured

1. Why was production reduced yesterday?
2. What is the expected production of Porcelain-Glossy-24x24 for next quarter?
3. What should our production plan be for Q3 2026?
4. How much stock do we have in each warehouse right now?
5. Which machines had the most downtime this month?
6. What is the defect rate trend across our factories?
7. Which products have low stock levels?
8. How many orders came from the Mumbai Ceramics Expo?
9. What is the average machine efficiency by factory?
10. Which customers placed the largest orders this quarter?

Note the deliberate spread: four are the demo-critical questions, and six reach into corners of the model (CRM, events, defect rates) to prove the space is not a four-query trick.

6.3 Verified answers

Four questions carry the demo, so we pinned exact SQL for each rather than hoping. Here is the hardest one, the production-reduction join:

SELECT
pd.production_date, pd.factory_id, f.factory_name,
pd.product_id, p.product_name,
pd.planned_units, pd.actual_units,
pd.planned_units - pd.actual_units AS shortfall,
pd.downtime_minutes, pd.efficiency_percent,
ms.status AS machine_status,
ms.reason AS downtime_reason,
m.machine_name, m.machine_type
FROM tile_production_demo.gold.fact_production_daily pd
INNER JOIN tile_production_demo.gold.dim_factory f
ON pd.factory_id = f.factory_id
INNER JOIN tile_production_demo.gold.dim_product p
ON pd.product_id = p.product_id
LEFT JOIN tile_production_demo.gold.dim_machine m
ON m.factory_id = pd.factory_id
LEFT JOIN tile_production_demo.gold.fact_machine_status_log ms
ON ms.machine_id = m.machine_id
AND DATE(ms.start_time) = pd.production_date
AND ms.status IN ('Breakdown', 'Scheduled Cooldown', 'Idle')
WHERE pd.production_date = '2026-08-20'
AND pd.factory_id = 1
AND pd.actual_units < pd.planned_units
ORDER BY shortfall DESC

And the inventory one, which exists mainly to enforce latest-snapshot-only, the single most common way that question goes wrong:

WITH latest_snapshot AS (
SELECT MAX(snapshot_date) AS max_date
FROM tile_production_demo.gold.fact_inventory_snapshot
)
SELECT
w.warehouse_name, w.city, w.region,
p.product_name,
inv.stock_quantity, inv.reorder_point, inv.stock_status,
CASE WHEN inv.stock_quantity < inv.reorder_point
THEN 'REORDER NEEDED' ELSE 'Adequate' END AS action_needed
FROM tile_production_demo.gold.fact_inventory_snapshot inv
INNER JOIN tile_production_demo.gold.dim_warehouse w
ON inv.warehouse_id = w.warehouse_id
INNER JOIN tile_production_demo.gold.dim_product p
ON inv.product_id = p.product_id
CROSS JOIN latest_snapshot ls
WHERE inv.snapshot_date = ls.max_date
ORDER BY w.warehouse_name, inv.stock_status, inv.stock_quantity

The other two pin the single-product forecast lookup and the full quarterly plan sorted by projected revenue.

We also wrote an expected answer structure for each. Not just the SQL, but the shape of the sentence Genie should produce from it:

"Production was reduced at Factory 1 on 2026-08-20 due to:
- [Product Name]: [X] units short of plan ([Y]% efficiency)
- Cause: [Machine Type] was in [Status] - [Reason]
- Total downtime: [Z] minutes"

That template is why the answer reads like a plant manager wrote it, rather than like a query result got narrated.

6.4 The setup loop we actually used

1. Create the space and add all 16 tables from tile_production_demo.gold.
2. Paste the instructions, then ask the four demo questions in the Genie UI, not through the app.
3. Where the SQL was wrong, fix the instructions first. A rule fixes a whole class of questions; a verified answer fixes exactly one.
4. Only once a question was still unreliable did we pin it as a verified answer.
5. Re-test all four, plus the six stretch questions, to confirm nothing regressed.
6. Note the Space ID for app.yaml.

Step 3 is the one to internalize. It is tempting to pin every question as a verified answer and call it done, but a space built that way answers ten questions and fails the eleventh. Instructions generalize; verified answers do not.

7. WHAT WE LEARNED

Deployment. Workspace source beat Git source for Databricks Apps reliability while iterating. Keep app.yaml minimal, because our early failures were mostly YAML and routing rather than code, and starting from the working hello-world pattern and adding one thing at a time was faster than debugging a full config. Declare the Genie Space under resources:, which is what makes the space reachable and hands you an environment variable instead of a hardcoded ID.

Genie SDK. Use response.message_id, not response.id, because GenieMessage has no .id and the AttributeError does not tell you that. Iterate every attachment, not just the first: our early builds returned "No response" constantly because the text sat in attachment 2 while we read attachment 1, and text and SQL frequently arrive on different attachments. Always poll with a ceiling, 60 attempts at 2 seconds and then a clean timeout message, because Genie has no callback and an unbounded poll is a hung app. Cache the WorkspaceClient and build it once per process.

Permissions. The app runs as a service principal, not as you, and it inherits none of your grants. We lost real time to PERMISSION_DENIED before granting USE CATALOG on the catalog, USE SCHEMA on the schema, and SELECT on all 16 tables to the app's service principal. Verify with SHOW GRANTS before assuming the app is broken.

Performance. Roughly 30 seconds per Genie query is normal, covering natural-language parsing, SQL generation and warehouse execution, and it is not a bug to optimize away. So parallelize instead: three independent questions through a ThreadPoolExecutor cost the same wall-clock as one, and that single change took boot from about 90 seconds to about 30, making it the highest-leverage line in the app. Show progress honestly, because a bar counting "Answered 2 of 3" makes 30 seconds feel like work happening while a blank screen makes it feel broken.

Genie Space design, which is the part that actually determines answer quality. The instructions matter more than the app code, and everything hard about this project lived in the space configuration rather than in app.py. Tell Genie what not to do, because negative instructions fixed more wrong answers than positive ones did. Pre-compute anything statistical, since Genie is excellent at querying and poor at modeling, which is why we stored quarterly forecasts with confidence bounds as a table so that "what is next quarter's output?" is a lookup rather than a regression. Verified answers are the reliability lever for the questions that carry the demo. And test in the Genie UI first, always, because debugging a bad answer through the app adds a slow, noisy layer.

8. THE MEDALLION DESIGN, AND WHY ONLY GOLD IS MATERIALIZED

To be precise about what this project does and does not ship: we are not showing a three-layer medallion architecture. We designed the layering, and we materialize only the gold layer. Both the design and the reason follow.

The layering logic. Bronze is raw, as landed, with one table per source system rather than per business entity; its shape mirrors whatever the CSV export, Excel drop or IoT feed actually looks like, plus ingestion metadata, and it is what an ingestion job writes to. Silver is cleaned, deduplicated, typed and conformed to one row per natural key, for example one customer per mobile number; it matches gold's grain but is not yet business-aggregated. Gold is the 16 tables we already have: business-ready, and the only layer Genie ever queries.

Bronze tables, per source and in raw shape:

- bronze.warehouse_stock_raw mirrors warehouse CSV and Excel drops, with warehouse_name, product_sku, stock_qty, snapshot_date, source_file, ingested_at.
- bronze.factory_production_raw mirrors factory CSV drops, with factory_name, machine_name, product_sku, date, units_produced, downtime_notes, source_file, ingested_at.
- bronze.iot_machine_events_raw mirrors the IoT/PLC export or stream, with machine_id, event_type, event_ts, sensor_payload_json, ingested_at.
- bronze.crm_export_raw mirrors the CRM export, with mobile_number, customer_name_raw, product_interest_raw, interaction_date, event_name_raw, ingested_at.
- bronze.erp_orders_raw mirrors the ERP order export, with order_number, customer_mobile, sku, qty, price, order_date, warehouse_code, ingested_at.
- bronze.event_registration_raw mirrors event attendee lists, with event_name_raw, mobile_number, registered_at, ingested_at.

Nothing here is deduplicated or standardized. The same customer might appear with three spelling variants of their business name across the CRM and ERP exports. That is expected at this layer: bronze's job is to land what arrived, faithfully, so you can always reprocess from source.

Silver tables, conformed, deduplicated and typed, matching gold's grain before business rules are applied:

- silver.warehouse standardizes names and IDs across sources.
- silver.factory standardizes names and IDs.
- silver.machine deduplicates machine_id and types the capacity fields.
- silver.product normalizes SKUs to one row per SKU.
- silver.customer deduplicates by mobile_number, the natural key, collapsing name variants into one canonical record.
- silver.event gives canonical event names and dates.
- silver.machine_status_event types and standardizes the status enum parsed out of raw IoT payloads.
- silver.production_record is typed and joined to conformed factory, machine and product IDs.
- silver.inventory_snapshot is typed and joined to conformed warehouse and product IDs.
- silver.crm_interaction is joined to a conformed customer_id and event_id.
- silver.order is joined to conformed customer_id, product_id and warehouse_id.

silver.customer is where the interesting work happens. Mobile number is the unique key, so the three spelling variants of one customer that bronze faithfully preserved collapse into a single canonical record here, and every downstream fact then joins to one customer_id instead of guessing at name matches.

Why only gold is materialized. Databricks Free Edition caps you at roughly 30 tables, and the full design above adds up to 6 bronze plus 11 silver plus 16 gold, which is 33 tables against a ceiling of about 30. Fitting the whole pipeline inside the cap would have meant cutting gold down to roughly 10 tables, which deletes exactly the joins the interesting questions depend on: machine status logs, sensor readings and event attendance.

Given the choice between a complete pipeline over a thin model and a thin pipeline over a complete model, we took the second. The point of this app is what Genie can do with a rich, well-described semantic layer, and that lives entirely in gold. Genie never reads bronze or silver in a real deployment either.

Where the ETL logic went instead. The transformations are not skipped, they are enforced at generation time rather than as a materialized pipeline stage. The generator writes analytics-ready Delta tables in one pass, standing in for bronze ingestion. Referential integrity across all keys is guaranteed at generation, standing in for silver conformance. One row per natural key exists by construction, with customers keyed on mobile number, standing in for silver deduplication. Status enums, timestamps and numeric fields are typed on write, standing in for silver typing. And the gold business rules, the machine cooldown state machine along with plausible defect and efficiency distributions, are baked in directly.

In a production deployment, nothing about the app changes. Point the Genie Space at your real gold schema and TileGenie works identically, because it only ever queries gold. Adding bronze and silver underneath is a drop-in, and the app and the Genie Space are unaffected, because their entire contract is 16 well-described gold tables.

9. COMMANDS

One-time CLI setup:

brew tap databricks/tap && brew install databricks
databricks --version
databricks auth login --host https://YOUR-WORKSPACE.cloud.databricks.com
databricks current-user me

Generate the data. The generator is a PySpark script, so it runs inside the workspace rather than on your laptop:

databricks workspace import scripts/generate_tile_production_data.py /Workspace/Users/YOU/TileGenie/gen_data.py --language PYTHON --overwrite

Open it as a notebook and run it, then verify:

USE CATALOG tile_production_demo;
SHOW TABLES IN gold;
SELECT COUNT(*) FROM gold.fact_production_daily;
SELECT MAX(snapshot_date) FROM gold.fact_inventory_snapshot;

Create the app and find its service principal:

databricks apps create tile-genie
databricks apps get tile-genie --output JSON | jq -r '.service_principal_name'

Grant Unity Catalog access to that service principal:

GRANT USE CATALOG ON CATALOG tile_production_demo TO `app-xxxx tile-genie`;
GRANT USE SCHEMA ON SCHEMA tile_production_demo.gold TO `app-xxxx tile-genie`;
GRANT SELECT ON TABLE tile_production_demo.gold.fact_production_daily TO `app-xxxx tile-genie`;
-- repeat SELECT for each of the 16 tables
SHOW GRANTS ON TABLE tile_production_demo.gold.fact_production_daily;

Run locally first:

databricks apps run-local .

Or start the app yourself, supplying the environment variables. Note that this authenticates as you rather than as the service principal, so it can succeed locally and still fail once deployed; confirm the grants separately.

export GENIE_SPACE_ID=your-space-id
export DATABRICKS_HOST=https://YOUR-WORKSPACE.cloud.databricks.com
pip install -r requirements.txt

Sync and deploy. The deploy command is the same for a first release and every redeploy:

databricks sync . /Workspace/Users/YOU/TileGenie --watch
databricks apps deploy tile-genie --source-code-path /Workspace/Users/YOU/TileGenie

Debug a running app:

databricks apps get tile-genie --output JSON
databricks apps logs tile-genie
databricks apps list

Common symptoms and the first thing to check. If the app will not start, read the logs, because it is usually app.yaml syntax or a missing dependency. If you see PERMISSION_DENIED, revisit the grants, remembering that the service principal and not you needs them. If the space ID is reported as not configured, check the resources: block in app.yaml and that the ID matches the env value. If answers come back empty, you are probably reading only attachments[0] instead of iterating all of them. If you hit an AttributeError on .id, use response.message_id. And if every query times out, test the same question in the Genie UI, because the problem is the space rather than the app.

CONFIGURATION FILES

app.yaml, the whole file. The command line is simply however your frontend starts, and everything below it is the part that matters:

command: ["streamlit", "run", "app.py"]

env:
- name: GENIE_SPACE_ID
value: "your-genie-space-id"

resources:
- name: tilegenie_genie
genie_space:
genie_space_id: "your-genie-space-id"

requirements.txt, where only the first line is required by the architecture and the rest are our frontend's:

databricks-sdk>=0.35.0
streamlit~=1.38.0
pandas>=2.0.0
plotly>=5.18.0

One rule that applies to any frontend: the server must bind to 0.0.0.0 on the port Databricks Apps expects, or the platform cannot route to it.

CLOSING

The whole thing is one Python file, one YAML file, one generator script, and a well-written Genie Space. The Genie Space is where the engineering effort actually goes, and that is the real finding.