Querying Cost Data

Cost snapshots are exposed as two MQL tables, so you can go beyond the prebuilt rollups and slice spend any way you like. See Query Language for MQL basics.

Table One row per Key columns
cost_snapshots state snapshot state_id, kind, monthly_cost, hourly_cost, currency, resource_count, supported_count, priced_count, calculated_at, tx_id
cost_snapshot_resources resource in a snapshot snapshot_id, address, type, provider, region, monthly_cost, hourly_cost, supported, no_price, tags, components

Join them on cost_snapshot_resources.snapshot_id = cost_snapshots.id.

Working With Snapshots

A few rules make cost queries correct:

  • Money is text. The monthly_cost and hourly_cost columns are strings (to preserve precision) and are NULL when a resource has no billable cost. Cast with ::real for math, and filter WHERE monthly_cost IS NOT NULL before ranking or summing.
  • Snapshots are append-only. Each recompute writes a new row, so a state accumulates many kind = 'current' snapshots over time. To avoid double-counting, scope a query to a single snapshot_id (shown below), or use the rollup API for tenant-wide totals; it dedupes to each state's latest snapshot server-side.
  • kind is current for real estimates. A planned kind is reserved for future use.

To get the latest snapshot id for a state:

SELECT id FROM cost_snapshots
WHERE state_id = '5156028b-…' AND kind = 'current'
ORDER BY calculated_at DESC LIMIT 1

Example Queries

Per-State Totals

SELECT state_id, monthly_cost, priced_count, resource_count
FROM cost_snapshots
WHERE kind = 'current' AND monthly_cost IS NOT NULL
ORDER BY monthly_cost::real DESC
LIMIT 4

state_id                              monthly_cost  priced_count  resource_count
------------------------------------  ------------  ------------  --------------
5156028b-d378-4ab6-8a8b-4a783e633bc6  1509.640000   21            34
3523a799-01bd-47cc-bdc2-7c72f628f24e  130.816000    5             8
2d531a2c-299d-4787-ba47-6c75c9723516  38.472000     3             3

Most Expensive Resources in a State

SELECT address, type, monthly_cost
FROM cost_snapshot_resources
WHERE snapshot_id = '9eb9021d-…' AND monthly_cost IS NOT NULL
ORDER BY monthly_cost::real DESC
LIMIT 5

address                            type                     monthly_cost
---------------------------------  -----------------------  ------------
aws_db_instance.primary            aws_db_instance          656.270000
aws_db_instance.replica_1          aws_db_instance          328.500000
aws_elasticache_cluster.app_cache  aws_elasticache_cluster  240.170000
aws_db_instance.replica_2          aws_db_instance          164.250000
aws_elasticache_cluster.sessions   aws_elasticache_cluster  120.450000

Monthly Cost by Resource Type

count is a reserved word in MQL, so alias the count to something else (here, cnt) and sort by the aggregate expression rather than the alias:

SELECT type, sum(monthly_cost::real) AS monthly, count(*) AS cnt
FROM cost_snapshot_resources
WHERE snapshot_id = '9eb9021d-…' AND monthly_cost IS NOT NULL
GROUP BY type
ORDER BY sum(monthly_cost::real) DESC
LIMIT 5

type                     monthly  cnt
-----------------------  -------  ---
aws_db_instance          1149.02  3
aws_elasticache_cluster  360.62   2

Cost by Tag

Extract a tag with the #>> path operator (use #>> rather than ->> inside GROUP BY). A NULL group is resources missing that tag:

SELECT tags#>>'{Team}' AS team, sum(monthly_cost::real) AS monthly
FROM cost_snapshot_resources
WHERE snapshot_id = '9eb9021d-…' AND monthly_cost IS NOT NULL
GROUP BY tags#>>'{Team}'
ORDER BY sum(monthly_cost::real) DESC

team  monthly
----  -------
data  1149.02
      360.62

For tag attribution across the whole tenant, prefer the ?tag_key= rollup endpoint; it dedupes states and buckets untagged resources for you.

Coverage Gaps

The resources that did not contribute to the totals (recognized but no_price):

SELECT type, count(*) AS cnt
FROM cost_snapshot_resources
WHERE snapshot_id = '9eb9021d-…' AND no_price
GROUP BY type
ORDER BY count(*) DESC

Next Steps