ducklens 0.1.0

Score how much of your Snowflake or BigQuery bill fits on one DuckDB machine

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 scores every query, rolls the scores up per warehouse into a move, split, or keep decision, and reconciles the totals to your metered invoice.

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 that holds a few gigabytes in memory. What breaks a single machine is the working set, not the scan. So the real question is whether a query spilled, and ducklens answers it from 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

uv venv -p 3.12 .venv
uv pip install --python .venv/bin/python -e '.[dev]'
.venv/bin/ducklens --help

Try it without an account

demo generates synthetic history and runs a full audit:

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 footprints, and replays them across a few warehouses:

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.

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. It needs the [snowflake] or [bigquery] extra.

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 leads and scan size 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 blocked query is attributed to a single flag, so the residual dollars never double-count.

Cost comes from your metered credits, spread across queries by runtime and calibrated so the per-query numbers sum back to what you were billed. The headline anchors to METERING_DAILY_HISTORY. Idle warehouse time and serverless spend are separate lines, never folded into the movable number. On BigQuery the model 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 5.8 million query sample across 1,290 warehouses, ducklens scores in about 20 seconds and puts 81% 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 this.

When it says stay

The report is built 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

.venv/bin/pytest

License

MIT.