Snowflake Optimizing Clustering Keys¶
Problem Statement¶
Decide which frequently run queries are worth optimizing with clustering keys and materialized views, balancing performance gains against compute and storage costs.
Full example scenario
As a data engineer at a software company, you are tasked with optimizing the performance of a critical application running on Snowflake. The database handles a large volume of read and write operations, and the current table structures and clustering keys are not optimized, leading to slow query performance and higher costs associated with compute time and storage. Your goal is to design the most efficient clustering strategy and optimize materialized views to enhance query performance while minimizing the impact on storage and compute costs. You have a set of queries that are frequently run against the database, each with a different frequency and selectivity. Which queries should be optimized, in order to maximize the total query performance improvement — calculated as the sum of the chosen queries’ performance improvements divided by the total number of queries?
The total additional compute cost introduced by the clustering keys and materialized views must not exceed 20 units.
The total storage used by all optimizations must not exceed 45 GB.
The total query performance improvement must be at least 20%.
The query data can be found in
snowflake.csv, with the columnsQuery_ID,Frequency,Selectivity (%),Compute_Maintenance_Cost,Storage_Cost (GB),Performance_Improvement (%).
What an optimal solution looks like
Both axes are running totals: cumulative storage spent (x) against cumulative performance gained (y), adding one query at a time in order of performance-per-GB. The dashed line adds greedily and stops early; the solid line is the true optimum.
Order of addition — greedy: Q8 → Q3 → Q4 → Q9 → Q2 → Q1, then stuck with 6 GB spare and nothing left small enough to use it. Optimum: Q8 → Q3 → Q9 → Q2 → Q1 → Q5, landing exactly on the 45 GB wall.
Both paths agree on the first two picks (Q8, then Q3 — the two most efficient queries per GB). They part ways on the third pick: greedy takes Q4 next because its ratio (17/6 ≈ 2.83) beats what's left, but that choice leaves only 6 GB of headroom — too little for anything else, including Q5 (12 GB, the single biggest remaining gain at +30%). The optimum skips Q4, keeps the room, and ends up fitting Q5 in after all. Sorting by ratio points roughly the right way, but only a full combinatorial search finds the exact mix that spends the whole budget.
Built with Modeler¶
The problem above is stated as a single shot: you hand over the whole problem and get a model back. Modeler takes the other route — you start with the business question you actually have, and it works with you until the problem is pinned down, the data is in, the code runs, and the answer holds up when the problem changes.
The tabs below walk the same query-optimization problem through that route, one Modeler capability at a time.
Instead of writing the whole prompt up front, you start with the business question you actually have. Modeler searches the knowledge base, asks one clarifying question at a time — always the most important one first — and tells you which parts of the model are still missing. The options it offers come from examples in the knowledge base and from how similar problems are usually modeled in your industry, so you can answer with a number instead of prose.
I’m a data engineer at a software company. Our Snowflake database is getting hit with a lot of read/write traffic and query performance is suffering — compute and storage costs are creeping up too. We run a set of queries repeatedly, and I think if we add clustering keys / materialized views for the right subset of them we could meaningfully speed things up, but every one we optimize adds ongoing compute and storage overhead. Can you help me build a model to decide which queries are worth optimizing?
This is a binary selection problem — a “query optimization knapsack” that trades off performance benefit against resource overhead. Before anything else I need to know whether clustering keys and materialized views are two separately selectable techniques per query, or one combined choice, since that decides what the decision variable even represents.
Great question. This is a classic binary selection problem — think of it as a “query optimization knapsack” where you want to maximize the performance (or cost) benefit of optimizing a subset of queries while staying within compute and storage budgets. Let me ask clarifying questions one at a time to build a complete specification.
To start: when you say “optimize” a query, you mentioned clustering keys and materialized views as two distinct techniques. Can a single query benefit from both at the same time (i.e., both a clustering key and a materialized view applied independently and additively), or is it an either-or choice per query?
It’s an either-or choice per query — each query gets one combined “optimization package” (clustering key plus materialized view together) or nothing, not two separate techniques applied independently. So think of each query as a single yes/no decision.
Here are the constraints I know about: the total extra compute-maintenance cost across everything we optimize can’t exceed 20 units, total extra storage can’t exceed 45 GB, and we need the overall query performance improvement to be at least 20%. Performance improvement is measured as the sum of the chosen queries’ Performance_Improvement values, divided by the total number of queries we have (not just the ones we optimize) — and we want to maximize that ratio.
One more thing — we also want to guarantee at least 3 queries get optimized. A plan that technically maximizes performance by picking just one or two “best” queries feels too thin a rollout for a database this size.
That settles the decision structure (one binary per query) and gives me three numeric constraints plus a volunteered fourth — a rollout floor of at least 3 queries. Recording all of it. What is still open is how the team wants the result reported back, which shapes what the output section of the specification should promise.
Good. Now let me ask about the output. When the model produces a solution, what does your team need to see? For example:
Just the list of query IDs selected for optimization
The list plus the achieved performance improvement ratio and total compute/storage usage
All of the above plus a per-query breakdown (e.g., the individual performance improvement and cost of each selected query)
Something else entirely (e.g., a report format, a ranked list)
Which of these best matches what you need, or would you combine a few of them?
Rather than defaulting to something reasonable, Modeler asks one more time — this last open item (the output format) gets settled together with the data upload in the next tab. Modeler updates the specification after every answer, even while it is still incomplete, so you can always see what it has understood and what it is still waiting on.
The remaining open item was how results should be reported back. Rather than
settle that with more typed-out prose, Modeler asked for the actual query
data as a file — so it can read the real shape of the numbers instead of
having them summarized at it. Attach snowflake.csv to the conversation
and it reads the real structure of your data: entity names, column headers,
units and all.
Query_ID,Frequency,Selectivity (%),Compute_Maintenance_Cost,Storage_Cost (GB),Performance_Improvement (%)
Q1,5000,10,3,10,25
Q2,3000,15,4,8,20
Q3,2000,20,2,5,15
Q4,2000,25,3,6,17
Q5,1000,30,5,12,30
Q6,4000,25,6,15,35
Q7,4000,30,7,16,36
Q8,1500,5,1,3,10
Q9,2500,10,2,7,18
snowflake.csv attached
Here’s the actual data — one row per query, columns are Query_ID, Frequency (times run per day), Selectivity (%), Compute_Maintenance_Cost, Storage_Cost (GB), and Performance_Improvement (%). Compute and storage costs are already in the same units as the budgets I mentioned (compute units and GB), and Performance_Improvement is a percentage per query, same units as the 20% floor.
Nine rows, one per query, matching a flat list rather than a matrix — consistent with a single binary decision per query. Compute, storage and performance units all line up with what is already in the specification, so nothing needs rescaling. Two columns, though — Frequency and Selectivity — are not referenced anywhere in the objective or constraints the user has described so far. Before finalizing, that gap is worth surfacing rather than silently ignoring: it changes what “performance improvement” actually optimizes for.
The specification is updated with your data and all constraints. One final clarifying question before we move to implementation:
The dataset includes Frequency (executions per day) and Selectivity (%) for each query. Right now they are not used in the objective or any constraint — the objective only uses Performance_Improvement. Would you like to keep it that way (pure Performance_Improvement maximization), or should Frequency and/or Selectivity influence the objective — for example, by weighting each query’s performance improvement by how often it runs (so a high-frequency query with a 25% improvement counts more than a low-frequency query with the same 25%)?
Keeping it pure for now — and logging frequency-weighting as an explicit, revisitable item rather than dropping it silently — is what actually closes the specification. That same answer also told Modeler to go ahead and build, which the Cloud solve tab picks up next.
Where your files live
Everything in the conversation — the file you attached and every artifact Modeler generates from it — lands in the conversation workspace as an attachment. Each one opens in a side panel next to the chat, so you can read the specification while you keep answering questions, or scroll the code while you ask for a change. From that panel you can copy the contents or download the file.
By the end of this conversation the workspace holds six items:
Attachment |
What it is |
|---|---|
|
The query data you attached. |
|
The specification, rewritten after every answer. |
|
The |
|
The |
|
Output of the test runs, including the solver logs. |
|
The mathematical formulation, generated on request. |
Starting from files you already have
The same upload path runs in reverse. Attach the files of an existing
optimization project — a specification, a model script, a test file, or any
subset — and ask Modeler to check the project. It completes the
specification where it is thin (asking clarifying questions, as above), fills
in a missing implementation or test suite, reconciles code that has drifted
from the specification, and runs the tests. Point it at a model written for
another solver or another language and it recovers a specification from that
file, asks you to confirm it, then reimplements the model in gurobipy.
The specification is the artifact the questions are building toward, and the one everything else is generated from. Modeler rewrites it after every answer — even while it is still incomplete — so it doubles as the running record of what has been agreed and what is still open. It follows a fixed seven-section structure and is written entirely in business terms: no mathematics, so the person who owns the problem can read it and say whether it is right.
1. Overview
A software company runs a set of recurring queries against a Snowflake database. Read/write traffic and query volume are causing performance degradation and rising compute and storage costs. Each query can receive a single combined “optimization package” (clustering key plus materialized view together) or remain unoptimized. The model decides which queries to optimize so that the overall query performance improvement is maximized, while staying within compute maintenance and storage budgets, and ensuring a minimum number of queries are optimized to support a meaningful rollout.
2. Objective
Maximize the total query performance improvement ratio: the sum of the selected (optimized) queries’ performance improvement percentages, divided by the total number of queries in the dataset.
3. Constraints
C1 — Compute maintenance budget. The total compute maintenance cost of all selected queries must not exceed 20 compute units, so the extra maintenance work the optimizations create stays within what the team can absorb.
C2 — Storage budget. The total storage cost of all selected queries must not exceed 45 GB, so clustering keys and materialized views don’t grow storage spend unchecked.
C3 — Minimum rollout. At least 3 queries must be selected for optimization, so the plan is a meaningful rollout rather than one or two cherry-picked wins.
4. Input
Entity |
Fields |
Values |
|---|---|---|
Query |
query id, frequency (runs/day), selectivity (%), compute maintenance cost, storage cost (GB), performance improvement (%) |
9 rows, supplied in |
Budget parameters |
max compute, max storage, min queries optimized |
20 units, 45 GB, 3 |
Optimizing every query at once would cost 33 compute units and 82 GB of storage — well over both budgets — so the model has to leave a real majority of the candidate queries unoptimized.
5. Output
The selected query IDs.
The achieved performance improvement ratio.
Total compute and storage usage against their budgets.
A per-query breakdown (performance improvement, compute cost, storage cost) for every selected query.
6. Test scenarios
ID |
Scenario |
Acceptance criteria |
|---|---|---|
T1 |
Baseline — all 9 queries, default budgets. |
Optimal. Selects {Q1, Q2, Q3, Q5, Q8, Q9}; ratio 13.11%; compute 17 of 20; storage 45 of 45 (binding). |
T2 |
Compute budget tightened to 4 units — cheapest 3 queries by compute already cost 5. |
Infeasible — C3 cannot be met within the compute budget. |
T3 |
Budgets relaxed to 1,000 / 1,000 — no resource pressure at all. |
Optimal. All 9 queries selected; ratio 22.89%; compute 33, storage 82. |
T4 |
Minimum viable dataset: exactly 3 queries, ample budget. |
Optimal. All 3 selected (C3 forces it); ratio 25.00%. |
T5 |
Storage budget tightened to 2 GB — below the cheapest query’s 3 GB. |
Infeasible — C3 cannot be met within the storage budget. |
T6 |
Data perturbation: Q8’s performance improvement drops from 10% to 9%, all else unchanged. |
Optimal, same selected set as T1 — {Q1, Q2, Q3, Q5, Q8, Q9} is unchanged; ratio drops slightly to 13.00%. Storage geometry, not Q8’s own value, is what keeps it selected. |
T7 |
Naive baseline: greedy top-3 queries by raw performance improvement alone (Q7, Q6, Q5). |
Greedy is budget-feasible but ratio-inferior: 11.22% vs. the optimizer’s 13.11% — a 16.8% relative gain, on twice as many queries optimized. |
7. Out of scope
Frequency-weighted objective. Weighting each query’s performance improvement by how often it runs (Frequency), so high-traffic queries count more than low-traffic ones with the same raw improvement. Explicitly deferred to a future version.
Selectivity-weighted objective. Using query selectivity to adjust the benefit of optimization. Also deferred.
Multiple optimization techniques per query. Applying clustering keys and materialized views as independent, separately selectable choices. Each query is a single yes/no decision in this version.
A hard minimum performance-improvement floor. A constraint requiring the achieved ratio to be at least 20% was part of the original ask, but is infeasible against the current data and budgets — the best achievable ratio is about 13.1%. Removed and flagged for revisiting if budgets or data change.
With the specification settled, Modeler writes the gurobipy implementation.
This is the version that came out of the run-and-fix loop in the Cloud
solve tab, after Modeler ran the tests and fixed what it found — not the
first draft.
"""
Snowflake Query Optimization Model
-----------------------------------
Selects which recurring Snowflake queries to optimize (clustering key +
materialized view package) to maximize the overall query performance
improvement ratio, subject to compute, storage, and rollout constraints.
"""
import gurobipy as gp
from gurobipy import GRB
import pandas as pd
# ---------------------------------------------------------------------------
# Optimizer class
# ---------------------------------------------------------------------------
class QueryOptimizer:
"""
Binary optimization model that decides which Snowflake queries should
receive an optimization package (clustering key + materialized view).
Objective
---------
Maximize the average performance improvement ratio across all queries,
defined as sum(selected queries' Performance_Improvement) / total_queries.
Constraints
-----------
C1 - Total compute maintenance cost <= max_compute
C2 - Total storage cost <= max_storage
C3 - Number of optimized queries >= min_queries_optimized
"""
def __init__(self, env):
"""Initialize optimizer with a Gurobi environment."""
self.env = env
self.model = None
self.queries_df = None
# Budget parameters with defaults
self.max_compute = 20.0
self.max_storage = 45.0
self.min_queries_optimized = 3
# Internal references
self._x = {} # decision variables keyed by Query_ID
def __enter__(self):
"""Enter context manager."""
return self
def __exit__(self, exc_type, exc_val, exc_tb):
"""Dispose Gurobi model on exit."""
if self.model is not None:
self.model.dispose()
return False
# ------------------------------------------------------------------
# Data assignment
# ------------------------------------------------------------------
def set_data(self, queries_df: pd.DataFrame,
max_compute: float = 20.0,
max_storage: float = 45.0,
min_queries_optimized: int = 3):
"""
Load query data and budget parameters.
Parameters
----------
queries_df : DataFrame with columns
Query_ID, Compute_Maintenance_Cost, Storage_Cost_GB,
Performance_Improvement_pct
max_compute : float — compute maintenance budget (compute units)
max_storage : float — storage budget (GB)
min_queries_optimized : int — minimum number of queries to optimize
"""
self.queries_df = queries_df.copy()
self.max_compute = max_compute
self.max_storage = max_storage
self.min_queries_optimized = min_queries_optimized
# ------------------------------------------------------------------
# Model building
# ------------------------------------------------------------------
def build_model(self):
"""Build the Gurobi MIP model."""
self.model = gp.Model("snowflake_query_optimization", env=self.env)
self._create_variables()
self._set_objective()
self._add_constraints()
def _create_variables(self):
"""Binary decision variable x[i] = 1 if query i is optimized."""
for _, row in self.queries_df.iterrows():
qid = row["Query_ID"]
self._x[qid] = self.model.addVar(
vtype=GRB.BINARY,
name=f"optimize_{qid}"
)
def _set_objective(self):
"""
Maximize the sum of performance improvements of selected queries.
Dividing by n (constant) does not change the optimal solution;
the ratio is computed at reporting time.
"""
self.model.setObjective(
gp.quicksum(
row["Performance_Improvement_pct"] * self._x[row["Query_ID"]]
for _, row in self.queries_df.iterrows()
),
GRB.MAXIMIZE
)
def _add_constraints(self):
"""Add all business constraints to the model."""
# C1 — Compute maintenance budget
self.model.addConstr(
gp.quicksum(
row["Compute_Maintenance_Cost"] * self._x[row["Query_ID"]]
for _, row in self.queries_df.iterrows()
) <= self.max_compute,
name="C1_compute_budget"
)
# C2 — Storage budget
self.model.addConstr(
gp.quicksum(
row["Storage_Cost_GB"] * self._x[row["Query_ID"]]
for _, row in self.queries_df.iterrows()
) <= self.max_storage,
name="C2_storage_budget"
)
# C3 — Minimum number of queries optimized
self.model.addConstr(
gp.quicksum(self._x[qid] for qid in self._x) >= self.min_queries_optimized,
name="C3_min_queries"
)
# ------------------------------------------------------------------
# Solving
# ------------------------------------------------------------------
def solve(self) -> int:
"""Solve the model and return the Gurobi optimization status."""
self.model.optimize()
return self.model.Status
# ------------------------------------------------------------------
# Solution retrieval
# ------------------------------------------------------------------
def get_solution(self) -> dict:
"""
Extract and return the solution as a structured dictionary.
Returns
-------
dict with keys:
status, is_optimal, objective_value,
selected_queries, total_compute_cost, total_storage_cost,
query_breakdown
"""
status = self.model.Status
is_optimal = (status == GRB.OPTIMAL)
if not is_optimal:
return {
"status": status,
"is_optimal": False,
"objective_value": None,
"selected_queries": [],
"total_compute_cost": None,
"total_storage_cost": None,
"query_breakdown": None,
}
n = len(self.queries_df)
selected = [
qid for qid, var in self._x.items()
if var.X > 0.5
]
selected_df = self.queries_df[self.queries_df["Query_ID"].isin(selected)]
total_compute = selected_df["Compute_Maintenance_Cost"].sum()
total_storage = selected_df["Storage_Cost_GB"].sum()
objective_value = self.model.ObjVal / n # performance ratio (%)
breakdown = [
{
"Query_ID": row["Query_ID"],
"Performance_Improvement_pct": row["Performance_Improvement_pct"],
"Compute_Maintenance_Cost": row["Compute_Maintenance_Cost"],
"Storage_Cost_GB": row["Storage_Cost_GB"],
}
for _, row in selected_df.iterrows()
]
return {
"status": status,
"is_optimal": True,
"objective_value": round(objective_value, 4),
"selected_queries": selected,
"total_compute_cost": round(total_compute, 4),
"total_storage_cost": round(total_storage, 4),
"query_breakdown": breakdown,
}
# ------------------------------------------------------------------
# Display
# ------------------------------------------------------------------
def display_results(self):
"""Print a formatted summary of the optimization results."""
sol = self.get_solution()
print("=" * 60)
print(" Snowflake Query Optimization Results")
print("=" * 60)
if not sol["is_optimal"]:
print(f" Status : {sol['status']} (no optimal solution found)")
print("=" * 60)
return
print(f" Status : Optimal")
print(f" Performance Improvement : {sol['objective_value']:.2f}%")
print(f" Queries Optimized : {len(sol['selected_queries'])} / "
f"{len(self.queries_df)}")
print(f" Compute Used : {sol['total_compute_cost']:.1f} / "
f"{self.max_compute} units")
print(f" Storage Used : {sol['total_storage_cost']:.1f} / "
f"{self.max_storage} GB")
print()
print(" Per-Query Breakdown:")
print(f" {'Query_ID':<12} {'Perf Impr (%)':>14} "
f"{'Compute':>10} {'Storage (GB)':>14}")
print(" " + "-" * 52)
for entry in sol["query_breakdown"]:
print(f" {entry['Query_ID']:<12} "
f"{entry['Performance_Improvement_pct']:>14.1f} "
f"{entry['Compute_Maintenance_Cost']:>10.1f} "
f"{entry['Storage_Cost_GB']:>14.1f}")
print("=" * 60)
# ---------------------------------------------------------------------------
# Main — demonstration with production data
# ---------------------------------------------------------------------------
if __name__ == "__main__":
# Production dataset (snowflake.csv)
queries_data = {
"Query_ID": ["Q1", "Q2", "Q3", "Q4", "Q5", "Q6", "Q7", "Q8", "Q9"],
"Frequency": [5000, 3000, 2000, 2000, 1000, 4000, 4000, 1500, 2500],
"Selectivity_pct": [10, 15, 20, 25, 30, 25, 30, 5, 10],
"Compute_Maintenance_Cost": [3, 4, 2, 3, 5, 6, 7, 1, 2],
"Storage_Cost_GB": [10, 8, 5, 6, 12, 15, 16, 3, 7],
"Performance_Improvement_pct": [25, 20, 15, 17, 30, 35, 36, 10, 18],
}
queries_df = pd.DataFrame(queries_data)
with gp.Env(empty=True) as env:
env.setParam("OutputFlag", 0)
env.setParam("TimeLimit", 10)
env.start()
with QueryOptimizer(env) as optimizer:
optimizer.set_data(
queries_df,
max_compute=20,
max_storage=45,
min_queries_optimized=3,
)
optimizer.build_model()
optimizer.solve()
optimizer.display_results()
Alongside the implementation, Modeler writes a pytest suite with one test
per scenario in the specification. Each test references its scenario
identifier, and uses small hard-coded query data and a short solver time
limit so the suite runs in well under a second.
"""
pytest test suite for the Snowflake Query Optimization model.
Covers all scenarios defined in specification.md (T1 - T7).
"""
import time
import pytest
import pandas as pd
import gurobipy as gp
from gurobipy import GRB
from snowflake_modeler import QueryOptimizer
# ---------------------------------------------------------------------------
# Shared fixtures / helpers
# ---------------------------------------------------------------------------
PRODUCTION_DATA = {
"Query_ID": ["Q1", "Q2", "Q3", "Q4", "Q5", "Q6", "Q7", "Q8", "Q9"],
"Frequency": [5000, 3000, 2000, 2000, 1000, 4000, 4000, 1500, 2500],
"Selectivity_pct": [10, 15, 20, 25, 30, 25, 30, 5, 10],
"Compute_Maintenance_Cost": [3, 4, 2, 3, 5, 6, 7, 1, 2],
"Storage_Cost_GB": [10, 8, 5, 6, 12, 15, 16, 3, 7],
"Performance_Improvement_pct": [25, 20, 15, 17, 30, 35, 36, 10, 18],
}
SMALL_DATA = {
"Query_ID": ["QA", "QB", "QC"],
"Frequency": [100, 200, 150],
"Selectivity_pct": [10, 20, 15],
"Compute_Maintenance_Cost": [2, 3, 4],
"Storage_Cost_GB": [5, 8, 10],
"Performance_Improvement_pct": [25, 30, 20],
}
TOL = 1e-4 # feasibility / objective tolerance
def _make_env():
"""Return a started Gurobi environment with output suppressed."""
env = gp.Env(empty=True)
env.setParam("OutputFlag", 0)
env.setParam("TimeLimit", 10)
env.start()
return env
# ---------------------------------------------------------------------------
# T1: Full Dataset — Optimal Solution
# ---------------------------------------------------------------------------
def test_T1_full_dataset_optimal():
"""
T1: Run the model on all 9 production queries.
Brute-force confirms the optimal selection is {Q1, Q2, Q3, Q5, Q8, Q9}
with performance sum = 118 and ratio = 118/9 = 13.1111%
(compute=17, storage=45 — storage is binding).
"""
start = time.time()
df = pd.DataFrame(PRODUCTION_DATA)
with _make_env() as env:
with QueryOptimizer(env) as opt:
opt.set_data(df, max_compute=20, max_storage=45,
min_queries_optimized=3)
opt.build_model()
opt.solve()
sol = opt.get_solution()
elapsed = time.time() - start
assert elapsed < 10, f"T1 timed out: {elapsed:.1f}s"
assert sol["is_optimal"] is True, "T1: expected optimal solution"
# Exact optimal selection
assert set(sol["selected_queries"]) == {"Q1", "Q2", "Q3", "Q5", "Q8", "Q9"}, \
f"T1: expected Q1/Q2/Q3/Q5/Q8/Q9, got {sol['selected_queries']}"
# Exact performance ratio: 118 / 9
expected_ratio = round(118 / 9, 4)
assert abs(sol["objective_value"] - expected_ratio) <= TOL, \
f"T1: expected ratio {expected_ratio}, got {sol['objective_value']}"
# Budget compliance
assert sol["total_compute_cost"] <= 20.0 + TOL, \
f"T1: compute {sol['total_compute_cost']} exceeds budget"
assert abs(sol["total_storage_cost"] - 45.0) <= TOL, \
f"T1: storage should be binding at 45.0, got {sol['total_storage_cost']}"
# Breakdown structure
assert sol["query_breakdown"] is not None
assert len(sol["query_breakdown"]) == 6
# ---------------------------------------------------------------------------
# T2: Tight Compute Budget — Infeasible
# ---------------------------------------------------------------------------
def test_T2_tight_compute_budget_infeasible():
"""
T2: Compute budget of 4 units cannot accommodate >= 3 queries.
Cheapest 3 queries by compute cost: Q8(1) + Q3(2) + Q9(2) = 5 > 4.
C3 cannot be satisfied — the problem is infeasible.
"""
start = time.time()
df = pd.DataFrame(PRODUCTION_DATA)
with _make_env() as env:
with QueryOptimizer(env) as opt:
opt.set_data(df, max_compute=4, max_storage=45,
min_queries_optimized=3)
opt.build_model()
opt.solve()
sol = opt.get_solution()
elapsed = time.time() - start
assert elapsed < 10, f"T2 timed out: {elapsed:.1f}s"
assert sol["is_optimal"] is False, "T2: expected infeasible result"
assert sol["objective_value"] is None, "T2: objective should be None"
assert sol["selected_queries"] == [], "T2: no queries should be selected"
# ---------------------------------------------------------------------------
# T3: Relaxed Budgets — All Queries Selected
# ---------------------------------------------------------------------------
def test_T3_relaxed_budgets_all_selected():
"""
T3: Very large budgets; all 9 queries are selected because every
additional query contributes positively to the objective.
Expected ratio = 206 / 9 = 22.8889%.
"""
start = time.time()
df = pd.DataFrame(PRODUCTION_DATA)
with _make_env() as env:
with QueryOptimizer(env) as opt:
opt.set_data(df, max_compute=1000, max_storage=1000,
min_queries_optimized=3)
opt.build_model()
opt.solve()
sol = opt.get_solution()
elapsed = time.time() - start
assert elapsed < 10, f"T3 timed out: {elapsed:.1f}s"
assert sol["is_optimal"] is True, "T3: expected optimal solution"
assert len(sol["selected_queries"]) == 9, \
f"T3: expected all 9 queries, got {len(sol['selected_queries'])}"
# Exact ratio when all 9 are selected: 206 / 9
expected_ratio = round(206 / 9, 4)
assert abs(sol["objective_value"] - expected_ratio) <= TOL, \
f"T3: expected ratio {expected_ratio}, got {sol['objective_value']}"
expected_compute = sum(PRODUCTION_DATA["Compute_Maintenance_Cost"])
expected_storage = sum(PRODUCTION_DATA["Storage_Cost_GB"])
assert abs(sol["total_compute_cost"] - expected_compute) <= TOL, \
"T3: compute total mismatch"
assert abs(sol["total_storage_cost"] - expected_storage) <= TOL, \
"T3: storage total mismatch"
# ---------------------------------------------------------------------------
# T4: Minimum Viable Dataset — Edge Case
# ---------------------------------------------------------------------------
def test_T4_minimum_dataset_edge_case():
"""
T4: Exactly 3 queries with budgets that allow all 3 to be selected.
C3 forces all 3 to be chosen. Expected ratio = 75 / 3 = 25.0%.
"""
start = time.time()
df = pd.DataFrame(SMALL_DATA)
with _make_env() as env:
with QueryOptimizer(env) as opt:
opt.set_data(df, max_compute=20, max_storage=45,
min_queries_optimized=3)
opt.build_model()
opt.solve()
sol = opt.get_solution()
elapsed = time.time() - start
assert elapsed < 10, f"T4 timed out: {elapsed:.1f}s"
assert sol["is_optimal"] is True, "T4: expected optimal solution"
assert len(sol["selected_queries"]) == 3, \
f"T4: expected 3 queries, got {len(sol['selected_queries'])}"
# Exact ratio: (25 + 30 + 20) / 3 = 75 / 3 = 25.0
assert abs(sol["objective_value"] - 25.0) <= TOL, \
f"T4: expected ratio 25.0, got {sol['objective_value']}"
assert sol["total_compute_cost"] <= 20.0 + TOL, "T4: compute budget exceeded"
assert sol["total_storage_cost"] <= 45.0 + TOL, "T4: storage budget exceeded"
# ---------------------------------------------------------------------------
# T5: Tight Storage Budget — Infeasible
# ---------------------------------------------------------------------------
def test_T5_tight_storage_budget_infeasible():
"""
T5: Storage budget of 2 GB makes it impossible to select even one query
(cheapest is Q8 at 3 GB), so C3 cannot be satisfied.
"""
start = time.time()
df = pd.DataFrame(PRODUCTION_DATA)
with _make_env() as env:
with QueryOptimizer(env) as opt:
opt.set_data(df, max_compute=20, max_storage=2,
min_queries_optimized=3)
opt.build_model()
opt.solve()
sol = opt.get_solution()
elapsed = time.time() - start
assert elapsed < 10, f"T5 timed out: {elapsed:.1f}s"
assert sol["is_optimal"] is False, "T5: expected infeasible result"
assert sol["objective_value"] is None, "T5: objective should be None"
# ---------------------------------------------------------------------------
# T6: Data Perturbation — Stable Optimal Set
# ---------------------------------------------------------------------------
def test_T6_data_perturbation_stable_set():
"""
T6: Reduce Q8's Performance_Improvement from 10% to 9% (single-point
perturbation). The optimal selected set must remain exactly
{Q1, Q2, Q3, Q5, Q8, Q9} — unchanged from T1.
Why the set is stable: storage is the binding constraint at exactly
45 GB. The three unselected queries (Q4=6GB, Q6=15GB, Q7=16GB) each
require more storage than the 3 GB freed by dropping Q8, so no
substitution is feasible within C2 regardless of Q8's performance value.
Expected: ratio = 117 / 9 = 13.0%, storage still binding at 45 GB.
"""
start = time.time()
# Apply perturbation: Q8 Performance_Improvement 10 -> 9
perturbed = dict(PRODUCTION_DATA)
perturbed["Performance_Improvement_pct"] = [25, 20, 15, 17, 30, 35, 36, 9, 18]
df = pd.DataFrame(perturbed)
with _make_env() as env:
with QueryOptimizer(env) as opt:
opt.set_data(df, max_compute=20, max_storage=45,
min_queries_optimized=3)
opt.build_model()
opt.solve()
sol = opt.get_solution()
elapsed = time.time() - start
assert elapsed < 10, f"T6 timed out: {elapsed:.1f}s"
assert sol["is_optimal"] is True, "T6: expected optimal solution"
# Selected set must be identical to T1 despite Q8 perturbation
assert set(sol["selected_queries"]) == {"Q1", "Q2", "Q3", "Q5", "Q8", "Q9"}, \
f"T6: selected set changed unexpectedly to {sol['selected_queries']}"
# Exact ratio: 117 / 9 = 13.0
expected_ratio = round(117 / 9, 4)
assert abs(sol["objective_value"] - expected_ratio) <= TOL, \
f"T6: expected ratio {expected_ratio}, got {sol['objective_value']}"
# Storage constraint remains binding
assert abs(sol["total_storage_cost"] - 45.0) <= TOL, \
f"T6: expected storage still binding at 45.0, got {sol['total_storage_cost']}"
# Compute unchanged
assert abs(sol["total_compute_cost"] - 17.0) <= TOL, \
f"T6: expected compute 17.0, got {sol['total_compute_cost']}"
# ---------------------------------------------------------------------------
# T7: Greedy Baseline Comparison
# ---------------------------------------------------------------------------
def test_T7_greedy_baseline_comparison():
"""
T7: Compare the optimizer's T1 result against a naive greedy strategy
that picks the top-3 queries by raw Performance_Improvement alone
(satisfying only the C3 minimum-3-query requirement).
Greedy top-3 by Performance_Improvement descending:
Q7 (36%) + Q6 (35%) + Q5 (30%) = sum 101, ratio = 101/9 = 11.2222%
Compute = 7+6+5 = 18 (feasible, <= 20)
Storage = 16+15+12 = 43 (feasible, <= 45)
Optimizer (T1 baseline):
{Q1, Q2, Q3, Q5, Q8, Q9} = sum 118, ratio = 118/9 = 13.1111%
Expected relative gain = (118 - 101) / 101 = 16.83% >= 15% threshold.
"""
start = time.time()
df = pd.DataFrame(PRODUCTION_DATA)
# --- Run the optimizer ---
with _make_env() as env:
with QueryOptimizer(env) as opt:
opt.set_data(df, max_compute=20, max_storage=45,
min_queries_optimized=3)
opt.build_model()
opt.solve()
sol = opt.get_solution()
elapsed = time.time() - start
assert elapsed < 10, f"T7 timed out: {elapsed:.1f}s"
assert sol["is_optimal"] is True, "T7: optimizer must find an optimal solution"
# Compute optimizer_sum directly from the breakdown to avoid floating-point
# round-trip error when back-calculating from the already-rounded ratio.
optimizer_sum = sum(
entry["Performance_Improvement_pct"]
for entry in sol["query_breakdown"]
)
# --- Compute greedy baseline inline ---
sorted_df = df.sort_values("Performance_Improvement_pct", ascending=False)
greedy_top3 = sorted_df.head(3)
greedy_sum = greedy_top3["Performance_Improvement_pct"].sum() # 101
greedy_ratio = greedy_sum / len(df) # 11.2222%
# Greedy selection must be budget-feasible (sanity check)
assert greedy_top3["Compute_Maintenance_Cost"].sum() <= 20.0 + TOL, \
"T7: greedy selection exceeds compute budget — test data changed"
assert greedy_top3["Storage_Cost_GB"].sum() <= 45.0 + TOL, \
"T7: greedy selection exceeds storage budget — test data changed"
# Optimizer must strictly outperform greedy
assert sol["objective_value"] > greedy_ratio + TOL, \
(f"T7: optimizer ratio {sol['objective_value']:.4f}% must exceed "
f"greedy ratio {greedy_ratio:.4f}%")
# Optimizer must NOT select the same set as greedy
assert set(sol["selected_queries"]) != set(greedy_top3["Query_ID"].tolist()), \
"T7: optimizer selected the same queries as greedy — expected a better plan"
# Relative gain must be at least 15%
relative_gain = (optimizer_sum - greedy_sum) / greedy_sum
assert relative_gain >= 0.15 - TOL, \
(f"T7: relative gain {relative_gain:.2%} is below the 15% threshold; "
f"optimizer_sum={optimizer_sum}, greedy_sum={greedy_sum}")
# Pin exact known values for regression safety
assert abs(greedy_sum - 101.0) <= TOL, \
f"T7: expected greedy_sum=101, got {greedy_sum}"
assert abs(optimizer_sum - 118.0) <= TOL, \
f"T7: expected optimizer_sum=118, got {optimizer_sum}"
Generating code and tests is the easy half. Modeler also runs the suite in an isolated environment created fresh for this conversation, solving against Gurobi in the cloud — you do not need Gurobi installed locally, and you do not need a license of your own to see the model solve. It reads the output and fixes what it finds, so what you get back is a model that has been executed, not one that merely looks plausible.
The specification was already complete by the time the data arrived, and answering the frequency/selectivity question in the previous tab doubled as the go-ahead — the same message asked Modeler to finalize the specification, implement it, write tests, run them, and report back. Before writing any code, that finalization pass caught something worth knowing: the originally requested 20%-improvement floor turned out to be mathematically unreachable within the 20-unit compute and 45 GB storage budgets — brute-force enumeration over all 512 subsets shows the best achievable ratio is 13.1%. Modeler removed that constraint rather than implement a model that could never solve, and logged it under the specification’s Out of Scope section.
With the infeasible floor removed, the specification has no open
items left, so there is nothing to ask before implementing. Writing
the QueryOptimizer class and a pytest suite covering scenarios
T1-T7, then running the tests.
6 of 7 tests pass on the first run. One failure, in the newest test — let me dig into it.
The run, and the loop
$ pytest test_snowflake_modeler.py -q
......F
=================================== FAILURES ===================================
__________________________ test_T7_greedy_baseline_comparison __________________________
assert abs(optimizer_sum - 118.0) <= TOL, \
> f"T7: expected optimizer_sum=118, got {optimizer_sum}"
E AssertionError: T7: expected optimizer_sum=118, got 117.99999999999999
1 failed, 6 passed in 0.29s
This is a code-level bug, not a modeling error — the model’s own
selection is correct (it is exactly {Q1, Q2, Q3, Q5, Q8, Q9}, matching
the specification’s T1 result). The problem is in the test’s
arithmetic: it derived optimizer_sum by multiplying the already
rounded ratio back out (round(118/9, 4) * 9 = 117.99999999999999),
which reintroduces floating-point error that the strict equality
check then trips over. The fix belongs in the test, not the model —
sum the raw Performance_Improvement_pct values straight from the
solution’s per-query breakdown instead of reversing the rounded ratio.
Found it — a floating-point rounding bug in test T7, not a modeling
issue. The test was computing the optimizer’s raw performance sum by
multiplying the rounded ratio back out: round(118/9, 4) * 9 lands
on 117.99999999999999 instead of exactly 118, which then fails
a strict tolerance check. I fixed it by summing
Performance_Improvement_pct directly from each selected query’s
entry in query_breakdown instead of back-calculating from the
rounded ratio. Re-running now.
$ pytest test_snowflake_modeler.py -q
.......
7 passed in 0.31s
Gurobi Optimizer version 13.0.2 build v13.0.2rc1 (armlinux64)
Optimize a model with 3 rows, 9 columns and 27 nonzeros (Max)
Variable types: 0 continuous, 9 integer (9 binary)
Coefficient statistics:
Matrix range [1e+00, 1e+01]
Objective range [1e+01, 4e+01]
Bounds range [1e+00, 1e+00]
RHS range [3e+00, 4e+01]
Root relaxation: objective 1.416667e+02, 2 iterations, 0.00 seconds
Explored 1 nodes (2 simplex iterations) in 0.00 seconds (0.00 work units)
Best objective 1.180000000000e+02, best bound 1.180000000000e+02, gap 0.0000%
All 7 scenarios pass cleanly. Here is a full summary of everything that was built.
Results
Optimal solution
6 of 9 queries selected for optimization — {Q1, Q2, Q3, Q5, Q8, Q9} — for a performance improvement ratio of 13.11%:
Query |
Perf. improvement |
Compute cost |
Storage cost |
Optimized? |
|---|---|---|---|---|
Q1 |
25% |
3 |
10 GB |
Yes |
Q2 |
20% |
4 |
8 GB |
Yes |
Q3 |
15% |
2 |
5 GB |
Yes |
Q4 |
17% |
3 |
6 GB |
No |
Q5 |
30% |
5 |
12 GB |
Yes |
Q6 |
35% |
6 |
15 GB |
No |
Q7 |
36% |
7 |
16 GB |
No |
Q8 |
10% |
1 |
3 GB |
Yes |
Q9 |
18% |
2 |
7 GB |
Yes |
Site-wide metrics achieved:
Metric |
Value |
|---|---|
Performance ratio |
13.11% |
Queries optimized |
6 of 9 |
Compute used |
17.0 of 20 units |
Storage used |
45.0 of 45 GB (binding) |
Files created
File |
Description |
|---|---|
|
Complete problem specification |
|
Input query dataset |
|
Gurobipy implementation ( |
|
Pytest suite covering scenarios T1-T7 |
Test results
Test |
Scenario |
Result |
|---|---|---|
T1 |
Baseline — 6 queries selected, 13.11% ratio |
PASSED |
T2 |
Compute budget of 4 — infeasible |
PASSED |
T3 |
Budgets relaxed — all 9 selected, 22.89% ratio |
PASSED |
T4 |
Minimum viable dataset — all 3 forced by C3 |
PASSED |
T5 |
Storage budget of 2 GB — infeasible |
PASSED |
T6 |
Q8 perturbed — selected set unchanged, ratio 13.00% |
PASSED |
T7 |
Greedy top-3 baseline — optimizer wins by 16.8% |
PASSED |
Let me know if you would like any changes, such as adjusting the budgets or the minimum rollout size, or generating the mathematical formulation.
That log lands in the workspace as execution.log alongside the code, so
the solve is auditable rather than asserted.
The loop ends one of two ways. Either every test passes, as here — or Modeler concludes a failure reflects a genuine contradiction between the specification, the implementation and the tests. In that case it stops instead of guessing, tells you what it found, and asks which side is the one to correct.
What the cloud environment gives you, and what it does not
The environment is built for validating a model, not for running production
workloads. Python 3 is available with gurobipy, gurobipy-pandas,
numpy, pandas, scipy and pytest pre-installed. Within that:
Solves use a Gurobi trial license, capped at 2,000 variables and 2,000 constraints. This model, at 9 columns and 3 rows, is nowhere near the cap — but a full-scale catalog of hundreds of recurring queries could be.
Execution is limited to 60 seconds per command, with tight CPU and memory limits. This is why the generated tests use small hard-coded data and a short solver time limit.
Additional packages cannot be installed, there is no internet access, and Compute Server and Instant Cloud cannot be used.
So treat a passing suite as evidence that the model is right, not as a performance measurement. Download the implementation and run it against your own license and your own full query catalog for that. See Solving limits for the details.
Business problems do not hold still. The specification above puts frequency-weighted objective out of scope — a reasonable simplification, until leadership decides raw percentages aren’t good enough and real-world traffic should count. Ask for the change in plain language and Modeler updates the specification first, then propagates it to the implementation and the tests, in that order.
Actually, let’s revisit that frequency thing now — leadership wants the objective to reflect real-world impact, not just raw percentage improvement. Please update the model so the objective weights each selected query’s Performance_Improvement by how often it runs (Frequency), instead of just summing the raw percentages. Everything else — the compute budget, storage budget, and minimum 3-query rollout requirement — should stay the same.
This is a scope change, not a data correction: frequency-weighting is currently listed under “Out of scope.” Updating the specification first — replace the objective definition, drop the deferred bullet — then the implementation and tests, and a full re-run to see whether the previous baseline still holds under the new objective.
All 7 tests pass in 0.41 seconds. Here is a full summary of everything that changed and the new results.
What changed
sum(Performance_Improvement_pct * x_i), now sum(Frequency * Performance_Improvement_pct * x_i). Every query’s contribution is scaled by how many times per day it actually runs, so the model directly optimizes real-world impact rather than raw technical percentages.query_breakdown entries now include Frequency and Weighted_Score (= Frequency × Performance_Improvement_pct) alongside the existing fields.Updated results
Field |
Old (unweighted) |
New (frequency-weighted) |
|---|---|---|
Objective |
13.11% ratio |
424,000 weighted score |
Queries selected |
Q1, Q2, Q3, Q5, Q8, Q9 (6) |
Q1, Q6, Q7, Q8 (4) |
Compute used |
17 / 20 units |
17 / 20 units |
Storage used |
45 / 45 GB (binding) |
44 / 45 GB |
Q6 and Q7 both run 4,000 times per day and were previously rejected because their combined storage footprint crowded out a larger number of smaller queries. Under the frequency-weighted objective, their combined contribution of 284,000 out of 424,000 total makes them far too valuable to leave out — and Q8 slots in cheaply to satisfy the minimum-3-query rollout requirement with room to spare.
Go ahead, and let me know if the answer actually changes.
Specification, then code, then tests
The new objective, in the specification’s own words:
Maximize the total frequency-weighted performance improvement of the
selected (optimized) queries. For each selected query, its contribution
is the product of its daily execution frequency (Frequency) and its
performance improvement percentage (Performance_Improvement_pct).
The only change to the model itself is which quantity gets summed in the objective:
def _set_objective(self):
"""
- Maximize the sum of performance improvements of selected queries.
- Dividing by n (constant) does not change the optimal solution;
- the ratio is computed at reporting time.
+ Maximize total frequency-weighted performance improvement.
+ Each query contributes Frequency * Performance_Improvement_pct
+ if selected, so high-frequency queries with strong improvements
+ receive proportionally higher priority.
"""
self.model.setObjective(
gp.quicksum(
- row["Performance_Improvement_pct"] * self._x[row["Query_ID"]]
+ row["Frequency"] * row["Performance_Improvement_pct"] * self._x[row["Query_ID"]]
for _, row in self.queries_df.iterrows()
),
GRB.MAXIMIZE
)
get_solution() changes alongside it: objective_value is no longer
divided by n (it is now a weighted score, not a percentage ratio), and
each query_breakdown entry gains Frequency and Weighted_Score
fields.
T1 is the test that changes the most, since it pins an exact optimal selection:
def test_T1_full_dataset_optimal():
"""
T1 (frequency-weighted): Run the model on all 9 production queries
with the frequency-weighted objective. The optimal selection is
Q1, Q6, Q7, Q8 with a total weighted score of 424,000
(compute=17, storage=44).
"""
start = time.time()
df = pd.DataFrame(PRODUCTION_DATA)
with _make_env() as env:
with QueryOptimizer(env) as opt:
opt.set_data(df, max_compute=20, max_storage=45,
min_queries_optimized=3)
opt.build_model()
opt.solve()
sol = opt.get_solution()
elapsed = time.time() - start
assert elapsed < 10, f"T1 timed out: {elapsed:.1f}s"
assert sol["is_optimal"] is True, "T1: expected optimal solution"
# Optimal weighted score must be 424,000
assert abs(sol["objective_value"] - 424_000) <= TOL, \
f"T1: expected weighted score 424000, got {sol['objective_value']}"
# Optimal selection must be exactly Q1, Q6, Q7, Q8
assert set(sol["selected_queries"]) == {"Q1", "Q6", "Q7", "Q8"}, \
f"T1: expected Q1/Q6/Q7/Q8, got {sol['selected_queries']}"
# Budget constraints respected
assert sol["total_compute_cost"] <= 20.0 + TOL, \
f"T1: compute {sol['total_compute_cost']} exceeds budget"
assert sol["total_storage_cost"] <= 45.0 + TOL, \
f"T1: storage {sol['total_storage_cost']} exceeds budget"
Modeler re-runs the whole suite, not just the changed test — the point of propagating in this order is that nothing is left stale behind the change.
Where it stops and asks
Re-running the suite under the new objective doesn’t just change numbers — it changes what several tests are even entitled to assert. T1, T3 and T4 all compare against an exact optimal solution, and the new objective produces a different exact solution. Modeler tightened each one to pin the new value, but flagged that as a judgment call rather than making it silently:
Test |
What changed |
The ambiguity |
|---|---|---|
T1 |
Was: check feasibility properties only. Now: pins exact selection
|
Tighter is objectively better regression coverage — but you didn’t explicitly ask for exact-value pinning. |
T3 |
Was: check all 9 selected and totals match. Now: also pins total
weighted score to |
The number is arithmetically correct, but derived internally, not confirmed with you. |
T4 |
Was: check all 3 selected and budgets respected. Now: pins weighted
score to |
Same pattern as T3 — correct, but a silent derivation. |
It was mostly clean, but three things are worth being transparent about: T1, T3 and T4 all had their acceptance criteria silently tightened with derived numeric values (424,000 / 623,000 / 11,500). No scenario needed re-interpretation at the business level — the two infeasibility tests, T2 and T5, are completely unaffected — but you should confirm those three pinned values are the right ones before treating the test suite as authoritative.
Confirmed on all three — pin the exact values in T1, T3, and T4 as you derived them (424,000 / 623,000 / 11,500). Tighter regression coverage is exactly what we want here.
This is the payoff of writing a spec-first tool: a place where the tests quietly outran what was actually agreed gets surfaced and confirmed, rather than shipped as an assumption nobody signed off on.
The answer flips
The old, unweighted-optimal plan is still a valid feasible plan under the new objective — it just is not the best one anymore:
Plan |
Selected queries |
Weighted score |
|---|---|---|
Old plan, scored under the new objective |
Q1, Q2, Q3, Q5, Q8, Q9 |
305,000 |
New optimal plan |
Q1, Q6, Q7, Q8 |
424,000 |
Re-optimizing under the frequency-weighted objective is worth 119,000 points — a 39% gain over just re-scoring the old plan. Q6 and Q7 were the two queries the unweighted model rejected outright for being too storage-heavy; under the new objective their combined 4,000-run-per-day traffic makes them the two most valuable queries in the entire dataset.
The specification, the implementation and the tests never mention mathematics — that is deliberate, so the business owner can read them. But you may still need the formulation: to review the model, to document it, or to hand it to someone who thinks in constraints rather than in code. Ask at any point and Modeler derives one from the current specification and implementation, in Markdown with LaTeX, and saves it to the workspace with everything else.
Sets and indices
\(i \in Q\): queries, where \(Q = \{1, 2, \ldots, n\}\) and \(n\) is the total number of queries in the dataset.
Parameters
\(p_i\): performance improvement of query \(i\) if optimized (%).
\(c_i\): compute maintenance cost of query \(i\) if optimized (compute units).
\(s_i\): storage cost of query \(i\) if optimized (GB).
\(C^{\max}\): maximum total compute maintenance budget (20 compute units).
\(S^{\max}\): maximum total storage budget (45 GB).
\(K^{\min}\): minimum number of queries that must be optimized (3).
\(n\): total number of queries in the dataset (9).
Decision variables
\(x_i \in \{0, 1\}\): 1 if query \(i\) is selected for optimization, 0 otherwise, for every \(i \in Q\).
Objective function
Maximize the total performance improvement ratio across all queries:
Since \(n\) is a constant, this is equivalent to maximizing \(\sum_{i \in Q} p_i \, x_i\), which is the form used in the Gurobi model; the ratio is computed only when reporting the result.
Constraints
C1 — Compute maintenance budget.
C2 — Storage budget.
C3 — Minimum rollout.
Complete model
Additional notes
Because \(n\) is a fixed constant, dividing the objective by \(n\) does not affect which solution is optimal. The Gurobi model maximizes \(\sum_{i \in Q} p_i x_i\) directly and divides by \(n\) only when reporting the achieved ratio.
The problem is a binary integer program, structurally a 0-1 knapsack with an additional lower-bound constraint on the number of items selected (C3), rather than only an upper bound on resources.
With \(n = 9\) queries the problem has 9 binary variables and 3 constraints, solved to optimality effectively instantaneously.
A hard minimum performance-improvement floor (\(\frac{1}{n}\sum_i p_i x_i \geq P^{\min}\), with \(P^{\min} = 20\%\)) was part of the original request but proved infeasible against \(C^{\max}=20\) and \(S^{\max}=45\): brute-force enumeration shows the best achievable ratio is about 13.1%. That constraint was removed and deferred rather than implemented as an infeasible requirement.
Note that this is the formulation of the original model, before the frequency-weighted objective added in the Changing the model tab.
### Sets and indices
- $i \in Q$: queries, where $Q = \{1, 2, \ldots, n\}$ and $n$ is the total
number of queries in the dataset.
### Parameters
- $p_i$: performance improvement of query $i$ if optimized (%).
- $c_i$: compute maintenance cost of query $i$ if optimized (compute units).
- $s_i$: storage cost of query $i$ if optimized (GB).
- $C^{\max}$: maximum total compute maintenance budget (20 compute units).
- $S^{\max}$: maximum total storage budget (45 GB).
- $K^{\min}$: minimum number of queries that must be optimized (3).
- $n$: total number of queries in the dataset (9).
### Decision variables
- $x_i \in \{0, 1\}$: 1 if query $i$ is selected for optimization, 0
otherwise, for every $i \in Q$.
### Objective function
Maximize the total performance improvement ratio across all queries:
$$
\text{Maximize} \quad \frac{1}{n} \sum_{i \in Q} p_i \, x_i
$$
Since $n$ is a constant, this is equivalent to maximizing
$\sum_{i \in Q} p_i \, x_i$, which is the form used in the Gurobi model; the
ratio is computed only when reporting the result.
### Constraints
**C1 — Compute maintenance budget.**
$$
\sum_{i \in Q} c_i \, x_i \leq C^{\max}
$$
**C2 — Storage budget.**
$$
\sum_{i \in Q} s_i \, x_i \leq S^{\max}
$$
**C3 — Minimum rollout.**
$$
\sum_{i \in Q} x_i \geq K^{\min}
$$
### Complete model
$$
\begin{aligned}
\max \quad & \frac{1}{n} \sum_{i \in Q} p_i \, x_i \\
\text{s.t.} \quad & \sum_{i \in Q} c_i \, x_i \leq C^{\max} \\
& \sum_{i \in Q} s_i \, x_i \leq S^{\max} \\
& \sum_{i \in Q} x_i \geq K^{\min} \\
& x_i \in \{0, 1\} \qquad \forall i \in Q
\end{aligned}
$$
### Additional notes
- Because $n$ is a fixed constant, dividing the objective by $n$ does not
affect which solution is optimal. The Gurobi model maximizes
$\sum_{i \in Q} p_i x_i$ directly and divides by $n$ only when reporting
the achieved ratio.
- The problem is a **binary integer program**, structurally a 0-1 knapsack
with an additional lower-bound constraint on the number of items selected
(C3), rather than only an upper bound on resources.
- With $n = 9$ queries the problem has 9 binary variables and 3 constraints,
solved to optimality effectively instantaneously.
- A hard minimum performance-improvement floor
($\frac{1}{n}\sum_i p_i x_i \geq P^{\min}$, with $P^{\min} = 20\%$) was
part of the original request but proved infeasible against $C^{\max}=20$
and $S^{\max}=45$: brute-force enumeration shows the best achievable ratio
is about 13.1%. That constraint was removed and deferred rather than
implemented as an infeasible requirement.
Note that this is the formulation of the original model, before the
frequency-weighted objective added in the **Changing the model** tab.