Unit 12.00: Shaping a table an assistant can retrieve from

Unit ID: SQL-M12-U01 - Estimated active time: 14-17 minutes Objective: build a retrieval table with a stable identifier and self-contained content.

Retrieval needs one row per retrievable thing

An assistant retrieves rows and shows them to a model. So the grain question from Module 1 returns with a new answer: one row is one thing you would want the assistant to find and cite.

CREATE TABLE order_facts AS
SELECT
  'order-' || o.order_id AS doc_id,
  'Order ' || o.order_id || ' placed on ' || CAST(o.placed_at AS DATE)
    || ' for ' || COALESCE(c.country, 'unknown country')
    || '. Status ' || o.status || '. Total ' || o.order_total || '.' AS content,
  o.order_id,
  o.status,
  CAST(o.placed_at AS DATE) AS placed_on,
  COALESCE(c.country, 'UNKNOWN') AS country
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id;

SELECT COUNT(*) FROM order_facts;   -- 1000

One row per order, exactly as intended. Check that the join did not change the grain - 1,000 in, 1,000 out

• because Module 5's fan-out would silently duplicate every fact you are about to expose.

Content must stand alone

SELECT content FROM order_facts WHERE order_id = 501;
-- Order 501 placed on 2026-06-21 for IN. Status completed. Total 1000.00.

That sentence is understandable with no surrounding context. Compare a chunk that says only Status completed. Total 1000.00. - retrieved on its own, the model cannot tell which order it describes, and will happily attach it to the wrong one.

Every retrievable row must name its own subject. This is the single most common defect in hand-built retrieval tables.

A stable, unique identifier

SELECT COUNT(*) AS rows, COUNT(DISTINCT doc_id) AS ids FROM order_facts;
-- 1000 | 1000

doc_id must be unique - otherwise a citation points at more than one thing - and it must be stable, so a rebuild does not renumber everything and invalidate every citation ever issued. Derive it from the source key ('order-' || order_id), never from a row number.

Size the content deliberately

SELECT ROUND(AVG(LENGTH(content)), 1) AS avg_chars,
       MAX(LENGTH(content))           AS max_chars,
       COUNT(*) FILTER (WHERE content IS NULL) AS empty
FROM order_facts;
-- 71.6 | 84 | 0

Short, uniform, and no empty rows. Zero empty content matters: a NULL or blank row is retrievable and carries no information, so it can only ever displace something useful.

Practice

Build a retrieval table over customers at one row per customer, and verify grain and uniqueness.

CREATE TABLE customer_facts AS
SELECT 'customer-' || customer_id AS doc_id,
       'Customer ' || customer_id || ' is based in '
         || COALESCE(city, 'an unrecorded city') || ', '
         || COALESCE(country, 'unknown country')
         || '. Joined ' || signed_up || '.' AS content,
       customer_id, country
FROM customers;

SELECT COUNT(*) AS rows, COUNT(DISTINCT doc_id) AS ids FROM customer_facts;
-- 4812 | 4812

Takeaway

One row per retrievable thing, content that names its own subject, and a stable unique doc_id derived from the source key.

---

Mark complete