When you can’t change the query or the schema

A supplier to large US retailers gives its customers a window into their own sales data. The customers open ThoughtSpot, ask a question, and see their numbers. They do not know there is a warehouse behind it, and they should not have to.

The largest customer’s report took over fifteen minutes. Two things could not be touched to fix it.

The queries. ThoughtSpot generates them from what the user clicks. There is no query to rewrite, because there is no query until someone asks.

The schema. Customers had saved answers built on the existing structure. Rename a column and you break reports people rely on, in an account you do not administer.

What that rules out

Rewriting the query. Denormalising for the access pattern. Remodelling the tables. Adding a hint. Those are the standard moves, and all of them assume the two things you are not allowed to move.

One route is left: keep the surface identical and replace what it points at. The query arrives at the same name, the schema looks the way it always did, and underneath the answer is no longer computed at the moment of the question.

Precompute, don’t recompute

Three incrementally refreshed materialized views carried the big customer’s report — daily point-of-sale, daily orders, daily shipments — with dependent views on top of them, since nothing could be optimised on the ThoughtSpot side:

CREATE MATERIALIZED VIEW mv_pos_daily
  DISTKEY (retailer_id)
  SORTKEY (sale_date, retailer_id)
  AUTO REFRESH NO
AS
SELECT retailer_id, store_id, sku, sale_date,
       SUM(units)   AS units,
       SUM(revenue) AS revenue
FROM   pos_transactions
GROUP  BY 1, 2, 3, 4;

Refreshed once a day. That sounds like a compromise and is not: the underlying data is produced on a daily rhythm, so a twenty-four hour view is the natural period rather than a concession. It is worth asking early whether anyone genuinely needs fresher than that, because the answer decides your whole design and it is usually no.

Keys chosen from the queries, not the schema

Redshift is columnar and distributed, and two physical choices decide whether it is fast or merely expensive.

The DISTKEY decides how rows are spread across nodes. Choose it wrong and every join moves data between machines before it can answer anything. The SORTKEY decides what can be skipped — with the right one the engine reads the blocks for a date range and ignores the rest; with the wrong one it reads everything and filters afterwards.

Both were picked from the queries the system actually runs, not from how the schema is drawn. Those are different lists. The schema suggests joining on the primary key; the log shows that in practice everything filters by retailer and date. Read the log.

A precomputed answer can be quietly wrong

This is the part that makes the approach safe enough to ship. A materialized view is an answer computed earlier — if it drifts from the source, the system does not fail, it lies, and nobody notices until someone compares two numbers by hand in a meeting.

So there is a validation script that checks the views against what the source query would have returned:

-- same window AND same grain on both sides; anything returned is a discrepancy
SELECT retailer_id, sale_date, mv.units AS mv_units, src.units AS src_units
FROM (
  SELECT retailer_id, sale_date, SUM(units) AS units
  FROM   mv_pos_daily
  WHERE  sale_date >= DATEADD(day, -7, CURRENT_DATE)
  GROUP  BY 1, 2
) mv
FULL OUTER JOIN (
  SELECT retailer_id, sale_date, SUM(units) AS units
  FROM   pos_transactions
  WHERE  sale_date >= DATEADD(day, -7, CURRENT_DATE)
  GROUP  BY 1, 2
) src USING (retailer_id, sale_date)
WHERE  DECODE(mv.units, src.units, 1, 0) = 0;

That script is the difference between an optimisation and a risk. Precomputation without verification is just a faster way to be wrong.

Fifteen minutes to two tenths of a second

The number is not interesting as a multiple. It is interesting because of the line it crosses.

Jakob Nielsen’s response-time thresholds have held for thirty years: about 0.1 seconds feels instantaneous, about one second keeps a person’s train of thought intact, and ten seconds is the outer limit of held attention. Fifteen minutes is not on that scale at all. A fifteen-minute report is not a tool, it is a request — you start it and go do something else, and you only start it when you already know what you want.

Under a second, the same report becomes something you explore. Change the filter, look at another period, compare two categories, all inside one conversation with a colleague. Nobody had to be trained for that. It is not the same product running faster; it is a different product.

Where else this applies

Any time the surface belongs to someone else. A BI tool, a partner’s integration, a public API contract, a client’s saved reports, a mobile app version you cannot force anyone to update.

The instinct is to negotiate the surface open — a schema change request, a migration window, a note to customers about updating their saved reports. Sometimes that is right. More often it costs three months of other people’s goodwill to buy a change you did not need, because the same speedup was available underneath, where nobody had to agree to anything.

This came out of building a Redshift warehouse under a BI surface that could not be modified, for a supplier whose customers read their own data. Read the case study →