Cobalt 3: join history at the right point in time

Define the time contract

Original fictional customer segments. Dates are ISO YYYY-MM-DD days. Segment intervals use [valid_from,valid_to): start inclusive, end exclusive; NULL end means open-ended. The question is the segment effective on each order day. It is not “what did the warehouse know at an earlier reporting time?” That requires a separate system-time axis.

Mini fixture

A is starter from January1 until February1, then business with no end. B is starter from January1 with no end. C has no dimension row. Orders: f1 A January31 10,000; f2 A February1 12,000; f3 B February2 7,000; f4 C February2 3,000. Amounts use integer minor units.

customer | valid_from | valid_to   | segment
A        | 2026-01-01 | 2026-02-01 | starter
A        | 2026-02-01 | NULL       | business
B        | 2026-01-01 | NULL       | starter

Your task

Assign each fact to at most one effective segment. Keep unmatched facts as UNMAPPED, a reserved reporting label. Predict results at the exact February1 boundary. Design interval-overlap and fact-match-count checks before writing the final aggregation. A primary key on customer and valid_from does not rule out overlaps.

Worked solution

f1 maps to starter; f2 maps to business because the previous interval excludes February1. f3 maps to starter; f4 is UNMAPPED. Output: starter 2/17,000; business 1/12,000; UNMAPPED 1/3,000. Counts sum to four and values to 32,000. An inner join loses the unmatched fact; joining every order to the latest segment misattributes f1 even though totals still reconcile.

SELECT COALESCE(d.segment,'UNMAPPED'),COUNT(*),SUM(f.amount_minor)
FROM orders f LEFT JOIN customer_segments d
 ON f.customer_id=d.customer_id
 AND f.order_day>=d.valid_from
 AND (d.valid_to IS NULL OR f.order_day<d.valid_to)
GROUP BY COALESCE(d.segment,'UNMAPPED');

Overlap counterexample

Add A interval [January15,February15) with a different segment. Both f1 and f2 now match two rows, inflating joined count to six and value to 54,000. DISTINCT or an arbitrary row_number hides the invalid contract. Detect the overlap and stop attribution publication until history is repaired.

Gap and boundary exercise

Remove B’s history from a scratch copy. Predict the UNMAPPED total while preserving the source facts. Add a new order exactly February1 and verify only the business interval matches. Check date validity before comparing strings; ISO-looking column names are insufficient evidence of valid values.

Gap solution

Without B, UNMAPPED has two orders totalling 10,000; business stays 1/12,000 and starter becomes 1/10,000. Source total remains 4/32,000. A gap should create a visible stewardship task, not silent fact loss. A later history correction may require reattributing affected facts.

Review and explain

Score 0–2 for fact grain, half-open boundary, overlap failure, gap handling and business-time vs system-time distinction. Explain why this historical attribution join alone does not establish a leakage-free machine-learning feature table.

Authoritative reference

Read the official reference ↗

PostgreSQL Global Development Group, Range Types. Review half-open ranges and overlap constraints. Our runnable project uses explicit SQLite predicates, not PostgreSQL range types. Next: Cobalt 4.

Make the reasoning yours.

Try the activity before reading its reference. Keep your sources, commands and assumptions with your work. Companion activities are unsaved self-practice and do not award a certificate or change lesson progress.