SQL Libraries: Define Once, Compose

Concept. A library packages the SQL you repeat as named, parameterized queries, so you define a term once and compose it everywhere. Each definition fixes a subtle choice once, what "a listen" means or how a NULL counts, so every question built on it computes the same way. This is ordinary SQL engineering: a team builds a governed layer of definitions, with or without anything on top.

Intuition. Define listener_of(artist) and active_since(days) once. "Active Beatles listeners" is active_since(7) ∩ listener_of('Beatles'), composed from the two, never rewritten.

You have written queries and seen how they run. But a real team asks the same question a hundred ways. Alice calls a user active if they listened this week; Bob counts the last thirty days; Claude, asked to draft the query, picks a third rule. Three dashboards, three definitions of "active user," and they drift apart across the company. The fix is the one software reaches for with any repeated shape. Name it, pin it once, and reuse it.

Define Once, Compose

A definition is a named, parameterized query you call by name. You write the shape once and pass the specifics in. In Postgres that is a table-valued function; in a semantic layer like dbt it is a named metric. Each one also pins a subtle choice a one-off query would leave open.

-- definitions.sql: each term is a named function you compose by name.

-- Step 1: Listener of an artist
CREATE FUNCTION listener_of(artist TEXT) RETURNS TABLE(user_id INT) AS $$
  SELECT DISTINCT l.user_id
  FROM Listens l JOIN Songs s ON l.song_id = s.song_id
  WHERE s.artist = listener_of.artist          -- the parameter, qualified
$$ LANGUAGE sql STABLE;

-- Step 2: Active in a recent window
CREATE FUNCTION active_since(days INT) RETURNS TABLE(user_id INT) AS $$
  -- a day is a calendar day in UTC (midnight boundary), not a rolling 24h;
  -- a NULL listen_time never counts as active.
  SELECT DISTINCT user_id FROM Listens
  WHERE listen_time >= CURRENT_DATE - days
$$ LANGUAGE sql STABLE;

-- Step 3: Liked at or above a rating
CREATE FUNCTION liked(min_rating NUMERIC) RETURNS TABLE(user_id INT, song_id INT) AS $$
  -- a NULL rating is not a like, so Daffy's unrated play drops out.
  SELECT DISTINCT user_id, song_id FROM Listens
  WHERE rating >= min_rating
$$ LANGUAGE sql STABLE;

Why STABLE. With no marker Postgres assumes VOLATILE and plans the function as a black box: it builds the whole definition, then throws away what you did not ask for. STABLE lets it inline the body and use your indexes. Filtering listener_of to one user, on 3M listens: 14ms black box, 0.2ms inlined. Nothing is traded away; STABLE is simply true here, since the function reads and never writes.

These three are just examples; a real library has dozens. What matters is that each one pins a decision that would otherwise drift. Take active_since: is "a day" the last rolling 24 hours, or a calendar day in UTC? Both are defensible, so Alice and Bob would pick differently. The definition settles it once (a UTC calendar day, and a NULL time never counts), and liked settles that a NULL rating is not a like. The comments carry as much as the code, because they record those forks. Pin them once, and every query built on the term inherits the same answer, so no two dashboards drift apart. The name is the interface: you compose definitions by calling them, so active_since(7) ∩ listener_of('Beatles') is "active Beatles listeners" without anyone rewriting it. (A CTE names a query only inside itself, so a CTE inlines a definition while you compose; the stored library is these named functions.)

What a Library Spans

A handful of definitions is small, but it composes into a large space of questions, none of them written out.

A DEFINED panel of three parameterized primitives (listener_of, active_since, liked) composes into a large green ANSWERABLE region of questions, none written out: All minus listener_of('Taylor'), active_since(7) intersect listener_of('Beatles'), listener_of('Taylor') intersect liked(4.0). A dashed power_user chip sits in the DEFINED set as the primitive you add next, and adding it unlocks a new dashed green composition. Past a hard boundary is a grey OFF THE TABLE region: subjective, missing-data, or predictive questions.

Figure 1. A few defined terms (blue) compose into a large answerable domain (green), none of those queries written out. Add one term (power_user) and a new composition appears: the domain grows by adding to the library, not by anything in the answerable set. Past a hard boundary is what no definition can reach (grey): subjective, missing-data, or predictive questions.

Answerable is the span of your library: any composition of your terms, at any value. "Users who don't listen to Taylor" is All − listener_of('Taylor Swift'); "active Beatles listeners" is active_since(7) ∩ listener_of('Beatles'). You wrote neither, and both are correct because the pieces are verified and the composition is mechanical. A gap is a question no term expresses yet, like "power users," so you add one definition and every question built on it becomes answerable too. Off the table is where no definition reaches, because the answer is not in the data to define: "which songs are good" has no ground truth in the schema, "why did Pluto stop listening" needs data you do not store, "will Daffy churn" is a prediction, not a recorded fact. It is not that these are forbidden; it is that no current definition crosses the line, because there is nothing in the data to pin one to.

Takeaways

  1. Define once, compose. A named, parameterized query is the interface; you call it, never re-derive it, so answers stay consistent.

  2. A definition pins a fork. The UTC day boundary, the NULL-is-not-a-like rule: decide it once in the term, and every query inherits it.

  3. A few terms span a domain. Grow what you can answer by adding a definition, not by writing more queries.