Mission: Zava Support Intelligence Mart

Mission/open goal Description

I chose Data Engineer mission, built a notebook-driven Support Intelligence Mart for Zava. The ZCPTM-SS-M-BW defect hiding across EN/ES/FR, invisible to keyword search, found by find_similar_docs_by_text.

Harness and model

Github Copilot agent in VS Code, run against a SQL Server 2025 dev container. Model: GPT 5.5

Turn-by-turn journey

Phase 1 - Discovery

Prompt:
I want to have functions that will discover schema, discover keys, validate relationships. Add to the notebook the derived functions and a mermaid ERD of the 8 entities, give solid edges for enforced FKs, dashed for curated links.
I re-derived the schema from the live catalog. This helped me shape the downstream with these facts:

• The join spine is RelatedOrderId has zero nulls on tickets, chats and docs. SKU is nullable everywhere thus unreliable.
• Enforced vs curated relationships. The DB declares 8PKs and 9enforcedFKs

• Docs.Embedding is native vector(1536). This later pins the embedding model.

Phase 2 - Building the mart

Prompt: Add to the support_mart data quality summary, compute identity nulls and add to the notebook these data quality checks with implications that will be used as a rulebook in later sections.

• Data quality: enforced FKs. Checked for duplicates . Reconciled the sales fact to the source, proving the joins conserved every row and every dollar.

• It is at this stage that I also noted that sales are dominated by anonymous guests but support tickets are almost entirely from registered customers. Thus, revenue risk from support inherently links registered/B2B support signals to a mostly guest revenue base.

• Chat parsing uses structural, language agnostic features as primary signal, with and English keyword lexicon flagged explicitly as a supplementary heuristic that undercounts spanish/french.

Phase 3 - Registering a new MCP tool find_similar_docs_by_text

• A new MCP tool registered in DATA API Builder, generating query embeddings in database via Azure OpenAI and searching with VECTOR_DISTANCE.

This is the agent invoking the MCP tool:
Prompt -

Results:

Phase 4 - Segment aware scorecard

• A weighted sum of named 0-1 components: revenue exposure, ticket volume, priority severity, unresolved rate, low-satisfaction rate, negative-doc count, similar-complaint density and cross-lingual complaint density.
• I ranked within segment, so that concentration for instance 67.45% for B2B does not crush a genuinely defective B2C product.

• Scored across four dimension: product, category, channel and segment.
• I documented weight tilt toward review/complaint density signals over ticket bassed ones. The reason is unprompted public reviews and cross-lingual clustering are leading indicators of product quality risk while support tickets are a self selected signal. To keep this honest, a weighting_sensitivity table shows the top product's score under both balanced and tilted weights.

Phase 5 - Executive Summary

The executive summary resolved that:

Repo link:

https://github.com/DanWangere/sql-ai-promptathon

Completion

• Yes, the agent completed the mission or goal.
• No, the agent did not complete the mission or goal.

Bonus work

My notebook kept on breaking and I realized it was because the agent was editing the raw .pynb JSON, the metadata patching, the json.dumps rewrites. This prompted me to put all the logic in a plain .py module ie, the connection helper, the SQL, the feature engineering functions, the embedding calls and the tool build. The agent edits it as a normal source file.

Prompt:
Create TWO files in the repo root:

• support_mart.py this will be the engine module all real logic lives here, edit as a normal source file.
• support_intelligence_mart.ipynb this will be a notebook that imports the module and narrates.

The notebook thus serves as a presentation layer that imports the module and calls it section by section.
Each section ends with a quality gate that asserts its load bearing facts and prints PASS so the notebook proves itself on a clean run.

The embedding model behind the seeded vectors was unrecorded anywhere, sys.external_models was empty and the seed file holds only raw numbers. This mattered because a query embedded by the wrong model produces meaningless distances silently ie the tool still returns results, they are just near-random. vector(1536) narrowed the candidates but did not identify one, so I proved it by re-embedding a known document and comparing to its stored vector. A French review which also confirms non-English text embeds into the same space matched at cosine 1.000000 with text-embedding-3-large @ 1536 dims using Title + newline + Body. Body alone scored 0.957, it was convincing enough to accept, and wrong enough to have quietly broken every result.
I then registered that confirmed model with CREATE EXTERNAL MODEL and repeated the test inside SQL Server, AI_GENERATE_EMBEDDINGS reproduced the same document's stored vector to a cosine distance of 0.0000005. Effectively zero, proving the in-database path lands in exactly the same vector space as the seeded data, which is what makes the tool provably correct rather than merely functional.

The reported work is in support_mart.py and support_intelligence_mart.ipynb at the linked repository; start by reading the module and notebook’s section-by-section quality gates. Run the notebook against the SQL Server 2025 dev container and inspect the embedding validation and MCP-tool sections. Done is the completed mission with all quality gates passing and the documented vector-model check reproduced.