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.
pipx install ducklens # or: pip install ducklens, or: uv tool install ducklens
ducklens --helpAdd the [snowflake] or [bigquery] extra if you want the tool to run the read-only export for you: pipx install "ducklens[snowflake]".
demo generates synthetic history and runs a full audit:
ducklens demo
ducklens demo --source bigqueryFor 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:
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.duckdbExample 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
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.duckdbducklens pull runs the read-only export for you if you would rather hand it credentials.
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.
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 it. There is a full write-up at https://ducklens.dev/blog/snowset-fit-on-one-machine .
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.
uv venv -p 3.12 .venv
uv pip install --python .venv/bin/python -e '.[dev]'
.venv/bin/pytestMIT.