---
name: bigquery-historical-data-aggregator
description: Aggregates and analyzes historical data from multiple BigQuery tables with similar schemas. Queries multiple tables using UNION ALL, calculates aggregate metrics (averages, sums, counts), handles table discovery via INFORMATION_SCHEMA, and processes large datasets efficiently with batch queries.
---
# Instructions

## Core Workflow
1.  **Discover Tables:** First, query `INFORMATION_SCHEMA.TABLES` to identify all available tables within the target dataset. This ensures the skill adapts to the actual table names present.
2.  **Aggregate Historical Data:** Construct a SQL query that uses `UNION ALL` to combine data from all identified tables. Calculate the required aggregate metric (e.g., `AVG(score)`) grouped by the relevant key (e.g., `student_id`, `name`).
3.  **Handle Large Results:** For datasets returning many rows (>50), use a batched querying strategy (e.g., `LIMIT` and `OFFSET` or filtering by key ranges) to retrieve the complete result set without truncation.
4.  **Join with Latest Data:** Read the latest data from the provided source (e.g., a local CSV file). Perform a join between the aggregated historical data and the latest data to enable comparative analysis.
5.  **Calculate Deltas & Filter:** Compute the percentage change or difference between historical and latest values. Apply the user-specified threshold filter (e.g., `drop_percentage > 0.25`).
6.  **Output Results:** Write the filtered results to the specified output file (e.g., `bad_student.csv`).
7.  **Trigger Critical Actions:** For records exceeding a higher, critical threshold (e.g., `drop_percentage > 0.45`), execute immediate actions such as writing critical log entries to a designated logging service.

## Key Techniques
*   **Dynamic Table Inclusion:** Use the list from `INFORMATION_SCHEMA` to build the `UNION ALL` query dynamically. Do not hardcode table names.
*   **Efficient Batch Retrieval:** When the final aggregated list or intermediate results are large, retrieve data in manageable chunks using `WHERE` clauses on sequential keys or `LIMIT/OFFSET`.
*   **Precise Percentage Calculation:** Ensure the percentage change formula is correct: `(historical_value - latest_value) / historical_value`.
*   **Logging for Notification:** When writing critical logs, include all necessary identifiers (e.g., name, ID) and context so downstream systems can trigger alerts or notifications.

## Error Handling & Validation
*   Confirm the target dataset exists before querying.
*   Verify that source files (e.g., CSV) exist and are readable.
*   Validate that the log bucket or destination for critical alerts exists and is accessible.

## Bundled Resources
*   `scripts/aggregate_query_template.sql`: A parameterized SQL template for the core aggregation logic.
*   `references/schema_example.md`: An example schema to illustrate the expected table structure.

---