Write a PRD That Makes APIs and Data Testable
A step‑by‑step guide to write a PRD that makes APIs testable and data verifiable with simple SQL.

This guide is for product managers who ship features that touch APIs and data, but avoid specs and tests. By the end, you will have a contract‑first PRD format, a set of steps to follow, and a worked example with API samples and three SQL checks you can run during QA.
- Treat your PRD as the contract for API shape, behavior, errors, and data lineage.
- Include one sample cURL, one JSON response, and three SQL checks.
- Write acceptance as data you can query, not only UI flows.
- Assign data owners and downstream consumers. Plan rollback and monitoring.
Why a contract‑first PRD
APIs are code. Data is code. Treat your PRD like a contract.
Vague API requests and missing data contracts create rework and hidden bugs. Metrics drift. People argue about why dashboards do not match. A contract‑first PRD sets the API shape, error cases, data lineage, and the exact checks that prove it works.
The quick technique
Add three things in one place:
- One sample cURL that calls the endpoint with real field names.
- One sample JSON response that shows success and an error case.
- Three SQL statements with comments that prove the feature works.
Engineers use them as unit and integration tests. QA runs the same SQL to verify. Analytics keeps the snippets as an audit trail. You can do this in your PRD.
Steps to write a testable PRD
Who will call the API. Which teams read the data. What is out of scope.
Method, path, headers, params, body, success, errors, idempotency, pagination, auth, rate limits.
Tables, columns, types, timezone, where the data comes from, where it lands, how it is derived.
One cURL and one JSON for success and for a common error. Include edge cases.
Row counts, cohort membership, retention windows, not just UI clicks.
Where to run the SQL, what gets alerted, what thresholds, who watches it.
Versioning, deprecation, backfills, safe migrations, rollbacks for schema and API.
Single owner for the API, the tables, and the dashboards. Name downstream users.
Contract‑first vs vague tickets
| Contract‑first PRD | Vague ticket |
|---|---|
| Explicit endpoint, schema, errors, idempotency, timezone. | “Add referral API.” Defaults are assumed. Timezone unstated. |
| Sample cURL and JSON added to spec. | No concrete examples. Engineers guess and diverge. |
| Data lineage defined from raw to marts. | Tables appear later. Analytics reverse‑engineers intent. |
| Acceptance criteria are SQL checks you can run. | Acceptance is only UI behavior. Hard to verify data. |
| Change process, versioning, rollback are clear. | Breaking change risk. Scramble during incidents. |
| Named owners and downstream consumers. | Unclear who to ask or alert when numbers drift. |
Common mistakes to avoid
Do not assume timezone, currency, idempotency, or pagination rules. State them. Use UTC for timestamps. Require an Idempotency‑Key for writes.
Say how schemas evolve, how you deprecate fields, and how you roll back. Include who approves and what gets notified.
Checks that a human can only “see” in UI are fragile. Write criteria as queries that return rows and counts.
Worked example: Referral attribution PRD section
This is a compact section you can paste into a PRD. It covers the API, the data lineage, and three SQL checks you can run in QA.
Create a Referral Attribution API to record that user B was referred by user A via a referral code. Downstream users: Growth, Data, Finance.
API owner: Growth Backend team. Data owner: Analytics Engineering. Dashboard owner: Growth Analytics.
Method: POST
Path: /v1/referrals/attributions
Headers:
- Authorization: Bearer <token>
- Content-Type: application/json
- Idempotency-Key: <uuid> # required for dedupe of retries
Timezone: All timestamps are ISO‑8601 in UTC.
Request body:
{
"referred_user_id": "u_12345", # the new user
"referral_code": "FRIEND50", # code used by the new user
"event_timestamp": "2026-03-01T12:34:56Z", # when attribution event happened (UTC)
"source": "ios_app", # optional: ios_app | android_app | web
"campaign_id": "camp_2026_q1" # optional campaign grouping
}
Success 201 response:
{
"attribution_id": "attr_7890",
"referred_user_id": "u_12345",
"referrer_user_id": "u_67890", # looked up from referral_code
"referral_code": "FRIEND50",
"attributed_at": "2026-03-01T12:34:56Z",
"status": "attributed"
}
Error examples:
400 Bad Request # missing required field
{
"error": "invalid_request",
"message": "referred_user_id is required"
}
409 Conflict # duplicate attribution for same referred_user_id with different code
{
"error": "conflict",
"message": "attribution already exists for referred_user_id"
}
422 Unprocessable Entity # self‑referral or expired code
{
"error": "invalid_referral",
"message": "referral_code expired or self‑referral"
}
Edge cases: self‑referrals, expired codes, duplicate retries (use Idempotency‑Key), and multiple events for the same referred user. Only one active attribution per referred_user_id.
- Raw ingest: raw.events_referral (one row per API call, includes idempotency key and request payload).
- Core model: core.referrals (validated, joined with users and codes).
- Mart: mart.referral_attributions (one row per referred_user_id, latest valid event wins).
- Timezone: store and query in UTC; convert for UI only.
Run these during QA against staging. Replace values with your test ids.
1) Raw events exist and are well‑formed
-- Expect: at least 1 raw event for the test referral_code within the QA window
-- Check required fields are not null and timestamp is in UTC (ends with 'Z')
SELECT
COUNT(*) AS raw_event_count,
MIN(event_timestamp) AS first_seen_utc,
MAX(event_timestamp) AS last_seen_utc,
SUM(CASE WHEN referred_user_id IS NULL THEN 1 ELSE 0 END) AS null_referred_users,
SUM(CASE WHEN referral_code IS NULL THEN 1 ELSE 0 END) AS null_codes,
SUM(CASE WHEN RIGHT(CAST(event_timestamp AS VARCHAR), 1) = 'Z' THEN 0 ELSE 1 END) AS non_utc_timestamps
FROM raw.events_referral
WHERE referral_code = 'FRIEND50'
AND event_timestamp BETWEEN TIMESTAMP '2026-03-01 00:00:00+00' AND TIMESTAMP '2026-03-07 23:59:59+00';
Pass when raw_event_count ≥ 1 and all null_… and non_utc_timestamps = 0.
2) Deduplicated attributions: one per referred user
-- Expect: no referred_user_id appears more than once in the mart
-- Also confirm each mart row ties back to at least one raw event
WITH dups AS (
SELECT referred_user_id, COUNT(*) AS c
FROM mart.referral_attributions
GROUP BY referred_user_id
HAVING COUNT(*) > 1
),
backlinks AS (
SELECT m.attribution_id
FROM mart.referral_attributions m
LEFT JOIN core.referrals c ON c.attribution_id = m.attribution_id
)
SELECT
(SELECT COUNT(*) FROM dups) AS duplicate_users,
(SELECT COUNT(*) FROM mart.referral_attributions) AS mart_rows,
(SELECT COUNT(*) FROM backlinks) AS with_core_link;
Pass when duplicate_users = 0 and with_core_link = mart_rows.
3) Seven‑day retention for referred cohort
-- Build a cohort of referred users by attribution date and count returns within 7 days
-- user_activity captures daily active events (one row per user per day)
WITH cohort AS (
SELECT referred_user_id, DATE(attributed_at) AS cohort_date
FROM mart.referral_attributions
WHERE referral_code = 'FRIEND50'
),
activity AS (
SELECT user_id, activity_date
FROM analytics.user_activity
),
joined AS (
SELECT c.cohort_date,
a.user_id,
a.activity_date,
DATE_DIFF('day', c.cohort_date, a.activity_date) AS d
FROM cohort c
JOIN activity a ON a.user_id = c.referred_user_id
WHERE a.activity_date BETWEEN c.cohort_date AND DATE_ADD('day', 7, c.cohort_date)
)
SELECT
cohort_date,
COUNT(DISTINCT user_id) AS cohort_size,
SUM(CASE WHEN d BETWEEN 1 AND 7 THEN 1 ELSE 0 END) AS returning_user_days,
COUNT(DISTINCT CASE WHEN d BETWEEN 1 AND 7 THEN user_id END) AS users_returned_7d
FROM joined
GROUP BY cohort_date
ORDER BY cohort_date;
Pass when cohort_size matches expectations for your test, and users_returned_7d reflects the activity you triggered in QA.
- Monitor 7‑day rolling counts of raw.events_referral and mart.referral_attributions. Alert when either drops by a large percent day‑over‑day.
- Version API with /v1, keep old fields for a deprecation window. Announce removals ahead of time.
- For schema adds: additive first. For breaking changes: backfill and dual‑write. Roll back by flag to stop writes to new path.
Build‑ready checklist
- API method, path, headers, body, errors, idempotency, and timezone are written.
- One cURL and one JSON response example are present and copy‑pastable.
- Tables, columns, and lineage from raw to mart are listed.
- Three SQL checks exist with comments and pass criteria.
- Data owners and downstream consumers are named.
- Monitoring thresholds and alert channels are defined.
- Change process, versioning, and rollback steps are clear.
- QA knows where to run the queries and how to seed test data.
What you get by doing this
Delivery moves faster. Engineers build the right endpoint the first time. QA can verify with simple queries. Analytics trusts the counts. Fewer disputes over metrics. You also keep an audit trail you can share with analytics and legal when needed.


