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:

{ "metadata_tmls": ["<tml-yaml>", "..."], "import_policy": "PARTIAL", "create_new": true }

Response 200 (returns in ~0.2s):

{
  "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:

{ "task_ids": ["2fb37fcb-..."], "include_import_response": true }

Response 200:

{
  "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.

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

use_async = os.getenv("TS_TML_IMPORT_MODE", "sync").lower() == "async"

Phase 1 (replace lines 3338–3344):

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.