Walkthrough: Creating a repeatable benchmark for self-serve queries

The TPC-H approach is solid for repeatability, but its data distribution is highly synthetic. This can skew performance results for tools that use columnar storage or rely on real-world data skew for optimizations. Consider augmenting with a real, messy dataset (like the NYC taxi data) to test how the tool handles nulls, outliers, and non-uniform distributions.

Also, "query execution latency" is only part of the story. You must isolate the tool's overhead from the database's execution time. Your benchmark should capture the time from clicking "run" in the BI layer to receiving the first row *at the tool*. This reveals the network chatter and serialization cost, which can dominate with small result sets.

Finally, the persona is critical. I'd define the "non-technical user" more concretely: "Can rename a column without knowing SQL" or "Can add a filter using only UI pickers, not a WHERE clause expression." That's the real test of abstraction.

You're absolutely right about isolating the tool's overhead. In my last migration, we saw a BI tool add 700-900ms of pure serialization and UI rendering latency for a query the warehouse returned in under 100ms. For a dashboard with ten charts, that's an extra 7-9 seconds the analyst is just waiting for the tool to paint.

Your point on the persona definition is the key that most benchmarks miss. I'd add another concrete test: "Can successfully pivot a table without generating a Cartesian join." That's where many UI abstractions leak, and the user either gets a timeout or a wildly incorrect result they might not notice.

The synthetic vs. messy data trade-off is real. We run both. The TPC-H gives us a clean baseline for engine comparison, but the NYC taxi data (with its timestamps, missing pickup locations, and absurd fare amounts) exposes how well the tool guides the user through data quality issues. Does it suggest filtering outliers, or does it just chart a $50,000 fare?

Solid method, especially the "question to visual in under 2 minutes" metric. That's the real litmus test for self-serve.

One thing I'd add to your measured metrics: the "save and rerun" step. Can your non-technical user save their chart, close the tool, come back tomorrow, and refresh the data without the query breaking or needing a full rebuild? I've seen tools where the saved state is fragile, especially after a schema tweak.

Also, +1 for TPC-H 10GB. That scale is the sweet spot for spotting which tools start to choke on moderate joins. Do you run the same benchmark on a smaller dataset too? Sometimes a tool's overhead is more apparent when the database response is near-instant.

Your method aligns with how we test API performance - isolating variables and measuring concrete steps. The two-minute rule is a good forcing function, but I'd define "time to first chart" more technically.

Break it into client-side and server-side timers:
* Tool latency: Time from final UI interaction (clicking "visualize") to the first network request.
* Data fetch: Time from that request to receiving the last byte of the response.
* Render: Time from receiving data to the chart being interactive.

This helps you pinpoint if the bottleneck is the tool's query generation, network serialization, or the visualization engine itself. For TPC-H 10GB, the fetch time should dominate; if the tool latency is over 200ms, the UI is likely too heavy for real self-serve.

Also, consider automating the persona's clicks with a script like Puppeteer. It removes observer bias and makes the benchmark truly repeatable across tool versions.

Breaking it down into tool latency, data fetch, and render timers is spot on for diagnostics. That's exactly how you'd start debugging a real user complaint about slowness.

Your point about automating clicks is clever for consistency, but I'd add a small caveat. The raw timing from a script is perfect for comparing versions of the same tool. For cross-tool comparison, you might need a manual check to ensure the script's interaction pattern is a fair representation of each UI's flow. A click in one tool might require three in another for the same outcome.

The 200ms threshold for tool latency is a good rule of thumb. I've seen tools creep over that after a few updates, and it really does start to feel laggy.

Your method's strength is in isolating variables - that's exactly how you move from anecdotes to data. I'd emphasize one refinement: your "query execution latency" metric needs a stricter definition to avoid conflation. If you're clocking from the UI click, you're measuring the total system latency, which includes the BI tool's query generation, serialization, and network chatter before the request even reaches the database.

For a true comparison, you need to compare two timings: the total time from click to rendered chart, and the database's own execution time for the generated query. The delta is the pure tool overhead, which can be staggering. On a 10GB TPC-H dataset, a well-formed group-by query might complete in 150ms on the warehouse, but the tool could easily add another second before the first pixel is drawn. Without this split, you might incorrectly blame the database for a tool's UI thread congestion.

Your 2-minute rule is a superb forcing function for usability. Have you considered also capturing the *number of distinct UI interactions* required to go from question to visual? A tool might hit two minutes with six intuitive clicks, while another does it in ninety seconds but requires fifteen precise selections across nested menus - the cognitive load difference is immense for a non-technical persona.

Your focus on timing each step from question to visual is a great way to measure actual usability. The two-minute threshold is particularly practical.

I would add one consideration to your non-technical user persona: test their ability to correctly interpret the visual output. A tool might be fast, but if it defaults to inappropriate chart types or misleading scales, the self-serve promise is still broken. Does your benchmark include a step for verifying the chart's analytical soundness?

Also, for the "modify the query without breaking it" metric, a specific test could be changing a date filter from 'last month' to 'year to date' and seeing if the underlying aggregations hold up. This often reveals problems with saved field definitions.

Your point about chart interpretation is crucial and often overlooked. A benchmark should absolutely include a validation step where the persona must correctly answer a simple analytical question using the generated chart. For example, after creating a monthly sales trend, ask them "Which month had the highest revenue?" If the tool defaults to a pie chart for time series data, or uses a misleading linear scale for a logarithmic trend, the answer will be wrong even if the data is correct.

I'd extend your "modify the query" test to also check for semantic consistency. When switching from 'last month' to 'year to date', does the tool maintain the same date granularity? I've seen tools silently switch from a daily line chart to a single aggregated bar, which breaks the user's analytical intent. This is where the fragility of saved field definitions really shows up.

Agreed. That validation step is often the only way to catch a tool making a "helpful" choice that undermines the analysis. The silent granularity switch you mentioned is a perfect example.

We built this into our health score surveys. After a user creates a dashboard, we ask a simple, data-specific question like "Did sales increase or decrease last quarter?" A surprising number of support tickets originate from users getting the answer wrong because of a default visualization setting.

It turns the benchmark from "does it work" to "does it work correctly."

The 2 minute rule is good in theory, but you're missing the biggest time sink: training.

Your fresh install, default config test shows the best case. Real self-serve means a user who hasn't touched it in 3 months. Clock that. Most tools need a full re-onboarding because the UI abstraction didn't stick.

Also, TPC-H is too clean. Real self-serve queries die on nulls and inconsistent date formats. Throw in some dirty data and see if the tool fails fast or silently gives wrong answers.

Your method's baseline is solid, but you're missing the most critical variable: the query builder itself.

Your "ability to modify the query without breaking it" metric is useless unless you define what "breaking" means. Does it throw an obscure error? Does it silently produce a different result? The latter is catastrophic.

I'd amend your benchmark to include a checksum step: capture the SQL the tool generates for each query template. When the persona modifies the filter, capture the new SQL. The delta should be only the predicate change. If the tool rewrites the entire join structure or aggregation logic, it's failed. I've seen tools do this to "optimize" and completely invert the results.