Unit 12.01: Metadata that makes an answer citable

Unit ID: SQL-M12-U02 - Estimated active time: 13-16 minutes Objective: attach the fields a reader needs to verify or challenge an assistant's answer.

A citation is only useful if it leads somewhere

An assistant says "Order 501 was completed for ₹1,000." A reader who wants to check that needs to know which record it came from, when it was last true, and where the data originated.

That is metadata, and it has to be added at preparation time - it cannot be recovered afterwards.

The fields worth carrying

-- Rebuild the same table with its metadata attached, rather than keeping a
-- second copy around. Retrieval tables get enriched; they do not get versioned
-- into order_facts_v2, order_facts_final, order_facts_final_2.
CREATE OR REPLACE TABLE order_facts AS
SELECT
  'order-' || o.order_id  AS doc_id,          -- what to cite
  '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.placed_at             AS source_updated_at, -- how current it is
  'orders+customers'      AS source_tables,   -- where it came from
  'internal'              AS visibility,      -- who may see it
  o.order_id,                                 -- the source key, kept for joins back
  o.status,                                   -- filterable facets
  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;

Each column earns its place:

Facets do the work a vector search cannot

Semantic similarity is poor at exact constraints. "Completed orders from India in June" should be a filter, not a hope:

SELECT COUNT(*) FROM order_facts
WHERE country = 'IN' AND status = 'completed';

Filtering first shrinks the candidate set to rows that are definitely eligible, then similarity ranks within it. Metadata is what makes that possible.

The date field is not decoration

SELECT COUNT(*) FROM order_facts
WHERE source_updated_at < '2026-06-15';
-- 472

472 rows are more than two weeks old at that cutoff. Without source_updated_at you cannot answer "how stale is this answer?" - and the assistant will quote all of it with equal confidence.

Practice

Which single metadata field would you add first to a table with none, and why?

Takeaway

Carry identity, freshness, provenance, visibility, and facets. Metadata cannot be reconstructed later, and without it no answer is checkable.

---

Mark complete