Skip to content
Masthead Data

insights table

insights table

Section titled “insights table”

The core insights data table that provides actionable recommendations for cost optimization and data management.

Description

Section titled “Description”

The core insights data table for Masthead’s customers consolidates recommendations for cost optimization by identifying unused and dead-end tables, along with potential actions to reduce storage costs and improve data management efficiency.

Schema

Section titled “Schema”
Column name Data type Description
category STRING Category of the insight. Currently, this field supports only ‘Cost’. Future releases support additional categories.
type STRING Type of insight; valid values include ‘Dead end’, ‘Unused’, ‘Storage billing model’, ‘Compute costs’, and ‘Capacity commitment’.
subtype STRING Specific subtype of the insight, such as ‘Dead end table’, ‘Leaf dead end table’, ‘Unused table’, ‘Storage billing model’, ‘Dead end pipeline’, compute model reassignment, or a capacity commitment change such as ‘Buy commitment’.
project_id STRING Google Cloud project ID
target_resource STRING Name of the target resource
last_updated_time TIMESTAMP When this insight was last updated
overview JSON JSON object with insight details. For details, see the insight types section.
operations JSON Array of recommended actions to address the insight. For details, see the insight types section.

Insight types

Section titled “Insight types”

Cost category

Section titled “Cost category”
Dead end
Section titled “Dead end”
  • Leaf dead end table - Tables that aren’t used by any downstream processes and are at the end of the data lineage
  • Dead end table - Tables that have no downstream dependencies and may be candidates for removal
JSON columns schema

Overview:

{
  "resource_type": "TABLE|VIEW",
  "resource_name": "project.dataset.table",
  "num_bytes": 123456,
  "creation_time": "2025-01-01T00:00:00",
  "last_modified_time": "2025-01-15T00:00:00",
  "cost_30d": 10.5,
  "savings_30d": 10.5
}

Operations:

[
  {
    "action": "drop",
    "resource_type": "TABLE|VIEW",
    "resource_name": "project.dataset.table"
  }
]
  • Dead end pipeline - Pipelines that don’t produce any useful output and that downstream processes don’t use. This feature isn’t yet implemented.
Unused tables
Section titled “Unused tables”
  • Unused table - Tables that users haven’t accessed or queried within the configured time window
JSON columns schema

Overview:

{
  "resource_type": "TABLE|VIEW",
  "resource_name": "project.dataset.table",
  "num_bytes": 123456,
  "creation_time": "2025-01-01T00:00:00",
  "last_modified_time": "2025-01-15T00:00:00",
  "cost_30d": 10.5,
  "savings_30d": 10.5
}

Operations:

[
  {
    "action": "drop",
    "resource_type": "TABLE|VIEW",
    "resource_name": "project.dataset.table"
  }
]
Storage billing model
Section titled “Storage billing model”
  • Storage billing model - Datasets that you can move to a more cost-effective storage billing model based on their update patterns and data compression.
JSON columns schema

Overview:

{
  "resource_type": "dataset",
  "resource_name": "project.dataset",
  "storage_billing_model": "LOGICAL",
  "location": "us-central1",
  "cost_30d": 100.0,
  "savings_30d": 20.0
}

Operations:

[
  {
    "action": "alter",
    "resource_type": "dataset",
    "resource_name": "project.dataset",
    "recommended_storage_billing_model": "PHYSICAL"
  }
]
Compute costs
Section titled “Compute costs”
  • Reassign models in [reservation] reservation - Recommendations for reassigning specific data transformations to optimal BigQuery compute model based on historical slot utilization and query runtime characteristics.
Capacity commitment
Section titled “Capacity commitment”

One row per commitment group whose latest daily recommendation saves money. A commitment group is a reservation admin project, location and edition: target_resource is project:location:EDITION, and project_id is the admin project. To apply these recommendations, see Slot Commitments.

  • Buy commitment - A commitment, or more committed slots next to the active one, costs less than the current reservations.
  • Decrease commitment at renewal - A smaller commitment, or none, costs less. Savings apply after the active commitment ends.
  • Set baseline - The commitment size is right, or a smaller size is already scheduled to renew, but the reservation baselines don’t match it.
JSON columns schema

Overview:

{
  "cost_30d": 20000.0,
  "recommended_cost_30d": 18500.0,
  "savings_30d": 1500.0,
  "period": {"from": "2026-09-23T00:00:00", "to": "2026-10-23T00:00:00"},
  "days": 30,
  "location": "EU",
  "edition": "ENTERPRISE",
  "active_committed_slots": 0,
  "recommended_committed_slots": 500,
  "current_slot_capacity": 0,
  "autoscale_efficiency": 0.9,
  "prices": {"payg": 0.066, "commitment": 0.0528, "active_commitment": 0.0528},
  "cost_per_slot_hr": {"actual": 0.071, "recommended": 0.066},
  "active_commitment_ends_at": null
}

period.to is exclusive. Prices are net, per slot-hour.

Operations:

[
  {
    "action": "CREATE_CAPACITY",
    "commitment": "admin-project:EU.COMMITMENT_ID",
    "edition": "ENTERPRISE",
    "slot_count": 500,
    "plan": "ANNUAL"
  },
  {
    "action": "SET_BASELINES",
    "total_slot_capacity": 500,
    "reservations": [
      {"reservation": "admin-project:EU.reservation-a", "slot_capacity": 0, "autoscale_max_slots": 400, "ignore_idle_slots": false},
      {"reservation": "admin-project:EU.reservation-b", "slot_capacity": 0, "autoscale_max_slots": 100, "ignore_idle_slots": true}
    ]
  }
]

Actions by subtype:

  • Buy commitment: CREATE_CAPACITY (slot_count is the slots to add, all of them when active_committed_slots is 0), then SET_BASELINES.
  • Set baseline: SET_BASELINES only. When active_committed_slots is above total_slot_capacity, a smaller size is already scheduled to renew: lower the baselines on or before active_commitment_ends_at, when the savings start.
  • Decrease commitment at renewal: CHANGE_RENEWAL, then SET_BASELINES. total_slot_capacity is the size after renewal, and 0 means set every baseline to 0.

In SET_BASELINES, slot_capacity and autoscale_max_slots are each reservation’s baseline and autoscale maximum at the end of the previous day, and total_slot_capacity is the total to split across them. In CHANGE_RENEWAL, renew_slots is the size to keep renewing:

{
  "action": "CHANGE_RENEWAL",
  "renew_slots": 50,
  "commitments": [
    {"commitment": "admin-project:US.1234567890", "slot_count": 100, "plan": "ANNUAL", "renewal_plan": "ANNUAL", "ends_at": "2027-09-13T00:00:00"}
  ]
}

Usage examples

Section titled “Usage examples”

Find dead end tables

Section titled “Find dead end tables”
-- Get all dead end tables with their details
SELECT
  subtype,
  project_id,
  target_resource,
  INT64(overview.num_bytes) AS num_bytes,
  STRING(overview.resource_type) AS resource_type,
  last_updated_time
FROM `masthead-prod`.DATASET_NAME.insights
WHERE category = 'Cost'
  AND type = 'Dead end'
ORDER BY num_bytes DESC;
-- Get all dead end tables with their details
SELECT
  subtype,
  project_id,
  target_resource,
  INT64(overview.num_bytes) AS num_bytes,
  STRING(overview.resource_type) AS resource_type,
  last_updated_time
FROM `masthead-prod`.DATASET_NAME.insights
WHERE category = 'Cost'
  AND type = 'Dead end'
ORDER BY num_bytes DESC;

Find unused tables

Section titled “Find unused tables”
-- Get all unused tables sorted by size
SELECT
  project_id,
  target_resource,
  INT64(overview.num_bytes) AS num_bytes,
  STRING(overview.resource_type) AS resource_type,
  last_updated_time
FROM `masthead-prod`.DATASET_NAME.insights
WHERE category = 'Cost'
  AND type = 'Unused'
ORDER BY num_bytes DESC;

Calculate potential storage savings

Section titled “Calculate potential storage savings”
-- Calculate total storage used by dead end and unused tables
SELECT
  type,
  COUNT(*) AS resource_count,
  SUM(SAFE.INT64(overview.num_bytes)) AS total_bytes,
  ROUND(SUM(SAFE.INT64(overview.num_bytes)) / POW(1024, 3), 2) AS total_gb,
  ROUND(SUM(overview.cost_30d), 2) AS monthly_cost_usd,
  ROUND(SUM(overview.savings_30d), 2) AS monthly_savings_usd
FROM `masthead-prod`.DATASET_NAME.insights
WHERE category = 'Cost'
  AND subtype IN ('Dead end table', 'Unused table')
  AND overview.num_bytes IS NOT NULL
GROUP BY type
ORDER BY total_bytes DESC;

Get insights by project

Section titled “Get insights by project”
-- View insights grouped by project
SELECT
  project_id,
  type,
  COUNT(*) AS insight_count,
  SUM(SAFE.INT64(overview.num_bytes)) AS total_bytes
FROM `masthead-prod`.DATASET_NAME.insights
WHERE category = 'Cost'
  AND subtype IN ('Dead end table', 'Unused table')
  AND overview.num_bytes IS NOT NULL
GROUP BY project_id, type
ORDER BY total_bytes DESC;

Storage billing model recommendations

Section titled “Storage billing model recommendations”
-- Get a DDL statement to alter the storage billing model for recommended datasets
SELECT
  *,
  CONCAT('ALTER SCHEMA `', SAFE.STRING(operations[0].resource_name), '` SET OPTIONS(storage_billing_model = "', SAFE.STRING(operations[0].recommended_storage_billing_model),'");') AS ddl
FROM `masthead-prod`.DATASET_NAME.insights
WHERE type = 'Storage billing model'
ORDER BY
  FLOAT64(overview.savings_30d) DESC

Capacity commitment recommendations

Section titled “Capacity commitment recommendations”
-- Get capacity commitment recommendations with their actions
SELECT
  subtype,
  target_resource,
  INT64(overview.active_committed_slots) AS active_committed_slots,
  INT64(overview.recommended_committed_slots) AS recommended_committed_slots,
  FLOAT64(overview.savings_30d) AS savings_30d,
  operations
FROM `masthead-prod`.DATASET_NAME.insights
WHERE category = 'Cost'
  AND type = 'Capacity commitment'
ORDER BY savings_30d DESC;

AI agent skills

Section titled “AI agent skills”

To automate the analysis of cost optimization recommendations programmatically using AI agents, such as Google Antigravity or Claude Code, you can configure pre-built agent skills. These skills help agents query and interpret this insights table to recommend standard or enterprise compute models, alter dataset billing options, or identify dead-end tables.

For a list of available FinOps agent skills, see the AI agent skills guide.

Limitations

Section titled “Limitations”
  • Preview Version: This is an early preview version and the schema may change based on feedback.
  • Data Freshness: Masthead updates insights daily based on usage patterns and lineage analysis.
  • Data Retention: Insights include the most recent data from the account’s configured lookback window, which defaults to 30 days.
  • False Positives: see details in Dead-end and Unused Tables section.

Best practices

Section titled “Best practices”
  1. Review Before Action: Always manually verify insights before taking action on production data.
  2. Test Removals: Consider moving tables to a staging area before permanent deletion.
  3. Check Dependencies: Cross-reference with the lineage table to understand data dependencies.
  4. Monitor Trends: Regular review of insights can help identify data management patterns and opportunities.
  5. Coordinate with Teams: Ensure data owners are aware before removing resources they may depend on.