ducklens 0.1.1

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 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

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:

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:

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.

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

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

License

MIT.