Metadata-Version: 2.4
Name: ducklens
Version: 0.1.1
Summary: Score how much of your Snowflake or BigQuery bill fits on one DuckDB machine
Project-URL: Homepage, https://ducklens.dev
Project-URL: Repository, https://github.com/munimdev/ducklens
Project-URL: Documentation, https://ducklens.dev/tool
Author: Munim Zafar
License-Expression: MIT
License-File: LICENSE
Keywords: bigquery,cost,duckdb,finops,migration,snowflake
Classifier: Environment :: Console
Classifier: License :: OSI Approved :: MIT License
Classifier: Programming Language :: Python :: 3
Classifier: Topic :: Database
Classifier: Topic :: Utilities
Requires-Python: >=3.10
Requires-Dist: duckdb>=1.5.0
Requires-Dist: pyarrow>=14.0
Requires-Dist: pytz>=2024.1
Requires-Dist: rich>=13.0
Requires-Dist: typer>=0.12
Provides-Extra: bigquery
Requires-Dist: google-cloud-bigquery>=3.0; extra == 'bigquery'
Provides-Extra: dev
Requires-Dist: mypy>=1.10; extra == 'dev'
Requires-Dist: pytest-cov>=5.0; extra == 'dev'
Requires-Dist: pytest>=8.0; extra == 'dev'
Requires-Dist: ruff>=0.6; extra == 'dev'
Provides-Extra: snowflake
Requires-Dist: snowflake-connector-python>=3.0; extra == 'snowflake'
Description-Content-Type: text/markdown

# ducklens

ducklens reads your Snowflake or BigQuery query history and works out how much of the bill could run on a single DuckDB machine. It goes query by query, rolls the results up per warehouse into a move, split, or keep call, and reconciles the totals to your metered invoice.

Site and write-ups: https://ducklens.dev

This is worth measuring because DuckDB is an out-of-core engine. A query that scans two terabytes but streams through a filter and an aggregate runs fine on a box with a few gigabytes of memory. What actually breaks a single machine is a query whose working set outgrows memory, which often has little to do with how much it scanned. So the real question is whether a query spilled, and ducklens decides on observed spill instead of guessing from scan size.

The scorer is one SQL file, `ducklens/scoring.sql`. Every threshold is a named key you can override. Read it, disagree with a number, change it, and re-run.

## Install

```bash
pipx install ducklens        # or: pip install ducklens, or: uv tool install ducklens
ducklens --help
```

Add the `[snowflake]` or `[bigquery]` extra if you want the tool to run the read-only export for you: `pipx install "ducklens[snowflake]"`.

## Try it without an account

`demo` generates synthetic history and runs a full audit:

```bash
ducklens demo
ducklens demo --source bigquery
```

For a real analytical workload, `scripts/tpch_to_history.py` runs TPC-H locally, captures the actual execution times and scan sizes, and replays them across a few warehouses:

```bash
python scripts/tpch_to_history.py --sf 8 --days 90 --ram-gb 8 --out ./tpch
ducklens audit --source snowflake \
  --query-history ./tpch/query_history.parquet \
  --metering ./tpch/warehouse_metering_history.parquet \
  --metering-daily ./tpch/metering_daily_history.parquet \
  --ram-gb 8 --db audit.duckdb
```

Example output:

```
43% of your total Snowflake bill is movable query compute
    = $31,379/yr of $72,178/yr invoice

GROSS ANNUAL RUN-RATE DELTA   $18,410 - $24,908

HYBRID SPLIT
  BI_SERVING_WH   100%   $19,769   MOVE
  DBT_WH           78%   $14,386   SPLIT
  AD_HOC_WH         6%    $6,804    KEEP
```

## Audit your own account

You run a read-only export and ducklens reads the files locally. Nothing leaves your machine and it never sees a credential. The export SQL is in `ducklens/export_sql/`. `snowflake_export.sql` copies the three Account Usage views to a temp stage as parquet, with a switch to drop query text if you would rather not share it.

```bash
ducklens audit --source snowflake \
  --query-history 'query_history*.parquet' \
  --metering 'warehouse_metering*.parquet' \
  --metering-daily 'metering_daily*.parquet' \
  --db audit.duckdb

ducklens audit ... --format html -o report.html
ducklens explain <query_id> --db audit.duckdb
```

`ducklens pull` runs the read-only export for you if you would rather hand it credentials.

## How it scores

A query fits unless a flag says otherwise. The flags, in priority order:

- remote spill, or local spill past the box's memory
- warehouse-specific SQL that would not port
- sustained high concurrency, which is a serving workload rather than a batch one
- multi-cluster scale-out
- long queue times
- stored procedures, multi-statement write transactions, and high-frequency writes

Spill is what decides it, and a large scan never blocks a query on its own. Concurrency counts only sustained overlap: sixteen 200ms dashboard pings score zero, while sixteen overlapping 30-second queries score sixteen. Each held-back query is blamed on a single flag, so the residual dollars do not double-count.

Cost comes from your metered credits, spread across queries by runtime and calibrated so the per-query numbers add back up to what you were billed. The headline is anchored to `METERING_DAILY_HISTORY`. Idle warehouse time and serverless spend are shown as their own lines, kept out of the movable number. On BigQuery it switches to bytes billed.

The report prints the movable share of the bill, the per-warehouse split, the costliest queries keeping each warehouse in place, a saving range, and a table of how fit changes with box size. `--format` gives you rich, html, markdown, or json.

## On real data

Snowset is a public trace of about 70 million real Snowflake queries, released with the NSDI 2020 paper on Snowflake's architecture. It records real spill bytes per query, which is what the scorer needs. On a random 21 million query sample across 1,690 warehouses, ducklens puts 77% of the query compute in the single-machine range, holding the genuinely spill-heavy warehouses back. `ducklens/export_sql/snowset_to_history.sql` maps the trace onto the input schema so you can reproduce it. There is a full write-up at https://ducklens.dev/blog/snowset-fit-on-one-machine .

## When it says stay

The report is meant to talk you out of a migration when the numbers do not hold. Below roughly a $40k/yr bill, the machine and object storage cost more than you save. Serving workloads, sustained writes, multi-petabyte estates, and regulated governance surfaces stay on the warehouse. The report says so per warehouse, with the dollars attached.

## Development

```bash
uv venv -p 3.12 .venv
uv pip install --python .venv/bin/python -e '.[dev]'
.venv/bin/pytest
```

## License

MIT.
