# Dynamic BigQuery query workflow

Use this workflow for ad hoc analysis and for the query-discovery phase of a manual billing case.

## Resolve data sources

1. Try `bq_catalog.py resolve-entity <name>` for known business aliases.
2. If unresolved, list allowed tables and search likely column names. Describe only the candidate tables needed for the request.
3. Treat live Catalog metadata as authoritative for physical columns and types. Treat the semantic model as authoritative only for reviewed aliases, defaults, and safety policy.
4. Verify table Locations before joins. Query different Locations separately and combine normalized results outside BigQuery.
5. For joins, identify the key from schema and reviewed business knowledge. Do not infer a financially material join solely from similar column names.

Catalog examples:

```bash
"$MANUAL_INVOICE_PYTHON" "$SKILL_DIR/scripts/bq_catalog.py" \
  list-tables --dataset gcp-digital-bi.metadb_new

"$MANUAL_INVOICE_PYTHON" "$SKILL_DIR/scripts/bq_catalog.py" \
  search-columns --dataset gcp-digital-bi.metadb_new --contains tenant

"$MANUAL_INVOICE_PYTHON" "$SKILL_DIR/scripts/bq_catalog.py" \
  describe-table gcp-digital-bi.metadb_new.some_table
```

Catalog responses are cached under `${MANUAL_INVOICE_CACHE_DIR:-~/.cache/manual-invoice}/catalog`. Use `--refresh` when current schema is required.

## Generate SQL directly

After resolving the required schemas, generate SQL immediately. Preserve the generated SQL and its parameter file as evidence. A separate `query-plan.json` is optional for ad hoc queries and required only when a billing case needs a reviewed or frozen interpretation.

Before execution, confirm the output grain, dimensions, metrics, filters, joins, parameter types, time field, timezone, and interval boundaries in the generated SQL. Ask only if an unresolved choice would materially change the result.

For a date range written as `2026-04-01-2026-07-10`, treat the end date as inclusive unless the user says otherwise. Query it as a half-open interval ending `2026-07-11T00:00:00` in the selected timezone and record the conversion. Label July as partial when grouping monthly.

## Generate and execute SQL

1. Use fully qualified `project.dataset.table` identifiers.
2. Use named parameters for every user-provided scalar value.
3. Prefer explicit selected columns. `COUNT(*)` is allowed; row-level `SELECT *` produces a warning.
4. Preserve `NUMERIC` and `BIGNUMERIC` as exact decimals.
5. Run `query_bigquery.py --auto-execute`. It validates the SQL, performs a dry run, checks `BQ_MAX_BYTES_BILLED`, and executes automatically when safe.
6. Use `query_bigquery.py --dry-run` only when a human wants to review the estimate before execution.
7. Let the runner cap downloaded rows according to the semantic policy.
8. Never use generated SQL that performs DML, DDL, scripting, exports, procedures, or external federated queries.

## Summarize results

Report the query grain, filters, partial periods, row truncation, source tables, Job Project, Location, and exact saved values. Do not rename undocumented numeric fields to revenue, payable amount, currency totals, or other business concepts unless a reviewed rule defines that mapping.