Structure of Detailed data export

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

The detailed usage cost data provides all of the information included in the standard usage cost data, along with additional fields that provide granular, resource-level cost data, like a virtual machine or SSD that generates usage. The detailed export includes granular cost information about the following services:

  • AlloyDB for PostgreSQL
  • App Engine
  • BigQuery
  • Bigtable
  • Cloud Data Fusion
  • Cloud Deploy
  • Cloud Run functions
  • Cloud Logging
  • Cloud Run
  • Cloud SQL
  • Cloud Storage
  • Compute Engine
  • Dataflow
  • Dataproc Metastore
  • Firestore and Datastore
  • Google Kubernetes Engine (GKE)
    To view a breakdown of GKE cluster costs in a detailed data export, you must also enable cost allocation for GKE.
  • Managed Microsoft AD
  • Memorystore for Redis
  • Secret Manager
  • Spanner

Identify granular cost data by service

To analyze granular cost information in a detailed export, use the following table to identify the column that contains information about specific resources.

Service description Column How to identify resources
AlloyDB for PostgreSQL service.description and resource.name or resource.global_name

The service.description column contains the name of the service. The resource.name column contains the name provided by the user. The resource.global_name column contains a unique identifier for the resource.

App Engine service.description and resource.name, or resource.global_name

The service.description column contains the name of the service. The resource.name column contains the name provided by the user. The resource.global_name column contains a unique identifier for the resource.

BigQuery service.description and resource.name, or resource.global_name

The service.description column contains the name of the service. The resource.name column contains the name provided by the user. The resource.global_name column contains a unique identifier for the resource.

Bigtable service.description and resource.global_name

The service.description column contains the name of the service. The resource.global_name column contains a unique identifier for the resource.

Cloud Data Fusion service.description and resource.name or resource.global_name

The service.description column contains the name of the service. The resource.name column contains the name provided by the user. The resource.global_name column contains a unique identifier for the resource.

Cloud Deploy service.description and resource.name or resource.global_name

The service.description column contains the name of the service. The resource.name column contains the name provided by the user. The resource.global_name column contains a unique identifier for the resource.

Cloud Run functions service.description and resource.name or resource.global_name

The service.description column contains the name of the service. The resource.name column contains the name provided by the user. The resource.global_name column contains a unique identifier for the resource.

Cloud Logging service.description and resource.global_name

The service.description column contains the name of the service. The resource.global_name column contains a unique identifier for the resource.

Cloud Run service.description and resource.name or resource.global_name

The service.description column contains the name of the service. The resource.name column contains the name provided by the user. The resource.global_name column contains a unique identifier for the resource.

Cloud SQL service.description and resource.global_name

The service.description column contains the name of the service, and the resource.global_name column contains a unique identifier for the resource.

Cloud Storage service.description and resource.global_name

The service.description column contains the name of the service. The resource.global_name column contains a unique identifier for the resource.

Compute Engine service.description and resource.name or resource.global_name

The service.description column contains the name of the service. The resource.name column contains the name provided by the user. The resource.global_name column contains a unique identifier for the resource.

Dataflow service.description and resource.name, or resource.global_name

The service.description column contains the name of the service. The resource.name column contains the name provided by the user. The resource.global_name column contains a unique identifier for the resource.

Dataproc Metastore service.description and resource.name or resource.global_name

The service.description column contains the name of the service. The resource.name column contains the name provided by the user. The resource.global_name column contains a unique identifier for the resource.

Firestore and Datastore service.description and resource.name, or resource.global_name

The service.description column will be App Engine. The resource.name column contains the name provided by the user. The resource.global_name column contains a unique identifier for the resource.

Google Kubernetes Engine (GKE) labels.key

Use the following label keys to filter the resources:

  • goog-fleet-project: Filter your cluster resources by fleet host project, if the cluster is registered to a fleet.
  • goog-k8s-cluster-location: Filter your GKE resources by location.
  • goog-k8s-cluster-name: Filter your GKE resources by cluster.
  • goog-k8s-node-pool-name: Filter your cluster resources by node pool.
  • k8s-namespace: Filter your GKE resources by namespace.
  • k8s-namespace-labels: Filter your GKE resources by fleet namespace label (GKE Enterprise customers only).
  • k8s-label: View all your GKE resources.

To view granular GKE cluster costs in your detailed cost data export, you must also enable cost allocation for GKE.

See example queries for filtering GKE data in BigQuery exports.

Managed Microsoft AD service.description and resource.name or resource.global_name

The service.description column contains the name of the service. The resource.name column contains the name provided by the user. The resource.global_name column contains a unique identifier for the resource.

Memorystore for Redis service.description and resource.global_name

The service.description column contains the name of the service. The resource.global_name column contains a unique identifier for the resource.

Secret Manager service.description, resource.name, and resource.global_name

The service.description column contains the name of the service. The resource.name column contains the name provided by the user. The resource.global_name column contains a unique identifier for the resource.

Spanner service.description and resource.global_name

The service.description column contains the name of the service, and the resource.global_name column contains a unique identifier for the resource.

See examples of querying granular data for your resources.

Detailed usage cost data schema

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

When you use the detailed usage cost data in BigQuery, note the following:

  • When selecting or creating a BigQuery dataset for your detailed usage cost data, you can select any dataset location that is supported for use with Cloud Billing data.
  • When you enable the detailed usage cost data export for the first time in Cloud Billing, if you select a dataset configured to use a multi-region location (EU or US), Cloud Billing data will be available retroactively from the start of the previous month. Data is exported in chronological order. For the initial backfill of exported data, it might take up to five days for your retroactive Cloud Billing data to finish exporting before you start seeing your most recent usage data.
  • If you enable the detailed usage cost data export and select a dataset that's configured to use a supported region location, your Cloud Billing data will be available starting from the date when you enabled the export.
  • If you enabled, disabled, and subsequently re-enabled the detailed usage cost data export, the Cloud Billing data might not be available for the period when data export was explicitly disabled.
  • Learn more about the frequency of the data loads into your BigQuery tables.
  • See other limitations that might impact exporting your billing data to BigQuery, such as datasets with customer-managed encryption keys (CMEK) enabled.
  • Consider the additional data volume that your BigQuery tables might need and the additional cost when enabling detailed usage cost data instead of the standard usage cost data export. The increased granularity of resource-level information can increase the number of rows, which are aggregated in the standard usage cost format. We recommend that you review Control costs in BigQuery for further best practices in managing your BigQuery costs.
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 and 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.

usage_end_time Timestamp The end time of the hourly usage window within which the given cost was calculated. The usage and 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.

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.ancestors Struct

This field describes the structure and value of the resource hierarchy of a cost line item, including projects, folders, and organizations. Ancestors are ordered from node to root (project, folder, then organization).

project.ancestors.resource_name String The relative resource name for each ancestor in the format 'resourceType/resourceNumber'. Using project.ancestors.resource_name will offer a more complete view of project.ancestry_numbers.
project.ancestors.display_name String The name that you created for your resource in your console.
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 multi-region, country, region, or zone; or global for resources don't have a specific location. For more information, see Geography and regions and Google Cloud locations.
location.country String When location.location is a country, region, or zone, this field is the country of usage, e.g. US. For more information, see Geography and regions and Google Cloud locations.
location.region String When location.location is a region or zone, this field is the region of usage, e.g. us-central1. For more information, see Geography and regions and Google Cloud locations.
location.zone String When location.location is a zone, this field is the zone of usage, e.g. us-central1-a. For more information, see Geography and regions and Google Cloud locations.
cost Float The cost of the usage before any credits, to a precision of up to six decimal places. 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.id field is empty, then the product SKU isn't associated with a credit.

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.full_name values are only present for SKUs with an alphanumeric credits.id. If the value of the credits.id is a description of the credit type (such as Committed Usage Discount: CPU), then the credits.full_name field is empty.

credits.type String This field describes the purpose or origin of the credits.id. Credit types include:
  • COMMITTED_USAGE_DISCOUNT: Resource-based committed use contracts purchased for Compute Engine in return for deeply discounted prices for VM usage.
  • COMMITTED_USAGE_DISCOUNT_DOLLAR_BASE: Spend-based committed use contracts purchased for services in exchange for your commitment to spend a minimum amount.
  • DISCOUNT: The discount credit type is used for credits earned after a contractual spending threshold is reached. Note that in the Cloud Billing reports available in the Google Cloud console, the discount credit type is listed as Spending based discounts (contractual).
  • FREE_TIER: Some services offer free resource usage up to specified limits. For these services, credits are applied to implement the free tier usage.
  • PROMOTION: The promotion credit type includes Google Cloud Free Trial and marketing campaign credits, or other grants to use Google Cloud. When available, promotional credits are considered a form of payment and are automatically applied to reduce your total bill.
  • RESELLER_MARGIN: If you're a reseller, the reseller margin credit type indicates the Reseller Program Discounts earned on every eligible line item.
  • SUBSCRIPTION_BENEFIT: Credits earned by purchasing long-term subscriptions to services in exchange for discounts.
  • SUSTAINED_USAGE_DISCOUNT: The sustained use discounts credit type is an automatic discount that you earn for running specific Compute Engine resources for a significant portion of the billing month.
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.
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 values are only present if the cost line item was generated for a Cloud Billing modification. A modification can happen for correction or non-correction reasons. The adjustment_info type contains details about the adjustment, whether it was issued for correcting an error or other reasons.

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 with an issue.
adjustment_info.description String A description of the adjustment and its cause.
adjustment_info.type String

The type of adjustment.

Types include:

  • USAGE_CORRECTION: A correction due to incorrect reported usage.
  • PRICE_CORRECTION: A correction due to incorrect pricing rules.
  • METADATA_CORRECTION: A correction to fix metadata without changing the cost.
  • GOODWILL: A credit issued to the customer for goodwill.
  • SALES_BASED_GOODWILL: A credit issued to the customer for goodwill, as part of a contract.
  • SLA_VIOLATION: A credit issued to the customer due to a service-level objective (SLO) violation.
  • BALANCE_TRANSFER: An adjustment to transfer funds from one payment account to another.
  • ACCOUNT_CLOSURE: An adjustment to bring a closed account to a zero balance.
  • GENERAL_ADJUSTMENT: A general billing account modification.
adjustment_info.mode String

How the adjustment was issued.

Modes include:

  • PARTIAL_CORRECTION: The correction partially negates the original usage and cost.
  • COMPLETE_NEGATION_WITH_REMONETIZATION: The correction fully negates the original usage and cost, and issues corrected line items with updated usage and cost.
  • COMPLETE_NEGATION: The correction fully negates the original usage and cost, and no further usage is remonetized.
  • MANUAL_ADJUSTMENT: The adjustment is allocated to cost and usage manually.
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.
tags Struct

Fields that describe the tag, such as key, value, and namespace.

tags.key String

The short name or display name of the key associated with this particular tag.

tags.value String

The resources attached to a tags.key. At any given time, exactly one value can be attached to a resource for a given key.

tags.inherited Boolean

Indicates whether a tag binding is inherited (Tags Inherited = True) or direct/non-inherited (Tags Inherited = False). You can create a tag binding to a parent resource in the resource hierarchy.

tags.namespace String

Represents the resource hierarchy that define tag key and values. Namespace can be combined with tag key and tag value short names to create a globally unique, fully qualified name for the tag key or tag value.

cost_at_list Float

The list prices associated with all line items charged to your Cloud Billing account.

transaction_type String

The transaction type of the seller. The transaction type might be one of the following:

  • GOOGLE = 1: Services sold by Google Cloud.
  • THIRD_PARTY_RESELLER = 2: Third party services resold by Google Cloud.
  • THIRD_PARTY_AGENCY = 3: Third party services sold by a partner, with Google Cloud acting as the agent.
seller_name String

The legal name of the seller.

Additional fields available to detailed usage cost data export
resource Struct

The fields that describe the structure and value of information relevant to service resources (like a virtual machine or a SSD) that generate usage.

resource.global_name String

A globally unique service identifier for the resource that generated relevant usage.

resource.name String

A service-specific identifier for the resource that generated relevant usage. This can be input generated by the user.

price Struct

Fields that describe the structure and value related to the prices charged for usage.

price.effective_price Numeric

The price charged for usage of the Google Cloud or Google Maps Platform SKUs and SKU pricing tiers. If your Cloud Billing account has custom, contract pricing, this is your billing-account-specific price; otherwise, this is the list price of the SKU or SKU tier.

price.tier_start_amount Numeric

The lower bound number of units for a SKU's pricing tier. For example, a SKU with three pricing tiers such as 0-100 units, 101-1000 units, and 1001+ units, will display three pricing rows for the SKU, with 0, 101, and 1001 in the price.tier_start_amount field representing the starting unit quantity for the SKU's pricing tiers.

Learn more about pricing tiers.
price.unit String

The unit of usage in which the pricing is specified and resource usage is measured (such as gibibyte, tebibyte, month, year, gibibyte hour, gibibyte month, or count). The value in the price.unit field matches the value in the usage.pricing_unit field.

price.pricing_unit_quantity Numeric

The SKU's pricing tier unit quantity. For example, if the tier price is $1 per 1000000 Bytes, then this column will show 1000000.

subscription Struct

Fields that describe your spend-based or resource-based commitments. You can use these fields to analyze your fees for specific commitments.

subscription.instance_id String

The subscription ID linked to a commitment.

Understand standard and detailed usage cost data

The following sections describe the standard and detailed usage cost data exported to BigQuery.

About labels

The cost data for a specific label only shows usage from the date that the label was applied to a resource. For example, if you add the label environment:dev to a Compute Engine VM on January 15, 2024, any analysis for environment:dev includes only the usage for that VM since January 15.

You might also see label data 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.
compute.googleapis.com/is_unused_reservation true; false Indicates usage that was reserved through Zonal Reservations but not used.
storage.googleapis.com/object_state live; noncurrent; soft_deleted; multipart The state of the storage object being charged.

Differences between exported data and invoices

Google Cloud products report usage and cost data to Cloud Billing processes at varying intervals. As a result, you might see a delay between your use of Google Cloud services, and the usage and costs being available to view in Cloud Billing. Typically, your costs are available within a day, but can sometimes take more than 24 hours.

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.

When you query your costs using timestamp fields, your returned data might pick up late-reported usage that wasn't 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

As of September 1, 2020, your usage cost 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 usage cost data looks 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

In the rare event that your Cloud Billing data contains an error or requires an adjustment, it's appended with corrective data. These adjustments fall under one of two categories: billing modifications or corrections.

Billing modifications

Billing modifications appear as separate line items. If you received a billing modification, a new line item in your Cloud Billing export to BigQuery shows the change. The adjustments shown correspond to the invoice, credit memo, and debit memo documents available in the Documents area of the Billing section in the Google Cloud console.

For more information on billing modifications and how they're applied, see Understand memos and adjustments.

Corrections

Corrections appear as new data that negates incorrect data on the source SKUs. In some cases, new data replaces the incorrect charge. All columns in the billing data export will match the original data, except for the following columns:

  • cost
  • credit
  • usage.amount
  • export_time

For example, imagine that you're charged $10 for your usage of SKU A on January 1. On your January invoice (issued in early February), you'll see a charge of $10 for SKU A. However, on February 2, Google Cloud issued a correction against SKU A, reducing the usage cost to $5. You'll receive two additional line items on your February invoice (issued in early March):

  • -$10 for usage on January 1 (negating the original charge)
  • $5 for usage on January 1 (stating the intended charge)

These new items have an adjustment_info column in the billing data export. The original January invoice, showing the overcharge, won't be adjusted. You can verify your charges in your billing data export by viewing your costs by usage_start_time and grouping by Day. In these views, any corrections or charges for late-monetized usage are accumulated, and you don't need to worry about any temporarily incorrect data.

If you want more detailed information on your corrections, view all charges in an invoice month, and look for charges where the usage date occurred before the invoice month. These charges are the results of corrections or late-monetized usage.

The following code sample shows how to create a basic query that returns the total cost of corrections or late-monetized usage:

SELECT
  SUM(cost)
    + SUM(IFNULL((SELECT SUM(c.amount)
      FROM   UNNEST(credits) c), 0))
    AS total
FROM `project.dataset.gcp_billing_export_v1_XXXXXX-XXXXXX-XXXXXX`
WHERE
  invoice.month = '202311' AND
  DATE(TIMESTAMP_TRUNC(usage_start_time, Day, 'US/Pacific')) < '2023-11-01';

For a query example that returns a cost breakdown by service, for invoice charges, where the usage date occurred before the invoice month, see Query cost details to view corrections or late-monetized usage by service for a specified invoice month in "Example queries for Cloud Billing data export."

About promotional credits in custom pricing contracts

If you have a custom pricing contract, you might receive promotional credits to use on Google Cloud as part of the contract. For example, you might receive $1,000 to use on Compute Engine resources. Promotional credits are typically considered a form of payment. When available, promotional credits are automatically applied to reduce your total bill.

The terms of your contract specify whether the promotional credits apply to your costs calculated at the list price of a SKU, or the net price (after discounts).

If your promotional credits apply to costs that are calculated at the list price, in the Cost table report, there's a service called Invoice, with a SKU called Contract billing adjustment. This SKU adjusts your credits so that they apply to the costs at list price. To see the usage that the adjustment is for, query the system.labels columns. The key in system.labels.key is cloud-invoice.googleapis.com/sku_id, and the value in system.labels.value contains the SKU ID that the credit and the adjustment applied to.

About tags

Tags are resources in the form of key-value pairs that can be attached to resources directly or through inheritance. You can use tags to perform chargebacks, audits, and other cost allocation analysis. You can also use tags and conditional enforcement of policies for fine-grained control across your resource hierarchy.

Tags have a robust permissions model and can support inheritance, centralized management, nomenclature standardization, and policy engine integration, while labels are a separate tool that allow you to annotate resources.

Tags data appears in BigQuery exports for Resources, Projects, Folders, and Organizations.

Available tags

The Standard costs and Detailed costs exports for Resources, Projects, Folders, and Organizations include these fields for tags data: Tags Key, Tags Value, Tags Inherited, and Tags Namespace.

Resource-level tags in the Cloud Billing data export are available for the following resources:

  • AlloyDB for PostgreSQL clusters, instances, and backups
  • Artifact Registry repositories
  • Cloud Run services and jobs
  • Cloud Storage buckets
  • Compute Engine instances
  • Memorystore for Redis instances
  • Secret Manager secrets
  • Spanner instances

Tags limitations

  • Tags might take up to an hour to propagate to BigQuery exports. If a tag has been added or removed within an hour, or if a resource has existed for less than an hour, it might not appear in the export.

Detailed usage cost query examples

This section provides examples of how to query the Cloud Billing detailed usage cost data exported to BigQuery.

Because the detailed usage cost schema includes all of the fields from the standard usage cost schema, the query examples provided for the standard data exported to BigQuery also work with the detailed data that's exported. The standard query examples aren't written to retrieve any of the resource-level information that's provided with the detailed usage cost export option. When creating queries for the detailed data, you can use a standard query example as a template, update the Table name, and add any of the fields that are available in the detailed usage cost schema.

Common values used in the example detailed cost queries

The query examples in this section use the following value for Table name: project.dataset.gcp_billing_export_resource_v1_XXXXXX_XXXXXX_XXXXXX

Return the resource-level costs on an invoice

The following queries demonstrate two ways of viewing resource-level cost and credit values on an invoice using exported billing data.

  • The total field directly sums the floating point cost and credit values, which can result in floating point rounding errors.
  • The total_exact field converts costs and credit values to micros before summing, then converts back to dollars after summing, avoiding the floating point rounding error.

Sum costs for each resource, per invoice

This query shows the invoice total for each resource.name per month, as a sum of regular costs, taxes, adjustments, and rounding errors. Any costs not associated with a resource-level item are aggregated under the name null for the month.

Standard SQL

SELECT
  invoice.month,
  resource.name,
  SUM(cost)
    + SUM(IFNULL((SELECT SUM(c.amount)
                  FROM UNNEST(credits) c), 0))
    AS total,
  (SUM(CAST(cost * 1000000 AS int64))
    + SUM(IFNULL((SELECT SUM(CAST(c.amount * 1000000 as int64))
                  FROM UNNEST(credits) c), 0))) / 1000000
    AS total_exact
FROM `project.dataset.gcp_billing_export_resource_v1_XXXXXX_XXXXXX_XXXXXX`
GROUP BY 1, 2
ORDER BY 1 ASC, 2 ASC
;

For example, the result of the preceding query might be:

Row month name total total_exact
1 201901 null $1005.004832999999984 $1005.00
2 201901 backend1 $781.8499760000028 $781.85
3 201902 null $953.0034923645475983 $953.03
4 201902 backend1 $992.3101739999999717 $992.31
5 201902 bitnami-launchpad-wordpress-1-wordpress $1.2817819999999998 $1.28

Return details by cost type for each resource, per invoice month

This query shows the totals for each cost_type for each resource.name per month. Cost types include regular costs, taxes, adjustments, and rounding errors. Any costs not associated with a resource-level item are aggregated under the name null for the month.

Standard SQL

SELECT
  invoice.month,
  cost_type,
  resource.name,
  SUM(cost)
    + SUM(IFNULL((SELECT SUM(c.amount)
                  FROM   UNNEST(credits) c), 0))
    AS total,
  (SUM(CAST(cost * 1000000 AS int64))
    + SUM(IFNULL((SELECT SUM(CAST(c.amount * 1000000 as int64))
                  FROM UNNEST(credits) c), 0))) / 1000000
    AS total_exact
FROM `project.dataset.gcp_billing_export_resource_v1_XXXXXX_XXXXXX_XXXXXX`
GROUP BY 1, 2, 3
ORDER BY 1 ASC, 2 ASC, 3 ASC
;

For example, the result of the preceding query might be:

Row month cost_type name total total_exact
1 201901 regular null $1000.501209987994782 $1000.50
2 201901 rounding_error null –$0.500489920049387 –$0.50
3 201901 tax null $10.000329958477891 $10.00
4 201901 adjustment null –$5.002572999387045 –$5.00
5 201901 regular backend1 $410.998795012082947 $411.00
2 201901 rounding_error backend1 –$0.2404900489920378 –$0.24
3 201901 tax backend1 $4.105840329977189 $4.11

Get a breakdown of Google Kubernetes Engine (GKE) cluster costs

This section provides examples of filtering GKE cluster costs in your BigQuery export reports. To learn more about GKE cluster costs, visit View breakdown of cluster costs.

Filter GKE costs

The following example queries show you how to filter and group your GKE costs for supported resource types by cluster name, namespace, and label.

GKE cluster costs before credits

SELECT
  SUM(cost) AS cost_before_credits,
  labels.value AS cluster_name
FROM  `project-ID.dataset.gcp_billing_export_resource_v1_XXXXXX-XXXXXX-XXXXXX`
LEFT JOIN UNNEST(labels) as labels
  ON labels.key = "goog-k8s-cluster-name"
GROUP BY labels.value
;

GKE costs after credits by namespace

SELECT
  labels.value as namespace,
  SUM(cost) + SUM(IFNULL((SELECT SUM(c.amount) FROM UNNEST(credits) c), 0)) AS cost_after_credits,
FROM `project-ID.dataset.gcp_billing_export_resource_v1_XXXXXX-XXXXXX-XXXXXX`
LEFT JOIN UNNEST(labels) as labels
  ON labels.key = "k8s-namespace"
GROUP BY namespace
;

GKE costs by SKU

SELECT
  project.id AS project_id,
  labels.value AS cluster_name,
  sku.id AS sku_id,
  sku.description AS sku_description,
  SUM(cost) AS cost
FROM `project-ID.dataset.gcp_billing_export_resource_v1_XXXXXX-XXXXXX-XXXXXX`
JOIN UNNEST(labels) AS labels
  ON labels.key = "goog-k8s-cluster-name"
GROUP BY
  cluster_name, project_id, sku_description, sku_id
;

Query examples with tags

The following examples illustrate ways to query your data with tags.

Calculate costs by invoice month with tags

The following query demonstrates how you can use return costs by invoice month for the cost_center tag.

SELECT
  invoice.month AS invoice_month,
  tag.value AS cost_center,
  ROUND((SUM(CAST(cost AS NUMERIC))
    + SUM(IFNULL((SELECT SUM (CAST(c.amount AS NUMERIC))
                  FROM UNNEST(credits) AS c), 0))), 2)
    AS net_cost
FROM `project-ID.dataset.gcp_billing_export_resource_v1_XXXXXX-XXXXXX-XXXXXX`, UNNEST(tags) AS tag
WHERE tag.key = "cost_center" AND tag.namespace = "821092389413"
GROUP BY invoice.month, tag.value
ORDER BY invoice.month, tag.value;

For example, the result of the preceding query might be:

Row invoice_month cost_center net_cost
1 202208 android_mobile_apps 9.93
2 202208 ios_mobile_apps 9.93
3 202209 android_mobile_apps 25.42
4 202209 ios_mobile_apps 25.4
5 202209 personalization 16.08

View costs of untagged resources

This query shows the invoice total for untagged resources, grouped by invoice month.

SELECT
 invoice.month AS invoice_month,
 ROUND((SUM(CAST(cost AS NUMERIC))
   + SUM(IFNULL((SELECT SUM(CAST(c.amount AS NUMERIC))
                 FROM UNNEST(credits) AS c), 0))), 2)
   AS net_cost
FROM
 `project-ID.dataset.gcp_billing_export_v1_XXXXXX-XXXXXX-XXXXXX`
WHERE "color" NOT IN (SELECT key FROM UNNEST(tags))
GROUP BY invoice_month
ORDER BY invoice_month;

For example, the result of the preceding query might be:

Row invoice_month net_cost
1 202202 0
2 202203 16.81
3 202204 54.09
4 202205 55.82
5 202206 54.09
6 202207 55.83
7 202208 31.49

Cost and pricing reports available in the Google Cloud console