mozilla-bigquery-etl-skills

Autonomously audits and updates all base schema files in bigquery_etl/schema/. Fixes type mismatches, missing descriptions, missing modes, and cross-file duplicates. Promotes fields from dataset tables into the correct base schema file (global.yaml, app_<product>.yaml, <dataset_name>.yaml) and creates missing dataset schema files when evidence is sufficient. Writes a recommendations document for items requiring human judgment. Re-reviews up to three passes to apply further safe changes. Optionally creates a PR when requested.

Autonomously builds complete BigQuery data models from provided requirements through testing and monitoring. Use when you have clear requirements and need full ETL pipelines, new tables, or complex multi-step data workflows implemented end-to-end.

Autonomously picks up pre-generated _missing_metadata.yaml files from bigquery_etl/schema/missing_metadata/ and updates the base schema files in bigquery_etl/schema/ — fixing type mismatches, adding missing mode and description fields, removing cross-file duplicates, promoting commonly-used fields into the appropriate base schema file (global.yaml, app_<product>.yaml, or <dataset_name>.yaml), and creating new dataset schema files when there is sufficient evidence. Runs up to three self-review passes, writes a recommendations document for items requiring human judgment, and optionally creates a PR when requested.

You are a workflow agent that enriches `schema.yaml` files for BigQuery tables in the Mozilla bigquery-etl repository. You orchestrate the `schema-enricher` skill for schema enrichment, the `schema-readme-generator` skill for README generation, and optionally the `create-pr` skill when the user explicitly requests a PR.

Use this skill to audit tables for missing column descriptions and classify each missing column into the correct base schema promotion target (global.yaml, app_<product>.yaml, or <dataset_name>.yaml). Accepts a dataset name and an optional table filter — omit the filter to audit all tables in the dataset. Outputs a per-column recommended_target report for use in _missing_metadata.yaml. Composable with schema-enricher (Step 6).

Use this skill when creating or updating Bigeye monitoring configurations (bigconfig.yml files) for BigQuery tables. Works with metadata-manager skill.

The core skill for working within the bigquery-etl repository. Use this skill when understanding project structure, conventions, and common patterns. Works with model-requirements, query-writer, metadata-manager, sql-test-generator, and bigconfig-generator skills.

Use this skill when looking up, auditing, or managing column descriptions from global, application-specific, and dataset-specific column definition YAML files (bigquery_etl/schema/global.yaml, bigquery_etl/schema/app_<name>.yaml, and bigquery_etl/schema/<dataset>.yaml). Use it to find a description for a specific column, list all columns in a base schema, audit which columns in a table's schema.yaml are covered by base schemas, or identify columns missing descriptions. Works with schema-enricher skill.

Use this skill when the prompt asks to create, open, or submit a pull request in the bigquery-etl repository. Handles branch creation, staging, committing, pushing, and opening a draft PR with a structured description. Triggered by phrases like "create a PR", "open a PR", "submit a PR", "push and open a PR".

Use this skill when looking up field descriptions for Mozilla Glean telemetry tables (tables ending in _live or _stable, e.g. <app>_stable.<ping>_v1). Fetches descriptions from the Glean Dictionary (dictionary.telemetry.mozilla.org) using WebFetch with targeted field extraction — only the fields referenced in query.sql, never the full table schema.

Use this skill when creating or updating DAG configurations (dags.yaml), schema.yaml, and metadata.yaml files for BigQuery tables. Handles creating new DAGs when needed and coordinates test updates when queries are modified (invokes sql-test-generator as needed). Works with bigquery-etl-core, query-writer, and sql-test-generator skills.

Use this skill when gathering requirements for new BigQuery data models OR when asked to edit existing queries in bqetl. For new models, guides structured requirements interviews. For existing queries, understands current model, checks downstream dependencies, and gathers requirements for changes. Works as pre-planning before query-writer skill.

Use this skill when writing or updating SQL queries (query.sql) or Python ETL scripts (query.py) following Mozilla BigQuery ETL conventions. ALWAYS checks for and updates existing tests when modifying queries. Coordinates downstream updates to schemas and tests. Works with bigquery-etl-core, metadata-manager, and sql-test-generator skills.

Use this skill to enrich schema.yaml files for BigQuery tables in the bigquery-etl repository. Handles creating schema.yaml when it doesn't exist, finding and filling missing column descriptions (from base schemas, upstream source schema, query context, or application context), validating columns against the query, and generating a summary with recommendations for where to add new descriptions (global.yaml, <dataset_name>.yaml, or app_<name>.yaml). Works with column-description-finder skill.

Use this skill to create or update README.md files for BigQuery ETL tables in the mozilla bigquery-etl repository. Follows layout conventions derived from comparing README files across the repo — rich style with emoji headings, Mermaid data flow diagram, graduated example queries, and concise metadata overview table. Requires schema.yaml with complete descriptions (run schema-enricher first if needed) and a complete metadata.yaml.

ALWAYS use this skill when users ask to "create a skill", "make a skill for...", "add a new skill", or similar requests. This skill guides the creation of effective skills in the bigquery-etl repository that extend Claude's capabilities with specialized knowledge for BigQuery ETL workflows, Mozilla data platform conventions, or telemetry analysis. CRITICAL - First checks for conflicts with existing skills and recommends using/updating existing skills when appropriate. Do NOT attempt to create skills without invoking this skill first.

ALWAYS use this skill when users ask to create, generate, or write UNIT TESTS for BigQuery SQL queries. Invoke proactively whenever the request includes "test" or "tests" with a query/table name. This skill is for unit testing ONLY (not data quality checks - use bigconfig-generator for Bigeye monitoring). Works with bigquery-etl-core skill to understand query patterns.