ecommerce-oltp
This server exposes MCP tools for fraud-case evidence, Genie fraud-agent listings, OLTP data generation, star-schema ETL, and constrained dataset queries.
Fraud cases and agents –
list_fraud_casesreturns the 15 named fraud evidence packs;list_fraud_agentslists the 10 Genie fraud specialists;run_fraud_caseruns one case (01–15) returning at most 50 evidence rows;run_fraud_agent_casesruns all cases owned by one specialist.Historical OLTP generation –
generate_historical_oltpwrites customers, addresses, orders, lines, shipments, andentity_linkrows, with configurable customer count, years, and orders per year, optionally as a Databricks job.Realtime/CDC order generation –
generate_realtime_ordersappends 100–10,000 new OLTP orders for a selected year window, optionally as a job.Star-schema ETL –
etl_star_historicalrebuilds dims andfact_salesfrom OLTP (overwrite);etl_star_cdcapplies the Delta change feed fromcustomer_orderintofact_sales(append).Controlled data access –
query_datasetruns a single SELECT against dims, facts, or OLTP with a forcedLIMIT 50, preventing full-table dumps.
Provides tools for triggering Databricks-based Ecommerce Genie Ontology workflows, including OLTP historical/realtime data generation, CDC ETL into the star schema, and fraud evidence pack runs.
Click on "Deploy Server".
Wait a few minutes for the server to deploy. Once ready, it will show a "Started" state.
In the chat, type
@followed by the MCP server name and your instructions, e.g., "@ecommerce-oltpGenerate 5,000 realtime OLTP orders"
That's it! The server will respond to your query, and you can continue using it as needed.
Here is a step-by-step guide with screenshots.
Databricks Genie Ontology
Governed customer sales and funds-movement data: Unity Catalog, OLTP, star schema, and fraud-ready MCP. Portal teams call LangGraph, Google ADK, or AWS Strands through FastAPI. Provision, load, and tear down with GitHub Actions — no notebook import.
All data management is currently managed in Databricks and Databricks Genie and its AI Agents.
Table of Contents
Related MCP server: MCP Restaurant Ordering API Server
Running Agentic Fraud Analytics - Start To Finish
Use Actions → Run workflow. Time is the last measured GitHub Actions job duration (19 Sep 2026 Step 100 unless noted). Look at the GitHub run first, then the Databricks account and workspace pages.
Template URLs only — replace the {placeholders}:
Account workspace:
https://accounts.cloud.databricks.com/workspaces/{WORKSPACE_ID}?account_id={ACCOUNT_ID}Workspace home:
https://{WORKSPACE_HOST}Catalog:
https://{WORKSPACE_HOST}/explore/data/{CATALOG}Genie:
https://{WORKSPACE_HOST}/genieGenie MCP (one space):
https://{WORKSPACE_HOST}/api/2.0/mcp/genie/{SPACE_ID}Apps:
https://{WORKSPACE_HOST}/appsCustom MCP App:
https://{WORKSPACE_HOST}/apps/mcp-ecommerce-oltpDiscover:
https://{WORKSPACE_HOST}/search/discoverThis run:
https://github.com/{OWNER}/{REPO}/actions/runs/{RUN_ID}
Fresh start: run 01 - Setup - Step 100 - Create All Together. It chains 01 → 02 → 03 → 04 → 06 → 07. Atomic steps stay 01–10 so we can insert 11, 12, … in the middle later; 100+ stays the composite.
S. No | GitHub workflow name | Time | What to look at | Comments |
0 | 01 - Setup - Step 100 - Create All Together | 16 min | Same checks as Steps 01, 02, 03, 04, 06, 07 | Measured 15 min 51 s (19 Sep 2026, run 35472667395). Wall clock 01+02+03+04+06+07. One Run workflow. Skips Step 05 and Step 08. |
1 | 01 - Setup - Step 01 - Create Databricks stack | 5 min | GitHub job green; workspace RUNNING; SQL warehouse up; catalog empty tables | Measured 4 min 53 s (same Step 100). Workspace 42 s, warehouse 18 s, catalog 3 min 3 s, deploy jobs 42 s. Account page |
2 | 01 - Setup - Step 02 - Create all Genie agents | 1 min | 11 Genie spaces (Retail Analytics + 10 fraud specialists) | Measured 1 min 4 s. Genie |
3 | 01 - Setup - Step 03 - Publish ecommerce-oltp MCP App | 4 min | App mcp-ecommerce-oltp on Apps | Measured 3 min 30 s. |
4 | 01 - Setup - Step 04 - Publish Discover domains | 20 s | Discover cards for Sales, Customer, Supply Chain, Finance are Published | Measured 20 s. Domain API |
5 | 01 - Setup - Step 05 - Invoke Retail Analytics Genie | 3 min | Sample questions return SQL + a short answer | Measured 3 min 14 s (not in Step 100). Confirms Genie MCP on the retail space. Optional question input. |
6 | 01 - Setup - Step 06 - Populate next 100000 OLTP rows | 3 min |
| Measured 2 min 34 s for 100,000 rows on a warm cluster. Catalog |
7 | 01 - Setup - Step 07 - Populate next N months of dims and facts | 3 min |
| Measured 3 min 10 s for 3 months. Same catalog. Months 1–12 (default 3). Fraud reads |
8 | 01 - Setup - Step 08 - Pipeline next 100000 OLTP and next N months star | 6 min | Step 06 then Step 07 in one run | Last measured pieces: 2 min 34 s + 3 min 10 s. Use this instead of running 06 and 07 separately. |
9 | 02 - Fraud Agent - 01 - Fraud Velocity Agent | 3–10 min | Space Fraud Velocity Agent | Genie MCP for velocity bursts and split orders. |
10 | 02 - Fraud Agent - 02 - Fraud Address Link Agent | 3–10 min | Space Fraud Address Link Agent | Shared-address / duplicate-account hops. |
11 | 02 - Fraud Agent - 03 - Fraud Ship-to Bill-to Agent | 3–10 min | Space Fraud Ship-to Bill-to Agent | Ship-to ≠ bill-to. |
12 | 02 - Fraud Agent - 04 - Fraud Returns Agent | 3–10 min | Space Fraud Returns Agent | High / rapid returns. |
13 | 02 - Fraud Agent - 05 - Fraud First-Order Agent | 3–10 min | Space Fraud First-Order Agent | High-value first orders. |
14 | 02 - Fraud Agent - 06 - Fraud Address Surge Agent | 3–10 min | Space Fraud Address Surge Agent | New address + expedite / surge. |
15 | 02 - Fraud Agent - 07 - Fraud Promo Agent | 3–10 min | Space Fraud Promo Agent | Promo and discount abuse. |
16 | 02 - Fraud Agent - 08 - Fraud Inventory Agent | 3–10 min | Space Fraud Inventory Agent | Orders vs stock mismatch. |
17 | 02 - Fraud Agent - 09 - Fraud Cancel Agent | 3–10 min | Space Fraud Cancel Agent | Cancel / abort shipment. |
18 | 02 - Fraud Agent - 10 - Fraud Geo Agent | 3–10 min | Space Fraud Geo Agent | Default system prompt is the Case 15 notes. system_prompt overrides it. Five starter-question checkboxes ask those prompts. additional_prompt is comma-separated customer ids. |
19 | 01 - Setup - Step 09 - Destroy Databricks stack | 1 min | Workspace gone from account console; cleanup email | Measured 1 min 7 s (19 Sep 2026, run 35472183222). Type |
Historical generate / realtime CDC Actions are retired (z_retired_*). Use Step 100 for a full recreate, or Step 06 + 07 (or 08) for data only.
Screenshots
Catalog —
docs/images/catalog-explorer.pngCatalog
retail_oltpafter Step 01 — 13 source tables (analytics_log, customers, orders, postings, ingest). 2b. Catalogretail_starafter Step 01 — dims, facts, metric views.Genie Agents after Step 02 — Retail Analytics plus the ten fraud specialists.
Apps after Step 03 — mcp-ecommerce-oltp Active on
/apps-v2.




Business Context
Customer behavior
Customer behavior is how a person buys, pays, ships, and moves money over time: orders and lines, the addresses they use, channel, status, and — once funds movement is in scope — wires, cash, book transfers, CDs, brokerage, demand drafts, and cards. Most of that activity is legitimate. What matters is the baseline per customer: typical amount, velocity, counterparties, and whether a new address or a sudden outflow sits outside changing transaction behaviors.
Purpose
The purpose of this context is fraud detection: pick out abnormal behaviors and rare fraud cases in real time, while keeping false alarms down. Fixed thresholds and rule-based checks miss identity theft and automated attacks, and they raise false-positives when fraudulent transactions are extremely rare compared with legitimate ones. Amount, sudden loss of balance, and type of movement are the high-ranking signals; agents should use them for risk analysis without dumping ten years of rows into a model.
Fraud Detection behavior
Fraud judgment is the LLM fraud agent, not PySpark.
Each specialist has its own system prompt (amount vs baseline, sudden drain, transaction type, shared-address network, velocity, changing behavior, rare event, explain the why).
The agent writes one outcome per customer, not one verdict for a list.
A customer list is only for paging the work. Do not ask the model to judge thousands of IDs in one shot.
Outcome values:
fraud_foundornot_found.Persist on
analytics_log_customer:analytics_outcomeplusanalytics_log_info(JSON: the why — amount, velocity, address, type).Session table
analytics_log: requesting user, customer count, date range, requested datetime, analytics start/end, statusIn ProgressorCompleted.initiate_fraud_analytics(from_date, to_date)finds customer IDs on the server. It does not dump IDs into the LLM.That call creates
analytics_logand oneanalytics_log_customerrow per customer, with dim/fact counts for the same window.PySpark / SQL only prepare: customers in range, counts, optional evidence pack (named case, max 50 rows) as signals.
PySpark does not set
fraud_found. SparkHAVING/ dollar thresholds are a rule engine — they miss changing behavior and raise false positives.MCP never returns 300,000 postings or 15 million orders. Hydrate one customer at a time (max 50 evidence rows).
Agents stay customer-scoped. Loop sequential or parallel with a small concurrency cap.
Databricks Genie specialists run in the workspace on the same warehouse.
Portal agents (LangGraph, Google ADK, AWS Strands) call MCP through FastAPI — not raw tables.
Same customer and date explain both a velocity flag and a wire-outflow flag.
Sales fraud and funds-movement fraud share only
dim_customeranddim_date.Network hops use
entity_link(has_address,shared_address). No graph database for these cases.Generate / ETL / next-100k / next-N-months jobs load data. They do not classify fraud.
close_analyticssets statusCompletedwhen the agent says the run is done.
Customer Data Capacity Considered
Capacity is planned from 18 September 2016 through 18 September 2026 (ten years ending 18 September 2026). Agents never load this volume; they hydrate one customer at a time.
Item | Planned | Notes |
Customers | 100,000 | One person is one customer |
Accounts per customer | 4 | Checking, certificate of deposit, credit card, brokerage |
Transactions per customer per year | 30,000 | Sales lines and funds-movement postings in that year |
Years of history | 10 | End date 18 September 2026; start 18 September 2016 |
Transactions per customer | 300,000 | 30,000 × 10 years |
Postings in the warehouse | about 30 billion | 100,000 × 300,000 — design ceiling, not a generate job |
Create / Generate Historical never writes 30 billion rows. That load would run for days on a large warehouse and blow the 3-hour stack window. Default generate is 200 customers, 3 years, 500 postings per customer per year (about 300,000 postings) plus sales orders. Orders are also capped at 20 million rows per run.
OLTP and Star Schema Models
Sales and funds movement live in OLTP (customer, customer_address,
customer_account, customer_order, customer_order_line,
customer_order_shipment, customer_transaction, ingestion_tracker,
ingestion_log, analytics_log, analytics_log_customer) plus conformed dimensions
and facts (dim_account, dim_transaction_type, dim_counterparty,
fact_transaction with the sales stars). Design volume is the capacity above.
Agents are customer-scoped: initiate_fraud_analytics keeps IDs on the
server; the agent pages, hydrates one customer, and writes the outcome.
Databricks Genie agents run in the workspace. Non-Genie agents
(LangGraph, Google ADK, AWS Strands) come through the portal via FastAPI and
MCP. The LangGraph and Google ADK packages are conformance clients so the MCP
tools stay honest.
Business Data Architecture
DataWarehouse
A data warehouse turns ten years of customer activity into a place agents and people can ask the same question and get the same answer. Facts hold the numbers. Each fact row is one event (one sale line, one return, one money movement). Dimensions hold the who, when, what, and where. That is what makes amount, velocity, segment, and type comparable across 100,000 customers without each team rewriting joins on raw OLTP.
The Architecture behind MCP
The architecture behind MCP is what makes agentic AI fraud detection safe at this volume. Other teams build functional agents — amount vs baseline, sudden drain, type of movement, shared-address network, changing behavior, rare events. MCP does not scan 300,000 postings per customer. It answers those questions for one customer at a time from star facts (baselines, peers, type mix) or OLTP (the supporting orders and postings). Genie agents and portal agents call the same warehouse, so a velocity flag and a wire-outflow flag can be explained from the same customer and date.
Sales fraud and funds-movement fraud share dim_customer and dim_date only.
Do not hang wire transfers or card dues off customer_order / fact_sales.
Add a second fact table: one row per posting (customer_transaction /
fact_transaction) with account, type, counterparty, amount, and balance
before/after.


Fraud idea | Functional agent |
Amount + sudden origin-balance drain | Amount vs baseline — spend far above this customer's recent median; sudden drain |
Transaction type as a signal | Transaction-type — wire, cash, card, cancel, expedite, promo |
Graph / network features | Shared-address network — hops on |
Behavioral vs transactional vs network | Velocity, returns, geo, and first-order specialists |
Adaptive / changing fraud | Changing-behavior — realtime CDC windows, not a static snapshot |
Rare events + fewer false positives | Rare-event — peer baselines, not a blanket dollar threshold |
Feature importance / explainability | Explain the why — amount, velocity, address; not a black box |
Order-event star (current, fraud) — each row is one customer_order with hour-level order_ts, billing/shipping dim_address, and dim_region. Use this for velocity, ship-to≠bill-to, and Case 15 impossible geo (mv_order_event).
Sales star (STALE) — each row is one sales line (one product on one order). Kept for Retail Analytics merchandising history only. No hour, no shipping region. Do not use for fraud.

Returns star — each row is one return, tied to the same customer, date, and product as sales.

Inventory star (STALE) — monthly product/store snapshot. Kept so mv_inventory_health still answers stock questions. Not a customer-fraud grain. Case 11 is the only fraud agent that may still join it.

Funds-movement star — each row is one money movement. Customer and date match sales; account, type, and counterparty are new.

MCP contract (customer first)
initiate_fraud_analytics(from_date, to_date)— server finds IDs; writesanalytics_log+analytics_log_customerAgent pages customers; hydrates one customer (counts + max 50 evidence rows)
record_customer_outcome—fraud_found/not_found+ JSON whyclose_analytics— statusCompletedGenie in the workspace; LangGraph / Google ADK / AWS Strands via FastAPI → MCP, not raw tables
Dims (conformed)
Dim | Why |
| Home / billing / shipping geography for Case 15 |
| Ship-to vs bill-to and far-region ( |
| Type signal for funds-movement agents |
| Same vs other account; product (checking / CD / card / brokerage) |
| Other bank, other person, brokerage |
| Already present |
Do not add dim_wire, dim_cash, or dim_credit_card. Those are rows in
dim_transaction_type.
dim_transaction_type
type_code | class | direction | same_bank | same_account |
wire_transfer | transfer | out | no | no |
cash_deposit | cash | in | yes | yes |
cash_withdrawal | cash | out | yes | yes |
bank_transfer_other | transfer | out | no | no |
bank_transfer_same_acct | transfer | book | yes | yes |
intra_bank_same_accts | transfer | book | yes | no |
cd_withdraw | term | out | yes | no |
cd_deposit | term | in | yes | no |
brokerage_in | securities | in | no | no |
brokerage_out | securities | out | no | no |
demand_draft_request | instrument | out | yes | no |
demand_draft_issue | instrument | out | yes | no |
card_purchase | card | out | no | no |
card_payment | card | in | yes | no |
card_due | card | obligation | yes | no |
OLTP posting row: transaction_id, customer_id, account_id,
counterparty_id, type_code, txn_ts, amount, balance_before,
balance_after, status. Partition facts by date and cluster on
customer_id / account_id. Refresh fact_transaction incrementally; do not
overwrite 10 years on every run.
Each of api, workflows, and adapter_databricks has an ObjectsFactory
that looks up named components and caches singleton instances.
src/ecommerce_genie_ontology/
api/ # FastAPI + CliApp
objects_factory.py # ApiObjectsFactory
workflows/
objects_factory.py # WorkflowsObjectsFactory
orchestrator.py # WorkflowRunner
provision|create_agents|invoke_agents|cleanup/
workflow.py
workspace_facade/{interface,impl}.py
tasks/
adapter_databricks/
objects_factory.py # AdapterDatabricksObjectsFactory
daos/ # SDK / REST
facades/ # SqlFacade, GenieFacade, GovernanceFacade, JobsFacade
common/
interfaces/ # Protocols used for DI
dtos/ constants/ utils/Workflow | Job name | Tasks |
provision |
|
|
create_agents |
|
|
invoke_agents |
| one task per sample question |
cleanup |
|
|
generate_historical |
| PySpark OLTP history ( |
generate_realtime |
| Append 100–10,000 new OLTP orders for CDC |
etl_historical |
| Overwrite star-schema dims/facts from OLTP |
etl_cdc |
| Apply Delta change feed into |
generate_next_oltp |
| Append next 100,000 OLTP rows plus |
etl_next_months |
| Append |
Default generate is 200 customers, 3 addresses, 4 accounts, 25,000 orders per customer per year, 500 postings per customer per year, 3 years ending this month (about 15 million orders and 300,000 postings). That is per year, not per day. Dims/facts are rebuilt from those OLTP tables so they match. Do not set Generate to 100,000 × 30,000 × 10 — that is the design ceiling, not a GitHub Action.
Prerequisites
A Databricks workspace with Unity Catalog and a SQL warehouse
Permission to create a catalog/schema, metric views, and Genie agents
uv(this project does not use pip)
Setup
cp .env.example .envFill in at least:
DATABRICKS_HOST=https://your-workspace.cloud.databricks.com
DATABRICKS_TOKEN=dapiXXXXXXXXDATABRICKS_WAREHOUSE_ID is optional. Catalog provision looks up warehouse ecommerce-genie-ontology by name. Genie uses the same host/token unless you set GENIE_HOST / GENIE_TOKEN.
uv syncGitHub Actions (no local CLI)
Use Actions → Run workflow. Create starts a 3-hour timer; a later Create cancels the previous timer. Destroy can be run by hand any time before that. Pipeline workflows are also manual (workflow_dispatch only).
01 - Setup — 01–99 are atomic (rerun one, or insert a new number in the middle). 100+ is a composite.
01 - Setup - Step 01 - Create Databricks stack — workspace, SQL warehouse, catalog, deploy jobs
01 - Setup - Step 02 - Create all Genie agents — Retail Analytics + 10 fraud specialists
01 - Setup - Step 03 - Publish ecommerce-oltp MCP App — Databricks App
mcp-ecommerce-oltpfor LangGraph / ADK / Playground01 - Setup - Step 04 - Publish Discover domains — Sales, Customer, Supply Chain, Finance via
/api/discover/v1/domains01 - Setup - Step 05 - Invoke Retail Analytics Genie
01 - Setup - Step 06 - Populate next 100000 OLTP rows
01 - Setup - Step 07 - Populate next N months of dims and facts
01 - Setup - Step 08 - Pipeline next 100000 OLTP and next N months star
01 - Setup - Step 09 - Destroy Databricks stack
01 - Setup - Step 10 - Destroy Databricks stack in 3 hours
01 - Setup - Step 100 - Create All Together — 01 → 02 → 03 → 04 → 06 → 07. Fresh start after Destroy.
02 - Fraud Agent (each Action creates that specialist’s Genie space; Databricks hosts Genie MCP at /api/2.0/mcp/genie/{space_id})
02 - Fraud Agent - 01 - Fraud Velocity Agent
02 - Fraud Agent - 02 - Fraud Address Link Agent
02 - Fraud Agent - 03 - Fraud Ship-to Bill-to Agent
02 - Fraud Agent - 04 - Fraud Returns Agent
02 - Fraud Agent - 05 - Fraud First-Order Agent
02 - Fraud Agent - 06 - Fraud Address Surge Agent
02 - Fraud Agent - 07 - Fraud Promo Agent
02 - Fraud Agent - 08 - Fraud Inventory Agent
02 - Fraud Agent - 09 - Fraud Cancel Agent
02 - Fraud Agent - 10 - Fraud Geo Agent
Destroy deletes App mcp-ecommerce-oltp, drops the catalog, deletes the warehouse and workspace, then emails CLEANUP_NOTIFY_EMAIL that this codebase’s Databricks demo stack is gone and should not keep billing.
Repo Settings → Secrets and variables → Actions:
Secret | Purpose |
| Account API |
| OAuth M2M service principal |
| OAuth M2M secret |
| Users who can open the workspace UI |
| Inbox for the “stack cleaned” email |
| SMTP server (for example |
|
|
| SMTP login |
| SMTP password or app password |
| Optional From address; defaults to |
Optional: DATABRICKS_ACCOUNT_HOST, DATABRICKS_TOKEN, DATABRICKS_HOST, DATABRICKS_WORKSPACE_NAME, DATABRICKS_AWS_REGION, DATABRICKS_WAREHOUSE_NAME, DATABRICKS_CATALOG, DATABRICKS_SCHEMA, DATABRICKS_OLTP_SCHEMA.
Run locally (uses .env, talks to Databricks APIs)
Optional if you skip Actions → Run workflow. Same Databricks APIs; you run them from a machine with .env.
uv run genie-ontology run provision
uv run genie-ontology run create_agents
uv run genie-ontology run publish_mcp
uv run genie-ontology run publish_domains
uv run genie-ontology run invoke_agentsOr all three in order:
uv run genie-ontology run alluv run genie-ontology run invoke_agents --question "What was total revenue last quarter by product category?"
uv run genie-ontology run cleanup --confirm DELETE
uv run genie-ontology run truncate --confirm DELETECreate the Databricks workspace
Account-admin credentials in .env: DATABRICKS_ACCOUNT_ID plus DATABRICKS_TOKEN or OAuth client id/secret.
npm run ecommerce:workspace:databricks-setupEach ecommerce:*:databricks-setup command restarts local FastAPI so it always loads current code, then POSTs, then stops the server. No need to start ecommerce:api:run first or Ctrl+C afterward.
That calls POST /api/v1/ontology/provision-workspace and creates or reuses the serverless workspace named ecommerce-genie-ontology. Set DATABRICKS_WORKSPACE_ADMIN_EMAILS in .env (comma-separated) so those account users are assigned workspace ADMIN and can open the UI. The OAuth service principal is also assigned ADMIN; without a human email, only the SP can call APIs and you will see “You do not have permission to access this page”.
Create the SQL warehouse
After the workspace is RUNNING, create a serverless SQL warehouse (PRO + serverless compute, 2X-Small) named ecommerce-genie-ontology:
npm run ecommerce:warehouse:databricks-setupThat restarts FastAPI, POSTs WhReq to /api/v1/ontology/provision-warehouse, then stops the server. The warehouse is found later by name; copying warehouse_id into .env is optional.
Create the catalog, schema, and star tables
This is the reference-repo provision workflow: CREATE CATALOG / CREATE SCHEMA, star-schema tables, metric views, tags, and pages. Catalog defaults to ecommerce_genie_ontology; schemas default to retail_oltp (source) and retail_star (dims/facts) (DATABRICKS_CATALOG / DATABRICKS_OLTP_SCHEMA / DATABRICKS_SCHEMA in .env).
npm run ecommerce:catalog:databricks-setupThat restarts FastAPI, POSTs WfReq { "workflow": "provision" } to /api/v1/ontology/workflows/run, then stops the server.
Drop the catalog
Runs as the service principal (so you do not need MANAGE in the SQL Editor). Drops DATABRICKS_CATALOG with CASCADE and trashes the Genie agent. Workspace, warehouse, and metastore stay.
npm run ecommerce:catalog:databricks-truncateThen recreate with npm run ecommerce:catalog:databricks-setup.
HTTP interface
npm run ecommerce:api:runGET /healthGET /api/v1/ontology/workflowsPOST /api/v1/ontology/workflows/runbody:WfReqPOST /api/v1/ontology/workflows/deploybody:DpReqPOST /api/v1/ontology/provision-workspacebody:PwReqPOST /api/v1/ontology/provision-warehousebody:WhReqPOST /api/v1/ontology/truncatebody:TcReq(confirmmust beDELETE)POST /api/v1/ontology/destroybody:DyReq(confirmmust beDELETE; drops catalog, warehouse, and workspace)POST /api/v1/ontology/oltp/historicalbody:OhReqPOST /api/v1/ontology/oltp/realtimebody:OdReq(count100–10000,year_windowlatest|last_2|last_3|all)POST /api/v1/ontology/etl/historicalbody:EhReqPOST /api/v1/ontology/etl/cdcbody:EcReqPOST /api/v1/ontology/oltp/nextbody:NxReq(row_countdefault 100000)POST /api/v1/ontology/etl/next-monthsbody:EmReq(months1–12, default 3)GET /api/v1/ontology/fraud/casesPOST /api/v1/ontology/fraud/runbody:FcReq(case_id01–15)GET /api/v1/ontology/fraud/agentsPOST /api/v1/ontology/fraud/analytics/initiatebody:AnInitReqPOST /api/v1/ontology/fraud/analytics/getbody:AnIdReqPOST /api/v1/ontology/fraud/analytics/customersbody:AnListReqPOST /api/v1/ontology/fraud/analytics/customerbody:AnCustomerReqPOST /api/v1/ontology/fraud/analytics/customer/oltpbody:AnCustomerReqPOST /api/v1/ontology/fraud/analytics/customer/starbody:AnCustomerReqPOST /api/v1/ontology/fraud/analytics/outcomebody:AnOutcomeReqPOST /api/v1/ontology/fraud/analytics/closebody:AnIdReqPOST /api/v1/ontology/chatbody:ChReq(backendlanggraph|google_adk|genie)
Historical generate and ETL should run as Databricks jobs (as_job: true, the default) because 15 million orders need Spark. The local process only triggers the job.
How Genie Agent Works
Start with a Genie Agent (a Genie space). This is Databricks Genie on
/genie, not App mcp-ecommerce-oltp.
Genie Agent intro
Eleven spaces. Create all of them with GitHub Action 01 - Setup - Step 02 - Create all Genie agents (or Step 100, which calls Step 02). Recreate one specialist with that row’s 02 - Fraud Agent - NN GitHub Action. Nobody types the right-hand Configure tabs in the UI.
Space | Assigned cases | GitHub Action that creates / upserts it |
Retail Analytics Genie | merchandising (metric views) | 01 - Setup - Step 02 - Create all Genie agents |
Fraud Velocity Agent | 01, 07 | 01 - Setup - Step 02 or 02 - Fraud Agent - 01 - Fraud Velocity Agent |
Fraud Address Link Agent | 02, 06, 13 | 01 - Setup - Step 02 or 02 - Fraud Agent - 02 - Fraud Address Link Agent |
Fraud Ship-to Bill-to Agent | 03 | 01 - Setup - Step 02 or 02 - Fraud Agent - 03 - Fraud Ship-to Bill-to Agent |
Fraud Returns Agent | 04, 12 | 01 - Setup - Step 02 or 02 - Fraud Agent - 04 - Fraud Returns Agent |
Fraud First-Order Agent | 05 | 01 - Setup - Step 02 or 02 - Fraud Agent - 05 - Fraud First-Order Agent |
Fraud Address Surge Agent | 08, 09 | 01 - Setup - Step 02 or 02 - Fraud Agent - 06 - Fraud Address Surge Agent |
Fraud Promo Agent | 10 | 01 - Setup - Step 02 or 02 - Fraud Agent - 07 - Fraud Promo Agent |
Fraud Inventory Agent | 11 | 01 - Setup - Step 02 or 02 - Fraud Agent - 08 - Fraud Inventory Agent |
Fraud Cancel Agent | 14 | 01 - Setup - Step 02 or 02 - Fraud Agent - 09 - Fraud Cancel Agent |
Fraud Geo Agent | 15 | 01 - Setup - Step 02 or 02 - Fraud Agent - 10 - Fraud Geo Agent |
Open a space → Configure. Same mapping for every fraud specialist (Retail is the last column only):
Configure tab | Who writes it | Which GitHub Action | Code |
About title + description | This repo | Step 02 or that space’s 02 - Fraud Agent - NN |
|
About → Common questions | This repo | same | Plain English in |
About → Warehouse | This repo | 01 - Setup - Step 01 - Create Databricks stack (name); Step 02 attaches it | warehouse |
About → Agent ID | Databricks | none (assigned at create) |
|
Sources | This repo | Step 02 or 02 - Fraud Agent - NN | Fraud: |
Instructions | This repo | Step 02 or 02 - Fraud Agent - NN ( | Geo: Case 15 notes. Others: default “investigate only these cases”. Retail: |
Examples (join list) | Databricks, from keys we created | 01 - Setup - Step 01 ( | We do not send example SQL on fraud spaces |
The prompt used in this workspace for Fraud Geo is:
Which customers had orders shipped to two different regions within one hour?That is the first sample question on the space and the first checkbox on
02 - Fraud Agent - 10 - Fraud Geo Agent. Type it in the chat (or check
the box on that GitHub Action). Do not start from Retail Analytics for this
question. MCP run_fraud_case still uses ids 01–15; those ids are not sent
to Genie.
Fraud Geo Agent
What it is. A Databricks Genie space titled Fraud Geo Agent. Step 02
(or GitHub Action 02 - Fraud Agent - 10 - Fraud Geo Agent) creates it with create_agents --agent-id geo. In this repo that means case_ids: ("15",) — this space is assigned to investigate Case 15 only (impossible geography: the same customer has two shipping regions within one hour). That is a routing label in fraud_agents.py, not an AI “ownership” concept. It answers questions; it does not run Spark load jobs and it does not call mcp-ecommerce-oltp.
Structure (serialized space version: 2 from serialized_fraud_space):
Title:
Fraud Geo AgentDescription: Impossible geography: two regions on the same customer in a short window
Warehouse:
ecommerce-genie-ontology(author compute)Catalog:
{CATALOG}(Unity Catalogecommerce_genie_ontology)data_sources.tables: theSHARED_TABLESlist — fully qualified{CATALOG}.retail_oltp.*and{CATALOG}.retail_star.*only. Genie does not search other catalogs or schemas. Session/ETL tables (analytics_log,analytics_log_customer,ingestion_tracker,ingestion_log) are omitted so every fraud space skips them.instructions.text_instructions: Case 15 notes (overridable with system_prompt on GitHub Action 02 - Fraud Agent - 10 - Fraud Geo Agent)config.sample_questions: the five starters belowRegistry row:
{CATALOG}.retail_star._genie_agent_registry(title,space_id,warehouse_id)
URLs (copy space_id from the registry or from Step 02 logs):
Surface | URL |
All agents |
|
This agent (chat) |
|
Settings / Monitor | same room → Settings or Monitor |
Genie MCP |
|
Query History |
|
What it does. For the Case 15 prompt it should self-join
retail_star.fact_order_event on customer_key where
shipping_region_key differs and order_ts is at most 60 minutes apart.
Prefer mv_order_event only when you need counts. Seeded customers use
address_id *-AGEO (West vs Northeast) and two orders 25 minutes apart.
What it outputs. Generated read-only SQL, a result table, and a short
English answer. Expected columns: customer_key, both order ids, both
regions, both timestamps, minutes_apart. LIMIT 50. It must not say the
tables are empty after Step 06 + 07 (or Step 100).
What happened on this agent (simple)
You opened Fraud Geo Agent and typed
Which customers had orders shipped to two different regions within one hour?
(or the older wording Run fraud case 15 Impossible geo two regions one hour).
We already taught the space (GitHub Actions, not the chat):
Step 01 created the tables and wrote comments + primary/foreign keys. That is the metadata (names, meanings, how tables join).
Step 02 (or 02 - Fraud Agent - 10 - Fraud Geo Agent) attached those tables, pasted the Case 15 instructions, and the five sample questions.
Step 06 (inside Step 100) wrote the rows: 200 customers and a Case 15 seed — first 20 people, two orders 25 minutes apart (
10:00/10:25), ship regions that cannot both be true (*-AGEO).
Genie read the metadata, not the 200k rows. It looked at instructions (“join
fact_order_event…”), table comments, column names, and the join keys. Then its managed LLM wrote SQL. It did not scan every order in its head.The SQL warehouse ran that SQL and sent back a small table.
You saw 20 rows — customers
1–20, ordersO000000070001–O000000070040,minutes_apart = 25, West vs another region. That is the seed, not a live crime ring. Five region pairs × 4 customers is how we generated the addresses.Genie wrote the English / PDF (
Case 15 Fraud Detection_ .pdf) from those 20 rows. Spark created the seed. Genie created the story. The warehouse created the query run. Nobody typed thatSELECTinto the room.This chat is not Genie MCP. You will not see
genie_askhere. Same SQL appears on warehouse Monitoring / Query History. Export PDF is the answer text, not a tool log.
Earlier, before fact_order_event and the seed, the same prompt returned
no rows. After Step 100 + data, the same prompt returns these 20 pairs.
Prompts on this agent
Kind | Text |
Use this |
|
Starter (schema smoke) |
|
Starter (schema smoke) |
|
Starter (schema smoke) |
|
GHA extra | same question plus |
Context we provide (what Genie sees before the LLM writes SQL):
General instructions (Case 15 notes quoted in
fraud_agents.py)Unity Catalog comments on the attached tables
Table/column descriptions from
SHARED_TABLESSample questions on the space
Published Discover pages only if they actually exist in Discover (see below)
The current chat thread
How it knows which schemas and tables to use
Genie only considers objects listed on the space. It does not browse the whole workspace.
Use | Do not use for Case 15 |
Schema | Other catalogs, |
Schema | |
|
|
|
|
|
|
| Full-table dumps; inventing load steps |
SQL shape for the Case 15 prompt:
WITH pairs AS (
SELECT
a.customer_key,
a.order_id AS order1_id,
a.order_ts AS order1_ts,
a.shipping_region_key AS region1,
b.order_id AS order2_id,
b.order_ts AS order2_ts,
b.shipping_region_key AS region2,
(UNIX_TIMESTAMP(b.order_ts) - UNIX_TIMESTAMP(a.order_ts)) / 60.0 AS minutes_apart
FROM retail_star.fact_order_event a
JOIN retail_star.fact_order_event b
ON a.customer_key = b.customer_key
AND a.shipping_region_key <> b.shipping_region_key
AND b.order_ts > a.order_ts
AND (UNIX_TIMESTAMP(b.order_ts) - UNIX_TIMESTAMP(a.order_ts)) <= 3600
WHERE a.status <> 'cancelled' AND b.status <> 'cancelled'
)
SELECT * FROM pairs
ORDER BY minutes_apart, customer_key
LIMIT 50This Genie Agent Runtime Behavior
SQL does not run inside the LLM. Genie is a Databricks service: the model writes SQL, the space’s SQL warehouse runs it, then Genie hands the rows back so the model can write the English answer.
One turn after you paste the Geo prompt above:
FETCHING_METADATA — pull comments and PK/FK for the attached
retail_oltp/retail_startables.FILTERING_CONTEXT — keep Case 15 instructions, the
fact_order_eventdescription, sample questions, and this thread. Drop STALE sales/inventory unless the question is merchandising.ASKING_AI — Databricks-managed LLM proposes read-only SQL. No warehouse yet. There is no model picker on
/genie; Databricks chooses the compound stack. Query History and App logs do not name Claude vs GPT.PENDING_WAREHOUSE — wait for
ecommerce-genie-ontology.EXECUTING_QUERY — warehouse runs the generated SQL as you (Unity Catalog). Compute is the author’s warehouse. Retries stay in Genie.
mcp-ecommerce-oltpis not invoked.COMPLETED — chat shows SQL + rows + short answer. Failed turns show
FAILEDand an error type (SQL_EXECUTION_EXCEPTION,NO_TABLES_TO_QUERY_EXCEPTION, …).
Do Pages come into the picture?
Sometimes — only after a page is Published in Discover. They never run SQL and they never call MCP.
What this repo actually does:
Step 04 does create and publish Discover domains (Sales, Customer, Supply Chain, Finance) via
POST/PATCH /api/discover/v1/domains.Page bodies (Impossible Geo, Order Event Fact, plus merchandising pages) are written to
{CATALOG}.retail_star._ontology_pages.There is still no public Pages create/publish API. Step 04 retries Discover page endpoints; those calls SKIP until Databricks accepts the payload. Until you Publish a page in
https://{WORKSPACE_HOST}/search/discover, Case 15 does not get page synonyms or citations.
When a page is Published, Genie can use it as extra ontology: synonyms
(impossible geo, two regions one hour, case 15), citations in the
answer, and a pointer at fact_order_event. The warehouse path stays the
same. If Case 15 works with empty Discover Pages, that is expected —
instructions + SHARED_TABLES + UC comments are enough.
Genie MCP tools this agent uses — and where to trace them
Two different clients hit the same Genie space. They do not share one tool-call log.
A. You type in /genie/rooms/{SPACE_ID}
The UI uses the Genie Conversation API, not MCP. You will not see
genie_ask / genie_poll_response / genie_get_query_result in a tool
panel. Databricks still runs the same warehouse SQL.
B. An MCP client talks to Genie MCP
https://{WORKSPACE_HOST}/api/2.0/mcp/genie/{SPACE_ID}
That server exposes only these tools (Databricks-hosted; not our App):
Tool | When it fires | What you get |
| First question (and follow-ups with |
|
| Client waits through | Progress, final text, source links |
| After a query attachment exists | Schema + rows of the warehouse SQL |
| Client aborts the turn | Cancelled |
| MCP Apps / View clients | Same ask, interactive View |
Step 05 and invoke_agents --agent-id geo use the workspace SDK
(start_conversation_and_wait) — path A, not these MCP tool names.
Cursor / Claude Desktop / Supervisor pointed at Genie MCP is path B.
mcp-ecommerce-oltp is never in either list.
Where to trace them
What | Where to open |
Path A — chat SQL + thoughts |
|
Path A — all questions | Same room → Monitor (CAN MANAGE) |
Path A or B — message JSON, statuses, |
|
Path A or B — warehouse SQL, duration, user |
|
Path A or B — who asked | Audit logs: Genie Agent events (ids and time, not SQL) |
Path B — which MCP tool ran | The MCP client transcript (Cursor / Claude / Supervisor tool calls). Databricks does not write |
Path B — Genie MCP HTTP | Client debug / proxy logs against |
Step 05 / Fraud Geo GitHub Action ask job | GitHub Actions log: |
Space id for all of the above |
|
Our App tools ( | Not this agent. App logs at |
Pages / citations | Discover |
MCP tools
There are two MCP surfaces. Databricks Genie is the managed analytics path inside the workspace. This repo also runs its own MCP server for load, ETL, and fraud evidence. Do not put generate or CDC tools on Genie.
MCP tools in Databricks Genie
Databricks hosts these servers. create_agents publishes one Retail Analytics
Genie space plus ten fraud specialist spaces on the same OLTP and star tables.
An MCP client authenticates to the workspace and calls Genie; Genie writes the
SQL.
Server | URL | When to use |
Genie One |
| Natural-language questions across the workspace |
Genie Agent |
| Questions scoped to one Genie space (retail analytics or one fraud specialist) |
Databricks SQL |
| The MCP client already has a SQL string (you typed it, Cursor/Claude wrote it, or an agent composed it). Databricks only executes it. Genie is not in this path. Not for fraud generate / CDC. |
Genie One / Genie Agent tools (the client calls genie_ask; the rest are for
the in-flight turn):
Tool | What it does |
| Ask a natural-language data question. Returns |
| Read progress, the final answer, and links back to Databricks sources. |
| Fetch the SQL result Genie ran (schema and rows). |
| Cancel an in-flight Genie turn. |
| Same ask, opens the interactive View (MCP Apps clients). |
Genie spaces created here: Retail Analytics Genie (certified metric views)
and the ten fraud specialists (velocity, address_link, ship_bill,
returns, first_order, address_surge, promo, inventory, cancel,
geo). Those specialists answer questions; they do not run Spark jobs.
MCP tools in this codebase
The custom MCP is ecommerce-oltp-mcp in
src/ecommerce_genie_ontology/mcp/. GitHub Action Step 03 publishes it as
Databricks App mcp-ecommerce-oltp (name prefix mcp- so Playground /
Supervisor list it). The App URL uses Pyctuator
(GET / → /actuator/health, plus /actuator/info and /actuator/metrics);
tools stay on {APP_URL}/mcp. LangGraph, Google ADK, Cursor, and Claude Desktop can
also call it over stdio. Portal chat reaches the same functions through
FastAPI facades. Classic Genie spaces do not attach this App; they stay on
Genie MCP. Every evidence tool returns at most 50 rows.
uv sync --extra mcp
uv run --extra mcp genie-ontology mcp
uv run --extra mcp genie-ontology mcp --httpCursor / Claude Desktop:
{
"mcpServers": {
"ecommerce-oltp": {
"command": "uv",
"args": ["run", "--extra", "mcp", "genie-ontology", "mcp"],
"cwd": "/path/to/Databricks-Genie-Ontology"
}
}
}Evidence and specialists
Tool | What it does |
| The 15 named fraud cases (evidence packs, not full tables). |
| The 10 specialists and which case ids each is assigned ( |
| Run one case ( |
| Run every pack owned by one specialist. |
| Date range in; server finds IDs; writes |
| Session header and pending customer count. |
| Page of customers (max 50) with counts and outcome. |
| One customer: counts + at most 50 evidence rows. |
| At most 25 orders and 25 postings for one customer. |
| At most 25 sales facts and 25 posting facts for one customer. |
|
|
| Status |
| One |
Load and ETL (operators; not on Genie)
Tool | What it does |
| Write customer, address, order, line, shipment, and |
| Append 100–10,000 new orders. Window: |
| Overwrite star dims and facts from OLTP. |
| Apply Delta change feed from |
| Next 100,000 OLTP rows. Writes |
| Next N months of dims/facts (1–12). No error if less OLTP remains. |
Customer-first contract is on this server, not Genie: open a session, page customers, hydrate one customer, write the outcome, then close.
Fraud agents (inside ecommerce_genie_ontology)
FastAPI never imports LangGraph or Google ADK. It calls a facade; the facade invokes that stack.
src/ecommerce_genie_ontology/
agents_langgraph/ # LgFacade → LangGraph StateGraph → MCP tools
facade.py graph.py
agents_google_adk/ # GaFacade → ADK root + 10 sub-agents → MCP tools
facade.py agent.py runtime.pyuv sync --extra agents
# or: uv sync --extra langgraph --extra google-adk --extra mcpPOST /api/v1/ontology/chat with "backend": "langgraph" or "google_adk" or "genie". The 10 Databricks Genie specialists are created by create_agents on the same OLTP + dims/facts.
Run all 15 fraud packs without an LLM:
uv run genie-ontology fraud-agent
uv run genie-ontology fraud-agent --cases 01,02,06
uv run genie-ontology run fraud --case-id 02Context management and graph databases
The agent never sees 15 million orders. Spark/SQL stay in Databricks. Each fraud case is a named query plus a small evidence pack. retail_oltp.entity_link stores 1–2 hop relationships (has_address, shared_address) so you do not need Neo4j for these 15 cases. Add a graph store later only if you need unbounded multi-hop traversal or a live investigation UI.
OLTP and CDC without GitHub Actions
Same jobs as 01 - Setup Steps 07–10. Use this only if you are not running those Actions.
uv run genie-ontology deploy
uv run genie-ontology run generate_historical --as-job --customers 200 --orders-per-year 25000 --years 3
uv run genie-ontology run etl_historical --as-job
uv run genie-ontology run generate_realtime --as-job --count 1000 --year-window latest
uv run genie-ontology run etl_cdc --as-jobRegister and run Databricks Jobs without GitHub Actions
Same as 01 - Setup Steps 01–03. Use this only if you are not running those Actions.
uv run genie-ontology deploy
uv run genie-ontology run provision --as-job
uv run genie-ontology run create_agents --as-job
uv run genie-ontology run invoke_agents --as-jobdeploy uploads this package plus thin wrapper notebooks to
DATABRICKS_WORKSPACE_PATH and creates or updates the jobs (Genie provision plus OLTP/CDC Spark jobs).
Discover domains use the public Domain API
(POST /api/discover/v1/domains, then PATCH draft=false to publish).
Step 04 and provision t04 create Sales, Customer, Supply Chain, and Finance
from the existing governed tags. Pages still have no documented public create
API. Provision stores the same content in <catalog>.<schema>._ontology_pages
and Step 04 retries Discover page endpoints after the parent domain exists.
Page bodies include Impossible Geo and Order Event Fact. fact_sales and
fact_inventory are documented as STALE merchandising snapshots.
Available Tools
9 toolsetl_star_cdcB
Apply Delta change feed from OLTP customer_order into fact_sales (append).
| Name | Required | Description | Default |
|---|---|---|---|
| as_job | No |
TDQS
Does the description disclose side effects, auth requirements, rate limits, or destructive behavior?
With no annotations, the description must carry the disclosure burden. It does disclose the append behavior and source/target, but omits side effects, asynchronous job behavior, idempotency, or failure handling.
Agents need to know what a tool does to the world before calling it. Descriptions should go beyond structured annotations to explain consequences.
Is the description appropriately sized, front-loaded, and free of redundancy?
The description is a single front-loaded sentence with no filler, efficiently conveying source, target, and append mode. It is concise, though this conciseness comes at the cost of behavioral and parameter detail.
Shorter descriptions cost fewer tokens and are easier for agents to parse. Every sentence should earn its place.
Given the tool's complexity, does the description cover enough for an agent to succeed on first attempt?
For an ETL write operation with no annotations or output schema, the description is too sparse. It does not explain the meaning of as_job, the relationship to etl_star_historical, or what happens on failures, empty change feeds, or duplicate data.
Complex tools with many parameters or behaviors need more documentation. Simple tools need less. This dimension scales expectations accordingly.
Does the description clarify parameter syntax, constraints, interactions, or defaults beyond what the schema provides?
There is one parameter, as_job, with 0% schema description coverage. The description does not mention or explain this parameter, leaving the agent with only the parameter name and default value as hints.
Input schemas describe structure but not intent. Descriptions should explain non-obvious parameter relationships and valid value ranges.
Does the description clearly state what the tool does and how it differs from similar tools?
The description names a specific verb ('Apply'), the exact data source ('Delta change feed from OLTP customer_order'), target ('fact_sales'), and mode ('append'). This clearly distinguishes it from siblings like etl_star_historical and generate_historical_oltp.
Agents choose between tools based on descriptions. A clear purpose with a specific verb and resource helps agents select the right tool.
Does the description explain when to use this tool, when not to, or what alternatives exist?
The description implies this is for incremental CDC appends, but it does not explicitly name alternatives, state prerequisites, or describe when not to use it. Usage context is inferable but not spelled out.
Agents often have multiple tools that could apply. Explicit usage guidance like "use X instead of Y when Z" prevents misuse.
etl_star_historicalC
Rebuild star-schema dims and fact_sales from OLTP (overwrite).
| Name | Required | Description | Default |
|---|---|---|---|
| as_job | No |
TDQS
Does the description disclose side effects, auth requirements, rate limits, or destructive behavior?
With no annotations, the description carries the full behavioral burden. It does disclose the key destructive trait 'overwrite', which is essential safety information for an agent. However, it omits consequences such as whether tables are dropped/recreated, whether the operation is transactional, and what happens to existing dependent objects.
Agents need to know what a tool does to the world before calling it. Descriptions should go beyond structured annotations to explain consequences.
Is the description appropriately sized, front-loaded, and free of redundancy?
The description is a single efficient sentence with no filler and the core action front-loaded. It is concise, though the brevity leaves out details that are penalized in other dimensions.
Shorter descriptions cost fewer tokens and are easier for agents to parse. Every sentence should earn its place.
Given the tool's complexity, does the description cover enough for an agent to succeed on first attempt?
For a destructive ETL tool with no annotations and no output schema, the description is too thin. It leaves the as_job parameter unexplained and does not state expected completion signals, side effects, or the relationship to etl_star_cdc, making confident invocation harder than necessary.
Complex tools with many parameters or behaviors need more documentation. Simple tools need less. This dimension scales expectations accordingly.
Does the description clarify parameter syntax, constraints, interactions, or defaults beyond what the schema provides?
The schema has one boolean parameter 'as_job' with 0% description coverage, and the description does not explain what it controls. The agent cannot know whether setting as_job to false changes execution mode, output, or behavior, so the description fails to compensate for the schema's silence.
Input schemas describe structure but not intent. Descriptions should explain non-obvious parameter relationships and valid value ranges.
Does the description clearly state what the tool does and how it differs from similar tools?
The description uses a specific verb ('Rebuild') with a clear resource ('star-schema dims and fact_sales') and source ('from OLTP'), and flags 'overwrite' to signal a full replacement. It does not explicitly differentiate from sibling etl_star_cdc, though the word 'historical' and the rebuild/overwrite language weakly distinguish it as a full load.
Agents choose between tools based on descriptions. A clear purpose with a specific verb and resource helps agents select the right tool.
Does the description explain when to use this tool, when not to, or what alternatives exist?
No explicit guidance is given about when to use this tool instead of etl_star_cdc, generate_historical_oltp, or other siblings. An agent must infer from the name and the words 'Rebuild'/'overwrite' that this is a full refresh, but there is no direct statement of intended use or exclusions.
Agents often have multiple tools that could apply. Explicit usage guidance like "use X instead of Y when Z" prevents misuse.
generate_historical_oltpC
Write customer, address, order, line, shipment, and entity_link tables.
| Name | Required | Description | Default |
|---|---|---|---|
| as_job | No | ||
| year_count | No | ||
| customer_count | No | ||
| orders_per_year | No |
TDQS
Does the description disclose side effects, auth requirements, rate limits, or destructive behavior?
No annotations are provided, so the description carries the full burden of behavioral disclosure. It implies mutation via 'Write' but discloses nothing about whether existing data is overwritten, whether execution is synchronous or asynchronous (despite the as_job parameter hinting at job-based execution), or what side effects occur. For a tool that writes multiple tables, this is a significant gap.
Agents need to know what a tool does to the world before calling it. Descriptions should go beyond structured annotations to explain consequences.
Is the description appropriately sized, front-loaded, and free of redundancy?
A single, front-loaded sentence with no wasted words - structurally clean. However, this is under-specification rather than effective conciseness: the description omits critical information about parameters and behavior, making it too brief for the tool's complexity.
Shorter descriptions cost fewer tokens and are easier for agents to parse. Every sentence should earn its place.
Given the tool's complexity, does the description cover enough for an agent to succeed on first attempt?
This is a 4-parameter mutation tool that writes six tables, with no annotations and no output schema. The description is far too thin - it doesn't explain parameter semantics, execution model, data implications, or any context an agent needs to invoke it correctly. Completely inadequate for a tool of this complexity.
Complex tools with many parameters or behaviors need more documentation. Simple tools need less. This dimension scales expectations accordingly.
Does the description clarify parameter syntax, constraints, interactions, or defaults beyond what the schema provides?
Schema description coverage is 0% and the description adds nothing about the 4 parameters. While year_count, customer_count, and orders_per_year are somewhat self-explanatory by name, as_job is ambiguous (job vs synchronous execution) and none of the parameters are explained or contextualized. The description fails to compensate for the complete lack of schema-level parameter documentation.
Input schemas describe structure but not intent. Descriptions should explain non-obvious parameter relationships and valid value ranges.
Does the description clearly state what the tool does and how it differs from similar tools?
States a specific action ('Write') with an enumerated resource list (customer, address, order, line, shipment, entity_link tables). The purpose is clear and concrete. However, differentiation from siblings relies mostly on the tool name ('historical' vs generate_realtime_orders) rather than the description itself, and 'write' is a generic verb that doesn't clarify whether this creates, populates, or overwrites.
Agents choose between tools based on descriptions. A clear purpose with a specific verb and resource helps agents select the right tool.
Does the description explain when to use this tool, when not to, or what alternatives exist?
Provides no guidance on when to use this tool versus alternatives. Siblings include generate_realtime_orders (similar generation, realtime) and etl_star_historical (ETL for historical data), yet the description gives no selection criteria, prerequisites, or exclusions. An agent must infer usage entirely from the tool name.
Agents often have multiple tools that could apply. Explicit usage guidance like "use X instead of Y when Z" prevents misuse.
generate_realtime_ordersC
Append 100-10000 new OLTP orders. year_window is latest | last_2 | last_3 | all.
| Name | Required | Description | Default |
|---|---|---|---|
| count | No | ||
| as_job | No | ||
| year_window | No | latest |
TDQS
Does the description disclose side effects, auth requirements, rate limits, or destructive behavior?
With no annotations, the description carries the full burden and only discloses that it appends orders within a count range and year-window choices. It does not state side effects, persistence, idempotency, what 'as_job' does, or what the caller should expect afterward.
Agents need to know what a tool does to the world before calling it. Descriptions should go beyond structured annotations to explain consequences.
Is the description appropriately sized, front-loaded, and free of redundancy?
Two short sentences deliver the core action, count bounds, and year-window options without wasted words. It loses a point only because omitting as_job is a meaningful structural gap, not because of verbosity.
Shorter descriptions cost fewer tokens and are easier for agents to parse. Every sentence should earn its place.
Given the tool's complexity, does the description cover enough for an agent to succeed on first attempt?
For a tool with no annotations and no output schema, the description leaves out too much: return behavior, job semantics, prerequisite state, and how this append interacts with historical generation. An agent can make a default call but cannot fully reason about consequences.
Complex tools with many parameters or behaviors need more documentation. Simple tools need less. This dimension scales expectations accordingly.
Does the description clarify parameter syntax, constraints, interactions, or defaults beyond what the schema provides?
The description explains year_window values and indirectly maps '100-10000' to the count parameter, which helps. However, as_job is completely unexplained, and schema coverage is 0%, so the agent cannot derive its meaning from the schema either.
Input schemas describe structure but not intent. Descriptions should explain non-obvious parameter relationships and valid value ranges.
Does the description clearly state what the tool does and how it differs from similar tools?
The description uses a specific verb ('Append'), identifies the resource ('new OLTP orders'), and gives a concrete count range, so an agent knows the core action. It does not explicitly contrast with generate_historical_oltp, but the 'realtime' name and year-window options make the intent reasonably distinguishable.
Agents choose between tools based on descriptions. A clear purpose with a specific verb and resource helps agents select the right tool.
Does the description explain when to use this tool, when not to, or what alternatives exist?
There is no guidance about when to use this tool versus generate_historical_oltp, etl_star_cdc, or other siblings. The 'append' semantics hint at real-time ingestion, but the description leaves the selection criteria implicit.
Agents often have multiple tools that could apply. Explicit usage guidance like "use X instead of Y when Z" prevents misuse.
list_fraud_agentsA
List the 10 Databricks Genie fraud specialists that share OLTP + dims/facts.
| Name | Required | Description | Default |
|---|---|---|---|
No parameters | |||
Output Schema
| Name | Required | Description |
|---|---|---|
| result | Yes |
TDQS
Does the description disclose side effects, auth requirements, rate limits, or destructive behavior?
There are no annotations, so the description needs to convey safety and side effects. It states it returns a list of 10 specialists, which implies a read-only operation, but it doesn't explicitly say it doesn't modify anything. It also doesn't clarify if the list is cached or if it takes time, but since it's a simple list with no params, the behavior is fairly predictable. The description adds some value by noting the shared data model, which hints at the underlying structure.
Agents need to know what a tool does to the world before calling it. Descriptions should go beyond structured annotations to explain consequences.
Is the description appropriately sized, front-loaded, and free of redundancy?
The description is a single, concise sentence that front-loads the action ('List the 10 Databricks Genie fraud specialists') and adds a relevant detail about their data model. There is no fluff; every word contributes to the meaning. This is an exemplary model of brevity.
Shorter descriptions cost fewer tokens and are easier for agents to parse. Every sentence should earn its place.
Given the tool's complexity, does the description cover enough for an agent to succeed on first attempt?
Given that there are no parameters and no annotations, the description is nearly complete: it explains what the tool does and provides a key detail about the output (the 10 specialists share OLTP + dims/facts). However, it doesn't describe the output schema (which exists), but since the description doesn't need to repeat what the output schema already holds, this is not a major gap. The description is adequate for a simple listing tool.
Complex tools with many parameters or behaviors need more documentation. Simple tools need less. This dimension scales expectations accordingly.
Does the description clarify parameter syntax, constraints, interactions, or defaults beyond what the schema provides?
The input schema has zero parameters, and the description confirms that by mentioning 'the 10'—the list size is fixed. Since there are no parameters, there's no additional semantic burden; the description fully covers the empty schema. This is a case where the description doesn't need to explain anything, so a high score is appropriate. However, it could be clearer that the list is fixed and no parameters are needed.
Input schemas describe structure but not intent. Descriptions should explain non-obvious parameter relationships and valid value ranges.
Does the description clearly state what the tool does and how it differs from similar tools?
The description tells the agent exactly what this tool does: it lists the 10 Databricks Genie fraud specialists, which is a specific, concrete action. It includes the key detail that these specialists share a specific data model (OLTP + dims/facts), which adds useful context. However, it doesn't explicitly differentiate from sibling list_fraud_cases, though the nouns differ (agents vs cases), so the differentiation is implicit.
Agents choose between tools based on descriptions. A clear purpose with a specific verb and resource helps agents select the right tool.
Does the description explain when to use this tool, when not to, or what alternatives exist?
The description implies the use case—when you need an overview of the fraud agents available—but it doesn't explicitly say when to use this instead of list_fraud_cases or run_fraud_agent_cases. The tool has no parameters, so it's a simple listing operation, but no guidance is given on what actions to take with the results.
Agents often have multiple tools that could apply. Explicit usage guidance like "use X instead of Y when Z" prevents misuse.
list_fraud_casesA
List the 15 fraud detection cases. Each case is a named SQL evidence pack.
| Name | Required | Description | Default |
|---|---|---|---|
No parameters | |||
Output Schema
| Name | Required | Description |
|---|---|---|
| result | Yes |
TDQS
Does the description disclose side effects, auth requirements, rate limits, or destructive behavior?
With no annotations, the description carries the transparency burden. 'List' communicates a non-mutating read operation and 'the 15' sets exact scope, but there is no stated behavior about output sorting, freshness, or side effects; this is adequate but minimal for a zero-parameter read tool.
Agents need to know what a tool does to the world before calling it. Descriptions should go beyond structured annotations to explain consequences.
Is the description appropriately sized, front-loaded, and free of redundancy?
Two compact sentences with the main action front-loaded; every word earns its place and the second sentence adds useful conceptual context.
Shorter descriptions cost fewer tokens and are easier for agents to parse. Every sentence should earn its place.
Given the tool's complexity, does the description cover enough for an agent to succeed on first attempt?
For a simple zero-parameter listing tool with an output schema present, the description gives the critical extra information: there are exactly 15 cases and each is a named SQL evidence pack. It does not explicitly connect to run_fraud_case for the next action, but that belongs to usage guidance and is not required for invoking this tool.
Complex tools with many parameters or behaviors need more documentation. Simple tools need less. This dimension scales expectations accordingly.
Does the description clarify parameter syntax, constraints, interactions, or defaults beyond what the schema provides?
The input schema is empty and fully covers all parameters (none), so there is no parameter gap for the description to fill. The 0-parameter baseline of 4 is appropriate.
Input schemas describe structure but not intent. Descriptions should explain non-obvious parameter relationships and valid value ranges.
Does the description clearly state what the tool does and how it differs from similar tools?
Uses a specific verb ('List') and resource ('fraud detection cases'), and adds the exact count and a definition ('named SQL evidence pack'). This clearly distinguishes it from siblings such as run_fraud_case (executes) and list_fraud_agents (different entity).
Agents choose between tools based on descriptions. A clear purpose with a specific verb and resource helps agents select the right tool.
Does the description explain when to use this tool, when not to, or what alternatives exist?
The description implies that this tool is the discovery entry point for the fixed set of fraud cases, and the definition of a case hints that returned names can be used with run_fraud_case. However, it never explicitly states when to choose this tool over alternatives or when not to use it, so guidance is only implicit.
Agents often have multiple tools that could apply. Explicit usage guidance like "use X instead of Y when Z" prevents misuse.
query_datasetA
Run a SELECT against dims, facts, or OLTP. LIMIT 50. Never dumps full tables.
| Name | Required | Description | Default |
|---|---|---|---|
| sql | Yes |
TDQS
Does the description disclose side effects, auth requirements, rate limits, or destructive behavior?
With no annotations, the description carries the full behavioral burden. It discloses that only SELECT operations are run, that results are capped at 50 rows, and that full-table dumps are forbidden. This is meaningful behavioral context for a read-only query tool, though it does not mention auth, errors, or return shape.
Agents need to know what a tool does to the world before calling it. Descriptions should go beyond structured annotations to explain consequences.
Is the description appropriately sized, front-loaded, and free of redundancy?
Two short sentences with no filler. The key constraints are front-loaded and every word adds value. This is an efficient, well-structured description.
Shorter descriptions cost fewer tokens and are easier for agents to parse. Every sentence should earn its place.
Given the tool's complexity, does the description cover enough for an agent to succeed on first attempt?
For a one-parameter query tool, the description is mostly complete: it states the operation, allowed data sources, row limit, and guarding behavior. It lacks an explicit statement about return format or output structure, but no output schema exists and the tool's purpose strongly implies a row result set.
Complex tools with many parameters or behaviors need more documentation. Simple tools need less. This dimension scales expectations accordingly.
Does the description clarify parameter syntax, constraints, interactions, or defaults beyond what the schema provides?
The input schema describes the single 'sql' parameter only by name and type, with 0% description coverage. The tool description compensates somewhat by clarifying that the SQL must be a SELECT and that the result set is limited to 50 rows. However, it does not explain SQL formatting, table qualification, or statement restrictions beyond SELECT.
Input schemas describe structure but not intent. Descriptions should explain non-obvious parameter relationships and valid value ranges.
Does the description clearly state what the tool does and how it differs from similar tools?
The description states a specific verb and resource: 'Run a SELECT against dims, facts, or OLTP.' It also adds a concrete constraint, LIMIT 50, making the tool's purpose unmistakable. It clearly differentiates from the ETL/generation/fraud-case siblings by being the only direct query tool.
Agents choose between tools based on descriptions. A clear purpose with a specific verb and resource helps agents select the right tool.
Does the description explain when to use this tool, when not to, or what alternatives exist?
The description gives clear context: this is for SELECT queries against dims, facts, or OLTP, with a 50-row limit. It does not explicitly name alternatives or state when not to use it, but the scope is clear enough for an agent to select it appropriately among the listed siblings.
Agents often have multiple tools that could apply. Explicit usage guidance like "use X instead of Y when Z" prevents misuse.
run_fraud_agent_casesC
Run every evidence pack owned by one of the 10 fraud specialists.
| Name | Required | Description | Default |
|---|---|---|---|
| agent_id | Yes |
TDQS
Does the description disclose side effects, auth requirements, rate limits, or destructive behavior?
No annotations are provided, so the description carries the full burden of behavioral disclosure. It says 'Run' without explaining whether this starts long-running jobs, writes data, returns results, or has side effects on the evidence packs. For an action-oriented tool, this is a significant transparency gap.
Agents need to know what a tool does to the world before calling it. Descriptions should go beyond structured annotations to explain consequences.
Is the description appropriately sized, front-loaded, and free of redundancy?
The description is a single, front-loaded sentence. It uses no filler and every word contributes to describing what the tool does.
Shorter descriptions cost fewer tokens and are easier for agents to parse. Every sentence should earn its place.
Given the tool's complexity, does the description cover enough for an agent to succeed on first attempt?
For a side-effecting 'run' tool with no annotations, no output schema, and minimal parameter explanation, the definition is incomplete. It communicates the high-level intent but lacks context about effects, output/return behavior, and how to discover the supported fraud specialist IDs.
Complex tools with many parameters or behaviors need more documentation. Simple tools need less. This dimension scales expectations accordingly.
Does the description clarify parameter syntax, constraints, interactions, or defaults beyond what the schema provides?
The schema provides no description for agent_id, and schema coverage is 0%, so the description must compensate. The phrase 'one of the 10 fraud specialists' gives some semantic meaning to agent_id, but it does not explain how to obtain valid IDs or connect this to list_fraud_agents.
Input schemas describe structure but not intent. Descriptions should explain non-obvious parameter relationships and valid value ranges.
Does the description clearly state what the tool does and how it differs from similar tools?
The description uses a specific verb ('Run') and resource ('evidence pack'), and the plural 'every' distinguishes it from the sibling run_fraud_case, which implies a single case. The scope is clear: all evidence packs owned by one of the 10 fraud specialists. It lacks an explicit contrast with sibling tools, but the distinction is recoverable.
Agents choose between tools based on descriptions. A clear purpose with a specific verb and resource helps agents select the right tool.
Does the description explain when to use this tool, when not to, or what alternatives exist?
There is no guidance on when to use this tool versus run_fraud_case, list_fraud_cases, or list_fraud_agents. The description implies a batch operation for a specialist, but it does not state prerequisites, exclusions, or how to choose among alternatives.
Agents often have multiple tools that could apply. Explicit usage guidance like "use X instead of Y when Z" prevents misuse.
run_fraud_caseB
Run one fraud case (ids 01-15). Returns at most 50 evidence rows.
| Name | Required | Description | Default |
|---|---|---|---|
| case_id | Yes |
TDQS
Does the description disclose side effects, auth requirements, rate limits, or destructive behavior?
No annotations are provided, so the description carries the burden of disclosing behavior. It does reveal a key behavioral trait: the tool returns at most 50 evidence rows, which is useful for setting agent expectations. However, it does not mention side effects (e.g., whether running a case performs writes or triggers external actions), which is a gap for a mutation-like tool.
Agents need to know what a tool does to the world before calling it. Descriptions should go beyond structured annotations to explain consequences.
Is the description appropriately sized, front-loaded, and free of redundancy?
The description is extremely concise—two short sentences, no filler. It front-loads the core action and then adds the crucial output limit. Every word earns its place.
Shorter descriptions cost fewer tokens and are easier for agents to parse. Every sentence should earn its place.
Given the tool's complexity, does the description cover enough for an agent to succeed on first attempt?
Given the tool's simplicity (single parameter, no output schema, no nested objects), the description is nearly sufficient but falls short in parameter guidance and side-effect disclosure. The 50-row limit is good context, but the lack of case_id explanation and any indication of write behavior leaves minor gaps.
Complex tools with many parameters or behaviors need more documentation. Simple tools need less. This dimension scales expectations accordingly.
Does the description clarify parameter syntax, constraints, interactions, or defaults beyond what the schema provides?
The input schema only has a case_id string with no description, and the schema description coverage is 0%. The description does not explain what case_id should be (e.g., the format, the allowed range 01-15, or how to obtain valid IDs). Since the schema provides no help, the description fails to compensate, leaving the agent guessing.
Input schemas describe structure but not intent. Descriptions should explain non-obvious parameter relationships and valid value ranges.
Does the description clearly state what the tool does and how it differs from similar tools?
The description states a specific verb ('run') and resource ('fraud case'), and clarifies that it handles one case at a time (ids 01-15). This is clear enough to distinguish from sibling tools like list_fraud_cases, though it doesn't explicitly name them.
Agents choose between tools based on descriptions. A clear purpose with a specific verb and resource helps agents select the right tool.
Does the description explain when to use this tool, when not to, or what alternatives exist?
The description implies when to use it: when you need to execute a specific fraud case. However, it does not explicitly say when not to use it or mention alternatives like list_fraud_cases for listing, or run_fraud_agent_cases for running multiple cases. The single-case scope is a mild hint but not a full routing guide.
Agents often have multiple tools that could apply. Explicit usage guidance like "use X instead of Y when Z" prevents misuse.
Tool Schema Changelog
Recent tool additions, removals, and schema changes observed during successful MCP inspections.
9 tool updates
v0.1.0- First observed
etl_star_cdc - First observed
etl_star_historical - First observed
generate_historical_oltp - First observed
generate_realtime_orders - First observed
list_fraud_agents - First observed
list_fraud_cases - First observed
query_dataset - First observed
run_fraud_agent_cases - First observed
run_fraud_case
TDQS
Scored across 9 tools
Most tools have clearly distinct roles: generation, ETL, fraud case listing/execution, and querying. The main ambiguity is between run_fraud_case and run_fraud_agent_cases, but their descriptions clarify single-case versus agent-owned case batches.
All tool names follow a consistent snake_case verb_noun pattern, with recognizable prefixes like run_, list_, generate_, etl_star_, and query_. This makes the tool surface predictable and easy to navigate.
Nine tools is a well-scoped size for this server's purpose. Each tool covers a distinct phase of the workflow—data generation, ETL, fraud case evaluation, and querying—without unnecessary redundancy.
The core lifecycle of generating OLTP data, building star-schema artifacts, running fraud cases, and querying results is well covered. Minor gaps include the lack of explicit schema/reset management or direct row-level CRUD, but agents can work around these using query_dataset and generation tools.
Maintenance
Related MCP Connectors
Your Databricks Lakehouse in natural language: run SQL on your SQL warehouses, track long-running qu
Check your cron jobs, recent runs, workflows and webhooks, and run a job now when you confirm.
List reverse-ETL sources, destinations, models, syncs and runs; trigger syncs into SaaS tools.
Agentic commerce gateway: discovery, search, checkout across Shopify/Woo/Odoo/PrestaShop.
Related MCP Servers
- FlicenseNot gradedqualityDmaintenanceEnables AI agents to explore and query a mock e-commerce store's data including customers, products, inventory, and orders through conversational interactions backed by PostgreSQL.-
- FlicenseNot gradedqualityDmaintenanceEnables simulating customer orders from a dummy restaurant menu and tracking their status in real-time via RESTful APIs.-
- FlicenseNot gradedqualityCmaintenanceEnables AI assistants to investigate and safely resolve commerce order exceptions, such as expired inventory reservations, by providing a workflow across synthetic order, payment, inventory, and fulfillment systems.-
- FlicenseNot gradedqualityCmaintenanceProvides deterministic mock customer health, product usage, and recommended playbook data via three MCP tools for a Customer Success Orchestrator demo.-