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.
- See other Limitations that might impact exporting your billing data to BigQuery, such as datasets with customer-managed encryption keys (CMEK) enabled.
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 |
Struct | project contains fields that describe the
Cloud Billing project, such as ID, number, name, ancestry_numbers,
and labels.
|
project.id |
String | The ID of the Google Cloud project that generated the Cloud Billing data. |
project.number |
String | An internally-generated, anonymized, unique identifier for the Google Cloud project that generated the Cloud Billing data. In your support cases and other customer communication, Google will refer to your projects by this project number. |
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 |
Struct | credits contains fields that describe the structure and
value of the credits associated with Google Cloud and Google Maps
Platform SKUs. |
credits.id |
String | If present, indicates that a credit is associated with the product SKU.
credits.id values are either an alphanumeric unique identifier
(for example, 12-b34-c56-d78), or a description of the credit type
(such as
Committed Usage Discount: CPU).
If the |
credits.full_name |
String | The name of the credit associated with the product SKU. This is a
human-readable description of an alphanumeric credits.id .
Examples include
Free trial credit or
Spend-based committed use discount.
|
credits.type |
String | This field describes the purpose or origin of the credits.id .
Credit types include:
|
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. |
adjustment_info |
Struct | adjustment_info contains fields that describe the structure and
value of an adjustment to cost line items associated with a
Cloud Billing account.
|
adjustment_info.id |
string | If present, indicates that an adjustment is associated with a cost line
item. adjustment_info.id is the unique ID for all
the adjustments associated caused by an issue.
|
adjustment_info.description |
string | A description of the adjustment and its cause. |
adjustment_info.type |
String | The type of adjustment. Types include:
|
adjustment_info.mode |
String | How the adjustment was issued. Modes include:
|
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.
Taxes
Starting on September 1, 2020, your daily cost detail data shows your tax liability for each of your projects, instead of as a single line item. If you have queries or visualizations that depend on tax data, you might need to update the queries to account for these changes.
For example, for costs recorded before September 1, your cost detail data looked similar to the following example, which shows a total tax liability of $10.
billing_account_id |
project.id |
cost_type |
cost |
---|---|---|---|
123456-ABCDEF-123456 | example-project | Regular | $60 |
123456-ABCDEF-123456 | test-project | Regular | $40 |
123456-ABCDEF-123456 | [empty] | Tax | $10 |
For costs recorded after September 1, the $10 is broken down to $6 for
example-project
, and $4 for test-project
:
billing_account_id |
project.id |
cost_type |
cost |
---|---|---|---|
123456-ABCDEF-123456 | example-project | Regular | $60 |
123456-ABCDEF-123456 | test-project | Regular | $40 |
123456-ABCDEF-123456 | example-project | Tax | $6 |
123456-ABCDEF-123456 | test-project | Tax | $4 |
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
- Return the total costs on an invoice
- Query examples with labels
- Committed use discount queries
- Additional 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:
|
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:
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
|
list_price |
Struct | The list price of the Google Cloud or Google Maps Platform SKUs
and SKU
pricing tiers, in effect as of the
List prices can be found at
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.
Your contracted prices (if applicable to your Cloud Billing
account) can be found at
|
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:
|
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:
|
aggregation_info.aggregation_interval |
String | The interval at which usage is aggregated to compute cost for
pricing tiers.
Intervals include:
|
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.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:
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
|
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
- Get list prices for a specific SKU
- Get list prices for a specific SKU, and include service description
- Use product taxonomy and geo taxonomy to query SKUs
Limitations
Exporting Cloud Billing data to BigQuery is subject to the following limitations.
- Your BigQuery dataset only reflects Google Cloud billing data incurred from the date you set up Cloud Billing export, and after. That is, Google Cloud billing data is not added retroactively, so you won't see Cloud Billing data from before you enable export.
- Customer-Managed Encryption Keys (CMEK) are not supported when exporting billing data to BigQuery. If you enable CMEK for a dataset, this will prevent Cloud Billing from writing billing data to the appropriate tables within that dataset.
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 Data 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