schema-enricher

What this skill does

# Schema Enricher

**Composable:** Orchestrates column-description-finder (for finding descriptions)
**When to use:** Creating a new schema.yaml, filling missing column descriptions, validating schema columns against the query output, or auditing an existing schema for completeness

## 🚨 REQUIRED READING - Start Here

**BEFORE performing any schema work, review the following:**

1. **Column description sources — note:** Base schema files (`global.yaml`, `app_<name>.yaml`, dataset-specific YAML) are fetched live from GitHub by the `column-description-finder` skill in Step 0c. Do not read these files inline — wait for the skill invocation in Step 0c.

2. **Schema YAML conventions:** READ `references/schema_yaml_conventions.md`
   - Field types, modes, description quality standards
   - Canonical field names and how aliases work

3. **Result file format:** READ `assets/missing_descriptions_template.yaml`
   - Template for capturing columns whose descriptions were derived from upstream source schema, query context, or application context (i.e., not found in a base schema)
   - Used to recommend additions to base schema files

## Workflow

### Step 0: Audit base schema coverage using `column-description-finder` ⚠️ MANDATORY — do not skip

**BEFORE looking up any column descriptions**, invoke the `column-description-finder` skill to audit base schema coverage. The skill fetches live base schema files from GitHub and uses deterministic scripts — avoiding the hallucination risk of reading files inline.

**0a — Identify the applicable schemas:**
- If `metadata.yaml` exists, check for an `app_schema:` field (e.g., `app_newtab`). If absent, proceed without an app schema flag.
- Note the dataset name for dataset-specific schema lookup.

**0b — Confirm schema.yaml and query file:**
- Check for `query.sql` and read it to understand SELECT output columns:
  - **If `query.sql` exists:** read it ✓
  - **If only `query.py` exists:** note it — Step 3 column validation will be skipped since column output cannot be reliably parsed from Python
- Check whether `schema.yaml` exists:
  - **If it exists:** proceed to Step 0c
  - **If it does not exist:** generate it first:
    ```bash
    ./bqetl query schema update <dataset>.<table>
    ```
    Then read the generated `schema.yaml` before proceeding to Step 0c.

**0c — Invoke `column-description-finder` skill:**

Run the audit script with the appropriate flags:
```bash
# With app schema:
python scripts/audit_base_schema_coverage.py <dataset>.<table> --app-schema <app_schema> --dataset-schema

# Without app schema:
python scripts/audit_base_schema_coverage.py <dataset>.<table> --dataset-schema
```

Capture the full output — it identifies:
- Columns covered by base schemas (and which file covers each)
- Columns with existing descriptions not in base schemas
- Columns with no description at all

⚠️ **Wait for the skill to complete and capture its full output before proceeding to Step 1.**

### Step 1: Categorize columns from audit output

Using the audit output from Step 0c, categorize every column:

| Column status | Source | Action |
|---|---|---|
| Covered by live base schema | audit output (column → source file) | Use the base schema description |
| Has own description | audit output | Retain existing description |
| No description (not in live schemas) | audit output | Check local base schema files first; if found → `local_only_base_schema_columns`; if not → flag for Step 2 |

**Checking local base schema files:** The `column-description-finder` audit fetches schemas live from GitHub. If a base schema file exists locally but not on GitHub main (returns 404 when WebFetched), the audit will not find descriptions from it. For columns marked "no description" by the audit:

1. Check local `bigquery_etl/schema/*.yaml` files for a match
2. If found — the description is valid but sourced from a **local-only** file. Tag the column as `local-only` and capture it in `local_only_base_schema_columns` in Step 6.
3. If not found in any local file either — flag for description fill in Step 2.

Base schema priority order applied by the audit script is defined in `skills/column-description-finder/references/column_definition_yaml_guide.md` (app-specific → dataset-specific → global).

Proceed to Step 2 for all columns with no description in any base schema (live or local).

### Step 2: Fill missing descriptions

For each column without a current description in schema.yaml, use this priority order:

1–3. **Base schemas** — descriptions are already identified in the Step 0c audit output. Apply them directly from the audit results (app-specific → dataset-specific → global).

4. **Upstream source schema.yaml** — for columns that are pass-through dimensions from a source table:
   - Identify the source table(s) from the FROM clause of `query.sql`
   - If the source table ends in `_live` or `_stable` (a Glean ingestion table): invoke the `glean-description-lookup` skill to fetch descriptions from the Glean Dictionary — no local `schema.yaml` exists for those tables
   - Otherwise: locate the source table's directory under `sql/` and read its `schema.yaml`
   - If the column exists and has a description, copy it directly
   - If the upstream `schema.yaml` is absent or the column has no description there, fall through to priority 5

5. **Query context** — examine `query.sql` to understand what the column computes:
   - Aggregation of clicks → "Total number of clicks recorded"
   - Boolean flag → "Whether [feature] is enabled for this user"
   - SAFE_DIVIDE expression → rate or ratio description

6. **Application context** — if no query context is clear, derive description from:
   - Column name semantics
   - Dataset/product area (e.g., newtab, pocket, topsites, search)
   - Related columns nearby in the schema

**⚠️ For any column whose description came from upstream source schema.yaml (priority 4), query context (priority 5), or application context (priority 6) — i.e., not from a base schema — capture it in `<table_name>_missing_metadata.yaml` (Step 6).**

#### Nested RECORD fields

Base schema matching covers top-level fields only (as noted in the Step 0c audit output). For nested fields within RECORD types, apply the same priority order as above — upstream source schema.yaml first, then query context, then application context. Descriptions derived from priorities 4, 5, or 6 for nested fields should also be captured in `<table_name>_missing_metadata.yaml`. Quality check (Step 4) applies to nested field descriptions as well.

### Step 3: Validate columns

Compare columns in `schema.yaml` against the query's SELECT output. Skip this step if only `query.py` exists (noted in Step 0b).

⚠️ **This command may update `schema.yaml` on disk.** Do NOT re-read `schema.yaml` after running — continue working from the field list and descriptions established in Steps 1–2. Use the command output (diff) only to identify structural additions and removals; apply those changes to your in-memory enriched schema, then write everything in Step 5.

```bash
./bqetl query schema update <dataset>.<table>
```

Review the diff to identify:
- **Columns in query but missing from schema.yaml** → add them with descriptions (check Step 0c audit output first; fall back to Step 2 priority order if not found)
- **Columns in schema.yaml but not in query** → flag for removal (confirm with user before deleting)
- **Type mismatches** → correct to match query output

### Step 4: Description quality check

Every description — whether sourced from a base schema, retained from the existing schema.yaml, or derived from context — is verified against:

- [ ] Not empty or null
- [ ] Not a restatement of the column name
- [ ] States what the value represents (count, flag, dimension, rate)
- [ ] Consistent with data type
- [ ] Contextually accurate for the product domain

Failing descriptions are corrected automatically.

### Step 5: Write schema.yaml

Write the enriched `schema.yaml` preserv