| name | ts-convert-from-databricks-mv |
| description | Convert or import a Databricks Metric View into ThoughtSpot as a Model. Use when Databricks is the source and the goal is a ThoughtSpot Model — whether migrating Databricks metrics and semantic definitions into ThoughtSpot or making a Metric View available for Spotter and search-based analytics. Direction is always Databricks → ThoughtSpot. Not for ThoughtSpot → Databricks, standalone DDL generation, or adding AI context to existing ThoughtSpot models. |
Databricks Metric View → ThoughtSpot Model
Converts a Databricks Unity Catalog Metric View into a ThoughtSpot Model. Reads the
Metric View YAML definition via DESCRIBE TABLE EXTENDED, maps dimensions and measures
to ThoughtSpot columns and formulas, translates SQL expressions, and imports the result
via ts tml import.
Two scenarios are supported:
- Scenario A (existing tables): ThoughtSpot Table objects already exist for the
Databricks source table(s) the Metric View references. Reuses those existing Table objects.
- Scenario B (new tables): No ThoughtSpot Table objects exist yet for the Databricks
source table(s). Creates new Table objects pointing to those objects.
References
| File | Purpose |
|---|
| ../../shared/mappings/ts-databricks/ts-from-databricks-rules.md | Databricks MV YAML parsing, type mapping, formula translation, column classification |
| ../../shared/mappings/ts-databricks/ts-databricks-formula-translation.md | SQL → ThoughtSpot formula translation rules (bidirectional reference) |
| ../../shared/mappings/ts-databricks/ts-databricks-properties.md | Property coverage — what maps, what doesn't, what is partially migrated |
| ../../shared/schemas/databricks-metric-view.md | Databricks Metric View YAML schema (v0.1 single-source, v1.1 multi-source) |
| ../../shared/schemas/thoughtspot-tml.md | TML export parsing (PyYAML pitfalls, type detection) |
| ../../shared/schemas/thoughtspot-table-tml.md | Table TML structure, connection reference, data types, import patterns, common errors |
| ../../shared/schemas/thoughtspot-model-tml.md | Model TML structure, join scenarios, formula visibility, self-validation checklist |
| ../../shared/schemas/thoughtspot-sql-view-tml.md | SQL View TML structure — sql_view: type for subquery sources |
| ../../shared/schemas/thoughtspot-formula-patterns.md | ThoughtSpot formula syntax, all function categories, LOD/window patterns, YAML encoding rules |
| ../ts-profile-databricks/SKILL.md | Databricks auth methods, profile config, CLI usage |
| ../ts-profile-thoughtspot/SKILL.md | ThoughtSpot auth methods, profile config, CLI usage |
| ../../shared/worked-examples/databricks/ts-to-databricks.md | End-to-end Dunder Mifflin conversion: LOD dimensions, semi-additive, cross-measure ratios, multi-fact split |
| ../../shared/worked-examples/databricks/ts-from-databricks.md | End-to-end MV → Model conversion: direct + computed dimensions, simple + ratio + window + conditional measures |
| ../../shared/worked-examples/databricks/ts-from-databricks-sql-view.md | Subquery source path: SQL View TML + Model on top |
Concept Mapping
| Databricks Metric View YAML | ThoughtSpot Model |
|---|
source: (table FQN, SQL query, or another metric view — 4 forms, see schema) | Table FQN → Single Table TML (db_table, db, schema decomposed from the FQN); SQL query → see source: as SELECT subquery row below; another metric view → fail loud (MV-on-MV chaining is not supported) |
Top-level comment: (v1.1) | Model description |
dimensions[].expr (direct column reference) | columns[] with column_type: ATTRIBUTE |
dimensions[].expr (computed expression) | formulas[] entry with translated expression + columns[] with formula_id reference |
dimensions[].expr (window function — LOD) | LOD formulas[] entry: group_aggregate(agg([col]), {[dim]}, query_filters()) — 3 args required. Live-verified 2026-07-09 (docs/audit/2026-07-09-dbx-semantic-claim-matrix.md, A1/A2): this is filter-aware on TS under both filter kinds, and reproduces a Databricks MV's own global filter: — but NOT an ad hoc query-time WHERE on an MV with no global filter (DBX-side asymmetry, not fixable by formula), unless the emitted formula uses {} instead of query_filters() paired with a model-level filters: block mirroring the MV's filter: — that combination reproduces both DBX conditions (A3 follow-up, same matrix, live-verified 2026-07-09) |
dimensions[].display_name (v1.1) | Column name (display name) |
dimensions[].comment (v1.1) | Column description |
dimensions[].synonyms (v1.1) | properties.synonyms[] + properties.synonym_type: USER_DEFINED |
measures[].expr (simple AGG(col) — SUM, AVG, MIN, MAX, COUNT) | columns[] with column_type: MEASURE + extracted aggregation |
measures[].expr (COUNT(DISTINCT col)) | formulas[] entry: unique count ( [TABLE::col] ) — NOT aggregation: COUNT_DISTINCT on a column_id (TS silently overrides to ATTRIBUTE) |
measures[].expr (complex — ratios, nested aggregates) | formulas[] entry with translated expression + columns[] with formula_id reference |
measures[].expr with MEASURE()/ANY_VALUE() | Cross-measure formula — inline the referenced expressions (cross-refs fail during TML import). Live-verified 2026-07-09 across query grain (docs/audit/2026-07-09-dbx-semantic-claim-matrix.md, B1) — CONFIRMED true ratio-of-sums, cross-platform, at every grain; no grain caveat needed |
measures[].window, order: raw date (semi-additive) | last_value ( sum ( [m] ) , query_groups ( ) , { [date] } ) / first_value ( ... ) — snapshot metrics (inventory, balance). Live-verified 2026-07-09, docs/audit/2026-07-08-dbx-window-claim-matrix.md C7. Also live-verified 2026-07-09 under a query-time date-range filter (docs/audit/2026-07-09-dbx-semantic-claim-matrix.md, D1) — CONFIRMED cross-platform, collapses to last/first-in-filtered-range on both platforms |
measures[].window, order: truncated period (period filter), no offset | sum ( [m] ) at the query grain — flow metrics (revenue, qty). Live-verified 2026-07-09, matrix C6 |
measures[].window, order: truncated period, offset: -N <unit> | moving_sum ( [m] , N , -N , [date] ) — row-relative LAG(N) idiom, NOT a wall-clock filter; valid only when the query returns exactly one row per period. Live-verified 2026-07-09 at month grain, N=1 (matrix C6/C6a); quarter/year grains and N>1 are Deferred (C8) extrapolations of the same idiom. Corrects the pre-2026-07-09 sum_if(diff_months/quarters/years([date], today())=N, [m]) mapping, which was WRONG for any multi-period query |
measures[].window with range: trailing N day (default/exclusive) | moving_sum([m], N, -1, [date]) — rolling look-back window, anchor excluded. Live-verified 2026-07-09, matrix C1/C2. Density caveat (E1): row-positional — matches only when the order column is dense at the window's unit grain (one row per unit, no gaps); see docs/audit/2026-07-09-dbx-semantic-claim-matrix.md (E1) |
measures[].window with range: trailing N day inclusive | moving_sum([m], N-1, 0, [date]) — anchor included. Live-verified 2026-07-09, matrix C1. Same E1 density caveat as above |
measures[].window with range: leading N day (default/exclusive) | moving_sum([m], -1, N, [date]) — rolling look-ahead window, anchor excluded. Live-verified 2026-07-09, matrix C3. Same E1 density caveat as above |
measures[].window with range: leading N day inclusive | moving_sum([m], 0, N-1, [date]) — anchor included. Live-verified 2026-07-09, matrix C3. Same E1 density caveat as above |
measures[].window with range: cumulative | cumulative_sum([m], [date]). Live-verified 2026-07-09, matrix C5 |
measures[].window with range: all | group_aggregate(sum([m]), {partition dims}, query_filters()), column_type: ATTRIBUTE — unbounded partition window, scoped per query partition. Live-verified 2026-07-09, matrix C4. Inherits the same A1/A2 filter asymmetry as the LOD row above (docs/audit/2026-07-09-dbx-semantic-claim-matrix.md), including its A3 {} + model-filter refinement |
measures[].window with inclusive/exclusive anchor modifier | Default is exclusive, confirmed. Applies only to trailing/leading. Live-verified 2026-07-09, matrix C1/C2/C3 |
measures[].expr with FILTER (WHERE cond) | agg_if ( cond , [x] ) — native *_if conditional aggregate (e.g., sum_if, unique_count_if) |
COUNT(*) | Formula: count ( 1 ) |
fields[] (GA alias for dimensions[]) | Same mapping as dimensions[] above — fields: is checked first, dimensions: is the fallback |
| Growth % (MoM, QoQ, YoY) | Inline sum([m]) and moving_sum([m], N, -N, [date]) expressions for both periods — cross-formula refs not supported during TML import |
joins: (nested hierarchy) | One Table TML per source; model joins[] from parent→child hierarchy |
joins[]."on" or joins[].using (exactly one present) | on → join expression as-is; using: [COL, ...] → [A::COL] = [B::COL] (AND-joined for multiple columns) |
filter: (any) | Boolean formula column [MV Filter] — users apply [MV Filter] = true. Always create, never description-only. Live-verified 2026-07-09 (docs/audit/2026-07-09-dbx-semantic-claim-matrix.md, C1): filter ordering is CONFIRMED cross-platform — a model-level filters: block filters rows before a windowed measure computes, matching a Databricks MV's own global filter:. Frame semantics on windowed measures still DIVERGE on gapped data — see the density caveat on the trailing/leading rows above (E1) |
Subquery in expr | Untranslatable — log in Unmapped Report |
source: as SELECT subquery (parenthesized (SELECT ...) or bare SELECT .../WITH ...) | Prompt user: (D) create Databricks VIEW, (T) create ThoughtSpot SQL View, (M) map to existing, (S) skip |
source: as another metric view (MV-on-MV) | Fail loud — not supported; ask the user to convert the upstream MV first or flatten the chain in Databricks |
version: | Drives parsing path (v0.1 vs v1.1) — not stored in ThoughtSpot |
Key structural rules:
column_id must use the column name from the ThoughtSpot Table TML. Export
Table TMLs to confirm — do not assume they match the Metric View column names.- Simple measures (
AGG(col) — one column, one aggregate) → MEASURE column.
Complex expressions → formulas[] entry.
- In Scenario A, the Table TML already exists — reuse its GUID and column names.
- In Scenario B, create a new Table TML from the Databricks schema introspection.
- v1.1 MVs provide
display_name, comment, and synonyms — map display_name to
column name, comment to description, and synonyms to properties.synonyms
(with properties.synonym_type: USER_DEFINED). Synonyms at the column root level
are silently dropped on import. v0.1 MVs only have name and expr.
- LOD dimensions (with
AGG() OVER (PARTITION BY ...)) map to ThoughtSpot
group_aggregate() formulas — always 3 arguments: group_aggregate(expr, {query}, query_filters()).
- Cross-measure references (
MEASURE(name), ANY_VALUE(dim)) must be inlined as
full expressions during TML import — [name] cross-references fail during import
(open-items #4). After import, users can simplify formulas in the ThoughtSpot UI.
- Duplicate column_id: when the same physical column appears as both an ATTRIBUTE
dimension and a MEASURE (e.g.,
COUNT(col)), convert the measure to a formula to
avoid the "unique column_id" import error (open-items #2).
Prerequisites
ThoughtSpot
- ThoughtSpot Cloud instance, REST API v2 enabled
- User account with
DATAMANAGEMENT or DEVELOPER privilege — only required for import
- Authentication configured — run
/ts-profile-thoughtspot if you haven't already
- The
ts CLI installed (pip install -e /path/to/tools/ts-cli)
No ThoughtSpot import access? You can still run this skill in file-only mode —
it generates the Table and Model TML files for you to import manually. Select FILE
at the Step 10 checkpoint or say "file only" at any point before Step 11.
Databricks
- Databricks workspace with Unity Catalog enabled
- SQL warehouse on the Preview channel — Metric Views are a Preview feature;
Current channel warehouses return
PARSE_SYNTAX_ERROR
- Databricks CLI installed and profile configured — run
/ts-profile-databricks if
you haven't already
- A Databricks connection configured in ThoughtSpot (required for Scenario B table creation)
CLI-first rule — no inline Python for TML operations
Every ThoughtSpot API call, TML generation, and model import in this skill must go
through a ts CLI command. Do not write inline Python scripts to export/merge/import TML,
iterate over formula failures, or assemble model JSON. If a CLI command fails or produces
wrong results, fix the CLI (tools/ts-cli/) and re-run — do not work around it with
manual scripting.
Step 0 — Overview
On skill invocation, display this plan before doing any work:
ts-convert-from-databricks-mv — convert a Databricks Metric View into a ThoughtSpot Model, translating dimensions, measures, and SQL expressions.
Steps:
- Authenticate (ThoughtSpot + Databricks) .............. auto
- List Metric Views in the catalog ..................... auto
- Select a Metric View ................................. you choose
- Fetch the Metric View definition ..................... auto
- Parse the YAML (dimensions, measures, filter) ........ auto
- Map to ThoughtSpot columns + translate expressions .... auto
- Table registration question (reuse or create) ........ you choose
- Discover / create ThoughtSpot Table objects ........... auto (may ask for clarification)
- Build Table + Model TML — ts databricks build-model .... auto
9.5. Confirm Spotter enablement (re-run build-model + flag) . you choose
- Review checkpoint — inspect TML before import ......... you confirm
- Import Model TML — ts databricks build-model --profile . auto
- Verify import and produce summary report .............. auto
File-only mode: at Step 10, choose FILE to write TML files for manual import.
Confirmation required: Steps 3, 7, 9.5, 10
Auto-executed: all others
Ready to start? [Y / N]
Do not begin Step 1 until the user confirms.
Workflow
Step 1: Authenticate
Session continuity: If profiles were already confirmed earlier in this conversation
(e.g. for a previous Metric View), skip this step and reuse them.
ThoughtSpot profile:
- Run
ts profiles list to show configured profiles.
- If multiple profiles: display a numbered list and ask the user to select one.
- If exactly one profile: display it and confirm before proceeding.
- Verify:
ts auth whoami --profile {name} — print display_name and base URL.
Databricks profile:
Load the profile from ~/.claude/databricks-profiles.json:
import json, os
profiles_path = os.path.expanduser("~/.claude/databricks-profiles.json")
with open(profiles_path) as f:
profiles = json.load(f)
for i, p in enumerate(profiles, 1):
print(f" {i}. {p['name']} ({p.get('host', '')})")
profile = next(p for p in profiles if p["name"] == "{profile_name}")
dbx_profile = profile["dbx_profile"]
catalog = profile.get("default_catalog", "")
warehouse_path = profile.get("sql_warehouse_http_path", "")
warehouse_id = warehouse_path.rstrip("/").split("/")[-1] if warehouse_path else ""
Verify connectivity:
source ~/.zshenv && databricks auth describe --profile {dbx_profile}
Store dbx_profile, catalog, and warehouse_id for use in subsequent steps.
SQL execution pattern
All Databricks SQL in this skill uses the Statement Execution API:
source ~/.zshenv && databricks api post /api/2.0/sql/statements \
--profile {dbx_profile} \
--json '{"warehouse_id": "{warehouse_id}", "statement": "{sql}", "wait_timeout": "50s"}'
The response contains:
{
"status": {"state": "SUCCEEDED"},
"manifest": {"schema": {"columns": [...]}},
"result": {"data_array": [[...], ...]}
}
If status.state is PENDING, poll the statement ID:
source ~/.zshenv && databricks api get /api/2.0/sql/statements/{statement_id} --profile {dbx_profile}
Step 2: List Metric Views
Query the catalog for available Metric Views:
SELECT table_catalog, table_schema, table_name
FROM system.information_schema.tables
WHERE table_type = 'METRIC_VIEW'
AND table_catalog = '{catalog}'
Execute via the SQL execution pattern above. Display results as a numbered list:
Metric Views in {catalog}:
1. {schema}.{view_name_1}
2. {schema}.{view_name_2}
...
If no Metric Views are found, check:
- The catalog name is correct
- The SQL warehouse is on the Preview channel
- The user's role has
USE CATALOG and USE SCHEMA grants
Step 3: Select a Metric View
If the user has already named a Metric View, skip this step.
Otherwise, ask the user to select from the list displayed in Step 2 (enter a number
or type a fully qualified catalog.schema.view_name directly).
Store the selected {catalog}, {schema}, and {view_name} for subsequent steps.
Step 4: Fetch the Metric View definition
Retrieve the YAML definition via DESCRIBE TABLE EXTENDED:
DESCRIBE TABLE EXTENDED {catalog}.{schema}.{view_name}
Execute via the SQL execution pattern. Parse the response result.data_array — each
row is [col_name, data_type, comment, metadata].
Extract the definition:
- Find the row where
col_name == 'View Text' — the data_type column contains
the full YAML string.
- Find the row where
col_name == 'Type' — confirm data_type == 'METRIC_VIEW'.
- Store the YAML string for parsing in Step 5.
If the query fails with "table or view not found", verify the fully-qualified name
and confirm the user's role has SELECT on the view.
Step 5: Parse the YAML — ts databricks parse-mv
Parse the YAML string extracted in Step 4 with the deterministic parser (schema:
../../shared/schemas/databricks-metric-view.md):
printf '%s' "$MV_YAML" > mv.yaml
source ~/.zshenv && ts databricks parse-mv mv.yaml --output parsed.json
The command handles version routing (0.1 / 1.1), fields:/dimensions: aliasing,
dimension/measure classification (including window: and its 5 range values),
the nested joins: walk (on/using, cardinality/rely precedence), the
materialization: block, and metadata pass-through. It prints WARNING: lines
(e.g. BL-098 density checks) on stderr.
Exit 1 with unsupported[]: the JSON is still written; each entry names the
construct and why. Show the list to the user and ask whether to skip this MV
(log to the Unmapped Report) or handle the construct manually. Never continue
silently past a non-empty unsupported[].
Read parsed.json and branch on source.kind:
sql_query — the source is a SELECT subquery. Present the (D / T / M / S)
options below.table_fqn with needs_live_check: true — run the MV-on-MV live check
below before treating it as a physical table. The same check applies to every
joins[].source.
Present these options to the user:
| Option | Action |
|---|
| (D) Create Databricks VIEW | Execute CREATE VIEW catalog.schema.view_name AS {source} in Databricks, then use the new view as a regular table FQN. Re-enter Step 5 with the new FQN. |
| (T) Create ThoughtSpot SQL View | Build a sql_view: TML that runs the source SQL against the Databricks connection, import it, then build a Model on top. See Step 5T below. |
| (M) Map to existing | User provides an existing ThoughtSpot Table/View name — skip table creation, proceed to Model. |
| (S) Skip | Log the MV to the Unmapped Report and continue to the next MV. |
If the user selects (T), proceed to Step 5T. Otherwise continue with the
selected option. For (D), (M), and (S), the rest of Step 5 proceeds as normal
(or skips).
Step 5T — Build a ThoughtSpot SQL View from a subquery source:
See ../../shared/schemas/thoughtspot-sql-view-tml.md
for the full TML reference.
-
Read source.raw and filter from parsed.json, then construct the SQL query:
sql_query = parsed["source"]["raw"]
if parsed.get("filter"):
sql_query = f"SELECT * FROM ({parsed['source']['raw']}) _mv WHERE {parsed['filter']}"
-
Introspect the query columns. Execute the query with LIMIT 0 via the Statement
Execution API to retrieve the column schema without returning data:
SELECT * FROM ({sql_query}) _cols LIMIT 0
Parse manifest.schema.columns from the response — each entry has name and
type_name.
-
Build the sql_view: TML:
sql_view:
name: "{model_name}"
description: "SQL View for Databricks MV {mv_fqn}. Source: {source_fqn}"
connection:
name: "{databricks_connection_name}"
sql_query: "{sql_query}"
sql_view_columns:
- name: "{col_name}"
sql_output_column: "{col_name}"
properties:
column_type: ATTRIBUTE
-
Import via ts tml import --profile {profile} --policy PARTIAL --create-new.
Record the returned GUID.
-
Continue to Step 6 (mapping) — treat the SQL View as the source table. Column
references in the Model TML use [SQL_VIEW_NAME::column] syntax. The Model's
model_tables entry references the SQL View name and GUID.
See ../../shared/worked-examples/databricks/ts-from-databricks-sql-view.md
for a complete worked example.
MV-on-MV detection (when source.kind is table_fqn): an FQN-shaped source:
cannot be assumed to be a physical table — it may be another metric view. Query:
SELECT table_type FROM system.information_schema.tables
WHERE table_catalog = '{src_catalog}' AND table_schema = '{src_schema}'
AND table_name = '{src_table}'
If table_type = 'METRIC_VIEW', fail loud — do not build a Table TML against it.
Report to the user:
The Metric View's source ('{source_fqn}') is itself a Metric View, not a physical
table. Chained (MV-on-MV) sources are not supported by this skill yet. Convert
'{source_fqn}' on its own first, or flatten the source chain in Databricks before
retrying.
Log the MV to the Unmapped Report and stop processing it. This same check applies
to joins[].source values.
Version routing and classification are handled by parse-mv — both versions
normalize into one parsed.json shape.
See ../../shared/mappings/ts-databricks/ts-from-databricks-rules.md
(v1.1 Multi-Source Parsing section) for background reading on the join-walk
semantics parse-mv implements.
Step 6: Translate expressions — ts databricks translate-formulas
1. Author tables.json — the alias-path → ThoughtSpot-table-name map:
- Key
"source" (required) plus one key per join alias; nested joins use
dot-joined paths from the root ("orders.customers").
- Value: the ThoughtSpot Table object name. Derive the initial name from the
FQN's table part upper-cased (
analytics.ecommerce.transactions →
TRANSACTIONS); Step 8 confirms it against the real objects and enriches
these values with GUIDs.
{"source": "TRANSACTIONS", "orders": "DM_ORDER", "orders.customers": "DM_CUSTOMER"}
2. Translate:
source ~/.zshenv && ts databricks translate-formulas \
--input parsed.json --tables tables.json --output translated.json
The command encodes the full mapping surface — the function map from
../../shared/mappings/ts-databricks/ts-databricks-formula-translation.md,
the window decision tree from
../../shared/mappings/ts-databricks/ts-from-databricks-rules.md,
conditional aggregates, LOD group_aggregate (3-arg with query_filters()),
COUNT(DISTINCT) → unique count, and cross-measure inlining. Every
dimension/measure lands in translated[] or skipped[] with a reason —
review both.
3. Review the output with the user:
skipped[] — each entry needs a decision: accept the omission, or build
the formula manually per the reason text (e.g. range: all needs a
partition-dimension judgment call — the reason names the manual recipe).annotations[] — surface every sparse_data_risk (BL-098: Databricks
date-interval windows vs ThoughtSpot row-positional moving_sum; numbers
match only on data dense at the window grain), pending_verification (C8
grain/offset extrapolations), one_row_per_period, and
lod_filter_asymmetry note. These feed the Step 10 ⚠ WINDOW markers.- The
filter entry (when present) becomes the model-level filter in Step 9.
Semantic caveats (live-verified):
Density caveat (E1, live-verified 2026-07-09 on gapped data — see
docs/audit/2026-07-09-dbx-semantic-claim-matrix.md). Databricks'
trailing/leading N day windows are genuine date-interval windows;
ThoughtSpot's moving_sum/moving_average are row-positional — the two are
indistinguishable on dense daily data, but diverge on data with date gaps.
Treat any translated[] entry carrying a sparse_data_risk annotation as an
approximation that matches Databricks only when the order column is dense at
the window's unit grain (one row per unit, no gaps); run a density check on
any source with possible gaps before accepting the translation.
Filter-asymmetry caveat (A1/A2/A3, live-verified 2026-07-09 — same matrix).
The range: all / LOD group_aggregate(..., query_filters()) mapping is
filter-aware for a Databricks MV's own global filter:, but filter-blind to
an ad hoc query-time WHERE on an MV with no global filter — unless the
third argument is {} instead of query_filters(), paired with a
model-level filters: block mirroring the MV's filter: (live-tested
2026-07-09, A3 follow-up): that combination is filter-blind to a
search-level pin and filter-aware of the model filter, reproducing both
Databricks conditions in one formula. Every entry carrying a
lod_filter_asymmetry annotation needs this judgment call made explicitly
with the user.
Deferred grains note (C8). The range: current + offset: -N <unit>
row-relative LAG(N) mapping is live-verified at month grain (N=1) only;
quarter/year grain offsets remain Deferred (C8 — see
docs/audit/2026-07-08-dbx-window-claim-matrix.md). Entries carrying a
pending_verification annotation at those grains need this caveat surfaced
to the user before the translation is treated as final.
Step 7: Table registration question
After mapping, display the source table(s) and ask:
v0.1 (single source):
The Metric View references 1 source table:
{catalog}.{schema}.{table_name}
Is this table already registered in ThoughtSpot?
Y Yes — use existing ThoughtSpot Table object
N No — create a new Table object from scratch
? Not sure — search ThoughtSpot first
Enter Y / N / ?:
v1.1 (multi-source with joins):
List all source tables (fact table from source: + all dimension tables from joins:)
and ask the same question for each.
- Y → skip search, go to Step 8A (column verification only)
- N → skip search, go to Step 8B (create)
- ? → go to Step 8A (search + verify)
Step 8A: Discover and verify existing ThoughtSpot Table objects (Y and ? paths)
Skip this step if the user answered N in Step 7 — go directly to Step 8B.
Choose the search scope first. A whole-instance scan is the slow path — on a
large instance --all pulls every table. Offer the narrower option and search by
table-name pattern (--name), never --all-then-filter:
How should I search for these tables?
C Within a specific connection — fastest; search that one connection's tables
I Entire ThoughtSpot instance — broader, slower
Enter C / I :
Search by name (both scopes start here):
source ~/.zshenv && ts metadata search --subtype ONE_TO_ONE_LOGICAL --name "%{table_name}%" --profile {profile}
- C (within a connection) → first identify the connection using the
N (name it) / F (filter by substring) / L (list all) prompt in Step 8B — present that
prompt and let the user choose; do NOT run
ts connections list and dump every
connection by default. Then keep only results whose metadata_header.dataSourceName
equals the chosen connection name (each result carries its connection there, e.g.
"APJ_DBX"). Fastest, and unambiguous when the same table name exists on several
connections.
- I (entire instance) → run the name search above with no connection filter.
Filter the JSON to match the MV source table by table name (metadata_name) and, for
the connection scope, metadata_header.dataSourceName; use
metadata_header.database_stripes / metadata_header.schema_stripes to disambiguate
same-named tables. Build a map: physical_table_name -> {metadata_id, metadata_name}.
Only fall back to --all (fetch every table) when no usable name pattern can be
formed (e.g. the name is too generic). Tell the user that cost before running it.
Export TMLs for found tables to verify columns:
source ~/.zshenv && ts tml export {guid1} {guid2} ... --profile {profile} --parse
--parse returns structured JSON — access columns via item["tml"]["table"]["columns"]
directly. Parse table.columns[].name from each returned item. Build a column map:
table_name -> [col_name, ...]. Compare against the columns referenced in the MV
dimensions and measures to identify any column gaps.
The column_id in the model TML must use the column names from the ThoughtSpot
Table TML — export the TMLs to confirm them.
Confirm the plan before making any changes:
Table Plan:
✓ {TABLE_NAME} — found (GUID: {guid}) — all {n} columns present → use as-is
⚠ {TABLE_NAME} — found (GUID: {guid}) — missing {n} columns: {COL_A}, {COL_B} → update
✗ {TABLE_NAME} — not found in ThoughtSpot → create new
Actions to be taken:
• Update {TABLE_NAME}: add {n} missing columns
• Create {TABLE_NAME}: {n} columns from Databricks schema
No changes have been made yet. Proceed? (yes/no):
Do not proceed until the user confirms. If any table is not found, follow Step 8B
for those tables. If any table has missing columns, follow Step 8C before building
the model.
Enrich tables.json: upgrade each value from a name string to an object
carrying the ThoughtSpot GUID, e.g.
{"source": {"name": "TRANSACTIONS", "fqn": "<guid>"}}. Step 9's
build-model stamps these GUIDs as model_tables[].fqn. For file-only
runs (Step 10-FILE) where new tables were needed but NOT created, instead set
"create": true with db/schema/db_table and a columns list of
{"name", "dbx_type"} from the DESCRIBE TABLE output — build-model then
writes a {name}.table.tml alongside the model.
Step 8B: Create ThoughtSpot Table objects (Scenario B) — also the connection picker for the Step 8A connection-scoped search
Get all column names and types for the source table:
DESCRIBE TABLE {catalog}.{schema}.{table_name}
Execute via the SQL execution pattern. The response data_array contains rows
[col_name, data_type, comment]. Map Databricks types to ThoughtSpot types using
the data type mapping in
../../shared/mappings/ts-databricks/ts-from-databricks-rules.md.
Choose how to identify the connection — don't dump the full list by default. A long
connection list is noise when the user already knows the one they want. Ask first:
How would you like to choose the ThoughtSpot connection?
N Name it — type the exact connection name; I'll use it directly
F Filter — give a partial string; I'll list only connections that match
L List all — show every connection and pick by number
Enter N / F / L:
Then fetch the connections once (auto-paginated, returns all of the specified type):
source ~/.zshenv && ts connections list --type DATABRICKS --profile {profile}
Resolve the user's choice against that result:
- N (name it) — match the typed name against the returned
name values
(case-sensitive). Exactly one match → use it. No match → show the closest names and
re-ask. Don't fabricate a name the list doesn't contain — the table TML needs the exact,
case-sensitive connection name.
- F (filter) — keep connections whose
name contains the string (case-insensitive),
show them as a short numbered list (name, type, database), and pick from that. One match
→ auto-select and confirm; none → widen the string or switch to L.
- L (list all) — show the full numbered list and pick by number.
If only one connection exists in total, auto-select it and confirm regardless of the choice.
Use the exact name value from the API response in the table TML.
No suitable connection? A ThoughtSpot connection only sees catalogs its Databricks
credentials are granted. If no existing connection can see the source catalog, table
creation fails with "Database … does not exist in connection". Creating a Databricks
connection is out of this skill's scope — Databricks connections authenticate with a
Personal Access Token or OAuth (M2M), not key-pair, so there is no ts connections create
path for them (that is BL-036). Stop and direct the user to create the connection in the
ThoughtSpot UI (credentials per /ts-profile-databricks), then resume on this step and
select it. Do not trial-and-error existing connections.
Create the ThoughtSpot Table object:
cat tables-spec.json | source ~/.zshenv && ts tables create --profile {profile}
Where tables-spec.json is a JSON array built from the column data. See
ts tables create --help for the spec format. This command handles JDBC retry and
GUID resolution automatically, and outputs {name: guid}.
Both db and schema must be non-empty in every table spec entry. ThoughtSpot
requires the fully qualified three-part path (catalog.schema.table) even if the
connection has a default catalog configured. Passing an empty db or schema causes
"Fully qualified table mapping missing".
Record the created GUID for use in the model TML.
Enrich tables.json: upgrade each value from a name string to an object
carrying the ThoughtSpot GUID, e.g.
{"source": {"name": "TRANSACTIONS", "fqn": "<guid>"}}. Step 9's
build-model stamps these GUIDs as model_tables[].fqn. For file-only
runs (Step 10-FILE) where new tables were needed but NOT created, instead set
"create": true with db/schema/db_table and a columns list of
{"name", "dbx_type"} from the DESCRIBE TABLE output — build-model then
writes a {name}.table.tml alongside the model.
Step 8C: Update existing tables with missing columns
For each table from Step 8A with a column gap, introspect the Databricks schema
for the missing columns:
DESCRIBE TABLE {catalog}.{schema}.{table_name}
Filter the result to the missing column names. Map Databricks types to ThoughtSpot
types using ../../shared/mappings/ts-databricks/ts-from-databricks-rules.md.
Find the ThoughtSpot connection for the table:
source ~/.zshenv && ts connections list --type DATABRICKS --profile {profile}
Add the missing columns to the connection, then re-import the updated Table TML
(batch all imports in one call):
source ~/.zshenv && ts tml import --policy ALL_OR_NONE --profile {profile}
After import, re-export the updated TMLs to refresh the column map before Step 9.
Step 9: Build the Model TML — ts databricks build-model
Model name: {view_name_title_case} — derived from the Metric View name.
Ask the user if they want a different name. Do not add a TEST_MV_ or other
prefix — see ../../shared/schemas/ts-model-conversion-invariants.md (N1).
source ~/.zshenv && ts databricks build-model \
--parsed parsed.json --translated translated.json --tables tables.json \
--connection "{connection_name}" --model-name "{model_name}" \
--mv-fqn "{catalog}.{schema}.{view_name}" --output-dir ./tml_out
The command assembles the Model TML (columns, formulas, joins, the model-level
filters: block when the MV has a filter:, properties:), assembles Table
TML for any create: true entries, validates the hard TML invariants
(db_column_name on every table column, name:-only connection blocks,
guid: at document root, formula/column formula_id pairing, no
aggregation: in formulas[]) and runs the ts tml lint checks (I1/I2/I4/
I5/I8) — exit 1 names the specific field on any finding. It prints a summary
JSON on stdout; keep it for Step 10.
Filter handling is automatic: the MV filter: becomes an MV Filter
ATTRIBUTE formula plus a model-level filters: block (oper: in,
values: ['true']) — the live-verified emulation of the MV's global filter
(A1, docs/audit/2026-07-09-dbx-semantic-claim-matrix.md). If the filter was
skipped at translate time it appears in skipped[] — resolve it with the user
before importing (an unfiltered model silently changes every number).
Live-verified 2026-07-09 (see docs/audit/2026-07-09-dbx-semantic-claim-matrix.md,
A1) — this model-level filters: approach is the CONFIRMED correct emulation of
the MV's own global filter:: on ThoughtSpot, a model-level filters: block makes
LOD/window formulas (e.g. group_aggregate(..., query_filters())) filter-aware
identically to a query-level pin, matching the DBX MV's global-filter: condition
exactly. It does not, and cannot, reproduce a DBX consumer's ad hoc query-time
WHERE on an MV with no global filter — that DBX condition is filter-blind for
LOD/window dimensions, a source-side asymmetry, not a ThoughtSpot gap.
build-model emits these correctly; they matter when you hand-edit TML:
id must equal name exactly (same case, same characters). ThoughtSpot resolves
with and on join references against the table's actual name — if id differs
in case, joins fail with "{table_name} does not exist in schema". Use the exact
ThoughtSpot table object name for both id and name.id values must be unique across all model_tables entriesname values must also be unique — ThoughtSpot rejects models where two tables
share the same name value ("Multiple tables have same alias")
Step 9.5: Spotter enablement
Ask whether Spotter (AI search) should be enabled. Default is yes.
Enable Spotter (AI search) for this model? [Y / n] (default: Y)
Re-run the Step 9 build-model command appending --spotter-enabled (or
--no-spotter-enabled) — assembly is deterministic and cheap; the flag adds
properties.spotter_config.is_spotter_enabled to the model TML. When
updating an existing model (--existing-guid) and the user does not
explicitly answer: export the existing model first (ts tml export {guid} --profile {profile} --parse), read its current
properties.spotter_config.is_spotter_enabled, and pass the matching flag —
never overwrite a live setting with a default.
Step 10: Review checkpoint
Before importing, show the user a summary built from the Step 9 build-model summary
JSON (and translated.json/parsed.json for the formula log): tables from tables[]
(alias/name/fqn), column lists from columns.attributes/columns.measures,
formula translations from translated.json's translated[]/skipped[] entries,
window markers from window_measures[] (name, ts_expr, each annotation's detail
as the assumption line), and Spotter from spotter_enabled:
Model to import: {view_name}
Source: {catalog}.{schema}.{view_name} (Databricks Metric View v{version})
Filter: {parsed.json "filter" expr if summary filter_applied else "none"}
Tables:
✓ {tables[].name} (fqn: {tables[].fqn}) — alias: {tables[].alias}
Columns ({n} total):
ATTRIBUTE: {columns.attributes}
MEASURE: {columns.measures}
Formulas: {formula_count} formula(s)
Formula translations:
✓ {name}: {mv expr from parsed.json} → {ts_expr} # translated[]
⚠ {name}: OMITTED — {reason} # skipped[]
Window measures (review required):
⚠ WINDOW {name}: {ts_expr}
Assumption: {annotation.detail}
If any window measures exist, display this warning:
⚠ Window measures assume daily grain (one row per day for trailing/rolling).
Verify that the source data matches this assumption — if the table has
multiple rows per day, moving_sum/moving_average will over-count.
Spotter (AI search): enabled / disabled
Proceed with import?
yes — import to ThoughtSpot
no — cancel
file — write TML files without importing (for environments where you lack
DATAMANAGEMENT access, or to review the TML before committing)
Wait for user confirmation before proceeding.
If the user selects file, skip to Step 10-FILE.
Step 10-FILE: Output TML files (file-only mode)
This path is used when the user selected file at the Step 10 checkpoint, explicitly
said "file only", or has no ThoughtSpot DATAMANAGEMENT access.
1. Files are already on disk — Step 9's build-model --output-dir wrote
{model_name}.model.tml (and {table_name}.table.tml for every
create: true table). Nothing to generate here.
2. Report:
TML files written:
{model_name}.model.tml — ThoughtSpot Model TML
{table_name}.table.tml — ThoughtSpot Table TML (if new tables were needed)
To import to ThoughtSpot when you have access:
1. Package all .tml files into a zip:
zip {model_name}_tml.zip *.tml
2. In ThoughtSpot: Data → TML Import → upload the zip
(table TMLs will import first, then the model)
3. Or import via CLI:
ts tml import --file {model_name}.model.tml --policy ALL_OR_NONE --profile {profile}
Note: On first import, omit `guid` from the TML (already omitted here). ThoughtSpot
will assign a GUID — save it from the import response if you need to update the model later.
3. Proceed to Step 12 (Produce summary report) — include the formula translation log
and column summary so the user has the full picture before importing.
Pre-import validation gate
Before any ts tml import, run the mandatory lint gate — see
../../shared/schemas/ts-tml-import-gate.md
for the invariant list (I1/I2/I4/I5/I8), the stdin command, and the
update-vs-create guid and import-policy rules. Do not import until
ts tml lint reports "clean": true.
Step 11: Import the model
IMPORTANT — Updating vs creating: without --existing-guid, ThoughtSpot
always creates a new object, even when a model with the same name exists.
To update in place, pass the existing model's GUID — build-model stamps it
as a top-level guid: alongside model: (a guid: nested inside model: is
silently ignored). On first import omit it; record the returned GUID.
source ~/.zshenv && ts databricks build-model \
--parsed parsed.json --translated translated.json --tables tables.json \
--connection "{connection_name}" --model-name "{model_name}" \
--mv-fqn "{catalog}.{schema}.{view_name}" --output-dir ./tml_out \
{--spotter-enabled|--no-spotter-enabled} --profile {profile}
With --profile, the command imports the model TML via
ts tml import --policy PARTIAL after a clean lint, and reports
import_status and model_guid in the summary JSON. Save the GUID —
required for any future update import. On import_status: "failed" read
import_error and consult the table below.
Common import errors:
| Error | Likely cause | Fix |
|---|
column_id not found | Column name is wrong — MV dimension name used instead of ThoughtSpot Table TML column name | Export Table TML and verify column names |
Compulsory Field ... joins(N)->with is not populated | Missing with field on an inline join | Add with: {target_id} to every inline join entry |
{table_name} does not exist in schema (on with field) | with value doesn't match any id in model_tables | Ensure with matches the target's id exactly — same case as name |
Invalid srcTable or destTable in join expression | on clause references a table name that doesn't match any id | Check that both [table::col] refs in on use id values |
Multiple tables have same alias {name} | Two model_tables entries have the same name value | Deduplicate — keep only one entry |
fqn resolution failed | GUID is stale or from a different ThoughtSpot instance | Re-run Step 8A to get fresh GUIDs |
formula syntax error | ThoughtSpot formula has invalid syntax | Fix the formula expression |
YAML mapping error on formula with { | Formula with { [col] } emitted as inline YAML string | The CLI's YAML emitter quotes { correctly; this arises only in hand-edited TML |
| YAML parse error | Non-printable characters in strings | Strip non-printable chars from all string values before serialising |
Step 11b: Verify Import
After a successful import response, confirm the model was indexed and has the expected
shape — not just that the API returned 200.
1. Search for the model by GUID:
source ~/.zshenv && ts metadata search --subtype WORKSHEET --name "%{view_name}%" --profile {profile}
The GUID returned by the import response must appear in the results. If it is absent,
the import succeeded at the API level but indexing is delayed — wait 5 seconds and
retry once.
2. Export the imported model and count columns:
source ~/.zshenv && ts tml export {created_guid} --fqn --profile {profile}
Parse the returned TML and count model.columns[] entries. This count must be >= the
number of translatable fields from the MV (total dimensions + measures, minus any
entries in translated.json's skipped[]).
If the column count is lower than expected: compare the exported TML against the TML
sent in Step 11 to identify which columns ThoughtSpot silently dropped, and investigate.
3. Report the model URL:
Model imported successfully.
Name: {view_name}
GUID: {created_guid}
URL: {base_url}/#/model/{created_guid}
Open the URL in a browser to verify the model appears in the ThoughtSpot Data panel.
Step 12: Produce summary report
After a successful import (or file output), generate:
## Model Import Complete
**Model:** {view_name}
**GUID:** {created_guid}
**ThoughtSpot URL:** {base_url}/#/model/{created_guid}
**Source:** {catalog}.{schema}.{view_name} (Databricks Metric View v{version})
**Filter:** {filter_expr or "none"}
### Columns Imported ({n})
| Display Name | Type | Source |
|---|---|---|
| {name} | ATTRIBUTE | {TABLE}::{COL} |
| {name} | MEASURE ({agg}) | {TABLE}::{COL} |
| {name} | MEASURE (formula) | translated from SQL |
| ... | ... | ... |
### Formula Translation Log
| Column | Original Databricks SQL | Status | ThoughtSpot Formula |
|---|---|---|---|
| {name} | `{expr}` | ✓ Translated | `{ts_formula}` |
| {name} | `{expr}` | ⚠ Omitted | {reason} |
### Not Mapped
- Global filter: "{filter_expr}" — noted in model description, not enforced as a ThoughtSpot filter
- MV `version` field — metadata only, not stored in ThoughtSpot
Test questions: Suggest 3-5 natural language questions the user can try in Spotter
to verify the model works. Base them on the dimensions and measures present:
### Suggested test questions for Spotter
1. "What is the total {measure_1} by {dimension_1}?"
2. "Show me {measure_2} for each {dimension_2}"
3. "What are the top 10 {dimension_1} by {measure_1}?"
Step 13: Cleanup
Remove any temporary files written during the workflow:
rm -f /tmp/ts_model_build_*.yaml /tmp/ts_model_build_*.json
The ts CLI manages its own token cache — do not remove /tmp/ts_token_*.txt
unless the user explicitly requests a logout.
Multiple Metric View conversion
After completing Step 12 for one view, ask:
"Convert another Metric View?" If yes: return to Step 2. Reuse the already-confirmed
ThoughtSpot and Databricks profiles. Do not re-authenticate between views.
Changelog
| Version | Date | Summary |
|---|
| 1.8.2 | 2026-07-11 | Databricks MV schema: parameters: GA + tiered runtime requirement (16.4/17.3/18.1/18.2) documented (audit 13.1/13.10). |
| 1.8.1 | 2026-07-10 | Pre-import lint gate extracted to shared ts-tml-import-gate.md (BL-063 PR5) — content unchanged, now linked. |
| 1.8.0 | 2026-07-10 | Steps 5/6/9/9.5/10/11 rewired onto ts databricks parse-mv / translate-formulas / build-model; tables.json v2 |
| 1.7.0 | 2026-07-09 | Dimension/metric semantic deep-dive (BL-063 PR1.5, docs/audit/2026-07-09-dbx-semantic-claim-matrix.md). New capability (A3, the MINOR): group_aggregate's {} filter argument, paired with a model-level filters: block mirroring the source MV's filter:, reproduces BOTH halves of the A1/A2 DBX composite (MV-filter:-aware AND query-time-WHERE-blind) in a single ThoughtSpot construct — corrects A1/A2's "no TS analogue" conclusion. query_filters() remains the default LOD mapping; {} + a mirrored model filter is the new option for reproducing a DBX consumer's ad hoc query-time-WHERE-blind LOD. Subtraction form query_filters() - {col} tested and found not to exclude a filter pinned on a derived boolean formula — recorded, not adopted. Corrections/caveats: LOD query_filters() dimension confirmed filter-aware on TS under both filter kinds, with the caveat that the equivalence holds for a Databricks MV's own global filter: only, not for a consumer's ad hoc query-time WHERE (A1/A2, refined by A3 above); cross-measure ratio inlining confirmed grain-safe at every grain, no caveat needed (B1); global filter: × window ordering confirmed filter-before-window on both platforms, with a new frame-semantics caveat — Databricks trailing/leading windows are date-interval framed while moving_sum is row-positional, so results diverge on sparse/gapped data (C1, same root cause as E1); semi-additive last/first_value under a date-range filter confirmed cross-platform (D1). |
| 1.6.0 | 2026-07-09 | Window semantics live-verified against a Databricks fixture + ThoughtSpot number-match (docs/audit/2026-07-08-dbx-window-claim-matrix.md, C1–C7/C6a); resolves the previously-PENDING leading/all cases — new capability, not just a correction. CORRECTED: range: trailing N day (default/exclusive) — was moving_sum([m], N, 0, [date]) (actually reproduces trailing (N+1) day inclusive), now moving_sum([m], N, -1, [date]) (C1); range: current + offset: -N <unit> — was wall-clock sum_if(diff_months/quarters/years([date], today())=N, [m]), now row-relative moving_sum([m], N, -N, [date]) LAG idiom, valid only with one row per period (C6/C6a; quarter/year grains and N>1 Deferred per C8). RESOLVED (was PENDING): range: leading N day (default/exclusive) → moving_sum([m], -1, N, [date]) (C3); range: all → group_aggregate(sum([m]), {partition dims}, query_filters()) (C4); inclusive/exclusive anchor modifier default confirmed exclusive (C2). CONFIRMED unchanged: range: cumulative → cumulative_sum([m], [date]) (C5); semiadditive: last/first → last_value/first_value(...) (C7). Adds trailing/leading inclusive variants (moving_sum([m], N-1, 0, [date]) / moving_sum([m], 0, N-1, [date])). |
| 1.5.4 | 2026-07-03 | Product-currency fixes (audit 2026-07-03, findings 13.5/13.6/13.7): widen source: detection to catch the bare (unparenthesized) SQL form, not just (SELECT ...); add MV-on-MV fail-loud detection for source:/joins[].source; fix the join-walk snippet to handle using: [COL, ...] (previously assumed every join had "on", which would KeyError) and the spec's many_to_one default when neither rely: nor cardinality: is present; recognise (but flag PENDING LIVE VERIFICATION) the range: leading/range: all window values and the inclusive/exclusive anchor modifier. |
| 1.5.3 | 2026-06-17 | Connection step: add explicit "no suitable connection → stop & instruct" guidance — a connection only sees catalogs its credentials are granted; if none fits, table creation fails with "Database does not exist in connection". Creating a Databricks connection is out of scope (PAT/OAuth, not key-pair — no ts connections create path; tracked as BL-036): direct the user to the ThoughtSpot UI, then resume and select it. Don't trial-and-error connections. (Sibling to the Snowflake/Tableau create-connection change.) |
| 1.5.2 | 2026-06-17 | Replace the hand-written pre-import grep gate with ts tml lint (parser-based; now also catches I8 duplicate column_id). From the full audit sweep (codification, angle 11). |
| 1.5.1 | 2026-06-16 | Extend the N/F/L connection prompt into the Step 8A connection-scoped search path. The 8A "C — within a connection" path now explicitly presents the Step 8B N (name it) / F (filter by substring) / L (list all) prompt to identify the connection — it must NOT run ts connections list and dump every connection by default. Mirrors the same fix in ts-convert-from-tableau and ts-convert-from-snowflake-sv. |
| 1.5.0 | 2026-06-16 | Connection selection (Step 8B): add a how-to-identify-the-connection prompt (N name it / F filter by partial string / L list all) before dumping the full connection list. Fetch once via ts connections list --type DATABRICKS, then use the typed name directly, show a filtered subset, or show the full numbered list. Single connection still auto-selects. Mirrors the same prompt added to ts-convert-from-tableau and ts-convert-from-snowflake-sv. |
| 1.4.0 | 2026-06-16 | Step 8A table discovery: add a connection-scoped vs instance-wide search choice and search by --name "%table%" pattern instead of --all-then-filter. Connection scope filters results on metadata_header.dataSourceName (verified field). Avoids slow whole-instance scans on large instances. Mirrors the ts-convert-from-tableau Step 4c change. |
| 1.3.0 | 2026-06-12 | Adopt PT1 pass-through policy (scalar reliable; flag aggregate pass-through for review). |
| 1.2.0 | 2026-06-12 | Add pre-import validation gate (I1/I2/I4/I5) before model TML import (BL-001). |
| 1.1.0 | 2026-06-11 | Preserve existing Spotter setting on in-place model updates (don't reset to default). Drop TEST_MV_ prefix — model name uses the bare MV name (N1); cite canonical conversion invariants doc. |
| 1.0.0 | 2026-05-22 | Initial release — single conversion mode (Mode A) |
