Metadata-Version: 2.4
Name: bqlens
Version: 0.2.0
Summary: Find wasted BigQuery spend in one command. Read-only.
License-Expression: Apache-2.0
Keywords: bigquery,gcp,finops,cost-optimization,data-engineering
Classifier: Development Status :: 3 - Alpha
Classifier: Intended Audience :: Developers
Classifier: Programming Language :: Python :: 3
Classifier: Topic :: Database
Requires-Python: >=3.10
Description-Content-Type: text/markdown
License-File: LICENSE
Provides-Extra: bigquery
Requires-Dist: google-cloud-bigquery>=3.11; extra == "bigquery"
Provides-Extra: dev
Requires-Dist: pytest>=7.4; extra == "dev"
Dynamic: license-file

# bqlens

**Find wasted BigQuery spend in one command.**

No signup. No sales call. No agent installed in your VPC. It reads your query
history, tells you where the money is going, and tells you how to stop it.

```bash
pip install bqlens
bqlens scan --project my-project --days 30
```

```
  bqlens — my-project
  1,833 query jobs over the last 30 days

  Spend in window      $     1,946.10
  Projected monthly    $     1,946.10
  Recoverable          $       572.50/mo (29% of spend)

  1. [HIGH] Wildcard table scans without _TABLE_SUFFIX  (R004)
     $179.30/mo recoverable   ·  22 jobs
     ...
```

Try it with no credentials at all:

```bash
bqlens scan --demo
```

---

## What it looks at

bqlens reads `INFORMATION_SCHEMA.JOBS_BY_PROJECT` — job metadata and the SQL
text of your queries.

**It never reads a row of your table data.** There is no row access, no
sampling, no data leaving your project. The whole tool is a read of your own
query log plus regex and arithmetic, running on your machine.

## What it finds

| Rule | What it catches | Why it costs money |
|------|-----------------|--------------------|
| R001 | `SELECT *` on large scans | BigQuery bills per column read |
| R002 | `LIMIT` with no `WHERE` | LIMIT caps rows returned, not bytes scanned |
| R003 | Large scans with no filter | No partition pruning |
| R004 | Wildcard tables without `_TABLE_SUFFIX` | Scans every table in the dataset |
| R005 | The same query shape run over and over | One materialised refresh replaces all of them |
| R006 | Failed jobs that were still billed | Money that bought nothing |
| R007 | Cross joins / joins missing `ON` | Row multiplication |

## Sharing a report

```bash
bqlens scan --project my-project --redact
```

Strips the project name, user emails, SQL text and job IDs. Dollar amounts,
rule IDs, job counts and severities survive, along with stable pseudonyms so
you can still see that one service account is behind most of the waste.

Use it before pasting a report into a ticket, an email, or a chat window.

## If you don't have `bigquery.jobs.listAll`

bqlens falls back to `INFORMATION_SCHEMA.JOBS_BY_USER`, which needs no special
grant, and tells you the scan covers only your own jobs rather than the whole
project. Narrower, but it works without asking anyone for anything.

## Every dollar is counted once

A repeated `SELECT * ... LIMIT` matches three rules at once. If each rule
counted it, the reported savings would add up to more than your actual bill —
and the first thing a sceptical engineer does is add up our numbers.

So rules run in priority order and each job is claimed by exactly one rule,
the one with the most specific fix. The sum of findings can never exceed
actual spend, and there is a test that fails the build if it ever does.

Savings estimates are deliberately conservative. bqlens counts what a stated
fix would plausibly recover, not the full cost of the query. If it says $500,
the intent is that you find $500, not that you find $180 and stop trusting it.

## Usage

```bash
# Basic scan
bqlens scan --project my-project

# Non-US region (the JOBS view is region-qualified)
bqlens scan --project my-project --region region-asia-south1

# Your negotiated rate rather than list price
bqlens scan --project my-project --price-per-tib 5.00

# A page you can forward to your manager
bqlens scan --project my-project --format html --out report.html

# Machine-readable
bqlens scan --project my-project --format json

# Fail CI if waste creeps above a threshold
bqlens scan --project my-project --fail-over 200
```

### Options

| Flag | Default | Meaning |
|------|---------|---------|
| `--project` | — | GCP project to scan (required unless `--demo`) |
| `--region` | `region-us` | Region of the JOBS view |
| `--days` | `30` | Days of history |
| `--price-per-tib` | `6.25` | On-demand price per TiB scanned |
| `--min-gib` | `1.0` | Ignore queries smaller than this |
| `--min-repeats` | `10` | Repeats before a shape is flagged |
| `--format` | `terminal` | `terminal`, `json`, or `html` |
| `--out` | — | Write to a file |
| `--fail-over` | — | Exit 1 if monthly recoverable exceeds this |
| `--demo` | — | Run on synthetic data, no credentials |

## Permissions

The scanning account needs `bigquery.jobs.listAll` on the project — included
in `roles/bigquery.resourceAdmin`, or grant it directly. Without `listAll` you
will only see your own jobs, not the whole project's.

The metadata query is itself capped at 10 GiB billed, so the scanner can never
become the expensive thing in your bill.

## Install

```bash
pip install bqlens              # CLI + demo mode
pip install 'bqlens[bigquery]'  # adds the BigQuery client for real scans
```

Python 3.10+. Authenticate with `gcloud auth application-default login`.

## Development

```bash
pip install -e '.[dev]'
pytest
```

## How the rules were tested

Unit tests are circular when the same person writes the rule and the test. So
the detectors were also run over **4,000 real `.sql` files from public GitHub
repositories** — 9,269 SELECT statements written by strangers, years before
this tool existed (`scripts/corpus_test.py`).

That found nine real bugs, all now fixed and covered by regression tests built
from the offending snippets:

- `EXISTS (SELECT *)` reads no columns — it was being flagged as a wide read
- SQL inside string literals was being analysed as if it were code
- `LIMIT 0` (the clone-a-schema idiom) reads nothing
- `LIMIT` with `ORDER BY`/`GROUP BY` is a deliberate top-N query, not someone
  mistakenly believing LIMIT makes a scan cheap
- Queries with no `FROM` clause, or reading `dual` / `generate_series`, touch
  no storage at all
- `ON(a=b)` with no space was not recognised as an `ON` clause
- `NATURAL JOIN` needs no `ON` clause by definition
- `CROSS JOIN (SELECT AVG(x) FROM t)` attaches one row and multiplies nothing
- The `ON`-clause search window was too short for long queries

Fire rates on that corpus, before and after:

| Rule | Before | After |
|------|--------|-------|
| R001 `SELECT *` | 13.7% | 9.7% |
| R002 LIMIT without WHERE | 0.3% | 0.1% |
| R003 unfiltered scan | 61.6% | 18.6% |
| R007 cross join | 0.9% | 0.5% |

R003 remains the weakest rule and the first place to look if bqlens reports
something that seems wrong.

**What this does not prove:** the corpus is mostly MySQL and Postgres, so the
BigQuery-specific rule (R004, wildcard tables) is barely exercised by it. And
it says nothing about the cost arithmetic, which needs real job metadata.
Reports from real warehouses are what would close that gap — if you run this,
`--redact` makes the output safe to paste into an issue.

## Limitations, stated plainly

- Rules work on query text and job metadata. bqlens does not read table schemas,
  so it cannot know whether a table is partitioned — R003 flags unfiltered scans
  as a *candidate*, not a certainty.
- Savings percentages per rule are heuristics based on typical wide analytics
  tables. Your mileage varies.
- Slot-based (capacity) pricing is not yet modelled; cost figures assume
  on-demand. Reservation support is planned.
- SQL parsing is regex-based, not a full parser. Exotic queries may be missed.
  The corpus test above measures false positives; it cannot measure false
  negatives, because nobody has labelled that corpus.
- The cost estimates have not been validated against a real, busy warehouse.
  If you run this on one, please open an issue with `--redact` output.

## Licence

Apache-2.0
