Gowtham Potureddi

Posted on Jul 7

Warehouse FinOps Compared: Snowflake Credits vs BigQuery Slots vs Databricks DBUs

#python #sql #interview #dataengineering

warehouse finops is the discipline that decides whether next quarter's data platform bill is a $200k line item or a $2M one — and in 2026 it is the single most-probed area of the senior data-engineering interview loop, because cloud data spend now crosses infrastructure spend at almost every Series-B-and-later company. Three warehouses dominate the market and each one prices compute differently: Snowflake bills snowflake credits as warehouse-hours multiplied by T-shirt size, BigQuery bills bigquery slots as slot-seconds under either an on-demand bytes-scanned model or reservation-backed Editions, and Databricks bills databricks dbu consumption as a normalised compute unit whose rate depends on cluster type, whether Photon is enabled, and whether the underlying VM is spot or on-demand. If you cannot recite those three unit definitions and their idle-cost levers cold, the FinOps conversation stops before it starts.

This guide walks through data warehouse cost end-to-end for senior data engineers who own the platform bill: the four axes every interviewer probes (unit, elasticity, commit, attribution), how credits scale linearly with warehouse size and how auto-suspend collapses idle spend, why slot-time is the correct billing atom on BigQuery and how reservation baselines interact with the autoscaler, how the Jobs / SQL / All-Purpose DBU-rate split and the Photon multiplier reshape the Databricks bill, and finally how to normalise all three units onto a single $/TB scanned bar so leadership can compare snowflake pricing to BigQuery Editions to a Databricks DBU commit apples-to-apples. Each of the five sections closes with a Solution-Tail interview answer — code, a step-by-step trace, an output table, then a concept-by-concept breakdown of why the pattern works — and the cheat sheet at the end ships the actual cost-attribution SQL you paste into a FinOps review.

When you want hands-on reps immediately after reading, drill the SQL practice library →, rehearse on the ETL practice library →, and sharpen the tuning axis with the optimization practice library →.

On this page

Why warehouse FinOps became a first-class engineering discipline
Snowflake credits — warehouses, sizes, idle time
BigQuery slots — on-demand vs Editions + reservations
Databricks DBUs — cluster type × compute
Cross-warehouse comparison + FinOps patterns
Cheat sheet — Warehouse FinOps recipes
Frequently asked questions
Practice on PipeCode

1. Why warehouse FinOps became a first-class engineering discipline

`warehouse finops` is now the largest infra line in a modern data org — three cost models, three mental models

The one-sentence invariant: credits are warehouse-hours × T-shirt size, slots are seconds of computational capacity billed by the second, and DBUs are a normalised compute unit whose $/hour rate depends on cluster type × Photon × infra — and those three unit definitions are the entry-price for any conversation with a CFO, a FinOps lead, or a senior interviewer. Every other question in a warehouse-cost review reduces to those three atoms plus the idle-cost lever attached to each one.

The four axes interviewers actually probe.

Unit. What exactly are you paying for? A Snowflake credit is a warehouse-hour at X-Small; every T-shirt size up doubles the credits-per-hour. A BigQuery slot is a virtual CPU with a fixed memory budget and is billed by the second on Editions or bundled inside a bytes-scanned charge on the deprecated-defaults on-demand model. A DBU is Databricks' unit of processed data — the DBU-per-hour rate is what changes with cluster type (Jobs, SQL, All-Purpose), Photon on/off, and the underlying VM SKU. Naming the unit precisely is the senior signal; hand-waving "compute" is the junior signal.
Elasticity. How fast does capacity respond to demand and how quickly does it drain to zero? Snowflake auto-suspend collapses idle spend to zero within seconds, but multi-cluster warehouses spin up additional clusters that also cost credits. BigQuery autoscaler ramps slot capacity between a baseline and a max but takes a small cold-start hit at each step. Databricks clusters (classic) take 2–5 minutes to acquire VMs, so idle spend can leak for the entire warm-pool window. The interviewer wants to hear the trade-off explicitly.
Commit. How do you convert on-demand spend into committed spend for a discount? Snowflake offers capacity commitments (annual or multi-year) with a per-credit rate discount that grows with commit size. BigQuery offers Editions + reservations (Standard / Enterprise / Enterprise Plus) with committed slots at a lower $/slot-hr. Databricks offers DBU commits and reserved-VM discounts. The senior answer names the commit model and the break-even utilisation.
Attribution. Who owes what? A warehouse bill is a single line item; the FinOps discipline is turning that back into a per-team, per-pipeline, per-workload chargeback. Snowflake exposes warehouse_metering_history and query_history. BigQuery exposes INFORMATION_SCHEMA.JOBS_BY_PROJECT with total_slot_ms and total_bytes_processed. Databricks exposes system.billing.usage. The interviewer expects you to name the source table before you name the report.

What interviewers actually probe.

"Walk me through how a Snowflake credit is defined." — required answer names the warehouse-hour + T-shirt-size linear scaling + serverless-credits carve-out.
"When would you pick BigQuery on-demand vs Editions + reservations?" — required answer names the ad-hoc / bytes-scanned unpredictability vs the flat-slot-hour predictability trade-off.
"What does a DBU actually cost you?" — required answer breaks the rate into cluster-type × Photon multiplier × infra layer.
"How would you attribute $1M of warehouse spend back to five teams?" — required answer names the per-warehouse metering tables and the query-tag or resource-group taxonomy.

Why cost has become an engineering discipline, not just a finance discipline.

The bill is bigger than the salary bill. For most Series-B+ data teams, cloud data spend has crossed the total data-engineering salary bill. That flips the ownership: the CFO now asks the head of data for a quarterly cost review, not the head of infra.
The unit economics are per-query. A single misconfigured dbt model can burn 50k credits (Snowflake), 500B slot-ms (BigQuery), or 200 DBUs (Databricks) in a night. Cost has become a code-review property — reviewers reject queries with expensive plan shapes the same way they reject queries with n+1 patterns.
Commit vs burst is an engineering call. The decision to buy 500 baseline slots on BigQuery, or commit to 1000 Snowflake credits per day, or reserve 200 DBUs of All-Purpose capacity, is not a finance decision. Finance signs the contract; engineering picks the number by modelling utilisation curves against workload characteristics.
Attribution is a data-modeling problem. Turning warehouse_metering_history × query_history × query_tag into a per-team chargeback report is a joins + windowing exercise. That is why warehouse FinOps has migrated from finance dashboards to engineering-owned data marts.

What interviewers listen for.

Do you name the unit in the first sentence when asked about pricing? — senior signal.
Do you distinguish on-demand vs reservation with a clear break-even utilisation number? — senior signal.
Do you push back on "just buy a bigger warehouse" with the idle-cost argument? — required answer.
Do you describe attribution as "joining the metering table to the query table on a tag column" rather than as a vague "cost report"? — required answer.

Worked example — sizing a Snowflake capacity commit

Detailed explanation. A senior FinOps engineer is asked to size a Snowflake capacity commitment. On-demand credits list at $2 per credit; a 500k-annual-credit commit drops the rate to $1.60 (a 20% discount). Last year's usage was 620k credits with a linear-ish growth curve. The interviewer wants the sizing math and the risk profile.

The break-even. Any commit larger than actual usage costs money you did not use. Any commit smaller than actual usage misses the discount on the extra usage.
The straddle. Commit for the high-confidence floor of usage, not the peak. The variance above the commit stays on-demand.
The escalator. Multi-year commits offer larger discounts but bind the team to a growth curve that may not materialise.

Question. Given a projected usage of 720k credits (620k × 1.16 growth), an on-demand rate of $2.00, and commit tiers of 500k @ $1.60 / 700k @ $1.55 / 1M @ $1.45, which commit tier minimises total spend? Show the math for each tier.

Input.

Tier	Committed credits	Rate ($/credit)	On-demand rate	Projected usage	Break-even usage
No commit	0	—	2.00	720k	—
500k	500k	1.60	2.00	720k	400k
700k	700k	1.55	2.00	720k	542k
1M	1M	1.45	2.00	720k	725k

Code.

-- Snowflake — model the total spend for each commit tier
WITH scenarios(tier, commit_credits, commit_rate, ondemand_rate, projected) AS (
  VALUES
    ('no_commit', 0,      2.00, 2.00, 720000),
    ('500k',      500000, 1.60, 2.00, 720000),
    ('700k',      700000, 1.55, 2.00, 720000),
    ('1M',        1000000,1.45, 2.00, 720000)
),
spend AS (
  SELECT tier,
         commit_credits,
         commit_rate,
         ondemand_rate,
         projected,
         -- committed portion
         LEAST(projected, commit_credits) * commit_rate                  AS committed_spend,
         -- unused commit — you pay for it even if unused
         GREATEST(commit_credits - projected, 0) * commit_rate           AS unused_spend,
         -- on-demand overflow above the commit
         GREATEST(projected - commit_credits, 0) * ondemand_rate         AS overflow_spend
  FROM   scenarios
)
SELECT tier,
       ROUND(committed_spend + unused_spend + overflow_spend, 0) AS total_spend,
       ROUND(committed_spend, 0)                                  AS committed_spend,
       ROUND(unused_spend, 0)                                     AS unused_spend,
       ROUND(overflow_spend, 0)                                   AS overflow_spend
FROM   spend
ORDER  BY total_spend;

Step-by-step explanation.

No commit — 720k credits × $2.00 = $1,440,000. The baseline against which every commit is scored.
500k tier — the committed 500k × $1.60 = $800,000. The overflow (720k − 500k = 220k) at on-demand $2.00 = $440,000. Total = $1,240,000. Savings vs no commit = $200k.
700k tier — the committed 700k × $1.55 = $1,085,000. The overflow (20k × $2.00) = $40,000. Total = $1,125,000. Savings vs no commit = $315k. This is the winner.
1M tier — the committed 1M × $1.45 = $1,450,000. There is no overflow (usage is below commit), but there is unused-commit spend: (1M − 720k) × $1.45 = $406,000 of credits paid for and thrown away. Total = $1,450,000. Savings vs no commit = negative $10k. Over-committing costs money.
The 700k tier straddles the projected usage — the commit covers roughly 97% of demand, with 20k of overflow paying the on-demand premium. That is the textbook shape of a good commit.

Output.

Tier	Committed	Unused	Overflow	Total
No commit	0	0	1,440,000	1,440,000
500k	800,000	0	440,000	1,240,000
700k	1,085,000	0	40,000	1,125,000
1M	1,044,000	406,000	0	1,450,000

Rule of thumb. Size the commit to the high-confidence floor of projected usage, not the peak. Overflow above the commit pays the on-demand premium, which is cheap insurance; unused commit below the projected usage is a straight loss.

Worked example — BigQuery on-demand vs Editions break-even

Detailed explanation. A team runs 200 TB of BigQuery on-demand scans per month at $6.25 per TB — a straight $1,250 bill. A BigQuery Standard Edition reservation of 500 slots at $0.04 per slot-hour would cost $14,400 per month for 24×7 slots. On paper the reservation is 11× more expensive. But the on-demand model bills bytes even when the query is cheap on slot-time, and the reservation model runs unlimited queries within the slot budget. The break-even is on the shape of the workload, not the raw totals.

On-demand. Bytes-scanned × $6.25/TB, with a 300-slot free autoscale but no cap on the bytes charge.
Editions. Flat $/slot-hr regardless of bytes scanned. Autoscales up to a max slot count you set.
The tipping point. When queries scan many bytes but use few slot-seconds, on-demand wins. When queries scan few bytes but use many slot-seconds (compute-heavy, e.g. complex joins), Editions wins.

Question. Model the same 200 TB workload under both pricing models with two different query shapes: (a) heavy scan / light compute; (b) light scan / heavy compute. Show which model wins in each case.

Input.

Case	Bytes scanned (TB)	Slot-hours consumed	On-demand cost	Editions cost (500 slots × 730 hr)
(a) heavy scan / light compute	200	100	200 × $6.25 = $1,250	$14,400
(b) light scan / heavy compute	20	2000	20 × $6.25 = $125	$14,400
(c) balanced monster	500	250,000	500 × $6.25 = $3,125	$14,400

Code.

-- BigQuery INFORMATION_SCHEMA — measure workload shape before picking a model
SELECT
  DATE(creation_time)                                 AS run_date,
  SUM(total_bytes_processed) / POW(1024, 4)           AS tb_scanned,
  SUM(total_slot_ms) / 1000.0 / 3600.0                AS slot_hours,
  -- Cost under each model
  ROUND(SUM(total_bytes_processed) / POW(1024, 4) * 6.25, 2)             AS on_demand_cost_usd,
  ROUND(500 * 730 * 0.04, 2)                                             AS edition_cost_usd,
  -- Slot-hours per TB scanned — the shape metric
  ROUND(SUM(total_slot_ms) / 1000.0 / 3600.0
        / NULLIF(SUM(total_bytes_processed) / POW(1024, 4), 0), 2)       AS slot_hr_per_tb
FROM
  `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE
  creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
  AND job_type = 'QUERY'
GROUP BY run_date
ORDER BY run_date;

Step-by-step explanation.

Case (a) — 200 TB scanned with only 100 slot-hours of compute. On-demand costs $1,250; the Editions reservation would cost $14,400 to serve the same workload. On-demand wins by 11.5×. This is a scan-heavy workload (SELECT * FROM big_table WHERE date = ... with a partition-prune-friendly filter).
Case (b) — 20 TB scanned with 2000 slot-hours of compute. On-demand costs $125 (bytes are cheap); the Editions reservation still costs $14,400. On-demand wins massively. Notice this is a compute-heavy workload but the on-demand model still wins because BigQuery gives you the 300 free autoscale slots on top of the bytes-scanned charge.
Case (c) — the "balanced monster" — 500 TB scanned and 250k slot-hours of compute. On-demand costs $3,125 for bytes; but 500 slots × 730 hr = 365k slot-hours available under the reservation, so the reservation would comfortably serve this. The reservation costs $14,400. Even here on-demand wins.
The lesson: on-demand almost always wins until your slot-hour demand exceeds the on-demand's 300-slot autoscale budget for a sustained fraction of the month. That inflection point is roughly 200k slot-hours per month; below it, on-demand is cheaper and simpler; above it, Editions pays for itself.
The slot_hr_per_tb metric surfaces the shape. Below ~10 slot-hr/TB the workload is scan-heavy — stay on-demand. Above ~500 slot-hr/TB the workload is compute-heavy — move to Editions once your absolute slot-hour demand is high.

Output.

Case	Bytes ($)	Slot ($)	Cheaper model	Multiplier
(a) heavy scan / light compute	1,250	14,400	on-demand	11.5×
(b) light scan / heavy compute	125	14,400	on-demand	115×
(c) balanced monster	3,125	14,400	on-demand	4.6×

Rule of thumb. Do not migrate to BigQuery Editions until your sustained slot-hour demand exceeds the on-demand free-tier autoscale budget for a majority of hours. The total_slot_ms / total_bytes_processed ratio in JOBS_BY_PROJECT is your workload-shape thermometer.

Worked example — Databricks DBU rate math for a mixed cluster

Detailed explanation. A team runs three cluster types on Databricks: a Jobs cluster for scheduled ETL, an SQL warehouse for BI, and an All-Purpose cluster for ad-hoc notebooks. Each has a different DBU-per-hour rate. Photon roughly doubles the DBU rate but roughly halves the wall-clock. Spot instances save 60% on the underlying VM cost. The FinOps question is which combination minimises spend for a given workload.

Jobs cluster. DBU rate ~0.15 DBU/DBU-hr on standard, ~0.30 with Photon.
SQL warehouse. DBU rate ~0.22 DBU/DBU-hr on standard, ~0.55 with Photon (serverless).
All-Purpose cluster. DBU rate ~0.55 DBU/DBU-hr on standard, ~0.79 with Photon.
Photon multiplier. ~2× DBU rate but ~2× faster wall clock, so the effective DBU spend for the same work is roughly flat — the win is in wall clock, not in cost per se.

Question. For a nightly ETL that takes 4 hours on a Jobs cluster without Photon and 2 hours with Photon, on identical underlying VMs at $10/hr on-demand, calculate the total spend under both configurations at $0.55 per DBU. Include a spot-VM scenario.

Input.

Config	Wall clock (hr)	DBU rate	Total DBUs	$/DBU	VM $/hr	DBU cost	VM cost	Total
Jobs + standard, on-demand VM	4	0.15	0.6	0.55	10	0.33	40	40.33
Jobs + Photon, on-demand VM	2	0.30	0.6	0.55	10	0.33	20	20.33
Jobs + Photon, spot VM	2	0.30	0.6	0.55	4	0.33	8	8.33

Code.

# Databricks — quick DBU cost calculator for a nightly ETL
def etl_cost(wall_hr: float, dbu_rate: float, dbu_price: float, vm_hr_price: float) -> dict:
    """Return DBU cost, VM cost, and total cost for a nightly ETL run."""
    dbu_total = wall_hr * dbu_rate
    dbu_cost  = dbu_total * dbu_price
    vm_cost   = wall_hr * vm_hr_price
    return {
        "wall_hr":   wall_hr,
        "dbu_rate":  dbu_rate,
        "dbu_total": dbu_total,
        "dbu_cost":  round(dbu_cost, 2),
        "vm_cost":   round(vm_cost, 2),
        "total":     round(dbu_cost + vm_cost, 2),
    }

# Three configurations
print(etl_cost(4, 0.15, 0.55, 10))    # Jobs + standard, on-demand VM
print(etl_cost(2, 0.30, 0.55, 10))    # Jobs + Photon, on-demand VM
print(etl_cost(2, 0.30, 0.55, 4))     # Jobs + Photon, spot VM (60% VM discount)

Step-by-step explanation.

Baseline — Jobs cluster without Photon runs 4 hours. DBU spend is 4 × 0.15 × $0.55 = $0.33; VM spend is 4 × $10 = $40. Total = $40.33 per night. Notice DBU is a tiny fraction of the bill — the VM dominates.
Photon — halves the wall clock (2 hours) but doubles the DBU rate. Total DBUs are unchanged (2 × 0.30 = 0.6), so DBU cost stays at $0.33. VM cost halves to $20. Total = $20.33. Photon paid for itself purely via wall-clock reduction.
Spot instances — same 2-hour Photon run but the underlying VM cost drops from $10/hr to $4/hr (a 60% spot discount). VM cost drops to $8. Total = $8.33 — 5× cheaper than the baseline.
The FinOps lesson: Photon's DBU rate looks scary in isolation but the real spend is on the VM layer. Photon buys you wall-clock reduction, which reduces VM hours proportionally. Spot instances then compound the win.
The caveat: spot instances can be revoked mid-job. For a critical nightly ETL you want fallback to on-demand, or a job-restart-safe pipeline (Delta Lake with checkpoints handles this well). The 60% saving is worth it only if the pipeline is restart-tolerant.

Output.

Config	Wall	DBU $	VM $	Total	Savings
Jobs + standard, on-demand	4 hr	0.33	40.00	40.33	—
Jobs + Photon, on-demand	2 hr	0.33	20.00	20.33	50%
Jobs + Photon, spot	2 hr	0.33	8.00	8.33	79%

Rule of thumb. On Databricks, always calculate DBU + VM together — the DBU rate is a small slice of the total. Photon buys wall clock; spot buys VM discount; the two compound. Never look at DBU spend alone.

Senior interview question on warehouse cost models

A senior interviewer often opens with: "Your CFO asks you which warehouse is cheapest — Snowflake, BigQuery, or Databricks. Walk me through how you'd frame the answer, what the three cost units actually mean, and why the honest answer is always 'it depends on the workload'."

Solution Using unit-first framing + workload-shape analysis

-- Step 1 — normalise each warehouse's monthly spend into $/TB scanned and $/hour
-- so you can compare apples-to-apples. Requires: per-warehouse metering + query metadata.

-- Snowflake
WITH sf AS (
  SELECT
    DATE_TRUNC('month', start_time)                        AS month,
    SUM(credits_used) * 2.00                                AS spend_usd,       -- $2/credit list
    SUM(bytes_scanned) / POW(1024, 4)                       AS tb_scanned,
    SUM(execution_time) / 3600000.0                         AS query_hours
  FROM  snowflake.account_usage.query_history
  WHERE start_time >= DATEADD(MONTH, -3, CURRENT_DATE)
  GROUP BY month
),
-- BigQuery (on-demand)
bq AS (
  SELECT
    DATE_TRUNC(DATE(creation_time), MONTH)                  AS month,
    SUM(total_bytes_billed) / POW(1024, 4) * 6.25           AS spend_usd,
    SUM(total_bytes_processed) / POW(1024, 4)               AS tb_scanned,
    SUM(total_slot_ms) / 1000.0 / 3600.0                    AS query_hours
  FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
  WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 90 DAY)
    AND job_type = 'QUERY'
  GROUP BY month
),
-- Databricks
dbx AS (
  SELECT
    DATE_TRUNC('month', usage_start_time)                   AS month,
    SUM(usage_quantity) * 0.55                              AS spend_usd,       -- $0.55/DBU list
    NULL::DOUBLE                                            AS tb_scanned,      -- not directly available
    SUM(usage_quantity) / 0.30                              AS query_hours      -- back-solve for Photon Jobs
  FROM system.billing.usage
  WHERE usage_start_time >= DATEADD(MONTH, -3, CURRENT_DATE)
    AND sku_name LIKE '%COMPUTE%'
  GROUP BY month
)
-- Unified view
SELECT 'snowflake' AS engine, month, spend_usd, tb_scanned, query_hours,
       spend_usd / NULLIF(tb_scanned, 0)   AS usd_per_tb,
       spend_usd / NULLIF(query_hours, 0)  AS usd_per_hour
FROM sf
UNION ALL
SELECT 'bigquery',           month, spend_usd, tb_scanned, query_hours,
       spend_usd / NULLIF(tb_scanned, 0),   spend_usd / NULLIF(query_hours, 0)
FROM bq
UNION ALL
SELECT 'databricks',          month, spend_usd, tb_scanned, query_hours,
       spend_usd / NULLIF(tb_scanned, 0),   spend_usd / NULLIF(query_hours, 0)
FROM dbx
ORDER BY month, engine;

Step-by-step trace.

Step	What you compute	Why it matters
Snowflake spend	`credits × $2.00`	Credits are the atomic billing unit
BigQuery spend (on-demand)	`bytes_billed × $6.25/TB`	Bytes-scanned is the atomic on-demand unit
Databricks spend	`DBU × $0.55`	DBUs are the atomic Databricks unit
Normalise	`$ / TB scanned` and `$ / query-hour`	Puts three units on one axis
Compare	pick engine per workload shape	Answer is always workload-conditional

After the query runs, you get a per-engine, per-month view that lets you compare cost per unit of work across the three warehouses. The typical outcome: Snowflake wins for interactive BI, BigQuery wins for ad-hoc scans, Databricks wins for compute-heavy ML/ETL. The CFO gets a chart, not an anecdote.

Output:

engine	month	spend_usd	tb_scanned	query_hours	usd_per_tb	usd_per_hour
snowflake	2026-06	120,000	850	4,200	141.18	28.57
bigquery	2026-06	84,000	13,400	6,100	6.27	13.77
databricks	2026-06	96,000	(n/a)	5,800	(n/a)	16.55

Why this works — concept by concept:

Unit-first framing — each engine has an atomic billing unit (credit, slot-second, DBU). Every FinOps analysis starts by grounding the conversation in the unit; that gets both engineering and finance speaking the same language.
Normalisation — dividing spend by TB scanned and query-hours gives two comparable axes. Neither is perfect (BigQuery's slot-hour cheats Snowflake's warehouse-hour because a slot is much smaller than a warehouse), but they surface the shape of the workload.
Workload shape — the honest answer is "it depends" because the three engines are optimised for different workload shapes. Scan-heavy loves BigQuery on-demand; interactive-BI loves Snowflake's warehouse elasticity; compute-heavy loves Databricks Photon + Delta.
Metering tables — every answer must name the source of truth: snowflake.account_usage.query_history, INFORMATION_SCHEMA.JOBS_BY_PROJECT, system.billing.usage. Interviewers listen for these names.
Cost — the analysis itself is a handful of aggregate queries running against system tables. The queries are cheap; the decision they enable — commit tier, engine choice, workload migration — moves six or seven figures per year.

SQL
Topic — sql
SQL cost-attribution and metering-table problems

Practice →

Optimization Topic — optimization Optimization problems on warehouse cost tuning

Practice →

2. Snowflake credits — warehouses, sizes, idle time

`snowflake credits` scale linearly with T-shirt size and evaporate the moment auto-suspend fires — the two biggest levers of the Snowflake bill

The mental model in one line: a Snowflake credit is one X-Small warehouse-hour, every T-shirt size doubles the credits-per-hour (X-Small = 1, Small = 2, Medium = 4, Large = 8, X-Large = 16, 2X-Large = 32), and the bill is credits × per-second billing × on-demand or committed $/credit — so the two biggest optimisation levers are picking the right warehouse size and setting an aggressive auto-suspend. Everything else — multi-cluster warehouses, serverless-credit pricing for Snowpipe / tasks / search-optimization — is a variation on that theme.

The four axes for Snowflake FinOps.

Unit. A credit is Snowflake's atomic billing unit. One credit = one X-Small warehouse running for one hour. Size doubling doubles the credit rate; billing is per-second with a 60-second minimum on start.
Elasticity. Auto-suspend + auto-resume. When idle for AUTO_SUSPEND seconds, the warehouse suspends and stops burning credits. When a query arrives, it auto-resumes (2–5 seconds cold-start on the standard cache; near-instant on a warm cache). The default AUTO_SUSPEND of 600 seconds is far too generous — most teams set it to 60 seconds.
Commit. Capacity commitments (annual or multi-year) drop the per-credit rate by 10–40% depending on commit size. Overflow above the commit stays at on-demand list. Multi-year commitments compound the discount.
Attribution. WAREHOUSE_METERING_HISTORY (per-warehouse credits) joined to QUERY_HISTORY (per-query, including a QUERY_TAG) gives you a per-team, per-pipeline chargeback. The query tag is the pivotal column — enforce a tag convention or you cannot attribute anything.

T-shirt sizing and the linear credit ladder.

X-Small = 1 credit / hour — 8 cores, small memory, one node. The default for interactive BI and small ETL.
Small = 2 credits / hour — 2 nodes. Best for medium-sized ELT jobs where memory matters.
Medium = 4 credits / hour — 4 nodes. Common ETL default.
Large = 8 credits / hour — 8 nodes. Where you go for large joins and complex plans.
X-Large through 6X-Large — each step doubles the previous. 6X-Large = 512 credits / hour, roughly $1,000/hour on-demand.

The auto-suspend lever.

The default. AUTO_SUSPEND = 600 (10 minutes). Every warehouse that fires a query holds the cluster for the full 10 minutes after the query completes.
The recommended. AUTO_SUSPEND = 60 (60 seconds). Cuts idle spend by ~90% on burst workloads. The trade-off is a slightly higher cold-start rate for interactive queries — often invisible because Snowflake keeps the query-result cache warm.
The extreme. AUTO_SUSPEND = 30 (30 seconds). Suitable for warehouses that see very bursty traffic; combines with a large multi-cluster minimum to keep hot paths responsive.
The wrong knob. Turning AUTO_SUSPEND off entirely. The warehouse runs 24×7 whether or not queries arrive. This is the #1 warehouse-spend leak in the industry.

Multi-cluster warehouses.

Concurrency. A single-cluster warehouse can run 8 queries in parallel by default (MAX_CONCURRENCY_LEVEL = 8). Beyond that, queries queue.
Multi-cluster. Spins up additional clusters when queue depth exceeds a threshold, up to MAX_CLUSTER_COUNT. Each additional cluster costs the same credits per hour as the first.
Scaling policy. STANDARD scales fast up and slow down; ECONOMY scales slower up but faster down. Economy saves credits at the cost of some queue latency.
The trap. Setting MIN_CLUSTER_COUNT = 5 on a burst workload means 5 clusters run 24×7 — 5× the baseline cost. Almost always the wrong default.

Serverless credits.

Snowpipe. ~1.25 credits per 1000 files loaded; scales with data volume, not warehouse size.
Tasks (serverless). Priced per compute-second consumed, not per warehouse-hour.
Search Optimization Service. Priced by data volume; adds a background cost proportional to indexed data.
Materialised views. Priced per refresh; each MV background refresh consumes credits without you seeing a warehouse spin up.

Common interview probes on Snowflake credits.

"How is a credit defined?" — X-Small warehouse for one hour.
"What's the biggest lever to cut Snowflake spend?" — auto-suspend + right-sizing.
"When would you use a multi-cluster warehouse?" — bursty concurrency > 8, where queue depth matters more than cost.
"How do you attribute Snowflake spend to teams?" — QUERY_TAG + WAREHOUSE_METERING_HISTORY + QUERY_HISTORY.

Worked example — cost of one 4-hour ELT on a Medium warehouse

Detailed explanation. A textbook Snowflake FinOps question. A nightly dbt job runs on a Medium warehouse for 4 hours. Between two dbt models the warehouse sits idle for 20 minutes (waiting on a slow model to finish upstream). What does the run cost with the default AUTO_SUSPEND = 600 versus AUTO_SUSPEND = 60, and how does that compare to running it on a Large warehouse in half the time?

Setup. Medium = 4 credits/hour. Warehouse spins up, runs 4 hours of queries with a 20-minute idle gap in the middle.
Default. AUTO_SUSPEND = 600 — the warehouse never suspends during the 20-minute gap.
Aggressive. AUTO_SUSPEND = 60 — the warehouse suspends after 60 seconds of idle, saving 19 minutes.
Bigger warehouse. Large = 8 credits/hour but the job finishes in 2 hours.

Question. Compute the credit cost for each of the three scenarios. Which minimises spend? Which minimises wall clock?

Input.

Scenario	Size	Rate (cr/hr)	Wall clock (hr)	Idle gap	Effective billable time	Credits
A. Medium, AUTO_SUSPEND=600	Medium	4	4.0	20 min (kept warm)	4.0 hr	16.0
B. Medium, AUTO_SUSPEND=60	Medium	4	4.0	20 min (suspend after 1 min)	3.68 hr	14.72
C. Large, AUTO_SUSPEND=60	Large	8	2.0	10 min (suspend after 1 min)	1.85 hr	14.80

Code.

-- Snowflake — model the credit spend for each scenario
WITH scenarios(name, size_rate, wall_hr, idle_min, autosuspend_min) AS (
  VALUES
    ('A. Medium, AUTO_SUSPEND=600', 4, 4.0, 20, 10),   -- suspend threshold beyond gap → no suspend
    ('B. Medium, AUTO_SUSPEND=60',  4, 4.0, 20, 1),    -- suspends after 1 min of idle
    ('C. Large,  AUTO_SUSPEND=60',  8, 2.0, 10, 1)     -- suspends after 1 min of idle
),
sim AS (
  SELECT name,
         size_rate,
         wall_hr,
         idle_min,
         autosuspend_min,
         -- how much of the idle gap is actually billed
         LEAST(idle_min, autosuspend_min) / 60.0 AS billed_idle_hr
  FROM   scenarios
)
SELECT name,
       size_rate,
       wall_hr,
       idle_min,
       billed_idle_hr,
       -- credits = size × (wall - idle_gap + billed_idle_portion)
       ROUND(size_rate * (wall_hr - idle_min/60.0 + billed_idle_hr), 2) AS credits,
       ROUND(size_rate * (wall_hr - idle_min/60.0 + billed_idle_hr) * 2.00, 2) AS cost_usd
FROM   sim
ORDER  BY credits;

-- Apply the recommendation
ALTER WAREHOUSE etl_wh SET
  WAREHOUSE_SIZE           = 'MEDIUM'
  AUTO_SUSPEND             = 60
  AUTO_RESUME              = TRUE
  STATEMENT_QUEUED_TIMEOUT_IN_SECONDS = 60
  QUERY_TAG                = 'dbt_nightly';

Step-by-step explanation.

Scenario A — Medium × 4 hours = 16 credits. The 20-minute idle gap in the middle is fully billed because AUTO_SUSPEND = 600 means the warehouse never suspends during a 20-minute gap. At $2/credit list, this run costs $32.
Scenario B — Same wall clock, but AUTO_SUSPEND = 60 suspends the warehouse 60 seconds into the gap. You bill for 1 minute of the 20-minute gap instead of all 20 minutes. Effective billable = 4 hr − 20 min + 1 min = 3.68 hr × 4 credits = 14.72 credits. Saving vs A = 1.28 credits, ~8% on this run.
Scenario C — Large × 2 hours halves the wall clock but doubles the credit rate. 10-minute idle gap suspends after 1 minute. Effective billable = 2 hr − 10 min + 1 min = 1.85 hr × 8 credits = 14.80 credits. Roughly the same total as B but the wall clock is half.
The key insight: bigger warehouses cost the same total credits for the same total work in Snowflake's linear-scaling model — the credit rate doubles as the size doubles, but the work finishes proportionally faster. What changes is the wall clock (and the idle-cost surface).
The wrong intuition: "Large is more expensive than Medium." False on the total credits; true on the credit-rate; irrelevant on the bill for a compute-bound job.

Output.

Scenario	Credits	$ at $2/cr	Wall clock	Notes
A. Medium, AS=600	16.00	32.00	4 hr	Idle gap fully billed
B. Medium, AS=60	14.72	29.44	4 hr	Suspend saves 19 min
C. Large, AS=60	14.80	29.60	2 hr	Same cost, half wall

Rule of thumb. For compute-bound Snowflake work, the credit total is roughly invariant to warehouse size — pick the size that gives you the wall clock you want. The AUTO_SUSPEND knob dominates the idle-cost surface; always set it to 60 seconds unless a specific workload needs longer.

Worked example — auto-suspend audit query

Detailed explanation. A senior FinOps engineer inherits a Snowflake account with 40 warehouses. The immediate audit: which warehouses have AUTO_SUSPEND set high (or off) and are burning idle credits? The WAREHOUSES metadata table plus WAREHOUSE_METERING_HISTORY gives you the answer in one query.

The audit. Any warehouse with AUTO_SUSPEND > 300 (5 minutes) is a candidate. Any warehouse with AUTO_SUSPEND disabled entirely is a red flag.
The impact. Multiply the idle credit rate by the number of idle hours per day to size the leak.
The fix. ALTER WAREHOUSE ... SET AUTO_SUSPEND = 60 — non-disruptive, applies to the next auto-suspend cycle.

Question. Build the SQL that surfaces the top 10 warehouses ranked by potential idle-credit savings, assuming AUTO_SUSPEND is lowered from its current value to 60 seconds.

Input.

Assumption	Value
Target AUTO_SUSPEND	60 s
Typical warehouses in audit	40
Metering window	last 30 days
Idle threshold to flag	any warehouse with < 50% utilisation

Code.

-- Snowflake — auto-suspend audit
WITH wh AS (
  SELECT
    warehouse_name,
    auto_suspend,        -- seconds; NULL = disabled
    size,
    started_clusters,
    running_clusters
  FROM  snowflake.account_usage.warehouses
),
metering AS (
  SELECT
    warehouse_name,
    SUM(credits_used)                    AS credits_used,
    COUNT(DISTINCT DATE(start_time))     AS days_active,
    SUM(DATEDIFF('second', start_time, end_time)) / 3600.0  AS billed_hours
  FROM  snowflake.account_usage.warehouse_metering_history
  WHERE start_time >= DATEADD('day', -30, CURRENT_TIMESTAMP())
  GROUP BY warehouse_name
),
query_activity AS (
  SELECT
    warehouse_name,
    SUM(execution_time) / 1000.0 / 3600.0 AS query_hours
  FROM  snowflake.account_usage.query_history
  WHERE start_time >= DATEADD('day', -30, CURRENT_TIMESTAMP())
  GROUP BY warehouse_name
),
audit AS (
  SELECT
    m.warehouse_name,
    w.auto_suspend,
    w.size,
    m.credits_used,
    m.billed_hours,
    q.query_hours,
    m.billed_hours - q.query_hours                                     AS idle_hours,
    -- estimate savings assuming autosuspend drops from current to 60 s
    -- (rough model: idle savings scale with (auto_suspend - 60) / auto_suspend)
    CASE
      WHEN w.auto_suspend IS NULL THEN (m.billed_hours - q.query_hours)
      WHEN w.auto_suspend > 60    THEN (m.billed_hours - q.query_hours)
                                       * (w.auto_suspend - 60.0) / w.auto_suspend
      ELSE 0
    END AS est_idle_hours_saved,
    m.credits_used / NULLIF(m.billed_hours, 0) AS credit_rate_per_hour
  FROM  wh w
  JOIN  metering m       USING (warehouse_name)
  JOIN  query_activity q USING (warehouse_name)
)
SELECT warehouse_name,
       size,
       auto_suspend                                    AS current_auto_suspend_s,
       credits_used                                    AS credits_30d,
       ROUND(idle_hours, 1)                            AS idle_hours_30d,
       ROUND(est_idle_hours_saved * credit_rate_per_hour, 1) AS est_credits_saved_30d,
       ROUND(est_idle_hours_saved * credit_rate_per_hour * 2.00, 0) AS est_dollars_saved_30d
FROM   audit
ORDER  BY est_credits_saved_30d DESC
LIMIT  10;

Step-by-step explanation.

Pull the current AUTO_SUSPEND per warehouse from the WAREHOUSES metadata view — this is the "before" value we compare against.
Sum the credits_used and billed_hours from WAREHOUSE_METERING_HISTORY over the last 30 days. The billed_hours is the total wall-clock the warehouse was in a running state; the credits_used is the credit total.
Sum execution_time from QUERY_HISTORY over the same window — this is the time the warehouse was actually serving queries. The delta between billed_hours and query_hours is idle-time — you paid for the warehouse to sit warm.
Estimate the savings from dropping AUTO_SUSPEND from its current value to 60 seconds. The linear approximation (current − 60) / current overstates the savings slightly (because not every idle gap is longer than the current threshold) but gives a defensible upper bound.
Rank the top 10 by savings potential — surface the worst offenders. In practice, one or two warehouses account for 60–80% of the leak; fix those first.

Output.

warehouse	size	current AS	credits 30d	idle hr 30d	est cr saved	est $ saved
analytics_bi	Large	600	8,400	320	2,300	4,600
dbt_prod	Medium	300	4,200	180	640	1,280
adhoc_ds	X-Large	900	3,900	190	2,850	5,700
...	...	...	...	...	...	...

Rule of thumb. Run this audit monthly. Two or three warehouses will always dominate the savings potential; fix those and iterate. The audit query is the same every month; the winners rotate as workloads change.

Worked example — query-tag attribution for chargeback

Detailed explanation. A FinOps engineer needs to attribute Snowflake spend across five teams for the quarterly chargeback. Without a QUERY_TAG convention, this is impossible — you cannot tell which team owned a query after the fact. With a tag convention (e.g. team=marketing,pipeline=email_metrics,env=prod), attribution is a straightforward join between QUERY_HISTORY and WAREHOUSE_METERING_HISTORY.

The tag. Set at the query level via ALTER SESSION SET QUERY_TAG = 'team=X,pipeline=Y,env=prod', or via dbt project-level query_tag: 'dbt|team=X'.
The join. Query-level credit spend is not directly available; we approximate by prorating warehouse-hour credits by the query's fraction of execution time on that warehouse.
The report. GROUP BY the parsed tag fields; SUM the prorated credits.

Question. Build the SQL that produces a per-team, per-month chargeback report from QUERY_HISTORY and WAREHOUSE_METERING_HISTORY, using the team= prefix in the query tag.

Input.

Convention	Value
Tag format	`team=X,pipeline=Y,env=prod`
Tag parser	regex on `team=([^,]+)`
Attribution model	prorate by execution_time / total_warehouse_time
Report grain	(team, month)

Code.

-- Snowflake — per-team chargeback report
WITH qh AS (
  SELECT
    query_id,
    warehouse_name,
    query_tag,
    DATE_TRUNC('month', start_time)                       AS month,
    execution_time / 1000.0                                AS exec_seconds,
    REGEXP_SUBSTR(query_tag, 'team=([^,]+)', 1, 1, 'e', 1) AS team
  FROM   snowflake.account_usage.query_history
  WHERE  start_time >= DATE_TRUNC('month', DATEADD('month', -1, CURRENT_DATE))
    AND  start_time <  DATE_TRUNC('month', CURRENT_DATE)
    AND  execution_time IS NOT NULL
),
wh_totals AS (
  SELECT
    warehouse_name,
    DATE_TRUNC('month', start_time)                        AS month,
    SUM(credits_used)                                       AS credits_used,
    SUM(DATEDIFF('second', start_time, end_time))           AS warehouse_seconds
  FROM   snowflake.account_usage.warehouse_metering_history
  WHERE  start_time >= DATE_TRUNC('month', DATEADD('month', -1, CURRENT_DATE))
    AND  start_time <  DATE_TRUNC('month', CURRENT_DATE)
  GROUP  BY warehouse_name, month
),
prorated AS (
  SELECT
    qh.team,
    qh.month,
    qh.warehouse_name,
    -- credits per query = warehouse credits × (this query's seconds / total warehouse seconds)
    SUM(qh.exec_seconds) / NULLIF(wh.warehouse_seconds, 0) * wh.credits_used AS team_credits
  FROM   qh
  JOIN   wh_totals wh USING (warehouse_name, month)
  WHERE  qh.team IS NOT NULL
  GROUP  BY qh.team, qh.month, qh.warehouse_name, wh.warehouse_seconds, wh.credits_used
)
SELECT
  team,
  month,
  ROUND(SUM(team_credits), 1)                     AS credits,
  ROUND(SUM(team_credits) * 2.00, 0)              AS spend_usd_list,
  ROUND(SUM(team_credits) * 1.60, 0)              AS spend_usd_committed
FROM   prorated
GROUP  BY team, month
ORDER  BY month, spend_usd_list DESC;

Step-by-step explanation.

Extract the team from QUERY_TAG using a regex. The convention team=marketing,pipeline=... maps to a single team per query; queries without a tag are excluded (or bucketed as untagged, which you should also report).
Roll up per-warehouse credits and per-warehouse seconds per month from the metering history. This gives you the denominator — total warehouse-seconds you are distributing across teams.
Prorate: for each query, its share of the warehouse's monthly credits is (query_seconds / warehouse_seconds) × warehouse_credits. Sum across all a team's queries on that warehouse.
Aggregate across warehouses to produce a per-team, per-month credit total. Apply both the list rate ($2/credit) and the committed rate (say $1.60/credit) so the report shows the effective chargeback.
The untagged bucket is your accountability lever. Enforce a session-level QUERY_TAG at the driver layer and treat any untagged spend as a bug; over 6 months, the untagged fraction should drop from 30–40% to under 5%.

Output.

team	month	credits	list $	committed $
marketing	2026-06	4,200	8,400	6,720
growth	2026-06	3,800	7,600	6,080
finance	2026-06	2,500	5,000	4,000
platform	2026-06	1,900	3,800	3,040
ml	2026-06	1,600	3,200	2,560
untagged	2026-06	900	1,800	1,440

Rule of thumb. Attribution without QUERY_TAG is impossible. Ship the tag convention as code — a driver-side session hook that sets the tag automatically from the process's environment — not as a policy PDF nobody reads.

Senior interview question on Snowflake FinOps

A senior interviewer might ask: "You inherit a Snowflake account with $2M/year of on-demand spend, no query tags, no auto-suspend policy, and a single warehouse per environment. Walk me through the first 30 days — what do you measure, what do you change, and how do you prove the savings to the CFO?"

Solution Using a 30-day Snowflake FinOps hardening plan

-- 30-day plan — measurable checkpoints at each stage
-- Week 1 — instrumentation
--   Enable ACCOUNT_USAGE views; verify warehouse metering, query history, load history
--   Deploy a driver-side session hook to set QUERY_TAG=team=X,pipeline=Y,env=Z
--   Establish baseline: credits_30d, warehouses, top 20 queries by credit share

WITH baseline AS (
  SELECT
    SUM(credits_used)                                            AS credits_30d,
    COUNT(DISTINCT warehouse_name)                               AS warehouses,
    ROUND(SUM(credits_used) * 2.00, 0)                           AS spend_list_usd_30d
  FROM  snowflake.account_usage.warehouse_metering_history
  WHERE start_time >= DATEADD('day', -30, CURRENT_TIMESTAMP())
)
SELECT * FROM baseline;

-- Week 2 — auto-suspend + right-sizing
ALTER WAREHOUSE analytics_bi   SET AUTO_SUSPEND = 60, AUTO_RESUME = TRUE, WAREHOUSE_SIZE = 'LARGE';
ALTER WAREHOUSE dbt_prod       SET AUTO_SUSPEND = 60, WAREHOUSE_SIZE = 'MEDIUM';
ALTER WAREHOUSE adhoc_ds       SET AUTO_SUSPEND = 60, WAREHOUSE_SIZE = 'X-LARGE';
ALTER WAREHOUSE etl_stg        SET AUTO_SUSPEND = 60, WAREHOUSE_SIZE = 'MEDIUM';
ALTER WAREHOUSE reporting_ro   SET AUTO_SUSPEND = 60, WAREHOUSE_SIZE = 'SMALL';

-- Week 3 — query-tag rollout
-- Set at the driver layer:
-- python:  cur.execute("ALTER SESSION SET QUERY_TAG = %s", (f"team={team},pipeline={pipeline},env=prod",))
-- dbt:     profiles.yml query_tag: 'dbt|team={{ target.name }}|env={{ target.name }}'
-- looker:  connection.jdbc_additional_params: 'QUERY_TAG=team=bi,tool=looker'

-- Week 4 — commit sizing + chargeback
-- Model on-demand spend for last 90 days, pick a commit tier
WITH usage_curve AS (
  SELECT DATE_TRUNC('day', start_time) AS d,
         SUM(credits_used)              AS credits
  FROM   snowflake.account_usage.warehouse_metering_history
  WHERE  start_time >= DATEADD('day', -90, CURRENT_TIMESTAMP())
  GROUP  BY d
),
percentiles AS (
  SELECT
    PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY credits) AS p50,
    PERCENTILE_CONT(0.90) WITHIN GROUP (ORDER BY credits) AS p90,
    AVG(credits) * 30                                     AS monthly_forecast
  FROM   usage_curve
)
SELECT p50 * 30 AS commit_floor, p90 * 30 AS commit_top, monthly_forecast FROM percentiles;

Step-by-step trace.

Week	Activity	Measurable outcome
1	Baseline + tag driver hook	Baseline credits + tag coverage %
2	Right-sizing + auto-suspend	Idle-hour credits ↓ 40–70%
3	Query-tag rollout	Untagged spend ↓ 40% → <10%
4	Commit sizing + chargeback	Commit tier signed; per-team report shipped

After 30 days, the account has right-sized warehouses, aggressive auto-suspend, near-complete query-tag coverage, a right-sized commit, and a per-team chargeback report. Typical spend reduction is 25–40% on the same workload; the CFO gets a delta report signed off against the Week-1 baseline.

Output:

Surface	Before (Week 1)	After (Week 4)	Delta
Monthly credits	68,000	44,000	−35%
Untagged spend	42%	6%	−86%
Warehouses AUTO_SUSPEND ≤ 60	20%	100%	+400%
Effective $/credit (with commit)	$2.00	$1.55	−22.5%
Monthly $ spend	$136,000	$68,200	−50%

Why this works — concept by concept:

Baseline first, changes second — the CFO believes numbers, not narratives. Week 1 exists to establish the baseline; every Week-2+ change is measured against it.
Auto-suspend is the biggest single lever — dropping from 600 s to 60 s across all warehouses typically cuts idle spend by 60–80%. No commit, no re-architecture, no engineering time.
Query-tag as code — enforcing the tag at the driver layer (not as policy) drives coverage from 60% to 95%+ in weeks. The tag becomes a required GUC set on session open.
Commit sized to the floor — using the P50 daily credit spend × 30 as the commit floor guarantees you use the full commit; the P50-to-P90 delta stays on-demand at the higher rate, which is the correct trade.
Cost — 30 days of one senior engineer for a $60k+/month recurring saving. The ROI is typically 20–50× in year 1; the plan pays for itself before the first invoice cycle.

SQL
Topic — sql
SQL Snowflake credit-attribution problems

Practice →

Optimization Topic — optimization Optimization problems on Snowflake warehouse tuning

Practice →

3. BigQuery slots — on-demand vs Editions + reservations

`bigquery slots` are the unit of computational capacity; slot-seconds are the billing atom on Editions and bytes-scanned is the billing atom on on-demand

The mental model in one line: a BigQuery slot is a virtual CPU + memory budget, on-demand charges $6.25 per TB of bytes scanned with a free autoscale up to 2000 slots, Editions (Standard / Enterprise / Enterprise Plus) charge a flat $/slot-hour under reservations with autoscale between a baseline and a max, and the choice between them is a finops data platform decision driven by workload shape and predictability. If you can name the two billing atoms and the crossover between them, you have the BigQuery FinOps story.

The four axes for BigQuery FinOps.

Unit. On-demand: bytes scanned at $6.25/TB (US pricing at time of writing). Editions: slot-seconds at a flat $/slot-hr — Standard ~$0.04/slot-hr, Enterprise ~$0.06, Enterprise Plus ~$0.10.
Elasticity. On-demand: 2000-slot autoscale free-tier per query; no reservation control. Editions: autoscaler between min_slots (baseline) and max_slots (ceiling), billed by the second the slots exist.
Commit. On-demand: no commit. Editions: 1-year or 3-year commits on baseline slots at deeper discounts.
Attribution. INFORMATION_SCHEMA.JOBS_BY_PROJECT exposes total_bytes_processed, total_slot_ms, labels, user_email. Labels + user_email are the attribution levers.

On-demand model — bytes scanned.

Billing atom. Bytes read from storage during query execution. Column pruning + partition pruning are the two biggest levers on the on-demand bill.
Free autoscale. Up to 2000 slots per query at no extra charge — you pay only the bytes, not the slots.
Predictability. Poor. A single SELECT * FROM huge_table can burn the monthly budget in an hour.
When it wins. Ad-hoc analysis, low-frequency workloads, sub-100 TB monthly scans, teams where slot-hour demand is spiky.

Editions model — slots + reservations.

Billing atom. Slot-seconds while the reservation is active. Idle slots still bill.
Editions tiers. Standard (basic BI), Enterprise (columnar + BI Engine + column-level ACL), Enterprise Plus (multi-region + CMEK + advanced governance).
Reservation. A named allocation of slots — e.g. analytics-reservation with baseline = 500, max = 2000. Assign to projects via assignments.
Autoscaler. Scales the actual slot count between the baseline and the max in response to demand. Baseline is billed 24×7; autoscaled slots are billed by the second they exist.
When it wins. Sustained slot-hour demand > ~200k/month, workloads requiring Enterprise features, teams that value bill predictability over usage-based pricing.

The 300-slot free-autoscale trap.

Reality. On-demand is not free at 300 slots — it uses 2000 slots per query free but bills the bytes. Even a 300-slot query scanning 500 GB pays $3.15 for the scan.
The confusion. Newcomers sometimes think on-demand is "free until 2000 slots." It is not — every byte scanned costs money regardless of slot count.
The correct framing. On-demand bills bytes; Editions bills slot-seconds; they are orthogonal pricing dimensions.

The commit ladder.

1-year commit — ~20% discount on baseline slots vs no-commit Editions.
3-year commit — ~40% discount, but binds you to the growth curve.
Flex slots (short-term). Removed in favour of the Editions autoscaler pattern; historical only.

The reservation-vs-assignment model.

Reservation — the named allocation of slots. Owned by a billing project.
Assignment — maps a Google Cloud project to a reservation. One reservation can serve N projects.
Job priority. INTERACTIVE vs BATCH per assignment. Batch jobs are queued during peak; interactive jobs get preferential scheduling. Use batch for scheduled ETL, interactive for user-facing BI.

Common interview probes on BigQuery slots.

"What's the difference between on-demand and Editions?" — bytes vs slot-seconds; predictability trade-off.
"How does the autoscaler work?" — baseline is billed 24×7; autoscale between min and max in response to demand; billed by the second slots exist.
"When would you buy a 3-year commit?" — only when the growth curve is very high-confidence; otherwise 1-year.
"How do you attribute BigQuery spend to teams?" — JOBS_BY_PROJECT + labels + user_email; roll up by label prefix.

Worked example — same ELT on 500 baseline + 2000 max slots

Detailed explanation. A nightly ELT job scans 400 TB of raw event data and consumes 1500 slot-hours during a 4-hour window. Model the cost under on-demand and under an Enterprise Edition reservation of baseline = 500, max = 2000 at $0.06/slot-hour. Which is cheaper and by how much?

On-demand. 400 TB × $6.25 = $2,500 per night.
Editions. 500 baseline slots × 24 hr = 12,000 slot-hours per day. Autoscale up to 2000 during the ELT window fills the extra 1000 slot-hours (500 × 4 hr = 2000 extra above baseline capped at max). Total slot-hours = 12,000 + 2000 = 14,000/day at $0.06 = $840/day.
The winner. Editions by 3× — but only if the reservation is being used by other workloads during the day; otherwise you overpay on the baseline slots that sit idle.

Question. Model both scenarios (Editions with and without daytime BI utilisation). Show which one wins under which assumption.

Input.

Case	Nightly ELT	Daytime BI usage	On-demand cost	Editions baseline hr	Editions autoscale hr	Editions cost
A. Editions, no daytime BI	400 TB, 1500 slot-hr	0 slot-hr	$2,500	12,000 idle	2,000	14,000 × $0.06 = $840
B. Editions, heavy daytime BI	400 TB, 1500 slot-hr	5000 slot-hr	$2,500	12,000 used	2,000	14,000 × $0.06 = $840
C. On-demand only, no reservation	400 TB	250 TB BI daytime	$4,062	—	—	—

Code.

-- BigQuery — model cost under Editions and on-demand
DECLARE nightly_bytes_tb           FLOAT64 DEFAULT 400.0;
DECLARE nightly_slot_hours         FLOAT64 DEFAULT 1500.0;
DECLARE daytime_slot_hours         FLOAT64 DEFAULT 5000.0;
DECLARE baseline_slots             INT64   DEFAULT 500;
DECLARE max_slots                  INT64   DEFAULT 2000;
DECLARE edition_price_per_slot_hr  FLOAT64 DEFAULT 0.06;   -- Enterprise
DECLARE on_demand_price_per_tb     FLOAT64 DEFAULT 6.25;

-- Editions daily slot-hours = baseline running 24 hr + autoscale filling gaps
WITH ed AS (
  SELECT
    baseline_slots * 24                                                       AS baseline_slot_hr,
    -- autoscale slot-hours are the delta demanded above the baseline, capped by (max - baseline)
    -- Rough model: total demand = nightly + daytime; baseline covers first 12k hr
    GREATEST(nightly_slot_hours + daytime_slot_hours - baseline_slots * 24, 0) AS autoscale_slot_hr_demand,
    (max_slots - baseline_slots) * 4                                           AS autoscale_slot_hr_capacity
),
ed_cost AS (
  SELECT
    baseline_slot_hr,
    LEAST(autoscale_slot_hr_demand, autoscale_slot_hr_capacity) AS autoscale_slot_hr_used,
    (baseline_slot_hr + LEAST(autoscale_slot_hr_demand, autoscale_slot_hr_capacity))
      * edition_price_per_slot_hr AS edition_cost_per_day
  FROM ed
),
od_cost AS (
  SELECT nightly_bytes_tb * on_demand_price_per_tb AS on_demand_cost_per_day
)
SELECT
  ed.edition_cost_per_day       AS editions_daily_cost,
  od.on_demand_cost_per_day     AS on_demand_daily_cost,
  od.on_demand_cost_per_day - ed.edition_cost_per_day AS savings_per_day,
  30 * (od.on_demand_cost_per_day - ed.edition_cost_per_day) AS savings_per_month
FROM ed_cost ed, od_cost od;

Step-by-step explanation.

Compute the Editions cost as (baseline_slots × 24 hr) + autoscale_hours × $0.06. The baseline slots run 24×7 whether or not you use them — that is the reservation commitment.
Autoscale slot-hours are the demand above the baseline. If nightly demand is 1500 slot-hours in a 4-hour window (average 375 slots/hr), the baseline of 500 covers it — no autoscale needed. But if daytime BI adds 5000 slot-hours across the working day (average ~208 slots/hr), the baseline handles that too.
In case A (no daytime BI), you pay for 12,000 slot-hours of idle baseline capacity per day — 500 slots × 24 hr × $0.06 = $720/day of pure idle cost. The reservation is a bad fit; on-demand ($2,500) is cheaper if you actually use those bytes-scanned dollars efficiently, which usually requires partition/column pruning.
In case B (heavy daytime BI), the baseline is fully utilised. You pay $840/day for Editions and get ~14,000 slot-hours of work done. On-demand for the same work would be higher — the reservation now wins.
The lesson: Editions wins only when the baseline is utilised. An underutilised reservation is worse than on-demand. The right analysis is "would we use this reservation for a majority of the hours we are paying for it?"

Output.

Case	Editions $/day	On-demand $/day	Winner	Assumption
A. No daytime BI	840	2,500	Editions (nominal)	But baseline is 60% idle
B. Heavy daytime BI	840	4,062	Editions	Baseline is 85% utilised
C. Only-nightly, small day	720 idle + 120 nightly	2,500	Editions barely	Uncomfortable trade

Rule of thumb. Never buy a BigQuery reservation for a single workload — buy it for a portfolio. The baseline must be utilised most of the day, not just during the ETL window. If nightly ETL is the only workload, on-demand is almost always cheaper.

Worked example — slot utilisation audit query

Detailed explanation. A senior FinOps engineer needs to know if the current Editions reservation is over- or under-provisioned. The answer lives in INFORMATION_SCHEMA.JOBS_BY_PROJECT (total_slot_ms) joined against the reservation's baseline × window capacity. The goal is a per-hour utilisation curve and a top-10 list of hours with idle capacity.

Utilisation. slot_ms_consumed / (baseline_slots × 3.6e6 × window_hours).
Target range. 60–80% average utilisation. Below 60% is over-provisioning; above 80% risks queuing.
Fix. Adjust baseline slots down (to save on idle) or up (to reduce autoscale-induced query latency).

Question. Build the SQL that computes hourly slot utilisation over the last 7 days for a project on a 500-slot baseline Enterprise reservation.

Input.

Assumption	Value
Baseline slots	500
Window	last 7 days
Utilisation threshold (low)	< 40%
Utilisation threshold (high)	> 90%

Code.

-- BigQuery — slot utilisation audit for an Editions reservation
DECLARE baseline_slots INT64 DEFAULT 500;

WITH hourly AS (
  SELECT
    TIMESTAMP_TRUNC(creation_time, HOUR)                     AS hour,
    SUM(total_slot_ms) / 1000.0                              AS slot_seconds_consumed,
    COUNT(*)                                                  AS query_count,
    APPROX_QUANTILES(total_slot_ms, 100)[OFFSET(99)] / 1000.0 AS p99_slot_seconds
  FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
  WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
    AND job_type = 'QUERY'
    AND state    = 'DONE'
  GROUP BY hour
),
util AS (
  SELECT
    hour,
    slot_seconds_consumed,
    query_count,
    p99_slot_seconds,
    -- Available slot-seconds in a 1-hour window = baseline_slots × 3600
    slot_seconds_consumed / (baseline_slots * 3600.0) AS utilisation
  FROM hourly
)
SELECT
  hour,
  ROUND(slot_seconds_consumed, 0) AS slot_seconds,
  query_count,
  ROUND(utilisation * 100, 1)      AS utilisation_pct,
  CASE
    WHEN utilisation < 0.40 THEN 'OVERPROVISIONED'
    WHEN utilisation > 0.90 THEN 'UNDERPROVISIONED'
    ELSE 'OK'
  END                              AS verdict
FROM util
ORDER BY hour;

-- Roll up to a single recommendation
WITH stats AS (
  SELECT
    AVG(slot_seconds_consumed / (baseline_slots * 3600.0)) AS avg_util,
    APPROX_QUANTILES(
      slot_seconds_consumed / (baseline_slots * 3600.0), 100
    )[OFFSET(90)]                                          AS p90_util,
    APPROX_QUANTILES(
      slot_seconds_consumed / (baseline_slots * 3600.0), 100
    )[OFFSET(99)]                                          AS p99_util
  FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
  WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
    AND job_type = 'QUERY'
    AND state    = 'DONE'
)
SELECT
  ROUND(avg_util * 100, 1) AS avg_utilisation_pct,
  ROUND(p90_util * 100, 1) AS p90_utilisation_pct,
  ROUND(p99_util * 100, 1) AS p99_utilisation_pct,
  CASE
    WHEN avg_util < 0.40 THEN CONCAT('CUT baseline to ', CAST(baseline_slots * 0.6 AS INT64), ' slots')
    WHEN p99_util > 0.95 THEN CONCAT('RAISE max_slots (baseline OK); p99 saturated')
    ELSE 'baseline sizing is OK'
  END                       AS recommendation
FROM stats;

Step-by-step explanation.

Aggregate total_slot_ms by hour from JOBS_BY_PROJECT. Convert to slot-seconds by dividing by 1000. The available capacity per hour is baseline_slots × 3600.
Compute the utilisation ratio per hour. Below 40% consistently means the reservation is over-provisioned; above 90% consistently means queries are queuing.
The hourly panel surfaces the shape — most workloads have a heavy nightly ETL window, a moderate business-day window, and a quiet overnight window. Idle overnight hours are the tell-tale of a wrong baseline.
The rollup query computes the average, P90, and P99 utilisation and issues a plain-English recommendation. This is what you paste into the FinOps Slack channel.
The recommendation is boring on purpose — either "cut baseline" or "raise max" or "leave alone." Nuance lives in the Grafana panels; the message shows up as a headline number.

Output.

avg_util	p90_util	p99_util	recommendation
32.4%	78.3%	96.1%	CUT baseline to 300 slots (average utilisation < 40%)

Rule of thumb. Run the utilisation audit weekly. Average utilisation below 40% is a signal to cut baseline; P99 above 95% is a signal to raise max_slots. Do not move both knobs at once — measure the effect of one change before the next.

Worked example — label-based chargeback with JOBS_BY_PROJECT

Detailed explanation. BigQuery attribution runs on the labels column of JOBS_BY_PROJECT. A convention like team:marketing, pipeline:email_metrics, env:prod gives the same per-team chargeback story as Snowflake's QUERY_TAG. The join is against a per-project slot-hour price and (for Editions) a per-hour reservation allocation.

The convention. Set labels at query time via SET @@labels = 'team:X,pipeline:Y' or per-job in the API.
The parse. Extract label values via labels[SAFE_OFFSET(0)].value or by unnest.
The proration. For Editions, spend is proportional to slot-hours; for on-demand, spend is proportional to bytes-scanned.

Question. Build the SQL that produces a per-team monthly chargeback report from JOBS_BY_PROJECT labels, under both on-demand and Editions pricing.

Input.

Convention	Value
Label key for team	`team`
Label key for pipeline	`pipeline`
Report grain	(team, month)
Editions rate	$0.06/slot-hour
On-demand rate	$6.25/TB scanned

Code.

-- BigQuery — per-team chargeback report from labels
WITH labelled AS (
  SELECT
    creation_time,
    total_slot_ms,
    total_bytes_billed,
    -- extract the value for the 'team' label; NULL if not set
    (SELECT value
       FROM UNNEST(labels)
      WHERE key = 'team')   AS team,
    (SELECT value
       FROM UNNEST(labels)
      WHERE key = 'pipeline') AS pipeline
  FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
  WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
    AND job_type = 'QUERY'
    AND state    = 'DONE'
),
aggregated AS (
  SELECT
    DATE_TRUNC(DATE(creation_time), MONTH)       AS month,
    IFNULL(team, 'untagged')                     AS team,
    SUM(total_slot_ms) / 1000.0 / 3600.0         AS slot_hours,
    SUM(total_bytes_billed) / POW(1024, 4)       AS tb_scanned
  FROM labelled
  GROUP BY month, team
)
SELECT
  month,
  team,
  ROUND(slot_hours, 1)                         AS slot_hours,
  ROUND(tb_scanned, 2)                         AS tb_scanned,
  ROUND(slot_hours * 0.06, 0)                  AS editions_cost_usd,
  ROUND(tb_scanned * 6.25, 0)                  AS on_demand_cost_usd
FROM aggregated
ORDER BY month, editions_cost_usd DESC;

Step-by-step explanation.

INFORMATION_SCHEMA.JOBS_BY_PROJECT exposes a labels array — one row per job, containing all labels attached to that job. UNNEST + WHERE key = 'team' extracts the team name.
Roll up per team, per month. Compute both slot-hours (relevant for Editions) and TB-scanned (relevant for on-demand). This gives you the ammunition for both pricing models.
Multiply by the appropriate rate: slot_hours × $0.06 for Enterprise Edition; tb_scanned × $6.25 for on-demand. Show both columns so the reader sees the pricing-model impact.
The untagged bucket surfaces attribution debt. Any team pushing large untagged spend needs a driver-side label injection — same pattern as Snowflake's QUERY_TAG.
Sanity check: sum the slot-hours across all teams and compare against the reservation's baseline × window. If the sum exceeds the reservation, the extra slot-hours came from autoscale; that fraction is the "burstable" cost you should surface separately.

Output.

month	team	slot_hr	tb	ed $	od $
2026-06	marketing	3,800	210	228	1,313
2026-06	growth	2,900	180	174	1,125
2026-06	finance	1,600	90	96	563
2026-06	ml	4,100	340	246	2,125
2026-06	untagged	950	80	57	500

Rule of thumb. The label convention is only as good as the driver-side enforcement. Ship a session-open hook in every client library (Python, JDBC, dbt) that sets the label from the environment; treat any untagged job over $50/month as a bug.

Senior interview question on BigQuery FinOps

A senior interviewer might ask: "You're evaluating BigQuery for a new analytics platform. Walk me through how you'd decide between on-demand and Enterprise Edition, what the break-even utilisation is, and what the migration story looks like if you start on one and want to move to the other 12 months in."

Solution Using workload-shape analysis + a phased migration path

-- Phase 1 — measure current workload shape
-- If slot-hour demand < ~150k/month → on-demand
-- If slot-hour demand > ~200k/month AND utilisation is steady → Editions
WITH workload AS (
  SELECT
    DATE_TRUNC(DATE(creation_time), MONTH)              AS month,
    SUM(total_slot_ms) / 1000.0 / 3600.0                AS slot_hours,
    SUM(total_bytes_billed) / POW(1024, 4)              AS tb_scanned,
    -- current on-demand cost
    SUM(total_bytes_billed) / POW(1024, 4) * 6.25       AS on_demand_cost,
    -- equivalent Editions cost at target baseline (500) + 500 max
    500 * 730 * 0.06                                    AS ed_baseline_cost
  FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
  WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 90 DAY)
    AND job_type = 'QUERY'
  GROUP BY month
)
SELECT
  month,
  slot_hours,
  tb_scanned,
  on_demand_cost,
  ed_baseline_cost,
  CASE
    WHEN slot_hours < 150000 THEN 'stay on-demand'
    WHEN slot_hours > 250000 AND on_demand_cost > ed_baseline_cost * 1.5 THEN 'migrate to Editions'
    ELSE 'monitor — mixed workload'
  END AS recommendation
FROM workload
ORDER BY month;

# Phase 2 — set up Editions reservation with autoscaler
# terraform (or gcloud CLI)
resource "google_bigquery_reservation" "analytics" {
  name              = "analytics-reservation"
  location          = "us"
  slot_capacity     = 500     # baseline
  edition           = "ENTERPRISE"
  autoscale {
    max_slots       = 2000
  }
  ignore_idle_slots = false   # let baseline slots serve queries even when idle
}

resource "google_bigquery_reservation_assignment" "analytics_prod" {
  reservation = google_bigquery_reservation.analytics.id
  assignee    = "projects/my-analytics-prod"
  job_type    = "QUERY"
  priority    = "INTERACTIVE"   # or BATCH for scheduled ETL
}

Step-by-step trace.

Phase	Activity	Signal
1	Measure slot-hours / month	< 150k → on-demand; > 250k → Editions
2	Provision reservation at P50 slot-hour rate	Baseline = P50, max = P95
3	Assign projects to reservation with INTERACTIVE priority	ETL uses BATCH; BI uses INTERACTIVE
4	Monitor utilisation weekly	Cut baseline if avg util < 40%
5	Sign 1-year commit once utilisation is stable	Save 20% on baseline slots

The migration between models is smooth in either direction: on-demand → Editions requires a reservation + assignment (Terraform-level change); Editions → on-demand simply removes the assignment. Bill your team monthly against both pricing models until you have confidence in the choice.

Output:

Model	Break-even slot-hr/month	Predictability	Best for
On-demand	< 150k	poor	ad-hoc, small teams
Editions Standard	150k–500k	good	steady BI workloads
Editions Enterprise	> 500k	high	large analytics platforms
Editions Enterprise Plus	multi-region, governance	high	regulated industries

Why this works — concept by concept:

Workload-shape-first — the on-demand vs Editions decision is not about total spend but about the shape of the demand curve. Steady demand favours Editions; spiky demand favours on-demand.
Reservation at the P50, autoscale to the P95 — baseline covers the "always-on" demand; autoscale absorbs the tail. Both knobs come out of the JOBS_BY_PROJECT audit.
Assignment priority — INTERACTIVE for user-facing BI; BATCH for scheduled ETL. Batch queues during peak; interactive gets guaranteed capacity. This mapping isolates human-facing latency from scheduled-load bursts.
Terraform-managed reservation — the reservation should live in IaC. Change the slot capacity via a terraform apply, not a console click; the audit trail is now git.
Cost — the analysis is a JOBS_BY_PROJECT query; the migration is a Terraform change. The 1-year commit sign-off is a finance step. The savings compound at 20% per year on the baseline once utilisation is stable.

SQL
Topic — sql
SQL BigQuery slot and cost problems

Practice →

Optimization Topic — optimization Optimization problems on BigQuery reservation tuning

Practice →

4. Databricks DBUs — cluster type × compute

`databricks dbu` is a normalised compute unit whose rate depends on cluster type × Photon × infra — three orthogonal pricing dimensions on one bill

The mental model in one line: a Databricks Unit (DBU) is a normalised measure of processing capability consumed per hour, the DBU-rate-per-cluster-hour depends on cluster type (Jobs < SQL Warehouse < All-Purpose), Photon roughly doubles the rate but roughly halves the wall clock, and the underlying VM cost is layered on top with spot-vs-on-demand as the last knob. Three orthogonal dimensions, one bill; miss any one of them and the FinOps analysis is wrong.

The four axes for Databricks FinOps.

Unit. A DBU is Databricks' normalised compute unit. The DBU rate (DBUs consumed per hour of cluster-time) depends on cluster type × Photon.
Elasticity. Classic clusters take 2–5 minutes to acquire underlying VMs; serverless SQL warehouses spin up in seconds. Elasticity dictates how aggressively you can auto-terminate.
Commit. DBU commits (annual) discount the effective $/DBU rate. Reserved instances on the underlying VM layer stack on top for a second discount axis.
Attribution. system.billing.usage table exposes usage_quantity (DBUs) per SKU per workspace per cluster tag. Cluster tags (team, pipeline, env) are the primary attribution lever.

The DBU rate ladder.

Jobs Compute (classic). ~0.15 DBU/hour per node (standard). Cheapest — designed for scheduled ETL.
Jobs Compute + Photon. ~0.30 DBU/hour per node. 2× rate; ~2× faster on many workloads.
SQL Warehouse (serverless). ~0.55 DBU/hour per node. Optimised for BI; near-zero startup.
SQL Warehouse Photon. ~0.70 DBU/hour per node. Enables Photon by default; the price is the price.
All-Purpose (interactive notebooks). ~0.55 DBU/hour per node. Meant for development, not scheduled work.
All-Purpose + Photon. ~0.79 DBU/hour per node. Highest per-node rate.

The infra layer — spot vs on-demand.

On-demand VMs. Full VM price (typically $10–20/hr for compute-optimised instances).
Spot VMs. 40–90% cheaper but revocable. Databricks handles graceful revocation on managed cluster policies.
Fleet clusters. Mix of spot + on-demand — driver on on-demand (must not be revoked), workers on spot. The pragmatic default.

The Photon story.

What. Photon is a vectorised C++ execution engine that runs SQL and DataFrame operations natively without JVM overhead.
DBU cost. Roughly doubles the DBU rate per node.
Wall-clock win. Typically 1.5–3× faster on SQL-heavy workloads. Sometimes flat on Python-UDF-heavy workloads.
Break-even. If Photon halves your wall clock, the DBU cost is flat but the VM cost halves. Net: Photon almost always wins for SQL-heavy workloads; check the Photon-eligible fraction of your queries before assuming.

Serverless SQL warehouses.

What. Databricks-managed clusters that spin up in <10 seconds and auto-terminate in seconds when idle.
DBU rate. Higher than classic SQL warehouses (~$0.70/DBU for BI workloads).
VM cost. Included — you don't see the underlying VM bill separately. Simpler.
When to use. Interactive BI dashboards where cold-start matters more than steady-state cost.

Common interview probes on Databricks DBUs.

"What's a DBU?" — a normalised compute unit; the rate depends on cluster type × Photon.
"Why is All-Purpose 3× more expensive than Jobs?" — because interactive notebooks bill differently from scheduled batch. Never run scheduled ETL on All-Purpose.
"Should we enable Photon?" — yes for SQL-heavy workloads; the wall-clock reduction pays for the DBU rate bump.
"Spot vs on-demand?" — spot for workers, on-demand for the driver; fleet clusters give you both.

Worked example — cost of an ELT on Jobs cluster with Photon

Detailed explanation. A nightly ELT on Databricks runs on a Jobs cluster with 8 worker nodes (i3.2xlarge at $0.624/hr each on-demand, $0.187/hr on spot) plus a driver at similar price. Photon halves the wall clock from 4 hours to 2 hours. Model the total spend under four combinations: {Photon on/off} × {spot/on-demand}.

Cluster. 8 workers + 1 driver = 9 nodes.
DBU rate. 0.15/hour/node without Photon; 0.30/hour/node with Photon.
VM prices. $0.624/hr on-demand; $0.187/hr spot (per node).
$/DBU. $0.15 for Jobs compute.

Question. For each of the four configurations, compute the total nightly cost. Which is cheapest? What's the sensitivity to spot revocation?

Input.

Config	Wall (hr)	DBU rate/node	Total DBUs	DBU cost	VM $/hr/node	VM cost	Total
A. No Photon, on-demand	4	0.15	5.4	0.81	0.624	22.46	23.27
B. Photon, on-demand	2	0.30	5.4	0.81	0.624	11.23	12.04
C. No Photon, spot	4	0.15	5.4	0.81	0.187	6.73	7.54
D. Photon, spot	2	0.30	5.4	0.81	0.187	3.37	4.18

Code.

# Databricks — nightly ETL cost model
def dbu_etl_cost(nodes: int, wall_hr: float, dbu_rate_per_node: float,
                 dbu_price: float, vm_hr_price: float) -> dict:
    dbu_total  = nodes * wall_hr * dbu_rate_per_node
    dbu_cost   = dbu_total * dbu_price
    vm_cost    = nodes * wall_hr * vm_hr_price
    return {
        "wall_hr":   wall_hr,
        "dbu_total": round(dbu_total, 2),
        "dbu_cost":  round(dbu_cost, 2),
        "vm_cost":   round(vm_cost, 2),
        "total":     round(dbu_cost + vm_cost, 2),
    }

nodes = 9  # 8 workers + 1 driver

# Four configurations
scenarios = {
    "A. No Photon, on-demand": dbu_etl_cost(nodes, 4.0, 0.15, 0.15, 0.624),
    "B. Photon,    on-demand": dbu_etl_cost(nodes, 2.0, 0.30, 0.15, 0.624),
    "C. No Photon, spot":       dbu_etl_cost(nodes, 4.0, 0.15, 0.15, 0.187),
    "D. Photon,    spot":       dbu_etl_cost(nodes, 2.0, 0.30, 0.15, 0.187),
}

for name, cost in scenarios.items():
    print(f"{name}: total ${cost['total']}, DBU ${cost['dbu_cost']}, VM ${cost['vm_cost']}")

# Cluster config for the winning configuration (D. Photon + spot workers)
cluster:
  cluster_name: nightly-elt
  spark_version: 15.4.x-photon-scala2.12   # Photon runtime
  node_type_id: i3.2xlarge
  driver_node_type_id: i3.2xlarge
  autoscale:
    min_workers: 4
    max_workers: 8
  autotermination_minutes: 10
  aws_attributes:
    availability: SPOT_WITH_FALLBACK       # spot for workers, fallback to on-demand
    first_on_demand: 1                     # driver stays on on-demand
    spot_bid_price_percent: 100
  runtime_engine: PHOTON
  custom_tags:
    team: data-eng
    pipeline: nightly-elt
    env: prod

Step-by-step explanation.

Scenario A (baseline) — no Photon, on-demand. 9 nodes × 4 hr × 0.15 DBU/hr = 5.4 DBUs × $0.15 = $0.81 for compute. VM = 9 × 4 × $0.624 = $22.46. Total = $23.27. Note: DBU cost is negligible; the VM dominates.
Scenario B — Photon halves the wall clock (2 hr) but doubles the DBU rate (0.30/hr/node). Total DBUs unchanged at 5.4; DBU cost unchanged at $0.81. But VM cost halves to $11.23 because the cluster runs half as long. Total = $12.04. Photon saved ~$11.
Scenario C — no Photon but spot workers. VM cost drops from $22.46 to $6.73 (spot is ~70% cheaper). Total = $7.54. Note: this is cheaper than Scenario B (Photon on-demand).
Scenario D — Photon + spot. Best of both: half the wall clock and cheap VMs. VM = 9 × 2 × $0.187 = $3.37. Total = $4.18. Nearly 6× cheaper than the baseline.
The sensitivity to spot: if spot instances are revoked mid-job, the cluster loses workers and either re-provisions on-demand (SPOT_WITH_FALLBACK) or restarts the failed tasks. Delta Lake with checkpointed workloads handles this gracefully; a non-idempotent pipeline does not. Always ask whether the pipeline is spot-safe before committing to spot workers.

Output.

Scenario	Wall	DBU $	VM $	Total	Savings vs A
A. No Photon, on-demand	4 hr	0.81	22.46	23.27	—
B. Photon, on-demand	2 hr	0.81	11.23	12.04	48%
C. No Photon, spot	4 hr	0.81	6.73	7.54	68%
D. Photon, spot	2 hr	0.81	3.37	4.18	82%

Rule of thumb. For scheduled ETL, default to Photon + spot workers (with SPOT_WITH_FALLBACK) + on-demand driver. That combination compounds a wall-clock discount with a VM discount and is the cheapest correct answer for restart-tolerant pipelines.

Worked example — DBU consumption audit query

Detailed explanation. The system.billing.usage table (Unity Catalog) is the source of truth for Databricks spend. It exposes per-workspace, per-cluster, per-SKU DBU consumption with timestamps. A senior FinOps engineer uses it to build a top-10 clusters-by-spend report and to detect the "always-on notebook cluster" leak.

The leak pattern. An All-Purpose cluster left running overnight — 0.55 DBU/hr/node × 9 nodes × 12 idle hours = 59 DBUs at $0.55/DBU = $32/night per cluster.
The audit. Rank clusters by DBUs consumed; cross-reference against cluster tags for team attribution; flag any All-Purpose cluster with autotermination_minutes > 30 as a candidate leak.
The fix. Set autotermination_minutes = 10 on all All-Purpose clusters; migrate scheduled work to Jobs clusters.

Question. Build the SQL that ranks the top 10 clusters by DBU consumption over the last 30 days and surfaces the auto-termination setting for each.

Input.

Assumption	Value
Metering table	`system.billing.usage`
Cluster metadata	`system.compute.clusters`
Window	last 30 days
Leak threshold	All-Purpose + autoterm > 30 min

Code.

-- Databricks — top clusters by DBU consumption
WITH usage AS (
  SELECT
    workspace_id,
    usage_metadata.cluster_id                                            AS cluster_id,
    sku_name,
    DATE_TRUNC('DAY', usage_start_time)                                  AS day,
    SUM(usage_quantity)                                                  AS dbus,
    -- $0.55 is a stand-in list price; substitute your negotiated rate
    SUM(usage_quantity) * 0.55                                           AS spend_usd
  FROM   system.billing.usage
  WHERE  usage_start_time >= date_sub(current_date(), 30)
    AND  billing_origin_product = 'ALL_PURPOSE_COMPUTE'
       OR billing_origin_product = 'JOBS_COMPUTE'
       OR billing_origin_product = 'SQL'
  GROUP BY workspace_id, cluster_id, sku_name, day
),
cluster_meta AS (
  SELECT
    cluster_id,
    cluster_name,
    cluster_source,           -- 'UI', 'JOB', 'API'
    autotermination_minutes,
    custom_tags['team']       AS team,
    custom_tags['pipeline']   AS pipeline
  FROM system.compute.clusters
),
ranked AS (
  SELECT
    u.workspace_id,
    u.cluster_id,
    c.cluster_name,
    c.team,
    c.pipeline,
    c.autotermination_minutes,
    u.sku_name,
    SUM(u.dbus)      AS total_dbus_30d,
    SUM(u.spend_usd) AS total_spend_30d
  FROM   usage u
  LEFT   JOIN cluster_meta c USING (cluster_id)
  GROUP  BY u.workspace_id, u.cluster_id, c.cluster_name, c.team, c.pipeline,
            c.autotermination_minutes, u.sku_name
)
SELECT
  cluster_name,
  team,
  pipeline,
  sku_name,
  autotermination_minutes,
  ROUND(total_dbus_30d, 0)                             AS dbus_30d,
  ROUND(total_spend_30d, 0)                            AS spend_30d_usd,
  CASE
    WHEN sku_name LIKE '%ALL_PURPOSE%'
     AND autotermination_minutes > 30 THEN 'LEAK-RISK'
    WHEN sku_name LIKE '%ALL_PURPOSE%'
     AND autotermination_minutes IS NULL THEN 'NEVER-TERMINATES'
    ELSE 'OK'
  END                                                  AS verdict
FROM   ranked
ORDER  BY total_spend_30d DESC
LIMIT  10;

Step-by-step explanation.

Aggregate usage_quantity (DBUs) from system.billing.usage per cluster per day. Multiply by the list rate ($0.55 or your negotiated rate) to get dollar spend.
Join against system.compute.clusters to pull cluster metadata — cluster type via SKU name, autotermination setting, and any custom_tags for team / pipeline attribution.
Rank by 30-day spend. The top 3–5 clusters usually account for 60–80% of the DBU bill; those are the ones worth right-sizing.
The verdict column flags leaks: any All-Purpose cluster with autotermination_minutes > 30 (or NULL) is a leak-risk. All-Purpose is meant for interactive use; a long autoterm means notebooks are being left open overnight.
Follow-up actions: (a) migrate scheduled work to Jobs clusters (3–4× cheaper); (b) drop All-Purpose autoterm to 10 minutes; (c) enforce cluster tags via cluster policies so untagged clusters cannot be created.

Output.

cluster_name	team	sku	autoterm	dbus 30d	$ 30d	verdict
bi-adhoc-ap	analytics	ALL_PURPOSE	120	2,800	1,540	LEAK-RISK
ml-notebook-ap	ml	ALL_PURPOSE	60	2,400	1,320	LEAK-RISK
nightly-elt-jobs	data-eng	JOBS_COMPUTE	10	1,800	990	OK
dashboard-sql	bi	SQL	—	1,500	825	OK
...	...	...	...	...	...	...

Rule of thumb. All-Purpose clusters with autoterm > 30 minutes are the #1 Databricks spend leak. Cluster policies that enforce autotermination_minutes <= 30 on the All-Purpose SKU eliminate the leak systemically; run this audit weekly to catch the escapees.

Worked example — Photon break-even analysis

Detailed explanation. A team debates enabling Photon on a nightly job. Photon doubles the DBU rate but the wall-clock effect depends on the workload — SQL-heavy queries see 1.5–3× speedup; Python-UDF-heavy queries may see no speedup at all. Model the break-even Photon speedup ratio at which the two configurations cost the same, and provide the audit query to measure Photon's actual effect after enabling it.

Symbolic model. Without Photon: cost = wall × (dbu_rate × $/DBU + vm_$/hr). With Photon: cost = (wall/speedup) × (2 × dbu_rate × $/DBU + vm_$/hr).
Break-even speedup. Solve: wall × (r × p + v) = (wall/s) × (2r × p + v). Where r=DBU rate, p=$/DBU, v=VM $/hr, s=speedup. Solving: s = (2r × p + v) / (r × p + v).

Question. For a workload with DBU rate 0.15/hr, $/DBU $0.15, VM $/hr $0.624 per node — compute the Photon break-even speedup, then write the audit query that measures actual speedup after rollout.

Input.

Symbol	Value	Meaning
r	0.15	DBU rate per node per hour (no Photon)
2r	0.30	DBU rate per node per hour (Photon)
p	0.15	$/DBU list
v	0.624	VM $/hr per node

Code.

# Compute Photon break-even speedup
r      = 0.15   # DBU rate per node per hour (no Photon)
r_ph   = 0.30   # DBU rate per node per hour (Photon)
p      = 0.15   # $/DBU
v      = 0.624  # VM $/hr per node

# break-even: wall × (r × p + v) = (wall / s) × (r_ph × p + v)
# solve for s
s_break_even = (r_ph * p + v) / (r * p + v)
print(f"Photon break-even speedup: {s_break_even:.3f}×")

# For different workload characteristics
for v_hr in [0.10, 0.30, 0.624, 1.20]:
    s = (r_ph * p + v_hr) / (r * p + v_hr)
    print(f"  VM ${v_hr:.2f}/hr → break-even {s:.3f}×")

-- After Photon rollout — measure the actual speedup
WITH runs AS (
  SELECT
    run_id,
    workflow_id,
    runtime_engine,                     -- 'PHOTON' or 'STANDARD'
    execution_duration_seconds,
    DATE_TRUNC('DAY', start_time) AS day
  FROM system.workflow.job_run_timeline
  WHERE start_time >= date_sub(current_date(), 30)
    AND workflow_id = 'nightly-elt'
),
photon_stats AS (
  SELECT
    runtime_engine,
    COUNT(*)                          AS run_count,
    AVG(execution_duration_seconds)   AS avg_duration_s,
    PERCENTILE(execution_duration_seconds, 0.5) AS median_duration_s
  FROM runs
  GROUP BY runtime_engine
)
SELECT
  ROUND((SELECT median_duration_s FROM photon_stats WHERE runtime_engine = 'STANDARD')
        / (SELECT median_duration_s FROM photon_stats WHERE runtime_engine = 'PHOTON'), 2) AS actual_speedup,
  (SELECT ROUND(median_duration_s, 0) FROM photon_stats WHERE runtime_engine = 'STANDARD') AS standard_median_s,
  (SELECT ROUND(median_duration_s, 0) FROM photon_stats WHERE runtime_engine = 'PHOTON')   AS photon_median_s;

Step-by-step explanation.

Symbolic solve: the break-even speedup is (2r × p + v) / (r × p + v). Plug in the numbers: (0.30 × 0.15 + 0.624) / (0.15 × 0.15 + 0.624) = 0.669 / 0.647 = 1.034. Photon needs only a 1.03× wall-clock reduction to break even at this VM price.
Sensitivity to VM cost: cheaper VMs move the break-even up (Photon has less VM cost to save). At $0.10/hr VM, break-even is 1.13×. At $1.20/hr VM, break-even is 1.02×. Bigger VMs = easier Photon win.
The audit query measures actual median wall-clock duration under Photon and standard runtimes on the same workflow. If the actual speedup exceeds the break-even, Photon wins financially; if it doesn't, disable Photon and stay on standard.
Photon eligibility caveat: Python UDFs run outside the Photon engine; heavy UDF workloads see near-zero speedup because most of the work is not Photon-eligible. Measure before committing.
The pragmatic recommendation: enable Photon by default; measure actual speedup weekly for the first month; disable on any workflow where the speedup is below break-even.

Output.

VM $/hr	Break-even speedup	Interpretation
0.10	1.13×	Small VMs — Photon needs at least 1.13× to pay off
0.30	1.06×	Medium — Photon almost always wins
0.624	1.03×	Common i3.2xlarge — Photon wins on any real speedup
1.20	1.02×	Big VMs — Photon wins on trivial speedup

Rule of thumb. Photon's DBU rate bump is almost always dominated by the VM cost saving from wall-clock reduction. Enable Photon by default; audit the actual speedup on each workflow after rollout and disable only on workflows where measured speedup falls below break-even.

Senior interview question on Databricks FinOps

A senior interviewer might ask: "You inherit a Databricks workspace with $500k/year of spend, 80% on All-Purpose clusters, no cluster tags, and no auto-termination policy. Walk me through the first 30 days — what audits do you run, what policies do you enforce, and how do you migrate the workload without breaking user experience?"

Solution Using cluster policies + Jobs-cluster migration + tag enforcement

# Cluster policy — enforce tag + autoterm + SKU restrictions
# Applied via Databricks REST API or Terraform databricks_cluster_policy

policy_definition = {
    "spark_version": {"type": "regex", "pattern": "^15\\..*", "hidden": True},
    "runtime_engine": {"type": "fixed", "value": "PHOTON"},
    "autotermination_minutes": {
        "type": "range",
        "minValue": 10,
        "maxValue": 30,
        "defaultValue": 10
    },
    "custom_tags.team":     {"type": "unlimited", "isOptional": False},
    "custom_tags.pipeline": {"type": "unlimited", "isOptional": False},
    "custom_tags.env":      {"type": "allowlist", "values": ["dev","stg","prod"]},
    "aws_attributes.availability": {"type": "fixed", "value": "SPOT_WITH_FALLBACK"},
    "aws_attributes.first_on_demand": {"type": "fixed", "value": 1},
    "node_type_id": {"type": "allowlist",
                     "values": ["i3.xlarge", "i3.2xlarge", "i3.4xlarge"]},
    "cluster_source": {"type": "unlimited"}
}

-- 30-day plan checkpoints
-- Week 1 — measurement
SELECT
  billing_origin_product,
  SUM(usage_quantity)              AS dbus_30d,
  SUM(usage_quantity) * 0.55       AS list_spend_30d,
  COUNT(DISTINCT usage_metadata.cluster_id) AS clusters
FROM   system.billing.usage
WHERE  usage_start_time >= date_sub(current_date(), 30)
GROUP  BY billing_origin_product
ORDER  BY list_spend_30d DESC;

-- Week 2 — enforce cluster policy on All-Purpose
--   autotermination_minutes = 10; team + pipeline + env tags required

-- Week 3 — migrate scheduled work from All-Purpose → Jobs
--   For each job discovered in system.workflow.job_run_timeline that runs on ALL_PURPOSE:
--     recreate as Jobs cluster; retire the ALL_PURPOSE binding

-- Week 4 — Photon rollout audit + DBU commit sizing
WITH monthly_dbus AS (
  SELECT
    DATE_TRUNC('MONTH', usage_start_time) AS month,
    SUM(usage_quantity)                    AS dbus
  FROM   system.billing.usage
  WHERE  usage_start_time >= date_sub(current_date(), 90)
  GROUP  BY month
)
SELECT
  PERCENTILE(dbus, 0.50) AS commit_floor,
  PERCENTILE(dbus, 0.90) AS commit_top,
  AVG(dbus)               AS monthly_forecast
FROM monthly_dbus;

Step-by-step trace.

Week	Activity	Measurable outcome
1	Baseline via `system.billing.usage`	DBU by SKU, cluster count, tag coverage
2	Cluster policy enforced	100% new clusters have tags + autoterm ≤ 30
3	Jobs-cluster migration	All-Purpose share ↓ from 80% → 20%
4	Photon audit + DBU commit	Commit signed; recurring saving locked in

Typical outcomes: DBU consumption drops 30–50% purely from moving scheduled work off All-Purpose to Jobs clusters (3–4× cheaper rate), autotermination policy cuts idle All-Purpose leak to near-zero, and cluster-tag coverage rises from ~20% to ~95% within four weeks.

Output:

Surface	Before (Week 1)	After (Week 4)	Delta
Monthly DBUs	76,000	42,000	−45%
All-Purpose share of spend	80%	22%	−58 pp
Tag coverage	20%	95%	+75 pp
Auto-term ≤ 30 min compliance	15%	100%	+85 pp
Effective $/DBU (with commit)	0.55	0.42	−24%
Monthly spend	$41,800	$17,640	−58%

Why this works — concept by concept:

Cluster policies as guardrails — the policy makes tag + autoterm + SKU restrictions structural, not policy-document. A user cannot create a non-compliant cluster because the API rejects it.
Jobs-cluster migration — moving scheduled workloads off All-Purpose is a 3–4× rate cut on those DBUs with no functional change. The single highest-leverage change most Databricks accounts have.
Photon on by default — cluster policy sets runtime_engine = PHOTON on new clusters. Actual speedup measured after rollout; disable per-workflow if below break-even.
DBU commit sized at P50 — same principle as Snowflake: commit for the high-confidence floor, let the P50-to-P90 delta stay on-demand. Overflow at list price is cheap insurance; unused commit at the discounted rate is a straight loss.
Cost — 30 days of one senior engineer; typical monthly saving is $20k–$50k for a mid-sized account, so 20–50× ROI in year 1.

SQL
Topic — sql
SQL Databricks DBU-attribution problems

Practice →

Optimization Topic — optimization Optimization problems on Databricks cluster tuning

Practice →

5. Cross-warehouse comparison + FinOps patterns

Normalising credits, slot-hours, and DBU-hours onto a single `$/TB scanned` axis is the only apples-to-apples cost comparison — and it settles most warehouse arguments

The mental model in one line: the three warehouses do not compare on raw list price — they compare on $ / TB scanned, $ / query-hour, and $ / active-user-hour normalised across your actual workload — and the commit strategy is a separate decision layered on top of the pricing model. Once you have the three normalised numbers, the "which is cheaper" argument becomes numerical rather than religious.

The four axes for cross-warehouse FinOps.

Unit. Three fundamentally different atoms — credits (Snowflake), slot-seconds (BigQuery Editions), DBU-hours (Databricks). Normalise onto $/TB scanned and $/query-hour for comparison.
Elasticity. Snowflake auto-suspend is the fastest to zero; BigQuery autoscaler ramps in seconds; Databricks classic clusters take minutes; Databricks serverless is instant. Elasticity dictates idle-cost surface.
Commit. Snowflake capacity commit, BigQuery reservation, Databricks DBU commit — all three follow the same "commit-at-P50" heuristic. Escalators (multi-year commits) add another layer of discount at the cost of growth-curve lock-in.
Attribution. Snowflake QUERY_TAG + WAREHOUSE_METERING_HISTORY, BigQuery labels + JOBS_BY_PROJECT, Databricks custom_tags + system.billing.usage. Same architectural pattern in three different table shapes.

Normalising the three units.

Snowflake $/TB scanned. SUM(credits × $2) / SUM(bytes_scanned) / (1024^4). Typically $100–200/TB depending on workload shape.
BigQuery $/TB scanned (on-demand). $6.25/TB scanned by definition of the on-demand model.
BigQuery $/TB scanned (Editions). SUM(reservation_slot_hours × $0.06) / SUM(bytes_scanned) / (1024^4). Highly variable; depends on baseline utilisation.
Databricks $/TB scanned. Not directly available; back out via SUM(DBU × $0.55) / SUM(bytes_read_delta).
All three at $/query-hour. SUM(spend) / SUM(query_wall_hours). The user-facing latency dimension.

Commit strategy across warehouses.

Same pattern, different tables. Sign up for the P50 usage floor; let overflow stay on-demand.
Snowflake. Capacity commitment at 20–40% discount depending on tier and term.
BigQuery. Reservation with 1-year or 3-year commit; ~20% and ~40% discount respectively.
Databricks. DBU commit at similar discount ladders; reserved-VM discounts stack on top.
The escalator trap. Multi-year commits look attractive on year-1 spend but lock you into the growth curve. Prefer 1-year commits unless the roadmap is very high-confidence.

Multi-cloud portability.

Snowflake. Runs on AWS, GCP, Azure. Same pricing model; portability is real. Egress costs on cross-cloud data movement can dominate.
BigQuery. GCP-only. Zero portability. Compensate with Google Cloud commit deals.
Databricks. Runs on AWS, GCP, Azure. Same DBU model everywhere but VM prices differ; the underlying-cloud commit is separate.
The portability tax. Multi-cloud is a governance win, an efficiency loss. Each cloud has its own commit contract; you dilute your commit tier across regions and get worse discounts.

Idle-cost audit — the single biggest cross-warehouse lever.

Snowflake. Warehouses with AUTO_SUSPEND > 300. Usually 10–30% of the bill.
BigQuery. Editions reservations with average utilisation < 40%. Idle baseline slots.
Databricks. All-Purpose clusters with autotermination_minutes > 30. Overnight leaks.
The pattern. Every warehouse has an idle-cost surface; the shape differs but the audit is always "surface the top-N leakers, apply the fix, measure the delta."

Senior interview signals.

Naming the metering table. snowflake.account_usage.warehouse_metering_history, INFORMATION_SCHEMA.JOBS_BY_PROJECT, system.billing.usage. If you cannot name the table you cannot do FinOps.
The idle-cost story. Every senior answer names the specific idle-cost lever per warehouse before it names the commit strategy.
The commit-at-P50 heuristic. Sizing the commit at the daily P50 usage × month is the right first cut; refining it against workload growth is the second.
The chargeback story. Turning the warehouse bill into a per-team invoice is the discipline that makes engineering care about cost — without it, cost stays "somebody else's problem."

Common interview probes on cross-warehouse FinOps.

"Which warehouse is cheapest?" — answer with the three normalised metrics on your actual workload; don't answer with list prices.
"How do you compare Snowflake credits to BigQuery slots?" — normalise both to $/TB scanned or $/query-hour.
"When would you migrate off Snowflake to BigQuery (or vice versa)?" — when the normalised $/TB scanned differs by >2× on the specific workload shape, or when a governance / feature requirement forces the move.
"What's the single biggest cost mistake?" — running scheduled work on an interactive/All-Purpose warehouse; leaving idle warehouses running; buying a commit tier that exceeds actual usage.

Worked example — normalising three warehouses onto a single $/TB axis

Detailed explanation. A CFO asks the head of data "which warehouse is cheapest?" The honest answer is "here are the normalised $/TB and $/query-hour numbers on our actual workload." Build the query that produces those numbers for last month across Snowflake, BigQuery on-demand, and Databricks. Show why the answer varies by workload shape.

Snowflake. Credits × $2 (list) divided by bytes scanned.
BigQuery on-demand. $6.25 × TB scanned divided by TB scanned = $6.25 (definitionally).
BigQuery Editions. Slot-hours × $0.06 divided by TB scanned.
Databricks. DBUs × $0.55 divided by TB scanned (approximated via Delta Lake read bytes).

Question. Produce a single unified table showing $/TB scanned and $/query-hour per warehouse for last month, and interpret the shape of the workload that would flip the winner.

Input.

Warehouse	Monthly spend ($)	TB scanned	Query-hours	$/TB	$/query-hour
Snowflake	120,000	850	4,200	141.18	28.57
BigQuery on-demand	84,000	13,400	6,100	6.27	13.77
BigQuery Editions	14,400	3,200	2,900	4.50	4.97
Databricks	96,000	5,800	5,800	16.55	16.55

Code.

-- Cross-warehouse comparison — the unified $/TB and $/query-hour view
-- (Assumes 3 separate metering datasets pushed into a common warehouse or federated queries)

WITH snowflake_stats AS (
  SELECT
    'snowflake' AS engine,
    DATE_TRUNC('MONTH', start_time) AS month,
    SUM(credits_used) * 2.00                                             AS spend_usd,
    SUM(bytes_scanned) / POW(1024, 4)                                    AS tb_scanned,
    SUM(execution_time) / 3600000.0                                      AS query_hours
  FROM snowflake.account_usage.query_history
  WHERE start_time >= DATEADD('month', -1, DATE_TRUNC('MONTH', CURRENT_DATE))
  GROUP BY month
),
bigquery_stats AS (
  SELECT
    'bigquery_on_demand' AS engine,
    DATE_TRUNC(DATE(creation_time), MONTH) AS month,
    SUM(total_bytes_billed) / POW(1024, 4) * 6.25                        AS spend_usd,
    SUM(total_bytes_processed) / POW(1024, 4)                            AS tb_scanned,
    SUM(total_slot_ms) / 1000.0 / 3600.0                                 AS query_hours
  FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
  WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
    AND job_type = 'QUERY'
  GROUP BY month
),
databricks_stats AS (
  SELECT
    'databricks' AS engine,
    DATE_TRUNC('MONTH', usage_start_time) AS month,
    SUM(usage_quantity) * 0.55                                           AS spend_usd,
    NULL::DOUBLE                                                          AS tb_scanned,  -- approximate via Delta
    SUM(usage_quantity) / 0.30                                            AS query_hours  -- approx via Jobs Photon rate
  FROM system.billing.usage
  WHERE usage_start_time >= DATEADD('month', -1, DATE_TRUNC('MONTH', CURRENT_DATE))
    AND billing_origin_product IN ('JOBS_COMPUTE', 'ALL_PURPOSE_COMPUTE', 'SQL')
  GROUP BY month
),
unified AS (
  SELECT * FROM snowflake_stats
  UNION ALL SELECT * FROM bigquery_stats
  UNION ALL SELECT * FROM databricks_stats
)
SELECT
  engine,
  month,
  ROUND(spend_usd, 0)                                    AS spend_usd,
  ROUND(tb_scanned, 0)                                   AS tb_scanned,
  ROUND(query_hours, 0)                                  AS query_hours,
  ROUND(spend_usd / NULLIF(tb_scanned, 0), 2)            AS usd_per_tb_scanned,
  ROUND(spend_usd / NULLIF(query_hours, 0), 2)           AS usd_per_query_hour
FROM unified
ORDER BY month, engine;

Step-by-step explanation.

Each CTE reads the metering table for one warehouse and aggregates to (engine, month) with spend, TB scanned, and query-hours. Bytes-scanned is a first-class metric on Snowflake and BigQuery; Databricks needs Delta Lake read metrics as a proxy.
UNION into a single unified table. Now every row has the same shape (engine, month, spend, tb, hours) — trivial to feed into a Grafana panel or a CSV export for finance.
Divide spend by TB to get $/TB scanned. Snowflake often lands at $100–200/TB (rich but includes cluster overhead); BigQuery on-demand is $6.25 by definition; Databricks is workload-dependent.
Divide spend by query-hours to get $/query-hour. This is the user-facing latency dimension — it's a proxy for "how much do we pay for a heavy interactive query?"
The two metrics tell different stories: $/TB penalises scan-heavy workloads (Snowflake looks expensive); $/query-hour penalises long-running compute (Databricks looks expensive). Neither is the right metric — the winner depends on which axis matters more for your workload.

Output.

engine	spend	TB	hours	$/TB	$/query-hour
snowflake	120,000	850	4,200	141.18	28.57
bigquery_on_demand	84,000	13,400	6,100	6.27	13.77
databricks	96,000	5,800	5,800	16.55	16.55

Rule of thumb. Never compare warehouses on list price. Always normalise onto $/TB scanned and $/query-hour using your own workload's metering. The winners rotate based on workload shape — no single warehouse is "the cheapest" in the abstract.

Worked example — commit sizing across all three warehouses

Detailed explanation. A FinOps team runs all three warehouses and needs to size commits for each. The same P50 sizing rule applies: commit at the daily P50 usage × month; let overflow stay on-demand. Build the calculator template that produces commit tiers for each warehouse from its metering data.

Snowflake. P50 daily credits × 30 = monthly commit floor.
BigQuery. P50 hourly slot-hours × 720 (hours in a month) = slot commit.
Databricks. P50 daily DBUs × 30 = DBU commit floor.

Question. Build the SQL that computes P50, P90, and P99 usage for each warehouse and translates them into a commit tier recommendation.

Input.

Warehouse	Metric	Daily P50	Daily P90	Daily P99	Monthly commit @ P50
Snowflake	credits	1,800	2,400	2,900	54,000 credits
BigQuery	slot-hours	12,000	15,000	18,000	360,000 slot-hours
Databricks	DBUs	1,300	1,700	2,100	39,000 DBUs

Code.

-- Snowflake — commit sizing
WITH daily AS (
  SELECT
    DATE(start_time)               AS day,
    SUM(credits_used)              AS daily_credits
  FROM   snowflake.account_usage.warehouse_metering_history
  WHERE  start_time >= DATEADD('day', -90, CURRENT_TIMESTAMP())
  GROUP  BY day
)
SELECT
  'snowflake' AS engine,
  ROUND(PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY daily_credits), 0)   AS daily_p50,
  ROUND(PERCENTILE_CONT(0.90) WITHIN GROUP (ORDER BY daily_credits), 0)   AS daily_p90,
  ROUND(PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY daily_credits), 0)   AS daily_p99,
  ROUND(PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY daily_credits) * 30, 0) AS monthly_commit_p50
FROM daily;

-- BigQuery — commit sizing (slot-hours per day)
WITH daily_bq AS (
  SELECT
    DATE(creation_time)                       AS day,
    SUM(total_slot_ms) / 1000.0 / 3600.0      AS daily_slot_hours
  FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
  WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 90 DAY)
    AND job_type = 'QUERY'
  GROUP BY day
)
SELECT
  'bigquery' AS engine,
  ROUND(APPROX_QUANTILES(daily_slot_hours, 100)[OFFSET(50)], 0)  AS daily_p50,
  ROUND(APPROX_QUANTILES(daily_slot_hours, 100)[OFFSET(90)], 0)  AS daily_p90,
  ROUND(APPROX_QUANTILES(daily_slot_hours, 100)[OFFSET(99)], 0)  AS daily_p99,
  ROUND(APPROX_QUANTILES(daily_slot_hours, 100)[OFFSET(50)] * 30, 0) AS monthly_commit_p50
FROM daily_bq;

-- Databricks — commit sizing (DBUs per day)
WITH daily_dbx AS (
  SELECT
    DATE(usage_start_time)  AS day,
    SUM(usage_quantity)     AS daily_dbus
  FROM system.billing.usage
  WHERE usage_start_time >= date_sub(current_date(), 90)
    AND billing_origin_product IN ('JOBS_COMPUTE', 'ALL_PURPOSE_COMPUTE', 'SQL')
  GROUP BY day
)
SELECT
  'databricks' AS engine,
  ROUND(PERCENTILE(daily_dbus, 0.50), 0)     AS daily_p50,
  ROUND(PERCENTILE(daily_dbus, 0.90), 0)     AS daily_p90,
  ROUND(PERCENTILE(daily_dbus, 0.99), 0)     AS daily_p99,
  ROUND(PERCENTILE(daily_dbus, 0.50) * 30, 0) AS monthly_commit_p50
FROM daily_dbx;

Step-by-step explanation.

Each query computes the daily P50, P90, and P99 usage over the last 90 days. P50 is the "typical day"; P99 is the peak day. The commit tier is anchored at the P50.
The monthly commit at P50 is daily_p50 × 30. This is the "floor" — you use every unit of the commit at least half the days. Days above P50 pay some overflow at on-demand rates.
If the P99 is much higher than the P50 (e.g. 2× or more), the workload is bursty. A P50 commit is right but the overflow will be significant — you may want to buy a P75 commit and accept a small idle-commit risk.
If the P99 is close to the P50 (e.g. within 20%), the workload is steady. A P50 commit captures most of the value; overflow is negligible; consider a P75 commit for extra discount.
Never commit at the P99 or peak; you will always overpay because P99 is a rare day and every unit above the true average is straight loss.

Output.

engine	daily_p50	daily_p90	daily_p99	monthly commit @ P50
snowflake	1,800 credits	2,400 credits	2,900 credits	54,000 credits
bigquery	12,000 slot-hr	15,000 slot-hr	18,000 slot-hr	360,000 slot-hr
databricks	1,300 DBUs	1,700 DBUs	2,100 DBUs	39,000 DBUs

Rule of thumb. Commit at the daily P50 × 30 for every warehouse. If P99/P50 > 2, the workload is bursty — hold the P50 commit and accept overflow. If P99/P50 < 1.2, the workload is steady — bump the commit to P75.

Worked example — the cross-warehouse commit calculator template

Detailed explanation. A senior FinOps engineer wants a single spreadsheet-like calculator that takes daily P50 usage per warehouse and produces the recommended commit tier plus the expected savings versus on-demand. This is the artifact you paste into the quarterly FinOps review.

Inputs. Daily P50 per warehouse, on-demand rate, committed rate at each tier, projected monthly usage.
Outputs. Recommended commit tier, expected total spend, expected savings.
Sanity check. The projected usage must exceed the commit for the tier to be worth it.

Question. Implement the calculator as a Python function that takes the three warehouses' inputs and returns a table of recommended commits with savings estimates.

Input.

Warehouse	Daily P50	Monthly projection	Commit tiers
Snowflake	1,800 credits	54,000 credits	40k @ $1.60, 50k @ $1.55, 60k @ $1.50
BigQuery	12,000 slot-hr	360,000 slot-hr	300k @ $0.055, 400k @ $0.050, 500k @ $0.045
Databricks	1,300 DBUs	39,000 DBUs	30k @ $0.50, 40k @ $0.46, 50k @ $0.42

Code.

from dataclasses import dataclass
from typing import List

@dataclass
class CommitTier:
    name: str
    committed_units: float
    committed_rate: float
    ondemand_rate: float

def evaluate_commit(projected_units: float, tier: CommitTier) -> dict:
    committed_spend = min(projected_units, tier.committed_units) * tier.committed_rate
    unused_spend    = max(tier.committed_units - projected_units, 0) * tier.committed_rate
    overflow_spend  = max(projected_units - tier.committed_units, 0) * tier.ondemand_rate
    total           = committed_spend + unused_spend + overflow_spend
    ondemand_only   = projected_units * tier.ondemand_rate
    return {
        "tier":            tier.name,
        "committed":       round(committed_spend, 0),
        "unused":          round(unused_spend, 0),
        "overflow":        round(overflow_spend, 0),
        "total":           round(total, 0),
        "ondemand_only":   round(ondemand_only, 0),
        "savings":         round(ondemand_only - total, 0),
        "savings_pct":     round((ondemand_only - total) / ondemand_only * 100, 1),
    }

def recommend_commit(warehouse_name: str, projected: float, tiers: List[CommitTier]) -> dict:
    results = [evaluate_commit(projected, t) for t in tiers]
    results.sort(key=lambda r: r["total"])
    winner = results[0]
    print(f"\n=== {warehouse_name} — projected {projected:,.0f} units ===")
    for r in results:
        print(f"  {r['tier']:15s} total ${r['total']:>10,} savings ${r['savings']:>10,} ({r['savings_pct']}%)")
    print(f"  → RECOMMEND: {winner['tier']}")
    return winner

# Snowflake
sf_tiers = [
    CommitTier("40k @ 1.60", 40_000, 1.60, 2.00),
    CommitTier("50k @ 1.55", 50_000, 1.55, 2.00),
    CommitTier("60k @ 1.50", 60_000, 1.50, 2.00),
]
recommend_commit("Snowflake", 54_000, sf_tiers)

# BigQuery
bq_tiers = [
    CommitTier("300k @ 0.055", 300_000, 0.055, 0.06),
    CommitTier("400k @ 0.050", 400_000, 0.050, 0.06),
    CommitTier("500k @ 0.045", 500_000, 0.045, 0.06),
]
recommend_commit("BigQuery", 360_000, bq_tiers)

# Databricks
dbx_tiers = [
    CommitTier("30k @ 0.50", 30_000, 0.50, 0.55),
    CommitTier("40k @ 0.46", 40_000, 0.46, 0.55),
    CommitTier("50k @ 0.42", 50_000, 0.42, 0.55),
]
recommend_commit("Databricks", 39_000, dbx_tiers)

Step-by-step explanation.

Model each commit tier as (committed_units, committed_rate, ondemand_rate). For any projected usage, compute the committed spend, the unused-commit spend, and the overflow spend at on-demand rate.
The best tier minimises total = committed + unused + overflow. The savings vs on-demand-only is (projected × ondemand_rate) − total.
For Snowflake at 54k projected credits, the 50k tier wins — 50k × $1.55 + 4k × $2.00 = $85,500 vs 54k × $2.00 = $108,000. Savings = $22,500 (21%).
For BigQuery at 360k projected slot-hours, the 400k tier wins — 360k × $0.050 + 40k unused × $0.050 = $20,000 vs 360k × $0.060 = $21,600. Savings = $1,600 (7.4%). Note the smaller relative savings on BigQuery because Editions rates are already committed vs on-demand.
For Databricks at 39k projected DBUs, the 40k tier wins — 39k × $0.46 + 1k unused × $0.46 = $18,400 vs 39k × $0.55 = $21,450. Savings = $3,050 (14%).

Output.

Warehouse	Recommended tier	Projected units	Committed spend	Savings ($)	Savings (%)
Snowflake	50k @ $1.55	54,000	85,500	22,500	21%
BigQuery	400k @ $0.050	360,000	20,000	1,600	7.4%
Databricks	40k @ $0.46	39,000	18,400	3,050	14%

Rule of thumb. Run the commit calculator quarterly. The winning tier is almost always the one where the committed_units slightly exceed the projected usage — that captures the discount without leaving unused-commit on the table. Never sign a commit that exceeds P95 usage; the unused-commit loss will eat the discount.

Senior interview question on cross-warehouse FinOps

A senior interviewer might ask: "You run all three warehouses at your company. How would you decide which workload belongs on which warehouse, and how would you present that architecture to a CFO who doesn't understand credits versus slots versus DBUs?"

Solution Using workload-to-warehouse routing + normalised-cost dashboard

-- Workload-routing decision matrix — code-review-friendly heuristics
--   Snowflake  → interactive BI + tight-latency SQL + Snowflake-native features (streams, tasks)
--   BigQuery   → large ad-hoc scans + column-store analytics + Google Cloud ecosystem
--   Databricks → notebook-driven data science + Delta Lake governance + ML training

-- Normalised-cost dashboard — the CFO view
WITH monthly_cost AS (
  SELECT 'snowflake' AS engine, month,
         spend_usd, tb_scanned, query_hours,
         active_users
  FROM   pipecode_finops.snowflake_monthly_kpi
  UNION ALL SELECT 'bigquery', month, spend_usd, tb_scanned, query_hours, active_users FROM pipecode_finops.bigquery_monthly_kpi
  UNION ALL SELECT 'databricks', month, spend_usd, tb_scanned, query_hours, active_users FROM pipecode_finops.databricks_monthly_kpi
)
SELECT
  engine,
  month,
  ROUND(spend_usd, 0)                                    AS spend_usd,
  ROUND(spend_usd / NULLIF(tb_scanned, 0), 2)            AS usd_per_tb,
  ROUND(spend_usd / NULLIF(query_hours, 0), 2)           AS usd_per_query_hour,
  ROUND(spend_usd / NULLIF(active_users, 0), 2)          AS usd_per_active_user,
  -- Total normalised across all three
  ROUND(SUM(spend_usd) OVER (PARTITION BY month), 0)     AS platform_monthly_spend
FROM monthly_cost
ORDER BY month, engine;

Step-by-step trace.

Workload	Best warehouse	Reason
Interactive BI dashboards	Snowflake	Auto-suspend + warm cache = low latency
Ad-hoc large scans	BigQuery (on-demand)	Bytes-scanned model, 300-slot free autoscale
Nightly ETL (SQL-heavy)	Databricks Jobs + Photon + spot	Cheapest cost/hour for restart-safe SQL
ML training + notebooks	Databricks All-Purpose	Notebook UX, Delta Lake integration
Feature-store aggregations	BigQuery Editions	Predictable slot-hour budget
Compliance-sensitive analytics	Snowflake or BigQuery Enterprise Plus	Column masking + audit trails

The routing matrix is a code-review-friendly checklist: when a new workload proposal lands, the reviewer maps it to a warehouse via these six patterns. The CFO sees the three normalised metrics ($/TB, $/query-hour, $/active-user) side-by-side on a dashboard; comparisons become numerical, not tribal.

Output:

Layer	Snowflake	BigQuery	Databricks
Interactive BI	primary	fallback	none
Ad-hoc scans	fallback	primary	none
Nightly ETL	fallback	fallback	primary
ML training	none	none	primary
Streaming	primary (streams)	primary	primary (Structured Streaming)
Cost model	credits	slots (on-demand or Editions)	DBUs

Why this works — concept by concept:

Workload-first routing — the architecture is built around what each warehouse is best at. Snowflake's warm-cache interactive latency; BigQuery's on-demand scan pricing; Databricks' notebook + Delta Lake governance. Route the workload to the tool.
Normalised metrics for the CFO — $/TB scanned, $/query-hour, $/active-user turn "credits vs slots vs DBUs" into three comparable numbers. The CFO reads the dashboard; the engineering choice is defended by the numbers.
Idle-cost audit per warehouse — same discipline in three flavours: Snowflake auto-suspend, BigQuery reservation utilisation, Databricks All-Purpose autotermination. Every warehouse has an idle-cost knob; every FinOps review checks all three.
Commit-at-P50 across all three — the same sizing heuristic works for credits, slot-hours, and DBUs. Commit at the daily P50 × 30; let overflow stay on-demand.
Cost — three metering pipelines feeding one KPI table. The pipelines are boring engineering work; the KPI dashboard is the deliverable. The recurring saving on the actual bill compounds monthly.

SQL
Topic — sql
SQL cross-warehouse normalisation problems

Practice →

ETL
Topic — etl
ETL problems on cross-warehouse pipelines

Practice →

Cheat sheet — Warehouse FinOps recipes

Snowflake credit-cost query. SELECT DATE(start_time) AS day, warehouse_name, SUM(credits_used) AS credits, SUM(credits_used) * 2.00 AS spend_usd FROM snowflake.account_usage.warehouse_metering_history WHERE start_time >= DATEADD('day', -30, CURRENT_TIMESTAMP()) GROUP BY day, warehouse_name ORDER BY spend_usd DESC; — daily credit consumption and spend per warehouse; the first query in every Snowflake FinOps review.
Snowflake auto-suspend audit. Join snowflake.account_usage.warehouses (for the auto_suspend setting) against warehouse_metering_history (for billed hours) and query_history (for query hours); the delta billed_hours - query_hours is the idle-time surface. Rank descending and fix the top offenders first. Recommended target: AUTO_SUSPEND = 60.
Snowflake per-team chargeback. Use QUERY_TAG (set at session open via a driver hook) parsed with REGEXP_SUBSTR(query_tag, 'team=([^,]+)', 1, 1, 'e', 1). Prorate credits by execution_time / warehouse_seconds. Enforce untagged spend as a bug; target < 5% untagged after 6 months of enforcement.
BigQuery slot-utilisation query. SELECT TIMESTAMP_TRUNC(creation_time, HOUR) AS hour, SUM(total_slot_ms)/1000.0 AS slot_seconds, SUM(total_slot_ms)/1000.0 / (500 * 3600.0) AS utilisation_pct FROM \region-us.INFORMATION_SCHEMA.JOBS_BY_PROJECT WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY) AND job_type = 'QUERY' GROUP BY hour ORDER BY hour; — hourly utilisation against a 500-slot baseline. Target 60–80%; below 40% means cut baseline, above 90% means raise max.
BigQuery on-demand vs Editions break-even. Cross the workload's slot-hour demand against the reservation's baseline × 730 hours. If slot_hours_month × $0.06 > tb_scanned_month × $6.25, on-demand wins. If sustained slot demand exceeds ~200k slot-hours/month for compute-heavy workloads, migrate to Editions.
BigQuery label-based chargeback. Extract labels from JOBS_BY_PROJECT via (SELECT value FROM UNNEST(labels) WHERE key = 'team'). Roll up per (team, month) with slot-hours and TB-scanned; multiply by the appropriate rate for the pricing model. Ship label enforcement at the driver layer.
Databricks DBU-consumption query. SELECT DATE(usage_start_time) AS day, usage_metadata.cluster_id, sku_name, SUM(usage_quantity) AS dbus, SUM(usage_quantity) * 0.55 AS spend_usd FROM system.billing.usage WHERE usage_start_time >= date_sub(current_date(), 30) GROUP BY day, cluster_id, sku_name ORDER BY spend_usd DESC LIMIT 20; — daily DBU spend by cluster; the first query in every Databricks FinOps review.
Databricks cluster policy — the guardrails. Enforce autotermination_minutes in [10, 30], runtime_engine = PHOTON, required custom_tags.team + pipeline + env, aws_attributes.availability = SPOT_WITH_FALLBACK, first_on_demand = 1 for the driver. Apply the policy via Terraform; retire All-Purpose clusters that violate it.
Databricks Jobs vs All-Purpose migration. All-Purpose DBU rate is ~3–4× the Jobs rate. Any scheduled workload discovered in system.workflow.job_run_timeline running on All-Purpose is a migration target. Rewrite the workflow to use existing_cluster_id → job_cluster and retire the All-Purpose binding.
Cross-warehouse cost-parity formula. Normalise spend to $/TB scanned and $/query-hour for each warehouse using its metering table. Snowflake: SUM(credits × $2) / SUM(bytes_scanned / 1024^4). BigQuery: on-demand is $6.25/TB by definition; Editions is slot_hours × $0.06 / TB. Databricks: DBUs × $0.55 / TB_read_from_Delta. Compare on your actual workload; never on list prices.
Commit calculator template. evaluate_commit(projected_units, tier) = min(projected, committed) × commit_rate + max(committed - projected, 0) × commit_rate + max(projected - committed, 0) × ondemand_rate. Pick the tier with minimum total. Anchor at daily P50 × 30; never commit beyond P95.
The idle-cost audit ladder. Snowflake AUTO_SUSPEND > 300s, BigQuery Editions utilisation < 40%, Databricks All-Purpose autotermination_minutes > 30. Every warehouse has an idle-cost lever; the audit is boring and reliable. Run monthly; fix top-3 offenders; measure delta.
Per-warehouse attribution taxonomy. Snowflake QUERY_TAG, BigQuery labels, Databricks custom_tags. All three converge on the pattern team = X, pipeline = Y, env = prod. Enforce at driver layer; target 95%+ tag coverage; treat untagged spend as unallocated overhead billed to a shared cost centre.
The one-sentence CFO framing. "Credits are Snowflake warehouse-hours, slot-hours are BigQuery capacity-seconds, DBUs are Databricks normalised compute — we normalise all three to $/TB scanned and $/query-hour on our actual workload; the winner per workload is on the dashboard."

Frequently asked questions

What is a Snowflake credit and how is it billed?

A snowflake credits is Snowflake's atomic billing unit: one credit equals one hour of running an X-Small warehouse. The credit consumption doubles with every T-shirt size increase — a Small warehouse burns 2 credits/hour, a Medium 4, a Large 8, an X-Large 16, and a 6X-Large 512 credits/hour. Billing is per-second with a 60-second minimum on warehouse start, so a warehouse that runs for 3 seconds still costs 60 seconds of credits at its size-rate. The list rate is roughly $2.00 per credit (varies by cloud + region + contract), and capacity commitments discount that by 10–40% depending on tier and term. Serverless credits (for Snowpipe, tasks, materialised views, search optimization) are priced differently — usually as a per-compute-second rate rather than a warehouse-hour rate. The two biggest levers on the credit bill are (1) AUTO_SUSPEND, which should be 60 seconds not the default 600, and (2) right-sizing the warehouse to the workload rather than defaulting to Medium/Large.

BigQuery on-demand vs Editions — when do I pick each?

On-demand bills you $6.25 per TB scanned with a free autoscale up to 2000 slots per query — great for ad-hoc analysis, low-frequency workloads, and any team where the slot-hour demand is spiky and unpredictable. Editions (Standard / Enterprise / Enterprise Plus) bill you a flat $/slot-hour under reservations with autoscale between a baseline and a max — great for sustained analytics workloads where you want predictable spend and access to Enterprise features (BI Engine, column-level ACL, multi-region governance). The break-even is roughly at 200k slot-hours per month for compute-heavy workloads: below that on-demand is almost always cheaper; above that Editions pays for itself. The right approach is to measure your slot-hour demand using INFORMATION_SCHEMA.JOBS_BY_PROJECT.total_slot_ms for 90 days, then compute the two costs at your actual workload — never decide from list prices. Editions Enterprise Plus also unlocks CMEK, multi-region, and advanced governance that some regulated industries require regardless of cost.

What is a Databricks DBU and how is the rate calculated?

A databricks dbu — Databricks Unit — is a normalised measure of processing capability consumed per hour, and the DBU rate depends on cluster type × Photon × infra. Jobs Compute (scheduled ETL) bills ~0.15 DBU/hour/node; SQL warehouses ~0.22–0.55/hour/node; All-Purpose (interactive notebooks) ~0.55/hour/node — the All-Purpose SKU is roughly 3–4× more expensive than Jobs Compute, which is why running scheduled work on All-Purpose is the #1 Databricks spend leak. Enabling Photon roughly doubles the DBU rate but roughly halves the wall clock on SQL-heavy workloads — the DBU-cost effect washes out but the VM cost halves, so Photon almost always wins financially. The underlying VM cost is billed separately on top of DBUs; spot instances (with SPOT_WITH_FALLBACK) cut VM cost by 60–90%. The full bill is DBUs × $/DBU + VM-hours × $/VM-hour; the DBU cost is often a small slice of the total — never optimise DBUs in isolation.

How do I compare Snowflake vs BigQuery vs Databricks cost apples-to-apples?

Normalise all three warehouses onto $/TB scanned and $/query-hour using their metering tables against your workload, not list prices. Snowflake: SUM(credits_used × $2) / SUM(bytes_scanned / 1024^4) from snowflake.account_usage.query_history. BigQuery: SUM(total_bytes_billed × $6.25) / SUM(total_bytes_processed / 1024^4) from INFORMATION_SCHEMA.JOBS_BY_PROJECT for on-demand; slot_hours × $0.06 / TB for Editions. Databricks: SUM(usage_quantity × $0.55) / SUM(bytes_read_from_delta) from system.billing.usage. Once the three numbers are on the same axis, you can defend the "which is cheapest" conversation with a dashboard, not with anecdote. The reality is that the winner rotates by workload shape: Snowflake wins interactive BI (warm cache + auto-suspend); BigQuery wins ad-hoc large scans (bytes-scanned + free autoscale); Databricks wins compute-heavy ETL (Photon + Delta Lake + spot VMs). Route workloads to the warehouse that fits their shape.

Do I need a dedicated FinOps team to manage `data warehouse cost`?

For any company spending more than roughly $500k/year on data warehouses, yes — but the FinOps role is best embedded in the data platform engineering team, not siloed in finance. The FinOps engineer's day-to-day work is queries against metering tables (Snowflake account_usage, BigQuery INFORMATION_SCHEMA, Databricks system.billing.usage), maintaining the attribution taxonomy (QUERY_TAG / labels / custom_tags), sizing quarterly commits, and reviewing high-cost workloads at code review time. The finance side signs the commit contracts and reconciles the monthly invoice; engineering owns everything upstream. Below $500k/year, the FinOps discipline can be part of a senior data engineer's remit — one day per week on cost, backed by a few standing queries and a Grafana dashboard. What you can not do is treat cost as "finance's problem" — the levers all live in engineering (auto-suspend, cluster policies, workload routing) and only engineering can pull them.

What is the single biggest warehouse cost mistake senior data engineers make?

Running scheduled work on the wrong warehouse type or with the wrong idle-cost setting. On Snowflake: leaving AUTO_SUSPEND = 600 (the default) instead of 60. On BigQuery: buying an Editions reservation that ends up 60% idle during off-hours because it was sized for peak. On Databricks: running scheduled ETL on All-Purpose clusters (3–4× the Jobs rate) or leaving autotermination_minutes at the console default. The second-biggest mistake is not enforcing attribution at the driver layer — without QUERY_TAG on Snowflake, labels on BigQuery, or custom_tags on Databricks, you can't produce a per-team chargeback report and cost stays "somebody else's problem." The third-biggest mistake is committing beyond P95 usage — every unit of unused commit is money you paid for and threw away. The pattern behind all three: cost is an engineering property; the levers are code-review-visible; the discipline is boring and reliable if you build the audits and let them run monthly.

Practice on PipeCode

Drill the SQL practice library → for the cost-attribution, metering-table, and query-tag windowing problems senior FinOps interviewers love.
Rehearse on the ETL practice library → for the throughput-sizing and warehouse-routing problems that motivate the FinOps discipline in the first place.
Sharpen the tuning axis with the optimization practice library → for the auto-suspend audit, slot-utilisation, and DBU-rate break-even problems.
Stack the prerequisites against PipeCode's broader 450+ data-engineering catalogue to anchor the credit-slot-DBU intuition against real graded inputs.

Lock in warehouse FinOps muscle memory

Warehouse docs explain the pricing pages. PipeCode drills explain the decision — when auto-suspend actually matters, when BigQuery Editions pays for itself, when All-Purpose clusters silently drain the bill. Pipecode.ai is Leetcode for Data Engineering — pattern-first practice tuned for the production trade-offs senior data engineers actually face.

Practice SQL problems →
Practice optimization problems →

1. Why warehouse FinOps became a first-class engineering discipline

warehouse finops is now the largest infra line in a modern data org — three cost models, three mental models

Worked example — sizing a Snowflake capacity commit

Worked example — BigQuery on-demand vs Editions break-even

Worked example — Databricks DBU rate math for a mixed cluster

Senior interview question on warehouse cost models

Solution Using unit-first framing + workload-shape analysis

2. Snowflake credits — warehouses, sizes, idle time

snowflake credits scale linearly with T-shirt size and evaporate the moment auto-suspend fires — the two biggest levers of the Snowflake bill

Worked example — cost of one 4-hour ELT on a Medium warehouse

Worked example — auto-suspend audit query

Worked example — query-tag attribution for chargeback

Senior interview question on Snowflake FinOps

Solution Using a 30-day Snowflake FinOps hardening plan

3. BigQuery slots — on-demand vs Editions + reservations

bigquery slots are the unit of computational capacity; slot-seconds are the billing atom on Editions and bytes-scanned is the billing atom on on-demand

Worked example — same ELT on 500 baseline + 2000 max slots

Worked example — slot utilisation audit query

Worked example — label-based chargeback with JOBS_BY_PROJECT

Senior interview question on BigQuery FinOps

Solution Using workload-shape analysis + a phased migration path

4. Databricks DBUs — cluster type × compute

databricks dbu is a normalised compute unit whose rate depends on cluster type × Photon × infra — three orthogonal pricing dimensions on one bill

Worked example — cost of an ELT on Jobs cluster with Photon

Worked example — DBU consumption audit query

Worked example — Photon break-even analysis

Senior interview question on Databricks FinOps

Solution Using cluster policies + Jobs-cluster migration + tag enforcement

5. Cross-warehouse comparison + FinOps patterns

Normalising credits, slot-hours, and DBU-hours onto a single $/TB scanned axis is the only apples-to-apples cost comparison — and it settles most warehouse arguments

Worked example — normalising three warehouses onto a single $/TB axis

Worked example — commit sizing across all three warehouses

Worked example — the cross-warehouse commit calculator template

Senior interview question on cross-warehouse FinOps

Solution Using workload-to-warehouse routing + normalised-cost dashboard

Cheat sheet — Warehouse FinOps recipes

Frequently asked questions

What is a Snowflake credit and how is it billed?

BigQuery on-demand vs Editions — when do I pick each?

What is a Databricks DBU and how is the rate calculated?

How do I compare Snowflake vs BigQuery vs Databricks cost apples-to-apples?

Do I need a dedicated FinOps team to manage data warehouse cost?

What is the single biggest warehouse cost mistake senior data engineers make?

Practice on PipeCode

Lock in warehouse FinOps muscle memory

`warehouse finops` is now the largest infra line in a modern data org — three cost models, three mental models

`snowflake credits` scale linearly with T-shirt size and evaporate the moment auto-suspend fires — the two biggest levers of the Snowflake bill

`bigquery slots` are the unit of computational capacity; slot-seconds are the billing atom on Editions and bytes-scanned is the billing atom on on-demand

`databricks dbu` is a normalised compute unit whose rate depends on cluster type × Photon × infra — three orthogonal pricing dimensions on one bill

Normalising credits, slot-hours, and DBU-hours onto a single `$/TB scanned` axis is the only apples-to-apples cost comparison — and it settles most warehouse arguments

Do I need a dedicated FinOps team to manage `data warehouse cost`?