ts-convert-from-snowflake-sv

name: ts-convert-from-snowflake-sv description: Convert a Snowflake Semantic View into a ThoughtSpot Model by reading the view DDL, mapping tables and joins, translating SQL expressions to ThoughtSpot formulas, and importing the model via the ThoughtSpot REST API.

Snowflake Semantic View → ThoughtSpot Model

Converts a Snowflake Semantic View into a ThoughtSpot Model. Reads the semantic view DDL via GET_DDL, maps tables, relationships, dimensions, and metrics to ThoughtSpot TML, translates SQL expressions to ThoughtSpot formulas, and imports the result via the ThoughtSpot REST API.

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.

References

Concept Mapping

Distinct count formula: use unique count ( [TABLE::col] ) — note the space, not an underscore. count_distinct(...) is rejected by the TS formula parser.

String concatenation: use concat(a, ', ', b) in TS formulas — + does NOT concatenate strings.

SQL Call Batching (Minimise UI Confirmations)

CRITICAL for Snowsight Workspaces: Every snowflake_sql_execute call triggers a UI confirmation prompt. Minimise calls by batching related statements.

Target call budget: Aim for 4–5 total SQL calls per model:

Prerequisites

• A Snowflake role with USAGE on the database/schema containing the semantic view
• ThoughtSpot setup completed via /ts-profile-thoughtspot — SKILLS.PUBLIC.THOUGHTSPOT_PROFILES table must exist with at least one profile
• User account with DATAMANAGEMENT or DEVELOPER privilege in ThoughtSpot — only required for import

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

Workflow

Step 1: Select profile and get DDL

Check procedures and select the ThoughtSpot profile in one call:

SELECT PROCEDURE_NAME FROM SKILLS.INFORMATION_SCHEMA.PROCEDURES
WHERE PROCEDURE_SCHEMA = 'PUBLIC'
  AND PROCEDURE_NAME IN ('TS_SEARCH_MODELS', 'TS_EXPORT_TML', 'TS_IMPORT_TML');

SELECT NAME, BASE_URL, USERNAME, AUTH_TYPE, SECRET_NAME, TOKEN_EXPIRES_AT
FROM SKILLS.PUBLIC.THOUGHTSPOT_PROFILES
ORDER BY NAME;

Procedure check: if any of the three procedures are missing from the first result, stop and tell the user:

"Required stored procedures are not installed. Run /ts-setup-sv to install them, then retry."

Profile selection: using the rows from the second result:

• If multiple rows: display a numbered list (#. name — auth_type — base_url) and ask the user to select one. Store the selected NAME as {profile_name}.
• If exactly one row: display it and confirm before proceeding. Store as {profile_name}.

Validate the selected profile — branch by auth_type:

Token auth: check the TOKEN_EXPIRES_AT value already returned above (no second query):

• TOKEN_EXPIRES_AT > CURRENT_TIMESTAMP() → proceed
• Otherwise → stop:

  "The token for profile '{profile_name}' has expired. Run /ts-profile-thoughtspot → U → Refresh token, then retry."

Password auth: no expiry check needed — proceed directly to credential retrieval.

Batch: retrieve credential + get DDL:

If the user has not named the semantic view, first list available views:

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

Display results as a numbered list and ask the user to select one.

Then fetch credential and store the full DDL. The DDL can be very long and is truncated when read inline — always store it via CREATE TABLE AS SELECT:

-- Batch call: credential + DDL stored in temp table (two statements, one confirmation)
SELECT SYSTEM$GET_SECRET_STRING('SKILLS.PUBLIC.' || SECRET_NAME) AS secret_value
FROM SKILLS.PUBLIC.THOUGHTSPOT_PROFILES
WHERE name = '{profile_name}';

CREATE OR REPLACE TEMPORARY TABLE SKILLS.TEMP.SV_DDL AS
SELECT GET_DDL('SEMANTIC_VIEW', '{database}.{schema}.{view_name}') AS ddl_text;

Read the full DDL from the temp table in the next step:

SELECT ddl_text FROM SKILLS.TEMP.SV_DDL;

Do not use SELECT GET_DDL(...) directly — the result will be truncated. Do not use SUBSTR chunking — that requires multiple extra SQL calls. GET_DDL is a function (not a stored procedure), so CREATE TABLE AS SELECT works and stores the complete result in one call.

Store secret_value for use in subsequent API calls via stored procedures. Never print it.

Step 1.5: Choose conversion mode

Present this menu to the user and wait for a response:

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

Mode A / B — continue with Step 2 and the standard workflow. For Mode B, repeat Steps 1–6 for each additional SV, then merge all column sets before importing.

Mode C — skip Steps 2–9 and jump to the Mode C workflow section below.

Step 2: Parse the DDL

Parse the DDL string returned in Step 1. The DDL is a SQL CREATE OR REPLACE SEMANTIC VIEW statement. See ../../shared/mappings/ts-snowflake/ts-from-snowflake-rules.md for the full format.

Important: The real DDL format has flat dimensions and metrics blocks at the view level — NOT nested per-table. Relationships use REL_NAME as FROM(COL) references TO(COL) syntax.

Extract:

1. Tables block: for each entry, record:
   • Fully-qualified table reference (DB.SCHEMA.TABLE)
   • Table alias (explicit ALIAS as DB.SCHEMA.TABLE, or last segment of the name)
   • Primary key column(s) — marks this as a join target
   • Range constraint (if present): constraint <NAME> distinct range between <START> and <END> exclusive
2. Relationships block: for each entry, record name, from/to aliases and columns, and join style:
   • Equi-join: REL_NAME as FROM(COL) references TO(COL) → join_style: "equi"
   • Composite equi-join: multiple column pairs → join_style: "equi" with parallel column lists
   • Range join (BETWEEN): references TO(between START and END exclusive) → join_style: "range"
   • ASOF join: references TO(COL1, ASOF COL2) → join_style: "asof"
3. Dimensions block (flat, all tables): for each entry (TABLE.COL as view_alias.NAME [comment='...']), record:
   • Source: table alias + column name
   • Display name: value of comment='...', or title-cased NAME if no comment
   • Filter label: labels = (filter) before as → is_filter: true (BOOLEAN expression)
4. Metrics block (flat): for each entry, record:
   • Source: table alias + column name
   • Aggregation (from AGG(...)) or full expression
   • Display name: from comment='...' or title-cased NAME
5. Facts block (if present): for each entry (TABLE.FACT_NAME as EXPR [comment='...'] [with synonyms=(...)]), record:
   • Source: TABLE alias + fact name
   • Expression (SQL): the right-hand side
   • Synonyms + description: same mapping as dimensions
   • Filter label: labels = (filter) → is_filter: true
   • Visibility: PRIVATE modifier if present
6. Extension JSON (with extension (CA='...')): log but do not map to ThoughtSpot
7. Verified queries (ai_verified_queries (...)): if present, parse each query (name, question, sql). Store for NLS Feedback TML emission after Model import.

Build an internal map:

• tables: list of {alias, fqn, primary_key, range_constraint}
• relationships: list of {name, from_alias, from_cols[], to_alias, to_cols[], join_style}
• columns: all dimensions and metrics keyed by (table_alias, col_name), with is_filter flag
• facts: keyed by (table_alias, fact_name) → {expression, comment, synonyms[], visibility, is_filter}
• verified_queries: list of {name, question, sql}

Identify the fact table: the table that never appears on the TO side of any relationship.

Step 3: 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 → go to Step 4A (verify existing tables, skip the search)
• N → go to Step 4B (create new Table objects)
• ? → go to Step 4A (search + verify)

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

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

Search ThoughtSpot for matching table objects:

Search for all base tables in a single call by passing their names as an array:

CALL SKILLS.PUBLIC.TS_SEARCH_MODELS(
    '{profile_name}',
    ARRAY_CONSTRUCT('{table_name_1}', '{table_name_2}', ...),
    FALSE
);

The procedure fetches all ThoughtSpot objects and filters client-side to names containing any of the supplied keywords. Filter the returned results further to match by database + schema + table name (case-insensitive). Build map: physical_table_name → {guid, metadata_name}.

Multiple results with the same name: TS_SEARCH_MODELS returns tables across ALL connections. If the same table name appears more than once (different connections), ask the user to confirm which connection their tables are on, then filter to matching connection. Do not guess — using a GUID from the wrong connection will cause fqn resolution failed when the model imports.

Always use owner_only=FALSE. Token-authenticated users may not appear as the owner of objects they created, so owner_only=TRUE can return 0 results even for objects that exist.

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

The CALL result can be truncated when read inline. Always store via RESULT_SCAN. These must be two separate SQL calls — RESULT_SCAN depends on LAST_QUERY_ID().

-- Call 1: export
CALL SKILLS.PUBLIC.TS_EXPORT_TML('{profile_name}', ARRAY_CONSTRUCT('{guid1}', '{guid2}'));

-- Call 2: store full result (column is always named after the procedure, uppercase)
CREATE OR REPLACE TEMPORARY TABLE SKILLS.TEMP.TML_RAW (tml_data VARIANT);
INSERT INTO SKILLS.TEMP.TML_RAW
SELECT PARSE_JSON("TS_EXPORT_TML") FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));

Do not use the procedure in a FROM clause or as a UDF — it is a stored procedure, not a function. FLATTEN and direct SELECT from the CALL result will not work.

Parse table.columns[].name from each returned TML. Build a column map per table: table_name → [col_name, ...]. Column names in the ThoughtSpot TML are what you use in column_id — always use the TML as the authoritative source.

Confirm the plan before making any changes:

Table Plan:
  ✓  {TABLE_1}  — found (GUID: {guid}) — all {n} columns present → use as-is
  ⚠  {TABLE_2}  — found (GUID: {guid}) — missing {n} columns: {COL_A}, {COL_B} → update
  ✗  {TABLE_3}  — not found in ThoughtSpot → create new

Actions to be taken:
  • Update {TABLE_2}: add {n} missing columns
  • Create {TABLE_3}: {n} columns from Snowflake schema

No changes have been made yet. Proceed? (yes/no):

Do not proceed until the user confirms. For any table not found, follow Step 4B. For any table with missing columns, add them before building the model.

Step 4B: Create ThoughtSpot Table objects (Scenario B)

When tables don't exist in ThoughtSpot, create them first before the model.

Ask which ThoughtSpot connection to register them under:

Which ThoughtSpot connection should these tables be added to?

Connection name:

Store as {connection_name}.

Do NOT search for the connection using TS_SEARCH_MODELS — that procedure only finds LOGICAL_TABLE objects (worksheets, models, tables). Connections are a different object type and are not returned. The connection is referenced by name directly in the Table TML — no GUID or lookup is needed.

For each base table reference, introspect columns:

SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE
FROM {database}.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = '{schema}'
ORDER BY TABLE_NAME, ORDINAL_POSITION;

Build a table TML for each table and import them all in one batch:

table:
  name: TABLE_NAME
  db: DATABASE
  schema: SCHEMA
  db_table: TABLE_NAME
  connection:
    name: {connection_name}
  columns:
  - name: COL_NAME
    db_column_name: COL_NAME
    properties:
      column_type: ATTRIBUTE
    db_column_properties:
      data_type: INT64      # or VARCHAR, DOUBLE, etc.
  joins:                     # Only on tables that have FK relationships
  - name: JOIN_NAME
    destination: TARGET_TABLE
    on: "[SOURCE::FK_COL] = [TARGET::PK_COL]"
    type: INNER

BEFORE calling import — understand validate_only:

TS_IMPORT_TML takes a third boolean argument:

• TRUE = dry-run validation only — no objects are created. The response will contain a GUID, but that GUID is a temporary test artifact — the table does NOT exist in ThoughtSpot and this GUID will fail on any subsequent export or reference. Discard the entire TRUE response. Do not record GUIDs from it.
• FALSE = actual creation — objects are created and GUIDs are real and persistent.

This is a two-call sequence — do not skip the second call:

-- Call 1: Validate (dry run — no objects created)
CALL SKILLS.PUBLIC.TS_IMPORT_TML('{profile_name}', ARRAY_CONSTRUCT($$...$$, $$...$$), TRUE);
-- Check status = SUCCESS. If SUCCESS: proceed to Call 2. Discard all GUIDs from this response.
-- If FAILURE: see error handling below before retrying.

-- Call 2: Actual import (creates objects — run this after Call 1 succeeds)
CALL SKILLS.PUBLIC.TS_IMPORT_TML('{profile_name}', ARRAY_CONSTRUCT($$...$$, $$...$$), FALSE);
-- GUIDs from THIS response are real. Store them via RESULT_SCAN (see below).

If Call 1 (validation) fails with a connection-related error (e.g. "connection not found", "invalid connection name") — do NOT proceed to Call 2. Call TS_LIST_CONNECTIONS to fetch available connections and ask the user to correct the name:

CALL SKILLS.PUBLIC.TS_LIST_CONNECTIONS('{profile_name}');

Note: TS_LIST_CONNECTIONS may return a partial list if the ThoughtSpot instance has many connections. If the user's connection does not appear, ask them to enter the exact name directly — ThoughtSpot validates the name at import time.

Display results as a numbered list and ask the user to select:

Available ThoughtSpot connections:
  1. APJ_BIRD       (SNOWFLAKE)
  2. PROD_SF        (SNOWFLAKE)

Enter the connection name to use:

Update {connection_name} and rebuild all table TMLs with the corrected name (the connection field appears in every table TML), then re-run both calls.

-- Actual import (creates objects)
CALL SKILLS.PUBLIC.TS_IMPORT_TML('{profile_name}', ARRAY_CONSTRUCT($$...$$, $$...$$), FALSE);

Do NOT read the inline CALL response for GUIDs — the inline response is often truncated when many objects are imported. Immediately run the next two SQL calls to store the complete result and extract GUIDs:

-- Store full response via RESULT_SCAN (separate call — must follow immediately)
CREATE OR REPLACE TEMPORARY TABLE SKILLS.TEMP.TABLE_IMPORT_RESULT (result_data VARIANT);
INSERT INTO SKILLS.TEMP.TABLE_IMPORT_RESULT
SELECT PARSE_JSON("TS_IMPORT_TML") FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));

-- Extract all GUIDs as a single JSON object (one row — cannot be row-truncated)
SELECT OBJECT_AGG(
    value:response:header:name::STRING,
    value:response:header:id_guid::STRING::VARIANT
) AS guid_map
FROM SKILLS.TEMP.TABLE_IMPORT_RESULT,
LATERAL FLATTEN(input => result_data)
WHERE value:response:status:status_code::STRING = 'OK';

This returns one row like {"DM_ORDER": "guid1", "DM_DATE_DIM": "guid2", ...}. Parse each key-value pair to build the table_name → guid map for use in Step 6.

If OBJECT_AGG returns no rows (import procedure returned unexpected format), query individual tables by name from the temp table instead of searching:

-- Query one table's GUID by name from the temp table
SELECT value:response:header:id_guid::STRING AS guid
FROM SKILLS.TEMP.TABLE_IMPORT_RESULT,
LATERAL FLATTEN(input => result_data)
WHERE value:response:header:name::STRING = '{table_name}';

Run this once per table. Prefer this over searching.

Last resort — if the temp table is unavailable or empty:

Only if SKILLS.TEMP.TABLE_IMPORT_RESULT has no rows should you fall back to search. Do it in two batched calls — do NOT make one search call and one export call per table:

-- Call 1: Search for ALL table names in one call
CALL SKILLS.PUBLIC.TS_SEARCH_MODELS(
    '{profile_name}',
    ARRAY_CONSTRUCT('TABLE_1', 'TABLE_2', 'TABLE_3', ...),   -- all table names
    FALSE
);

You will get multiple GUIDs per name (tables from different connections). Collect ALL candidate GUIDs across all results, then export them all in ONE call:

-- Call 2: Export ALL candidates in one call
CALL SKILLS.PUBLIC.TS_EXPORT_TML(
    '{profile_name}',
    ARRAY_CONSTRUCT('guid_a', 'guid_b', 'guid_c', ...)   -- every candidate GUID
);

Store via RESULT_SCAN, then filter: keep only TMLs where table.connection.name matches {connection_name}. The surviving GUIDs are the newly created tables. Do NOT make separate search or export calls per table — batch everything into two calls.

CRITICAL — do NOT call TS_SEARCH_MODELS as the first approach after import.

TS_SEARCH_MODELS searches by name across ALL connections. It returns pre-existing tables from other connections as well as the ones you just created. Use the temp table (OBJECT_AGG or per-name query) first. Only fall back to search if the temp table is genuinely empty — and even then, batch all searches and exports into two calls total.

Also record the join names you defined in the Table TMLs during this step (e.g. DM_ORDER_to_DM_CUSTOMER). These are the referencing_join values for Step 6.

Only available procedures are: TS_SEARCH_MODELS, TS_EXPORT_TML, TS_IMPORT_TML, TS_LIST_CONNECTIONS. Do not attempt to call any other procedure — none others exist.

IMPORTANT: Use $$ dollar-quoting for each TML string to preserve YAML formatting. Do NOT use \n escape sequences — they are passed literally and break YAML parsing.

Snowflake → ThoughtSpot type mapping:

Step 5: Find join names (Scenario A only)

If the SV has only ONE table, skip this step — no joins needed.

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

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

1. Auto-discover from database PK/FK constraints:

   SHOW IMPORTED KEYS IN TABLE {db}.{schema}.{table};
   

   Build joins from FK→PK pairs. Composite FKs (same constraint name, multiple rows) become composite equi-joins. Present for user confirmation.

2. Analyse column overlap (deeper dive): scan matching column names across tables, check composite key uniqueness (COUNT(*) vs COUNT(DISTINCT(cols))), validate cardinality (MAX(cnt) from grouped query). Present suggestions with evidence.

3. User specifies joins manually: prompt for from/to tables, columns, cardinality.

4. Skip: create a separate model per table (cross-table queries require joins; without them, each table needs its own model).

Discovered joins are treated as inline joins[] in Step 6 (Scenario B pattern).

Scenario B — skip this step entirely. Join names were defined by you in the Table TMLs built in Step 4B. Use those same names as referencing_join values in Step 6. No search or export is needed. Go directly to Step 6.

For Scenario A only — find the pre-defined join name in the ThoughtSpot Table TML of the FROM table. Export TMLs for all FROM tables in one call:

POST {base_url}/api/rest/2.0/metadata/tml/export
{
  "metadata": [
    {"type": "LOGICAL_TABLE", "identifier": "{from_table_guid_1}"},
    {"type": "LOGICAL_TABLE", "identifier": "{from_table_guid_2}"}
  ],
  "export_fqn": false
}

Parse each returned edoc YAML string. Find in the joins_with section the entry whose destination matches the TO table name. Record the join name.

Step 6: Build and translate the model TML

IMPORTANT — TML format rules learned from production use:

1. column_type must be nested under properties: in both Table TML and Model TML. This applies to columns[] entries AND formulas[] entries:

   # Model TML columns — correct format
   columns:
   - column_id: dm_order::ORDER_ID
     name: "Order Id"
     properties:
       column_type: ATTRIBUTE
   # Model TML formulas — correct format
   formulas:
   - name: "Employee"
     expr: "concat ( [dm_employee::LAST_NAME] , ', ' , [dm_employee::FIRST_NAME] )"
     properties:
       column_type: ATTRIBUTE
   

   Bare column_type: ATTRIBUTE (without properties:) causes "No enum constant ColumnTypeEnum." error.

2. Model TML joins belong INSIDE the source table's model_tables[] entry — NOT at the top level of the model. A joins: key directly under model: is wrong and causes a "destination is missing" error. Joins live on the FROM table entry. The id: field on model_tables[] entries is optional — ThoughtSpot uses name: as the join reference target when id: is absent:

   model:
     model_tables:
     - name: DM_ORDER_DETAIL  # FROM table — exact ThoughtSpot table name (often uppercase)
       fqn: "{guid}"
       joins:
       - with: DM_ORDER        # must equal the `name:` of the target entry (case-sensitive)
         'on': '[DM_ORDER_DETAIL::FK_COL] = [DM_ORDER::PK_COL]'
         type: INNER
         cardinality: MANY_TO_ONE
     - name: DM_ORDER          # TO table — exact ThoughtSpot table name
       fqn: "{guid}"
   

   Fields: with (not destination:), 'on' (quoted), type, cardinality — all required. with must match the target's name: exactly — case-sensitive.

3. Join type is INNER for all dimension lookups. ThoughtSpot models work correctly with INNER joins for standard fact-to-dimension relationships.

4. Column column_id format is TABLE_NAME::COLUMN_NAME (not db_column_name). TABLE_NAME is the value of the name: field in model_tables[].

5. Display names should be title-cased (e.g. "Superhero Name" not "SUPERHERO_NAME").

6. Model properties should include:

   properties:
     is_bypass_rls: false
     join_progressive: true
   

7. Formula columns must NOT have aggregation: in the formulas[] entry — formulas are self-aggregating through their expression. A columns[] entry that references a formula via formula_id: CAN have aggregation:. Correct formulas[] format:

   formulas:
   - id: formula_Num Orders
     name: "Num Orders"
     expr: "unique count ( [DM_ORDER::ORDER_ID] )"
     properties:
       column_type: MEASURE
   

   No aggregation: field — not even aggregation: FORMULA.

8. Every formula must have a columns[] entry. Add a columns[] entry with formula_id: for every entry in formulas[]:

   columns:
   - name: "Num Orders"
     formula_id: formula_Num Orders   # matches the formula's `id` field exactly
     properties:
       column_type: MEASURE
       aggregation: COUNT
       index_type: DONT_INDEX
   

   formula_id must match the formula's id exactly (case-sensitive, spaces included). aggregation: is allowed on columns[] formula entries (unlike formulas[] entries). index_type: DONT_INDEX is recommended for computed numeric measures.

9. id fields in model_tables[] are optional. When present, id must equal name exactly (same case). ThoughtSpot resolves with and on references against name:. Omitting id: is simpler and avoids case-mismatch errors — use name: alone.

Apply all column, formula, and join mappings from ../../shared/mappings/ts-snowflake/ts-from-snowflake-rules.md to build the model TML dict. Serialise to a YAML string.

MANDATORY (I7) — before classifying any metric as untranslatable, open ../../shared/mappings/ts-snowflake/ts-snowflake-formula-translation.md and check the reverse-translation tables. Do not decide from SQL syntax alone. See ../../shared/schemas/ts-model-conversion-invariants.md (I7).

Facts → formulas (emit before metric formulas):

For each public fact in the facts map:

• Create a formulas[] entry with the translated expression. Use column_type: MEASURE for numeric expressions, column_type: ATTRIBUTE for string/date expressions.
• Create a paired columns[] entry with formula_id matching the formula's id.
• Private facts referenced by a metric: create with index_type: DONT_INDEX. Unreferenced private facts: skip.
• See ../../shared/mappings/ts-snowflake/ts-from-snowflake-rules.md "Facts Block → ThoughtSpot" for the full mapping pattern.

Identifier resolution (MANDATORY pre-pass before translating metrics):

Before translating any metric expression, resolve every table_alias.name reference. Use the Identifier Resolution Algorithm in ../../shared/mappings/ts-snowflake/ts-from-snowflake-rules.md:

1. Physical column? → use [TABLE::col] reference
2. Fact? → use [formula_<id>] reference (the fact's formula id, NOT display name — [Tenure Months] fails; [formula_Tenure Months] works)
3. Metric? → double aggregation: wrap inner metric in group_aggregate: outer_agg(group_inner_agg([CHILD_TABLE::col], [PARENT_TABLE::pk_col])). Use group_* shorthand when available (group_count, group_sum, etc.). See ../../shared/mappings/ts-snowflake/ts-from-snowflake-rules.md "Double Aggregation".
4. None? → FAIL loudly with the unresolvable reference name.

Window metrics referencing metrics (GAP-13): resolve the inner metric first, then apply the window translation. For cumulative functions, inline the inner aggregation directly: cumulative_sum(count([TABLE::col]), [TABLE::order_col]).

For each metric in the semantic view:

• Simple SUM/COUNT/AVG/MIN/MAX(table.col) → MEASURE column in columns[]
• COUNT(DISTINCT table.col) → always a formula in formulas[], never a MEASURE column:

  unique count ( [TABLE_ID::col_name] )
  

  ThoughtSpot rejects models where the same column_id appears more than once (I8). This applies broadly: when two metrics use the same physical column with different aggregations (e.g. SUM(SALARY) and AVG(SALARY)), only the first keeps column_id; express the rest as formulas. See ../../shared/schemas/ts-model-conversion-invariants.md (I8).
• Complex expression → read ../../shared/mappings/ts-snowflake/ts-snowflake-formula-translation.md first, then translate SQL to ThoughtSpot formula using the Snowflake → ThoughtSpot reverse-translation sections; add to formulas[]. Do not classify as untranslatable based on SQL syntax recognition alone — patterns like NON ADDITIVE BY, OVER (PARTITION BY ...), and PARTITION BY EXCLUDING all have documented ThoughtSpot equivalents.
• No native TS equivalent but warehouse has the SQL function → use a ThoughtSpot pass-through (sql_*_op). Scalar pass-throughs (sql_string_op, sql_int_op, sql_double_op, etc.) are row-level and reliable — use freely. Aggregate pass-throughs (sql_*_aggregate_op) interact with TS query-time grouping — always flag for review. See ../../shared/schemas/ts-model-conversion-invariants.md (PT1).
• Untranslatable (confirmed after consulting reference) → omit and log in report

Model name: {semantic_view_name} (or user-specified). Do not add a TEST_SV_ or other prefix — see ../../shared/schemas/ts-model-conversion-invariants.md (N1).

Range joins (join_style: "range"): generate on with >= and <:

on: "[FROM::COL] >= [TO::START] and [FROM::COL] < [TO::END]"

ASOF joins (join_style: "asof"): equi columns use =; ASOF column uses >=.

Composite equi-joins: "[FROM::COL1] = [TO::COL1] and [FROM::COL2] = [TO::COL2]".

Filter labels (is_filter: true): create as boolean formula columns (ATTRIBUTE):

formulas:
- id: "formula_{name}"
  name: "{name}"
  expr: "if ( [TABLE::COL] >= 90000 ) then true else false"
  properties:
    column_type: ATTRIBUTE

Verified queries: after successful Model import, generate NLS Feedback TML from the verified_queries list. Translate SV SQL to search tokens using the column name mapping. Complex SQL (subqueries, CTEs) → log as "manual review needed".

CRITICAL — Never normalise names from API responses. Names that came from TS_EXPORT_TML (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. Any silent transformation will cause a lookup failure in the model TML (wrong referencing_join, wrong table.name, wrong column_id). When in doubt, copy the string character-for-character from the API response.

Step 7: Self-validation before import

Run this checklist against the model TML before showing the user anything. Fix every issue found before moving on. Do not present a "✗ problem found" to the user and then ask whether to proceed — the user cannot evaluate TML errors. Fix them silently and note the correction in the output.

Checklist — run each check in order:

Spotter enablement (ask before showing the review summary):

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

Apply the answer to model.properties.spotter_config.is_spotter_enabled. On in-place updates of an existing model, if the user does not answer explicitly, preserve the existing value rather than overwriting with a default.

After all checks pass, show the user:

Model ready to import: {view_name}

Self-validation: all checks passed

Tables ({n}):
  ✓ {FACT_TABLE}    — fact table, joins: [{DIM_TABLE} via {join_name}]
  ✓ {DIM_TABLE}     — dimension
  ...

Columns: {n} ATTRIBUTE, {n} MEASURE, {n} formulas

Formula translations:
  ✓ {name}: {sql} → {ts_formula}
  ⚠ {name}: OMITTED — {reason}

Spotter (AI search): enabled / disabled

Proceed with import?
  yes  — import to ThoughtSpot via TS_IMPORT_TML
  no   — cancel
  file — output TML in code blocks without importing (for environments where you lack
          DATAMANAGEMENT access, or to review the TML before committing)

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

If any check cannot be fixed (e.g. required join name not found in any exported TML), report specifically what is missing and what step to re-run to resolve it — do not attempt the import.

Step 7-FILE: Output TML (file-only mode)

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

1. Present the Model TML for copy-paste:

Display the full model TML YAML in a fenced code block labelled yaml:

```yaml
{full model TML content here}
```

2. Present any new Table TMLs (Scenario B only):

If new table TMLs were built in Step 4B, display each one in its own labelled code block:

```yaml
# {table_name}.table.tml
{full table TML content here}
```

3. Provide import instructions:

To import to ThoughtSpot when you have access:

  1. In the Snowsight Workspace, call the import procedure with each TML:

       -- Import table TMLs first (Scenario B only):
       CALL SKILLS.PUBLIC.TS_IMPORT_TML('{profile_name}', ARRAY_CONSTRUCT($$
       {table_tml_yaml}
       $$), FALSE);

       -- Then import the model TML:
       CALL SKILLS.PUBLIC.TS_IMPORT_TML('{profile_name}', ARRAY_CONSTRUCT($$
       {model_tml_yaml}
       $$), FALSE);

  2. Run with TRUE as the third argument first for a dry-run validation:
       CALL SKILLS.PUBLIC.TS_IMPORT_TML('{profile_name}', ARRAY_CONSTRUCT($$...$$), TRUE);

  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.

4. Proceed to Step 9 (Summary report) — include the formula translation log and column summary so the user has the full picture before importing manually.

Step 8: Import the model TML

IMPORTANT — Updating vs creating: Without a guid field in the TML, ThoughtSpot always creates a new object, even if a model with the same name already exists. To update an existing model, add guid at the document root — as the very first line, BEFORE the model: key:

guid: "{existing_model_guid}"   # MUST be at document root — NOT inside model:
model:
  name: "{view_name}"
  model_tables:
  # ...

guid under model: (i.e. model: { guid: ... }) is silently ignored — ThoughtSpot treats the import as a new object and creates a duplicate. Always place it at the top.

On the first import (new model), omit guid — it doesn't exist yet. After import, record the GUID from the response for all future updates.

Import via the stored procedure:

CALL SKILLS.PUBLIC.TS_IMPORT_TML('{profile_name}', ARRAY_CONSTRUCT($$
{model_tml_yaml}
$$), FALSE);

IMPORTANT: Use $$ dollar-quoting to preserve YAML formatting.

Validate first (dry-run), then import for real:

-- Call 1: validate only (discard the GUID — it is a temporary artifact)
CALL SKILLS.PUBLIC.TS_IMPORT_TML('{profile_name}', ARRAY_CONSTRUCT($$
{model_tml_yaml}
$$), TRUE);

-- Call 2: actual import (only after Call 1 succeeds)
CALL SKILLS.PUBLIC.TS_IMPORT_TML('{profile_name}', ARRAY_CONSTRUCT($$
{model_tml_yaml}
$$), FALSE);

On success, extract and display the created model GUID. Save it — you will need it if you reimport to fix any errors.

Common errors:

Step 9: Summary report

## Model Import Complete

**Model:** {view_name}
**GUID:** {guid}
**ThoughtSpot URL:** {base_url}/#/model/{guid}

### Columns Imported ({n})
| Display Name | Type | Source |
|---|---|---|
| {name} | ATTRIBUTE | {table_id}::{COL} |
| {name} | MEASURE ({agg}) | {table_id}::{COL} |
| {name} | MEASURE (formula) | translated from SQL |

### Formula Translation Log
| Column | Original SQL | Status | ThoughtSpot Formula |
|---|---|---|---|
| {name} | `{sql}` | ✓ Translated | `{ts_formula}` |
| {name} | `{sql}` | 🔄 Double aggregation | `{ts_formula}` |
| {name} | `{sql}` | 📐 Fact formula | `{ts_formula}` |
| {name} | `{sql}` | ⚠ Omitted | {reason} |

### Not Mapped
- Extension JSON (Cortex Analyst context): not translated to ThoughtSpot

### Facts Mapped ({n})
| Fact Name | Source Table | Expression | ThoughtSpot Formula |
|---|---|---|---|
| {name} | {table} | `{sql_expr}` | `{ts_formula}` |

### Identifier Resolution Summary
- Physical columns resolved: {n}
- Fact references resolved: {n}
- Double aggregation patterns: {n}
- Unresolvable references: {n} (see OMITTED above)

Mode C workflow — Update existing ThoughtSpot Model

Step C1: Identify both objects

Ask for:

• The Snowflake Semantic View (source — the updated version): database.schema.view_name
• The ThoughtSpot Model to update (target): GUID or search by name

The skill does not auto-match by name — always collect both explicitly.

Step C2: Fetch both in parallel

Fetch SV DDL:

CREATE OR REPLACE TEMPORARY TABLE SKILLS.TEMP.SV_DDL_C AS
SELECT GET_DDL('SEMANTIC_VIEW', '{database}.{schema}.{sv_name}') AS ddl_text;

SELECT ddl_text FROM SKILLS.TEMP.SV_DDL_C;

Export model TML:

CALL SKILLS.PUBLIC.TS_EXPORT_TML('{profile_name}', ARRAY_CONSTRUCT('{model_guid}'));

Parse the DDL using the standard Step 2 rules. Extract from the model TML:

• model.columns — keyed by column name, with description, synonyms (from properties.synonyms), formula_id, and column_id
• model.formulas — keyed by formula id, storing expr

Step C3: Compute change set

Compare the SV column set against the existing model columns:

• New columns — in SV, not in model → add fully with generated descriptions + synonyms
• Removed columns — in model, not in SV → flag only, never auto-delete
• Modified descriptions — SV comment ≠ model description → review per column (default: KEEP)
• Modified synonyms — SV with synonyms ≠ model properties.synonyms → review per column (default: MERGE — additive union)
• Modified expressions — formula expression changed → review per column (default: SKIP)

Normalise expressions before comparing: collapse whitespace, lowercase SQL keywords, preserve [bracket] and {brace} column refs verbatim.

Step C4: Present diff and collect decisions

Show a change-set summary, then per-section review tables. Require user to type done.

Removed columns — informational list only, no action column:

⚠ These columns exist in the Model but are no longer in the SV. They are NOT removed automatically. To remove safely, run /ts-dependency-manager first.

Modified descriptions — table with UPDATE / KEEP per row.

Modified synonyms — table with MERGE / UPDATE / KEEP per row.

• MERGE = union of existing and SV synonym sets (preserves coaching synonyms absent from the SV)
• UPDATE = replace with SV set entirely
• KEEP = ignore SV change

Modified expressions — side-by-side old / new formula; YES / SKIP per column.

Step C5: Build and import updated TML

Deep-copy the existing model TML. Apply only confirmed changes.

Rules:

• guid: must be at the document root — NOT nested inside model:
• Never touch ai_context fields on any column
• Never touch Data Model Instructions
• Never auto-delete removed columns

-- Write updated TML to stage, then import
CALL SKILLS.PUBLIC.TS_IMPORT_TML('{profile_name}', ARRAY_CONSTRUCT('{updated_tml_json}'), 'VALIDATE_AND_APPLY', FALSE, TRUE);

The no_create_new parameter (last argument) must be TRUE to update in place. The import will fail if the GUID is not found — surface the error and stop.

Step C6: Post-import coaching handoff

Always surface after a successful import:

✓ Model "{model_name}" updated.

⚠ May need review:
  Column AI Context   — {N} new columns added (no ai_context yet); {M} columns changed
                        → /ts-object-model-coach → surface 1
  Data Model Instructions — schema changes may affect Spotter defaults
                        → /ts-object-model-coach → surface 5
  Removed columns     — run /ts-dependency-manager before manual removal

Multiple semantic view conversion

After completing one conversion, offer to convert additional views.

• Session continuity: If the ThoughtSpot profile was already selected and validated earlier in this conversation, skip the profile selection and validation. For token auth, reuse the stored secret_value (tokens are valid for the session). For password auth, re-retrieve the credential only if the stored value is no longer available.
• Do not re-authenticate between views.
• Return to Step 1 (DDL fetch only) for the next view, skipping the profile SQL call.

Changelog