ducklens 0.2.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 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

Both print a one-page report. From ducklens demo:

39% of your total Snowflake bill is movable query compute
    = $43,442/yr of $112,348/yr invoice

GROSS ANNUAL RUN-RATE DELTA   $28,722 - $38,859

HYBRID SPLIT
  AD_HOC_WH       77%   $18,665   SPLIT
  ANALYTICS_WH    79%   $17,103   SPLIT
  BI_SERVING_WH   37%   $15,032   KEEP
  DBT_WH          85%    $8,926   MOVE
  LOAD_WH         81%    $3,015   MOVE

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:

• spill larger than the box's local disk (DuckDB spills to NVMe, so only spill past that needs scale-out)
• 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 decides it: DuckDB is out-of-core and spills to local NVMe, so a query is held only when its spill is bigger than the box's disk, never for a large scan alone. 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 local disk 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. Run over the full trace, 68.7 million scored queries across about 2,050 warehouses, on one realistic machine with 256GB of RAM and 7.5TB of local NVMe, ducklens puts 71% of the query compute in the single-machine range. It holds back the spill that overflows even that disk and the high-concurrency serving warehouses DuckDB should not take. A RAM-only reading, ignoring that DuckDB spills to disk, is 60%. ducklens/export_sql/snowset_to_history.sql maps the trace onto the input schema so you can reproduce it, and 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.