Understanding the Cloud Billing data tables in BigQuery

This page provides reference information for the schema of the Cloud Billing data that is exported to each table in BigQuery.

Billing data tables

Shortly after enabling Cloud Billing export to BigQuery, billing data tables are automatically created in the BigQuery dataset.

  • Daily cost detail table – In your BigQuery dataset, this table is named gcp_billing_export_v1_<BILLING_ACCOUNT_ID>.
  • Pricing table – In your BigQuery dataset, this table is named cloud_pricing_export.

Frequency of data loads

Daily cost detail export:

  • When you first enable the daily cost detail export to BigQuery, it might take a few hours to start seeing your Google Cloud cost data.
  • Google Cloud services report usage and cost data to Cloud Billing processes at varying intervals.
  • Cloud Billing exports usage and cost data to your BigQuery dataset at regular intervals (there are no delivery or latency guarantees for the export to BigQuery).
  • Because the usage reporting frequency varies by Google Cloud service, you might not see Cloud Billing usage and cost data right away for all recently used Google Cloud services.

Pricing export:

  • When you first enable the pricing export to BigQuery, it might take up to 48 hours to start seeing your Google Cloud pricing data.
  • After you enable pricing export, the pricing data applicable to your Cloud Billing account is exported to BigQuery once each day.

Data availability:

  • Your BigQuery dataset only reflects Cloud Billing data incurred from the date when you first enabled the export, and after. Cloud Billing data is not added retroactively, so you won't see Cloud Billing data for usage that occurred before you enabled export.
  • If you delete any exported data (such as BigQuery export records), we cannot backfill the deleted records.
  • BigQuery loads are ACID compliant, so if you query the BigQuery Cloud Billing export dataset while data is being loaded into it, you will not encounter partially loaded data. Learn more about BigQuery characteristics.

Schema of the daily cost detail data

In your BigQuery dataset, your detailed Google Cloud daily cost data is loaded into a data table named gcp_billing_export_v1_<BILLING_ACCOUNT_ID>.

The following information describes the schema of the Google Cloud daily cost data that is exported to BigQuery.

Field Type Description
billing_account_id String

The Cloud Billing account ID that the usage is associated with.

For resellers: For usage costs generated by a Cloud Billing subaccount, this is the ID of the subaccount, not the ID of the parent reseller Cloud Billing account.

invoice.month String

The year and month (YYYYMM) of the invoice that includes the cost line items. For example: "201901" is equivalent to January, 2019.

You can use this field to get the total charges on the invoice. See Cloud Billing Export to BigQuery Query Examples.

cost_type String

The type of cost this line item represents: regular, tax, adjustment, or rounding error.

service.id String The ID of the service that the usage is associated with.
service.description String The Google Cloud service that reported the Cloud Billing data.
sku.id String The ID of the resource used by the service. For the full list of SKUs, see Google Cloud SKUs.
sku.description String A description of the resource type used by the service. For example, a resource type for Cloud Storage is Standard Storage US.
usage_start_time Timestamp The start time of the hourly usage window within which the given cost was calculated. The usage/costs for all services is displayed with hourly granularity, which means long running service usage is spread across multiple hourly windows.

For more information, see the BigQuery documentation on timestamp data types. See also, Differences between exported data and invoices below.

usage_end_time Timestamp The end time of the hourly usage window within which the given cost was calculated. The usage/costs for all services is displayed with hourly granularity, which means long running service usage is spread across multiple hourly windows.

For more information, see the BigQuery documentation on timestamp data types. See also, Differences between exported data and invoices below.

project.id String The ID of the Google Cloud project that generated the Cloud Billing data.
project.name String The name of the Google Cloud project that generated the Cloud Billing data.
project.ancestry_numbers String The ancestors in the resource hierarchy for the project identified by the specified project.id (for example, my-project-123).

For example: /ParentOrgNumber/ParentFolderNumber/. Learn more about the Resource Hierarchy.

project.labels.key String If labels are present, the key portion of the key:value pair that comprises the label on the Google Cloud project where the usage occurred. For more information about using labels, see Using Labels.
project.labels.value String If labels are present, the value portion of the key:value pair that comprises the label on the Google Cloud project where the usage occurred. For more information about using labels, see Using Labels.
labels.key String If labels are present, the key portion of the key:value pair that comprises the label on the Google Cloud resource where the usage occurred. For more information about using labels, see Using Labels.
labels.value String If labels are present, the value portion of the key:value pair that comprises the label on the Google Cloud resource where the usage occurred. For more information about using labels, see Using Labels.
system_labels.key String If system labels are present, the key portion of the key:value pair that comprises the system-generated label on the resource where the usage occurred. See also, Available system labels.
system_labels.value String If system labels are present, the value portion of the key:value pair that comprises the system-generated label on the resource where the usage occurred. See also, Available system labels.
location.location String Location of usage at the level of a country, region, or zone; or global for resources that have no specific location. See also, Regions and Zones for more information.
location.country String When location.location is a country, region, or zone, this field is the country of usage, e.g. US. See also, Regions and Zones for more information.
location.region String When location.location is a country or region, this field is the region of usage, e.g. us-central1. See also, Regions and Zones for more information.
location.zone String When location.location is a zone, this field is the zone of usage, e.g. us-central1-a. See also, Regions and Zones for more information.
cost Float The cost of the usage before any credits. To get the total cost including credits, any credits.amount should be added to cost. See this example query for more information.
currency String The currency that the cost is billed in. For more information, see Local Currency for Billing and Payments.
currency_conversion_rate Float The exchange rate from US dollars to the local currency. That is, cost ÷ currency_conversion_rate is the cost in US dollars.
usage.amount Float The quantity of usage.unit used.
usage.unit String The base unit in which resource usage is measured. For example, the base unit for standard storage is byte-seconds.
usage.amount_in_pricing_units Float The quantity of usage.pricing_unit used.
usage.pricing_unit String The unit in which resource usage is measured, according to the Cloud Billing Catalog API.
credits.name String A description of the credit applied to the Cloud Billing account.
credits.amount Float The amount of the credit applied to the usage. Credits are always negative amounts.
export_time Timestamp A processing time associated with an append of Cloud Billing data. This will always increase with each new export.
See also, Differences between exported data and invoices below.

About labels

You'll see columns for labels in your BigQuery dataset, but for the current release some label values will be empty. Label export data will be populated at different times for different services, depending on when each service provides it.

Available system labels

System labels are key-value pairs for important metadata about the resource that generated the usage. The following system labels are automatically included on applicable usage.

system_labels.key Example system_labels.value Description
compute.googleapis.com/machine_spec n1-standard-1, custom-2-2048 Configuration of the virtual machine. See Machine Types for more information.
compute.googleapis.com/cores for n1-standard-4 this is 4; for custom-2-2048 this is 2 The number of vCPUs available to the virtual machine.
compute.googleapis.com/memory for n1-standard-4 this is 15360 (i.e. 15 GB * 1024 MB/GB); for custom-2-2048 this is 2048 The amount of memory (in MB) available to the virtual machine.

Differences between exported data and invoices

Because Google Cloud services report usage and cost data to Cloud Billing processes at varying intervals, sometimes there is a slight delay (up to a few days) in usage reporting. At the end of a calendar month, late-reported usage might not be included on that month's invoice and instead might roll over to the next month's invoice. However, that same usage includes a timestamp by actual usage date and time when the usage is reported to Cloud Billing processes.

When you query your costs using timestamp fields, your returned data might pick up late-reported usage that was not originally included on the invoice that was generated for the same usage month. As a result, the Cloud Billing data returned might not map directly to that invoice.

Timestamp fields include:

  • usage_start_time
  • usage_end_time
  • export_time

To return Cloud Billing data that maps directly to an invoice, query on invoice.month instead of timestamp fields.

Errors and adjustments

You should be aware that in the unlikely event that your Cloud Billing data contains an error or requires an adjustment, your Cloud Billing data will be appended with data that negates the error. All columns will remain the same, except for:

  • cost
  • credit
  • usage.amount
  • export_time

If you need to determine which data is new, you can do so by querying export_time.

Query examples for daily cost detail data

Daily cost detail query examples


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 is exported to BigQuery.

Field Type Description
export_time Timestamp A processing time associated with an append of Cloud Billing data. This will always increase with each new export.
pricing_as_of_time Timestamp The pricing data applicable to your Cloud Billing account is generated once each day to prepare it for export to BigQuery. This is the daily timestamp of when the pricing data was generated.
billing_account_id String

The Cloud Billing account ID that the pricing is associated with.

For resellers: For pricing associated with a Cloud Billing subaccount, this is the ID of the subaccount, not the ID of the parent reseller Cloud Billing account.

billing_account_name String

The name of the Cloud Billing account that the pricing is associated with.

business_entity_name String The name of the Google service family for the service which offers the SKU. Values include GCP (Google Cloud) or Maps (Google Maps Platform).
service.id String The ID of the Google Cloud service or Google Maps Platform API that reported the Cloud Billing data. For example, 6F81-5844-456A.
service.description String The description of the Google Cloud service or Google Maps Platform API that reported the Cloud Billing data. For example, Compute Engine.
sku.id String The unique identifier for the resource SKU used by the service. For example, 2E27-4F75-95CD. For the full list of SKUs, see Google Cloud SKUs.
sku.description String A human-readable description of the resource SKU used by the service. For example, N1 Predefined Instance Core running in Americas.
sku.destination_migration_mappings Array of Strings The destination SKU(s) names which this SKU is being migrated to.
product_taxonomy Array of Strings List of product categories that apply to the SKU, such as Serverless, Cloud Run, TaskQueue, VMs On Demand, Cores: Per Core, and others.
geo_taxonomy Struct Geographic metadata that applies to the SKU, such as regions and multi-regions like us-east4 the European Union.
geo_taxonomy.type String The type of geographic metadata associated with the SKU. Valid values are:
  • GLOBAL – has no regions
  • REGIONAL – has 1 region
  • MULTI_REGION – has 2 or more regions
geo_taxonomy.regions Array of Strings The Google Cloud regions associated with the SKU. For example, Europe-west2 or US.

A region is a specific geographic place, such as London. A multi-region is a large geographic area, such as the United States, that contains two or more geographic places.

pricing_unit String The shorthand for the unit of usage in which the pricing is specified (such as GiBy.mo).
pricing_unit_description String The human-readable description of the unit of usage (such as gibibyte month).
account_currency_code String The currency that the Cloud Billing account is configured to operate in, using a 3-letter currency code defined in ISO 4217. For more information, see Local currency for billing and automatic payments.
currency_conversion_rate String The exchange rate from US dollars to the local currency the Cloud Billing account is configured to operate in. If the currency of the Cloud Billing account is USD, the exchange rate defaults to 1.0.

If your Cloud Billing costs are billed in a non-USD currency, you can convert your usage costs to USD using this formula: cost ÷ currency_conversion_rate = usage cost in US dollars.

Note that when Google charges in local currency, we convert prices into applicable local currency pursuant to the conversion rates published by leading financial institutions. This includes any surcharge collected for billing in non-USD currency. We use the rates that are in effect on the pricing_as_of_time.

list_price Struct

The list price of the Google Cloud or Google Maps Platform SKUs and SKU pricing tiers, in effect as of the pricing_as_of_time.

list_price contains fields that describe the structure and value of the list price, including:

List prices can be found at list_price.tiered_rates.usd_amount

The list price data is generated and exported for all customers. If your Cloud Billing account has custom, contract pricing, billing-account-specific pricing data is exported as well.

billing_account_price Struct

If you have contract pricing, this is your negotiated SKU price from the contract that is linked to your Cloud Billing account.

billing_account_price contains fields that describe the structure and value of the negotiated pricing for Google Cloud and Google Maps Platform SKUs and SKU pricing tiers. The billing_account_price includes:

Your contracted prices (if applicable to your Cloud Billing account) can be found at billing_account_price.tiered_rates.usd_amount

price_info Struct Background information about the contract price.
price_info.price_reason String

Background information on the origin of the contract price.

Reasons include:

  • FIXED_DISCOUNT: Percentage of discount off the list price price, anchored to the list price as of a fixed date.
  • FLOATING_DISCOUNT: Percentage of discount off the current list price (not anchored to a list price as of a specific date).
  • MIGRATED_PRICE: This is applicable for prices that were migrated from other SKUs.
  • MERGED_PRICE: SKU price after merging from multiple sources. For example, with merged tiers, each individual tier can be from a different source with different discount types.
price_info.discount_percent Numeric For contract pricing that is the result of a percent discount (FIXED_DISCOUNT or FLOATING_DISCOUNT), this is the percentage of the discount used.
price_info.discount_percent_fixed_date Date For contact pricing with a percent discount that is anchored to a specific date (FIXED_DISCOUNT), this is the date used.
price_info.discount_migrated_from String For contract pricing discounts that were migrated from other SKUs (MIGRATED_PRICE), this is the source SKU of the discount. Sometimes when a SKU is split into two, the discount is migrated from the old SKU to the new SKU(s).
aggregation_info Struct Represents the aggregation level and interval for the pricing tiers of a single SKU.
aggregation_info.aggregation_level String The level at which usage is aggregated to compute cost for pricing tiers.

Levels include:

  • ACCOUNT: Indicates a SKU with multi-tiered pricing; SKU usage for the pricing tiers is aggregated across all projects in a single Cloud Billing account.
  • PROJECT: Indicates a SKU with multi-tiered pricing; SKU usage for the pricing tiers is aggregated per project.
  • UNKNOWN_AGGREGATION_LEVEL: Indicates a SKU with single-tier pricing; this is the default value for SKUs with non-tiered pricing.
aggregation_info.aggregation_interval String The interval at which usage is aggregated to compute cost for pricing tiers.

Intervals include:

  • ONE_DAY: Indicates a SKU with multi-tiered pricing; SKU usage for the pricing tiers is aggregated every day.
  • ONE_MONTH: Indicates a SKU with multi-tiered pricing; SKU usage for the pricing tiers is aggregated every month.
  • UNKNOWN_AGGREGATION_INTERVAL: Indicates a SKU with single-tier pricing; this is the default value for SKUs with non-tiered pricing.
tiered_rates Array of Strings Information about the pricing tier including the SKU's price in USD and the SKU's price in the currency the Cloud Billing account is configured to use.

Some SKUs have only one pricing tier. If a SKU has multiple pricing tiers, each pricing tier will appear as a different row. You can identify SKUs with multiple pricing tiers using the tiered_rates.start_usage_amount.

tiered_rates.pricing_unit_quantity Float The SKU's pricing tier unit quantity. For example, if the tier price is $1 per 1000000 Bytes, then this column will show 1000000.
tiered_rates.start_usage_amount Float Lower bound amount for a given pricing tier, in pricing units. For example, a SKU with three pricing tiers such as 0-100 units, 101-1000 units, and 1001+ units, would display three pricing rows, with 0, 101, and 1001 as the [tiered_rates].start_usage_amount values.
tiered_rates.usd_amount Numeric The price for the SKU, in US dollars.
tiered_rates.account_currency_amount Numeric The SKU's tier price converted from USD to the currency the Cloud Billing account is configured to use, using the currency_conversion_rate.

This converted price is calculated using the following formula: tiered_rates.usd_amount * currency_conversion_rate = tiered_rates.account_currency_amount.

When Google charges in local currency, we convert prices into applicable local currency pursuant to the conversion rates published by leading financial institutions. This includes any surcharge collected for billing in non-USD currency. We use the rates that are in effect on the pricing_as_of_time.

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).
    • 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).
  • Tiers operate independently for each Cloud Billing account and do not 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. Currently, there are no SKUs in which usage affects the tiered pricing of another SKU.

  • Be aware that it is 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.

Example pricing queries: 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:

Row sku_id sku_description service_id service_description aggregation_info.
aggregation_level
aggregation_info.
aggregation_interval
tiered_rates.
pricing_unit_quantity
tiered_rates.
start_usage_amount
tiered_rates.
usd_amount
tiered_rates.
account_currency_amount
1 2DA5-55D3-E679 Requests 152E-C115-5142 Cloud Run ACCOUNT MONTHLY 1000000 0 0 0
            1000000 2000000 0.4 0.4

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`, UNNEST (sku_pricing.list_price.tiered_rates) as tier
WHERE DATE(_PARTITIONTIME) = "2020-07-20"
      AND sku.id = "2DA5-55D3-E679"
;

Query results:

Row sku_id sku_description service_id service_description pricing_unit_quantity start_usage_amount usd_amount account_currency_amount
1 2DA5-55D3-E679 Requests 152E-C115-5142 Cloud Run 1000000.0 0.0 0.0 0.0
2 2DA5-55D3-E679 Requests 152E-C115-5142 Cloud Run 1000000.0 2000000.0 0.4 0.4

All query examples for pricing data

Pricing data query examples

Cost and pricing reports available in the Google Cloud Console