Case Study 2: The Sessions That Were Not There

"The midnight hour had a high bounce rate, everyone knew it was bots, and so everyone threw the hour away."

Executive Summary

Kestrel's silver.sessions model reported a bounce rate of 39.6% in the 00:00 UTC hour against a 31.8% baseline — a 7.8-point excess, every single day, for fourteen months.

The team's explanation was crawler traffic. It was a good explanation: overnight bots do bounce, and the excess was confined to one hour. The response was to exclude the 00:00 hour from behavioural reporting.

Midnight UTC is 8:00 pm Eastern — Kestrel's second-busiest hour of the day. The exclusion threw away 6.7% of real traffic from every analysis for over a year.

There were no crawlers. The model ran on a daily batch, and a session in progress at midnight was cut in half: the portion after midnight became a new, single-event, zero-duration session. The boundary manufactured bounces, roughly 7,000 a night, all landing in the first hour.

The fix is §18.9's overlap — four lines. What makes this case study worth reading is why it survived fourteen months: the artifact was confined, regular, and had a plausible explanation waiting to receive it.

Skills applied: sessionization (§18.9); the batch-boundary artifact (§18.9's ⚠️ callout); window overlap and idempotent writes (Chapter 13 §13.4); reading a measurement against an independent implementation.

Background

The model. silver.sessions groups clickstream events into sessions with a 30-minute inactivity gap — the query from §18.9.

The scale. Kestrel's clickstream is 14,000,000 events/day (Chapter 1 §1.5). Events include page views, clicks, scrolls, searches, and cart actions, averaging about 20 events per session, which puts the model at roughly:

$$\frac{14{,}000{,}000\ \text{events/day}}{20\ \text{events/session}} = 700{,}000\ \text{sessions/day}$$

The schedule. Daily, at 02:00 UTC, over the previous UTC day.

The model as it ran, simplified:

WITH e AS (
    SELECT * FROM silver.events
     WHERE event_ts >= :day_start
       AND event_ts <  :day_end            -- ← the boundary
),
flagged AS (
    SELECT *,
           CASE WHEN LAG(event_ts) OVER w IS NULL
                  OR event_ts - LAG(event_ts) OVER w > INTERVAL '30 minutes'
                THEN 1 ELSE 0 END AS is_new_session
      FROM e
    WINDOW w AS (PARTITION BY anonymous_id ORDER BY event_ts)
)
...

LAG at the first row of a partition returns NULL, which the CASE treats as a session start. That is correct within a window and wrong at its edge, where a previous event exists and is simply outside the filter. The SQL cannot tell the two cases apart — both look like "no previous row."

The consumers. gold.fct_session, the growth team's weekly review, and two funnel dashboards.

The Problem

Bounce rate — sessions with exactly one page view — by hour of day, averaged over fourteen months:

hour (UTC)    sessions    bounce rate
----------    --------    -----------
 00:00          53,748       39.6%    ← 
 01:00          44,905       31.9%
 02:00          40,112       31.7%
 ...
 12:00          21,088       31.8%
 ...
 22:00          46,201       31.7%
 23:00          47,880       31.9%
              --------       -----
 all           708,412       32.4%

7.8 points above baseline, in exactly one hour, every day.

And in the daily aggregate it is 32.4% against a true 31.8% — six tenths of a point, which is inside anything anyone would call noise. The artifact is glaring at one grain and invisible at the grain most people look at.

The explanation on file was crawler traffic: bots sweep overnight, fetch one page, and leave. Reasonable. Kestrel does get crawled. Nobody checked it, and the operational consequence was a WHERE EXTRACT(hour FROM session_start) <> 0 in the growth team's reporting views.

Midnight UTC is 8:00 pm Eastern. The hour being discarded as bot noise was the second-busiest hour of Kestrel's day.

The Analysis

The engineer who found it was doing something else: sizing a capacity estimate, and needing a session count they trusted, they recomputed one from raw events. The numbers did not agree.

Step 1: count the sessions, and then count them a second way.

-- What the model says for one ordinary Tuesday.
SELECT COUNT(*) FROM silver.sessions
 WHERE session_start >= '2026-03-17' AND session_start < '2026-03-18';
-- 708,412

-- Recomputed from raw events over a range that straddles both boundaries by a
-- day, then filtered to the same day.
WITH s AS ( /* same sessionization, over Mar 16 -- Mar 19 */ )
SELECT COUNT(*) FROM s
 WHERE session_start >= '2026-03-17' AND session_start < '2026-03-18';
-- 701,377

$$708{,}412 - 701{,}377 = 7{,}035\ \text{sessions the model has and the recomputation does not}$$

$$\frac{7{,}035}{708{,}412} = 0.99\%\ \text{of reported sessions do not exist}$$

🔎 Read the Plan — the reconciliation that found it was not looking for it

Nobody was auditing the bounce rate. It had an explanation, and the explanation was load-bearing enough that an hour of data had been deleted from reporting on its strength.

The discrepancy surfaced because someone computed the same quantity a second way, for an unrelated reason, and the two numbers disagreed.

This is how measurement errors are actually found, and it is worth building deliberately rather than waiting for:

  • Compute your important aggregates twice, by different code paths, on a schedule.
  • Alert on the divergence, not on either value — neither one is the truth.
  • The second path does not have to be efficient or complete. One day a week is enough.

Kestrel now runs silver.sessions_shadow: one random day per week, recomputed from raw events over a ±2-hour window, asserting agreement with the model within 0.1%. It reads about 15 million rows and costs one node-hour a week — $1.20, or $62.40 a year on the Chapter 3 cost basis. It would have caught this in the first week.

The general form: a measurement with only one implementation has no error bar.

Step 2: find out where the excess lives. It was not spread across the day:

SELECT date_trunc('hour', session_start) AS h, COUNT(*)
  FROM silver.sessions
 WHERE session_start >= '2026-03-17' AND session_start < '2026-03-18'
 GROUP BY 1 ORDER BY 1;

hour (UTC)         model      recomputed    excess
2026-03-17 00:00   53,748        46,713     7,035   ← all of it
2026-03-17 01:00   44,905        44,905         0
2026-03-17 02:00   40,112        40,112         0

Every phantom is in the first hour of the batch. That is what a boundary artifact looks like, and essentially nothing else produces that shape.

Step 3: characterize the phantoms.

                        all sessions    the 7,035 excess
--------------------    ------------    ----------------
median page views                  3                   1
median duration                4m 12s                  0s
share single-page              31.8%               91.4%
share with a referrer          68.2%                4.1%
share with prior-day
  activity in raw events         n/a              100.0%

The last row settles it. Every one of the 7,035 has events before midnight in the raw stream — a crawler that arrives at 00:00 does not have a 23:47 page view. And 4.1% carry a referrer because a session's continuation has no referrer; only its first page does. These are the tails of real sessions, severed from their heads.

Step 4: the arithmetic, which has to reconcile.

If the true bounce rate is 31.8% and 91.4% of the phantoms are single-page, the reported 00:00 rate should be:

$$\frac{0.318 \times 46{,}713 + 0.914 \times 7{,}035}{46{,}713 + 7{,}035} = \frac{14{,}855 + 6{,}430}{53{,}748} = \frac{21{,}285}{53{,}748} = 39.6\%$$

Which is what the dashboard shows. And across the whole day:

$$\frac{0.318 \times 701{,}377 + 0.914 \times 7{,}035}{708{,}412} = \frac{223{,}038 + 6{,}430}{708{,}412} = \frac{229{,}468}{708{,}412} = 32.4\%$$

Also what the dashboard shows. A hypothesis that predicts both grains from one mechanism is a finding; one that explains only the grain you noticed is a story.

How many sessions should straddle midnight? Worth checking independently, because if the answer were 200 the mechanism would be wrong. Near 00:00 UTC — 8:00 pm Eastern — sessions start at roughly 779 per minute (46,713 in the hour). With a mean span of about nine minutes once the 30-minute gap is accounted for, the number in flight at any instant is:

$$779\ \text{sessions/min} \times 9\ \text{min} \approx 7{,}010$$

7,010 predicted, 7,035 observed. The mechanism is not merely consistent with the excess; it sizes it.

⚠️ Failure Mode — an artifact with a plausible story waiting for it survives indefinitely

Three properties kept this alive for fourteen months:

1. It was confined. One hour. A problem in one hour reads as a property of that hour, and midnight has properties — batch jobs, crawlers, timezone rollovers — that invite explanation.

2. It was regular. Regularity reads as signal. A one-off spike gets investigated; a pattern that repeats 400 times gets a name and a filter.

3. The explanation already existed. Crawler traffic is real at Kestrel. The story was not invented to cover the artifact — it was already there, and the artifact fell into it.

This is the dangerous case. An anomaly with no explanation gets escalated. An anomaly that confirms something you already believe gets cited, and then gets acted on — here, by deleting the hour.

Note the asymmetry the filter created: once 00:00 was excluded from reporting, the evidence that would have disproven the theory was the first thing removed. A workaround that hides the symptom also hides the disproof.

The defense is not skepticism, which does not scale past the things you happen to doubt. It is the second implementation from the callout above. That is a control. "Be careful" is not.

The Decision

The fix is §18.9's overlap:

WITH e AS (
    -- Read BEFORE the window. Chapter 13 §13.4's overlap, applied to a
    -- transformation rather than to an extract.
    SELECT * FROM silver.events
     WHERE event_ts >= :day_start - INTERVAL '2 hours'
       AND event_ts <  :day_end
),
... sessionize over e ...
SELECT * FROM sessions
 -- Emit only sessions that START inside the target window. The overlap exists
 -- to give the window function context, NOT to add rows -- and without this
 -- filter the overlap creates duplicates instead of fixing phantoms.
 WHERE session_start >= :day_start
   AND session_start <  :day_end;

Two judgments had to be made explicitly.

How much overlap? The inactivity gap is 30 minutes, so 30 minutes of prior events is mathematically sufficient. The team chose two hours, four times the gap:

  • The gap is a configuration value. Setting the overlap equal to it means the day someone raises the gap to 45 minutes, the model silently starts manufacturing phantoms again — and the symptom looks exactly like the bug they just fixed, only smaller.
  • Margin also absorbs late-arriving events near the boundary.
  • The cost is bounded and small. A two-hour overlap on a daily batch reads $14{,}000{,}000 / 12 = 1{,}166{,}667$ extra rows, an 8.3% increase in the model's input. A one-day overlap on a daily batch would have doubled it, which is the trap in copying "use a day of overlap" from a monthly job.

And an assertion, because a comment is not a control:

-- Fails the build if anyone widens the gap past the overlap.
{{ config(pre_hook="{{ assert_overlap_exceeds_gap() }}") }}

Which day does a session that starts at 23:50 and ends at 00:20 belong to? The rule adopted: a session belongs to the window containing its start. Written into the model's documentation, because it is a judgment and not a fact — assigning by end time, or splitting proportionally, are both defensible and produce different numbers.

📐 Design Decision — fix the boundary, or run the batch less often?

A weekly rebuild has one seventh as many boundaries. A monthly rebuild has one thirtieth. The phantom count per boundary is the same ~7,000, so a monthly model's error would be $7{,}000 / 21{,}700{,}000 = 0.03\%$ — undetectable.

This was proposed, and rejected, and the reasoning is the useful part of this case study:

Making an error invisible is not the same as fixing it. The monthly variant is still wrong, and it is wrong in a way that has been engineered past the point of discovery. Every month-boundary analysis still gets bad numbers; nothing will ever surface them again.

The daily cadence is what made this findable at all. A coarser grain would have concentrated the same defect into a place nobody looks.

It also costs freshness. Chapter 1 §1.7's 6am SLA exists because the CEO opens the dashboard at 06:15; a monthly session model cannot serve it.

The generalization: when a bug's visibility and a design's coarseness trade off, treat visibility as the thing you are buying. A defect you can see is cheaper than one you cannot, and "we made the symptom smaller" should always prompt the question of whether the error shrank or only the evidence.

Backfill. Fourteen months were recomputed. The write is idempotent by partition (Chapter 13 §13.7), so this was a re-run, not a migration — 428 days at about four minutes each, run over a weekend.

What Happened

Before After
Sessions, 2026-03-17 708,412 701,377
Phantom sessions/day 7,035 (0.99%) 0
Bounce rate, 00:00 hour 39.6% 31.9%
Bounce rate, all hours 32.4% 31.8%
Model input rows 14.0M 15.2M (+8.3%)
Hours excluded from reporting 1 0

The 00:00 excess disappeared. Whatever crawler traffic Kestrel receives — and it does receive some — is smaller than this model's own noise.

The hour <> 0 filter was removed from the growth team's views. That restored 6.7% of sessions to every behavioural analysis, and it is the part of this incident with a real business cost: fourteen months of funnel analysis had been conducted with Kestrel's second-busiest hour deleted, and the hour that was deleted is the evening-shopping peak, which does not behave like the rest of the day. No attempt was made to restate the historical conclusions, because there is no honest way to say which of them would have changed.

silver.sessions_shadow was added, per the callout above. $1.20/week.

An audit of every window function in the transformation layer — the shape, not the sighting — found two more:

  • silver.customer_first_order computed MIN(order_date) OVER (PARTITION BY customer_id) within the batch, so a returning customer's first order of the day looked like their first order ever. The is_new_customer flag had been wrong for a year. Fixed by deleting the window entirely and joining to a full-history aggregate — the shape was wrong, and no amount of overlap would have fixed it, because the correct context is all of history, not a bit more of it.
  • silver.price_changes used LAG(price) across a daily batch and reported a price change on the first row of every day. Fixed with overlap, which was the right fix there.

Two bugs, two different fixes, one audit. Telling which fix a model needs is the skill: overlap works when the required context is bounded (30 minutes of events); it cannot work when the required context is unbounded (every order the customer ever placed).

Lessons

  1. A window function inside a batch sees only the batch. LAG returns NULL at the first row of a partition, and the SQL cannot distinguish "no previous row exists" from "the previous row is outside my filter."

  2. Overlap the input; emit only records whose start falls in the target window. The WHERE on the output is not optional — without it the overlap replaces phantoms with duplicates.

  3. Size the overlap at a multiple of the gap, and assert the relationship in code. They are coupled, the gap is configuration, and a comment is not a control.

  4. Do not copy an overlap width across cadences. One day of overlap is 3% of a monthly batch and 100% of a daily one.

  5. Write down which window a straddling record belongs to. It is a judgment; the alternatives are defensible and produce different numbers.

  6. An artifact that is confined, regular, and has an explanation already available will survive indefinitely. The story does not have to be invented — it only has to be there.

  7. A workaround that hides a symptom usually hides the disproof too. Excluding the 00:00 hour removed the exact evidence that would have falsified the crawler theory.

  8. Reconcile at two grains. A mechanism that predicts both the 39.6% and the 32.4% from one cause is a finding; one that explains only the number you noticed is a story.

  9. Size the effect independently before believing the mechanism. 779 sessions/minute × 9 minutes ≈ 7,010 predicted against 7,035 observed is what promoted this from plausible to established.

  10. Compute important aggregates twice by different paths and alert on divergence. A measurement with one implementation has no error bar. Here: $62.40 a year, and fourteen months of a deleted peak hour.

  11. Making an error invisible is not fixing it. A coarser batch would have reduced this to 0.03% and beyond reach of discovery, while leaving it wrong.

  12. Audit for the shape, not the sighting — and expect the audit to turn up a case that needs a different fix. Overlap repairs bounded context; it cannot repair unbounded context.

Questions for Discussion

  1. The crawler explanation existed before the artifact did. What practices distinguish a hypothesis that has been confirmed from one that a coincidence happened to fit?

  2. The team declined to restate fourteen months of funnel conclusions. Is that the right call? What would an honest restatement have to establish first?

  3. The shadow model costs $62.40/year and would have caught this in week one. How would you decide which of your models deserve one? What is the property that makes a model a candidate?

  4. The overlap was set to four times the gap rather than the sufficient one times. Argue the other side — what is the cost of a generous overlap, and where does it stop being free?

  5. silver.customer_first_order needed the window removed, not widened. Write the rule that tells you which of the two fixes a model needs, and test it against a case you have seen.

  6. The design-decision callout argues that a coarser cadence would have hidden the bug rather than fixed it. Can you construct a case where reducing a defect's magnitude genuinely is the right response, even knowing it stays wrong?

  7. Two counts of the same quantity differed by 7,035 out of 708,412 and were investigated. What would have happened at 708,412 versus 708,414 — and does your answer suggest a threshold, or something else?

  8. The hour <> 0 filter was added by someone acting reasonably on the best available explanation. What review practice catches a filter like that at the moment it is written?