docs/async_tml_import_handoff.md

# Handoff: switch ThoughtSpot table import from sync to async

> **SUPERSEDED IN PART, 2026-08-10.** The async switch shipped and stands (it
> remains the right defense against gateway 504s), but the "~250s server time
> is inherent / not reducible" premise below is WRONG. Root cause: table TML
> import scans all external metadata visible to the connection; our
> connections were unscoped (role sees ~507 Snowflake DBs). Scoping the
> connection with a `database` property + writing demos to a small
> monthly-rotating database takes the same import from ~250s to **<1s**
> (measured on sebe and secloud, 2026-08-10). See the RESOLVED banner in
> `sre_slow_tml_import.md`.

**Audience:** an engineer/agent implementing the change. Self-contained — you
do not need prior conversation context.

**Goal:** replace the synchronous TML import in `ThoughtSpotDeployer.deploy_all`
with ThoughtSpot's **async import API** for both the table-create phase and the
join phase. This eliminates the gateway 504s and the fragile post-504 recovery
polling, and gives a definitive completion signal.

**Explicitly NOT the goal:** making imports faster. Measured server-side time is
the same either way (~250–305s per phase on sebe). Async is a **reliability and
observability** fix, not a speed fix. Do not claim it speeds up demos.

---

## 1. Why (measured evidence)

Two harnesses were run against `sebe` staging (`https://sebe.thoughtspotstaging.cloud`).

- **Naming test** (`tests/ts_table_perf.py`): 10 logical tables, one per naming
  convention, each a trivial 5-column empty table.
  - Every import took **222–305s**. Naming had no measurable effect.
  - **3/10 hit an nginx `504 Gateway Time-out` at exactly ~300s.** The import
    still completed server-side afterward (the table appeared on the next scan).
  - The full-connection scan was **<1s** at 10 tables; a duplicate-name
    re-create errored **fast (0.17s)**. So table count / name collisions are
    NOT the driver at this scale.
- **Sync vs async** (`tests/ts_import_sync_vs_async.py`): 4-table star schema,
  phase 1 create + phase 2 joins, run both ways.

  | metric | SYNC | ASYNC |
  |---|--:|--:|
  | phase 1 create — call/submit | 300.2s → **504** | **0.13s** |
  | phase 2 joins — call/submit | 300.1s → **504** | **0.26s** |
  | phase 1 total to verified | 305.7s | 252.5s |
  | phase 2 total to verified | 300.4s | 263.3s |
  | gateway 504s | **2 / 2** | **0** |
  | tables / joins verified | 4/4, 3/3 | 4/4, 3/3 |

**Conclusion:** the ~4–5 min/phase is ThoughtSpot server-side metadata
processing (instance load). Sync blocks the whole time and slams the ~300s
gateway wall; async submits instantly and polls a light status endpoint to a
clean `COMPLETED`.

---

## 2. Async API contract (verified live on sebe)

### Submit — `POST /api/rest/2.0/metadata/tml/async/import`
Request body is **identical to the sync import**:
```json
{ "metadata_tmls": ["<tml-yaml>", "..."], "import_policy": "PARTIAL", "create_new": true }
```
Response `200` (returns in ~0.2s):
```json
{
  "task_id": "2fb37fcb-1e8c-4bd3-99e2-a0967ff35ef7",
  "task_name": "ASYNC_TML_29:Jul:2026-03:52:55",
  "task_status": "IN_QUEUE",
  "import_response": null,
  "import_policy": "PARTIAL",
  "total_object_count": 1,
  "object_processed_count": null,
  "created_at": null, "in_progress_at": null, "completed_at": null
}
```
Read `task_id` from the response.

### Poll — `POST /api/rest/2.0/metadata/tml/async/status`
Request:
```json
{ "task_ids": ["2fb37fcb-..."], "include_import_response": true }
```
Response `200`:
```json
{
  "status_list": [{
    "task_id": "2fb37fcb-...",
    "task_status": "IN_PROGRESS",
    "import_response": { "status": { "status_code": "OK", "error_code": 0, "error_message": "" } },
    "total_object_count": 1,
    "object_processed_count": 0,
    "created_at": 1785297175686, "in_progress_at": 1785297175689, "completed_at": 0
  }]
}
```

**State machine observed:** `IN_QUEUE → IN_PROGRESS → COMPLETED`.
**Terminal signal (use both):** `task_status == "COMPLETED"` OR
`completed_at > 0`. Treat `"FAILED"`/`"ERROR"` (or
`import_response.status.status_code == "ERROR"`) as failure and surface
`import_response.status.error_message`.

> **GUID caveat:** in the probe, the status `import_response` carried only
> `status` (no per-object `header.id_guid`). So after completion, resolve table
> name → GUID with the existing `search_logical_tables_for_connection(...)`
> (§3). If your instance's `import_response` includes a per-object list with
> headers, prefer that and fall back to the connection scan.

---

## 3. Current (sync) implementation — what you're replacing

All in `thoughtspot_deployer.py`, inside `deploy_all` (starts **line 2539**):

| Piece | Location | Role |
|---|---|---|
| `_import_tml_chunk` (closure) | ~2768; sync `POST .../tml/import` at **2793**, `timeout=360`; 401 re-auth at 2814 | one synchronous batch import |
| `_import_tmls_chunked` | **3204**; `retriable_statuses = {502,503,504}` at 3206 | chunk + retry wrapper |
| `_resolve_existing_tables_after_timeout` | **3119**; default 900s / 30s via `TS_TML_504_POLL_TIMEOUT_SECONDS` / `TS_TML_504_POLL_INTERVAL_SECONDS` | 504-recovery poll (create) |
| `_verify_table_updates_after_timeout` | **3057** | 504-recovery poll (joins) |
| Phase 1 create call | **3338–3344** — `_import_tmls_chunked("Batch 1", …, create_new=True)` | creates tables |
| Phase 2 joins call | **3453–3460** — `_import_tmls_chunked("Batch 2", …, create_new=False, fatal_errors=False)` | adds joins |

Supporting (reuse as-is, do not reimplement):
- `create_table_tml(...)` — **line 920** — builds the table TML (phase 1:
  `all_tables=None`; phase 2: `all_tables=tables, table_guid=<guid>`,
  `foreign_keys=fks`). Keep passing `connection_fqn`.
- `search_logical_tables_for_connection(connection_guid, connection_name, expected_names)`
  — **line 2209** — returns `{NAME: {"response": {"status": {"status_code":"OK"},
  "header": {"id_guid","name"}}}}`. This is the return shape callers expect.
- `assign_tags_to_objects(guids, "LOGICAL_TABLE"/"DATA_SOURCE", tag)` — line 2383.

---

## 4. Target implementation

### 4a. Add one method (drop-in for `_import_tmls_chunked`)
Return the **same shape** `_import_tmls_chunked` returns — a dict
`{TABLE_NAME_UPPER: {"response": {"status": {...}, "header": {"id_guid","name"}}}}`
— so downstream processing at lines 3351–3376 (create) and 3462–3478 (joins) is
unchanged.

```python
def import_tmls_async(self, expected_names, tmls, create_new,
                      connection_guid, connection_name,
                      poll_interval_s=None, timeout_s=None,
                      log_progress=None, slog=None):
    """Async TML import: submit once, poll status to completion, then resolve
    name->guid from the connection. Returns {NAME: import-object} like
    _import_tmls_chunked. No gateway 504 handling needed."""
    base = self.base_url
    poll_interval_s = poll_interval_s or int(os.getenv("TS_TML_ASYNC_POLL_INTERVAL_SECONDS", "5"))
    timeout_s = timeout_s or int(os.getenv("TS_TML_ASYNC_TIMEOUT_SECONDS", "900"))

    r = self.session.post(f"{base}/api/rest/2.0/metadata/tml/async/import",
                          json={"metadata_tmls": tmls, "import_policy": "PARTIAL",
                                "create_new": create_new}, timeout=60)
    if r.status_code == 401 and self.authenticate():
        r = self.session.post(f"{base}/api/rest/2.0/metadata/tml/async/import",
                              json={"metadata_tmls": tmls, "import_policy": "PARTIAL",
                                    "create_new": create_new}, timeout=60)
    r.raise_for_status()
    task_id = (r.json() or {}).get("task_id")

    deadline = time.time() + timeout_s
    final = None
    while time.time() < deadline:
        s = self.session.post(f"{base}/api/rest/2.0/metadata/tml/async/status",
                              json={"task_ids": [task_id], "include_import_response": True}, timeout=60)
        if s.status_code == 200:
            final = ((s.json() or {}).get("status_list") or [{}])[0]
            st = final.get("task_status")
            if final.get("completed_at") or st in ("COMPLETED", "SUCCESS", "FAILED", "ERROR", "PARTIAL_SUCCESS"):
                break
        time.sleep(poll_interval_s)

    # Fail loudly on a terminal error.
    imp = (final or {}).get("import_response") or {}
    if (final or {}).get("task_status") in ("FAILED", "ERROR") or \
       (imp.get("status") or {}).get("status_code") == "ERROR":
        raise RuntimeError(f"async import failed: {(imp.get('status') or {}).get('error_message')}")

    # Resolve name -> guid from the connection (import_response has no headers).
    return self.search_logical_tables_for_connection(
        connection_guid, connection_name,
        expected_table_names=expected_names,
        record_size=max(50, len(expected_names) * 2))
```

### 4b. Branch the two call sites behind a flag
Add near the top of `deploy_all` (or read once):
```python
use_async = os.getenv("TS_TML_IMPORT_MODE", "sync").lower() == "async"
```
Phase 1 (replace lines 3338–3344):
```python
if use_async:
    objects = self.import_tmls_async(table_names_order, table_tmls_batch1, True,
                                     connection_guid, connection_name,
                                     log_progress=log_progress, slog=_slog)
else:
    objects = _import_tmls_chunked("Batch 1", table_names_order, table_tmls_batch1,
                                   create_new=True, chunk_size=create_chunk_size)
```
Phase 2 (replace lines 3453–3460): same pattern with
`table_names_order_batch2 / table_tmls_batch2`, `create_new=False`. On async the
existing `_resolve_/_verify_*_after_timeout` recovery paths are simply not used
(there is no 504), so no other logic changes.

### 4c. Rollout
- Land behind `TS_TML_IMPORT_MODE` (default `sync`) so it's a config flip and an
  instant rollback. Validate on `hf-test`, then set `async` in prod settings.
- Keep the sync code path intact for fallback until async is proven in prod.

---

## 5. Edge cases / gotchas
- **fqn:** keep passing `connection_fqn` to `create_table_tml` (already done) —
  docs say it reduces same-name validation ambiguity.
- **401 mid-flight:** re-auth on submit (shown) and, ideally, on status polls.
- **Timeout:** if the poll deadline elapses without terminal status, fall back
  to a connection scan (`search_logical_tables_for_connection`) to see whether
  the objects landed anyway — same philosophy as today's 504 recovery.
- **create_new=False (joins/update):** async supports it; verify the join
  update returns `COMPLETED` and that `joins_with` is present on export.
- **Connection delete** (for test cleanup only): use
  `POST /api/rest/2.0/connection/delete {"connection_identifier": "<guid>"}` —
  `metadata/delete` rejects type `CONNECTION` ("not in DeleteMetadatatype enum").

---

## 6. Testing
- **A/B:** `python tests/ts_import_sync_vs_async.py --env "sebe - se" --yes`
  (keeps the sandbox by default; `--cleanup` deletes that run's tables). It
  already builds the star schema, runs both modes, and verifies tables + joins.
- **Full pipeline:** run a normal demo build (App tab GO → `defined_go` →
  `deploy_all`) with `TS_TML_IMPORT_MODE=async`; confirm all tables + joins are
  created with **no 504 lines in the logs** and a clean completion.
- Sandbox available for manual testing: connection `ZPERFTEST_DONOTDELETE`,
  Snowflake `DEMOBUILD.ZPERFTEST` on sebe.

## 7. Acceptance criteria
1. `TS_TML_IMPORT_MODE=async` builds a multi-table model (create + joins) end to
   end with **zero gateway 504s** and a definitive completion signal.
2. Output object shape unchanged → no downstream changes needed in `deploy_all`
   or the model/liveboard steps.
3. `sync` remains available via the flag and behaves exactly as today.
4. Session logs record import mode, task_id(s), and per-phase completion.

## 8. Out of scope
The ~4–5 min/phase server-side latency. Async does not change it. Separately:
confirm the latency on a quieter env (prod `secloud`) and raise with the TS
infra team if sebe is unhealthy.