TS Convert To Snowflake Sv

ThoughtSpot → Snowflake Semantic View

Convert a ThoughtSpot Worksheet or Model into a Snowflake Semantic View. Searches ThoughtSpot for available models, exports the TML definition, maps it to the Snowflake Semantic View DDL format, and creates it via CREATE OR REPLACE SEMANTIC VIEW.

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

References

Concept Mapping

DDL Format Reference

The output is a CREATE OR REPLACE SEMANTIC VIEW statement. Full structure:

CREATE OR REPLACE SEMANTIC VIEW {sv_name}
  tables (
    {DB}.{SCHEMA}.{TABLE} [primary key ({PK_COL})],
    ...
  )
  relationships (
    {left_table}_to_{right_table} as {LEFT_TABLE}({fk_col}) references {RIGHT_TABLE}({pk_col}),
    ...
  )
  dimensions (
    {TABLE}.{ALIAS} as {table_lower}.{PHYSICAL_COL} [with synonyms=('{display_name}')],
    {TABLE}.{ALIAS} as {SQL_EXPRESSION} [with synonyms=('{display_name}')],  -- formula dim
    ...
  )
  metrics (
    {TABLE}.{ALIAS} as {AGG}({table_lower}.{COL}) [with synonyms=('{display_name}')],
    {TABLE}.{ALIAS} non additive by ({TIME_TABLE}.{TIME_COL} {asc|desc} nulls last) as SUM({table_lower}.{COL}) [with synonyms=(...)],
    {TABLE}.{ALIAS} as DIV0({table_lower}.{metric_alias}, {table_lower}.{other_metric_alias}) [...],  -- ratio: reference metric aliases not raw aggregates
    ...
  )
  comment='{description}'
  with extension (CA='{ca_json}')

DDL rules:

• All non-metric columns (including dates, FK columns) go in dimensions(). There is no time_dimensions clause in the DDL — date classification lives only in the CA extension JSON.
• Metric expressions reference column aliases (lowercase, as defined in dimensions() or earlier metrics() entries), not raw physical column names. For ratio metrics, reference the previously-defined aggregated metric alias: DIV0(tbl.amount, tbl.quantity) — do not nest SUM() calls directly.
• Relationship names: {left_table}_to_{right_table} (lowercase). Disambiguate duplicates by appending the FK column: {left_table}_{fk_col}_to_{right_table}.
• Column alias format: TABLE_NAME.DESCRIPTIVE_ALIAS (uppercase, e.g. DM_ORDER.ORDER_ID). Reference the alias with lowercase table and alias: dm_order.ORDER_ID or dm_order.order_id.
• with extension (CA='...') is a JSON string that maps each table's columns into dimensions[], time_dimensions[], and metrics[] by alias name (lowercase). Required for Cortex Analyst to understand the semantic structure. Relationship names are also listed here.

CA extension JSON structure:

{
  "tables": [
    {
      "name": "dm_order",
      "dimensions": [{"name": "order_id"}, {"name": "fk_col"}],
      "time_dimensions": [{"name": "order_date"}],
      "metrics": [{"name": "employees"}]
    }
  ],
  "relationships": [
    {"name": "dm_order_to_dm_customer"}
  ]
}

For the full coverage matrix including unmapped properties, see ../../shared/mappings/ts-snowflake/ts-snowflake-properties.md.

Prerequisites

ThoughtSpot

• ThoughtSpot Cloud instance, REST API v2 enabled
• User account with DATAMANAGEMENT or DEVELOPER privilege
• Authentication configured — run /ts-profile-thoughtspot if you haven't already

Quick auth decision:

Can you log into ThoughtSpot in a browser (even via SSO)?
  YES → token_env   — get a token from Developer Playground (no admin needed)
  NO  → password_env or secret_key_env — see ts-profile-thoughtspot.md

Snowflake

• Role with CREATE SEMANTIC VIEW on the target schema — only required if creating live
• Connection configured — run /ts-profile-snowflake if you haven't already
• Not sure where to start? → Python connector + password auth has the fewest setup steps

No Snowflake access? You can still run this skill in file-only mode — it generates the CREATE OR REPLACE SEMANTIC VIEW DDL and writes it to a .sql file you can run manually in Snowsight later. Select FILE at the Step 10 checkpoint or say "file only" at any point before Step 12.

Step 0 — Overview

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

ts-convert-to-snowflake-sv — export a ThoughtSpot Worksheet or Model and create a matching Snowflake Semantic View.

Steps:

1. Authenticate (ThoughtSpot) ......................... auto

1.5. Choose session mode (A: single / B: split / C: update) . you choose 2. Find and select the model / worksheet .............. you choose 3. Export and parse the TML ........................... auto 4–6. Resolve physical tables + case sensitivity ......... auto 7. (Mode B only) Multi-domain analysis ................ you choose 8. Translate formulas ................................. auto 9. Build DDL via ts snowflake build-sv .............. auto 9.5C. (Mode C only) Diff against existing SV + confirm changes . you confirm 10. Checkpoint — review DDL before Snowflake execution .. you confirm 11. Validate the generated DDL ......................... auto 12. CREATE OR REPLACE SEMANTIC VIEW in Snowflake ........ auto 12b. Verify creation .................................... auto 13. Generate example test questions ..................... auto

File-only mode: at Step 10, choose FILE instead of executing — generates a .sql file for manual import in Snowsight.

Confirmation required: Step 10 (DDL review); Step 9.5C for 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 a ThoughtSpot profile was already confirmed earlier in this conversation (e.g. for a previous model in a batch), skip profile selection and reuse it.

Profile selection (first model only):

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.

Available ThoughtSpot profiles:
  1. Production — analyst@company.com @ myorg.thoughtspot.cloud
  2. Staging    — analyst@company.com @ myorg-staging.thoughtspot.cloud

Select a profile (or press Enter to use #1):

After the profile is confirmed, verify the connection:

ts auth whoami --profile {profile_name}

The CLI handles token caching, Keychain access, and expiry automatically. No temp files or manual token management needed in this skill.

If ts auth whoami returns 401, the token is expired. Direct the user to /ts-profile-thoughtspot (U3 — Refresh Credential) — that section is the canonical, cross-platform refresh procedure. Then clear the stale cache and retry:

ts auth logout --profile {profile_name}
ts auth whoami --profile {profile_name}

Step 1.5: Session Mode

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

Mode A (or press Enter): set session_mode = "single". Step 7 is skipped — the skill produces one SV regardless of how many domains are detected.

Mode B: set session_mode = "split". Step 7 runs automatically in SPLIT mode — the SPLIT/SINGLE/CUSTOM choice prompt is suppressed since the user already chose here.

Mode C: set session_mode = "update". After confirming the model in Step 2, also ask for the existing SV to update:

Existing Snowflake Semantic View to update:
  Enter database.schema.view_name or press Enter to browse: _______

Store {existing_sv_name}. The skill runs Steps 2–9 as a DDL dry run, then diverges at Step 9.5C (diff + review) before executing.

Step 2: Find and Select a Model or Worksheet

Present the following options to the user:

How would you like to find your model?
  G — I have a GUID
  S — Search (by name, author, tags, or a combination)
  B — Browse all

Option G — Direct GUID

If the user provides a GUID, skip search entirely. Store it as {selected_model_id}. The model name will be confirmed from the TML export in Step 3.

Option S — Search

Ask the user which filters to apply (they may provide any combination):

Enter search criteria (leave blank to skip):
  Name keyword:
  Tags (comma-separated):

Run the search using the CLI:

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

Omit --name if no keyword was supplied. The --all flag auto-paginates. --subtype WORKSHEET restricts results to worksheets and models only.

Zero results fallback: If the search returns zero results, retry without --name and apply case-insensitive substring filtering against metadata_name client-side.

Tags are supported via --tag <name-or-guid> (repeatable). If the user supplies tags, add --tag "<tag_name>" for each one.

Option B — Browse All

ts metadata search --profile {profile_name} --subtype WORKSHEET --all

Displaying Results

1. [WORKSHEET] Retail Sales WS            id: e61c7c4c-...
2. [WORKSHEET] TS: BI Server              id: eaab6de7-...

API subtype note: Both Worksheets and Models appear as type: WORKSHEET in the search response — there is no separate MODEL subtype. metadata_detail is frequently null and must not be relied on for subtype filtering. The actual TML format (worksheet vs model top-level key) is only determined after export in Step 3.

Store metadata_id as {selected_model_id} and metadata_name as {original_model_name}.

Step 3: Export the TML

Mode C only: run the following in parallel with the TML export to fetch the existing SV DDL:

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

Store the result as {existing_sv_ddl} — used in Step 9.5C.

ts tml export {selected_model_id} --profile {profile_name} --fqn --associated

Batch mode — export all models in one call:

When the user has selected multiple models for conversion (e.g. "convert all BIRD_ models"), pass all GUIDs to a single export call:

ts tml export {guid_1} {guid_2} --profile {profile_name} --fqn --associated --parse

--parse returns structured JSON directly — non-printable character stripping and YAML parsing are handled by the CLI. Separate by type field. Cache associated table TMLs by GUID — if two models share a physical table, the TML is returned once and should not be re-fetched for the second model.

Separate into:

• Primary object: parsed YAML has top-level key worksheet or model
• Table objects: parsed YAML has top-level key table
• SQL view objects: parsed YAML has top-level key sql_view — collect separately for handling in Step 5

Step 4: Identify TML Format

Step 5: Resolve Physical Table Names

to_snake(name) — naming convention used by ts snowflake build-sv: Lowercase, replace non-alphanumeric chars with _, strip leading/trailing _. Examples: "eye colour" → eye_colour, "# of Products" → of_products, "1st Quarter" → field_1st_quarter. The wrapper view naming in this step follows the same convention.

Build a map: logical_table_name → { database, schema, physical_table }.

From each Table TML object extract:

table:
  name: fact_sales       # map key
  db: ANALYTICS
  schema: PUBLIC         # accessed as tbl.get("schema") — NOT tbl.get("schema_")
  db_table: FACT_SALES

PyYAML field name: The schema field is "schema" in Python dicts after parsing — never "schema_". See ../../shared/schemas/thoughtspot-tml.md for details.

Schema is reliably exported: With export_fqn: true and export_associated: true, the schema value is present in Table TML whenever it is set in ThoughtSpot. If it appears missing, first verify with tbl.keys() — do not prompt the user until confirmed genuinely absent.

If db or schema is confirmed absent after inspection, ask the user to provide them. If a table has no associated TML, fetch it separately using its FQN GUID:

ts tml export {fqn_guid} --profile {profile_name} --fqn

Use TODO_DATABASE / TODO_SCHEMA placeholders for unresolved tables and flag them.

SQL view resolution: For every sql_view object referenced in model_tables[] (or table_paths[] for Worksheet format), classify its sql_query using the logic in ../../shared/schemas/thoughtspot-tml.md:

Simple — SELECT * FROM single_table [AS alias]:

• Extract the physical FQN from the FROM clause
• Resolve db, schema, db_table from the FQN
• Borrow column types from the matching physical table TML (run SHOW COLUMNS if no matching table TML exists)
• Treat the sql_view as a regular table for all subsequent steps
• Note it in the Unmapped Properties Report under "SQL Views resolved automatically"

Complex — anything else (WHERE, column list, JOIN, aggregation, subquery, UNION):

• Do not attempt auto-resolution

• At the Step 10 checkpoint, present the sql_query to the user and ask:

  sql_view "{name}" uses SQL that cannot be auto-mapped to a single physical table:
    {sql_query}
  
  How should this be handled?
    C — Create a Snowflake VIEW from this SQL in the target schema, then reference it
    M — Map to an existing Snowflake table or view (you provide the name)
    S — Skip — omit all columns sourced from this view
  

  • C (Create view): Before executing the semantic view CREATE, run:

    CREATE OR REPLACE VIEW {target_db}.{target_schema}.{to_snake(sv_name)} AS
    {sql_query};
    

    Then run SHOW COLUMNS IN VIEW {target_db}.{target_schema}.{view_name} to get column types. Reference the new view as base_table.table.

  • M (Map to existing): Ask for the fully-qualified Snowflake object name. Run SHOW COLUMNS on it to get column types. Use as base_table.

  • S (Skip): Omit all model columns whose column_id references this sql_view. Log each omitted column in the Unmapped Properties Report under "SQL Views skipped".

Case-sensitivity detection — connect to Snowflake now, before building the DDL:

This step requires a live Snowflake connection. Select the Snowflake profile and establish the connection now using the profile selection and auth logic described in Step 12 — do not wait until Step 12 to do this. The quoting decisions made here affect every column reference and every {DB}.{SCHEMA}.{TABLE} entry in the generated DDL. When Step 12 is reached, skip profile selection (already done) and proceed directly to target location selection.

Schema case — infer directly from the TML, no Snowflake query needed:

The schema field value exported by ThoughtSpot with export_fqn: true reflects exactly how the identifier was stored. If it is lowercase, it is case-sensitive and must be quoted:

def is_cs(identifier):
    return identifier != identifier.upper()

# Example: "financial" → case-sensitive (quoted); "PUBLIC" → case-insensitive (bare)
cs_schema = is_cs(tbl.get("schema", ""))
schema_ref = f'"{schema}"' if cs_schema else schema

No SHOW SCHEMAS call is needed.

Column case — use a single INFORMATION_SCHEMA.COLUMNS query per schema:

Instead of running one SHOW COLUMNS per table (N round-trips), run a single INFORMATION_SCHEMA.COLUMNS query that returns all columns for all tables at once. INFORMATION_SCHEMA stores names as they were created, so lowercase = case-sensitive.

Python connector (method: python):

table_names_sql = ", ".join(f"'{t.upper()}'" for t in all_physical_tables)
cur.execute(f"""
    SELECT table_name, column_name, data_type
    FROM {db}.INFORMATION_SCHEMA.COLUMNS
    WHERE table_schema = '{schema.upper()}'
      AND table_name IN ({table_names_sql})
    ORDER BY table_name, ordinal_position
""")
cs_columns = {}   # phys_table → set of case-sensitive column names
col_types = {}    # (phys_table, col_name) → data_type

for table_name, col_name, data_type in cur.fetchall():
    cs_columns.setdefault(table_name, set())
    if col_name != col_name.upper():          # lowercase → case-sensitive
        cs_columns[table_name].add(col_name)
    col_types[(table_name, col_name)] = data_type

Snowflake CLI (method: cli):

import subprocess, json

def snow_json(snow_cmd, cli_connection, query):
    r = subprocess.run(
        [snow_cmd, 'sql', '-c', cli_connection, '--format', 'json', '-q', query],
        capture_output=True, text=True
    )
    return json.loads(r.stdout)

table_names_sql = ", ".join(f"'{t.upper()}'" for t in all_physical_tables)
rows = snow_json(snow_cmd, cli_connection, f"""
    SELECT table_name, column_name, data_type
    FROM {db}.INFORMATION_SCHEMA.COLUMNS
    WHERE table_schema = '{schema.upper()}'
      AND table_name IN ({table_names_sql})
    ORDER BY table_name, ordinal_position
""")

cs_columns = {}
col_types = {}
for r in rows:
    tbl_name = r['TABLE_NAME']
    col_name = r['COLUMN_NAME']
    cs_columns.setdefault(tbl_name, set())
    if col_name != col_name.upper():
        cs_columns[tbl_name].add(col_name)
    col_types[(tbl_name, col_name)] = r['DATA_TYPE']

Note: INFORMATION_SCHEMA predicates are literal string comparisons — they do NOT normalise case. Query with the exact stored case. If the first probe returns zero rows, retry with UPPER(table_schema) = UPPER('{schema}') to handle case-insensitive schemas (stored uppercase) vs case-sensitive schemas (stored in original case).

Rule: lowercase column name in INFORMATION_SCHEMA.COLUMNS → the column is case-sensitive (created with a quoted identifier).

Apply quoting as follows:

primary_key and relationship_columns — Cortex Analyst conflict:

There is no single YAML format that satisfies both tools for case-sensitive columns in these two fields. SYSTEM$CREATE_SEMANTIC_VIEW_FROM_YAML requires '"id"'; Cortex Analyst rejects '"id"' with error 392700.

If any SHOW COLUMNS result returns lowercase column names that are used as join keys or primary keys, you MUST create uppercase wrapper views before generating the DDL. Do not proceed to Step 6 without resolving this:

# Detect whether wrapper views are needed
needs_wrapper = any(
    cs_cols_map.get(phys['db_table'], set())   # any cs columns in join-key tables
    for phys in phys_map.values()
)

If needs_wrapper is True:

1. Create a new uppercase schema: CREATE SCHEMA IF NOT EXISTS {db}.{TARGET_SCHEMA}_SV
2. For each physical table, create a view that uppercases all column names:

   CREATE OR REPLACE VIEW {db}.{TARGET_SCHEMA}_SV.{TABLE_NAME} AS
   SELECT "col1" AS COL1, "col2" AS COL2, ...
   FROM {db}."{schema}"."{table}";
   

3. Update phys_map to point at the new schema and uppercase table/column names
4. All identifiers used in the DDL will then be bare uppercase — no quoting needed anywhere

Execute these DDL statements using the same method as the column queries above.

Python connector — run wrapper view DDL in parallel:

from concurrent.futures import ThreadPoolExecutor, as_completed

def exec_ddl(connection_factory, ddl):
    conn = connection_factory()
    conn.cursor().execute(ddl)
    conn.close()
    return ddl.split('\n')[0][:60]  # first line for progress output

ddl_list = [
    f"CREATE OR REPLACE VIEW {db}.{TARGET_SCHEMA}_SV.{VIEW_NAME} AS SELECT ...",
    # one entry per physical table
]

with ThreadPoolExecutor(max_workers=min(len(ddl_list), 8)) as pool:
    futures = {pool.submit(exec_ddl, connection_factory, d): d for d in ddl_list}
    for f in as_completed(futures):
        print(f"  Created: {f.result()}")

Snowflake CLI — write all DDL to one file, execute in a single call:

with open("/tmp/sv_wrappers.sql", "w") as f:
    f.write(";\n".join(ddl_list) + ";")
subprocess.run([snow_cmd, 'sql', '-c', cli_connection, '-f', '/tmp/sv_wrappers.sql'],
               capture_output=True, text=True)
import os; os.remove("/tmp/sv_wrappers.sql")

See /ts-profile-snowflake for connection setup, and ts snowflake exec for running SQL from a file without hand-rolling the connector/CLI calls above.

See ../../shared/schemas/snowflake-schema.md — Known Snowflake Semantic View Limitations for full details.

Step 6: Build Path → Table Map (Worksheet format only)

Skip for Model format.

From worksheet.table_paths[], build: path_id → table_alias.

table_paths:
- id: fact_sales_1    # path_id used in column_id references
  table: fact_sales   # resolves to this table alias

Step 7: Multi-Domain Analysis

Mode gate:

• Mode A (session_mode = "single") — skip this step entirely. Proceed to Step 8.
• Mode C (session_mode = "update") — skip this step entirely. Proceed to Step 8.
• Mode B (session_mode = "split") — run this step and enter SPLIT mode automatically. Set split_mode = True immediately; suppress the SPLIT/SINGLE/CUSTOM choice prompt since the user already chose Mode B at Step 1.5.

Trigger (Mode B only): Run whenever model_tables[] contains ≥2 tables and at least one join exists. Skip (proceed to Step 8) if the model has 0 or 1 fact tables.

Algorithm:

1. Build a directed join graph from model_tables[].joins[]:

• Nodes: every table in model_tables[]
• Directed edges: one edge per join, pointing from the FK table (source — the table whose joins[] array declared the join) to the PK table (target)

2. Identify fact tables and dimension tables:

• Fact table: any table with ≥1 outbound edge (it appears as the source of at least one join)
• Dimension table: any table with zero outbound edges (only ever a join target)

3. Check the fact-table count:

• 0 or 1 fact table → skip this step. Proceed to Step 8 as normal.
• ≥2 fact tables → continue.

4. For each fact table, traverse its reachable dimension set via BFS through its outbound joins. A dimension table is "reachable from fact F" if you can reach it by following directed edges from F.

5. Identify shared dimensions: any dimension table reachable from 2+ distinct fact roots. Dimensions reachable from only one fact belong exclusively to that domain.

6. Present the domain map to the user:

I detected {N} logical domains in this model:

  Domain 1 — {FACT_TABLE_ROOT}
    Fact tables:  {list}
    Dimensions:   {list; flag shared dims with "(shared)"}

  Domain 2 — {FACT_TABLE_ROOT}
    Fact tables:  {list}
    Dimensions:   {list; flag shared dims with "(shared)"}

  Shared dimensions (included in each view if you split): {list}

How would you like to proceed?
  SPLIT   — Create {N} separate Semantic Views (one per domain)
  SINGLE  — Create one combined Semantic View (current behaviour)
  CUSTOM  — I'll assign tables to groups manually

7a. SINGLE: Set split_mode = False. Proceed to Step 8 with no change in scope.

7b. SPLIT: Set split_mode = True. For each domain:

• domain.tables = fact table(s) + all reachable dimensions (including shared ones, duplicated into every domain that reaches them)
• Default domain.sv_name = {model_name}_{snake_case(primary_fact_table)} (e.g. model sales_inventory, fact DM_ORDER → sales_inventory_dm_order). User may rename at the Step 10 checkpoint.
• Proceed to Step 8 and run it once per domain in sequence.

7c. CUSTOM: Display a numbered list of all tables. User types group assignments, for example: 1,2,3 → Group A; 4,5,6 → Group B. Validate that each group forms a connected subgraph (every table reachable from at least one other table in its group). If a group is disconnected, ask the user to revise. Treat each group as a domain and proceed as SPLIT.

Cross-domain formula columns (split mode only):

A formula whose expression references column IDs from tables in multiple domains cannot be cleanly split. Assign it to the domain containing the most of its referenced tables. Log it in the Unmapped Properties Report under a new section:

#### Cross-Domain Formulas (assigned to primary domain)
| Formula | Assigned To | References tables in |
|---|---|---|
| {name} | {domain_name} | {other_domain_name} |

Step 8: Translate Formulas

MANDATORY — read the reference before assessing any formula: Open ../../shared/mappings/ts-snowflake/ts-snowflake-formula-translation.md and use its Decision Flowchart to classify every formula. Do not classify a formula as untranslatable based on function name recognition alone. Patterns that appear ThoughtSpot-specific have documented Snowflake equivalents — for example:

Looks untranslatable	Actually translatable as
last_value(agg, query_groups(), {date_col})	SUM(col) + non_additive_dimensions on the date table
sum(group_aggregate(sum(m), {attr}, query_filters()))	Plain SUM(m) — outer sum + query_filters() simplifies
sum(group_aggregate(sum(m), query_groups(), query_filters()))	Plain SUM(m)
safe_divide(sum(m), [NamedMetric]) where NamedMetric is same measure at coarser grain	DIV0(tbl.metric, SUM(tbl.metric) OVER (PARTITION BY dim.COL)) — contribution ratio pattern

| group_aggregate(sum(m), {attr}, query_filters() + {region='east'}) | SUM(CASE WHEN t.REGION = 'east' THEN t.M END) — an additive hardcoded filter is translatable (corrected 2026-08-26, finding 13.9, live-verified). Only filters that suppress query filters ({}, {attr='v'} alone, {attr}, query_filters() - {...}) remain untranslatable |

Consult the reference. Never reason from first principles about ThoughtSpot functions.

For each formula column (formula_id is set in model.columns[]):

1. Look up formula expression from formulas[] by id or name
2. Resolve column references using the syntax rules for the TML format (Worksheet uses [path_id::col], Model uses [TABLE::col])
3. Classify using the Decision Flowchart in the formula translation reference, then translate using the rules in that file
4. Handle nested references up to 3 levels deep

Write the translated formulas to a JSON file for ts snowflake build-sv:

{
  "formula_id_1": {"expr": "SUM(table.col)", "kind": "metric"},
  "formula_id_2": {"expr": "CONCAT(t.first, ' ', t.last)", "kind": "dimension"}
}

Each entry: formula_id (matching the formulas[].id in the TML) → {expr, kind}. kind is "metric" or "dimension" based on the column's properties.column_type. Untranslatable formulas are omitted from this file — build-sv skips any formula column whose formula_id is not in the translated map and reports it as skipped.

Confirmed untranslatable patterns: do not maintain a static list here — the set of untranslatable patterns evolves independently of this skill. Consult the "Untranslatable Patterns", "Untranslatable LOD Patterns", and "Untranslatable Semi-Additive Patterns" sections of ts-snowflake-formula-translation.md for the current, authoritative list before omitting any column.

Step 9: Build Semantic View DDL (ts snowflake build-sv)

Split mode: If split_mode = True (set in Step 7), run this step once per domain. For each domain, filter the exported Model TML to include only the domain's tables in model_tables[] and only columns whose column_id prefix is in domain.tables. Write the filtered TML to a separate JSON file before running build-sv.

Model format — use ts snowflake build-sv:

ts snowflake build-sv \
  --model {export_dir}/model.json \
  --tables-dir {export_dir}/ \
  --sv-name {target_database}.{target_schema}.{sv_name} \
  --output {sv_name}.sql \
  --formulas {formulas.json}

Omit --formulas if the model has no formula columns (or all formulas are untranslatable).

What build-sv handles (codified in sv_build_sv.py):

• column_id resolution → physical column names via Table TML
• Column classification (dimension / time_dimension / metric) using data type and column_type properties
• Relationship parsing (on expressions) and naming with collision avoidance
• Aggregation mapping (SUM, COUNT, COUNT_DISTINCT, AVG, MIN, MAX, etc.)
• Metric topological ordering (derived metrics after their dependencies)
• Alias deduplication across the entire view
• Synonym and comment emission
• CA extension JSON generation (dimensions, time_dimensions, metrics per table)
• Full DDL assembly (CREATE OR REPLACE SEMANTIC VIEW)

The command writes the DDL to --output and prints a JSON summary to stdout:

{
  "sv_name": "DB.SCHEMA.MY_SV",
  "ddl_file": "my_sv.sql",
  "dimensions": 12,
  "time_dimensions": 3,
  "metrics": 8,
  "relationship_count": 4,
  "skipped_formulas": 1,
  "dropped_join_attrs": 2,
  "unmapped_properties": 5
}

Capture this summary for the Step 10 conversion summary. Skipped formulas and dropped join attributes are also reported on stderr.

Worksheet format — build-sv does not yet support worksheet TML:

If the exported TML is worksheet format (top-level key worksheet), build-sv cannot process it. Follow the manual procedure:

1. Build a path_id → table_alias map from worksheet.table_paths[]
2. Resolve joins from Table TML joins_with[] entries
3. For each worksheet_columns[] entry, resolve column_id via the path map, classify as dimension/time_dimension/metric using ../../shared/mappings/ts-snowflake/ts-to-snowflake-rules.md
4. Assemble the DDL manually following the DDL Format Reference at the top of this file

connections.yaml — do not consult proactively. Only if Snowflake returns a column-not-found error after execution should you check connections.yaml, where external_column may override db_column_name for a given column.

TML temp file cleanup — do this after build-sv completes:

rm -f /tmp/ts_tml_*.json {formulas.json}

Step 9.5C: Diff Against Existing SV — Mode C Only

Skip this step for Modes A and B. Run only when session_mode = "update".

Parse the existing SV DDL (fetched in Step 3C) using the same logic as ../../shared/mappings/ts-snowflake/ts-from-snowflake-rules.md to extract its current column set, expressions, and descriptions.

Compute the change set (ts snowflake diff)

The comparison itself (SQL-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. Write both column maps to temp JSON files shaped {"expr": ..., "description": ...} per column (synonyms aren't tracked on the to-side, so omit that key):

import json

def _cols_for_diff(cols: dict) -> dict:
    return {
        name: {"expr": data["expr"], "description": data.get("description", "")}
        for name, data in cols.items()
    }

with open("/tmp/ts_sv_diff_existing.json", "w") as f:
    json.dump(_cols_for_diff(existing_sv_parse["columns"]), f)
with open("/tmp/ts_sv_diff_generated.json", "w") as f:
    json.dump(_cols_for_diff(generated_sv["columns"]), f)

ts snowflake diff --current /tmp/ts_sv_diff_existing.json --new /tmp/ts_sv_diff_generated.json
rm -f /tmp/ts_sv_diff_*.json

Parse the printed change_set JSON from stdout:

{
  "new_columns":           ["..."],
  "removed_columns":       ["..."],
  "modified_expressions":  [{"column": "...", "current": "...", "new": "..."}],
  "modified_descriptions": [{"column": "...", "current": "...", "new": "..."}],
  "modified_synonyms":     []
}

modified_synonyms will always be empty here since neither side supplies synonym data — ignore that key. removed_columns still needs per-column user confirmation (below); everything else is display-only input to the next section.

Present the diff and collect decisions

=== Change set for "{existing_sv_name}" ===

  ✚ New columns:              {N}   (will be added)
  ✖ Removed columns:          {M}   (confirm each — unchecked = keep in new SV)
  ~ Modified expressions:     {R}   (will be updated — review before confirming)
  ✏ Modified descriptions:    {P}   (will be updated automatically)
  = Unchanged columns:        {T}   (no change)

Removed columns — require per-column confirmation. Pre-fill all as unchecked (keep):

These columns are in the current SV but not in the updated Model.
Confirm removal from the SV? (unchecked = keep in the new SV DDL)

  [ ] {col_name}  — currently: {existing_expr}
  ...

Unchecked columns are re-added verbatim from the existing SV DDL — they are preserved even if the Model no longer includes them.

Modified expressions — show old and new side-by-side. Require YES / SKIP per column; do not bulk-apply.

Descriptions are applied automatically — no per-column confirmation needed.

Require the user to type done after reviewing before proceeding.

Build the final DDL

Assemble from the reviewed changes:

• All new columns (from generated DDL)
• All unchanged columns (from generated DDL)
• Confirmed-removed columns: omit
• Unchecked (kept) removed columns: carry forward from existing SV DDL verbatim
• Modified expressions confirmed YES: use generated DDL value
• Modified descriptions: use generated DDL value

Replace the generated_sv_ddl variable with this assembled DDL before Step 10.

⚑ Step 10: CHECKPOINT — Review with User

Do not proceed without explicit user confirmation.

Mode C: show the assembled (diff-reviewed) DDL and a diff summary instead of the full conversion summary. The prompt becomes:

Shall I apply these changes to {existing_sv_name} in Snowflake?
  ✚ {N} columns added, ✖ {M} removed, ~ {R} expressions updated, ✏ {P} descriptions updated

  YES  — proceed (CREATE OR REPLACE)
  NO   — cancel
  FILE — write the final DDL to a file without executing

Single mode (split_mode = False): present the following three sections.

Split mode (split_mode = True): present one labelled block per domain (Domain 1, Domain 2, …). Each block contains the three sections below for that domain. At the end, show the combined prompt once covering all domains.

1. Generated DDL — full CREATE OR REPLACE SEMANTIC VIEW statement in a SQL code block.

Split mode: label each block — e.g. ### Domain 1 — sales_inventory_dm_order. Include the domain.sv_name as the view name; remind the user they may rename it before creating.

2. Conversion Summary:

- Tables:          {n}
- Relationships:   {n}
- Dim

…(truncated)