Unit 10.04: Re-validating after the final small edit

Unit ID: SQL-M10-U05 - Estimated active time: 12-15 minutes Objective: treat any change to a validated query as invalidating the validation.

The last edit is the dangerous one

The failure pattern is consistent: build carefully, validate thoroughly, then make one small change just before sending - add a column, adjust a filter, rename something - and ship without re-checking.

That final edit arrives *after* the careful thinking has stopped, which is exactly why it is the one that breaks things.

Changes that look cosmetic and are not

Adding a column from another table turns a validated query into a join:

-- Validated: 988 rows
SELECT order_id, order_total FROM orders WHERE status = 'completed';

-- "Just adding the item count" - now at item grain, 3,364 rows
SELECT o.order_id, o.order_total, i.sku
FROM orders o
JOIN order_items i ON i.order_id = o.order_id
WHERE o.status = 'completed';

Every aggregate downstream is now inflated. The edit looked like adding a field.

Widening a filter changes the population:

-- fragment: WHERE clause shown on its own
-- 988 orders, ₹26,85,905
WHERE status = 'completed'

-- 1,000 orders, ₹27,01,463 - a 0.58% change nobody announced
-- (filter removed)

Changing a date boundary by one day moves 33 orders (Module 8).

The re-validation is short

You do not repeat the whole process. Re-run the two checks that would catch a grain or population change:

-- Population unchanged?
SELECT COUNT(*) FROM orders WHERE status = 'completed';   -- 988

-- Total unchanged?
SELECT SUM(order_total) FROM orders WHERE status='completed';  -- 2685905.00

Thirty seconds. If both match what you validated, the edit was genuinely cosmetic.

Keep the check with the query

Leave the reconciliation query in the file beside the analysis, commented:

-- VALIDATION (expect 988 | 2685905.00)
-- SELECT COUNT(*), SUM(order_total) FROM orders WHERE status = 'completed';

Now the expected values travel with the query, and anyone editing it later - including you in three months - can confirm in one paste that they have not changed the answer.

Practice

You add ORDER BY order_total DESC LIMIT 100 to a validated revenue query before sending it. What re-check do you run, and what would you expect?

Takeaway

Any edit invalidates the validation. Re-run the population and total checks - thirty seconds - and keep the expected values in the file so the check survives you.

---

Mark complete