Structure of pricing data export

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