# BigQuery pre-processing scripts

This directory contains standalone Python scripts that prepare BigQuery tables
consumed by the Go service.

## `monthly_cloud_billing_revrec.py`

Prepares the monthly cloud billing revenue recognition inputs used by
`internal/dataplatform/service/bigquery.go`.

The script reads:

- `gcp-digital-bi.dwh_finance.salesforce_org_monthly_revrec`
- `gcp-digital-bi.dwh_finance.salesforce_order_daily_view`

For production-mode runs it writes:

- `gcp-digital-bi-dev.sandbox_dwh_ywang.monthly_revrec_collections`
- `gcp-digital-bi-dev.sandbox_dwh_ywang.monthly_revrec_orders`
- month-suffixed backup tables in `gcp-digital-bi-dev.sandbox_dwh_ywang`

Before any output is published, the script verifies that each selected billing
row's `total_amount` reconciles to its revenue-category amounts within 0.01. A
larger mismatch fails the run with the tenant and billing month instead of
silently omitting or partially processing the row.

Historical reruns can be made explicit with environment variables:

- `MONTHLY_CLOUD_BILLING_BILL_MONTH=YYYY-MM`
- `MONTHLY_CLOUD_BILLING_REVENUE_MODE=recognised|predicted`
- `MONTHLY_CLOUD_BILLING_PREVIEW_RUN_ID=<letters_numbers_underscores>`
- `MONTHLY_CLOUD_BILLING_ORDER_SNAPSHOT_DATE=YYYY-MM-DD`

When a preview run ID is present, the script writes only two isolated tables
whose names end in `_preview_<run_id>`; it does not overwrite the generic or
month-suffixed publish tables. Without overrides, the existing behavior remains
previous-month recognised revenue written to both generic and month-suffixed
targets. Historical reruns should also pin the order snapshot date used by the
original run so later CRM orders cannot retroactively change the allocation.

Commitment-plan allocation uses `revenue_amount` for the raw cap and
`actual_revenue_amount` (falling back to `adjusted_revenue_amount`) for the
adjusted cap. This keeps credits or manual adjustments from changing the raw
revenue split.

Runtime dependencies are not vendored in this repository. Run it from an
environment that has BigQuery credentials plus:

- `google-cloud-bigquery`
- `pandas`
- `numpy`