Mission: Support Intelligence Data Mart (Data Engineer)

Mission/open goal Description

Mission 1: Principal Data Engineer — Build Zava's Support Intelligence Data Mart.

Goal: turn the raw PromptathonDb tables (SalesOrders, SalesOrderLines, SupportTickets, SupportChats, Docs, Products, Customers, Employees) into a reusable, analyst-ready Support Intelligence Mart that answers: "Which products, categories, customers, clients, or channels show the strongest signals of revenue risk caused by support issues?"

Delivered as a single end-to-end executable Jupyter notebook covering all 8 required sections: database discovery, data quality checks, sales fact engineering, support fact engineering, chat transcript parsing, document enrichment, vector similarity features, and a transparent weighted risk scoring model, ending in a dynamically generated executive summary of the top risk clusters.

Environment note: the repo's devcontainer/Docker + SQL MCP server wasn't available locally, so I ran the notebook against a SQL Server 2025 instance using direct pyodbc/T-SQL calls that hit the exact same entities and the same dbo.FindSimilarDocsByDocId stored procedure the MCP server wraps (native VECTOR(1536) + VECTOR_DISTANCE('cosine', ...)). Functionally identical evidence, captured as SQL calls/results in notebook cells rather than literal MCP tool-call transcripts.

Harness and model

Claude Code (VS Code extension) with Claude Sonnet 5 -- direct pyodbc/T-SQL execution (same entities/stored proc as the SQL MCP server) against a SQL Server 2025 instance

Turn-by-turn journey

1. Prompt: Discover the PromptathonDb schema and confirm relationships between all 8 entities.
   Action: Queried INFORMATION_SCHEMA.COLUMNS across all tables and verified 9 FK-style relationships (Orders<->Lines<->Products/Customers/Employees<->Tickets<->Chats<->Docs).
   Result: 0 unmatched rows on every relationship check.

2. Prompt: Run data quality checks (nulls, dupes, orphans, malformed JSON) and produce a summary.
   Action: Checked null/duplicate PKs (0), orphan rows (0), and MessagesJson/TagsJson validity (0 malformed). Classified the 1,625 of 1,800 orders with a null CustomerId into B2B/Retail walk-in/Guest-online segments instead of dropping them.
   Result: qa_summary report with zero blocking issues.

3. Prompt: Engineer a clean sales fact table at order-line grain.
   Action: Built sales_fact with buyer_segment() (B2B / Retail walk-in / Guest online / Registered B2C) and an IsCancelled flag.
   Result: Analyst-ready sales fact table.

4. Prompt: Engineer a clean support fact table at ticket grain.
   Action: Built support_fact with a severity map (Critical=4..Low=1), IsUnresolved and IsLowSatisfaction flags, joined to customer/employee context.
   Result: Support fact table ready to join to sales.

5. Prompt: Parse SupportChats.MessagesJson into structured features.
   Action: Extracted message counts, customer vs agent turns, and matched complaint/resolution phrase lists.
   Result: chat_features table.

6. Prompt: Classify and enrich Docs.
   Action: Parsed TagsJson into rating/language/scenario/resolution tags, derived IsNegative, and linked docs back to tickets/orders/customers.
   Result: Enriched, linkable Docs table.

7. Prompt: Generate vector similarity features for high-risk docs.
   Action: Ran dbo.FindSimilarDocsByDocId against negative-rated docs; derived SIMILARITY_THRESHOLD data-drivenly as the 25th percentile of observed cosine distances (0.237; mean 0.285, std 0.058 across 70 comparisons) and rolled results into SimilarComplaintCount, RepeatedSKUCount, RepeatedScenarioCount, ClusterStrength per SKU.
   Result: sku_similarity_features table.

8. Prompt: Build a transparent weighted risk scorecard and executive summary.
   Action: Combined 7 min-max-normalized signals (revenue at stake, ticket volume, priority severity, unresolved rate, low satisfaction, negative docs, vector complaint density) into product_risk_scorecard, rolled up to category/channel/segment risk, and generated a dynamic markdown executive summary.
   Result: Top risk SKU: ZCPTM-SS-M-BW (Premium) -- score 0.85, $19,380 revenue across 223 units, 9 tickets (all low-satisfaction), 6 negative docs, cluster strength 0.65. #2 ZCETM-LS-L-BO (Elite, 0.20). #3 ZCPTW-SS-L-BO (Premium, 0.20).

Artifact: https://github.com/ShimonTheDBA/sql-ai-promptathon/blob/mission1-support-intelligence-mart/notebooks/support_intelligence_mart.ipynb (fully executed, outputs saved)

Architecture/data flow:
PromptathonDb (Orders+Lines, Tickets, Chats, Docs, Products, Customers, Employees) -> discovery+QA -> sales_fact + support_fact -> chat_features + doc enrichment -> dbo.FindSimilarDocsByDocId vector search -> similarity_features -> weighted risk scoring -> product/category/channel/segment scorecards -> executive summary

Completion

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

Bonus work

• Ran against a real SQL Server 2025 instance to exercise the native VECTOR(1536) type and VECTOR_DISTANCE('cosine', ...) end-to-end via dbo.FindSimilarDocsByDocId, rather than mocking vector search.
• No hardcoded thresholds: SIMILARITY_THRESHOLD was derived live from the observed cosine-distance distribution (25th percentile) instead of guessed.
• Notebook is fully re-runnable end-to-end (jupyter nbconvert --execute) with zero errors, and the executive summary is generated dynamically from live query results, not hand-written.
• Verified referential integrity across all 9 relationships and JSON validity across 1,800+ chat/doc rows before building any downstream feature

Open notebooks/support_intelligence_mart.ipynb and review the executed cells covering discovery, QA, fact engineering, chat and document enrichment, vector similarity, and risk scoring. Run it end to end with jupyter nbconvert --execute and verify the generated scorecards and executive summary use the PromptathonDb entities and dbo.FindSimilarDocsByDocId without errors.