Pricing system table reference

Preview

This feature is in Public Preview.

This article provides you with an overview of the pricing system table, including the schema and example queries. The pricing table gives you access to a historical log of SKU pricing. A record gets added each time there is a change to a SKU price. These logs can help you perform cost analysis and monitor pricing changes.

The pricing system table is located at system.billing.list_prices.

Pricing table schema

The pricing system table uses the following schema:

Column name

Data type

Description

Example

price_start_time

timestamp

The time this price became effective

2023-01-01T09:59:59.999Z

price_end_time

timestamp

The time this price stopped being effective

2023-01-01T09:59:59.999Z

account_id

string

ID of the account this report was generated for

1234567890123456

sku_name

string

Name of the SKU

STANDARD_ALL_PURPOSE_COMPUTE

cloud

string

Name of the Cloud this price is applicable to. Possible values are AWS, AZURE, and GCP.

AWS, AZURE, or GCP

currency_code

string

The currency this price is expressed in

USD

usage_unit

string

The unit of measurement that is monetized.

DBU

pricing

struct

A structured data field that includes pricing info at the published list price rate. The key default will always return a single price that can be used for simple estimates. Some pricing models might also include additional keys that provide more detail.

{"default": "0.10"}

Sample queries

You can use the following sample queries to answer common questions about SKU pricing:

Find the list price of a particular SKU on a particular date

Since the table only contains records for times when the SKU price changes, you have to look up the most recent price change on or before the date.

SELECT sku_name, price_start_time, pricing.default
FROM system.billing.list_prices
WHERE sku_name = 'STANDARD_ALL_PURPOSE_COMPUTE'
AND price_start_time <= "2023-01-01"
ORDER BY price_start_time DESC
LIMIT 1

View total cost of everything that used a particular custom tag during the previous calendar month

Note

Be sure to replace the custom tag’s key and value.

SELECT SUM(usage.usage_quantity * list_prices.pricing.default) as `Total Dollar Cost`
FROM system.billing.usage
JOIN system.billing.list_prices
ON list_prices.sku_name = usage.sku_name
WHERE usage.custom_tags.{{ tag_key }} = {{ tag_value }}
AND usage.usage_end_time >= list_prices.price_start_time
AND (list_prices.price_end_time IS NULL OR usage.usage_end_time < list_prices.price_end_time)
AND usage.usage_date BETWEEN "2023-05-01" AND "2023-05-31"

View prices that have changed between months 

SELECT sku_name, price_start_time, pricing.default
FROM system.billing.list_prices
WHERE price_start_time BETWEEN "2023-05-01" AND "2023-07-01"

Estimate the add-on costs for usage in the previous calendar month

This query applies a simple percentage to all usage in the period. Note that this might differ slightly from the actual monetization due to how entitlements for some add-ons are managed. Replace the add-on rate with your account’s rate.

SELECT SUM(usage.usage_quantity * list_prices.pricing.default) * {{ add_on_rate }} as `Total Add-On Dollar Cost`
FROM system.billing.usage
JOIN system.billing.list_prices ON list_prices.sku_name = usage.sku_name
  WHERE usage.usage_end_time >= list_prices.price_start_time
  AND (list_prices.price_end_time IS NULL OR usage.usage_end_time < list_prices.price_end_time)
  AND usage.usage_date BETWEEN "2024-02-01" AND "2024-02-29"