Designing and Automating Data Warehouse Unit Economics Dashboards: Attributing Storage, Compute, and Query Costs to Teams, Models, and Business Domains in Snowflake and BigQuery

Introduction

Your Snowflake bill arrives and it's $47,000 for the month — up 23% from last month. You open the invoice and it tells you exactly nothing useful: a single line item for compute credits, another for storage, maybe a vague breakdown by warehouse name. The Finance team wants to know which business unit is responsible. Your VP of Engineering wants to know which dbt models are burning the most compute. Your platform team wants to know whether the new marketing attribution pipeline was worth it. You have none of those answers.

This is the unit economics problem in data warehousing, and it's more common than you'd think. As organizations scale their modern data stacks, costs compound quickly — more teams, more models, more dashboards, more exploratory queries. Without a systematic approach to cost attribution, you're flying blind. You can't make intelligent trade-offs, you can't hold teams accountable, and you certainly can't justify your infrastructure budget to leadership.

By the end of this lesson, you'll know how to extract, model, and visualize cost data from both Snowflake and BigQuery at a granular enough level to answer real questions: which dbt model costs $3.20 per run, which analyst query pattern is burning $800/week, and which business domain's storage is growing 15% month-over-month. You'll build a cost attribution pipeline that runs automatically, populates a unit economics dashboard, and gives you the observability to actually manage your data warehouse spend.

What you'll learn:

• How to extract query, compute, and storage cost metadata from Snowflake's ACCOUNT_USAGE schema and BigQuery's INFORMATION_SCHEMA views
• How to attribute costs to teams, dbt models, and business domains using query tagging and metadata enrichment
• How to build a normalized cost model that works across both platforms
• How to automate cost ingestion with dbt and schedule it reliably
• How to design a unit economics dashboard with actionable metrics, not just raw numbers

---

Prerequisites

You should be comfortable with:

• Writing analytical SQL — window functions, CTEs, and aggregations
• Basic dbt project structure; if you need a refresher, the Introduction to dbt article covers the essentials
• Familiarity with either Snowflake or BigQuery as a query execution environment
• A working knowledge of warehouse cost models (credits, slots, bytes billed)

---

Understanding What You're Actually Measuring

Before you write a single query, you need to understand what "cost" means in each platform. This matters because the billing model shapes what data is available and how you should model it.

Snowflake uses a credit-based model. Compute is charged by the second against virtual warehouse size — an X-Small warehouse consumes 1 credit/hour, an XL consumes 16 credits/hour. Credits are priced by edition and cloud region (typically $2–$4 per credit). Storage is billed separately, based on the daily average of compressed bytes stored. There's no per-query billing by default; you pay for warehouse uptime, and queries share that cost.

BigQuery uses a bytes-scanned model for on-demand pricing ($6.25 per TB scanned, with a 10MB minimum per query), or a slot-based reservation model for enterprise usage. Storage is billed per GB per month, with a distinction between active storage (modified in the last 90 days) and long-term storage (cheaper, auto-applied). Every query has a deterministic bytes-billed value, which makes cost attribution more natural.

Key insight: Snowflake cost attribution requires you to prorate warehouse costs across queries by time or execution share, because multiple queries can run simultaneously. BigQuery cost attribution is cleaner — each query has a direct bytes_billed value you can price immediately. Both approaches have nuances we'll cover.

The implication: your cost model needs to handle both paradigms, especially if your organization uses both platforms. We'll build a normalized schema that abstracts these differences away.

---

Step 1: Extracting Cost Metadata from Snowflake

Snowflake's SNOWFLAKE.ACCOUNT_USAGE schema is your primary data source. The key views are:

• QUERY_HISTORY — every query executed, with execution time, warehouse, bytes scanned, credits used
• WAREHOUSE_METERING_HISTORY — credit consumption per warehouse per hour
• DATABASE_STORAGE_USAGE_HISTORY — bytes of storage per database per day
• METERING_DAILY_HISTORY — aggregate daily credits

The ACCOUNT_USAGE views have a latency of 45 minutes to 3 hours. For a cost attribution pipeline that runs daily, this is fine. For real-time alerts, you'd use INFORMATION_SCHEMA instead, which has lower latency but limited history (7 days).

Here's the foundational query to extract query-level cost data with attribution:

-- snowflake_query_costs.sql
-- Extracts per-query costs attributed to warehouse, role, user, and query tag

WITH warehouse_credits AS (
  SELECT
    warehouse_name,
    DATE_TRUNC('hour', start_time) AS credit_hour,
    SUM(credits_used) AS credits_used,
    -- credits_per_hour helps us prorate costs within the hour
    SUM(credits_used) AS total_credits_in_hour
  FROM snowflake.account_usage.warehouse_metering_history
  WHERE start_time >= DATEADD('day', -90, CURRENT_TIMESTAMP())
  GROUP BY 1, 2
),

query_execution AS (
  SELECT
    query_id,
    query_text,
    database_name,
    schema_name,
    query_type,
    user_name,
    role_name,
    warehouse_name,
    warehouse_size,
    execution_status,
    start_time,
    end_time,
    total_elapsed_time,           -- milliseconds
    bytes_scanned,
    bytes_written,
    rows_produced,
    -- Extract dbt model name from query tag (set via dbt's query_comment)
    TRY_PARSE_JSON(query_tag):dbt_metadata:node_name::STRING AS dbt_model_name,
    TRY_PARSE_JSON(query_tag):dbt_metadata:node_id::STRING   AS dbt_node_id,
    TRY_PARSE_JSON(query_tag):team::STRING                   AS team_tag,
    TRY_PARSE_JSON(query_tag):domain::STRING                 AS domain_tag,
    DATE_TRUNC('hour', start_time) AS query_hour
  FROM snowflake.account_usage.query_history
  WHERE
    execution_status = 'SUCCESS'
    AND start_time >= DATEADD('day', -90, CURRENT_TIMESTAMP())
    AND warehouse_name IS NOT NULL
),

-- Prorate warehouse credits to each query by its share of execution time in that hour
query_with_hourly_execution AS (
  SELECT
    qe.*,
    -- Total milliseconds of queries in that warehouse-hour
    SUM(qe.total_elapsed_time) OVER (
      PARTITION BY qe.warehouse_name, qe.query_hour
    ) AS total_elapsed_ms_in_hour,
    wc.total_credits_in_hour
  FROM query_execution qe
  JOIN warehouse_credits wc
    ON qe.warehouse_name = wc.warehouse_name
    AND qe.query_hour = wc.credit_hour
),

query_costs AS (
  SELECT
    query_id,
    query_text,
    database_name,
    schema_name,
    query_type,
    user_name,
    role_name,
    warehouse_name,
    warehouse_size,
    execution_status,
    start_time,
    end_time,
    total_elapsed_time,
    bytes_scanned,
    bytes_written,
    rows_produced,
    dbt_model_name,
    dbt_node_id,
    COALESCE(team_tag, role_name) AS attributed_team,
    COALESCE(domain_tag, database_name) AS attributed_domain,
    -- Prorate credits
    CASE
      WHEN total_elapsed_ms_in_hour > 0
      THEN (total_elapsed_time / total_elapsed_ms_in_hour) * total_credits_in_hour
      ELSE 0
    END AS attributed_credits,
    -- Convert to USD (parameterize your credit price)
    CASE
      WHEN total_elapsed_ms_in_hour > 0
      THEN (total_elapsed_time / total_elapsed_ms_in_hour) * total_credits_in_hour * 3.00
      ELSE 0
    END AS attributed_cost_usd
  FROM query_with_hourly_execution
)

SELECT * FROM query_costs
ORDER BY attributed_cost_usd DESC;

Warning: The proration approach above distributes warehouse costs by execution time share. This is reasonable but not perfect — a query that ran for 30 seconds but scanned 500GB "caused" more warehouse spin-up cost than one that ran for 30 seconds scanning 100KB. For most attribution purposes, time-based proration is the right default. If you need byte-based proration, replace total_elapsed_time with bytes_scanned in the share calculation.

---

Step 2: Extracting Cost Metadata from BigQuery

BigQuery's metadata lives in INFORMATION_SCHEMA views inside your project. For cross-project cost analysis, you'll use the region-* variants that span projects within a region. The key views are:

• INFORMATION_SCHEMA.JOBS_BY_ORGANIZATION — all jobs across projects (requires org-level permissions)
• INFORMATION_SCHEMA.JOBS — jobs within a specific project
• INFORMATION_SCHEMA.TABLE_STORAGE — storage stats per table

-- bigquery_query_costs.sql
-- Run this in BigQuery's region-level INFORMATION_SCHEMA

WITH raw_jobs AS (
  SELECT
    job_id,
    project_id,
    user_email,
    job_type,
    statement_type,
    creation_time,
    start_time,
    end_time,
    -- bytes_billed is the billable amount, not bytes_processed
    total_bytes_billed,
    total_bytes_processed,
    total_slot_ms,
    error_result,
    query,
    labels,
    -- BigQuery labels are key-value maps set at query or job level
    (SELECT value FROM UNNEST(labels) WHERE key = 'dbt_node_id')    AS dbt_node_id,
    (SELECT value FROM UNNEST(labels) WHERE key = 'team')           AS team_tag,
    (SELECT value FROM UNNEST(labels) WHERE key = 'domain')         AS domain_tag,
    (SELECT value FROM UNNEST(labels) WHERE key = 'dbt_invocation') AS dbt_invocation_id,
    TIMESTAMP_DIFF(end_time, start_time, SECOND) AS duration_seconds
  FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_ORGANIZATION
  WHERE
    creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 90 DAY)
    AND job_type = 'QUERY'
    AND state = 'DONE'
    AND error_result IS NULL
),

bigquery_costs AS (
  SELECT
    job_id,
    project_id,
    user_email,
    job_type,
    statement_type,
    creation_time,
    start_time,
    end_time,
    duration_seconds,
    total_bytes_billed,
    total_bytes_processed,
    total_slot_ms,
    dbt_node_id,
    -- BigQuery labels don't have free-form query text; use job_id as identifier
    query,
    COALESCE(team_tag, SPLIT(user_email, '@')[OFFSET(0)]) AS attributed_team,
    COALESCE(domain_tag, project_id)                       AS attributed_domain,
    -- On-demand pricing: $6.25 per TB
    ROUND(
      (total_bytes_billed / POW(1024, 4)) * 6.25, 6
    ) AS attributed_cost_usd
  FROM raw_jobs
)

SELECT * FROM bigquery_costs
ORDER BY attributed_cost_usd DESC;

Tip: BigQuery charges a minimum of 10MB per query, even if your query scans less. This means lots of small exploratory queries add up fast. A team running 10,000 SELECT COUNT(*) queries per day is spending about $0.60/day on minimum charges alone — $220/year on effectively nothing. Track query counts alongside bytes to surface this pattern.

---

Step 3: Tagging Your Queries for Accurate Attribution

The queries above work fine for attributing costs by role or user — but for attribution to specific dbt models or business domains, you need structured metadata embedded in the query itself. Both platforms support this, but the mechanism differs.

In Snowflake, dbt uses query_comment in dbt_project.yml to embed JSON metadata:

# dbt_project.yml

query-comment:
  comment: "{{ tojson(dict(dbt_metadata=dict(
      node_name=model.name,
      node_id=node.unique_id,
      run_id=invocation_id,
      schema=model.schema
    ),
    team=var('team_name', 'unattributed'),
    domain=var('business_domain', 'unattributed')
  )) }}"
  append: true   # append to end of query rather than prepend

This embeds a JSON block in every query dbt runs. Our Snowflake extraction uses TRY_PARSE_JSON(query_tag) to parse this back out.

In BigQuery, dbt uses job labels, which are structured key-value pairs attached to the BigQuery job:

# dbt_project.yml

models:
  your_project:
    marketing:
      +meta:
        team: "marketing_analytics"
        domain: "marketing"
    finance:
      +meta:
        team: "finance_analytics"
        domain: "finance"

For runtime labels on BigQuery jobs, you configure dbt's BigQuery adapter:

# profiles.yml (BigQuery target)

your_project:
  target: prod
  outputs:
    prod:
      type: bigquery
      project: your-gcp-project
      dataset: prod_analytics
      job_labels:
        dbt_node_id: "{{ model.unique_id }}"
        team: "{{ model.config.meta.team | default('unattributed') }}"
        domain: "{{ model.config.meta.domain | default('unattributed') }}"

For non-dbt queries (BI tool queries, ad-hoc analyst SQL), attribution is harder. The best options are:

1. Naming conventions for Snowflake roles/warehouses: Create roles like MARKETING_ANALYST_ROLE and warehouses like MARKETING_WAREHOUSE. Then role_name and warehouse_name become your team proxy.
2. BigQuery service accounts per team: Each team's tools use a dedicated service account. The user_email then maps to a team.
3. Session variables in Snowflake: Analysts can run ALTER SESSION SET QUERY_TAG = '{"team": "marketing", "domain": "attribution"}' at the start of their session. You can enforce this in your BI tool's connection string.

Note: Perfect attribution is impossible without some discipline from query authors. Start with what you can infer automatically (role, warehouse, service account), then progressively add richer tagging as teams adopt the practice. An 80% accurate cost attribution dashboard is infinitely more useful than no dashboard.

---

Step 4: Modeling Storage Costs

Compute gets the attention, but storage costs are surprisingly meaningful at scale — and unlike compute, they're 100% attributable without any proration gymnastics.

Snowflake storage extraction:

-- snowflake_storage_costs.sql
-- Daily storage costs by database, schema, and table

WITH daily_storage AS (
  SELECT
    usage_date,
    database_name,
    average_database_bytes,
    average_failsafe_bytes,
    -- Snowflake charges ~$23/TB/month (on-demand), prorated daily
    ROUND(
      (average_database_bytes / POW(1024, 4)) * 23.0 / 30.0, 4
    ) AS storage_cost_usd_per_day
  FROM snowflake.account_usage.database_storage_usage_history
  WHERE usage_date >= DATEADD('day', -90, CURRENT_DATE())
)

SELECT
  usage_date,
  database_name,
  ROUND(average_database_bytes / POW(1024, 3), 2) AS size_gb,
  storage_cost_usd_per_day
FROM daily_storage
ORDER BY usage_date DESC, storage_cost_usd_per_day DESC;

For table-level storage, Snowflake's ACCOUNT_USAGE.TABLE_STORAGE_METRICS view gives you row counts, active bytes, and Time Travel bytes per table. This is particularly valuable for identifying tables with excessive Time Travel retention eating into your storage budget.

BigQuery storage extraction:

-- bigquery_storage_costs.sql

SELECT
  table_catalog AS project_id,
  table_schema  AS dataset_id,
  table_name,
  total_rows,
  ROUND(active_logical_bytes / POW(1024, 3), 2)       AS active_size_gb,
  ROUND(long_term_logical_bytes / POW(1024, 3), 2)    AS long_term_size_gb,
  -- Active: $0.02/GB/month, Long-term: $0.01/GB/month
  ROUND(
    (active_logical_bytes / POW(1024, 3)) * 0.02
    + (long_term_logical_bytes / POW(1024, 3)) * 0.01,
    4
  ) AS monthly_storage_cost_usd,
  last_modified_time
FROM `region-us`.INFORMATION_SCHEMA.TABLE_STORAGE
WHERE total_rows > 0
ORDER BY monthly_storage_cost_usd DESC;

---

Step 5: Building the Normalized Cost Model in dbt

With raw extraction queries in hand, the next step is building a normalized cost model that works across platforms and can feed a unified dashboard. This is where dbt fundamentals become essential — we'll create a mart-layer model that standardizes the schema regardless of platform.

The target schema for our unified cost model:

fact_warehouse_costs
├── cost_id (surrogate key)
├── platform ('snowflake' | 'bigquery')
├── cost_date (date)
├── cost_hour (timestamp, null for storage)
├── cost_type ('compute' | 'storage')
├── query_id
├── model_name (dbt model, if applicable)
├── model_node_id
├── attributed_team
├── attributed_domain
├── attributed_user
├── warehouse_or_project
├── bytes_scanned
├── execution_ms
├── cost_usd
├── _loaded_at (pipeline metadata)

Here's the dbt model that unifies both sources:

-- models/marts/finance/fact_warehouse_costs.sql

{{
  config(
    materialized='incremental',
    unique_key='cost_id',
    partition_by={
      'field': 'cost_date',
      'data_type': 'date',
      'granularity': 'day'
    },
    cluster_by=['platform', 'attributed_team', 'attributed_domain']
  )
}}

WITH snowflake_compute AS (
  SELECT
    {{ dbt_utils.generate_surrogate_key(['query_id', "'snowflake_compute'"]) }} AS cost_id,
    'snowflake'   AS platform,
    'compute'     AS cost_type,
    start_time::DATE AS cost_date,
    DATE_TRUNC('hour', start_time) AS cost_hour,
    query_id,
    dbt_model_name AS model_name,
    dbt_node_id    AS model_node_id,
    attributed_team,
    attributed_domain,
    user_name AS attributed_user,
    warehouse_name AS warehouse_or_project,
    bytes_scanned,
    total_elapsed_time AS execution_ms,
    attributed_cost_usd AS cost_usd,
    CURRENT_TIMESTAMP() AS _loaded_at
  FROM {{ ref('stg_snowflake__query_costs') }}

  {% if is_incremental() %}
    WHERE start_time > (SELECT MAX(cost_hour) FROM {{ this }})
  {% endif %}
),

bigquery_compute AS (
  SELECT
    {{ dbt_utils.generate_surrogate_key(['job_id', "'bigquery_compute'"]) }} AS cost_id,
    'bigquery'    AS platform,
    'compute'     AS cost_type,
    DATE(creation_time) AS cost_date,
    TIMESTAMP_TRUNC(creation_time, HOUR) AS cost_hour,
    job_id AS query_id,
    dbt_node_id AS model_name,
    dbt_node_id AS model_node_id,
    attributed_team,
    attributed_domain,
    user_email AS attributed_user,
    project_id AS warehouse_or_project,
    total_bytes_billed AS bytes_scanned,
    duration_seconds * 1000 AS execution_ms,
    attributed_cost_usd AS cost_usd,
    CURRENT_TIMESTAMP() AS _loaded_at
  FROM {{ ref('stg_bigquery__query_costs') }}

  {% if is_incremental() %}
    WHERE creation_time > (SELECT MAX(cost_hour) FROM {{ this }})
  {% endif %}
),

snowflake_storage AS (
  SELECT
    {{ dbt_utils.generate_surrogate_key(['usage_date', 'database_name', "'snowflake_storage'"]) }} AS cost_id,
    'snowflake' AS platform,
    'storage'   AS cost_type,
    usage_date AS cost_date,
    NULL AS cost_hour,
    NULL AS query_id,
    NULL AS model_name,
    NULL AS model_node_id,
    database_name AS attributed_team,   -- team attribution requires schema-level ownership mapping
    database_name AS attributed_domain,
    NULL AS attributed_user,
    database_name AS warehouse_or_project,
    average_database_bytes AS bytes_scanned,
    NULL AS execution_ms,
    storage_cost_usd_per_day AS cost_usd,
    CURRENT_TIMESTAMP() AS _loaded_at
  FROM {{ ref('stg_snowflake__storage_costs') }}

  {% if is_incremental() %}
    WHERE usage_date > (SELECT MAX(cost_date) FROM {{ this }} WHERE platform = 'snowflake' AND cost_type = 'storage')
  {% endif %}
)

SELECT * FROM snowflake_compute
UNION ALL
SELECT * FROM bigquery_compute
UNION ALL
SELECT * FROM snowflake_storage

Tip: Use an incremental model here, not a full refresh. Cost history tables are append-only by nature — you never need to recompute last month's costs. With incremental models at scale, you process only new rows on each run, keeping the pipeline fast and cheap. The ultimate recursion — your cost attribution pipeline should cost almost nothing to run.

---

Step 6: Building the Unit Economics Metrics Layer

Raw cost rows aren't enough. The power of unit economics is in the rates — cost per model run, cost per GB ingested, cost per dashboard load. These derived metrics are what make the dashboard actionable. This is a perfect use case for a semantic layer.

Here's a dbt model that computes the key unit economics metrics per dbt model:

-- models/marts/finance/unit_economics_by_model.sql

WITH model_costs AS (
  SELECT
    model_name,
    attributed_domain,
    attributed_team,
    platform,
    COUNT(DISTINCT query_id)                     AS total_runs,
    COUNT(DISTINCT cost_date)                    AS active_days,
    SUM(cost_usd)                                AS total_cost_usd,
    AVG(cost_usd)                                AS avg_cost_per_run,
    PERCENTILE_CONT(0.95) WITHIN GROUP (
      ORDER BY cost_usd
    )                                             AS p95_cost_per_run,
    SUM(bytes_scanned) / POW(1024, 3)            AS total_gb_scanned,
    AVG(execution_ms) / 1000.0                   AS avg_execution_seconds,
    MAX(cost_usd)                                AS max_single_run_cost,
    -- Cost trend: last 7 days vs prior 7 days
    SUM(CASE WHEN cost_date >= CURRENT_DATE() - 7 THEN cost_usd ELSE 0 END)  AS cost_last_7d,
    SUM(CASE WHEN cost_date BETWEEN CURRENT_DATE() - 14 AND CURRENT_DATE() - 8
             THEN cost_usd ELSE 0 END)                                        AS cost_prior_7d
  FROM {{ ref('fact_warehouse_costs') }}
  WHERE
    cost_type = 'compute'
    AND model_name IS NOT NULL
    AND cost_date >= CURRENT_DATE() - 90
  GROUP BY 1, 2, 3, 4
),

with_trends AS (
  SELECT
    *,
    CASE
      WHEN cost_prior_7d > 0
      THEN ROUND((cost_last_7d - cost_prior_7d) / cost_prior_7d * 100, 1)
      ELSE NULL
    END AS cost_wow_pct_change,
    -- Efficiency score: cost per GB scanned (lower = more efficient)
    CASE
      WHEN total_gb_scanned > 0
      THEN ROUND(total_cost_usd / total_gb_scanned, 4)
      ELSE NULL
    END AS cost_per_gb_scanned
  FROM model_costs
)

SELECT * FROM with_trends
ORDER BY total_cost_usd DESC

And the companion metric for team-level rollup:

-- models/marts/finance/unit_economics_by_team.sql

SELECT
  attributed_team,
  attributed_domain,
  platform,
  cost_type,
  DATE_TRUNC('month', cost_date) AS cost_month,
  SUM(cost_usd)                   AS total_cost_usd,
  SUM(CASE WHEN cost_type = 'compute' THEN cost_usd ELSE 0 END) AS compute_cost_usd,
  SUM(CASE WHEN cost_type = 'storage' THEN cost_usd ELSE 0 END) AS storage_cost_usd,
  COUNT(DISTINCT query_id)         AS total_queries,
  COUNT(DISTINCT model_name)       AS distinct_models_run,
  SUM(bytes_scanned) / POW(1024, 4) AS total_tb_scanned,
  -- Compute as % of team's total platform spend
  SUM(cost_usd) / SUM(SUM(cost_usd)) OVER (
    PARTITION BY platform, DATE_TRUNC('month', cost_date)
  ) * 100 AS pct_of_platform_monthly_spend
FROM {{ ref('fact_warehouse_costs') }}
WHERE cost_date >= DATE_TRUNC('month', CURRENT_DATE()) - INTERVAL '6 months'
GROUP BY 1, 2, 3, 4, 5
ORDER BY cost_month DESC, total_cost_usd DESC

---

Step 7: Automating the Pipeline

Manual extraction isn't a dashboard — it's a chore. You need to automate the full pipeline: extract from ACCOUNT_USAGE / INFORMATION_SCHEMA, transform through the staging and mart layers, and make results available to your visualization layer on a regular schedule.

If you're already using Airflow for orchestration (as covered in orchestrating dbt runs with Airflow), the cost pipeline fits naturally as a daily DAG:

# dags/warehouse_cost_attribution_dag.py

from airflow import DAG
from airflow.providers.dbt.cloud.operators.dbt import DbtCloudRunJobOperator
from airflow.providers.slack.operators.slack_webhook import SlackWebhookOperator
from datetime import datetime, timedelta

default_args = {
    'owner': 'platform-team',
    'retries': 2,
    'retry_delay': timedelta(minutes=10),
    'email_on_failure': True,
    'email': ['platform-oncall@yourcompany.com'],
}

with DAG(
    dag_id='warehouse_cost_attribution',
    default_args=default_args,
    schedule_interval='0 6 * * *',   # 6 AM UTC daily — after ACCOUNT_USAGE latency clears
    start_date=datetime(2024, 1, 1),
    catchup=False,
    tags=['cost', 'platform', 'finance'],
) as dag:

    run_cost_models = DbtCloudRunJobOperator(
        task_id='run_cost_attribution_models',
        job_id=12345,   # your dbt Cloud job ID for the cost attribution tag
        wait_for_termination=True,
        dbt_cloud_conn_id='dbt_cloud_default',
    )

    alert_on_anomaly = SlackWebhookOperator(
        task_id='alert_cost_anomaly_check',
        http_conn_id='slack_platform_alerts',
        message="""
        :white_check_mark: Daily cost attribution models completed.
        Check the unit economics dashboard for anomalies.
        """,
    )

    run_cost_models >> alert_on_anomaly

Schedule this at 6 AM UTC, which gives Snowflake's ACCOUNT_USAGE latency time to clear for the prior day's queries. For BigQuery, INFORMATION_SCHEMA.JOBS is available with minimal latency, so you could run it sooner.

Warning: Do not schedule your cost attribution DAG to run hourly against ACCOUNT_USAGE. The views themselves run on Snowflake compute, and querying them frequently adds non-trivial cost to your bill. A daily run is sufficient for operational cost management. If you need near-real-time alerting on runaway queries, use Snowflake's RESOURCE_MONITORS feature instead — it's native, free, and fires alerts before costs compound.

---

Step 8: Designing the Dashboard

You have the data. Now let's talk about what actually belongs on a unit economics dashboard versus what's just noise. The goal is actionability: every metric should have an obvious answer to "what do I do if this number looks wrong?"

Dashboard structure:

Overview tab — Executive summary

• Total platform spend MTD vs prior month (line chart with % change annotation)
• Spend by platform (Snowflake vs BigQuery) — pie or stacked bar
• Top 5 cost-driving teams this month (horizontal bar)
• Projected end-of-month spend based on current daily average

Compute tab — Query-level analysis

• Most expensive dbt models (table: model name, domain, team, avg cost/run, p95 cost/run, runs this month, total cost)
• Cost per run trend for top 10 models (small multiples line chart)
• Query cost distribution by hour-of-day (heatmap — useful for identifying nocturnal batch jobs that could shift to off-peak warehouses)
• Top 20 most expensive ad-hoc queries (table with query text snippet, user, cost, timestamp)

Storage tab — Growth analysis

• Storage by database/dataset over time (stacked area chart)
• Tables sorted by storage cost (with "days since last query" column — the kill list)
• Time Travel and Fail-Safe overhead as % of total Snowflake storage
• Month-over-month storage growth rate by domain

Anomaly tab — Things to investigate

• Models with >50% cost increase week-over-week
• Users with query costs > 2 standard deviations above their 30-day mean
• Warehouses with unusually long idle times (paying for uptime with no work done)
• Queries with bytes_scanned / rows_produced > 10,000 (scanning lots, returning little — full table scans with heavy filters)

The "tables with high storage and no recent queries" metric is pure gold. In most organizations, 20-30% of stored data has never been queried in 90 days. That's concrete savings on your first dashboard run.

---

Hands-On Exercise

Let's build a working cost attribution report for your own Snowflake environment. This exercise assumes you have ACCOUNTADMIN or SYSADMIN access to run ACCOUNT_USAGE queries.

Part 1: Run the base extraction

Execute the snowflake_query_costs.sql query from Step 1 in your Snowflake console. Add a WHERE clause to limit to the last 7 days initially to keep it fast:

WHERE start_time >= DATEADD('day', -7, CURRENT_TIMESTAMP())
AND warehouse_name IS NOT NULL
AND execution_status = 'SUCCESS'

Sort by attributed_cost_usd DESC. Look at the top 10 queries. Do you recognize them? Are they dbt runs, BI tool queries, or ad-hoc SQL?

Part 2: Build the team attribution map

If you're not using query tags yet, you'll find team_tag is NULL for most rows. Create a simple mapping table in your warehouse:

CREATE TABLE admin.cost_attribution.team_role_map AS
SELECT
  role_name,
  CASE
    WHEN role_name ILIKE '%marketing%' THEN 'Marketing Analytics'
    WHEN role_name ILIKE '%finance%'   THEN 'Finance Analytics'
    WHEN role_name ILIKE '%data_eng%'  THEN 'Data Engineering'
    WHEN role_name ILIKE '%dbt%'       THEN 'Data Engineering'
    WHEN role_name ILIKE '%bi_%'       THEN 'Business Intelligence'
    ELSE 'Unknown'
  END AS team_name
FROM (SELECT DISTINCT role_name FROM snowflake.account_usage.query_history
      WHERE start_time >= DATEADD('day', -30, CURRENT_TIMESTAMP()));

Join this into your cost query as a fallback when team_tag is NULL.

Part 3: Identify your top 3 quick wins

Using your query output, answer:

1. Which team or role is responsible for the largest share of compute costs?
2. Are there any queries scanning >10GB that return fewer than 100 rows? (Candidates for query optimization or partition pruning — see profiling and optimizing slow dbt models)
3. Which warehouse has the highest average cost-per-query? Is it sized correctly for its workload?

Write down three actionable recommendations based on what you find. These are your first pull requests.

---

Common Mistakes & Troubleshooting

Mistake 1: Using bytes_scanned instead of bytes_billed in BigQuery

bytes_processed reflects actual data read. bytes_billed reflects what you're charged — it rounds up to 10MB minimum. For cost calculations, always use total_bytes_billed. The difference is small per query but meaningful in aggregate across thousands of small queries.

Mistake 2: Double-counting Snowflake costs

If you're prorating warehouse credits by query execution time and also pulling from METERING_DAILY_HISTORY, you'll double-count. Use one or the other. The proration approach in Step 1 is more granular; the daily history is simpler for trend reporting. Don't mix them in the same fact table.

Mistake 3: Assuming query_tag is always JSON

Some Snowflake queries set query_tag as plain text strings, not JSON. TRY_PARSE_JSON() will return NULL for these — which is fine — but if you're applying JSON extraction to a plain string, you'll get silent NULLs everywhere. Add a data quality check:

-- Check what percentage of queries have parseable JSON tags
SELECT
  COUNT(*) AS total_queries,
  SUM(CASE WHEN TRY_PARSE_JSON(query_tag) IS NOT NULL THEN 1 ELSE 0 END) AS json_tagged,
  ROUND(
    SUM(CASE WHEN TRY_PARSE_JSON(query_tag) IS NOT NULL THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 1
  ) AS pct_json_tagged
FROM snowflake.account_usage.query_history
WHERE start_time >= DATEADD('day', -7, CURRENT_TIMESTAMP())
AND query_tag IS NOT NULL AND query_tag != '';

If this percentage is below 30%, your query tagging strategy isn't deployed widely enough to be useful.

Mistake 4: Attributing storage costs at the database level only

Database-level storage attribution is too coarse. Marketing and Finance might share a database, in which case all storage looks like the same team. You need schema-level (or table-level) ownership mapping. Maintain a simple schema_ownership table in your warehouse:

CREATE TABLE admin.cost_attribution.schema_ownership (
  platform        VARCHAR,
  database_name   VARCHAR,
  schema_name     VARCHAR,
  team_name       VARCHAR,
  domain          VARCHAR,
  owner_email     VARCHAR
);

Join this to your storage queries for accurate team attribution.

Mistake 5: Ignoring failed queries

Failed queries in Snowflake still consume credits if they ran for any significant time before failing. A query that crashes after 45 seconds on a Large warehouse still consumed ~$0.03. Filter out execution_status != 'SUCCESS' from your billing analysis only if you're sure failures are instantaneous — otherwise include them.

---

Summary & Next Steps

You've built a complete unit economics attribution system: raw cost extraction from both Snowflake and BigQuery, query tagging strategies for dbt models and ad-hoc analysts, a normalized dbt fact model, derived unit economics metrics, an automated daily pipeline, and a dashboard structure that drives action rather than just reporting numbers.

The key mental shift this enables is from "our data warehouse costs $X this month" to "the marketing attribution pipeline costs $0.87 per run and runs 1,200 times a month — is that worth it?" That's the question that transforms data engineering from a cost center into a managed business investment.

Where to go from here:

• Add cost-to-lineage integration. Once you have per-model costs, connect them to your data lineage graph to see total pipeline cost — from ingestion to mart. The multi-hop lineage tracking article shows how to wire OpenLineage into this picture.

• Set up proactive budget alerts. Snowflake Resource Monitors and BigQuery budget alerts both support threshold-based notifications. Wire these to Slack or PagerDuty so you catch runaway costs before the month-end invoice arrives.

• Build a cost-aware CI pipeline. If you're using dbt with CI/CD, as described in automating dbt environment promotion, you can add a cost estimation step that flags PRs where model changes are projected to increase run cost by more than 20%.

• Implement chargeback or showback. Once attribution is running reliably for 60+ days, you have enough data to run a showback program: teams receive monthly reports of their attributed costs with benchmarks. This alone typically drives 10-20% voluntary cost reduction as teams realize their expensive query patterns.

The data infrastructure that powers everything else in your modern data stack finally becomes visible, measurable, and improvable. That visibility is what separates platforms that scale sustainably from ones that become financial surprises.