metadata-manager

What this skill does

# Metadata Manager

**Composable:** Works with bigquery-etl-core (for conventions), query-writer (for queries), and sql-test-generator (for test updates)
**When to use:** Creating/updating DAG configurations, schema.yaml and metadata.yaml files, coordinating test updates when queries are modified

## Overview

Generate and manage schema.yaml, metadata.yaml files, and DAG configurations following Mozilla BigQuery ETL conventions. This skill handles:
- Creating new DAGs when no suitable existing DAG is found
- Generating schema and metadata files for new tables with intelligent descriptions
  - Gets schema structure from /sql directory or DataHub
  - Gets descriptions from Glean Dictionary (for `_live`/`_stable`), /sql directory, or DataHub
  - **Proactively detects ANY missing descriptions and asks user if they want to add them to source tables**
  - **Auto-generates missing descriptions when adding new tables or editing existing queries**
  - Improves descriptions with context and clarity
  - Recommends updates to source tables when descriptions are unclear
- Coordinating test updates when queries are modified (handles simple updates directly, invokes sql-test-generator for complex fixture creation)

**For comprehensive documentation, see:**
- Creating derived datasets: https://mozilla.github.io/bigquery-etl/cookbooks/creating_a_derived_dataset/
- Scheduling reference: https://mozilla.github.io/bigquery-etl/reference/scheduling/
- Recommended practices: https://mozilla.github.io/bigquery-etl/reference/recommended_practices/

## 🚨 REQUIRED READING - Start Here

**BEFORE creating or modifying metadata/schema/DAG files, READ these references:**

1. **Schema Discovery:** READ `references/schema_discovery_guide.md` (for schema generation)
   - Priority order: /sql → Glean Dictionary → DataHub (last resort/validation)
   - Token-efficient patterns for getting source schemas
   - When to use each source

2. **DAG Discovery:** READ `references/dag_discovery.md`
   - How to find the right Airflow DAG for your table
   - Common DAG patterns

3. **DAG Creation:** READ `references/dag_creation_guide.md` (when creating new DAGs)
   - When to create a new DAG vs reuse existing
   - Complete DAG creation workflow
   - Configuration options and best practices

4. **Metadata YAML Guide:** READ `references/metadata_yaml_guide.md`
   - All metadata.yaml options and their meanings
   - Scheduling configuration
   - Partitioning and clustering options
   - Ownership and labels

5. **Schema YAML Guide:** READ `references/schema_yaml_guide.md`
   - Field types and modes (REQUIRED, NULLABLE, REPEATED)
   - Nested and repeated structures
   - Description best practices

6. **Schema Description Improvements:** READ `references/schema_description_improvements.md`
   - When to improve source descriptions
   - How to recommend updates to source tables
   - Description improvement checklist

7. **Glean Dictionary Patterns:** READ `references/glean_dictionary_patterns.md` (for _live/_stable tables)
   - Token-efficient extraction from large files
   - Finding Glean Dictionary files
   - Common table patterns

## 📋 Templates - Copy These Structures

**When creating a new DAG, READ and COPY from these templates:**

- **Daily scheduled DAG?** → READ `assets/dag_template_daily.yaml`
  - Most common pattern for daily processing at 2 AM UTC

- **Hourly scheduled DAG?** → READ `assets/dag_template_hourly.yaml`
  - For real-time or frequent processing (every hour)

- **Custom schedule DAG?** → READ `assets/dag_template_custom.yaml`
  - For weekly, multi-hour, or specific time requirements

**When creating metadata.yaml, READ and COPY from these templates:**

- **Daily partitioned table?** → READ `assets/metadata_template_daily_partitioned.yaml`
  - Most common pattern for daily aggregations

- **Hourly partitioned table?** → READ `assets/metadata_template_hourly_partitioned.yaml`
  - For real-time or hourly aggregations

- **Full refresh table?** → READ `assets/metadata_template_full_refresh.yaml`
  - For tables that recalculate all data each run

**When creating schema.yaml, READ and COPY from these templates:**

- **Simple flat schema?** → READ `assets/schema_template_basic.yaml`
  - Basic field types and descriptions

- **Nested/repeated fields?** → READ `assets/schema_template_with_nested.yaml`
  - RECORD types and array structures

## Quick Start

### Creating New Table Metadata

**Step 1: Find or create the appropriate DAG (use this priority order)**
1. **FIRST:** Search local `dags.yaml` files using grep for keywords related to the dataset/product
2. **SECOND:** Check `references/dag_discovery.md` for common DAG patterns
3. **IF NO SUITABLE DAG EXISTS:** Create a new DAG
   - **READ `references/dag_creation_guide.md` for when to create vs reuse**
   - Present DAG options to the user (existing similar DAG vs new DAG)
   - If creating new DAG:
     - Choose template: `assets/dag_template_daily.yaml`, `dag_template_hourly.yaml`, or `dag_template_custom.yaml`
     - Infer values from context (dataset name, product area, similar tables)
     - Ask user to confirm/modify: schedule, owner, start_date, impact tier
     - Add new DAG entry to the **bottom** of `dags.yaml`
     - Validate with `./bqetl dag validate <dag_name>`

**Step 2: Create directory structure**
```bash
./bqetl query create <dataset>.<table> --dag <dag_name>
```

**Step 3: Create metadata.yaml**
- Use templates from `assets/` as a starting point
- Choose the appropriate template based on partitioning strategy
- Customize with owners, description, labels, scheduling, and BigQuery config
- See `references/metadata_yaml_guide.md` for detailed options

**Step 4: Create schema.yaml with intelligent descriptions**
- **If query.sql exists:** Run `./bqetl query schema update <dataset>.<table>` to auto-generate structure
- **Get source table schemas** using priority order (see `references/schema_discovery_guide.md`):
  1. Check `/sql` directory for schema structure (ALWAYS FIRST)
  2. Use DataHub for schema structure (when not in `/sql`)
- **Get descriptions** using priority order:
  1. Glean Dictionary for `_live`/`_stable` table descriptions via https://dictionary.telemetry.mozilla.org/
  2. `/sql` directory schema.yaml files for derived tables
  3. DataHub for descriptions (when not in above sources)
  - **IMPORTANT:** Glean Dictionary provides descriptions only, NOT schemas
- **Apply base schema descriptions (RECOMMENDED):**
  - Run: `./bqetl query schema update <dataset>.<table> --use-global-schema`
  - For ads data: `./bqetl query schema update <dataset>.<table> --use-dataset-schema --use-global-schema`
  - Auto-populates descriptions for standard fields (submission_date, client_id, dau, etc.)
  - See "Using Base Schemas for Auto-Populating Descriptions" section below for details
- **Check for ANY missing descriptions:**
  - **For source tables:** Notify user and ask if they want to generate descriptions for source tables
  - **For new tables or editing existing queries:** Auto-generate ANY missing descriptions (no asking)
  - This improves metadata completeness for all downstream consumers
- **Use source descriptions as base** and improve them:
  - Add context specific to derived table
  - Clarify transformations
  - Fill gaps in source descriptions
- **Generate recommendations** for source table description updates when needed
- See `references/schema_description_improvements.md` for improvement workflow
- See `references/glean_dictionary_patterns.md` for token-efficient Glean Dictionary usage

**Step 5: Deploy schema (if updating existing table)**
```bash
./bqetl query schema deploy <dataset>.<table>
```

### Updating Existing Queries

When modifying an existing query.sql, follow the test update workflow:

**1. Check for existing tests:**
```bash
ls tests/sql/<project>/<dataset>/<table>/
```

**2. Update the query.sql file** with your changes

**3. Update schema.yaml if output changed:**
```bash
./bqetl query schema