How to Write SQL That Audits Itself

Not every number needs a guard. Writing assertions everywhere adds noise and slows iteration. Use a simple rule based on what happens when the number is wrong.

Two rules of thumb from experience, and they are rules of thumb rather than sourced facts: assert the invariants that must be mathematically true (retained never exceeds cohort size, parts sum to the whole), and range-check the ones that are only empirically true (retention usually falls over time). Invariants are safe to enforce always; ranges should warn first, because the business sometimes genuinely changes.

If you want a second pair of eyes on which invariants your report actually satisfies, a free 30-minute AI discovery call is a reasonable place to walk through the report and its failure history.

Suppose you have users(user_id, signup_at) and events(user_id, event_at), and the report must show, for each signup month, the number active in month 0, month 1, and so on.

Here is the core query, kept deliberately plain:

WITH cohorts AS (
  SELECT user_id,
         DATE_TRUNC('month', MIN(signup_at)) AS cohort_month
  FROM users
  GROUP BY user_id
),
activity AS (
  SELECT DISTINCT c.user_id,
         c.cohort_month,
         DATE_TRUNC('month', e.event_at) AS activity_month
  FROM cohorts c
  JOIN events e ON e.user_id = c.user_id
),
sized AS (
  SELECT cohort_month, COUNT(*) AS cohort_size
  FROM cohorts
  GROUP BY cohort_month
)
SELECT s.cohort_month,
       s.cohort_size,
       a.activity_month,
       COUNT(DISTINCT a.user_id) AS active_users,
       COUNT(DISTINCT a.user_id)::numeric / NULLIF(s.cohort_size, 0) AS retention_rate
FROM sized s
LEFT JOIN activity a ON a.cohort_month = s.cohort_month
GROUP BY s.cohort_month, s.cohort_size, a.activity_month
ORDER BY 1, 3;

The DISTINCT and the NULLIF handle two of the five failure modes. DISTINCT collapses duplicate event rows, and NULLIF(...,0) avoids the silent null division. Neither is an audit. They are just correct query construction. The audit is what you write next.

Assertion 1: retained users never exceed cohort size. This catches join fan-out and filter mismatches in one line:

SELECT cohort_month, cohort_size, active_users
FROM report
WHERE active_users > cohort_size;

If this returns any rows, the report is wrong and should not publish. Note that active_users in the query above is already distinct per user, so a failure here means the denominator is too small, not that the numerator is inflated.

Assertion 2: parts reconcile to the whole. If the pipeline separately computes a total active user count (for example, from a daily active table used elsewhere), assert that the sum of retained users across periods equals that total for each cohort within a tolerance or exactly, depending on how the two are defined. This is the check that catches a dropped period or a cohort silently missing from the join.

Assertion 3: every cohort row has a plausible size. A cohort size of zero or one in a month with real signups means the signup source or the truncation is broken:

SELECT cohort_month, cohort_size
FROM sized
WHERE cohort_size < 2;

Run these as separate queries. Do not try to fold them into the reporting query with CASE gymnastics; you want the failure to be visible as a result set, with the offending rows, so whoever is on call can see the cause.

The same reconciliation instinct applies to financial reports built on the same kind of join. Cohort-derived metrics like CAC payback depend on getting the cohort right before the arithmetic even starts, as The SaaS CFO's payback walkthrough and Stripe's formula explainer both assume a clean cohort definition. If your payback number is suspicious, check the cohort query before questioning the CAC inputs.

There are three practical ways to turn a check into a hard stop, and the right one depends on where the query runs.

Technique 1: the error-raising subquery. Portable across PostgreSQL, MySQL and SQL Server, using CASE and a deliberate cast or division that throws:

SELECT CASE
         WHEN (SELECT COUNT(*) FROM report WHERE active_users > cohort_size) > 0
         THEN 1/0
         ELSE 1
       END AS audit_passed;

PostgreSQL raises a division-by-zero error; MySQL returns null by default and needs ERROR 1 inside a CASE or a similar construct, so verify the behavior of your engine. The advantage is that the check runs in the same transaction as the report and stops the load.

Technique 2: the database-native assertion. Where the engine has one, use it. Guard your arithmetic at the same time with CASE or NULLIF so invalid values never silently coerce, a pattern consistent with the type and range checks described in this validation guide. Some warehouses support an ASSERT statement or a scripting block that raises a named error; check your engine's documentation rather than assuming.

Technique 3: the test query in CI. Store the audit SQL in the repo next to the report definition and run it as a step that fails the job if any rows come back. This is the most maintainable option for a dbt or Airflow pipeline, because the test is versioned and reviewed like code. It also lets you distinguish hard failures from warnings by using two files: audit_strict.sql and audit_warn.sql.

One more guard worth adding to any query that produces a protected value: the type and format checks that prevent bad input at the boundary are the same class of defense as parameterized queries, which this SQL injection prevention guide treats as the first line of protection. Auditing output and validating input are complements, not substitutes.

For teams wiring model outputs or generated SQL into these pipelines, Quanta Inference offers one OpenAI-compatible endpoint for text, image and embedding models under a single prepaid key at quantaintelligence.ai/inference, which keeps the checking and the generation in one billing and auth story.

Worked example with numbers, so you can see what a good and bad audit looks like.

Suppose the January cohort has 10,000 new users. In the report, month 1 shows 4,200 active, month 2 shows 3,100, month 3 shows 2,600. The expected total of retained users across those three periods, if each user is counted once per period, is 9,900 counted-periods. That is the number your reconciliation query should reproduce.

If the reconciliation returns 12,400 instead, the difference of 2,500 is your clue: roughly 25 percent extra counted-periods. A DISTINCT user_id per period would not fix it if the inflation is a duplicated dimension row (a user with two subscription records, both joined). A uniqueness check on the grain tells you immediately:

SELECT cohort_month, activity_month, user_id, COUNT(*) AS n
FROM activity
GROUP BY 1, 2, 3
HAVING COUNT(*) > 1
LIMIT 50;

If this returns 2,500 duplicate rows at month 1, you have found the cause. The fix is to dedupe the dimension before the join or to use a ROW_NUMBER() filter.

The same discipline applies to row count checks on the report output itself. One reference question on Stack Overflow covers @@ROWCOUNT and ROWCOUNT_BIG() in SQL Server, which are useful when you need the count of the statement just executed. A common trap: the count reflects the last statement, not your report query, if anything runs in between.

A practical checklist for a retention report:

• Cohort size computed from the user table alone, never through the events join.
• Activity deduplicated to one row per user per period before counting.
• Denominator guarded with NULLIF or a CASE that raises an error on zero.
• An assertion that no active count exceeds its cohort size.
• A reconciliation of period sums against an independently computed total.
• A uniqueness check on (cohort_month, period_number, user_id) upstream of the aggregate.
• An audit step that fails the pipeline if the assertion query returns rows.

Yes or no on each of those seven is a far better review comment than "does this look right."

An assertion nobody runs is documentation with extra steps. Put the audit where the report already runs.

In a scheduled pipeline, add an audit task immediately after the report materializes. The task runs the strict checks and raises; the downstream dashboard refresh depends on the audit task succeeding. This is the same shape as the three-stage validation process (sample, full dataset, source verification) that Teradata describes, compressed into one dependency edge. Database-native audit features such as SQL Server Audit can capture who ran what and whether it succeeded, which pairs well with the query-level checks: audit records tell you a load ran, the assertions tell you the numbers were right.

In an ad hoc setting, keep a single audits.sql file with one query per check, each returning zero rows on success. Run the whole file after every change to the report. It takes seconds and pays for itself the first time a join changes.

Two maintenance habits keep audits from rotting:

• Tie every assertion to a seen failure. Add a comment with the date (or a ticket reference) and what broke. Assertions justified by a real incident survive; speculative ones get deleted.
• Separate hard from soft. Put mathematical invariants in the strict file and business ranges in the warn file, and only page a human on the strict file.

When the pipeline itself is the thing you need to build or rebuild, and the SQL checks need a home with proper scheduling and alerting, Quanta takes a described pipeline or report and pairs you with a specialist who builds it alongside you.