TS Convert From Snowflake Sv

Snowflake Semantic View → ThoughtSpot Model

Converts a Snowflake Semantic View into a ThoughtSpot Model. Reads the semantic view DDL via GET_DDL, then uses three deterministic CLI commands — ts snowflake parse-sv (DDL → structured JSON), ts snowflake translate-formulas (SQL → ThoughtSpot formulas), and ts snowflake build-model (JSON → Model TML + import) — to map tables, relationships, dimensions, and metrics to ThoughtSpot TML.

Two scenarios are supported:

• Scenario A (existing tables): ThoughtSpot Table objects already exist for the Snowflake objects the semantic view references. Reuses those existing Table objects.
• Scenario B (new tables): No ThoughtSpot Table objects exist yet for the Snowflake objects the semantic view references. Creates new Table objects pointing to those objects.

Ask one question at a time for dependent decisions (each answer narrows the next — target database, then schema, then table). Batch independent questions when possible — e.g. connection name + target database + schema can be collected together (BL-074).

References

Concept Mapping

Full DDL-construct → ThoughtSpot-Model mapping table — tables, dimensions, metrics, non-additive/window-function formulas, relationships, synonyms, comments, and the unmapped extension clause: references/concept-mapping.md.

Key structural rules:

• column_id must use the column name from the ThoughtSpot Table TML. Export Table TMLs to confirm — do not assume they match the semantic view left-hand side.
• Simple metrics (AGG(view.col) — one column, one aggregate) → MEASURE column. Complex expressions → formulas[] entry.
• Unqualified derived metrics (NAME as m1 / m2, no table prefix on the left) → formulas[] MEASURE. This is the only SV construct that can combine metrics from two unrelated facts, so cross-fact ratios — attainment, period-over-period growth — arrive this way.
• In Scenario A, referencing_join points to a join pre-defined at the ThoughtSpot Table object level (found by exporting the FROM table's TML).
• In Scenario B / hybrid, inline joins[] on the FROM table entry (requires with field).

Prerequisites

ThoughtSpot

• ThoughtSpot Cloud instance, REST API v2 enabled
• User account with DATAMANAGEMENT or DEVELOPER privilege — only required for import
• Authentication configured — run /ts-profile-thoughtspot if you haven't already
• The ts CLI installed (pip install -e /path/to/tools/ts-cli)

No ThoughtSpot import access? You can still run this skill in file-only mode — it generates the Table and Model TML files for you to import manually. Select FILE at the Step 10 checkpoint or say "file only" at any point before Step 11.

Snowflake

• Role with USAGE on the database and schema containing the semantic view
• Connection configured — run /ts-profile-snowflake if you haven't already
• For Scenario B: role with CREATE TABLE or connection modification rights
• ts snowflake introspect needs the Snowflake connector in the ts environment. If it reports snowflake-connector-python is required, install it into the tool env: uv tool install thoughtspot-cli --with snowflake-connector-python

Step 0 — Overview

On skill invocation, display this plan before doing any work:

ts-convert-from-snowflake-sv — convert a Snowflake Semantic View into a ThoughtSpot Model, translating tables, joins, and SQL expressions.

Steps:

1. Authenticate (ThoughtSpot + Snowflake) ............. auto 1.5. Choose session mode (A: single / B: merge / C: update) . you choose
2. Identify the semantic view ......................... you choose
3. Get the semantic view DDL .......................... auto
4. Parse the DDL ..................................... auto (ts snowflake parse-sv)
5. Table registration question (reuse or create) ...... you choose
6. Discover / create ThoughtSpot Table objects ........ auto (may ask for clarification) 6D. Apply SV table descriptions to TS Table TMLs ....... auto (when SV has table comments)
7. Find join names (Scenario A) ...................... auto 7.5. Role-played dimension aliases (if any) ............ you choose
8. Assemble tables map ............................... auto 8.5. Display-name collisions (if any) .................. you choose
9. Translate SQL expressions → ThoughtSpot formulas ... auto (ts snowflake translate-formulas) 9.5. Confirm Spotter enablement (default: enabled) ...... you choose
10. Review checkpoint — inspect TML before import ...... you confirm
11. Import the model into ThoughtSpot .................. auto (ts snowflake build-model) 11c. Reconcile the Model against the SV ................. auto
12. Verify import and produce summary report ........... auto 12.5. Import verified queries as NLS Feedback ............ auto (when SV has verified queries)

File-only mode: at Step 10, choose FILE to write TML files for manual import.

Confirmation required: Steps 1.5, 5, 7.5 + 8.5 (if applicable), 9.5, 10 (Modes A/B); Steps 1.5, C4 (Mode C) Auto-executed: all others

Ready to start? [Y / N]

Do not begin Step 1 until the user confirms.

Workflow

Step 1: Authenticate

Session continuity: If profiles were already confirmed earlier in this conversation (e.g. for a previous view in a batch), skip this step and reuse them.

ThoughtSpot profile:

1. Run ts profiles list to show configured profiles.
2. If multiple profiles: display a numbered list and ask the user to select one.
3. If exactly one profile: display it and confirm before proceeding.
4. Verify: ts auth whoami --profile {name} — print display_name and base URL.

Snowflake connection: Uses the active Cortex Code connection (configured via cortex connections set). Verify with a SELECT CURRENT_USER(), CURRENT_ROLE() query.

Step 1.5: Session Mode

Choose a conversion mode:
  A — Convert ONE Semantic View → new ThoughtSpot Model   (default)
  B — Merge MULTIPLE Semantic Views → new ThoughtSpot Model
  C — Update an EXISTING ThoughtSpot Model from a changed Semantic View

If the user selects A (or presses Enter): set session_mode = "single". Continue with the workflow unchanged — Steps 2 through 13 run exactly as documented.

If the user selects B: set session_mode = "merge". The modified Steps 2, 3, and new Step 3.5 below apply; Steps 4–13 then run on the merged result exactly once.

If the user selects C: set session_mode = "update". Skip Steps 2–13 entirely. Run the Mode C workflow documented in the section below, then stop.

Mode C: Update an Existing ThoughtSpot Model

Run these steps when session_mode = "update" (Mode C selected at Step 1.5). Skip Steps 2–13 entirely. When Step C6 completes, the session ends.

Step C1: Identify both objects

Semantic View (source — the updated version):
  Enter database.schema.view_name or press Enter to browse: _______

ThoughtSpot Model (target — the existing model to update):
  G — I have a GUID
  S — Search by name

Enter G / S:

Store {sv_name} and {model_guid}. Always require both to be explicitly selected — do not attempt to auto-match by name.

Step C2: Fetch both in parallel

Run simultaneously:

SV side — fetch and parse the DDL:

SELECT GET_DDL('SEMANTIC_VIEW', '{database}.{schema}.{sv_name}');

printf '%s' "$DDL" > sv_ddl.sql
ts snowflake parse-sv sv_ddl.sql --output parsed.json
ts snowflake translate-formulas --input parsed.json --output translated.json

ThoughtSpot side — export the existing model:

ts tml export {model_guid} --profile {profile} --fqn --associated --parse

Extract from the Model bundle: the model TML dict, its columns[] (with description, synonyms, ai_context, formula_id, column_id per column), and its formulas[] (keyed by id → expr). These are used by ts snowflake diff in Step C3.

Step C3: Compute the change set (ts snowflake diff)

The column-level comparison (expression normalisation, new/removed/modified detection) is now computed by ts snowflake diff (ts-cli v0.30.0+) — a parser-based check, same rationale as the ts tml lint pre-import gate. Join-graph comparison stays a separate, skill-local step (below) since it needs the model's join shape, not just column text — ts snowflake diff only compares columns.

IMPORTANT: the SV side was already translated via ts snowflake translate-formulas in Step C2 — the comparison is TS-formula-to-TS-formula, not raw SQL to TS formula.

Build the two column maps and write them to temp JSON files. The "current" map comes from the exported Model TML (description, synonyms, formula expr per column). The "new" map comes from translated.json (description, synonyms, ts_expr per translated entry).

ts snowflake diff --current /tmp/ts_sv_diff_model.json --new /tmp/ts_sv_diff_sv.json \
  --ignore-empty-new-description
rm -f /tmp/ts_sv_diff_*.json

--ignore-empty-new-description reproduces this skill's description-comparison rule: only flag a description change when the SV supplies a non-empty new value — a blank SV description means "no opinion," not "clear the ThoughtSpot description."

Parse the printed change_set JSON from stdout — new_columns, removed_columns (flag only), modified_descriptions, modified_synonyms (each with added/ removed), modified_expressions — then add the join comparison, which is not part of ts snowflake diff's output:

Add the join comparison (not part of ts snowflake diff's column-only output): compare parsed.json's relationships[] vs the existing model's join graph. Flag any relationship not present in the existing model (name or endpoint differs).

Step C4: Present the diff and collect decisions

Display the summary, then per-section review tables. Wait for the user to edit and type done before proceeding. Full console templates (summary block, descriptions/ synonyms/expressions tables, removed-columns warning): references/step-c-update-mode.md "Step C4 templates".

Require the user to type done after reviewing before proceeding.

Step C5: Build the updated Model TML and import

Deep-copy the existing Model TML. Apply only the confirmed changes — full change-type → action mapping table (incl. ai_context/Instructions never-touch rules): references/step-c-update-mode.md "Step C5 change-action mapping".

Build tables.json from the existing model's table GUIDs (same format as Step 8), then import with build-model --existing-guid:

ts snowflake build-model \
  --parsed parsed.json --translated translated.json --tables tables.json \
  --model-name "{model_name}" --output-dir ./tml_out \
  --existing-guid {model_guid} \
  --profile {profile}

The --existing-guid flag stamps guid at the document root and skips the two-pass phase 1 (update-in-place). The import will fail if the GUID is not found — surface the error from the summary JSON's import_error field.

Step C6: Post-import coaching handoff

After a successful import, always surface the coaching-handoff message (/ts-object-model-coach and /ts-dependency-manager pointers). Exact template: references/step-c-update-mode.md "Step C6 handoff message".

Step 2: Identify the semantic view

Single mode (merge_mode = False): proceed as documented below.

Merge mode (merge_mode = True):

1. Also ask for the output ThoughtSpot Model name now:

   Output ThoughtSpot Model name: _______
   

2. Ask the user to list the Semantic Views to merge. Accept either:
   • A comma-separated list of names: SALES_SV, INVENTORY_SV
   • A wildcard/prefix — Claude will run:

     SHOW SEMANTIC VIEWS LIKE '{prefix}%' IN SCHEMA {database}.{schema};
     

     and display matches for user confirmation before proceeding
3. Confirm the final list before proceeding to Step 3.

Single mode: If the user has named the semantic view, proceed directly to Step 3.

Otherwise, list available semantic views so the user can choose:

SHOW SEMANTIC VIEWS IN SCHEMA {database}.{schema};

If the database and schema are unknown, ask the user or run SHOW DATABASES / SHOW SCHEMAS IN DATABASE {db} first.

Display results as a numbered list. Ask the user to select one (or enter a full database.schema.view_name directly).

Step 3: Get the semantic view DDL

Single mode: run as documented below.

Merge mode: execute GET_DDL for each SV in the confirmed list. Parse each DDL independently using the Step 4 logic and store as a separate parse result object before proceeding to Step 3.5.

SELECT GET_DDL('SEMANTIC_VIEW', '{database}.{schema}.{view_name}');

Store the returned DDL string in full — it will be parsed in the next step.

If the call fails with "object does not exist", verify the fully-qualified name and the user's role has USAGE on the schema.

Converting multiple views from the same schema? List then fetch each DDL:

SHOW SEMANTIC VIEWS IN SCHEMA {database}.{schema};
SELECT "name" FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));
-- then per name:
SELECT GET_DDL('SEMANTIC_VIEW', '{database}.{schema}."' || name || '"') AS ddl;

Parse each DDL in Step 4 before switching Snowflake queries.

Step 3.5: Merge and Deduplication (merge mode only)

Skip this step if merge_mode = False.

Combine all parse results from Step 3 into a single merged result that Steps 4–13 will treat as if it came from one Semantic View. Full dedup rule set (tables, relationships, metrics, dimensions/facts, fact re-detection, merge-summary template): references/step-3.5-merge-dedup.md.

If there are unresolved conflicts, require all to be resolved before accepting the merge summary's YES. After confirmation, continue with Step 4 using the merged result.

Step 4: Parse the DDL

Write the DDL from Step 3 to a file and parse it with ts snowflake parse-sv:

printf '%s' "$DDL" > sv_ddl.sql
ts snowflake parse-sv sv_ddl.sql --output parsed.json

The command extracts all SV constructs deterministically: tables (with aliases, primary keys, range constraints, table comments), relationships (equi/range/ASOF/composite), dimensions, metrics (simple, semi-additive, window), facts (with filter labels and private visibility), verified queries, extension JSON, custom instructions, synonyms, and descriptions. See ts-from-snowflake-rules.md for the underlying rules (codified in sv_parse.py).

Exit code 1 means unsupported constructs were found — the JSON is still written.

Review the output:

1. warnings[] — informational notes (logged in the report).
2. unsupported[] — constructs the parser could not handle. Display each to the user and stop if any are critical (unknown grammar, stray range tokens).
3. custom_instructions — if ai_sql_generation or ai_question_categorization are present, log as "Custom instructions present — review for ThoughtSpot data_model_instructions equivalent (GAP-06)" in the report.
4. verified_queries[] — stored for Step 12.5 (NLS Feedback TML import).

The parsed output contains: tables[], relationships[], dimensions[], metrics[], facts[], verified_queries[], extension, custom_instructions, comment (model description), and view_name/database/schema identity fields.

Step 5: Table registration question

After parsing, display the tables found and ask a single question:

The semantic view references {n} tables:
  {database}.{schema}.{TABLE_1}
  {database}.{schema}.{TABLE_2}
  ...

Are these tables already registered in ThoughtSpot?
  Y  Yes — use existing ThoughtSpot Table objects
  N  No  — create new Table objects from scratch
  ?  Not sure — search ThoughtSpot first

Enter Y / N / ?:

• Y → skip search, go to Step 6A (column verification only)
• N → skip search, go to Step 6B (create)
• ? → go to Step 6A (search + verify)

Step 6A: Discover and verify existing ThoughtSpot Table objects (Y and ? paths)

Skip this step if the user answered N in Step 5 — go directly to Step 6B.

Choose the search scope first. A whole-instance scan is the slow path — on a large instance --all pulls every table. Offer the narrower option and search by table-name pattern (--name), never --all-then-filter:

How should I search for these tables?
  C  Within a specific connection — fastest; search that one connection's tables
  I  Entire ThoughtSpot instance  — broader, slower

Enter C / I :

Search by name (both scopes start here):

ts metadata search --subtype ONE_TO_ONE_LOGICAL --name "%{table_name}%" --profile {profile}

• C (within a connection) → first identify the connection using the N (name it) / F (filter by substring) / L (list all) prompt in Step 6B — present that prompt and let the user choose; do NOT run ts connections list and dump every connection by default. Then pass --connection "{connection_name}" to ts metadata search rather than hand-filtering: the CLI scopes it in filter_by_connection (commands/metadata.py:35), which casefolds both sides. This step previously said to keep results whose dataSourceName equals the connection name — an executor following that literally drops rows the CLI keeps (APJ_SNOW vs apj_snow). Corrected 2026-08-26, finding 11.1. Fastest, and unambiguous when the same table name exists on several connections.
• I (entire instance) → run the name search above with no connection filter.

Filter the JSON to match each semantic view base table by table name (metadata_name). Connection scoping is already done by the --connection flag above — do NOT re-filter on metadata_header.dataSourceName here: the flag casefolds and a hand comparison does not, so re-applying it drops every row the flag kept (finding 11.1, 2026-08-26). Use metadata_header.database_stripes / metadata_header.schema_stripes to disambiguate same-named tables. Build a map: physical_table_name → {metadata_id, metadata_name}.

Only fall back to --all (fetch every table) when no usable name pattern can be formed (e.g. the name is too generic). Tell the user that cost before running it.

Export TMLs for all found tables in one call to verify columns:

ts tml export {guid1} {guid2} ... --profile {profile} --parse

--parse returns structured JSON — access columns via item["tml"]["table"]["columns"] directly. Parse table.columns[].name from each returned item. Build a column map per table: table_name → [col_name, ...]. Compare against the columns referenced in the semantic view dimensions and metrics to identify any column gaps.

The column_id in the model TML must use the column names from the ThoughtSpot Table TML — export the TMLs to confirm them.

Confirm the plan before making any changes:

Show the user a full status table and wait for confirmation. Exact template: references/step-6-table-registration.md "Step 6A — Table Plan confirmation template".

Do not proceed until the user confirms. If any table is not found, follow Step 6B for those tables. If any table has missing columns, follow Step 6C before building the model.

Step 6D: Apply SV table-level metadata to ThoughtSpot Table TMLs

If the SV tables (...) block has comment='...' on any base table, push those descriptions onto the corresponding ThoughtSpot Table objects before building the model. This is a separate Table TML import, run with --no-create-new so existing tables are updated in place.

Per table that has an SV table-comment:

1. Take the parsed Table TML from Step 6A.
2. Set table.description to the SV table comment.
3. Verify table.schema matches the actual Snowflake schema — older Table objects sometimes claim a different schema than the live object, which breaks import validation. If there's a mismatch, also fix table.schema here.
4. Wrap with {guid: ..., table: ...} at top level so --no-create-new updates the existing object.

Batch all updates into one ts tml import --policy ALL_OR_NONE --no-create-new call.

If the SV does not put comment='...' on any table, skip this step.

Step 6C: Update existing tables with missing columns

For each table from Step 6A with a column gap, introspect the Snowflake schema for the missing columns only:

SELECT table_name, column_name, data_type
FROM {database}.information_schema.columns
WHERE table_schema = '{SCHEMA}'
  AND table_name IN ({comma_quoted_table_names})
  AND column_name IN ({comma_quoted_missing_col_names})
ORDER BY table_name, ordinal_position;

Map Snowflake types to ThoughtSpot types using ../../shared/mappings/ts-snowflake/ts-from-snowflake-rules.md.

Find the ThoughtSpot connection for those tables:

ts connections list --profile {profile}

Note: ts connections list auto-paginates and returns all connections.

Add the missing columns to the connection, then re-import the updated Table TML for each affected table (batch all imports in one call):

ts tml import --policy ALL_OR_NONE --profile {profile}

After import, re-export the updated TMLs to refresh the column map before Step 8.

Step 6B: Create ThoughtSpot Table objects for views (Scenario B) — also the connection picker for the Step 6A connection-scoped search

Use ts snowflake introspect to query Snowflake and build the table spec, choose or create the ThoughtSpot connection, then create the Table objects in one batch. The full command sequence — the introspect call and its manual-query fallback, the E/C connection-selection flow, the ts connections create invocation with role/key requirements, the required credential-handling guardrail (private key by file path only, never pasted into chat), and the batch ts tables create call — is in references/step-6-table-registration.md "Step 6B — command sequence".

Table objects are created with ts tables create. Do NOT use ts connections add-tables. That command rewrites the connection's registered-object list, which is a different operation and is not what this step needs — a connection that can already reach the database needs no change. Run against a shared connection it can fail with a 500 (NullPointerException in validateConfigSourceConnectionId) and, if it succeeded, would risk the connection's authenticationType.

The tell: introspect writes tables-spec.json shaped to pipe into ts tables create unmodified. If a command rejects those keys (it wants table where the spec has db_table), that is the signal you have the wrong command — do not transform the keys to force it through.

4. Inline joins will be defined directly in the model TML (no referencing_join).

Step 7: Find join names (Scenario A only)

If there is only ONE table in the semantic view, there are no joins by definition. Skip this step and proceed to Step 8 with a single model_tables entry.

Joinless semantic views (GAP-03) — multi-table SVs with no relationships:

If the SV has multiple tables but no relationships(...) block (or the block is empty), ThoughtSpot still requires joins for cross-table queries. Present the user with join discovery options:

No relationships defined in the Semantic View ({n} tables found).
ThoughtSpot requires joins for cross-table queries.

How should we discover joins?

  1 — Auto-discover from database constraints (PK/FK)
  2 — Analyse column overlap and suggest joins (deeper dive)
  3 — I'll specify the joins manually
  4 — Skip — create model with no joins (single-table queries only)

Full detail for each option — the auto-discovery SQL, confirmation console templates, the column-overlap-analysis queries and evidence display, the manual-join prompt, and the separate-model-per-table fallback — is in references/step-7-join-discovery.md.

All discovered/specified joins (Options 1–3) are added to the relationships map and treated identically to SV-declared relationships in Step 8 (inline joins on the FROM table).

For each relationship in the semantic view, find the name of the pre-defined join in the ThoughtSpot Table objects.

Re-use the TMLs already exported in Step 6A — do not make another export call. The --parse output gives item["tml"]["table"] directly for each FROM table.

For a relationship FROM {from_table} KEY {from_col} TO {to_table} KEY {to_col}:

1. In the FROM table's parsed TML (item["tml"]["table"]), find the joins_with section.
2. Match the entry where destination.name (or destination) equals the TO table name.
3. Record the join name — this is the referencing_join value for the to_table entry in the model TML.

If no matching join is found:

• Warn the user: "No pre-defined join from {from_table} to {to_table}."
• Options: (1) use an inline join instead (Scenario B for this relationship), (2) abort and define the join at the ThoughtSpot Table level first.

Step 7.5: Role-played dimension aliases (I14)

A Semantic View may join one table to the same target several times (a date dimension on order/ship/booked date; an employee dimension on several account-team roles). That is legal in an SV, which scopes names per table, and fatal in ThoughtSpot, which has one flat join graph: the join path is ambiguous and the Model will not load. ts tml lint invariant I14 rejects it, so build-model refuses rather than emitting an unloadable Model.

Detect it from parsed.json before building:

from collections import Counter
pairs = Counter((r["from_table"], r["to_table"]) for r in parsed["relationships"])
roleplay = {k: v for k, v in pairs.items() if v > 1}

If roleplay is non-empty, follow references/step-7.5-roleplay-aliases.md — it covers picking the primary role, synthesizing the alias entries, the column trim (ask the user; the naive full-copy adds hundreds of near-duplicate columns and degrades NL search), and the tables.json entries the aliases need.

Otherwise skip to Step 8.

Step 8: Assemble the tables map

Build tables.json — a JSON object mapping each SV table alias to its ThoughtSpot table identity. ts snowflake build-model uses this to resolve column references, build joins, and assemble the model TML.

Model name: {view_name_title_case} — derived from the Snowflake Semantic View name. Ask the user if they want a different name. Do not add a TEST_SV_ or other prefix — see ../../shared/schemas/ts-model-conversion-invariants.md (N1).

CRITICAL — Never normalise names from API responses. Names that came from ts tml export (join names, column names, table names) or from import response GUIDs must be used exactly as returned — no .lower(), no .upper(), no title-casing, no whitespace trimming. The name value in tables.json must match the ThoughtSpot Table object name character-for-character.

Format:

{
  "ALIAS_1": {"name": "TS_TABLE_NAME", "fqn": "guid_from_step_6"},
  "ALIAS_2": {"name": "TS_TABLE_NAME", "fqn": "guid_from_step_6"}
}

• ALIAS is the SV table alias from parsed.json (the alias field in each tables[] entry).
• name is the exact ThoughtSpot Table object name (from ts tml export in Step 6A, or from ts tables create response in Step 6B).
• fqn is the ThoughtSpot Table GUID.

Scenario B (new tables created via ts snowflake introspect in Step 6B): the introspect command produces tables.json directly — use it as-is.

Scenario A (existing tables from Step 6A): build the map manually from the Step 6A discovery results.

Joinless models (user chose Option 4 in Step 7): create a separate tables.json per table. Each will produce a separate model via build-model. Name each model {view_name} — {TABLE_NAME} (or let the user choose).

Write the result to tables.json.

What build-model handles from here:

ts snowflake build-model (Steps 10-FILE / 11) takes parsed.json, translated.json, and tables.json and deterministically assembles the model TML. It handles:

• Fact table detection (tables never on the TO side of a relationship)
• Inline join assembly (equi, range, ASOF, composite) with LEFT_OUTER / MANY_TO_ONE defaults
• Column classification (ATTRIBUTE / MEASURE), column_id resolution
• Formula entries with formula_id pairing, id-based cross-references
• Synonym mapping (first → display name, rest → properties.synonyms)
• Description mapping, filter labels, private columns (index_type: DONT_INDEX)
• Duplicate column_id detection (I8) — promotes duplicates to formulas
• COUNT(DISTINCT) → unique count(...) formula (I5)
• Name collision resolution, formula_ prefix for cross-references
• YAML block scalar encoding for { } formulas

Step 8.5: Display-name collisions

A Semantic View scopes construct names per table; a ThoughtSpot Model has one flat column namespace. On a wide multi-fact SV the two collide by construction and build-model refuses with duplicate display title(s): .... Detect it before building:

import re
from collections import defaultdict
def title(n): return " ".join(w.capitalize() for w in re.split(r"[_\s]+", n))
groups = defaultdict(list)
for block in ("dimensions", "facts", "metrics"):
    for e in parsed[block]:
        groups[title(e["source_column"])].append(e)
dups = {k: v for k, v in groups.items() if len(v) > 1}

If dups is non-empty, follow references/step-8.5-display-name-collisions.md — characterise the collisions, ask the user which resolution they want (it changes the model's whole search surface), then apply it to the parsed doc.

Otherwise skip to Step 9.

Step 9: Translate SQL expressions → ThoughtSpot formulas

Run the deterministic formula translator:

ts snowflake translate-formulas --input parsed.json --output translated.json

The command translates all dimension, fact, and metric SQL expressions from Snowflake SQL into ThoughtSpot formula syntax. It handles:

• Identifier resolution (physical columns → [TABLE::col], facts → [formula_<id>], metrics → double aggregation via group_aggregate)
• Window functions (PARTITION BY → group_sum/group_aggregate; ORDER BY ROWS BETWEEN → moving_sum/cumulative_sum)
• Semi-additive patterns (NON ADDITIVE BY → last_value/first_value)
• LOD expressions, contribution ratios, COUNT_IF, COALESCE/NULLIF
• YAML block scalar encoding for { } formulas

All translation rules come from ts-snowflake-formula-translation.md (codified in sv_sql.py + sv_translate.py).

Review the output stats (printed to stdout):

{"total": N, "translated": M, "skipped": K}

Surface annotations[] too — each translated entry may carry review markers (🔄 double aggregation, ⚑ ambiguous reference or skipped double aggregation). Carry them into the Step 12 Review Flags section; they are the only signal for translations that succeeded but need a human check.

Surface skipped[] entries to the user — each has a name, block, and reason. These are formulas the translator could not handle (unsupported SQL constructs, triple aggregation, etc.). Ask whether to proceed without them or address manually.

Step 9.5: Spotter enablement

Ask whether Spotter (AI search) should be enabled. Default is yes.

Enable Spotter (AI search) for this model? [Y / n] (default: Y)

Store the answer as a flag for ts snowflake build-model:

• Y → pass --spotter-enabled
• n → pass --no-spotter-enabled
• Omit the flag entirely to leave the spotter_config block absent (pre-existing models being updated in place: if the user does not explicitly answer, omit the flag to preserve the existing setting).

Step 10: Review checkpoint

Before importing, show the user a summary assembled from parsed.json, translated.json, and tables.json — tables with fact/join annotations, columns by type, the formula translation log, filter labels, verified queries, and the Spotter setting. Exact console template: references/step-12-report-formats.md "Step 10 — Review checkpoint console template".

Wait for user confirmation before proceeding.

If the user selects file, skip to Step 10-FILE.

Step 10-FILE: Output TML files (file-only mode)

This path is used when the user selected file at the Step 10 checkpoint, explicitly said "file only", or has no ThoughtSpot DATAMANAGEMENT access.

Run ts snowflake build-model without --profile — it generates the TML files to --output-dir without importing:

ts snowflake build-model \
  --parsed parsed.json --translated translated.json --tables tables.json \
  --model-name "{model_name}" --output-dir ./tml_out \
  --sv-fqn "{database}.{schema}.{view_name}" \
  {--spotter-enabled|--no-spotter-enabled}

The command writes {model_name}.model.tml to the output directory, validates TML invariants, and prints a summary JSON to stdout. Exit code 1 on lint findings.

Report to the user:

TML files written to ./tml_out/:
  {model_name}.model.tml    — ThoughtSpot Model TML

To import to ThoughtSpot when you have access:
  ts tml import --file ./tml_out/{model_name}.model.tml --policy ALL_OR_NONE --profile {profile}

  Note: On first import, omit `guid` from the TML (already omitted here). ThoughtSpot
  will assign a GUID — save it from the import response if you need to update the model later.

Proceed to Step 12 — include the formula translation log and column summary from the build-model summary JSON.

Pre-import validation gate

ts snowflake build-model runs ts tml lint internally before any import — the command exits 1 on lint findings. See ../../shared/schemas/ts-tml-import-gate.md for the invariant list and import-policy rules. No separate lint step is needed.

Step 11: Import the model

Re-run ts snowflake build-model with --profile to import:

ts snowflake build-model \
  --parsed parsed.json --translated translated.json --tables tables.json \
  --model-name "{model_name}" --output-dir ./tml_out \
  --sv-fqn "{database}.{schema}.{view_name}" \
  {--spotter-enabled|--no-spotter-enabled} \
  --profile {profile}

For updating an existing model, add --existing-guid {guid}.

The command handles:

• Two-pass import (L7): phase 1 imports structure only (no formulas) to capture the GUID; phase 2 imports the full model with formulas using the captured GUID. With --existing-guid, phase 1 is skipped (update-in-place).
• GUID placement: always at the document root, never nested under model:.
• Pre-import lint: ts tml lint runs internally — the command exits 1 on findings.
• YAML serialization: block scalars for { } formulas, Unicode support.

Parse the summary JSON from stdout — it includes import_status and model_guid. On import_status: "failed", import_error gives the error details.

Common import errors: see ts-tml-import-gate.md § 4.

Step 11b: Verify Import

Follow ts-tml-import-gate.md § 5.

Step 11c: Reconcile the Model against the Semantic View

A successful import is not a correct conversion. Step 11b confirms the object exists and re-exports; it does not confirm a single number. Query the Model and compare it to the SV — this is the only check that catches a join wired to the wrong key, a role-played alias that resolved to the wrong node, or rows lost to an unmatched join.

Check 1 — grand totals, every additive measure. For each simple SUM metric, compare the Model's grand total to the SV's:

# ThoughtSpot
ts a

…(truncated)