The request arrives as one sentence in a meeting. Everything expensive that follows is decided by how much of the rest gets written down in the next twenty minutes.
Seven fields, one screen, filled in during the meeting. It is deliberately shorter than the template the client already has, because a form nobody completes is not a control — it is a folder.
The intake, with a worked request (REQ-118)
Field
What it must contain
REQ-118
1 · Requester + owner
two names; the owner is whoever signs the number off
requester: Head of Sales DE. Owner: Finance Controlling.
2 · The question
one sentence, worded as the report title would be
Monthly revenue by customer segment and country, DE / AT / CH / NL.
3 · Decision it changes
what somebody does differently depending on the answer
the quarterly segment-discount review, run today off a spreadsheet.
4 · Grain, slices, as-of
one row per what, sliced by what, and as of when
one row per month × customer_segment × country; segment as of the order date, not today.
5 · Measure definition
the arithmetic and the edge cases, citing the business rule
SUM(amount_eur) excluding status = 'CANCELLED' (rule 1); amount_eur already converted at the order-date rate (rule 2).
6 · Source + freshness
which objects, and how late is too late
gold.fct_order_line + gold.dim_customer; loaded daily by 07:00 CET; month closes on working day 3.
7 · Acceptance test
a query, a number, and who runs it — plus the reconciliation line that says how much of the number is unattributed
the two queries below; the June 2026 total agreed with Finance to the cent, alongside the unknown-member share (rule 7) it contains; the requester runs both.
Field four does most of the work, and field seven is the one that turns the rest into something checkable. Write the acceptance query during intake, against whatever exists today, even if it returns nothing:
Why
Intake is not paperwork. It is the only moment when a definition problem costs nothing to fix. Most the numbers do not match incidents in a warehouse are not bugs — they are one measure defined twice, once in the pipeline and once in someone's head, and both definitions are defensible.
Field three is the one that kills work, which is why it earns a place on a seven-field form. A request with no decision behind it becomes a report that is opened twice, maintained for four years, and included in every migration because nobody can prove it is unused.
How
sqlREQ-118 acceptance test — agreed before build, run by the requester
-- Rule 1: revenue excludes CANCELLED.
-- Rule 7: a fact line either resolves to a customer version valid at the order
-- timestamp, or points at the unknown member customer_sk = 'UNKNOWN'.
-- The unknown member has no dim_customer row satisfying the interval
-- predicate below, so this MUST be a LEFT JOIN. An inner join silently
-- drops those lines and the "agreed to the cent" total arrives short by
-- exactly the unattributed revenue.
-- Field 4: customer_segment AS OF the order date.
SELECT
DATE_TRUNC('MONTH', f.order_ts) AS month,
COALESCE(c.customer_segment, 'UNKNOWN') AS customer_segment,
COALESCE(c.country, 'UNKNOWN') AS country,
ROUND(SUM(f.amount_eur), 2) AS revenue_eur,
COUNT(DISTINCT f.order_id) AS order_count
FROM gold.fct_order_line f
LEFT JOIN gold.dim_customer c
ON c.customer_sk = f.customer_sk
AND f.order_ts >= c._tech_valid_from
AND (c._tech_valid_to IS NULL OR f.order_ts < c._tech_valid_to)
WHERE f.status <> 'CANCELLED'
AND f.order_ts >= TIMESTAMP '2026-06-01'
AND f.order_ts < TIMESTAMP '2026-07-01'
GROUP BY ALL
ORDER BY month, customer_segment, country;
-- The other reading of the same sentence -- segment as of TODAY -- is
-- ON c.customer_sk = f.customer_sk AND c._tech_is_current
-- and returns a different number. Field 4 exists so that choice is made once,
-- by the owner, and not by whoever writes the join.
The LEFT JOIN is not defensive coding — it is business rule 7 written as SQL. Rule 7 permits a fact line to point at the unknown member, and an unknown-member line has no dimension version satisfying the interval predicate. Under an inner join those lines vanish, the grand total is short by their revenue, and nothing in the output says so. Which is why the acceptance test is two queries: the number, and how much of the number is unattributed.
sqlREQ-118 reconciliation line — signed off with the total, not instead of it
-- Two ways a line ends up outside a real customer version:
-- unknown_member the fact carries customer_sk = 'UNKNOWN' (rule 7, expected)
-- unmatched a real customer_sk with NO version covering order_ts
-- (an SCD2 interval gap -- a defect, and it must be 0)
SELECT
ROUND(SUM(f.amount_eur), 2) AS revenue_eur_total,
ROUND(SUM(CASE WHEN f.customer_sk = 'UNKNOWN'
THEN f.amount_eur ELSE 0 END), 2) AS revenue_eur_unknown,
count_if(f.customer_sk = 'UNKNOWN') AS lines_unknown_member,
count_if(f.customer_sk <> 'UNKNOWN' AND c.customer_sk IS NULL)
AS lines_unmatched
FROM gold.fct_order_line f
LEFT JOIN gold.dim_customer c
ON c.customer_sk = f.customer_sk
AND f.order_ts >= c._tech_valid_from
AND (c._tech_valid_to IS NULL OR f.order_ts < c._tech_valid_to)
WHERE f.status <> 'CANCELLED'
AND f.order_ts >= TIMESTAMP '2026-06-01'
AND f.order_ts < TIMESTAMP '2026-07-01';
-- Acceptance criteria for REQ-118, agreed at intake:
-- revenue_eur_total = the figure Finance signs, to the cent
-- revenue_eur_unknown = disclosed with it, not netted out of it
-- lines_unmatched = 0
Doing it
Fill the form in the meeting, out loud, with the requester watching your screenEmailing the template gets it back in three weeks with fields four and five empty.
Write and run the acceptance query during intake, even against an empty goldRunning it proves the objects and the join are agreed.
Left-join every fact-to-dimension hop in an acceptance queryAgree the unknown-member share as a second signed number. A total that quietly excludes rule-7 lines is not the total anyone thinks they approved.
Force the as-of decision explicitly whenever the request touches an SCD2 dimensionRecord which reading was chosen.
Restate the measure in your own words and waitThere is always a correction, and it is always cheaper now.
Issue the requirement ID before anyone leaves the roomEvery branch, PR and table tag downstream references that string.
Embedding it
Have an engineer run the next intake with you in the room and silent unless askedThey run the one after alone.
Back the team the first time they refuse an incomplete intake, in front of the requesterWhether the form is real is decided that afternoon, once, permanently.
Ask 'what gets decided differently?' in refinement until somebody else asks it before you do
Have the team keep the list of requests that died at field threeAt the next steering meeting it is a stronger argument for the process than any slide you could write.
Defaults
Seven fields, one screen, completed in the meeting — not sent as homework
The acceptance test is a query and a number, agreed before a line of build
Any acceptance number resting on a dimension join is signed together with its unattributed shareThe total and the unknown-member line, never the total alone.
Measure definitions cite the business rule number instead of restating prose per ticket
Every requirement has one named human owner. 'The business' is not a name
Gotchas
'Segment as of today' versus 'segment as of the order date'Both joins are correct SQL and they return different revenue. The divergence grows silently as customers move segment, and it surfaces months later as a trend chart that restates itself — which reads exactly like the business changing.
An acceptance test written after the buildIt is then written against what was built, passes by construction, and proves only that the code does what the code does.
An INNER JOIN to gold.dim_customer in an acceptance queryRule 7 lets a fact line point at the unknown member customer_sk = 'UNKNOWN', and no dimension version satisfies the SCD2 interval predicate for it — so the inner join drops those lines with no error, no warning and no row count to notice. The number agreed 'to the cent' with Finance is then short by exactly the unattributed revenue, and it surfaces at acceptance, in front of the requester, which is the one meeting this entire form exists to make safe.
Field five answered with 'revenue, the usual'Rule 1 (exclude CANCELLED) and rule 2 (the rounding in amount_eur) are precisely the 'usual' on which finance and sales differ by a few percent — enough for the report to be disbelieved once and then never trusted again.
Skipping field six because latency 'is a technical detail'A daily 07:00 load against a request that assumed intraday is discovered in UAT, by the requester, in front of their own stakeholders.