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, 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:

Why row 12 matters here in particular. On the CLI this invariant is enforced mechanically — ts snowflake build-model runs ts tml lint (I13) and exits 1. CoCo has no ts CLI, so this checklist IS the gate: an unresolvable bracket reference is parsed by ThoughtSpot as search tokens and the import fails with error_code 14516, Search did not find "formula_X" (live-verified 2026-07-30). It reached production on the CLI side for five weeks because nothing checked it (BL-178/BL-183) — do not skip the row.

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.

**

…(truncated)