glean-description-lookup

What this skill does

# Glean Description Lookup

**Composable:** Invoked by schema-enricher (Step 2, priority 4 when the upstream source table ends in `_live` or `_stable`)
**When to use:** A source table ends in `_live` or `_stable` and you need field descriptions for schema.yaml generation or enrichment

## ⚠️ Scope — Only for `_live` and `_stable` Tables

This skill applies **only** to:
- Tables ending in `_live` (e.g. `<app>_live.<ping>_v1`)
- Tables ending in `_stable` (e.g. `<app>_stable.<ping>_v1`)

These tables are NOT in the `/sql` directory. They are live Glean ingestion tables managed outside bigquery-etl.

For all other tables (`_derived`, `_bi`, `mozdata`, etc.) — read `schema.yaml` from the local `/sql` directory instead.

## Workflow

### Step 1: Confirm table is `_live` or `_stable`

Check the source table referenced in `query.sql`:

```bash
grep -Ei "FROM|JOIN" query.sql | grep -oiE "[a-zA-Z0-9_-]+\.(.*_(live|stable))\.[a-zA-Z0-9_]+"
```

⚠️ **Backtick limitation:** This grep only matches unquoted identifiers. Backtick-quoted references (e.g. `` `project`.`dataset_stable`.`table` ``) will not appear in the output. If the grep returns nothing, either scan the query visually for `_stable`/`_live` dataset names, or skip ahead to Step 2 — the helper script handles all backtick quoting styles automatically.

If no `_live` or `_stable` table is found → **do not use this skill**. Use the local `/sql` directory.

### Step 2: Extract fields used from that table in `query.sql`

Choose the right flag based on your query:

| Query structure | Flag to use | Why |
|---|---|---|
| One Glean source table | `--glean-only` | Filters output to only Glean source tables |
| Multiple source tables, want fields from all Glean tables | `--glean-only` | Same — filters out non-Glean source tables |
| Multiple source tables, want fields from one specific Glean table | `--table <app>_stable.<ping>_v1` | Shows URL and suggested prompt for that table only; field extraction still covers the full query |

Run the helper script and record its full output:

```bash
# Single Glean table or all Glean tables
python scripts/extract_query_fields.py query.sql --glean-only

# Specific Glean table (dataset.table format; project prefix is optional)
python scripts/extract_query_fields.py query.sql --table <app>_stable.<ping>_v1
```

Record:
```
Source table: <app>_stable.<ping>_v1
Fields used: [<field_1>, <field_2>, <field_3>, ...]
```

⚠️ **Alias limitation:** The script captures all dotted identifiers but cannot determine which ones are table or UNNEST alias references. After running the script, review the field list for two issues:
- **Remove** any entries whose first segment is a known alias (e.g. `e.submission_timestamp` where `e` aliases the `_stable` table, or `ev.name` where `ev` is an `UNNEST` alias) — these are false positives with the alias prefix intact.
- **Add** any Glean paths that were only accessed via alias in the query and therefore do not appear in the list. Use the full unaliased Glean path (e.g. `submission_timestamp`, `events[].name`).

To find aliases: search `query.sql` for `FROM <table> AS <alias>` and `CROSS JOIN UNNEST(...) AS <alias>` patterns, then compare those alias names against the first segment of each field in the output.

⚠️ **Field cap:** The script always extracts and prints the full field list. However, the SUGGESTED WebFetch PROMPT section is capped at `--max-fields` (default 25). If you see a truncation warning on stderr, either use the full FIELD REFERENCES list manually (after reviewing for alias false positives), or re-run with `--max-fields 50` (or higher) and then review the expanded list for aliases before using it.

⚠️ **Keyword filtering:** The script automatically excludes SQL keywords and function names (e.g. `select`, `extract`, `count`) from the field list to reduce false positives. If a field appears absent from the output, verify it is not being silently filtered as a keyword — check `query.sql` directly.

### Step 3: Map the BigQuery table name to a Glean Dictionary URL

**Derivation rule (works for most apps):**
1. Strip `_live` or `_stable` suffix from the BigQuery dataset name → this is the **app_id** (dictionary URL form, uses underscores): `<app_id>_stable` → `<app_id>`
2. Strip the `_v<N>` suffix from the BigQuery table name → this is the Glean table name: `<table>_v1` → `<table>`

```
BigQuery dataset:  <app_id>_stable  or  <app_id>_live
BigQuery table:    <table>_v1

app_id (dictionary URL form, underscores):  <app_id>
Glean table name:                           <table>

URL:  https://dictionary.telemetry.mozilla.org/apps/<app_id>/tables/<table>
```

Consult `references/app_id_map.md` for known app mappings and alternate app_id variations. If your app is not listed, the derivation rule above works for all apps. To verify directly, visit https://dictionary.telemetry.mozilla.org/ and search for the app name — the URL contains the correct app_id.

⚠️ **Hyphen vs underscore:** The Glean Dictionary URL uses underscores (`<app_id>`). The probeinfo API (`probeinfo.telemetry.mozilla.org`) requires hyphens (`<app-id>`). If using the probeinfo API as a fallback, replace `_` with `-` in the app_id.

### Step 4: Determine fetch strategy from table type

Derive the table type from your BigQuery table name (e.g. `baseline_v1` → `baseline`, `metrics_v1` → `metrics`). The table below gives guidance on what to expect — but the actual strategy decision is made in Step 5 based on how many fields you extracted in Step 2 (≤ 25 → targeted, > 25 → sectional).

| Table type | Typical field count | Strategy |
|---|---|---|
| `events` / `metrics` | 100–500+ | Targeted: request only fields from Step 2 |
| `crash` | 30–100 | Sectional or targeted |
| `baseline` | 30–60 | Sectional: request by category (e.g. `client_info.*`) |
| `newtab` | 20–80 | Targeted or sectional depending on field count |
| `first_session` | 10–30 | Usually safe to request full schema |
| `deletion_request` | < 10 | Safe to request full schema |

### Step 5: Fetch descriptions with targeted WebFetch

Use your cleaned field list from Step 2 (after alias review). Never copy the script's SUGGESTED WebFetch PROMPT directly — it uses the raw unreviewed field list and may include alias false positives.

**Choose your strategy by field count:**
- **≤ 25 fields** → use the targeted prompt (single request)
- **> 25 fields** → use the sectional prompt (one request per category: `client_info`, `metrics`, `ping_info`, etc.)

**Targeted prompt:**
```
WebFetch:
URL: https://dictionary.telemetry.mozilla.org/apps/<app_id>/tables/<table>
Prompt: "Extract the name, BigQuery type, mode (NULLABLE/REPEATED), and description for
these fields only: <comma-separated cleaned list from Step 2>. Return as a list with one entry per field."
```

**Sectional prompt** (for tables with 25+ fields — repeat per category):
```
WebFetch:
URL: https://dictionary.telemetry.mozilla.org/apps/<app_id>/tables/<table>
Prompt: "Extract the name, BigQuery type, mode (NULLABLE/REPEATED), and description for
all fields in the <category> section (e.g. client_info, ping_info, metrics). Return as a list with one entry per field."
```

Categories are the top-level field groupings shown on the Glean Dictionary table page (e.g. `client_info`, `ping_info`, `metadata`, `metrics`). Visit the dictionary URL directly to see the structure for your table and identify which categories to request. For `metrics` tables with many metric types, you can also request by sub-type (e.g. `metrics.string`, `metrics.counter`).

### Step 6: Map Glean types to BigQuery types

Use this mapping when writing descriptions to `schema.yaml`:

| Glean type | BigQuery type | Notes |
|---|---|---|
| `string` / `text` / `url` | STRING | |
| `quantity` / `counter` | INTEGER | |
| `boolean` | BOOLEAN | |
| `datetime` | TIMESTAMP | |
| `date` | DATE | |
| `uuid` / `jwe` | STRING | |
| `labeled_counter` / `labeled_boolean` / `labeled_string` | RECORD | Nested key/value pa