Structure of pricing data export

Stay organized with collections Save and categorize content based on your preferences.

This document provides reference information for the schema of Cloud Billing pricing data that's exported to each table in BigQuery.

Schema of the pricing data

In your BigQuery dataset, your Cloud Billing account pricing data is loaded into a data table named cloud_pricing_export.

The following information describes the schema of the Cloud Billing account pricing data that's exported to BigQuery.

About pricing tiers

SKU prices are offered by pricing tiers. Pricing tiers provide a pricing structure based on different tier levels. Some SKUs have only a single pricing tier while others have multiple pricing tiers. Examples of SKUs with multi-tiered pricing include the following:

• SKUs with a free usage tier. For example: 1-1000 units are free. 1001+ units are priced at $1 each.
• SKUs where the price per unit decreases after the usage quantity within a tier is exceeded. For example: 1-100 units are priced at $5 each, 101-1000 units are priced at $4 each, and 1001+ units are priced at $3 each.

Some notes about multi-tiered pricing:

• The tier usage counter resets to zero based on the aggregation_interval of the SKU: daily or monthly.

  • Daily SKUs reset each day at 12 AM US and Canadian Pacific Time (UTC-8 or UTC-7).
  • Monthly SKUs reset to zero on the first day of each calendar month (example: January, February, and so on), at 12 AM US and Canadian Pacific Time (UTC-8 or UTC-7).

• Tiers operate independently for each Cloud Billing account and don't aggregate across multiple Cloud Billing accounts, even if the projects are in the same Organization or belong to the same legal entity.

• Tiers operate independently per SKU: The usage of one SKU can only affect the price of that SKU. There are no SKUs in which usage affects the tiered pricing of another SKU.

• It's possible that the SKU pricing tiers for list prices might not line up exactly with the pricing tiers for contract prices. This circumstance is rare.

• If a SKU has multiple pricing tiers, each SKU tier price is listed as a separate row in the pricing table. You can identify SKUs with multiple pricing tiers using the tiered_rates.start_usage_amount.

• Depending on how you write your query, you can return your SKU pricing tiers as nested or unnested data. For more information about nested and unnested data, see the following examples.

Pricing data query examples

This section provides examples of how to query the Cloud Billing pricing data exported to BigQuery.

• Get list prices for a specific SKU
• Get list prices for a specific SKU, and include service description
  • Example 1: Returns nested data
  • Example 2: Returns unnested data joined with the same table
• Use product taxonomy and geo taxonomy to query SKUs
  • Get the product taxonomy of a SKU
  • Get all SKUs for a specific product taxonomy
  • Get all SKUs for a specific geo taxonomy and product taxonomy

This section provides different examples of how to query the Cloud Billing pricing data exported to BigQuery.

Common values used in the example pricing queries

The query examples in this section use the following values:

• Table name: project.dataset.cloud_pricing_export
• SKU ID: 2DA5-55D3-E679 (Cloud Run - Requests)

Get list prices for a specific SKU

This example demonstrates a query that returns the list_price for each pricing tier for a specified SKU.

Standard SQL

SELECT sku.id,  sku.description, list_price.*
FROM `project.dataset.cloud_pricing_export`
WHERE DATE(_PARTITIONTIME) = "2020-07-20"
      AND sku.id = "2DA5-55D3-E679"
;

_PARTITIONTIME is a field auto-generated by BigQuery and represents the date that the data belongs to. Instead of _PARTITIONTIME, you can use a field that Cloud Billing export explicitly generates, such as pricing_as_of_time.

Here's the same query configured to use the pricing_as_of_time field:

SELECT sku.id,  sku.description, list_price.*
FROM `project.dataset.cloud_pricing_export`
WHERE DATE(pricing_as_of_time) = "2020-07-20"
      AND sku.id = "2DA5-55D3-E679"
;

Query results

Get list prices for a specific SKU, and include service description

The two examples in this section demonstrate queries that return the list_price for each pricing tier for a specified SKU, and includes the SKU description and the service description.

• Example 1 returns one SKU per row, with the pricing tiers displayed as nested data.
• Example 2 demonstrates unnesting the data to return one row per SKU per pricing tier.

Example 1: Returns nested data

This example queries a single SKU to return the list_price data. This SKU has multiple pricing tiers. The list price field values display in individual rows that are nested under the SKU ID row.

Standard SQL

SELECT sku.id AS sku_id,
       sku.description AS sku_description,
       service.id AS service_id,
       service.description as service_description,
       list_price.*
FROM my-billing-admin-project.my_billing_dataset.cloud_pricing_export
WHERE DATE(_PARTITIONTIME) = "2020-07-20"
      AND sku.id = "2DA5-55D3-E679"
;

Query results:

Example 2: Returns unnested data joined with the same table

This example queries a single SKU to return the list price. The SKU has multiple pricing tiers. The query demonstrates using the UNNEST operator to flatten the tiered_rates array and join the fields with the same table, resulting in one row per pricing tier.

Standard SQL

SELECT sku.id AS sku_id,
       sku.description AS sku_description,
       service.id AS service_id,
       service.description as service_description,
       tier.*
FROM `my-billing-admin-project.my_billing_dataset.cloud_pricing_export` as sku_pricing, UNNEST (sku_pricing.list_price.tiered_rates) as tier
WHERE DATE(_PARTITIONTIME) = "2020-07-20"
      AND sku.id = "2DA5-55D3-E679"
;

Query results:

Use product taxonomy and geo taxonomy to query SKUs

• Product taxonomy is a list of product categories that apply to the SKU, such as Serverless, Cloud Run, or VMs On Demand.
• Geo taxonomy is the geographic metadata that applies to a SKU, consisting of type and region values.

Get the product taxonomy of a SKU

This example demonstrates a query that returns the product_taxonomy list for a specified SKU, where the SKU ID = 2DA5-55D3-E679 (Cloud Run - Requests).

Standard SQL

SELECT sku.id AS sku_id,
       sku.description AS sku_description,
       service.id AS service_id,
       service.description as service_description,
       product_taxonomy
FROM `project.dataset.cloud_pricing_export`
WHERE DATE(_PARTITIONTIME) = "2020-07-20"
      AND sku.id = "2DA5-55D3-E679"
;

Query results:

Get all SKUs for a specific product taxonomy

This example demonstrates a query that returns all SKUs that match a specified product_taxonomy. In this query, we are specifying Serverless as the product taxonomy value.

Standard SQL

SELECT sku.id AS sku_id,
       sku.description AS sku_description,
       service.id AS service_id,
       service.description as service_description,
       product_taxonomy
FROM `project.dataset.cloud_pricing_export`
WHERE DATE(_PARTITIONTIME) = "2020-07-20"
     AND "Serverless" in UNNEST(product_taxonomy)
LIMIT 10
;

Query results:

Get all SKUs for a specific geo taxonomy and product taxonomy

This example demonstrates a query that returns all SKUs that match a specified geo_taxonomy region and a specified product_taxonomy, where region = us-east4 and product_taxonomy = VMs On Demand.

Standard SQL

SELECT sku.id AS sku_id,
       sku.description AS sku_description,
       service.id AS service_id,
       service.description as service_description,
       geo_taxonomy,
       product_taxonomy
FROM `project.dataset.cloud_pricing_export`
WHERE DATE(_PARTITIONTIME) = "2020-07-20"
      AND "VMs On Demand" in UNNEST(product_taxonomy)
      AND geo_taxonomy.type = "REGIONAL"
      AND "us-east4" in UNNEST (geo_taxonomy.regions)
;

Query results:

Returns the list prices for a SKU with multiple pricing tiers

You query your BigQuery data by table name. The table name used in the query's FROM clause is determined using three values: project.dataset.BQ_table_name.

Common values used in these examples:

• Table name: project.dataset.cloud_pricing_export
• SKU ID: 2DA5-55D3-E679 (Cloud Run - Requests)

Example 1: Returns nested data

This example queries a single SKU to return the list_price data. This SKU has multiple pricing tiers. The list price field values display in individual rows that are nested under the SKU ID row.

Standard SQL

SELECT sku.id AS sku_id,
       sku.description AS sku_description,
       service.id AS service_id,
       service.description as service_description,
       list_price.*
FROM my-billing-admin-project.my_billing_dataset.cloud_pricing_export
WHERE DATE(_PARTITIONTIME) = "2020-07-20"
      AND sku.id = "2DA5-55D3-E679"
;

Query results:

Example 2: Returns unnested data joined with the same table

This example queries a single SKU to return the list price. The SKU has multiple pricing tiers. The query demonstrates using the UNNEST operator to flatten the tiered_rates array and join the fields with the same table, resulting in one row per pricing tier.

Standard SQL

SELECT sku.id AS sku_id,
       sku.description AS sku_description,
       service.id AS service_id,
       service.description as service_description,
       tier.*
FROM `my-billing-admin-project.my_billing_dataset.cloud_pricing_export` as sku_pricing, UNNEST (sku_pricing.list_price.tiered_rates) as tier
WHERE DATE(_PARTITIONTIME) = "2020-07-20"
      AND sku.id = "2DA5-55D3-E679"
;

Query results:

Related topics

Topics related to exported Cloud Billing data

• Set up Cloud Billing data export to BigQuery
• Example queries for Cloud Billing data export to BigQuery
• Visualize spend over time with Looker Studio

Cost and pricing reports available in the Google Cloud console

• View your Cloud Billing reports and cost trends
• View and download the cost details of your invoice or statement
• View and download prices for Google's cloud services
• Understand your savings with cost breakdown reports
• Analyze the effectiveness of your committed use discounts
• View your cost and payment history