TS Object Model Coach

ThoughtSpot: Coach a Model (Comprehensive Spotter Preparation)

Spotter accuracy depends on five distinct coaching surfaces working together. Most teams curate them ad-hoc, in isolation, and let them drift over time. This skill produces all five from the same evidence base — Model schema, dependent Liveboards/Answers, analyst prose, and (optionally) Snowflake query history — and reviews any existing content critically rather than blindly adding more.

The five Spotter coaching surfaces:

Research-backed defaults:

• Reference Question target: ~15 to start (Snowflake's "10–20 covering common questions").
• Mix simple aggregations with complex joins/ratios — "Simple queries may not have as much useful information" (Cortex optimization).
• Existing AI assets are critiqued + improved, never silently overwritten.
• The user picks which surfaces to generate via a single up-front menu.

Ask one question at a time for dependent decisions. Batch independent questions into a single prompt to cut round-trips.

References

Prerequisites

• ThoughtSpot profile configured — run /ts-profile-thoughtspot if not
• Snowflake profile configured (optional, only for query-history mining) — /ts-profile-snowflake
• ts CLI installed: pip install -e tools/ts-cli
• Python: pip install pyyaml
• ThoughtSpot user must have MODIFY or FULL access on the target Model

Step 0 — Overview

On skill invocation, display:

ts-object-model-coach — comprehensively prepare a Model for Spotter: review existing AI context/synonyms/description, mine dependent objects + (optionally) Snowflake history, then generate your chosen mix of column AI Context, Synonyms, Reference Questions, Business Terms, and a Data Model Instructions draft.

Steps:

1. Authenticate and pick the Model ........................ you choose
2. Export Model TML; extract schema and existing AI assets . auto
3. Mine candidate sources (Liveboards/Answers, prose, Snowflake) . auto
4. Review existing AI assets — produce critique + deltas .. auto 4.5 Cross-Model consistency scan — flag column-name collisions across other Models . auto
5. Show critique; pick which surfaces to generate ......... you confirm
6. Generate proposals per selected surface ................. auto
7. Per-surface review with explainer blocks (purpose / signals / outcome rules) . you confirm
8. Build merged TML, backup, final import gate ............. you confirm
9. Import + smoke test ..................................... auto

Confirmation required: Steps 1, 5, 7, 8 Auto-executed: Steps 2, 3, 4, 4.5, 6, 9

Ready to start? [Y / N]

Do not begin Step 1 until the user confirms.

Step 1 — Authenticate and Pick the Model

Read ~/.claude/thoughtspot-profiles.json. Prompt for profile if multiple exist; confirm the single profile if exactly one.

ts auth whoami --profile "{profile_name}"

Save {base_url} (strip trailing slash) and {profile_name}.

Pick the Model — accept --guid or prompt to search:

ts metadata search \
  --subtype WORKSHEET --name "%{search_term}%" --profile "{profile_name}"

Mark each result [MODEL] or [WORKSHEET] using metadata_header.contentUpgradeId / worksheetVersion (same logic as ts-object-answer-promote Step 5). This skill targets Models only — if a Worksheet is selected, tell the user it must be upgraded to a Model first (no ThoughtSpot skill for this exists yet — a ts-object-model-builder skill is planned but not shipped) and stop.

Display format. Show results as a markdown table with columns # | Name | Owner | GUID | Modified. The Owner column is the metadata_header.authorDisplayName (fall back to authorName if the display name is absent). On shared instances, name collisions across authors are common; without the Owner column the user often picks the wrong object.

Save {model_guid} and {model_name}.

Optional Snowflake profile. Ask:

Mine the underlying Snowflake query history for real-world question patterns? (Y / N)
(requires a Snowflake profile with ACCOUNT_USAGE access; defaults to N)

If Y, prompt for Snowflake profile name and save as {sf_profile_name}.

Create the run directory:

import time, pathlib
run_dir = pathlib.Path.home() / "Dev" / "coaching-runs" / f"{slug(model_name)}-{int(time.time())}"
run_dir.mkdir(parents=True, exist_ok=True)

Step 2 — Export Model TML; Extract Schema and Existing AI Assets

Export the Model bundle:

ts tml export {model_guid} \
  --profile "{profile_name}" --fqn --associated --parse > {run_dir}/model_bundle.json

Parse and extract two structured outputs:

2a. Schema (drives candidate generation in Steps 3 + 6)

import json
data = json.loads(open(run_dir/"model_bundle.json").read())

model = next(i["tml"]["model"] for i in data if i["type"] == "model")
columns = model.get("columns", [])
formulas = model.get("formulas", [])

# Classify columns. The Model TML may not populate properties.data_type for every
# column — fall back to NAME PATTERN for date detection (e.g. "Transaction Date",
# "Order Date" → date dim even when data_type is None).
import re
DATE_NAME_RE = re.compile(r'\b(date|datetime|timestamp|time|day|month|quarter|year)\b', re.I)
measures   = [c for c in columns if c.get("properties",{}).get("column_type") == "MEASURE"]
attributes = [c for c in columns if c.get("properties",{}).get("column_type") == "ATTRIBUTE"]
date_dims  = [c for c in attributes if DATE_NAME_RE.search(c["name"]) or c.get("properties",{}).get("data_type") in ("DATE","DATE_TIME","TIMESTAMP")]
non_date_attrs = [c for c in attributes if c not in date_dims]

Joins live at the physical-table level (table_tml.table.joins_with), not on model.model_tables[].joins_with (which is empty in most exports). Aggregate them:

table_items = [i for i in data if i["type"] == "table"]
joins = []
for t in table_items:
    for j in t["tml"]["table"].get("joins_with", []):
        joins.append({
            "from_table": t["tml"]["table"]["name"],
            "to_table":   j.get("destination", {}).get("name"),
            "type":       j.get("type"),
        })
# A dim is a "join key" (D_join in scoring) when its table is a join target for ≥2 tables.
join_target_counts = {}
for j in joins:
    join_target_counts[j["to_table"]] = join_target_counts.get(j["to_table"], 0) + 1

2b. Existing AI assets (drives the critique in Step 4)

Pull two categories of existing assets:

1. Model TML assets (model.description, columns[].properties.ai_context, columns[].properties.synonyms[]) — read directly from the bundle parsed in 2a.
2. Existing feedback entries — NOT in the bundle. ts tml export --associated does not surface nls_feedback (verified). ts tml export --type FEEDBACK is also not supported — the API returns HTTP 400 when a model GUID is passed with type=FEEDBACK (verified 2026-05-11). The correct approach is to locate the feedback object's own GUID via ts metadata dependents, then export it directly:

import json, subprocess

# Step 1: find the feedback object GUID via dependents
dep_result = subprocess.run(
    ["bash", "-c",
     f"ts metadata dependents {model_guid} --raw --profile '{profile_name}'"],
    capture_output=True, text=True,
)
dep_body = json.loads(dep_result.stdout) if dep_result.stdout.strip() else []
deps_node = (dep_body[0].get("dependent_objects", {})
                        .get("dependents", {})
                        .get(model_guid, {})) if dep_body else {}
feedback_deps = deps_node.get("FEEDBACK", []) or []
feedback_guids = [d.get("id") or d.get("metadata_id") for d in feedback_deps
                  if d.get("id") or d.get("metadata_id")]

# Step 2: export each feedback GUID (no --type flag needed)
all_fb_entries = []
for fb_guid in feedback_guids:
    fb_result = subprocess.run(
        ["bash", "-c",
         f"ts tml export {fb_guid} --parse --profile '{profile_name}'"],
        capture_output=True, text=True,
    )
    fb_body = json.loads(fb_result.stdout) if fb_result.stdout.strip() else []
    if fb_body:
        fb_payload = fb_body[0].get("tml", {})
        entries = fb_payload.get("nls_feedback", {}).get("feedback", []) or []
        all_fb_entries.extend(entries)

# Full content available: search_tokens, formula_info, access, chart_type,
# display_mode, parent_question, rating, axis_config, etc.
fb_global = [e for e in all_fb_entries if e.get("access") == "GLOBAL"]
fb_user   = [e for e in all_fb_entries if e.get("access") != "GLOBAL"]

If no FEEDBACK dependents are found, all_fb_entries will be empty — proceed normally with zero feedback entries (the Step 5 critique will note this and suggest generating new content).

existing = {
    "model_description": model.get("description", "").strip(),
    "spotter_enabled":   model.get("properties",{}).get("spotter_config",{}).get("is_spotter_enabled", False),
    "columns_with_ai_context": [(c["name"], c["properties"]["ai_context"])
                                 for c in columns if c.get("properties",{}).get("ai_context")],
    "columns_with_synonyms":   [(c["name"], c.get("properties",{}).get("synonyms", []),
                                 c.get("properties",{}).get("synonym_type", ""))
                                 for c in columns if c.get("properties",{}).get("synonyms")],
    "existing_feedback_global": fb_global,
    "existing_feedback_user":   fb_user,
}
existing_entries = fb_global + fb_user   # flat list — used by Steps 8a, 8c, 9c

Persist both to {run_dir}/schema.json and {run_dir}/existing_assets.json.

Step 3 — Mine Candidate Sources

Three sub-steps — run all that apply.

3a. Dependent Liveboards/Answers via verified v2 API

Dependent Liveboards/Answers are not a flag on ts metadata search — use ts metadata dependents, the verified API contract documented in this skill's open-items.md #1 (independently verified for the ts-dependency-manager skill on the wip/ts-dependency-manager branch) — fast (~2s), VERIFIED on Cloud:

import json, subprocess

result = subprocess.run(
    ["bash", "-c",
     f"ts metadata dependents {model_guid} --raw --profile '{profile_name}'"],
    capture_output=True, text=True,
)
body = json.loads(result.stdout)
deps_node  = body[0].get("dependent_objects",{}).get("dependents",{}).get(model_guid, {})
answers    = deps_node.get("QUESTION_ANSWER_BOOK", []) or []
liveboards = deps_node.get("PINBOARD_ANSWER_BOOK", []) or []

Then export each dependent's TML and harvest:

• answer.search_query (tokenised — feeds taxonomy ranking)
• answer.name, answer.description, answer.dynamic_name, answer.dynamic_description
• For Liveboards: liveboard.name, liveboard.description, each liveboard.visualizations[].answer.name and per-tile description (these are the highest signal — analysts label tiles in business language)

Save all mined prose to {run_dir}/mined_prose.json and the search_queries to {run_dir}/mined_searches.json. The two have separate downstream consumers.

3b. Snowflake query history (optional)

If {sf_profile_name} is set AND the Model is Snowflake-backed (check table_items[].tml.table.connection.type == "SNOWFLAKE"):

Use QUERY_PARAMETERIZED_HASH to group queries by structure (different literals collapse to the same hash). Filter out internal ThoughtSpot tasks (SAGE_INDEXING, SAGE_SAMPLING, A3*) by filtering on the comment block:

WITH ranked AS (
  SELECT QUERY_PARAMETERIZED_HASH,
         ANY_VALUE(QUERY_TEXT) AS sample_query,
         COUNT(*) AS run_count
  FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
  WHERE QUERY_TEXT ILIKE '%{db}.{schema}.DM_%'
    AND START_TIME >= DATEADD('day', -90, CURRENT_TIMESTAMP())
    AND EXECUTION_STATUS = 'SUCCESS' AND QUERY_TYPE = 'SELECT'
    AND USER_NAME NOT LIKE 'ETL_%'
    AND CONTAINS(UPPER(QUERY_TEXT), 'GROUP BY')
    AND NOT QUERY_TEXT ILIKE '%task: SAGE_%'
    AND NOT QUERY_TEXT ILIKE '%task: A3%'
  GROUP BY QUERY_PARAMETERIZED_HASH
)
SELECT * FROM ranked WHERE run_count >= 2 ORDER BY run_count DESC LIMIT 30;

Real-world finding: demo / TS-fronted Snowflake accounts often have zero useful patterns — the workload is dominated by ThoughtSpot's own indexing. If the result is empty, log it and proceed with mined prose + schema only. Don't error.

3c. Prose mining

Per prose-mining-rules.md: extract noun phrases from all mined prose (model description, Answer/Liveboard names + descriptions, tile names), match them against Model column display names with stem overlap, and emit two outputs:

• Synonym candidates per column — phrases that look like alternative names ("inventory levels" ↔ [Inventory Balance])
• Question seeds — full sentences that read like business questions ("growth in sales amounts" → seed a YoY question with that phrasing)

Save to {run_dir}/mined_prose_extract.json.

3d. Existing GLOBAL feedback as input signal

Already-curated access: GLOBAL Reference Questions and Business Terms on the Model are the highest-quality input available — an analyst has explicitly promoted them for shared use. They feed:

• Paraphrase variant generation (Step 6.3) — existing feedback_phrase strings are pre-validated NL phrasings; reuse them as variants for any new question with matching search_tokens.
• Synonym proposal validation (Step 6.2) — existing BUSINESS_TERM entries show which phrase→column mappings the team has already accepted; don't propose competitors.
• Ranking (per question-taxonomy.md) — patterns that share search_tokens shape with an existing GLOBAL entry get +4 (signals an important measure×dim combination); identical feedback_phrase matches drop the candidate and mark the existing entry as KEEP.

access: USER entries are excluded from input signal by default — they're private and may be unreviewed. The user opts them in via the Step 5 scope menu prompt (see ai-asset-review-rules.md §4a).

# Already extracted in Step 2b — just compile the per-pattern lookup
import re
def tokens_of(s):
    return set(re.findall(r'\[([^\]]+)\]', s.lower()))

global_token_shapes = [tokens_of(e["search_tokens"]) for e in existing_feedback_global
                        if e.get("type") == "REFERENCE_QUESTION"]
global_phrase_to_col = {e["feedback_phrase"].lower(): tokens_of(e["search_tokens"])
                        for e in existing_feedback_global
                        if e.get("type") == "BUSINESS_TERM"}

Stash both into {run_dir}/feedback_signal.json for Step 4 (review) and Step 6 (generation).

Step 4 — Review Existing AI Assets — Produce Critique + Deltas

For each existing asset, score it against mined evidence and produce a delta proposal. Full heuristics in ai-asset-review-rules.md. Summary:

Per ai-asset-review-rules.md §4:

• GLOBAL feedback is treated as authoritative — preserved, used as input signal for Step 6, never silently overwritten
• USER feedback is gated behind the Step 5 opt-in. Default: skip entirely; not used as signal, not modified
• Stale references (entries pointing to columns/formulas no longer on the Model) get FLAG_FOR_HUMAN so the user can decide whether to keep, edit, or remove via UI

Existing values are never silently overwritten — the user must explicitly accept each REFINE or REWRITE in Step 7. Save the critique to {run_dir}/existing_review.json.

Step 4.5 — Cross-Model Consistency Scan

Spotter doesn't disambiguate between same-named columns across Models the user can reach. The same query may return different numbers depending on which Model the user happens to hit — the central failure mode in enterprise text-to-SQL (Axius, "The 7-Table Fallacy", 2026). Full implementation rules in cross-model-consistency.md. Summary:

For each column in this Model, search all Models the user can read in the org and compare on:

1. db_column_name — different warehouse source ⇒ almost always different meaning
2. column_type — measure vs attribute mismatch
3. aggregation — sum vs avg = different semantics
4. Formula expression — for formula columns, does the math agree?
5. ai_context text — substring conflict heuristic

Pre-scan gate

The scan is the only step that scales with tenant size, not target size. Show the user the cost estimate and let them choose the scope before any work starts:

Step 4.5 — Cross-Model Consistency Scan

Found {N_models} readable Models on this profile.

First-run scan exports TML for each Model in parallel (4-way concurrent),
cached locally on (guid, modified_time). Subsequent runs only re-export
Models that have been modified since the last run.

  Estimated time:  ~{est_seconds // 60} minute(s) first run, seconds thereafter
  Cache location:  ~/.cache/ts-object-model-coach/tml-corpus/

Proceed?
  [Y]              run full scan (default)
  [filter <name>]  scope-by-name LIKE pattern (e.g., "filter Dunder")
  [N]              skip Step 4.5 entirely

Choose:

Time estimate: assume ~1.5s per uncached Model export at 4-way parallel (N / 4 * 1.5s). A scan of 343 Models lands at ~2 min wall time first run.

If the user picks filter <name>, re-run the metadata search with --name "%<name>%" and recompute the count. Skip option (N) writes an empty cross_model_consistency.md and proceeds to Step 5 normally.

Implementation

import json, pathlib, subprocess, time
from concurrent.futures import ThreadPoolExecutor, as_completed

# 1. Enumerate readable Models (--all auto-paginates; default page size is 50).
res = subprocess.check_output([
    "ts", "metadata", "search",
    "--subtype", "WORKSHEET",
    "--all",
    "--profile", profile_name,
])
all_models = [r for r in json.loads(res)
              if r["metadata_header"].get("contentUpgradeId") != "WORKSHEET_TO_MODEL_UPGRADE"
              and r["metadata_header"].get("worksheetVersion") != "V1"
              and r["metadata_id"] != model_guid]

# 2. Resolve cache dirs and load FORBIDDEN cache (24h TTL).
cache_dir = pathlib.Path.home() / ".cache" / "ts-object-model-coach" / "tml-corpus"
cache_dir.mkdir(parents=True, exist_ok=True)
forbidden_cache_path = cache_dir.parent / "forbidden.json"
forbidden_cache = {}
if forbidden_cache_path.exists():
    raw = json.loads(forbidden_cache_path.read_text())
    cutoff_ms = (time.time() - 24 * 3600) * 1000
    forbidden_cache = {g: e for g, e in raw.items() if e.get("ts_ms", 0) > cutoff_ms}

# 3. Split into "hit cache", "skip — known-FORBIDDEN", and "needs export".
to_export, corpus = [], []
for m in all_models:
    if m["metadata_id"] in forbidden_cache:
        continue  # silently skip — user can clear ~/.cache/ts-object-model-coach/forbidden.json to retry
    cache_key = f"{m['metadata_id']}-{m['metadata_header']['modified']}.json"
    cache_path = cache_dir / cache_key
    if cache_path.exists():
        corpus.append(json.loads(cache_path.read_text()))
    else:
        # Evict stale entries for this guid before re-exporting
        for old in cache_dir.glob(f"{m['metadata_id']}-*.json"):
            old.unlink()
        to_export.append((m, cache_path))

# 4. Parallel export with progress reporting.
def _export_one(m, cache_path):
    try:
        out = subprocess.check_output(
            ["ts", "tml", "export", m["metadata_id"],
             "--profile", profile_name, "--fqn", "--parse"],
            stderr=subprocess.PIPE, timeout=60,
        )
        cache_path.write_bytes(out)
        return ("ok", m, json.loads(out))
    except subprocess.CalledProcessError as e:
        err = e.stderr.decode("utf-8", "replace")
        if "FORBIDDEN" in err or "UNAUTHORIZED" in err:
            return ("forbidden", m, err[:200])
        return ("error", m, err[:200])
    except Exception as e:
        return ("error", m, str(e)[:200])

if to_export:
    print(f"  Exporting {len(to_export)} Model(s) — {len(corpus)} cached, "
          f"{len(forbidden_cache)} skipped (known FORBIDDEN).")
    completed = 0
    with ThreadPoolExecutor(max_workers=4) as executor:
        futures = {executor.submit(_export_one, m, p): m for m, p in to_export}
        for future in as_completed(futures):
            completed += 1
            status, m, payload = future.result()
            if status == "ok":
                corpus.append(payload)
            elif status == "forbidden":
                forbidden_cache[m["metadata_id"]] = {
                    "ts_ms": int(time.time() * 1000),
                    "name": m["metadata_header"]["name"],
                    "error": payload,
                }
            # ("error", ...) — log and skip; will retry next run
            if completed % 25 == 0 or completed == len(to_export):
                print(f"  Exporting {completed}/{len(to_export)}...")

# 5. Persist FORBIDDEN cache for the next run.
forbidden_cache_path.write_text(json.dumps(forbidden_cache, indent=2))

Build the column-name → collisions index, run the divergence checks per cross-model-consistency.md, and write {run_dir}/cross_model_consistency.md with the explainer block from review-explainers.md Block 6 prepended.

Until the heuristic is calibrated against a live tenant (open-items.md #15), default the proposed RouteAction to NEEDS_REVIEW for every collision — let the user pick.

The scan output is referenced from the Step 5 critique summary; users can defer review by picking 0 from the surface menu or skip it via the cross-Model checkbox.

Performance notes.

• max_workers=4 is chosen to be polite to the API while delivering ~4x speedup. The endpoint tolerates higher concurrency, but 4 keeps the run well under any sensible rate limit even on smaller TS instances.
• FORBIDDEN cache TTL is 24 h to handle daily permission changes without re-paying the discovery cost on every run. Clear ~/.cache/ts-object-model-coach/forbidden.json to force a re-check.
• Successful TML caches do not expire — they're keyed on modified_time, so a Model edit naturally invalidates its entry. Stale entries for the same guid are evicted on miss.

Step 5 — Show Critique; Pick Which Surfaces to Generate

Display the critique summary, ask about USER feedback inclusion, then show the scope menu. Example output:

=== Existing AI assets on "Dunder Mifflin Sales & Inventory" ===

Model description:               Present (430 chars, AI-generated)
                                 Critique: KEEP — coverage is good
Column AI Context:               0 / 19 columns populated
Column Synonyms:                 0 / 19 columns populated
Existing feedback (GLOBAL):      0 entries        — used as input signal
Existing feedback (USER):        0 entries        — private to creator
Spotter enabled:                 ✅

Cross-Model consistency scan:
  Scanned 47 Models you can read.
  Of this Model's 19 columns, 5 have name collisions in other Models.
  Of those, 2 look genuinely divergent (different db_column_name or formula),
  3 are duplicates with identical definitions.
  → cross_model_consistency.md generated; review in Step 7.

If existing_feedback_user is non-empty, ask:

This Model has {N_user} access:USER feedback entries (private to their creators).
By default these are excluded from this run — not used as signal, not modified.

Include them as input signal? (y/N, default N)
   - Y: existing USER entries seed paraphrase variants and influence ranking
        (treated like GLOBAL for the duration of this run only)
   - N: ignore them entirely; they remain untouched on the Model

Choose:

Save the answer as include_user_feedback: bool in {run_dir}/scope.json.

Then show the surface menu:

What would you like to generate or improve? (tick all that apply)

  [ ] 1. Column AI Context           — propose a 2-3 sentence business description per column
  [ ] 2. Column Synonyms             — propose alternative names per column (parser-level)
  [ ] 3. Reference Questions         — full NL question → tokenised search mappings (target ~15)
  [ ] 4. Business Terms              — phrase → column mappings (coaching-layer alternative)
  [ ] 5. Data Model Instructions     — global rules draft (manual paste — TML location TBD)
  [ ] 6. Improve Model description   — only shown when Step 4 critique is REWRITE/EXPAND
  [ ] 7. Cross-Model consistency     — review same-named-column collisions in other Models
                                        (only shown when Step 4.5 found ≥ 1 collision)
  [ ] 8. Column metadata + hierarchies — cardinality, sample values, drill paths
                                          (requires Snowflake profile for samples)

Enter numbers (e.g. "1,3,5"), "all", or "0" to skip generation but still review #7:

If the user selects any of #2 (Column Synonyms), #3 (Reference Questions), or #4 (Business Terms) — i.e. any phrase-coaching surface — display the plain-English explainer from synonym-strategy-explainer.md verbatim, with all variables substituted from the user's data.

The explainer is informational, not a strategy choice — the skill auto-selects the right Method per phrase using the decision tree:

The user does NOT pick a global strategy ("A only" / "B only" / "Both"). Instead, during Step 7 review they can override per row — change KEEP to MOVE_TO_A, MOVE_TO_B, or MOVE_TO_C to route a phrase to a different Method. This per-row control is the right level of granularity; global strategy was wrong.

After displaying the explainer, ask:

Continue with Method A/B/C auto-selection (you can override per row in Step 7)?
(Y / N):

Save the surface selection set and the user's confirmation as {run_dir}/scope.json.

Step 6 — Generate Proposals Per Selected Surface

Per surface (only those selected in Step 5):

6.1 — Column AI Context (structured) + Column Description (prose)

ai_context is structured-only — closed enums and refs, never prose. The full spec is in ai-context-schema.md; concrete worked examples are in ai-context-examples.md. Step 6.1 generates two surfaces in parallel:

Bootstrapping measures

For each measure column, bootstrap the mandatory tier (additivity, time_basis, grain_keys) deterministically:

1. additivity — first-pass guess from aggregation:
   • SUM ⇒ additive unless mined evidence flags "snapshot" / "balance" / "closing" / "filled" → semi_additive candidate
   • MAX / MIN over a date column ⇒ semi_additive candidate
   • COUNT_DISTINCT and ratios/divisions ⇒ non_additive
2. For additivity: semi_additive — populate non_additive_dimension (the time-grain column the snapshot is anchored on). additive_dimensions is NOT an axis any more (removed 2026-04-29 — redundant with non_additive_dimension).
3. time_basis — for formula columns, infer from formulas[] query_groups({DATE_DIM.DATE})-style references. Never copy formula text into ai_context. Prefer the conformed shared date dim when one exists in the Model join graph. May be legitimately absent for time-agnostic measures.
4. grain_keys — list of column refs that uniquely identify one fact row. Derive from the table's primary key + the time_basis column.
5. source — conditional override only. Omit when column_id: TABLE::COL resolves cleanly via the table's fqn. Required when the column_id doesn't match the physical path (renamed/aliased columns, view-backed columns).

For optional axes on measures:

• unit — closed enum: currency / count / ratio / percentage / duration. Inferred from column name patterns and the underlying type.
• null_semantics — closed enum: zero / unknown / no_snapshot. For snapshot/balance measures, default to no_snapshot.

Bootstrapping dimensions

Dimensions get at most three axes in ai_context: source (conditional), null_semantics (when NULL has business meaning), and role (the primary dimensional axis).

The role axis prevents the Test 3 Q-010 failure mode (LLM picked category_id when the user asked for "category"). Combined with the system-prompt rule below, it also reinforces the prevention of phantom dimension tables (DM_CATEGORY etc.) when paired with the description prose.

Bootstrapping column.description (prose)

In parallel, generate column.description (prose, ≤ 200 chars) from:

• mined_prose_extract.ai_context_evidence_per_column (Step 3c)
• Existing column.description if present (preserve as primary signal)
• Mined Liveboard / Answer prose near the column reference

column.description is where business meaning, gotchas, and grain-in-words live. Do not duplicate this content into ai_context.

Generator system-prompt rule

Include this clause verbatim in any LLM prompt that consumes TML + ai_context (Spotter coaching prompts, third-party agent prompts, downstream SQL agents):

"This TML mixes ThoughtSpot DSL with metadata. Distinguish them when emitting SQL:

1. The formulas[] block contains ThoughtSpot formula DSL, not SQL. Functions like last_value(...), query_groups(...), growth_rate(...), cumulative_sum(...) and any other TS-specific formula functions are not SQL functions. Re-implement the formula's intent in target SQL from scratch — never copy or partially translate the DSL.
2. Square-bracket column refs ([Amount], [Stock Quantity]) and curly-brace dim refs ({DM_DATE_DIM.DATE}) are TS logical references, not SQL identifiers. Resolve each to its physical path via column_id: TABLE::COL plus the table's fqn (or via an explicit source: axis when present, which overrides column_id).
3. Column and table display names (e.g. Total Sales, Inventory Balance, Product Category) are TS logical names. They are never valid SQL identifiers — resolve them to physical paths the same way as bracket refs.
4. The ai_context block declares constraints on the column's result (additivity, time_basis, grain_keys, role). Respect them when writing SQL; do not infer constraints that are not declared. All prose context for a column lives in column.description, not ai_context."

Output and review

Propose each surface with a confidence score; flag low-confidence cases for explicit review in Step 7. Low-confidence drivers: mined evidence is sparse, additivity guess conflicts with mined evidence, no shared date dim detected for time_basis, or an id/label/code/key sibling pair lacks a clear role assignment.

6.2 — Column Synonyms

For each column, compile a candidate synonym list from:

• Mined prose phrases matching the column name (stem overlap ≥ 0.6)
• Common business shorthands inferred from the column's apparent role
• User-defined existing synonyms (preserved)

Reject phrases that are exact substrings/supersets of the display name (e.g. don't add "inventory" as a synonym of Inventory Balance).

Before writing proposals, run two cross-column validation checks across ALL columns (existing synonyms + proposed additions combined):

1. No synonym matches another column's display name. A synonym equal to another column's name causes Spotter to resolve the phrase to two different things depending on context. Flag every such entry as REMOVE in the review file with the note "synonym conflicts with column name [{other_col}]". This applies to existing synonyms too — surface them for removal even if they weren't proposed in this run.

2. No synonym appears on more than one column. Duplicate synonyms cause non-deterministic resolution. Flag every duplicate as REMOVE on the lower-priority column (keep it on the column whose display name is closest in meaning).

all_col_names_lower = {c["name"].lower(): c["name"] for c in m.get("columns", [])}

# Build merged map: synonym_lower → [col_name, ...] across existing + proposed
syn_to_cols = {}
for col in m.get("columns", []):
    all_syns = list(col.get("properties", {}).get("synonyms", []))
    proposed = synonym_deltas.get(col["name"], {}).get("proposed_additions", [])
    for syn in all_syns + proposed:
        syn_to_cols.setdefault(syn.lower(), []).append(col["name"])

synonym_errors = []
for col in m.get("columns", []):
    all_syns = list(col.get("properties", {}).get("synonyms", []))
    proposed = synonym_deltas.get(col["name"], {}).get("proposed_additions", [])
    for syn in all_syns + proposed:
        # Rule 1: synonym matches another column's display name
        match = all_col_names_lower.get(syn.lower())
        if match and match != col["name"]:
            synonym_errors.append(
                f"[{col['name']}] synonym '{syn}' conflicts with column name [{match}] — flag REMOVE"
            )
        # Rule 2: synonym appears on multiple columns
        owners = syn_to_cols.get(syn.lower(), [])
        if len(owners) > 1 and owners[0] != col["name"]:
            synonym_errors.append(
                f"[{col['name']}] synonym '{syn}' dup

…(truncated)