---
name: analyzing-tidb-x-growth
description: Analyzes TiDB X cluster growth (Starter, Essential, Premium service plans) from the GCP BigQuery BI table. Use when users ask for tenant cluster counts, daily new or cumulative cluster trends, high-volume tenants, service-plan distribution, or region distribution of TiDB X clusters.
---

# Analyzing TiDB X Growth

Answer daily growth questions for TiDB X clusters by querying the BigQuery BI table. TiDB X is the service-plan family containing `Starter`, `Essential`, and `Premium`.

## Data Source

Single source table:

```
`gcp-digital-bi.model_tidbcloud.dws_cloud_all_clusters_component_detail_basic_info`
```

Fields used across queries:

| Field | Meaning |
|-------|---------|
| `tenant_id` | Tenant identifier (numeric) |
| `tenant_name`, `tenant_alias` | Tenant display strings |
| `tenant_region` | Tenant's home region (use for tenant-level segmentation) |
| `tenant_internal_user` | Whether the tenant is internal (include in tenant-ranking reports) |
| `cluster_id` | Cluster identifier; unique in this table |
| `cluster_name` | Cluster name |
| `cluster_created_at` | Creation timestamp |
| `cluster_deleted_time` | Deletion timestamp; `NULL` means active |
| `cluster_service_plan` | `Starter` / `Essential` / `Premium` |
| `provider_name` | Cloud provider |
| `region_display_name` | Cluster region (use for cluster-level region analysis) |

## Shared Filtering Rules

Apply these to every query unless the user explicitly overrides:

| Rule | Filter | Why |
|------|--------|-----|
| Active clusters only | `cluster_deleted_time IS NULL` | Deleted rows skew growth and ranking counts |
| TiDB X scope | `cluster_service_plan IN ('Starter', 'Essential', 'Premium')` | Excludes non-TiDB-X clusters in the same table |
| Single plan | `cluster_service_plan = '<plan>'` when the user names one | User intent takes precedence over the default scope |

`cluster_id` is unique here, so cluster counts always use `COUNT(*)` — do **not** add `DISTINCT cluster_id` or dedup CTEs.

## Input Matching

| User input shape | Tenant match field | Cluster match field |
|------------------|--------------------|---------------------|
| Numeric string | `tenant_id` (exact) | `cluster_id` (exact) |
| Non-numeric string | `tenant_name` or `tenant_alias` (fuzzy) | `cluster_name` (fuzzy) |

Fuzzy match pattern: `LOWER(field) LIKE LOWER('%<value>%')`. State that fuzzy matching was used. If multiple records match, list candidates and ask the user before running narrow analyses. Never hardcode sample IDs into SQL templates — substitute from user input.

## Query Workflow

1. Confirm scope: tenant, cluster, service plan, date range, active-only or include deleted.
2. Apply the **Shared Filtering Rules**.
3. Resolve tenant/cluster identifiers via **Input Matching**.
4. Pick the matching template from **Query Templates** below.
5. Run via the **BigQuery Run Path**.
6. Summarize in business terms: growth direction, top tenants, active base, region concentration.

## Query Templates

Pick one based on the question:

| Question pattern | Template |
|------------------|----------|
| "Show details for cluster / tenant X" | [queries/cluster-details.md](queries/cluster-details.md) |
| "Daily new and cumulative clusters for tenant X" | [queries/daily-tenant-growth.md](queries/daily-tenant-growth.md) |
| "List orgs with more than N Starter clusters" | [queries/orgs-over-threshold.md](queries/orgs-over-threshold.md) |
| "Cluster counts by region / provider / service plan" | [queries/region-distribution.md](queries/region-distribution.md) |

## BigQuery Run Path

Always pass the execution project explicitly — the local default project often lacks `bigquery.jobs.create` permission.

```bash
bq query --use_legacy_sql=false --project_id="gcp-digital-bi" --format=prettyjson '<SQL>'
```

If the dataset requires a location, append `--location="US"`. If a query fails with `bigquery.jobs.create` for another project, retry once with `--project_id="gcp-digital-bi"` and report that the first job used the wrong default.

### Service-account authentication

When the user provides a GCP service-account JSON path:

```bash
export GOOGLE_APPLICATION_CREDENTIALS="/path/to/gcp-service-account.json"
gcloud auth activate-service-account --key-file="${GOOGLE_APPLICATION_CREDENTIALS}"
gcloud config set project "gcp-digital-bi"
bq query --use_legacy_sql=false --project_id="gcp-digital-bi" 'SELECT 1 AS ok'
```

For one-off invocations that should not touch global gcloud config:

```bash
GOOGLE_APPLICATION_CREDENTIALS="/path/to/gcp-service-account.json" \
bq query --use_legacy_sql=false --project_id="gcp-digital-bi" --location="US" '<SQL>'
```

If authentication fails, report the failing command and error category (missing file, invalid JSON, permission denied, BigQuery API not enabled). **Never echo tokens, private keys, or full credential JSON to the user or to logs.**

## Gotchas

- **Default project trap.** Don't rely on the local default project — pass `--project_id="gcp-digital-bi"` on every `bq` invocation.
- **Active-only by default.** Every SQL must include `cluster_deleted_time IS NULL` unless the user asks for deleted records or queries one specific `cluster_id`.
- **No DISTINCT, no dedup CTEs.** `cluster_id` is unique in the source table. Use `COUNT(*)`.
- **No hardcoded sample IDs.** Substitute `<tenant_id_from_user>` / `<cluster_id_from_user>` from the user's natural-language input.
- **No `target_tenants` CTE** just to display tenant strings. The source table already has `tenant_name` and `tenant_alias`; select them directly.
- **Fuzzy match wins for strings.** When the user gives a non-numeric tenant or cluster string, fuzzy-match against both `tenant_name` and `tenant_alias` (or `cluster_name`), confirm the candidate set, then drill in.
- **Region fields aren't interchangeable.** Use `region_display_name` for cluster-level region analysis; use `tenant_region` only when explicitly segmenting by tenant home region.
- **Don't echo credentials.** Authentication errors should report the *category* (missing file, permission denied, API disabled), never JSON contents.
- **Out of scope.** Cluster-count growth only — not pricing, billing, churn, performance metrics, or TiDB Cloud Dedicated.

## SQL Style

- Prefer compact SQL over multi-CTE generators — query the source table directly for detail lookups and group-by summaries.
- For small ID lists, use `IN (...)`.
- Keep placeholder names (`<tenant_id_from_user>`) explicit so downstream agents know what to substitute.

## Reporting Guidelines

For **trend** requests: date window, whether totals are daily-new / cumulative-created / active-end-of-day, daily new and active total trend lines, service-plan split when asked, notable spikes or drops with dates.

For **tenant ranking** requests: threshold used, note that deleted clusters were excluded by default, top tenants by count, and `tenant_internal_user` so reviewers can separate internal vs external usage.

For **region** requests: region field used (`region_display_name` by default), total and per-service-plan counts per region, concentrated regions, and any `UNKNOWN` bucket that needs schema follow-up.