A week cleaning 290 booking records taught me more about silent failure than any error message ever has
Last week I cleaned a deliberately messy dataset; 290 booking records from Safari Connect, Nairobi bus platform, 21 columns, 23 catalogued data problems. Class exercise, but the data was built from real failure modes.
The problems I'd been warned about took an afternoon. The ones that cost me were the five that ran perfectly, returned plausible output, and were wrong.
Every one of these produced a result. None produced an error.
1. The date heuristic that silently dropped five bookings
The dataset had three date formats in one column: 2024-09-15, 15/09/2024,and 09-25-2024. Two of those are ambiguous - 01-18-2024 is unmistakablyMM-DD-YYYY because there's no month 18, but 04-10-2024 could be either.
The supplied guide handled it like this:
UPDATE bookings_staging
SET departure_date = TO_DATE(departure_date, 'MM-DD-YYYY')::TEXT
WHERE departure_date LIKE '%-%'
AND LENGTH(departure_date) = 10
AND SPLIT_PART(departure_date, '-', 2)::INTEGER > 12;
Read that last condition. If the second component is too large to be a month,this must be month-first. Reasonable logic - and it only fires when the day happens to be 13 or higher.
Five rows had days between 1 and 12. They never converted. Then the next step filtered on ISO format:
INSERT INTO bookings SELECT ... FROM bookings_staging
WHERE departure_date SIMILAR TO '[0-9]{4}-[0-9]{2}-[0-9]{2}';
...and dropped them. No error. No warning. Five completed bookings and KES 3,840 of revenue gone from every downstream total.
The guide's expected row count was written as "~280+", which is loose enough to hide it.
The fix is to match on shape, not to infer from values:
WHERE departure_date ~ '^\d{2}-\d{2}-\d{4}$'
Anchored patterns are mutually exclusive, so you can classify every row before touching any of it:
SELECT
CASE
WHEN departure_date ~ '^\d{4}-\d{2}-\d{2}$' THEN 'ISO'
WHEN departure_date ~ '^\d{2}/\d{2}/\d{4}$' THEN 'DD/MM/YYYY'
WHEN departure_date ~ '^\d{2}-\d{2}-\d{4}$' THEN 'MM-DD-YYYY'
WHEN departure_date ~ '^\d{2}-\d{2}-\d{2}$' THEN 'DD-MM-YY'
ELSE 'UNRECOGNISED'
END AS pattern,
COUNT(*)
FROM bookings_staging GROUP BY 1;
If UNRECOGNISED is non-zero, stop. That bucket is the whole point.
Takeaway: when a filter silently discards rows, count what you're about to lose before you lose it. WHERE clauses in an INSERT ... SELECT are the quietest place data disappears.
2. A whole column of zeros from operator precedence
Month-over-month growth. Straightforward:
ROUND((revenue - LAG(revenue) OVER (ORDER BY month))
/ NULLIF(LAG(revenue) OVER (ORDER BY month), 0) * 100.0, 1) AS change_pct
The 100.0 is there. The NULLIF guards division by zero. It ran. Every
value came back 0.
* and / have equal precedence and evaluate left to right. So this computes (revenue - prev) / prev first - two integers truncates to 0, and only then multiplies by 100.0. The decimal arrives after the damage.
-- Wrong: division happens first, on integers
(a - b) / c * 100.0
-- Right: one operand is numeric before the division
(a - b) * 100.0 / c
A column of zeros with no error is the signature of integer division. Whenever a percentage comes back suspiciously round, check where the decimal enters the expression - not whether it's present.
3. A view that made one of the questions unanswerable
The reporting layer had a clean view:
CREATE OR REPLACE VIEW v_clean_trips AS
SELECT * FROM bookings
WHERE booking_status = 'Completed';
Sensible. Analysis should run on completed journeys.
Except one of the six business questions was "what is our cancellation rate and what did cancellations cost us?"
Built on that view, the answer is 0% and KES 0. Not an error - a confident, precise, completely wrong answer. The rows it needs are exactly the rows it filters out.
The fix is two views with an explicit split:
CREATE OR REPLACE VIEW v_all_bookings AS
SELECT *,
CASE WHEN booking_status = 'Completed' THEN total_fare ELSE 0 END AS realised_revenue,
CASE WHEN booking_status <> 'Completed' THEN total_fare ELSE 0 END AS lost_revenue
FROM bookings;
Takeaway: a view encodes an assumption about which rows matter. That assumption is invisible to anyone querying it later. If a question is about the rows you excluded, no amount of correct SQL on top will find them.
4. One extra row that broke a ranking
Ranking drivers by revenue, using a CTE:
WITH driver_totals AS (
SELECT driver_name, vehicle_type,
COUNT(*) AS trips, SUM(total_fare) AS revenue
FROM v_clean_trips
GROUP BY driver_name, vehicle_type
)
SELECT driver_name, revenue,
RANK() OVER (ORDER BY revenue DESC) AS overall_rank
FROM driver_totals;
Eight drivers. Nine rows.One driver operated two vehicles - 31 trips on a minibus and 2 on a bus. So GROUP BY driver_name, vehicle_type split him in two. His real total of 28,235 would have ranked 5th. Split, his fragments landed 6th and 9th.
And overall_rank was now ranking driver-vehicle combinations while the column name and the driver_name beside it said otherwise.
Everything ran. The output looked like a driver ranking. It wasn't one.
-- One row per driver; MODE() picks their most-used vehicle for the label
SELECT driver_name,
MODE() WITHIN GROUP (ORDER BY vehicle_type) AS main_vehicle,
SUM(total_fare) AS revenue
FROM v_clean_trips
GROUP BY driver_name;
Takeaway: run the CTE body alone and count the rows before you build on it. Nine where you expected eight is visible in three seconds and invisible forever afterwards.
5. Statistics that looked stronger than they were
This one isn't SQL, but it's the one I'd have got most wrong.
Eight drivers, each with a platform rating and an average passenger rating. The correlation between them came out at r = −0.507 - higher platform ratings going with lower passenger satisfaction. A tidy, counterintuitive,very quotable finding.
At n = 8, the critical value for significance is 0.707. My -0.507 gives p = 0.199. It isn't significant. Quoting it as a finding would have been overclaiming, and it's the exact thing a numerate stakeholder asks about.
But there was a second pattern in the same data. All 8 of 8 drivers were rated higher by the platform than by their passengers. Under a null of no bias, each driver is equally likely to fall either side, so 8/8 in one direction is:
p = 2 × (1/2)^8 = 0.008
That is significant. And it rests on 239 rated trips, not 8 numbers.Same dataset, same eight rows, two findings - one I couldn't defend, one I could. The weaker claim was the more interesting-sounding one.
Takeaway: small samples don't mean you can't conclude anything. They mean you have to pick the claim the sample actually supports. Directional consistency across all units is often much stronger evidence than a correlation coefficient computed from the same handful of points.
The pattern
Four of these five produced output that looked right. That's the real lesson:an error message is a gift. It tells you exactly where and what. Silent failure gives you a number, and numbers are persuasive.
What actually caught them, in every case, was the same habit - checking a count against an expectation:
- 288 rows in staging, 288 in production. Not "~280+"
- 10 rows out of the route CTE, 8 out of the driver CTE
- Route revenues summing to the same total as monthly revenues
- Percentage columns summing to 100
Every bug above surfaced as a number that didn't match a number. None surfaced as an error.
So write the expected value in a comment beside the query. Then check it.
SELECT COUNT(*), SUM(total_fare) FROM v_clean_trips;
-- must read: 253 · 227,810
That comment is the cheapest test you will ever write.
Full pipeline and SQL on GitHub.
If you've hit a silent one I've missed, I'd like to hear it.
