# BigQuery integration

## Authentication

Use Application Default Credentials. Prefer, in order:

1. An attached service account when running on Google Cloud.
2. Service-account impersonation for local development.
3. Workload Identity Federation outside Google Cloud.
4. A service-account JSON key only when the preceding options are unavailable.

Never store credentials in the plugin, case files, prompts, logs, or generated invoice documents. `GOOGLE_APPLICATION_CREDENTIALS` may point to a local credential file, but the file itself must remain outside the repository.

## Minimum permissions

- Grant `roles/bigquery.jobUser` on the project that creates and pays for query jobs.
- Grant `roles/bigquery.dataViewer` on each source dataset.
- Grant `roles/bigquery.dataEditor` only on a dedicated destination dataset when results must be written back.
- Grant `roles/iam.serviceAccountTokenCreator` to local users only when impersonation is required.

## Query requirements

- Treat the query Job Project as optional input. When omitted, let ADC infer it and record the actual client project in job evidence.
- Use an explicit Job Project when billing ownership must not change with credentials.
- Use GoogleSQL and named parameters.
- Use half-open billing periods: `timestamp >= period_start AND timestamp < period_end`.
- Select explicit columns; avoid `SELECT *` in frozen billing SQL.
- Add partition filters and a maximum bytes billed limit.
- Use `query_bigquery.py --auto-execute` so the runner performs a dry run before executing.
- Keep queries read-only in the M1 integration.
- Treat location as a per-query property. Leave it unset for automatic inference, but record the resolved job location in evidence.
- A single query may reference tables across projects only when all referenced datasets use the same exact location.
- Query different locations separately and combine their normalized outputs in the calculation layer.
- Save query text hash, job project, location, Job ID, bytes processed, bytes billed, cache status, timestamps, and result schema.
- Preserve `NUMERIC`/`BIGNUMERIC` values as decimals.
- Let `query_bigquery.py` validate generated SQL internally before submitting a Job. Use `scripts/bq_validate.py` separately only when SQL review is needed without execution.
- Cap downloaded result rows with `--max-result-rows` or `policy.maximum_result_rows` in the semantic model.

## Dynamic metadata discovery

Use `scripts/bq_catalog.py` to list allowed datasets/tables, describe a table, search columns, or resolve a reviewed entity alias. Catalog calls read metadata rather than table rows and cache schemas outside the plugin installation. The bundled semantic model is intentionally not a complete warehouse schema.

Access is controlled by `policy.allowed_tables` in the active semantic model. A pattern such as `gcp-digital-bi.metadb_new.*` permits dynamic queries of every table in that Dataset; add another reviewed pattern to a project override when another Dataset is needed.

## Runner parameter file

`scripts/query_bigquery.py` accepts a JSON object:

```json
{
  "customer_id": {"type": "STRING", "value": "customer-a"},
  "period_start": {"type": "DATE", "value": "2026-06-01"},
  "period_end": {"type": "DATE", "value": "2026-07-01"}
}
```

Supported scalar types include `STRING`, `INT64`, `FLOAT64`, `BOOL`, `DATE`, `DATETIME`, `TIMESTAMP`, `NUMERIC`, and `BIGNUMERIC`.