query-writer

What this skill does

# Query Writer

**Composable:** Works with bigquery-etl-core (for conventions), metadata-manager (for schemas), and sql-test-generator (for tests)
**When to use:** Writing or updating SQL queries (query.sql) or Python ETL scripts (query.py) following Mozilla BigQuery ETL conventions

## Overview

Generate and update SQL queries and Python ETL scripts for the bigquery-etl repository following Mozilla's conventions. This skill handles:
- Writing new query.sql or query.py files
- Updating existing queries
- **MANDATORY: Checking for and updating existing tests whenever queries are modified**
- Coordinating downstream updates to schemas (via metadata-manager) and tests (via sql-test-generator)

## 🚨 REQUIRED READING - Start Here

**BEFORE writing any query, READ these reference files to understand patterns:**

1. **SQL Conventions:** READ `references/sql_formatting_conventions.md`
   - Mozilla's formatting standards
   - Naming conventions
   - Code organization

2. **Common Patterns:** READ `references/common_query_patterns.md`
   - Standard query structures for different use cases
   - When to use CTEs vs subqueries
   - Aggregation patterns

3. **Partitioning:** READ `references/partitioning_patterns.md`
   - Incremental vs full refresh
   - Date partitioning requirements
   - Parameter usage

## 📋 Templates - Copy These Structures

**When writing queries, READ and COPY from these template files:**

**For SQL Queries:**
- **Basic aggregation?** → READ `assets/basic_query_example.sql`
- **Need CTEs?** → READ `assets/cte_query_example.sql`
- **Joining tables?** → READ `assets/join_example.sql`
- **UNNESTing arrays?** → READ `assets/unnest_example.sql`
- **User-level aggregation?** → READ `assets/user_aggregation_example.sql`

**For Python Queries:**
- **API calls or complex logic?** → READ `assets/python_query_template.py`
- Also READ `references/python_queries.md` for Python-specific patterns

## Schema and Description Lookups for Query Construction

When writing queries, you need both schemas (column names/types) and descriptions. **These come from different sources:**

### Schema Priority (Column Names & Types):
1. **FIRST:** Check local `schema.yaml` files in `sql/` directory
2. **LAST RESORT:** Use DataHub when schema not available locally

### Description Priority:
1. **FIRST:** Check Glean Dictionary for `_live`/`_stable` tables (https://dictionary.telemetry.mozilla.org/)
2. **SECOND:** Check local `metadata.yaml` files in `sql/` directory
3. **LAST RESORT:** Use DataHub when descriptions not available

**IMPORTANT:** Glean Dictionary provides **descriptions ONLY**, not schemas. For schemas of `_live`/`_stable` tables, use local `schema.yaml` files or DataHub.

**When to use each source:**
- **Local `/sql` files:** For schemas and metadata of any derived tables in bqetl (most common)
- **Glean Dictionary:** For descriptions of raw ingestion tables (`_live`, `_stable`) ONLY
- **DataHub:** Last resort for schemas when not available locally, or for descriptions when not in Glean/local files

## 🚨 Configuration Standards

**CRITICAL: Only use documented patterns and configurations!**

When writing queries, **ONLY use query patterns, SQL conventions, and partitioning configurations that are documented in:**
- Reference files in this skill (`references/` directory)
- Example queries in this skill (`assets/` directory)
- Existing patterns found in the `/sql` directory

**DO NOT:**
- Invent new query patterns or configurations that aren't documented
- Assume BigQuery features work the same as other SQL dialects
- Use undocumented partitioning or clustering configurations

**ALWAYS reference existing patterns** in the `/sql` directory to see how similar queries are structured.

**Typical workflow for this skill:**

1. **Gather requirements** - Use model-requirements skill if needed to understand what to build
2. **Write query** - Using this skill (query-writer) following Mozilla conventions
3. **Format query** - Run `./bqetl format <path>` to ensure proper SQL formatting
4. **Validate query** - Run `./bqetl query validate <path>` to check SQL syntax and conventions
5. **Generate schema/metadata** - Use metadata-manager skill to create schema.yaml and metadata.yaml (ONLY if validation passes)
6. **🚨 MANDATORY: Check for and update tests** - ALWAYS look for existing tests and update/create them (see Test Management section below)

## Quick Start

### SQL or Python?

**Use query.sql for:**
- Standard data transformations (95% of tables)
- Aggregations and GROUP BY operations
- Joins, CTEs, window functions
- Standard BigQuery operations

**Use query.py for:**
- API calls to external services
- Complex pandas transformations
- Multi-project queries or INFORMATION_SCHEMA operations
- Custom business logic clearer in Python

### Basic SQL Structure

```sql
-- Brief comment explaining the query's purpose
SELECT
  submission_date,
  sample_id,
  client_id,
  COUNT(*) AS n_total_events,
FROM
  `moz-fx-data-shared-prod.telemetry.events`
WHERE
  submission_date = @submission_date
GROUP BY
  submission_date,
  sample_id,
  client_id
```

**Key conventions:**
- Uppercase SQL keywords
- 2-space indentation
- Each field on its own line
- Always filter on `submission_date = @submission_date` for incremental queries

### Partitioning Requirements

**Incremental queries (most common):**
- Accept `@submission_date` parameter
- Output `submission_date` column matching parameter
- Filter: `WHERE submission_date = @submission_date`

**Full refresh queries:**
- No `@submission_date` parameter
- Set `date_partition_parameter: null` in metadata.yaml

## SQL Formatting

**ALWAYS format SQL queries using the bqetl formatter:**

```bash
# Format a specific query file
./bqetl format sql/moz-fx-data-shared-prod/telemetry_derived/events_daily_v1/query.sql

# Format an entire query directory
./bqetl format sql/moz-fx-data-shared-prod/telemetry_derived/events_daily_v1/

# Check if formatting is correct without modifying files
./bqetl format --check sql/moz-fx-data-shared-prod/telemetry_derived/events_daily_v1/
```

**Why formatting matters:**
- Ensures consistent code style across the repository
- Makes queries easier to read and review
- Required for CI/CD pipeline to pass
- Automatically handles indentation, keyword casing, and line breaks

**When to format:**
- Immediately after writing or modifying any SQL query
- Before running `./bqetl query validate`
- Before committing changes to git

**Note:** The `./bqetl query validate` command includes formatting checks, but it's better to run `./bqetl format` first to automatically fix any formatting issues rather than just checking for them.

## Assets (Examples)

The `/assets` directory contains complete query examples:

- `basic_query_example.sql` - Simple aggregation pattern
- `cte_query_example.sql` - Using CTEs for complex logic
- `user_aggregation_example.sql` - User-level metrics
- `join_example.sql` - Standard JOIN with partition filters
- `unnest_example.sql` - UNNEST for repeated fields
- `python_query_template.py` - Python query structure

## References (Detailed Documentation)

The `/references` directory contains detailed guides:

- `sql_formatting_conventions.md` - Formatting rules, UDF usage, header comments
- `partitioning_patterns.md` - Incremental vs full refresh patterns
- `jinja_templating.md` - Jinja functions and date handling
- `common_query_patterns.md` - Event processing, JOINs, performance tips
- `python_queries.md` - When to use Python, common patterns, best practices
- `external_documentation.md` - Links to official docs and example queries
- `test_update_workflow.md` - Workflow for updating existing queries and coordinating test updates

### DataHub Usage (CRITICAL for Token Efficiency)

**BEFORE using any DataHub MCP tools (`mcp__datahub-cloud__*`), you MUST:**
- **READ `../bigquery-etl-core/references/datahub_best_practices.md`** - Token-efficient query patterns and priority order
- Always prefer local