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

-- 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:

# 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 files (schema.yaml, metadata.yaml) over DataHub queries
• Always check Glean Dictionary for _live/_stable tables before using DataHub

Use DataHub ONLY for:

• Schema definitions when not available in /sql directory
• Field descriptions when missing (after checking Glean Dictionary → /sql hierarchy)
• Lineage/dependencies when can't infer from bqetl (telemetry sources, syndicated datasets)
• Syndicated datasets (directories without query.sql/query.py/view.sql - usually from dev teams' postgres databases)

When using DataHub:

• Extract ONLY essential fields (column names/types) from DataHub responses
• Use search → get_entity pattern with limited results
• Check metadata.yaml for syndicated datasets first

🚨 Test Management - MANDATORY FOR ALL QUERY UPDATES

CRITICAL: ALWAYS check for and update existing tests when modifying queries!

When Updating Existing Queries

BEFORE making changes:

1. Check for existing tests:

   ls tests/sql/<project>/<dataset>/<table>/
   

2. Note current source tables:

   grep -E "FROM|JOIN" sql/<project>/<dataset>/<table>/query.sql
   

AFTER making changes:

1. Update schema (if output structure changed):

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

2. 🚨 MANDATORY: Update test fixtures for ALL existing tests:
   • New source table added (JOIN, FROM)? → MUST use sql-test-generator skill to add fixtures to ALL test directories
   • Source table removed? → Delete its fixture files from all test directories
   • Output schema changed? → Update expect.ndjson/expect.yaml in all test directories
   • Query logic changed? → Review and update test input data and expected outputs
3. Run tests:

   pytest tests/sql/<project>/<dataset>/<table>/ -v
   

4. Fix any failures - Update fixtures and expectations until tests pass

When Creating New Queries

After writing query.sql:

1. MUST create unit tests using sql-test-generator skill
2. Tests validate query logic before deployment
3. Required for CI/CD pipeline

Common Test Update Scenarios

Added a new field based on existing source data:

• Update input data in <source_table>.ndjson files to include the field
• Update expected output in expect.ndjson files with expected values for the new field
• Update test schema files if needed (.schema.json files)

Added a JOIN to a new table:

• MUST use sql-test-generator skill to create fixtures for the new table in ALL test directories
• Prevents tests from querying production data (which causes failures)
• Ensures proper data types and schema structure

Changed aggregation logic:

• Update expected values in expect.ndjson to match new calculation logic
• May need to adjust input test data to create meaningful test cases

For detailed workflow: See references/test_update_workflow.md

Data Quality Checks vs Unit Tests

IMPORTANT DISTINCTION:

Data Quality Checks (Bigeye):

• Use bigconfig-generator skill to create Bigeye monitoring configurations
• Bigeye monitors production data for quality issues (anomalies, nulls, freshness, etc.)
• checks.sql files are DEPRECATED and should NOT be used
• All data quality monitoring should be done via Bigeye

Unit Tests (sql-test-generator):

• Use sql-test-generator skill to create unit test fixtures
• Unit tests validate query logic during development
• Tests run on small, synthetic fixtures (not production data)
• Ensures queries work correctly before deployment
• MANDATORY: Must be updated whenever queries are modified

Integration with Other Skills

query-writer works in coordination with other skills:

Works with bigquery-etl-core

• References core skill for project structure and naming conventions
• Uses common patterns, mozfun UDF references, and metric discovery

Works with metadata-manager

• After writing and validating queries: Use metadata-manager to generate/update schema.yaml
• Creates/updates metadata.yaml with scheduling and ownership

Works with sql-test-generator

• After writing and validating queries: Use sql-test-generator to create unit test fixtures
• Prevents production queries by ensuring complete test coverage

Works with bigconfig-generator

• For data quality monitoring: Use bigconfig-generator to create Bigeye configurations
• Monitors production tables for data quality issues (NOT checks.sql)

Typical Workflow

Creating new queries:

1. Use query-writer to write query.sql or query.py
2. Format the query: Run ./bqetl format sql/<project>/<dataset>/<table> to apply Mozilla SQL formatting standards
3. Validate the query: Run ./bqetl query validate sql/<project>/<dataset>/<table> to check for syntax errors and conventions
4. Invoke metadata-manager to generate schema.yaml and metadata.yaml (ONLY if validation passes)
5. Invoke sql-test-generator to create unit test fixtures
6. Run unit tests and validate query works correctly

Updating existing queries:

1. 🚨 FIRST: Check for existing tests: Run ls tests/sql/<project>/<dataset>/<table>/ to see what tests exist
2. Use query-writer to modify query.sql or query.py
3. Format the query: Run ./bqetl format sql/<project>/<dataset>/<table> to apply formatting
4. Validate the query: Run ./bqetl query validate sql/<project>/<dataset>/<table> to ensure changes are valid
5. Invoke metadata-manager to update schema.yaml if output structure changed (ONLY if validation passes)
6. 🚨 MANDATORY: Update tests:
   • If new source table added (JOIN/FROM): MUST invoke sql-test-generator to add fixtures to ALL test directories
   • If source table removed: Delete fixture files from all test directories
   • If output schema changed: Update expect.ndjson in all test directories
   • If query logic changed: Update test input data and expectations
7. Run tests: pytest tests/sql/<project>/<dataset>/<table>/ -v and fix any failures
8. See references/test_update_workflow.md for complete test update workflow and checklist

Performance Essentials

• Filter on partition columns: WHERE submission_date = @submission_date
• Avoid SELECT * - list only needed columns
• Filter before JOINs to reduce shuffling
• Use sample_id for testing: WHERE sample_id = 0 (1% sample)
• Use approximate functions: approx_count_distinct() when exact counts not needed

For detailed optimization: https://docs.telemetry.mozilla.org/cookbooks/bigquery/optimization.html

External Documentation

• Creating derived datasets: https://mozilla.github.io/bigquery-etl/cookbooks/creating_a_derived_dataset/
• Recommended practices: https://mozilla.github.io/bigquery-etl/reference/recommended_practices/
• Common workflows: https://mozilla.github.io/bigquery-etl/cookbooks/common_workflows/
• Query optimization: https://docs.telemetry.mozilla.org/cookbooks/bigquery/optimization.html

Scripts

The /scripts directory is reserved for helper scripts (currently empty).