# Dataset-First Buildout Agent Handoff Last updated: 2026-05-13 ## Goal DemoPrep needs to move from DDL-first data generation to dataset-first generation for demo-critical quality. Old flow: ```text research -> LLM-generated DDL -> LegitData populates columns -> repair pass -> ThoughtSpot ``` Target flow: ```text research -> scenario contract -> coherent dataset -> derived DDL -> Snowflake -> ThoughtSpot ``` The user cares most about data quality. The current DDL-first + independent-column generation approach produces unrealistic demo data: broken funnels, impossible rates, wrong magnitudes, and metrics that do not reconcile. The dataset-first path should generate business facts from formulas and scenario logic first, then derive DDL afterward. ## Important Context The user does **not** want command-line feature flags as the normal operating model. They are okay with config / `.env` settings, but they should be able to run the normal app: ```bash source .venv/bin/activate python app.py ``` The authenticated app runs on: ```text http://localhost:7860 ``` Do not switch the app to `launch_chat.py` unless explicitly asked. `launch_chat.py` is the local unauthenticated dev launcher. ## Current Status Dataset-first is implemented and deployed to the Hugging Face test Space. Current test deploy commit: ```text a6fbe9b fix: improve dataset-first scenario routing ``` The runtime path is active when: ```text DEMOPREP_DATASET_FIRST=1 ``` This is expected to be configured in the environment or app settings, not typed as a one-off launch command. The normal local run command remains: ```bash source .venv/bin/activate python app.py ``` Latest full quality baseline on test: ```text Run: 2026-05-13 20:38 Command: python tests/e2e_quality.py Target: https://thoughtspot-dp-test-demoprep.hf.space Result: 8/8 pipeline complete Average: 78.9/100, Grade B ``` Per-case scores: ```text Apple Hardware Sales Data 62, LB 62, Total 71.5/C Starbucks Custom Store Ops Data 72, LB 68, Total 78.0/B Best Buy Consumer Electronics Mktg Data 76, LB 71, Total 80.8/B Nike Retail Sales Data 85, LB 63, Total 83.3/B Macy's Department Store Sales Data 82, LB 71, Total 83.8/B Walmart Grocery Sales Data 71, LB 66, Total 77.0/B Delta Custom Airline Ops Data 61, LB 78, Total 75.0/B Wells Fargo Banking Marketing Data 78, LB 70, Total 81.5/B ``` Important table-shape proof points from that run: ```text Starbucks: CHANNELS, DATES, PRODUCT_CATEGORIES, REGIONS, STORES, STORE_OPERATIONS Retail: DATES, PRODUCTS, SALES_FACT, STORES with 36 products and 36 stores Delta: AIRCRAFT, AIRLINE_ROUTES, AIRPORTS, CABIN_CLASSES, DATES, ROUTES Banking: BANKING_MARKETING, CAMPAIGNS, MONTHS, PRODUCTS, REGIONS, SEGMENTS ``` The previous Datadog SaaS Sales targeted confirmation was: ```text Data score: 76/100 Liveboard score: 62/100 Total: 78.5/B Tables: MONTHS, ACCOUNTS, PRODUCTS, SALES_REPS, SALES_PIPELINE Rows: 10,838 total ``` These table shapes prove the dataset-first path is running. The legacy path usually produced tables like `DATES`, `SALES`, `USERS`, and generic 500-row dimensions. ## Current Dataset-First Code New package: ```text demoprep_app/ controllers/ pipeline/ research/ scenario/ dataset/ ddl/ integrations/ ``` Key files: ```text demoprep_app/scenario/contract.py demoprep_app/scenario/extractor.py demoprep_app/scenario/families.py demoprep_app/dataset/contracts.py demoprep_app/dataset/generators/base.py demoprep_app/dataset/generators/router.py demoprep_app/dataset/generators/retail_sales.py demoprep_app/dataset/generators/saas_sales.py demoprep_app/dataset/generators/template_generator.py demoprep_app/ddl/from_dataset.py demoprep_app/pipeline/dataset_first.py demoprep_app/integrations/snowflake/dataset_writer.py docs/architecture.md ``` Current generators: ```text saas_sales specialized retail_sales specialized template_generator generic scenario-family generator ``` `saas_sales` is generic SaaS/software sales, not Datadog-specific. Datadog is only the current test case. ## Matrix Size The existing vertical/line/function matrix is in `demo_personas.py`. Current shape: ```text 15 verticals 71 lines/sub-industries 6 functions 426 line × function cells ``` Do **not** build 426 handcrafted generators. The intended design is: ```text 426 matrix cells -> roughly 12-18 scenario families -> generic contract-driven generator ``` ## Scenario Families The desired scenario family list currently lives in: ```text demoprep_app/scenario/families.py ``` The current planned families are: ```text sales_pipeline retail_sales ecommerce_marketing marketing_funnel subscription_revenue finance_unit_economics inventory_supply_chain manufacturing_operations logistics_route_operations airline_route_operations customer_service_support healthcare_operations banking_product_marketing portfolio_financials workforce_hr it_operations legal_matter_management education_enrollment ``` These are **scenario families**, not company-specific implementations. ## Implemented Fixes ### Dataset-First Runtime - Added dataset-first orchestration in `demoprep_app/pipeline/dataset_first.py`. - Added runtime activation in `chat_interface.py`. - The app now builds a dataset bundle first, derives Snowflake DDL from it, deploys that DDL, then loads the generated dataset into Snowflake. - The UI still labels the stage as `DDL creation`; in dataset-first mode this means "derive DDL from the generated dataset", not "ask the LLM to design schema first". ### DDL From Dataset - Added `demoprep_app/ddl/from_dataset.py`. - DDL now follows actual generated dataset columns and values. - Numeric key columns emit Snowflake-compatible integer types so ThoughtSpot does not reject keys as `DOUBLE`. ### Dataset Loading - Added `demoprep_app/integrations/snowflake/dataset_writer.py`. - Dataset-first tables are inserted directly into Snowflake. - The old LegitData population path is bypassed for dataset-first bundles. ### Scenario Routing - Added `demoprep_app/scenario/selector.py`. - Broad vertical / line / function / use-case text maps into scenario families. - Specialized overrides remain for SaaS sales and retail sales. - Generic fallback uses `TemplateDatasetGenerator`. - Added `demoprep_app/scenario/extractor.py`. - The app runtime now asks the selected LLM to extract a scenario contract from the custom prompt and research context before dataset generation. - The deterministic selector is now a fallback, not the intended intelligence layer for public/custom demos. Recent routing fixes in `a6fbe9b`: - Delta / Air Transport now routes to `airline_route_operations`. - Starbucks custom store/daypart operations now routes to `restaurant_store_operations`. - Banking marketing routes to `banking_product_marketing`. ### Generic Template Generator - Added `demoprep_app/dataset/generators/template_generator.py`. - Generates reusable scenario-family datasets with dimensions, time tables, formulas, trend/seasonality, and constraints. - Expanded dimension cardinality so demos do not look toy-sized: - major dimensions such as products, stores, accounts, campaigns, routes: about 36 rows - reps, suppliers, carriers, airports, aircraft, warehouses: about 24 rows - compact dimensions such as regions, channels, priorities, shifts: about 8 rows - Fixed dimension singularization bugs such as `CAMPUSE_KEY`, `SEVERITIE_KEY`, and `CABIN_CLASSE_KEY`. ### Restaurant / Store Operations - Added `restaurant_store_operations` family for Starbucks-style custom store operations. - Added `store_ops` metric profile: - transactions - refund transactions - average ticket - gross sales - discounts - refunds - net sales - labor hours - wait time - on-time orders/rate - customer satisfaction - realistic weather temperature - Enforces basic store operations logic: - refunds <= transactions - on-time orders <= transactions - rates stay 0-100 - net sales reconciles below gross sales ### Airline Operations - Delta / Air Transport now uses airline route operations tables instead of the generic CRM sales pipeline. - Local validation confirms: - passengers <= seats - mishandled bags <= bags handled - load factor stays 0-100 ### Test Harness Identity - `tests/e2e_quality.py` no longer guesses the Snowflake schema from date/prefix patterns. - The test reads the model/liveboard GUIDs from the final app page. - It exports the exact ThoughtSpot model TML. - It derives the Snowflake schema from that model. - If no model URL exists, the test refuses to guess. This fixes the recurring problem where a quality test graded the wrong schema. ### Contract / Run Logging Dataset-first DDL logs now include: - scenario type - fact grain - contract source and confidence - business domain - table names - extracted dimensions and measures Deploy logs now include schema name, table names, and row counts. ThoughtSpot logs now include schema, connection, model GUID, liveboard GUID, liveboard URL, tables, and success flag. This is necessary because a failed custom demo must be debuggable from logs without guessing by date or schema prefix. ## Desired Architecture Use three layers: 1. **Scenario contract** - Produced from research/use case. - Defines grain, dimensions, measures, formulas, constraints, ranges, seasonality, events, and dashboard questions. 2. **Generic contract-driven dataset engine** - Executes a scenario contract. - Generates dimensions and fact rows. - Computes formula-driven fields. - Enforces constraints. - Handles row count guidance as a guideline, not an exact hardcoded count. 3. **Specialized scenario generators** - Only for high-value scenarios where generic output is not good enough. - Existing examples: `retail_sales.py`, `saas_sales.py`. The next agent should prioritize layer 2 so we do not hand-write hundreds of generators. ## What The Generic Engine Does Now Implemented responsibilities: - Accepts `ScenarioContract` and scenario family definitions. - Generates standard dimension tables based on template dimensions. - Generates fact tables based on template fact grain. - Creates time dimensions automatically where needed. - Generates coherent measures from metric profiles. - Applies formula fields after base measures are generated. - Enforces constraints such as: - child funnel count <= parent funnel count - percentages between 0 and 100 - revenue = units × price where applicable - margin = revenue - cost - passengers <= seats - mishandled bags <= bags handled - resolved cases <= opened cases - Adds seasonality and trend shape at dataset level. - Treats row count as guidance: - Small: roughly 1k fact rows - Medium: roughly 10k fact rows - Larger scenario-specific counts should be allowed. ## Metric Profiles Reusable metric profiles are now the right expansion path rather than one-off generators: ```text pipeline digital_funnel sales_margin subscription finance inventory operations logistics airline support healthcare banking_funnel portfolio workforce it_ops legal enrollment ``` Each profile should define base fields, formulas, and constraints. Example `digital_funnel`: ```text IMPRESSIONS CLICKS <= IMPRESSIONS SESSIONS <= CLICKS or related channel traffic CONVERSIONS <= SESSIONS SPEND_USD CTR_PCT = CLICKS / IMPRESSIONS * 100 CONVERSION_RATE_PCT = CONVERSIONS / SESSIONS * 100 CPA_USD = SPEND_USD / CONVERSIONS ``` Example `subscription`: ```text STARTING_ARR_USD NEW_LOGO_ARR_USD EXPANSION_ARR_USD CONTRACTION_ARR_USD CHURNED_ARR_USD ENDING_ARR_USD = starting + new + expansion - contraction - churn MRR_USD = ending_arr / 12 NRR_PCT = (starting + expansion - contraction - churn) / starting * 100 ``` ## Runtime Wiring Dataset-first activation is in: ```text chat_interface.py ``` Current intended switch: ```python DEMOPREP_DATASET_FIRST=1 ``` This should be read from `.env` / config, not passed as a weird command each time. The user expects to run `python app.py`. Important: if you change runtime wiring, explain before removing it. Do not remove activation code without confirming. ## Current Debug Issues To Keep Separate ### ThoughtSpot Timeout One five-case test run had Datadog time out during ThoughtSpot with a `504 Gateway Timeout`. That does not prove dataset generation failed. In that run, the completed stages were research, DDL, and deploy. Treat this as a ThoughtSpot deployment/runtime timeout unless Snowflake data is also missing. ### Liveboard Quality Dataset scores improved more than liveboard scores. In the latest 8-run, data averaged about `73/100` while liveboards averaged about `69/100`. The next quality push should ground liveboard/Spotter prompts in the actual scenario family and generated schema, not just the broader use case text. The biggest remaining weak spots from the latest run: - Apple Hardware Sales routed to SaaS-style sales pipeline, which is structurally valid but not hardware-specific enough. - Delta custom airline operations improved to the airline route table shape, but data depth is still limited for reservations, loyalty, staffing, disruption recovery, and route economics. - Retail data quality is now strong, but liveboard quality lags data quality. - Starbucks custom improved materially, but liveboard and semantic context still need better scenario grounding. ### Session Test Tags The test harness often logs: ```text Exact test tag not found in session_logs ``` This is now diagnostic only. The test identity path does not depend on session tag matching anymore because it derives schema from the final model URL. ## Validation Commands Run these after changes: ```bash python -m py_compile \ chat_interface.py \ demoprep_app/pipeline/dataset_first.py \ demoprep_app/dataset/generators/router.py \ demoprep_app/dataset/generators/saas_sales.py \ demoprep_app/dataset/generators/retail_sales.py \ demoprep_app/dataset/generators/template_generator.py \ demoprep_app/scenario/selector.py ``` Smoke test SaaS sales: ```bash python - <<'PY' from demoprep_app.pipeline.dataset_first import build_dataset_first_demo b = build_dataset_first_demo( company_name="Datadog", company_url="https://datadog.com", use_case="Software as a Service Sales", vertical="Technology", function="Sales", row_count_guidance=1000, ) assert b is not None assert b.scenario.scenario_type == "saas_sales" assert "CREATE TABLE SALES_PIPELINE" in b.ddl rows = b.dataset.table_map()["SALES_PIPELINE"].rows assert rows assert all(0 <= r["PROBABILITY_PCT"] <= 100 for r in rows) assert all(abs(r["EXPECTED_ARR_USD"] - (r["PIPELINE_AMOUNT_USD"] * r["PROBABILITY_PCT"] / 100)) < 0.02 for r in rows) print(b.scenario.scenario_type, sum(len(t.rows) for t in b.dataset.tables)) PY ``` Run diff check: ```bash git diff --check ``` Run local route/cardinality smoke checks: ```bash python tests/test_dataset_first_builders.py ``` Run the targeted Datadog E2E test against test: ```bash python tests/e2e_quality.py --datadog-saas-sales --env-name test ``` Run the five-case dataset-first E2E suite against test: ```bash python tests/e2e_quality.py --dataset-first-five --env-name test ``` Shortcut wrapper: ```bash tests/run_test_quality.sh datadog tests/run_test_quality.sh five ``` ## Test Deploy Command Project convention: ```bash git push hf-test develop:main ``` Current local work has been done from `new-branch`, so the equivalent deploy command used during this sprint was: ```bash git push hf-test HEAD:main ``` Do not push `hf` production unless explicitly asked. ## Immediate Next Tasks 1. Promote the latest 8-run as the current baseline. - 8/8 completed on test. - Average is `78.9/B`. - Dataset-first table shapes are confirmed across retail, banking, Starbucks custom, and Delta custom. 2. Improve contract extraction quality. - The LLM contract extractor is now the public-facing path for custom demos. - Add contract validation gates so generic enterprise placeholders are rejected when customer/domain nouns exist in the prompt. - If a contract lacks concrete dimension values for a custom demo, fail early or ask for clarification instead of generating a generic dataset. 3. Improve liveboard quality. - Feed scenario family, fact table, dimensions, measures, formulas, and story questions into liveboard generation. - Avoid generic questions when the dataset is specialized. 4. Improve Apple / hardware routing. - Apple Hardware Sales currently lands in SaaS-style pipeline. - It needs a hardware/product sales scenario or a stronger retail/product-sales mapping. 5. Deepen airline operations. - Delta now routes correctly to airline route operations. - Add richer airline measures for reservations, loyalty, disruption, staffing, baggage, and route economics. 6. Add semantic enrichment. - The logs sometimes show `Semantics generation returned 0 columns`. - Model descriptions/synonyms should be generated from the dataset contract and generated schema. 7. Keep specialized generators only where they materially improve quality. - `retail_sales.py` and `saas_sales.py` can remain as higher-quality overrides. - Add specialized overrides only after generic template output fails a real demo-quality test. 8. Improve ThoughtSpot failure reporting. - Surface exact table import errors in the UI. - Save failed TML payloads somewhere predictable. - Do not mark the pipeline done when TS table creation fails. 9. Push source of record when ready. - `hf-test/main` has the latest test deploy. - `origin/develop` may lag local work until explicitly pushed. ## Communication Notes The user is moving fast and cares deeply about data quality. Be direct and precise. ## Slack Deployment Notifications Current Slack integration is intentionally outbound-only. - Purpose: notify an approved Slack channel when DemoPrep deployment starts, completes, or fails. - Runtime config: `SLACK_BOT_TOKEN` plus `SLACK_DEPLOYMENT_CHANNEL_ID` or `SLACK_CHANNEL_ID`. - Required Slack permission: `chat:write`. - Required Slack setup: install the app in the workspace and invite the bot to the target channel. - Not required for this phase: Socket Mode, app-level `xapp-` token, event subscriptions, app mentions, slash commands, or a public Slack request URL. Future idea, explicitly deferred: a Slack bot that receives messages or slash commands to trigger/check deployments. That would require a new security review because it changes the app from outbound notification sender to inbound Slack interaction handler. Do: - Explain whether a change affects the running server or only code on disk. - Confirm before removing broad runtime wiring. - Keep dataset-first and ThoughtSpot debugging separate. - Say clearly when a restart is required. Do not: - Add command-line env flag instructions as the normal run path. - Hardcode company-specific generators. - Say “complete” if ThoughtSpot failed to create tables/model/liveboard.