Optimize Orchestration Compute (BigQuery Reservations)

Purpose

Automatically assign data models from data orchestration to BigQuery slot reservations based on compute complexity and cost optimization strategy. Routes high-compute workloads to on-demand capacities while using reservations pricing for high-traffic jobs.

When to Use

Assigning new models/actions to appropriate compute tiers (reserved vs on-demand)
Rebalancing reservation assignments based on priority changes
Optimizing costs by moving low-priority workloads to on-demand
Ensuring critical pipelines get guaranteed compute resources

Implementation Steps

Step 1: Source Configuration

Extract and apply recommendations from Masthead insights automatically.

Ask user for the BigQuery dataset where Masthead insights are stored. This dataset must contain an
```
insights
```
table with the same schema as
```
masthead-prod.YOUR_DATASET.insights
```
. If the user does not have access, direct them to request access.
Once provided, immediately persist it by appending to the project-level agent instructions file (e.g.
```
.github/copilot-instructions.md
```
, or
```
AGENTS.md
```
— whichever already exists in the project root, or creating if none exist):
```

MASTHEAD_DATASET=YOUR_DATASET

```
On subsequent runs, read this value from the instructions file instead of prompting the user again.
Identify which orchestration technology is in use by querying available recommendations:

bash

bq query --project_id=YOUR_PROJECT --use_legacy_sql=false --format=csv \
"SELECT subtype, COUNT(*) AS recommendation_count
FROM \`masthead-prod.YOUR_DATASET.insights\`
WHERE category = 'Cost'
  AND type = 'Compute costs'
GROUP BY subtype"

If compute model recommendations are not present or all rows share the same technology, infer from the user's project structure (presence of

dbt_project.yml

→ dbt,

definitions/

folder → Dataform, Airflow DAG files → Airflow). Confirm the detected tool with the user before proceeding.

Pull recommendations from
```
masthead-prod.YOUR_DATASET.insights
```
:

bash

bq query --project_id=YOUR_PROJECT --use_legacy_sql=false --format=csv \
"SELECT
  action_name,
  recommended_model,
  cost_30d,
  last_updated_time
FROM \`masthead-prod.YOUR_DATASET.insights\`
WHERE category = 'Cost'
  AND type = 'Compute costs'
  AND subtype = ...
ORDER BY cost_30d DESC" > compute_assignment_candidates.csv

Pull recommendations from
```
masthead-prod.YOUR_DATASET.insights
```
:

bash

bq query --project_id=YOUR_PROJECT --use_legacy_sql=false --format=csv \
"SELECT
  action_name,
  recommended_model,
  cost_30d,
  last_updated_time
FROM \`masthead-prod.YOUR_DATASET.insights\`
WHERE category = 'Cost'
  AND type = 'Compute costs'
  AND subtype = ...
ORDER BY cost_30d DESC" > compute_assignment_candidates.csv

Resolve reservation targets using
```
recommended_model
```
values and reservation edition metadata:

Verify reservation editions using
```
INFORMATION_SCHEMA.RESERVATIONS
```
:

bash

bq query --project_id=RESERVATION_ADMIN_PROJECT --location=US --use_legacy_sql=false --format=csv \
"SELECT
  reservation_name,
  project_id,
  edition,
  slot_capacity
FROM \`region-us\`.INFORMATION_SCHEMA.RESERVATIONS
ORDER BY project_id, reservation_name"

Map

recommended_model = 'ON_DEMAND'

to the config entry where

reservation = 'none'

For all other values (for example
```
ENTERPRISE
```
), choose a reservation whose edition matches
```
recommended_model
```
.
If exactly one matching reservation exists, assign automatically.
If multiple matching reservations exist, ask the user which reservation tag to use.
If no matching reservation exists, ask the user to pick a fallback reservation or create a new matching reservation first.
Ensure an on-demand bucket exists. If missing, create one:

javascript

{
  tag: 'on_demand',
  reservation: 'none',
  actions: []
}

Convert recommendations into assignment lists automatically:

bash

bq query --project_id=YOUR_PROJECT --use_legacy_sql=false --format=csv \
"SELECT
  action_name,
  recommended_model
FROM \`masthead-prod.YOUR_DATASET.insights\`
WHERE category = 'Cost'
  AND type = 'Compute costs'
  AND subtype = ...
ORDER BY cost_30d DESC" > compute_assignment_final.csv

Apply all recommendations based on the detected orchestration tool:

Dataform

Open
```
definitions/_reservations.js
```
.

Replace

on_demand

actions

with all rows where

recommended_model = 'ON_DEMAND'

Replace reserved reservation
```
actions
```
with all rows where
```
recommended_model != 'ON_DEMAND'
```
.
Remove duplicates; keep only actions present in this repo's Dataform graph.
Verify:
```
dataform compile
```
and check for duplicate assignments.
See package reference:
```
@masthead-data/dataform-package
```

dbt

Follow the reservation assignment workflow from
```
masthead-data/dbt-reservations
```
.
Map recommendations to the appropriate dbt model tags or selector targets.
Update the relevant
```
dbt_project.yml
```
or profile configuration per the repo's instructions.

Airflow

Follow the reservation assignment workflow from
```
masthead-data/airflow-reservations
```
.
Map recommendations to DAG or task-level BigQuery reservation labels.
Update the relevant operator configuration per the repo's instructions.

Step 2: Verify Changes

After applying, confirm assignments are non-overlapping and align with the recommendation output. For Dataform:

bash

# Check syntax
dataform compile

# Validate no duplicate assignments
grep -r "\.actions" definitions/_reservations.js

For dbt and Airflow, follow the verification steps in their respective repositories.

Decision Criteria

Factor	Reserved Slots	On-Demand
Priority	High, SLA-bound	Low, flexible
Frequency	Regular, scheduled	Ad-hoc, occasional
Cost Pattern	Predictable usage	Variable, sporadic
Impact	Critical pipelines	Experimental, samples

Key Notes

Each action should appear in only ONE reservation config
File starts with
```
_
```
to ensure it runs first in Dataform queue
Changes take effect on next Dataform workflow run
Package automatically handles global assignment (no per-file edits needed)
Default mode is full auto-apply of recommendations; rely on PR review for validation
The only interactive checkpoint is reservation selection when more than one reservation matches the recommended edition

Package References

Dataform:
```
@masthead-data/dataform-package
```
dbt:
```
masthead-data/dbt-reservations
```
Airflow:
```
masthead-data/airflow-reservations
```

optimize-data-model-compute

NPX Install

Tags

SKILL.md Content

Optimize Orchestration Compute (BigQuery Reservations)

Purpose

When to Use

Implementation Steps

Step 1: Source Configuration

Dataform

dbt

Airflow

Step 2: Verify Changes

Decision Criteria

Key Notes

Package References