TS Object Model Aggregates

ThoughtSpot: Aggregate Model Advisor

ThoughtSpot 26.6 lets a primary Model declare associated aggregate Models via an aggregated_models TML block — Search/Spotter/Liveboard queries transparently route to a smaller, pre-aggregated table when its columns fully satisfy the query, cutting scanned rows by orders of magnitude (docs). Deciding which aggregates to build is manual today — this skill automates the analysis:

1. Mines the Model's dependent Liveboards and Answers into normalized query signatures (what dimensions, date grain, filters, and measures each one uses).
2. Generalizes those signatures up a grain lattice and ranks candidate aggregate grains by weighted coverage.
3. Optionally profiles candidates against a live warehouse (or emits SQL for manual profiling) to replace coverage estimates with actual compression ratios.
4. Presents a marginal-gain curve — diminishing returns as more aggregates are added — and lets you pick the cut-off. Maintenance-cost judgment stays human.
5. Generates the warehouse DDL, the aggregate Table + Model TML, and the aggregated_models association patch for each approved candidate — every artifact is shown before it executes or imports, nothing happens silently.

All deterministic logic (signature parsing, measure decomposition, lattice generalization, cost-based greedy selection, SQL/DDL generation, TML assembly) lives in the ts aggregate command group (tools/ts-cli/ts_cli/aggregate/). This skill is thin orchestration: authentication, object picking (with an Owner column), the judgment calls no CLI can make (aggregate naming, ambiguous-measure review, RLS parity sign-off, the cut-off point), and the confirmation gates between each artifact.

Dependent-walking reuses the same alias-aware v2 metadata dependents path ts-dependency-manager uses. TML mining and object-picker conventions follow ts-object-model-coach. Backup/rollback of the primary Model's TML reuses ts dependency backup/rollback, exactly as ts-dependency-manager does.

This skill is pre-merge. The core behaviours are now VERIFIED live on an aggregate-aware cluster (routing fires for formula measures, date re-aggregation, the aggregated_models TML shape, first-match precedence, dependent types, token casing, filter precision — see references/open-items.md #0/#1/#2/#6/#7/#8/#10), and the DDL-from-AgentQL path is proven end-to-end. Row-level security is now auto- propagated onto every generated aggregate (#17, WIRED) rather than gated manually, but its live enforcement is unverified — treat every propagated RLS rule as provisional until Step 7's RLS leak-test passes. The remaining OPEN items (#3 model visibility, #4 non-additive routing, #5 cross-connection, #9 WEEK-boundary drift, #17's live leak-test + flat-shape confirmation) are lower-priority edge cases with live-test scripts (or, for #17, a documented live-test procedure); a few IMPLEMENTED items (#11/#14/#15) still want a live re-check. Treat every recommendation this skill produces as provisional until Step 7's routing verification (and, when RLS was propagated, its leak-test) passes, and read the open items before trusting a candidate the classifier or lattice flagged as ambiguous.

Ask one question at a time for dependent decisions. Batch independent questions into a single prompt to cut round-trips.

References

Prerequisites

• ThoughtSpot profile configured — run /ts-profile-thoughtspot if not
• ts CLI installed: pip install -e tools/ts-cli (v0.46.0+ — the ts aggregate group)
• Python package: pyyaml (pip install pyyaml)
• Snowflake profile (optional — only for connected-mode profiling/history/DDL execution) — /ts-profile-snowflake (Claude Code) or an active cortex connections connection (Cortex Code CLI). v1 history mining is Snowflake-only; profiling/DDL support Snowflake, Databricks, and BigQuery dialects, but only Snowflake has a connected (vs. manual) execution path today.
• ThoughtSpot user must have MODIFY or FULL access on the target Model, plus the Can manage data privilege and edit access on the connection the aggregate table will register against (registration validates table.schema against the connection's own credentials — the connection must be able to see the schema the aggregate table lands in).

Step 0 — Overview

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

ts-object-model-aggregates — audit a Model's usage, recommend aggregate grains with quantified benefit, and generate the warehouse DDL + aggregate Model TML + association patch, gated by your approval at every step.

1. Profile check ......................................... auto
2. Pick the primary Model (Owner column shown) ........... you choose
3. Extract query signatures from dependents .............. auto
4. Optional: mine Snowflake query history for weights .... you choose
5. Recommend → profile → re-recommend → pick the cut-off . you confirm
6. Generate per selected candidate (DDL/table/model/assoc) you confirm at every gate
7. Verify routing ......................................... auto, you confirm on failure
8. Summary report ......................................... auto

Confirmation required: Steps 2, 4, 5 (cut-off, and RLS force-add/exclude if any selected candidate conflicts), 6 (connection, DDL execution, table registration fallback, RLS propagation confirm, CLS parity, each TML import), 7 (only if a rollback decision is needed) Auto-executed: Steps 1, 3, 8

Ready to start? [Y / N]

Do not begin Step 1 until the user confirms.

Step 1 — Profile Check

Read ~/.claude/thoughtspot-profiles.json. If missing or empty, tell the user to run /ts-profile-thoughtspot first and stop. If multiple profiles exist, ask which to use; if exactly one, confirm it.

ts auth whoami --profile "{profile_name}"

If this fails, the token may be expired — see /ts-profile-thoughtspot's refresh procedure. Save {profile_name} for all subsequent steps.

Step 2 — Pick the Primary Model

This skill targets Models only (mirrors ts-object-model-coach Step 1). Accept --guid directly, or search:

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

Classify each result [MODEL] or [WORKSHEET] using metadata_header.contentUpgradeId / worksheetVersion (same logic as ts-object-answer-promote Step 5). If the user picks a Worksheet, tell them it must be upgraded to a Model first and stop — this skill's ts aggregate signatures command reads model.model_tables/aggregated_models, which don't exist on a plain Worksheet.

Display format. Show results as a markdown table with columns # | Name | Owner | GUID | Modified. Owner is metadata_header.authorDisplayName (fall back to authorName); Modified is metadata_header.modified (epoch millis) rendered as an ISO date. On shared instances, name collisions across authors are common — without the Owner column the user often picks the wrong Model.

Save {model_guid} and {model_name}. Create the working directory:

import time, pathlib
workdir = pathlib.Path.home() / "Dev" / "aggregate-runs" / f"{slug(model_name)}-{int(time.time())}"
workdir.mkdir(parents=True, exist_ok=True)

Step 3 — Extract Query Signatures

ts aggregate signatures \
  --model {model_guid} --profile "{profile_name}" --out "{workdir}"

Writes {workdir}/model.tml.yaml and {workdir}/signatures.jsonl. Stdout JSON: {"model_guid", "signatures", "full", "partial", "dependents", "export_failures"}. Report to the user:

Extracted {full} full + {partial} partial signatures from {dependents} dependent
Answer(s)/Liveboard(s) ({export_failures} export failure(s), skipped — not fatal).

partial signatures had a search_query token the parser couldn't resolve against the Model's columns — they're excluded from coverage scoring but counted so coverage percentages stay honest. If partial is large relative to full, note open-items.md #8 (possible token-casing mismatch) as a likely cause and flag it in the final report.

Export each model_tables Table TML

recommend doesn't need this, but profile and generate both do (a --tables-dir of <NAME>.tml.yaml files, one per model_tables entry, keyed by the table's exact name: — case-sensitive). Do this now so it's out of the way:

import json, subprocess, yaml, pathlib

model_tml = yaml.safe_load(open(f"{workdir}/model.tml.yaml").read())
tables_dir = pathlib.Path(workdir) / "tables"
tables_dir.mkdir(parents=True, exist_ok=True)

physical_tables = []  # (display_name, db_table, connection_type) — used in Steps 4/6
for entry in model_tml["model"]["model_tables"]:
    table_name, table_fqn = entry["name"], entry["fqn"]
    result = subprocess.run(
        ["bash", "-c",
         f"ts tml export {table_fqn} "
         f"--profile '{profile_name}' --fqn --parse"],
        capture_output=True, text=True,
    )
    body = json.loads(result.stdout)
    table_section = body[0]["tml"]["table"]
    (tables_dir / f"{table_name}.tml.yaml").write_text(
        yaml.dump({"table": table_section}, default_flow_style=False, allow_unicode=True)
    )
    physical_tables.append((
        table_name,
        table_section.get("db_table", table_name),
        table_section.get("connection", {}).get("type"),
    ))

If any export fails, note it and continue — a candidate that needs that table for its FROM/join clauses will surface as UnsupportedModelError later (skipped, not fatal) rather than blocking the whole run.

Step 4 — Optional: Mine Snowflake Query History

Ask:

Mine the underlying Snowflake query history to weight signatures by actual usage
frequency (rather than treating every dependent as equally important)? (Y / N)
Requires a Snowflake profile with ACCOUNT_USAGE access. v1 history mining is
Snowflake-only — skip this if the Model's tables live on Databricks or BigQuery.

Check physical_tables from Step 3 for connection_type == "SNOWFLAKE". If none of the Model's tables are Snowflake-backed, tell the user history mining isn't available for this Model's source and skip straight to Step 5.

If Y, prompt for the Snowflake profile name ({sf_profile_name}) and run:

ts aggregate history \
  --dir "{workdir}" --snowflake-profile "{sf_profile_name}" \
  --tables "{comma_separated_db_table_names}" --days 30

--tables is the comma-separated physical table names (the db_table values collected in Step 3, not the Model's display names). Writes {workdir}/weights.json. Stdout: {"history_rows", "weighted_signatures"}. Save {sf_profile_name} — Step 5 can reuse it for connected-mode profiling.

If N (or history mining doesn't apply), proceed to Step 5 without weights.json — recommend treats every signature with weight 1.0 (coverage-only mode) and the final report notes this explicitly rather than presenting an unweighted curve as if it reflected real usage.

Step 5 — Recommend, Profile, Re-recommend, Pick the Cut-off

5a. Pass 1 — coverage-mode recommend

ts aggregate recommend --dir "{workdir}" \
  $([ -f "{workdir}/weights.json" ] && echo --weights "{workdir}/weights.json")

Writes/updates {workdir}/candidates.json. Stdout: {"mode", "selected", "curve", "candidates", "excluded_unprofiled", "rls_conflicts", "routing_ineligible_measures"} — mode will be "coverage" on this first pass (no profiling data yet).

Routing-eligibility preflight (do this before profiling — a load-bearing gate). Aggregate-aware routing on this product fires ONLY for formula measures — a plain measure column produces an aggregate that queries never route to (open-items.md #0). recommend reports every targeted plain measure in routing_ineligible_measures ([{measure, reason, remedy}], via ts agentql classify-columns). If it is non-empty, stop and resolve it before Step 6: tell the user which measures won't route, and offer to promote each to a formula measure on the primary Model — redefine e.g. Amount from a plain SUM measure column (column_id: <T>::<col>) to a formula measure Amount = sum ( [<T>::<col>] ), keeping the same name/synonyms/description (a semantic no-op that flips routing on). Back up the primary (ts dependency backup) before importing the promoted TML. Do not build aggregates over a measure still listed here — they would be inert. (Verified live 2026-07-15: promoting the primary's plain Amount/Quantity to formulas is what made routing fire.)

Semi-additive measures. recommend also reports any last_value/first_value period-end snapshot measures (e.g. an inventory/account balance) under semiadditive_measures. The advisor does NOT auto-generate an aggregate for these — a correct snapshot needs a windowed last_value OVER (…) DDL out of scope for the generator, and flat-summing it would give wrong numbers. If any are listed and the user wants them aggregated, hand-build a period-end snapshot aggregate following references/semiadditive-recipe.md (verified live — month-end pattern + a mandatory numeric gate before import).

5b. Profile — connected or manual mode

Ask:

Profile the top candidates against the warehouse to replace estimated coverage with
actual row counts and compression ratios?

  C  Connected — I have a Snowflake profile with query access
  M  Manual    — emit a SQL script for you to run yourself (any warehouse)
  S  Skip      — keep coverage-only ranking (no compression numbers)

Enter C / M / S:

C — connected mode (reuse {sf_profile_name} from Step 4 if set, else ask):

ts aggregate profile --dir "{workdir}" --tables-dir "{workdir}/tables" \
  --snowflake-profile "{sf_profile_name}" --top-k 10 \
  --model-guid "{model_guid}" --profile "{profile_name}"

M — manual mode:

ts aggregate profile --dir "{workdir}" --tables-dir "{workdir}/tables" \
  --emit-sql "{workdir}/profile.sql" \
  --model-guid "{model_guid}" --profile "{profile_name}"

--model-guid/--profile (both already established in Step 3) let each candidate's profiling SQL prefer AgentQL — the same ThoughtSpot-generated-SQL path Step 6 uses for DDL — so the row counts reflect the correct join path on role-playing/ambiguous-path dimensions, falling back to the built-in walker automatically on any failure.

Tell the user: "Run {workdir}/profile.sql against your warehouse (any dialect — the script is dialect-specific SQL, not a ts command). Each numbered statement returns one row count; save the results as {"base_rows": N, "candidates": {"cand_1": rows, ...}} (the __base__ statement's result goes to base_rows), then tell me when it's ready." When ready, ask for the results file path and run:

ts aggregate profile --dir "{workdir}" --tables-dir "{workdir}/tables" \
  --results "{results_path}"

Both modes write agg_rows/base_rows back into {workdir}/candidates.json. Candidates whose SELECT can't be built deterministically are reported as skipped (not fatal) — note them in the final report as needing manual SQL authoring.

S — skip: proceed to 5c without profiling; the curve stays coverage-only.

5c. Re-run recommend for the cost-mode curve

ts aggregate recommend --dir "{workdir}" \
  $([ -f "{workdir}/weights.json" ] && echo --weights "{workdir}/weights.json")

No extra flags needed — recommend reads base_rows/agg_rows back out of candidates.json automatically (_merge_prior_agg_rows) and switches to "mode": "cost" once at least one candidate is profiled. If any candidate ids appear in excluded_unprofiled, note them — they were left out of cost-mode ranking because they weren't profiled (increase --top-k in 5b and re-profile if one of them looks important).

5d. Present the curve and let the user pick the cut-off

Join each curve entry (id, marginal_benefit, cumulative_coverage_pct, compression) with that candidate's own dimensions/bucket/flags/measure_columns from candidates.json, plus the proposed aggregate name — deterministic and computed the same way ts aggregate generate will name the real table/Model, via ts_cli.commands.aggregate._aggregate_name:

import json, yaml
from ts_cli.commands.aggregate import _aggregate_name

model_tml = yaml.safe_load(open(f"{workdir}/model.tml.yaml").read())
payload = json.loads(open(f"{workdir}/candidates.json").read())
for c in payload["candidates"]:
    c["_proposed_name"] = _aggregate_name(model_tml, c, None)

#   Grain                         Bucket   Rows saved  Compr.  Cum.cov.  Measures       Proposed aggregate            Flags
1   Region × Product Category    MONTHLY  4.2M         240×    58%       Sales, Units   DM_SALES_AGG_MONTHLY_REGION_PRODUCT_CATEGORY
2   Store                        DAILY    1.1M          38×    79%       Sales          DM_SALES_AGG_DAILY_STORE
3   Customer × Category × State  —        0.3M          12×    85%       Sales, Margin  DM_SALES_AGG_CUSTOMER_CATEGORY_STATE          wide_grain
4   Region × Product Category    WEEKLY   0.2M           9×    87%       Sales          DM_SALES_AGG_WEEKLY_REGION_PRODUCT_CATEGORY   compression<10×, rls_conflict

(mode: cost — profiled row counts; coverage % is weighted by history if you mined it in Step 4)

Soft flags to call out, not hard rules — the maintenance-cost judgment stays human: compression < 10×, wide_grain (grain > 8 columns), a candidate covering only stale/rarely-viewed dependents, and rls_conflict (candidate's own rls_conflict: true from recommend — see 5e below, this candidate's grain omits a base-table RLS filter column). Also surface the standing caveat from open-items.md #10: coverage % doesn't account for filter precision finer than the bucket — a candidate may look like it covers a query that filters more precisely than its stored grain.

Ask the user to pick a cut-off (e.g. "1,2" or "1-3" or "all" or "none"). Save the selected candidate ids as {selected_candidates}.

5e. Resolve RLS conflicts on the selected candidates

ts aggregate recommend (5a/5c) already attached rls_conflict/rls to every candidate in candidates.json when the base tables carry row-level security (a no-op when they don't). If none of {selected_candidates} have rls_conflict: true, skip this step entirely.

For each conflicting id, show the conflict and ask:

⚠ RLS CONFLICT — {grain_summary}

  Base table row-level security requires: {rls.required}
  This candidate's grain omits:           {rls.missing}

  F  Force-add the missing column(s) to this candidate's grain — widens the grain,
     which lowers compression (you'll want to re-profile it below)
  X  Exclude this candidate from the selected set

Enter F / X:

F — force-add. Apply add_rls_columns_to_candidate directly to this candidate in candidates.json:

import json
from ts_cli.aggregate.rls import add_rls_columns_to_candidate

payload = json.loads(open(f"{workdir}/candidates.json").read())
candidates = payload["candidates"]
idx = next(i for i, c in enumerate(candidates) if c["id"] == candidate_id)
candidates[idx] = add_rls_columns_to_candidate(candidates[idx], candidates[idx]["rls"]["missing"])
open(f"{workdir}/candidates.json", "w").write(json.dumps(payload, indent=2))

The grain just changed, so re-run 5b (profile) ONLY for this candidate before generating it in Step 6 — its row count and compression will differ now that the RLS column is part of the grain, and profile just adds agg_rows to the widened candidate in place. ts aggregate generate recomputes the conflict on this widened candidate and will no longer fail closed on it.

Do NOT re-run 5c / ts aggregate recommend after a force-add. recommend regenerates candidates.json from signatures.jsonl (via generate_candidates) and fully overwrites it — it does not read the existing candidates, so the force-added dimension (which lives only in candidates.json) would be discarded, the candidate would revert to its un-widened grain, rls_conflict would be true again, and Step 6's generate would fail closed — an infinite loop. Profile-only preserves the widened dimensions; recommend erases them. If you must re-run recommend for an unrelated reason (e.g. new weights), re-apply every force-add afterward, before generating.

X — exclude. Drop this id from {selected_candidates} and move on to the next conflicting id (or Step 6 if none remain).

Step 6 — Generate Per Selected Candidate

Repeat this entire step once per id in {selected_candidates}, one at a time. The processing order across candidates doesn't matter for the final association ordering — patch_association re-sorts the whole aggregated_models block by projected row count on every run, regardless of which candidate was generated first.

6a. Connection prompt

Per the standing rule (never silently reuse or trial-and-error an existing connection): ask which warehouse dialect the aggregate table should target (default: whichever connection_type the primary's own tables use, from Step 3).

The aggregate table for {grain_summary} needs a ThoughtSpot connection that can reach
its target database/schema.

  E  Use an existing connection
  C  Create a new connection   (Snowflake, key-pair auth)

Enter E / C:

E — use an existing connection:

ts connections list --profile "{profile_name}" --type {dialect_upper}

Ask how to identify it — name it exactly, filter by a partial string, or list all — same flow as ts-convert-from-snowflake-sv Step 6B. Save the exact name value from the response as {connection_name}.

C — create a new connection (Snowflake only in v1):

ts connections create \
  --name "{connection_name}" --account "{account}" --user "{user}" \
  --role "{role}" --warehouse "{warehouse}" --database "{database}" \
  --private-key-path "{key_path}" --profile "{profile_name}"

Never ask the user to paste a private key, password, or secret into the conversation — the key is passed by file path only. For Databricks/BigQuery or password/OAuth auth, direct the user to create the connection in the ThoughtSpot UI and return on the E path.

Ask for the target {db} and {schema} for the aggregate table.

6a.1 — Choose the materialization

Always ask this — both the E and C branches above, for every dialect (this replaced a v1 prompt that only fired on the E branch and only mentioned a warehouse, not the underlying materialization choice).

Live-tested on Snowflake: a materialized view whose definition joins more than one table is rejected outright with error 002212 ("Invalid materialized view definition. More than one table referenced in the view definition."), and this skill's aggregate SELECTs join the star's fact + dimension tables. So Snowflake never offers a materialized view — only a dynamic table (which supports joins) or a plain table. Databricks and BigQuery materialized views join natively, so they keep the materialized-view option.

Snowflake:

How would you like to materialize {grain_summary}?

  D  Dynamic table — auto-refreshing; needs a warehouse to run refreshes
  T  Plain table   — CREATE TABLE AS (no warehouse needed; refresh it yourself)

Note: a Snowflake materialized view isn't offered here — Snowflake rejects a
materialized view whose definition joins more than one table (error 002212), and
this aggregate's SELECT joins the star's tables. Forcing --materialization mview
on Snowflake will error at generate time.

Enter D (then the warehouse name) / T:

If D: ask for the warehouse name (if the C branch above already created a connection with a warehouse, offer to reuse that name) and save it as {warehouse} — it's required; ts aggregate generate hard-fails with --warehouse is required to create a Snowflake dynamic table if it's empty. If the user has no warehouse to give, steer them to T instead. Set {materialization} = dynamic.

If T: leave {warehouse} empty and set {materialization} = ctas.

Databricks / BigQuery:

How would you like to materialize {grain_summary}?

  M  Materialized view — auto-refreshing
  T  Plain table        — CREATE TABLE AS (manual refresh)

Enter M / T:

If M: set {materialization} = mview ({warehouse} stays empty — not needed). If T: set {materialization} = ctas.

6b. Generate the artifacts

Use the {materialization} (dynamic | ctas | mview) and {warehouse} chosen in 6a.1 — dynamic requires a non-empty {warehouse} (Snowflake only); ctas and mview need none:

ts aggregate generate \
  --dir "{workdir}" --candidate {candidate_id} --model-guid {model_guid} \
  --tables-dir "{workdir}/tables" --db "{db}" --schema "{schema}" \
  --connection-name "{connection_name}" --profile "{profile_name}" \
  --dialect {dialect} --materialization {materialization} \
  $([ -n "{warehouse}" ] && echo --warehouse "{warehouse}") \
  --out-dir "{workdir}/{candidate_id}"

--materialization dynamic emits a Snowflake dynamic table (needs --warehouse — chosen in 6a.1); --materialization mview emits a Databricks/BigQuery materialized view; --materialization ctas emits a plain CREATE TABLE AS on any dialect and needs no warehouse. --materialization mview on Snowflake is rejected by ts aggregate generate itself (the 002212 guard in sqlgen.build_ddl) — 6a.1 never offers that combination, so this only fires if the flags are overridden by hand. Writes five files to {workdir}/{candidate_id}/: ddl.sql, table_spec.json, table.tml.yaml, agg_model.tml.yaml, primary_patched.tml.yaml. Stdout: {"candidate", "aggregate_name", "files"}.

DDL SELECT source: by default ts aggregate generate builds AgentQL for the candidate's grain and asks ThoughtSpot (via --model-guid/--profile, already passed above) to compile it — this resolves joins against the full semantic model, avoiding wrong joins the built-in walker can produce on role-playing / ambiguous-path dimensions (e.g. grouping by the wrong role-played date column). It falls back to the built-in walker automatically if AgentQL generation is unavailable or errors, printing a stderr note when it does; pass --no-spotql to force that walker directly. If ddl.sql's SELECT looks wrong for a Model with role-playing dimensions, check stderr for a fallback note before assuming the DDL itself is broken.

This first generate call has no --agg-model-guid yet (the aggregate Model doesn't exist in ThoughtSpot until 6f imports it) — primary_patched.tml.yaml from this pass is keyed by the aggregate Model's name, which is ambiguous (it collides with the equally-named backing Table, DUPLICATE_OBJECT_FOUND on a live cluster). Do not import this provisional file. ts aggregate generate prints a stderr warning to that effect. Only ddl.sql, table_spec.json, table.tml.yaml, and agg_model.tml.yaml from this pass are used going forward; 6f.1 below regenerates primary_patched.tml.yaml correctly once the aggregate Model's GUID is known.

6c. DDL gate

Show the full contents of {workdir}/{candidate_id}/ddl.sql. Ask:

Create this aggregate table now?

  Y  Yes — execute the DDL directly (requires warehouse write access)
  M  Manual — I'll run it myself; tell me when it's done
  N  Cancel this candidate

Enter Y / M / N:

Y — connected execution. For a method: cli Snowflake profile:

{snow_cmd} sql -c {cli_connection} -f "{workdir}/{candidate_id}/ddl.sql"

For a method: python profile, connect via the Python connector (same helper ts-load-source-data/ts-convert-to-snowflake-sv use) and cursor.execute() the file's contents. Cortex Code CLI users: execute the DDL directly via the sql_execute tool against the active cortex connections connection.

M — manual. Tell the user the exact file path to run, then wait for confirmation before continuing — this flow is idempotent, so resuming in a later session is fine.

N — cancel. Skip the rest of Step 6 for this candidate and move to the next one (or Step 7 if this was the last).

6d. Register the table in ThoughtSpot

table_spec.json is a single spec object; ts tables create reads a JSON array from stdin:

python3 -c "
import json
spec = json.load(open('{workdir}/{candidate_id}/table_spec.json'))
print(json.dumps([spec]))
" | ts tables create --profile "{profile_name}"

Output: {{aggregate_table_name}: guid_or_null}. Save the GUID as {aggregate_table_guid} — 6e needs it to confirm RLS attached. Schema validation against the connection here doubles as proof the DDL actually ran — ts tables create reuses the existing JDBC-error retry handling (transient errors retried automatically). If the GUID comes back null after retries (a persistent JDBC/schema error), fall back: ask the user to add the table via the ThoughtSpot UI manually, then resume once it exists — check with ts metadata search --subtype ONE_TO_ONE_LOGICAL --name "{aggregate_table_name}".

RLS registration is a two-pass import, handled automatically by this same command (Task 25 — live finding). When table_spec.json carries rls_rules (Task 23's propagation — see 6e below for what it contains), a single create_new import that already includes rls_rules fails live: OBJECT_NOT_FOUND ... LOGICAL_TABLE. The propagated rule's table_paths entry self-references the table being created ([{aggregate_table_name}_1::COL]), and that reference can't resolve to a LOGICAL_TABLE that doesn't exist yet in the same call that creates it. So ts tables create does this instead, with no extra flag or step required from you:

1. Pass 1 — create. Import the table TML without rls_rules, --create-new. This is the GUID reported above.
2. Pass 2 — attach. Re-import the same TML with rls_rules and the just-created GUID at the document root, --no-create-new (update in place).

A table with no RLS at all is unaffected — one pass, as always. If pass 2 fails (the table imports fine but attaching RLS doesn't), stderr shows table created ({guid}) but attaching row-level security failed — the table now EXISTS but is UNSECURED. Do not treat the GUID coming back non-null as proof RLS attached; 6e's confirm step below is what actually proves it either way.

6e. RLS propagation — confirm it actually attached; CLS parity stays a manual gate

A fast aggregate that leaks rows across tenants is worse than no aggregate. As of Task 23, this is no longer a manual "go apply RLS yourself in the UI" gate — 6b's ts aggregate generate call already did the propagation work, before you ever saw the artifacts, and 6d's ts tables create call already did the two-pass registration against the live cluster:

• generate extracted rls_rules from every base Table TML in {workdir}/tables/ this candidate draws from.
• If the candidate's grain omitted a required RLS filter column, generate already FAILED CLOSED (non-zero exit, before writing any of the five output files) — if that happened, Step 5e was skipped or the force-add wasn't applied to candidates.json before this generate call; go back to 5e, force-add or exclude, then re-run 6b from scratch. Do not hand-author RLS on the aggregate table as a workaround for a fail-closed error.
• Otherwise, the base rule(s) were remapped onto the aggregate's own grain columns, written into {workdir}/{candidate_id}/table.tml.yaml's table.rls_rules, and 6d attempted to attach them live via its pass 2.

Read back what should have been propagated:

import yaml

table_tml = yaml.safe_load(open(f"{workdir}/{candidate_id}/table.tml.yaml").read())
rls = table_tml["table"].get("rls_rules")

If rls is falsy, no base table carried RLS — nothing to show or confirm, proceed straight to the CLS question below.

Otherwise, confirm it actually attached live — 6d's pass 2 can fail independently of pass 1 (the table exists either way), so a non-null GUID in 6d's output is not proof RLS is in effect. Export the live object and compare:

ts tml export {aggregate_table_guid} --profile "{profile_name}" --parse

Check the returned table.rls_rules is present and matches rls above (same tables/table_paths/rules shape — all three sub-blocks; a live rule missing the tables sub-block naming the table itself is malformed and would not have imported in the first place, so its presence here is also confirmation the shape was well-formed). If it's missing or doesn't match, 6d's pass 2 silently failed despite its own stderr check (or that check was missed) — do not proceed. Do not re-run 6d — its pass 1 is a --create-new import, so re-running it against an already-created table makes a DUPLICATE same-named table rather than updating this one. Instead attach RLS directly against the known {aggregate_table_guid}: add guid: {aggregate_table_guid} to the document root of table.tml.yaml and run ts tml import --file {workdir}/{candidate_id}/table.tml.yaml --profile "{profile_name}" --no-create-new, then re-export and re-check before continuing.

Once confirmed, pull the "before" side of the comparison from the same {workdir}/tables/*.tml.yaml files read above (each base table's own table.rls_rules.rules[].expr) and show both sides:

RLS auto-propagated onto {aggregate_table_name} (confirmed attached via live export):

  {rule_name}: {rewritten_expr}
  ...

Rewritten from the base table's rule(s) (same filter shape, remapped to the aggregate's
own columns):
  {base_table}: {rule_name}: {base_expr}
  ...

This is still PROVISIONAL until Step 7's live leak-test confirms it actually enforces
the rule once queries route to this aggregate — this step only confirms the rule is
attached to the table object, not that ThoughtSpot evaluates it correctly once routing
kicks in.

Confirm this matches your intent before the aggregate Model is imported. (type CONFIRM
to proceed, anything else cancels this candidate)

Column-Level Security (CLS) is unchanged — still a manual gate. It cannot be reliably auto-detected today — retrieval of column_security_rules TML is itself an open item on ts-dependency-manager (unverified). Ask explicitly instead of silently skipping the check:

Do any of the base tables for this aggregate have Column-Level Security (CLS)
restricting which users can see specific columns? (Y / N — if unsure, check
Table → Column Security in the ThoughtSpot UI before answering)

If Y, require the same kind of explicit confirmation that equivalent CLS has been (or will be) applied to the aggregate table before continuing. Do not proceed past this gate on an unconfirmed "I'm not sure."

6f. Import the aggregate Model

ts tml import --file "{workdir}/{candidate_id}/agg_model.tml.yaml" \
  --profile "{profile_name}" --policy ALL_OR_NONE --create-new

--create-new is required — agg_model.tml.yaml has no guid: (it's brand new). Parse the returned JSON for the new Model's GUID:

import json
resp = json.loads(result.stdout)
agg_model_guid = resp[0]["response"]["object"][0]["header"]["id_guid"]

If the import fails, check that the RLS/CLS gate above wasn't bypassed and that the connection_name matches exactly (case-sensitive) an existing connection.

6f.1. Re-patch the primary with the aggregate Model's GUID

Why this step exists: the aggregate Model and its backing Table share a name (both {aggregate_name}) — a name-based aggregated_models association id is ambiguous and ThoughtSpot has been observed to reject it (DUPLICATE_OBJECT_FOUND). The GUID only exists after 6f's import, so primary_patched.tml.yaml must be regenerated now that it does, before 6g imports it. Re-run the exact same ts aggregate generate call from 6b, adding --agg-model-guid {agg_model_guid}:

ts aggregate generate \
  --dir "{workdir}" --candidate {candidate_id} --model-guid {model_guid} \
  --tables-dir "{workdir}/tables" --db "{db}" --schema "{schema}" \
  --connection-name "{connection_name}" --profile "{profile_name}" \
  --dialect {dialect} --materialization {materialization} \
  $([ -n "{warehouse}" ] && echo --warehouse "{warehouse}") \
  --agg-model-guid "{agg_model_guid}" \
  --out-dir "{workdir}/{candidate_id}"

This rewrites all five files identically to 6b except primary_patched.tml.yaml, whose new aggregated_models entry is now keyed by {agg_model_guid} instead of the ambiguous name — no stderr warning this time. ts aggregate generate is idempotent (Task 16): re-running it never duplicates or drifts the primary's other existing aggregate associations, it only affects this candidate's own entry. Use this primary_patched.tml.yaml in 6g, not the provisional one from 6b.

6g. Back up and patch the primary Model

Non-negotiable — always run before touching the primary. Reuses ts dependency backup exactly as ts-dependency-manager does:

import json, subprocess

plan = {"operation": "REMOVE", "source": {"guid": model_guid, "type"

…(truncated)