How to bring the project id info into the databricks billing usage table on GCP

Danish11052000
Contributor

Hi,

We are using Databricks on GCP and are currently analyzing costs using the billing usage/system tables.

One challenge we are facing is that the billing usage data provides usage and cost information, but we are unable to identify the associated GCP Project ID for each usage record.

Our goal is to:

  • Map Databricks usage and costs to specific GCP projects.
  • Generate project-level chargeback/showback reporting.
  • Understand which GCP project is consuming the most Databricks resources.

Has anyone implemented a solution for this?

Any guidance, examples, or best practices would be greatly appreciated.

Satyasai
Contributor

The reason you are not seeing the GCP Project ID directly in the system.billing.usage table is that Databricks system tables track DBU usage at the workspace, cluster, and workload level rather than underlying Google Cloud project infrastructure.

As such, Databricks workspaces in GCP are typically deployed either within a specific customer-managed VPC / GCP Project or across multiple projects and teams successfully implement showback/chargeback using a few proven strategies.

Solution 1: Workspace to Project Mapping Table (Easiest)

If your architecture utilizes specific Databricks Workspaces to specific GCP Projects (i.e., Project A hosts Workspace A, Project B hosts Workspace B), create a reference mapping table in Unity Catalog.

Create a Mapping Table and Join with System Billing Usage

balajij8
Esteemed Contributor III

@Danish11052000 System tables does not generally carry details such as GCP Project ID. You can create an static mapping table (GCP Project id, other details, workspace id) in Unity Catalog and use the info with the system tables if your deployment is like every Databricks workspace maps to exactly one GCP project. You can load this mapping using Terraform deployment scripts, since each workspace's resources land in a specific project. You can join the lookup table to system.billing.usage on workspace_id, filtering for cloud = 'GCP' and record_type = 'ORIGINAL'. You can also join system.billing.list_prices to convert the DBUs to dollars, giving the correct project level chargeback report to see which GCP project is consuming the most resources.


You can rely on sub-workspace attribution via custom tags if you have multiple GCP projects sharing a single workspace. You can apply a custom tag like gcp_project_id = <project> to the clusters, SQL warehouses and pools so Databricks propagates it into the custom_tags column of system.billing.usage. To prevent untagged usage, enforce this through cluster policies so computes cannot even be created without it. For the serverless, you can leverage serverless usage policies to auto-tag the usage and it lands in the custom_tags column.

You can extract custom_tags['gcp_project_id'] in the queries for the per-project cost breakdowns and that tags only apply to usage incurred after the tag is set - old records will remain untagged and hence get these policies in place quickly.

data_pulse
Contributor

@Danish11052000 

A feasible workaround is to use the Databricks Account Workspaces API to fetch the GCP project_id alongside workspace_id. The workspace API schema exposes it under cloud_resource_container.gcp.project_id. Reference

url = f"https://accounts.gcp.databricks.com/api/2.0/accounts/{ACCOUNT_ID}/workspaces"
workspaces = requests.get(
    url,
    headers={"Authorization": f"Bearer {TOKEN}"}
).json()

rows = [
    (
        str(w["workspace_id"]),
        w.get("workspace_name"),
        w.get("cloud_resource_container", {})
         .get("gcp", {})
         .get("project_id")
    )
    for w in workspaces
]
df = spark.createDataFrame(
    rows,
    ["workspace_id", "workspace_name", "project_id"]
)

data_pulse_0-1790669526481.png

Then persist in a mapping table like workspace_project_map and enrich billing as:

SELECT
  u.*,
  m.project_id
FROM system.billing.usage u
LEFT JOIN finops.workspace_project_map m
  ON u.workspace_id = m.workspace_id

At present, system.access.workspaces_latest provides workspace metadata such as workspace_id, workspace_name, workspace_url, and status but doesn't expose the GCP project_id as mentioned in docs too.

So the practical approach for now could be API → mapping table → billing enrichment, while also watching for Databricks to add project_id directly to a system table in the future.

anuj_lathi
Databricks Employee
Databricks Employee

There's no GCP project ID column in system.billing.usage, but you don't need one. On GCP, each Databricks workspace is deployed into a specific GCP project, so you map workspace_id to project ID yourself and join. For finer granularity within a workspace, use tags.

Why the workspace is the key

system.billing.usage is account-wide and global, so you see usage for every workspace from whichever region you query [1]. Each record carries workspace_id, cloud, sku_name, usage_quantity, custom_tags, and usage_metadata, but nothing project-specific [2]. workspace_id is the natural bridge to a project.

Step 1: Build a workspace-to-project mapping table

Pull your workspace list from the account console or the Account API/CLI. On GCP, the workspace object includes the GCP project it was created in. Verify the exact field name in your account's output. Then land it in a small Delta table that you maintain:

CREATE TABLE IF NOT EXISTS governance.finops.workspace_gcp_project (
  workspace_id    STRING,
  workspace_name  STRING,
  gcp_project_id  STRING,
  business_unit   STRING,
  cost_center     STRING
);

Workspaces change rarely, so a scheduled job that refreshes this from the API works well. You can also load it by hand and add chargeback attributes like cost center, which the API won't give you.

Step 2: Join usage to the mapping and to list prices

SELECT
  m.gcp_project_id,
  m.cost_center,
  u.usage_date,
  u.sku_name,
  SUM(u.usage_quantity)                          AS dbus,
  SUM(u.usage_quantity * p.pricing.default)      AS list_cost_usd
FROM system.billing.usage u
JOIN system.billing.list_prices p
  ON  u.sku_name = p.sku_name
  AND u.cloud    = p.cloud
  AND u.usage_start_time >= p.price_start_time
  AND (p.price_end_time IS NULL OR u.usage_start_time < p.price_end_time)
JOIN governance.finops.workspace_gcp_project m
  ON u.workspace_id = m.workspace_id
WHERE u.cloud = 'GCP'
  AND u.usage_date >= date_sub(current_date(), 30)
GROUP BY ALL
ORDER BY list_cost_usd DESC;

Sum usage_quantity rather than filtering on record_type, so that corrections (retractions and restatements) net out properly [2]. Also note that list_prices gives list price. If you have a discounted contract rate, apply that factor or use your invoice data for true cost.

Step 3: Go finer than the workspace

If one workspace serves several teams or projects, the workspace-to-project join isn't enough. Two options:

  • Tag your compute. Apply custom tags to clusters, jobs, pools, and warehouses (for example gcp_project, team, cost_center). They flow into custom_tags on the usage table [2]:

    SELECT custom_tags['team'] AS team, SUM(usage_quantity) AS dbus
    FROM system.billing.usage
    WHERE cloud = 'GCP' AND usage_date >= date_sub(current_date(), 30)
    GROUP BY ALL;
    

    Enforce tags with compute policies so they aren't optional.

  • Use usage_metadata. Fields like cluster_id, job_id, warehouse_id, and dlt_pipeline_id let you attribute usage to a specific asset and then to an owner [2]. For serverless, tagging is done through budget policies rather than cluster tags.

Don't forget the GCP infrastructure side

The system tables only cover Databricks charges (DBUs). The VM, disk, and network costs for classic compute are billed by Google. That data lives in your GCP Billing export to BigQuery, where project.id is native and resource labels are available. Databricks tags on classic compute show up as GCP labels, so a tag like cost_center lets you join the two sides for a full chargeback view: DBUs from system tables plus infrastructure from BigQuery.

Recommendation

Start with the workspace-to-project mapping table and the query above. That gets you a per-project showback within a day. Then enforce tagging through compute policies so you can allocate within shared workspaces. Finally, join in the BigQuery billing export using the same tags if you want total cost per project.

References

[1] System tables reference | Databricks on AWS — https://docs.databricks.com/aws/en/admin/system-tables
[2] Billable usage system table reference | Databricks on AWS — https://docs.databricks.com/aws/en/admin/system-tables/billing

Anuj Lathi
Solutions Engineer @ Databricks