warehouse-spend-attributor

Snowflake-flavored FinOps engine on DuckDB. Reconciles per-query attribution against billed warehouse credits, surfaces the idle-padding gap that naive QUERY_HISTORY dashboards hide (21% of the bill in the demo, roughly $72), and attributes every credit to a team so no one gets blamed for someone else's idle warehouse.

What this solves

• QUERY_HISTORY-only dashboards under-report by 20% or more. Summing execution_time_hours * credits_per_hour misses the auto-suspend idle padding that warehouses burn between queries. On the demo dataset the gap is $72 (21% of the bill).
• "Which team owes the gap?" needs an answer, not a shrug. The reconciliation allocates every billed credit to a team by fair-share of active time per warehouse-hour, so the bill ties out exactly.
• The offender list only means something after reconciliation. Ranking queries by naive credits ranks them by execution time; ranking by fair-share credits ranks them by actual dollar cost, including their share of the idle they caused.

Summary

Attributing Snowflake spend to teams starts with QUERY_HISTORY because that is where users and warehouses live. The natural first cut is to compute cost per query as execution_time_hours * credits_per_hour and sum by user. Most FinOps dashboards ship on that method, and it is wrong in a specific way. Warehouse compute is billed by wall clock (WAREHOUSE_METERING_HISTORY.CREDITS_USED_COMPUTE), not by summed query time. When queries overlap, or when a warehouse stays warm for the auto-suspend delay after the last query, the bill charges for seconds no query line item can claim.

This engine runs both methods against a Snowflake-shaped schema (720 queries across 3 warehouses over a 24-hour window) and reconciles them. Naive attribution captures 108.08 credits. The actual bill is 136.88 credits. That 28.30-credit gap (about $72, or 21% of the bill) is auto-suspend idle padding, and under naive attribution no team owns it.

Fair-share attribution closes the gap. For each warehouse-hour, the billed compute credits are allocated to the queries that ran in that hour, proportional to their share of active milliseconds, including idle time. Queries crossing hour boundaries split proportionally between hours. Every credit on the bill ties to a team; the residual is exactly $0.00, gated by an assertion that fails the build if it ever drifts. The reconciliation report shows both attributions side by side per team so the "hidden" money is legible: Analytics was under-reported by $16, Data-Eng by $15, and every team saw between 19% and 24% of their true spend missing from the naive view.

Architecture

flowchart TD
    G["01_generate<br/>QUERY_HISTORY<br/>WAREHOUSE_METERING_HISTORY<br/>users, warehouses"] --> N["02_attribute_naive<br/>credits = time × rate"]
    G --> F["03_attribute_fair_share<br/>per warehouse-hour<br/>proportional to active ms"]
    N --> R["04_reconcile<br/>naive vs fair vs bill<br/>hidden_from_naive per team"]
    F --> R
    F --> O["05_offenders<br/>top queries, inefficient queries<br/>warehouse utilization"]
    F -. "must tie to bill exactly" .-> T["tests/assertions<br/>-bail gated"]

Loading

Tech stack

Technology	Role in this project	Why chosen here
DuckDB	Executes the pipeline against a Snowflake-shaped schema	Zero server, runs in CI with no credentials; SQL is portable to Snowflake with only interval and epoch syntax changes (see ADR-0001)
Pure SQL	All attribution and reconciliation logic	The math is auditable in one screen per file; a Snowflake admin can port and validate it
Snowflake schema (mirrored)	QUERY_HISTORY, WAREHOUSE_METERING_HISTORY, credit rates	The reusable artifact is the SQL, and it stays valuable when the target is real Snowflake
Makefile	run, test, report targets	Explicit pipeline; the report target is the finance-facing artifact
DuckDB error() + -bail	Assertion framework	Tests become real gates on the build; a broken attribution fails CI with exit 1

Quickstart

Prerequisites: DuckDB CLI 1.1 or later.

git clone https://github.com/Vanithanallamothu/warehouse-spend-attributor.git
cd warehouse-spend-attributor

make run     # generate data, run both attributions, print the reconciliation report
make test    # run assertions (a failure aborts with exit 1)
make report  # re-print the finance-facing report without regenerating

Results

The demo covers 720 queries across 3 warehouses over 24 hours. Naive per-query attribution vs. fair-share reconciliation, per team:

Team	Naive credits	Fair credits	Hidden from naive (USD)	% hidden
Analytics	26.58	33.02	$16.08	19.5%
Ops	25.87	31.88	$15.02	18.8%
ML	20.43	26.23	$14.51	22.1%
Data-Eng	19.95	26.08	$15.33	23.5%
Finance	15.24	19.66	$11.05	22.5%
Total	108.08	136.88	$72.00	21.0%

Warehouse utilization confirms where the padding is coming from:

Warehouse	Size	Utilization	Billed	Idle padding
WH_ML	XLARGE	83%	$213.32	$36.00
WH_ETL	LARGE	76%	$101.93	$24.00
WH_ANALYTICS	MEDIUM	53%	$25.73	$12.00

WH_ANALYTICS sits at 53% utilization: nearly half of what it bills is auto-suspend padding, which is the actionable finding a naive dashboard cannot produce.

Architecture Decision Records

• ADR-0001: Snowflake-shaped schema on DuckDB, not raw Snowflake
• ADR-0002: Fair-share allocation of billed warehouse credits, not per-query summation

Portability to real Snowflake

The core reconciliation SQL uses only standard set operations, window functions, and interval arithmetic. Porting to Snowflake needs three narrow substitutions:

• epoch_ms(x) becomes DATE_PART('EPOCH_MILLISECOND', x).
• interval '1' hour becomes INTERVAL '1 HOUR'.
• date_trunc('hour', x) is identical.

The attribution_fair model reads QUERY_HISTORY and WAREHOUSE_METERING_HISTORY by their real Snowflake column names, so on a real account it is a schema-swap, not a rewrite.

Intentionally out of scope

• The cloud-services 10% adjustment. Snowflake only bills cloud-services credits above 10% of the daily compute total. The demo treats them as line items; the attribution logic is unaffected because both sides of the reconciliation see the same numbers.
• Storage credits. This is a compute-and-cloud-services attribution engine. Storage is billed differently and belongs in a separate model.
• Warehouse right-sizing. The utilization view is diagnostic, not prescriptive. A right-sizing recommender needs a queueing model that is a project of its own.
• Query rewrite suggestions. The inefficient-queries view flags high credits per GB scanned; recommending a fix requires the query text and a plan analysis.

Security and compliance

• No credentials, no network calls: the engine runs against a local DuckDB file.
• The dataset is fully synthetic and deterministic, so there is no query text, user PII, or account data in the repo or in CI logs.
• CI pins DuckDB to 1.1.3 for reproducible builds.

Failure modes

• Fair-share attribution does not tie to the bill. assert_ties_to_bill fails if the residual exceeds one cent. Verified during development by deleting rows from attribution_fair and confirming the build fails with exit 1.
• A query is dropped from attribution. Assertion #2 requires attribution_fair to have the same row count as query_history, catching a bad join or filter.
• A team disappears. Assertion #3 requires every team in users to appear in the team spend rollup.
• Naive matches fair-share unexpectedly. Assertion #4 requires naive to be strictly less than fair-share on datasets with any idle padding; a match signals either flat-idle test data or a broken reconciliation.
• Negative credits. Assertion #5 catches sign flips in the allocation math.
• Utilization out of range. Assertion #6 catches wall-clock miscalculations (billed less than active would produce >100% utilization).

Hardest problem solved

The naive dashboard printed a number the finance team could not reconcile against the invoice. Summing execution_time_hours * credits_per_hour from QUERY_HISTORY gave 108.08 credits. The Snowflake bill was 136.88. The 28.30-credit gap ($72 at $2.50/credit, 21% of the bill) had no team on it.

The cause is that Snowflake bills warehouse compute by wall clock. WAREHOUSE_METERING_HISTORY.CREDITS_USED_COMPUTE charges every second the warehouse is up, and after the last query finishes it stays up for the auto-suspend delay (60 seconds by default, 120 or more on ML warehouses). No query owns those seconds; the summed-per-query view therefore silently under-reports the bill and leaves the gap on nobody's cost center.

The fix was to reconcile per warehouse-hour and allocate that hour's billed compute credits to the queries that ran in it, proportional to their share of active milliseconds. Queries crossing hour boundaries split between hours. Idle padding lands on the queries that were already there, which naturally attributes it to the teams that kept the warehouse warm. The reconciliation now ties to the bill exactly (residual $0.00), the per-team hidden number is legible ($11 to $16 per team), and the utilization view shows WH_ANALYTICS at 53% (47% of that warehouse's bill is idle), which is the action item that only appears once the math ties out. Fixed in commit e58fd8c, guarded by assert_ties_to_bill which is verified to fail the build when the attribution drifts.

The lesson worth telling: a FinOps dashboard that does not reconcile to the bill is not a FinOps dashboard, it is a directional estimate. The reconciliation is the entire product.

Future work

• Carve idle padding into its own line and bill it to platform ops, as a stricter variant of ADR-0002.
• Add serverless task credits and Snowpipe credits (both live in separate ACCOUNT_USAGE views with the same reconciliation shape).
• Time-phase to a rolling daily and monthly report per team; add anomaly detection on the hidden-from-naive percentage as an early warning that autosuspend policy has drifted.
• Ship a right-sizing recommender that takes utilization and query concurrency and suggests a warehouse size change.
• Tie the offender list into a Slack notification for queries above a per-team threshold.