#227 Data strategy 5.1 min
What Does One Query Cost? Cost Per Query From Timings
In a made-up traffic mix, one kind of query was 2 percent of the traffic and 60 percent of the compute cost. Here is how to find it.
Put in your own machine rate
Copy the code folder first. This one needs DuckDB 2.0.0 from the video's requirements. In measure.py, change HOURLY_RATE from 0.20 to 0.40, then run python cost.py. Predict the dollars per 1,000 column for 1,000 queries a day before you run it. The machine costs 24 hours at the new rate, divided by 1,000 queries, times 1,000. Compare the busy column with the video's run: has it changed? The busy column will be close to the video's, not identical, because timings differ from run to run. Put HOURLY_RATE back to 0.20 afterwards.
A hint
The rate only prices time. The machine busy column comes from the timings, not the rate.
Change who uses the system
In measure.py, change the traffic share of customer_rank from 0.02 to 0.20 and the share of lookup from 0.40 to 0.22 so the shares still add up to 1. Predict the share of bill column for customer_rank before you run python measure.py. Will it go up or down, and by roughly how much? Then run it and compare. Put the shares back afterwards, or exercise 4 will not match the video.
A hint
The share of the bill is the share of traffic times the cost of one query, divided by the total.
Time it once only
In measure.py, in the main function, change rows = profile() to rows = profile(runs=1). Run python measure.py three times and write down the median column for customer_rank each time. Predict whether the three numbers will match exactly. Why does the video say timing only once is a mistake?
A hint
Compare your numbers with the first and median columns in the video's output. Timings move between runs, and the median ignores the odd slow run.
See the gap shrink as traffic grows
In cost.py, change the list of queries per day to 200,000, 500,000 and 1,000,000. Before running python cost.py, predict whether the machine busy column and the vs seconds only column will go up or down as traffic grows. Then run it and check. The video says the gap shrinks to a few times near a million, with the machine still mostly idle. Your busy and gap numbers will differ from the video's, because they come from your own timings.
A hint
Cost per query is the day's machine cost divided by the number of queries, so more queries share the same bill.
These tasks are for you to try on your own machine. Nothing is checked or saved.
What this does not show
The rate, the data and the traffic mix are made up. Timings come from one busy laptop with DuckDB on two threads and moved a lot between runs. Storage, network and people are not counted.
Up next in Data strategy
More in Data strategy
Related videos
- #309 Profile Messy Customer Data in SQL Before Any Model Forward deployed engineer interviews
- #37 SQL Agent Guardrails: 8 Attacks Tested (DuckDB, Python) AI agents and MCP
- #219 Choose AI Use Cases: Does Your Winner Survive? Data strategy
- #01 Local AI Agent in Python: No API Key (Ollama) AI agents and MCP
