query-writer

What problem does it solve?

Writing and maintaining BigQuery SQL queries or Python ETL scripts that adhere to specific formatting, conventions, and testing requirements is a meticulous task. This skill automates the creation, updating, and validation of these queries, ensuring consistency and reducing manual effort.

Core Features & Use Cases

• Automated Query Generation: Writes new query.sql or query.py files following Mozilla's BigQuery ETL conventions, including proper formatting, partitioning, and UDF usage.
• Mandatory Test Management: Automatically checks for and updates existing unit tests (coordinating with sql-test-generator) whenever queries are modified, preventing regressions and ensuring data integrity.
• Schema & Metadata Coordination: Integrates with metadata-manager to update schema.yaml and metadata.yaml files when query outputs change, maintaining consistent data definitions.
• Validation & Formatting: Ensures queries are correctly formatted and validated against BigQuery ETL standards, making them ready for deployment.
• Use Case: A developer needs to create a new BigQuery SQL query for a daily aggregation. They use this skill to generate the query.sql file, which then automatically triggers the creation of schema.yaml, metadata.yaml, and unit tests, all adhering to project standards.

Quick Start

Write a BigQuery SQL query to aggregate daily user activity from telemetry.events into my-project.my-dataset.daily_activity_v1. Ensure it uses @submission_date for incremental processing.