Unit 12.04: Data minimisation before you expose a table

Unit ID: SQL-M12-U05 - Estimated active time: 13-16 minutes Objective: decide what must not be in the retrieval table at all.

The safest data is the data you did not include

Every column you expose is a column that can be retrieved, quoted, logged, and cached. Minimisation is not caution for its own sake - it bounds the damage of every other failure.

Ask of each column: does answering the intended questions require this? If not, leave it out.

Applying it to our table

-- fragment: a column checklist, not a query
-- Included: needed to answer and to cite
doc_id, content, order_id, status, placed_on, country,
source_updated_at, source_tables, visibility

-- Excluded: not required for the questions this assistant answers
customer name, exact timestamps beyond the date, internal customer_id

Note that content names the country but not the customer. An assistant answering "how many completed orders from India in June?" never needs to know who placed them - so it should not be able to say.

Aggregate when the detail is not needed

If the questions are about totals rather than individual orders, expose totals:

CREATE TABLE country_summary AS
SELECT COALESCE(c.country, 'UNKNOWN') AS country,
       COUNT(*)                       AS orders,
       SUM(o.order_total)             AS revenue
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
GROUP BY 1;

Five rows instead of a thousand, and no row-level data to leak. When aggregation answers the question, it is both safer and cheaper.

The three questions before exposing any table

1. What questions must this answer? Anything beyond that scope is surplus.
2. What is the worst thing a wrong or leaked answer could do? That sets how careful the rest must be.
3. Who will be able to retrieve it? If the answer is "anyone with access to the assistant", assume the

most junior person in that group.

Logs are a second copy

Retrieval systems log queries and retrieved content for debugging. Those logs contain the same data with weaker access controls and longer retention than anyone intends. Minimising the table minimises the logs too - one more reason the excluded column is the safest one.

Practice

Your assistant answers "how many orders came from each country last month?" Which columns are genuinely required?

SELECT COALESCE(c.country,'UNKNOWN') AS country, COUNT(*) AS orders
FROM orders o JOIN customers c ON c.customer_id = o.customer_id
WHERE o.placed_at >= '2026-06-01' AND o.placed_at < '2026-07-01'
GROUP BY 1 ORDER BY orders DESC;

Takeaway

Include only what the intended questions require, aggregate when detail is unnecessary, and remember the logs hold a second copy of whatever you expose.

---

Mark complete