# TS Convert From Snowflake Sv > Convert or import a Snowflake Semantic View into ThoughtSpot as a Model. Use when Snowflake is the source and the goal is a ThoughtSpot Model — whether migrating Snowflake metrics and semantic definitions into ThoughtSpot or making a Semantic View available for Spotter and search-based analytics. Direction is always Snowflake → ThoughtSpot. Not for ThoughtSpot → Snowflake, standalone DDL generation, or adding AI context to existing ThoughtSpot models. - Skill: `thoughtspot/ts-convert-from-snowflake-sv` (Agent Skill, multi-file: 11 files) - Install (CLI): `npx skillmds@latest add thoughtspot/ts-convert-from-snowflake-sv` - Raw SKILL.md: https://api.skillmd.com/api/skills/thoughtspot/ts-convert-from-snowflake-sv/raw - Safety review: pending - Works with: Claude Code, Claude.ai, OpenAI Codex - Category: Data & Analytics - Author: thoughtspot (https://skillmd.com/u/thoughtspot) - Updated: 2026-09-21 - Page: https://skillmd.com/skills/thoughtspot/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 | File | Purpose | |---|---| | [../../shared/mappings/ts-snowflake/ts-from-snowflake-rules.md](../../shared/mappings/ts-snowflake/ts-from-snowflake-rules.md) | Snowflake Semantic View DDL parsing, type mapping, formula translation, column classification | | [../../shared/mappings/ts-snowflake/ts-snowflake-formula-translation.md](../../shared/mappings/ts-snowflake/ts-snowflake-formula-translation.md) | SQL → ThoughtSpot formula translation rules (bidirectional reference) | | [../../shared/schemas/thoughtspot-table-tml.md](../../shared/schemas/thoughtspot-table-tml.md) | Table TML structure, connection reference, data types, import patterns, common errors | | [../../shared/schemas/thoughtspot-model-tml.md](../../shared/schemas/thoughtspot-model-tml.md) | Model TML structure, join scenarios, formula visibility, self-validation checklist | | [../../shared/schemas/thoughtspot-formula-patterns.md](../../shared/schemas/thoughtspot-formula-patterns.md) | ThoughtSpot formula syntax, all function categories, LOD/window/semi-additive patterns, YAML encoding rules | | [../../shared/worked-examples/snowflake/ts-from-snowflake.md](../../shared/worked-examples/snowflake/ts-from-snowflake.md) | End-to-end example: BIRD_SUPERHEROS_SV → ThoughtSpot Model (se-thoughtspot, inline joins, verified against live DDL) | | [../../shared/worked-examples/snowflake/ts-from-snowflake-dunder.md](../../shared/worked-examples/snowflake/ts-from-snowflake-dunder.md) | End-to-end example: DUNDER_MIFFLIN_SALES_INVENTORY → TS Model. Exercises multi-value synonyms, per-column descriptions, table comments, semi-additive metrics (closing/opening), `unique count` formula, and `concat()` for strings. | | [../ts-profile-thoughtspot/SKILL.md](../ts-profile-thoughtspot/SKILL.md) | ThoughtSpot auth methods, profile config, CLI usage | | Cortex Code connection (configured via `cortex connections set`) | Snowflake connection code, SQL execution patterns | | [references/concept-mapping.md](references/concept-mapping.md) | SV DDL construct → ThoughtSpot Model mapping table | | [references/step-3.5-merge-dedup.md](references/step-3.5-merge-dedup.md) | Step 3.5 merge-mode dedup rules and merge-summary template | | [references/step-6-table-registration.md](references/step-6-table-registration.md) | Step 6A table-plan template; Step 6B introspect/connection/create command sequence | | [references/step-7-join-discovery.md](references/step-7-join-discovery.md) | Step 7 joinless-SV (GAP-03) join-discovery options | | [references/step-7.5-roleplay-aliases.md](references/step-7.5-roleplay-aliases.md) | Step 7.5 role-played dimension aliases — I14, the alias shape, column trim | | [references/step-8.5-display-name-collisions.md](references/step-8.5-display-name-collisions.md) | Step 8.5 flat-namespace collisions — characterise, choose, disambiguate | | [references/step-c-update-mode.md](references/step-c-update-mode.md) | Mode C diff-review, change-action mapping, and handoff templates (C4–C6) | | [references/step-12-report-formats.md](references/step-12-report-formats.md) | Step 10/12/12.5 console + TML report templates | | [references/open-items.md](references/open-items.md) | Known gaps and deferred capabilities for this skill | --- ## 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](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: ```sql SELECT GET_DDL('SEMANTIC_VIEW', '{database}.{schema}.{sv_name}'); ``` ```bash 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: ```bash 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). ```bash 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](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](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`: ```bash 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](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: ```sql 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: ```sql 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. ```sql 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: ```sql 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](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`: ```bash 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](../../shared/mappings/ts-snowflake/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):** ```bash 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:** ```bash 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](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: ```sql 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: ```bash 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): ```bash 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](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](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: ```python 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](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:** ```json { "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: ```python 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](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: ```bash 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_]`, 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](../../shared/mappings/ts-snowflake/ts-snowflake-formula-translation.md) (codified in `sv_sql.py` + `sv_translate.py`). **Review the output stats** (printed to stdout): ```json {"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](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). --- ### 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: ```bash 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`](../../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: ```bash 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](../../shared/schemas/ts-tml-import-gate.md#4-common-import-errors). --- ### Step 11b: Verify Import Follow [`ts-tml-import-gate.md` § 5](../../shared/schemas/ts-tml-import-gate.md#5-post-import-verification). --- ### 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: ```bash # ThoughtSpot ts a …(truncated)