At 3 AM, Ask the Cache

The pager does not care that the neural network would have been elegant.

Imagine a pricing service going dark during close. Someone still has to quote a number. Not the perfect number. Not the number a future research plan might have produced. A number that is available, defensible, and honest about how much evidence is underneath it.

That is a different problem from “train a model.” If the source system normally returns a surcharge for a carrier, cabin, route, date, and flight, the backup path should first ask for the most specific historical evidence it can trust. If that cell is too thin, it should step down to a broader reference class with its eyes open.

This post uses airline YQ-style surcharges as a motivating example, but the pattern is more general than travel. It applies to any segmented quantity where the data are sparse, long-tailed, and operationally important: refund rates, fulfillment costs, fraud review time, cloud spend, conversion delay, or support load. The awkwardly useful lesson is that a large fraction of the intelligence comes from statistics and systems design before a predictive model enters the room.

Thesis: when the failure mode is sparse lookup under pressure, start with a robust fallback cache. Use machine learning later only if it beats this baseline on accuracy, latency, interpretability, and failure behavior.

Incident First, Model Later

A naive cache says: group by the fields we care about, take the average, and join back at prediction time. That fails quickly.

Long-tailed money variables punish means. One bad upstream parse, one refunded ticket, or one rare premium fare can pull an average away from the value a human would defend. Robust statistics gives us the right first move: use a median or another robust quantile. The median is not magic, but it has a useful breakdown property: roughly half the observations must be contaminated before the estimate can be pushed arbitrarily far away.¹

The second failure is sparsity. The most specific key is often the most tempting:

carrier + cabin + origin + destination + departure_month + flight_number

It is also often the least reliable. There may be one observation, or none. A single local median based on two tickets is not “personalized”; it is fragile.

The third failure is staleness. Old data are not wrong in the same way noisy data are wrong. They may describe a different world. A good fallback cache has to say what time window it represents and how often it is rebuilt.

The resulting design goal sounds modest, then quietly eats an afternoon:

Always return the most specific estimate that passes a statistical quality check, while making the chosen fallback level observable.

A Ladder of Less-Specific Truths

Think of the cache as a hierarchy of reference classes. At the top is a narrow cell: same carrier, same cabin, same route, same flight. Below it are broader cells: same carrier and cabin, same carrier, then global.

The key order is the strategy. Put stable, high-signal dimensions early and sparse, volatile dimensions late.

SQL already has the primitive we need. GROUP BY ROLLUP computes totals across a hierarchy of grouping columns; Athena documents it as a way to generate subtotals for a set of columns.² In Trino/Presto-style engines, approx_percentile(x, 0.5) gives an approximate median over large groups without sorting all values exactly.³ Athena engine version 3 uses t-digest for approx_percentile, which is designed for compact approximate quantile summaries.⁴ Quantile sketches are a deep area; the Greenwald-Khanna algorithm and t-digest are two well-known reference points for space-efficient approximate quantiles.⁵, ⁶

The cache table can be built in one pass:

CREATE TABLE yq_fallback_cache AS
WITH normalized AS (
  SELECT
    CAST(COALESCE(validating_carrier, '') AS VARCHAR) AS carrier,
    CAST(COALESCE(cabin, '') AS VARCHAR) AS cabin,
    CAST(CONCAT(COALESCE(origin_airport, ''), '-', COALESCE(destination_airport, '')) AS VARCHAR) AS route,
    CAST(COALESCE(outbound_flight_number, '') AS VARCHAR) AS flight_number,
    CAST(yq_amount AS DOUBLE) AS yq_amount
  FROM raw_ticket_taxes
  WHERE yq_amount IS NOT NULL
    AND yq_amount >= 0
)
SELECT
  carrier,
  cabin,
  route,
  flight_number,
  4 - (
    grouping(carrier)
    + grouping(cabin)
    + grouping(route)
    + grouping(flight_number)
  ) AS cache_level,
  approx_percentile(yq_amount, 0.5) AS median_yq,
  avg(yq_amount) AS mean_yq,
  count(*) AS n
FROM normalized
GROUP BY ROLLUP (
  carrier,
  cabin,
  route,
  flight_number
);

Two details matter more than they look.

First, normalize keys before the rollup and before lookup. NULL, empty string, case differences, integer-vs-string flight numbers, and whitespace should not decide whether a cache hit exists.

Second, make cache_level a first-class output. A fallback cache that only returns a number is hard to debug. A fallback cache that returns both the number and the level that served it can be monitored, audited, and tuned.

Write Down the Promise

The lookup contract is:

\[\hat{y}(x) = \widehat{Q}_{0.5}(L^\*, x), \quad L^\* = \max\{L: n_L(x) \ge n_{\min}\}.\]

In words: find the most specific level whose cell has enough observations, and use that cell’s median.

That minimum count, \(n_{\min}\), is not a theorem. It is an operational threshold. I would start with something like 25 or 30, then tune it against a time-split validation set. The point is not that 30 is sacred. The point is that one ticket should not carry the authority of a market.

There is a better version for sparse cells: partial pooling. Instead of dropping a level entirely when it is small, shrink it toward its parent:

\[\tilde{Q}_{0.5}(L, x) = w_L(x)\widehat{Q}_{0.5}(L, x) + \left(1 - w_L(x)\right)\tilde{Q}_{0.5}(L-1, x), \quad w_L(x) = \frac{n_L(x)}{n_L(x) + k}.\]

The constant \(k\) is a skepticism parameter. Larger \(k\) means local evidence has to work harder before it overrides the broader reference class. This is the same practical instinct behind empirical Bayes and hierarchical modeling: separate estimates are noisy, and related groups can borrow strength from one another.⁷, ⁸

Make the Toy Worse on Purpose

The simulator below creates a synthetic surcharge world with carriers, cabins, routes, and flights. Training data are sparse and contaminated by occasional large outliers. Test targets are the clean underlying cell values. The chart compares four strategies:

• global mean,
• most-specific mean,
• median cascade with a fixed \(n_{\min}\),
• median shrinkage toward parent levels.

It is not a proof. It is a compact stress test for the failure modes above. Try raising the outlier rate or making the traffic sparser; the brittle local mean usually gets worse before the fallback strategies do.

Sparse traffic:

45%

Outlier rate:

8%

Shrinkage skepticism:

30

Global mean Local mean Median cascade Shrinkage

Deterministic toy data; lower mean absolute error is better. The fixed cascade uses n_min = 25.

The interesting lesson is not that the shrinkage curve always wins. It will not. The lesson is that the baseline is strong and legible. If a learned model cannot beat this under a fair time split, with comparable latency and better monitoring, then the model is probably solving a problem we do not yet have.

Keep the Serving Path Dull

At serving time, the simplest implementation is a cascade of left joins. The first non-null estimate wins:

WITH request AS (
  SELECT
    CAST(COALESCE(validating_carrier, '') AS VARCHAR) AS carrier,
    CAST(COALESCE(cabin, '') AS VARCHAR) AS cabin,
    CAST(CONCAT(COALESCE(origin_airport, ''), '-', COALESCE(destination_airport, '')) AS VARCHAR) AS route,
    CAST(COALESCE(outbound_flight_number, '') AS VARCHAR) AS flight_number
  FROM requests_to_price
)
SELECT
  request.*,
  COALESCE(l4.median_yq, l3.median_yq, l2.median_yq, l1.median_yq, l0.median_yq) AS estimated_yq,
  COALESCE(l4.cache_level, l3.cache_level, l2.cache_level, l1.cache_level, l0.cache_level) AS cache_hit_level,
  COALESCE(l4.n, l3.n, l2.n, l1.n, l0.n) AS cache_hit_n
FROM request
LEFT JOIN yq_fallback_cache l4
  ON request.carrier = l4.carrier
 AND request.cabin = l4.cabin
 AND request.route = l4.route
 AND request.flight_number = l4.flight_number
 AND l4.cache_level = 4
 AND l4.n >= 30
-- Continue down the hierarchy.
LEFT JOIN yq_fallback_cache l0
  ON l0.cache_level = 0;

In production I would usually generate this SQL rather than hand-maintain a dozen joins. The generated code should still be explicit in the compiled query; when a fallback system breaks, clever metaprogramming is not what you want to read first.

For shrinkage, compute parent estimates in the cache build or in a second materialization step. Serving should stay boring:

SELECT
  request_id,
  shrunken_median_yq AS estimated_yq,
  cache_level,
  n,
  parent_cache_level
FROM yq_fallback_cache_ready
WHERE lookup_key = :normalized_lookup_key;

Dashboards Are Control Surfaces

The cache is only trustworthy if you can see how it behaves. I would ship it with four dashboard panels:

1. Hit rate by fallback level. A sudden jump to global is an incident, not a harmless detail.
2. Error by fallback level on delayed ground truth. Specific levels should earn their specificity.
3. Rejected cells by reason: too few observations, stale window, missing key, or quality filter.
4. Drift by major segment. Track medians for top carriers, routes, cabins, and date buckets.

Those panels also tell you when machine learning becomes worth it. If the fallback hierarchy is stable but systematically biased for known feature interactions, a model may help. If error is mostly from missing recent data, a model may only make the failure less obvious.

Where This Baseline Deserves to Lose

This is not an argument against machine learning. It is an argument against using it before the baseline has a spine.

A fallback cache struggles when the relevant signal is not present in the grouping keys, when the world shifts faster than the rebuild window, when missingness is highly non-random, or when the target itself is redefined by policy. It also does not produce calibrated uncertainty intervals by default. You can add them with bootstrap intervals, Bayesian hierarchy, or conformal calibration, but they should be designed deliberately.

Approximate quantiles also need testing. They are excellent for large-scale systems, but they are still approximations. If a financial close process depends on exact cents, separate “fast estimate” and “official accounting” paths.

Ask the Model Question Last

The best non-ML baseline is not a weak straw man. It is a serious system: normalized keys, robust quantiles, rollup hierarchy, minimum evidence thresholds, partial pooling, freshness windows, and level-by-level monitoring.

Only after that baseline exists can we ask the machine-learning question honestly:

What does the model learn that the reference-class hierarchy cannot, and is that improvement worth the operational surface area?

Sometimes the answer is yes. But a surprising amount of intelligence comes from knowing when a smaller, more inspectable idea is enough, especially at 3 AM when the system owes people an answer.

References

1. Brian D. Ripley, “Robust Statistics”, Oxford lecture notes. The note summarizes breakdown points and contrasts the mean with the median. ↩

2. AWS, “SELECT - Amazon Athena”, on GROUPING SETS, CUBE, and ROLLUP. ↩

3. Trino documentation, “Aggregate functions”, for approx_percentile. ↩

4. AWS, “Athena engine version 3”, noting the t-digest implementation change for approx_percentile. ↩

5. Michael Greenwald and Sanjeev Khanna, “Space-Efficient Online Computation of Quantile Summaries”, SIGMOD 2001. ↩

6. Ted Dunning and Otmar Ertl, “Computing Extremely Accurate Quantiles Using t-Digests”, 2019. ↩

7. Bradley Efron and Carl Morris, “Data Analysis Using Stein’s Estimator and Its Generalizations”, Journal of the American Statistical Association, 1975. ↩

8. Andrew Gelman et al., “Bayesian Data Analysis, 3rd edition”, for hierarchical modeling and partial pooling. ↩