Data Warehouse Cost Allocation by Team, Product, Workload

Your exec asks why last month’s warehouse bill spiked and which teams drove it. You can’t answer with a single account-wide number. This guide shows a pragmatic path to data warehouse cost allocation: tag queries at the source, map users and roles to cost centers, split spend by warehouses or reservations, attach dbt metadata, attribute BI service accounts, and handle shared pools and idle compute. You’ll end with a monthly ledger per team, product, and workload you can defend in a review, and a clear plan for showback or chargeback.

From one bill to a ledger: the allocation model

Start by writing a cost model before writing SQL. Define cost centers (teams, products), cost drivers (queries, slots, warehouses), and the rules to allocate shared pools. Then build a daily ledger that rolls up by month. Direct costs come from traceable usage signals; shared costs come from a rule you can explain later without backtracking.

Formula: allocate shared pools predictably

Allocation for a shared pool S using driver units D is:

allocated_amount(team) = direct(team)
                        + ( D(team) / SUM(D(all)) ) * S

Choose D you can measure: number of executed queries, slot-seconds, credits, or dashboard runs. Document it next to the ledger.

The three main types of cost allocation

• Direct: fully traceable usage (e.g., a dedicated warehouse for Finance).
• Step-down: allocate a platform team’s shared pool to downstream teams first, then allocate the remainder.
• Activity-based: allocate by a driver like query runtime or slot-seconds.

Quick note on the physical warehouse question

“How much would a 5000 sq ft warehouse cost?” is a facilities question. Here we cover cloud analytics systems, not buildings.

Platform signals and what they really mean

Different engines expose different usage signals and pricing model quirks. Don’t pretend they’re identical—treat each as its own cost accounting source of truth.

Platform	Usage signal	Compute billing unit	Tag/Label mechanism	Common pitfalls
Snowflake	QUERY_HISTORY, WAREHOUSE_METERING_HISTORY	Credits per warehouse size & time	QUERY_TAG, USER, ROLE, WAREHOUSE	BI tools overwrite tags; idle warehouses accrue; cache hides drivers
BigQuery	INFORMATION_SCHEMA.JOBS, Reservations	Bytes data scanned or slot-seconds	Job labels, project/dataset, service accounts	Unlabeled jobs; shared service accts; idle slots under flat-rate
Amazon Redshift (incl. Amazon Redshift Serverless)	System tables, usage limits, tags	RA3 nodes, RPU seconds (serverless)	Resource tags, namespaces	Mixed classic vs RA3; shared clusters blur teams
Databricks	Cluster/job runs, workspace tags	DBU + cloud infra	Cluster/job tags, users, repos	Ephemeral job clusters unlabeled; autoscaling headroom

Note the pricing model differences (consumption-based vs reservations) drive different cost drivers and TCO. Your total cost of ownership will include storage costs, orchestration, and BI as well.

Snowflake: implementation pattern that survives prod

On Snowflake Inc.’s engine, you allocate by warehouse, then refine by query. Pull credits and attach tags, roles, and users. Example daily rollup:

-- Credits by warehouse-day
SELECT
  wm.WAREHOUSE_NAME,
  DATE_TRUNC('day', wm.START_TIME) AS day,
  SUM(wm.CREDITS_USED_COMPUTE) AS credits
FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY wm
WHERE wm.START_TIME >= DATEADD('day', -31, CURRENT_TIMESTAMP())
GROUP BY 1,2;

-- Join to queries tagged by team/product
SELECT
  DATE_TRUNC('day', q.START_TIME) AS day,
  q.QUERY_TAG,
  q.USER_NAME,
  q.ROLE_NAME,
  q.WAREHOUSE_NAME,
  COUNT(*) AS queries,
  SUM(q.EXECUTION_TIME)/1000.0 AS seconds
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY q
WHERE q.START_TIME >= DATEADD('day', -31, CURRENT_TIMESTAMP())
GROUP BY 1,2,3,4,5;

Propagate metadata from dbt so every model run carries a stable tag:

-- dbt_project.yml
models:
  marts:
    +query_tag: "team={{ var('team') }}|product={{ var('product') }}|job=dbt"

Failure modes you’ll hit in week two: the BI tool sets its own QUERY_TAG; multi-statement tasks split attribution; a warehouse left running idles credits; result cache makes "+0 credits" queries still look busy. Mitigate by enforcing tags in a session hook and isolating BI on its own warehouse. When a 40M-row orders table rebuild runs on an X-Small warehouse, the long runtime inflates idle risk elsewhere; don’t share that warehouse.

When optimization becomes the lever, see our deeper guides: Snowflake Cost Optimization: Where the Credits Go, Snowflake Performance Tuning, and SQL Query Optimization for Columnar Warehouses.

BigQuery: implementation pattern that sticks

For on-demand, the clean driver is bytes data scanned per job; for flat-rate, use slot-seconds from Reservations and Assignments. Always label jobs at submit time and backfill where unlabeled.

-- Jobs with labels and bytes scanned
SELECT
  DATE_TRUNC(j.creation_time, DAY) AS day,
  j.labels[SAFE_OFFSET(0)].key AS label_key,
  j.labels[SAFE_OFFSET(0)].value AS label_val,
  j.user_email,
  j.total_bytes_processed
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_ORGANIZATION j
WHERE j.creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 31 DAY);

Map Reservations to teams via Assignments; idle slots become a shared pool. For storage, use TABLE_STORAGE to attribute long-lived datasets. Common pitfalls: Looker/BI schedules run as a shared service account with no labels; scheduled queries inherit project defaults only; denormalized dashboards trigger repeated full scans without partitions or clustering. Treat unlabeled jobs as “platform” until you can enforce labels in orchestration. Keep the word "+slots" separate from user counts—300 engineers can be quiet while one data scientist runs a monster join.

Under flat-rate, record slot-seconds per assignment daily. Under on-demand, group by labels and sum total_bytes_processed as your driver, then apply your allocation rule.

Tagging via dbt, Airflow, and BI

Most spend originates from transformations, so make dbt the source of truth for attribution. Set tags/labels from vars, pass through orchestration, and verify in logs.

-- dbt_project.yml
vars:
  team: "finance"
  product: "revenue"
models:
  core:
    +query_tag: "team={{ var('team') }}|product={{ var('product') }}|area=core"

-- macros/set_query_tag.sql (Snowflake)
{% macro set_query_tag() %}
  {% if target.type == 'snowflake' %}
    alter session set query_tag='team={{ var(''team'') }}|job=dbt';
  {% endif %}
{% endmacro %}

-- dbt_project.yml hook
on-run-start:
  - {{ set_query_tag() }}

Orchestrators should enforce labels too:

# Airflow pseudo-code
SnowflakeOperator(
  sql="{{ ref('model') }}",
  snowflake_conn_id="snowflake",
  session_parameters={
    "QUERY_TAG": f"team={ team }|product={ product }|dag={ dag_id }"
  },
)

Lock BI tools to a dedicated warehouse or reservation with a fixed tag, and define exposures/owners in dbt so you can link models to dashboards. If you suspect model runtime is your driver, a dbt repo performance audit is usually the fastest win. For dependency-aware orchestration and speed-ups, see dbt State-Aware Orchestration and dbt Model Optimization.

Service accounts, roles, and mapping to teams

Most dashboards run under service accounts. You must translate them to real owners or products. Build a mapping table fed by BI admin exports and code repos.

Signal	Where	How it helps attribution	Gotcha
CLIENT_APPLICATION_NAME	Query logs	Identify Looker/Mode/Power BI	Some drivers mask the name
Service account email	Job metadata	Map to team via admin export	Often a shared "prod@..."
Dashboard slug/ID in SQL	BI SQL comments	Back-map to owner	Not always present
Role/warehouse	Warehouse/job	Dedicated infra per BI app	Shared role hides users

Enforce a role per BI workspace, and set a default tag. For Slack-native agents and internal AI platforms, set a unique tag and route through an isolated warehouse so you can throttle separately. Your mapping should handle a "platform" bucket for admin queries and unowned assets until owners are assigned.

Shared pools, idle compute, and reservations

Direct usage is the easy part. The thorny part is shared pools and idle compute. On credit-based engines, a warehouse that’s left running accrues credits even with no queries—treat those minutes as a pool to allocate. On reservation models, slot-seconds with no work are idle; decide who pays.

-- Estimate Snowflake idle window share per day per warehouse
WITH q AS (
  SELECT WAREHOUSE_NAME, DATE_TRUNC('hour', START_TIME) h, COUNT(*) c
  FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
  WHERE START_TIME >= DATEADD('day', -7, CURRENT_TIMESTAMP())
  GROUP BY 1,2
), w AS (
  SELECT WAREHOUSE_NAME, DATE_TRUNC('hour', START_TIME) h, SUM(CREDITS_USED_COMPUTE) cr
  FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY
  WHERE START_TIME >= DATEADD('day', -7, CURRENT_TIMESTAMP())
  GROUP BY 1,2
)
SELECT w.WAREHOUSE_NAME, h, cr, COALESCE(c,0) AS queries
FROM w LEFT JOIN q USING (WAREHOUSE_NAME, h);

Classify workloads as steady, bursty, or seasonal. Steady BI refreshes fit small always-on reservations; bursty ELT prefers autosuspend warehouses; seasonal ML training may warrant temporary capacity. If you’re on AWS with serverless computing options, note that enabling serverless features shifts control from schedulers to the engine; budget a platform “tax” bucket. Redshift Serverless tagging helps, but document who funds the baseline. For anything flat-rate, expose the idle bucket explicitly in the ledger; it sharpens conversations without finger-pointing.

Showback vs chargeback: rolling it out with a dashboard

Start with showback: a monthly dashboard that breaks down warehouse costs by team, product, and workload, and highlights changes. After two or three stable cycles, move to chargeback with finance. This is different from generic cloud cost management: the useful unit here is workload-level usage (queries, jobs), not VM-hours.

• Define cost centers and owners; agree on the driver per shared pool.
• Publish a daily ledger; annotate anomalies in-line.
• Review in a 30-minute ops session; file fixes as tickets.
• When stable, connect to accounting for chargeback.

Need an end-to-end setup with source integration and a review-ready dashboard? See our Business Health Reporting service. For a deeper audit of people, process, and architecture before you allocate costs, review our Analytics Engineering Audit. We also cover pricing model tradeoffs and TCO impacts in related posts.

Optimization opportunities your ledger will surface

Allocation isn’t just fairness; it’s how you find fixes. Typical top offenders:

• An incremental model that rewrites too much. Switch to a state-aware strategy or tighter predicates.
• Dashboards scanning entire fact tables. Add partitions on date and cluster by the frequent filter key.
• Massive cross-joins from convenience views. Materialize as tables with the smallest grain you need.
• Wide SELECT * queries. Project only required columns.

-- Example: narrow a rebuild window
{{
  config(materialized='incremental', unique_key='order_id')
}}
WITH base AS (
  SELECT * FROM {{ source('app', 'orders') }}
  {% if is_incremental() %}
    WHERE updated_at > (SELECT COALESCE(MAX(updated_at), '1900-01-01') FROM {{ this }})
  {% endif %}
)
SELECT ... FROM base;

Attribution will show whether compute costs come from ELT, ad hoc, or BI. A data engineer and a data scientist can both be “heavy” in different ways; allocate by the same rule, then help each optimize. If you need help turning the fix list into changed runtimes and fewer credits, we can step in—see dbt Repo Performance and Snowflake Cost Optimization. This is where you tangibly manage costs without slowing teams.

---

Next step: enforce tags on all jobs, build a daily ledger, and pilot showback with one team. If you want a partner who’s implemented this at scale, start a project with Vertex.