What is control pass rate, and how do you measure it in Metabase?

Control pass rate is the share of monitored compliance controls currently passing their automated tests, reported per framework — SOC 2, ISO 27001, HIPAA, whichever you're certifying against. It's the day-to-day readiness number between audits. Measure it in Metabase from control tests synced from Vanta or Drata.

What control pass rate measures

It measures how far you are from a clean audit, continuously instead of annually. Compliance platforms retest controls on a schedule, so the current state — latest test per control — is a live readiness figure, and the gap between frameworks tells you where the next certification effort goes. The companion view matters just as much: which controls have been failing longest, and who owns them. A pass rate without an owner column is a status report; with one, it's a work queue.

What data does it need?

• A control_tests table: control_id, status (pass/fail/not_applicable), tested_at.
• A control_framework_map table, since one control routinely maps to several frameworks.
• A controls table with name and owner, so failures land on someone's desk.
• Daily snapshots of per-control status if you want a trend — the tests table only proves the present.

SQL patterns

WITH latest AS (
  -- one row per control: its most recent test result
  SELECT DISTINCT ON (control_id)
    control_id,
    status,
    tested_at
  FROM control_tests
  ORDER BY control_id, tested_at DESC
)
SELECT
  m.framework,
  COUNT(*) AS monitored_controls,
  COUNT(*) FILTER (WHERE l.status = 'pass') AS passing,
  ROUND(
    100.0 * COUNT(*) FILTER (WHERE l.status = 'pass')
    / NULLIF(COUNT(*), 0), 1
  ) AS pass_rate_pct
FROM latest l
JOIN control_framework_map m ON m.control_id = l.control_id
WHERE l.status <> 'not_applicable'
GROUP BY m.framework
ORDER BY pass_rate_pct;

WITH latest AS (
  SELECT DISTINCT ON (control_id)
    control_id,
    status
  FROM control_tests
  ORDER BY control_id, tested_at DESC
),
last_pass AS (
  SELECT control_id, MAX(tested_at) AS last_passed_at
  FROM control_tests
  WHERE status = 'pass'
  GROUP BY control_id
)
SELECT
  c.name AS control,
  c.owner,
  lp.last_passed_at::date AS last_passed,
  CURRENT_DATE
    - COALESCE(lp.last_passed_at, c.monitoring_started_at)::date
    AS days_failing
FROM latest l
JOIN controls c ON c.id = l.control_id
LEFT JOIN last_pass lp ON lp.control_id = l.control_id
WHERE l.status = 'fail'
  AND COALESCE(lp.last_passed_at, c.monitoring_started_at)
      < CURRENT_DATE - INTERVAL '14 days'
ORDER BY days_failing DESC;

Pitfalls

Where this metric applies

• Vanta + Metabase — control test results and framework mappings
• Drata + Metabase — monitored controls with owners and test history

Related

Metrics

Dashboards

FAQ