docs/dataset_first_agent_handoff.md

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:

research -> LLM-generated DDL -> LegitData populates columns -> repair pass -> ThoughtSpot

Target flow:

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:

source .venv/bin/activate
python app.py

The authenticated app runs on:

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:

a6fbe9b fix: improve dataset-first scenario routing

The runtime path is active when:

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:

source .venv/bin/activate
python app.py

Latest full quality baseline on test:

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:

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:

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:

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:

demoprep_app/
  controllers/
  pipeline/
  research/
  scenario/
  dataset/
  ddl/
  integrations/

Key files:

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:

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:

15 verticals
71 lines/sub-industries
6 functions
426 line × function cells

Do not build 426 handcrafted generators.

The intended design is:

426 matrix cells -> roughly 12-18 scenario families -> generic contract-driven generator

Scenario Families

The desired scenario family list currently lives in:

demoprep_app/scenario/families.py

The current planned families are:

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:

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:

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:

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:

chat_interface.py

Current intended switch:

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:

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:

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:

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:

git diff --check

Run local route/cardinality smoke checks:

python tests/test_dataset_first_builders.py

Run the targeted Datadog E2E test against test:

python tests/e2e_quality.py --datadog-saas-sales --env-name test

Run the five-case dataset-first E2E suite against test:

python tests/e2e_quality.py --dataset-first-five --env-name test

Shortcut wrapper:

tests/run_test_quality.sh datadog
tests/run_test_quality.sh five

Test Deploy Command

Project convention:

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:

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.