sql-test-generator

What this skill does

# SQL Test Generator (Unit Tests Only)

**Composable:** Works with bigquery-etl-core (for patterns), metadata-manager (for test updates), and query-writer (for query understanding)
**When to use:** Creating/updating unit test fixtures for BigQuery SQL queries, preventing production queries in tests

**NOT for data quality checks:** Use bigconfig-generator skill for Bigeye monitoring. The `checks.sql` file approach is DEPRECATED.

Generate unit test fixtures for BigQuery SQL queries following bigquery-etl conventions. These are development-time tests that validate query logic on small, synthetic data.

**Official Documentation:** https://mozilla.github.io/bigquery-etl/cookbooks/testing/

## Unit Tests vs Data Quality Checks

**CRITICAL DISTINCTION:**

**This skill creates UNIT TESTS:**
- Test query logic during development
- Run on small, synthetic fixtures (not production data)
- Validate transformations, joins, aggregations work correctly
- Part of CI/CD pipeline

**For data quality monitoring (NOT this skill):**
- Use **bigconfig-generator skill** to create Bigeye monitoring configurations
- Monitors production data for quality issues (anomalies, nulls, freshness)
- `checks.sql` files are **DEPRECATED** - do NOT create or update them
- All production data quality checks should be done via Bigeye

## 🚨 REQUIRED READING - Start Here

**BEFORE creating any test fixtures, you MUST read these files:**

1. **CRITICAL FOR SAFETY:** Read `references/preventing_production_queries.md`
   - Prevents accidentally querying production data
   - Explains how to verify fixtures are working correctly
   - Required reading before ANY test creation

2. **UNDERSTAND TEST PATTERNS:** Read `references/test_strategy_patterns.md`
   - Learn which test scenarios are needed for different query types
   - Understand how many tests to create
   - See examples of test coverage strategies

## 📋 Templates - Copy These Structures

**When creating test fixtures, READ and COPY the structure from these template files:**

**For Input Fixtures:**
- **Glean events?** → READ `assets/glean_events_fixture.yaml` and copy its structure
  - Shows proper `client_info`, `events`, and `extra` formatting
  - Use this for any `*_stable.events_v1` or Glean ping tables

- **Legacy telemetry with arrays?** → READ `assets/legacy_array_fixture.yaml`
  - Shows how to structure array fields that get UNNESTed
  - Use for legacy telemetry tables with array columns

- **Simple table?** → READ `assets/simple_test_input.yaml`
  - Basic fixture structure for flat tables

- **UNION ALL query?** → READ both `assets/union_all_fixture1.yaml` AND `assets/union_all_fixture2.yaml`
  - Shows how to create fixtures for multiple data sources
  - Critical: ALL sources need fixtures in the SAME test directory

**For Query Parameters:**
- READ `assets/query_params_example.yaml` - Must be array format, not key-value pairs

**For Expected Output:**
- READ `assets/simple_test_expect.yaml` - Shows array format and NULL handling
- READ `assets/timestamp_example.yaml` - Shows correct TIMESTAMP format (ISO 8601 with timezone)

## Reference Documentation (Read as needed)

- `references/common_test_failures.md` - **READ THIS** when tests fail - Real-world failures and solutions (NULL handling, ordering, timestamps)
- `references/yaml_format_guide.md` - YAML syntax, type inference, nested structures (read if you encounter YAML errors)
- `references/external_documentation.md` - Links to official docs and related resources

### DataHub Usage (CRITICAL for Token Efficiency)

**IMPORTANT: DataHub is for lineage discovery ONLY, NOT for schema lookups**

**For schema discovery, use this priority order:**
1. 🥇 Local `schema.yaml` files in `sql/` directory
2. 🥈 Glean Dictionary for `_live` and `_stable` tables
3. 🥉 BigQuery ETL docs (https://mozilla.github.io/bigquery-etl/)
4. ❌ **NEVER use DataHub for schemas** - use it only for lineage

**When DataHub IS useful:**
- ✅ Discovering upstream/downstream table dependencies
- ✅ Finding which tables are related
- ✅ Understanding table usage patterns

**See these guides:**
- `../metadata-manager/references/schema_discovery_guide.md` - Complete schema discovery workflow
- `../metadata-manager/references/glean_dictionary_patterns.md` - Token-efficient Glean Dictionary usage
- `../metadata-manager/scripts/datahub_lineage.py` - Efficient lineage queries

## Test Structure

Tests live in: `tests/sql/<project>/<dataset>/<table>/<test_name>/`

**Required files:**
- Input fixtures: `<full_table_reference>.yaml` (e.g., `moz-fx-data-shared-prod.telemetry.events.yaml`)
- Expected output: `expect.yaml`
- Query parameters (if needed): `query_params.yaml`

## Critical Requirements

### ⚠️ PREVENTING PRODUCTION QUERIES - READ THIS FIRST ⚠️

**The #1 way tests accidentally query production data:**
- Missing fixture files for ANY table referenced in FROM/JOIN/UNION clauses
- When a fixture is missing, BigQuery falls back to querying the production table

**How to prevent this:**
1. Use `grep -E "FROM|JOIN" query.sql` to identify ALL source tables
2. Create a fixture file for EVERY table found, even if it contributes minimal data
3. If test results show thousands of rows or production-like data, STOP - you're querying production
4. Check the "Initialized" lines in pytest output - every source table should appear

### 1. File Naming - MOST COMMON FAILURE
Input fixtures **must** match how the table is referenced in the query:
- Query uses `moz-fx-data-shared-prod.dataset.table` → file must be `moz-fx-data-shared-prod.dataset.table.yaml`
- Query uses `dataset.table` → file must be `dataset.table.yaml`
- Query uses `table` → file must be `table.yaml`

Wrong naming causes tests to query production BigQuery instead of your fixtures.

### 2. YAML Format
All fixtures must use array syntax (starts with `-`). See `references/yaml_format_guide.md` for details.

```yaml
# Correct - array syntax with dashes
- field1: value1
  field2: value2
```

### 3. Required Fields
- **Input fixtures:** Include ALL fields the query references, even if NULL: `loaded: null`
- **expect.yaml:** **COMPLETELY OMIT** fields that will be NULL - do NOT include them with `field: null`
  - ❌ Wrong: `avg_time_seconds: null`
  - ✅ Correct: omit the field entirely
  - BigQuery does not return NULL fields in results, so including them in expect.yaml will cause test failures
- **CRITICAL for UNION/UNION ALL queries**: You MUST create fixtures for ALL source tables with at least one row of test data. If you want a source to contribute no data, either:
  - Add a fixture with data that gets filtered out by WHERE clauses, OR
  - Create a minimal fixture that passes filters but contributes to the test
  - DO NOT create empty array fixtures (`[]`) - they cause "Schema has no fields" errors
  - DO NOT omit fixture files - missing fixtures cause the query to hit production BigQuery
- Omit `generated_time` columns from `expect.yaml`

### 4. Query Parameters Format
Must be an array (starts with `-`), not key-value pairs. See `assets/query_params_example.yaml` for template.

```yaml
- name: submission_date
  type: DATE
  value: "2024-12-01"
```

### 5. TIMESTAMP Format ⚠️ VERY COMMON FAILURE

**CRITICAL:** BigQuery returns TIMESTAMP and DATETIME fields in ISO 8601 format with timezone.

**In expect.yaml, ALWAYS use ISO 8601 format:**
```yaml
# ✅ CORRECT - ISO 8601 with timezone
- campaign_created_at: "2025-06-01T10:00:00+00:00"
  campaign_updated_at: "2025-07-01T12:00:00+00:00"
```

```yaml
# ❌ WRONG - Missing 'T' and timezone
- campaign_created_at: "2025-06-01 10:00:00"
  campaign_updated_at: "2025-07-01 12:00:00"
```

**Format rules:**
- TIMESTAMP fields: `2025-06-01T10:00:00+00:00` (ISO 8601)
- DATE fields: `2025-06-01` (YYYY-MM-DD)
- With microseconds: `2025-06-01T10:00:00.123456+00:00`

**Input fixtures can use either format, but expect.yaml MUST use ISO 8601.**

See `references/common_test_failures.md` section 3 for details and examples.

## Test Gener