Analytics Reliability & Release Control

Decision: should the next analytics release ship, be held, or ship with an explicitly accepted caveat?

Deterministic synthetic portfolio data · snapshot 30 Jun 2026 · raw anomalies are disclosed in the scenario manifest.

Release decision

SHIP WITH CAVEAT

Hard gate derived from failures, signoff, rollback and accepted exceptions.

Hard gate derived from failures, signoff, rollback and accepted exceptions.Source: Release decisionTables: fact_release, fact_quality_result, fact_pipeline_run, fact_incident, fact_change_request

Deterministic query executed by scripts/build_analytics_projects.py.

WITH latest_release AS (
  SELECT *, CASE
    WHEN critical_failures > 0 OR signoff_complete = 0 OR rollback_ready = 0 THEN 'HOLD'
    WHEN high_warnings BETWEEN 1 AND 2
         AND exceptions_accepted = 1
         AND exception_owner <> ''
         AND exception_expiry > scheduled_date THEN 'SHIP WITH CAVEAT'
    WHEN high_warnings = 0 THEN 'SHIP'
    ELSE 'HOLD' END AS release_decision
  FROM fact_release ORDER BY scheduled_date DESC LIMIT 1
), recent_quality AS (
  SELECT * FROM fact_quality_result WHERE run_date >= '2026-06-01'
), critical AS (
  SELECT SUM(CASE WHEN status = 'PASS' THEN 1 ELSE 0 END) * 1.0 / COUNT(*) AS critical_pass_rate
  FROM recent_quality WHERE severity = 'Critical'
), freshness AS (
  SELECT SUM(CASE WHEN freshness_minutes <= 240 AND pipeline_status = 'SUCCEEDED' THEN 1 ELSE 0 END) * 1.0 / COUNT(*) AS freshness_sla_rate
  FROM fact_pipeline_run WHERE run_date >= '2026-06-01'
), latest_recon AS (
  SELECT MAX(ABS(dashboard_value - governed_value) / ABS(governed_value))
         AS latest_reconciliation_variance
  FROM fact_pipeline_run WHERE run_date = '2026-06-30'
), open_incidents AS (
  SELECT COUNT(*) AS open_high_critical_incidents FROM fact_incident
  WHERE resolved_at = '' AND severity IN ('High', 'Critical')
), lead_time AS (
  SELECT AVG(julianday(release_date) - julianday(ready_date)) AS average_change_lead_days
  FROM fact_change_request WHERE release_date <= '2026-06-30'
)
SELECT release_decision, ROUND(critical_pass_rate, 6) AS critical_pass_rate,
       ROUND(freshness_sla_rate, 6) AS freshness_sla_rate,
       ROUND(latest_reconciliation_variance, 6) AS latest_reconciliation_variance,
       open_high_critical_incidents, ROUND(average_change_lead_days, 2) AS average_change_lead_days
FROM latest_release CROSS JOIN critical CROSS JOIN freshness CROSS JOIN latest_recon
CROSS JOIN open_incidents CROSS JOIN lead_time;

Critical pass rate

98.9%

Passed critical controls divided by executed critical controls in June.

Passed critical controls divided by executed critical controls in June.Source: Release decisionTables: fact_release, fact_quality_result, fact_pipeline_run, fact_incident, fact_change_request

Deterministic query executed by scripts/build_analytics_projects.py.

WITH latest_release AS (
  SELECT *, CASE
    WHEN critical_failures > 0 OR signoff_complete = 0 OR rollback_ready = 0 THEN 'HOLD'
    WHEN high_warnings BETWEEN 1 AND 2
         AND exceptions_accepted = 1
         AND exception_owner <> ''
         AND exception_expiry > scheduled_date THEN 'SHIP WITH CAVEAT'
    WHEN high_warnings = 0 THEN 'SHIP'
    ELSE 'HOLD' END AS release_decision
  FROM fact_release ORDER BY scheduled_date DESC LIMIT 1
), recent_quality AS (
  SELECT * FROM fact_quality_result WHERE run_date >= '2026-06-01'
), critical AS (
  SELECT SUM(CASE WHEN status = 'PASS' THEN 1 ELSE 0 END) * 1.0 / COUNT(*) AS critical_pass_rate
  FROM recent_quality WHERE severity = 'Critical'
), freshness AS (
  SELECT SUM(CASE WHEN freshness_minutes <= 240 AND pipeline_status = 'SUCCEEDED' THEN 1 ELSE 0 END) * 1.0 / COUNT(*) AS freshness_sla_rate
  FROM fact_pipeline_run WHERE run_date >= '2026-06-01'
), latest_recon AS (
  SELECT MAX(ABS(dashboard_value - governed_value) / ABS(governed_value))
         AS latest_reconciliation_variance
  FROM fact_pipeline_run WHERE run_date = '2026-06-30'
), open_incidents AS (
  SELECT COUNT(*) AS open_high_critical_incidents FROM fact_incident
  WHERE resolved_at = '' AND severity IN ('High', 'Critical')
), lead_time AS (
  SELECT AVG(julianday(release_date) - julianday(ready_date)) AS average_change_lead_days
  FROM fact_change_request WHERE release_date <= '2026-06-30'
)
SELECT release_decision, ROUND(critical_pass_rate, 6) AS critical_pass_rate,
       ROUND(freshness_sla_rate, 6) AS freshness_sla_rate,
       ROUND(latest_reconciliation_variance, 6) AS latest_reconciliation_variance,
       open_high_critical_incidents, ROUND(average_change_lead_days, 2) AS average_change_lead_days
FROM latest_release CROSS JOIN critical CROSS JOIN freshness CROSS JOIN latest_recon
CROSS JOIN open_incidents CROSS JOIN lead_time;

Freshness SLA

99.2%

Scheduled June runs completed successfully within four hours.

Scheduled June runs completed successfully within four hours.Source: Release decisionTables: fact_release, fact_quality_result, fact_pipeline_run, fact_incident, fact_change_request

Deterministic query executed by scripts/build_analytics_projects.py.

WITH latest_release AS (
  SELECT *, CASE
    WHEN critical_failures > 0 OR signoff_complete = 0 OR rollback_ready = 0 THEN 'HOLD'
    WHEN high_warnings BETWEEN 1 AND 2
         AND exceptions_accepted = 1
         AND exception_owner <> ''
         AND exception_expiry > scheduled_date THEN 'SHIP WITH CAVEAT'
    WHEN high_warnings = 0 THEN 'SHIP'
    ELSE 'HOLD' END AS release_decision
  FROM fact_release ORDER BY scheduled_date DESC LIMIT 1
), recent_quality AS (
  SELECT * FROM fact_quality_result WHERE run_date >= '2026-06-01'
), critical AS (
  SELECT SUM(CASE WHEN status = 'PASS' THEN 1 ELSE 0 END) * 1.0 / COUNT(*) AS critical_pass_rate
  FROM recent_quality WHERE severity = 'Critical'
), freshness AS (
  SELECT SUM(CASE WHEN freshness_minutes <= 240 AND pipeline_status = 'SUCCEEDED' THEN 1 ELSE 0 END) * 1.0 / COUNT(*) AS freshness_sla_rate
  FROM fact_pipeline_run WHERE run_date >= '2026-06-01'
), latest_recon AS (
  SELECT MAX(ABS(dashboard_value - governed_value) / ABS(governed_value))
         AS latest_reconciliation_variance
  FROM fact_pipeline_run WHERE run_date = '2026-06-30'
), open_incidents AS (
  SELECT COUNT(*) AS open_high_critical_incidents FROM fact_incident
  WHERE resolved_at = '' AND severity IN ('High', 'Critical')
), lead_time AS (
  SELECT AVG(julianday(release_date) - julianday(ready_date)) AS average_change_lead_days
  FROM fact_change_request WHERE release_date <= '2026-06-30'
)
SELECT release_decision, ROUND(critical_pass_rate, 6) AS critical_pass_rate,
       ROUND(freshness_sla_rate, 6) AS freshness_sla_rate,
       ROUND(latest_reconciliation_variance, 6) AS latest_reconciliation_variance,
       open_high_critical_incidents, ROUND(average_change_lead_days, 2) AS average_change_lead_days
FROM latest_release CROSS JOIN critical CROSS JOIN freshness CROSS JOIN latest_recon
CROSS JOIN open_incidents CROSS JOIN lead_time;

Latest max variance

0.2%

Highest raw-to-dashboard variance on the snapshot date.

Highest raw-to-dashboard variance on the snapshot date.Source: Release decisionTables: fact_release, fact_quality_result, fact_pipeline_run, fact_incident, fact_change_request

Deterministic query executed by scripts/build_analytics_projects.py.

WITH latest_release AS (
  SELECT *, CASE
    WHEN critical_failures > 0 OR signoff_complete = 0 OR rollback_ready = 0 THEN 'HOLD'
    WHEN high_warnings BETWEEN 1 AND 2
         AND exceptions_accepted = 1
         AND exception_owner <> ''
         AND exception_expiry > scheduled_date THEN 'SHIP WITH CAVEAT'
    WHEN high_warnings = 0 THEN 'SHIP'
    ELSE 'HOLD' END AS release_decision
  FROM fact_release ORDER BY scheduled_date DESC LIMIT 1
), recent_quality AS (
  SELECT * FROM fact_quality_result WHERE run_date >= '2026-06-01'
), critical AS (
  SELECT SUM(CASE WHEN status = 'PASS' THEN 1 ELSE 0 END) * 1.0 / COUNT(*) AS critical_pass_rate
  FROM recent_quality WHERE severity = 'Critical'
), freshness AS (
  SELECT SUM(CASE WHEN freshness_minutes <= 240 AND pipeline_status = 'SUCCEEDED' THEN 1 ELSE 0 END) * 1.0 / COUNT(*) AS freshness_sla_rate
  FROM fact_pipeline_run WHERE run_date >= '2026-06-01'
), latest_recon AS (
  SELECT MAX(ABS(dashboard_value - governed_value) / ABS(governed_value))
         AS latest_reconciliation_variance
  FROM fact_pipeline_run WHERE run_date = '2026-06-30'
), open_incidents AS (
  SELECT COUNT(*) AS open_high_critical_incidents FROM fact_incident
  WHERE resolved_at = '' AND severity IN ('High', 'Critical')
), lead_time AS (
  SELECT AVG(julianday(release_date) - julianday(ready_date)) AS average_change_lead_days
  FROM fact_change_request WHERE release_date <= '2026-06-30'
)
SELECT release_decision, ROUND(critical_pass_rate, 6) AS critical_pass_rate,
       ROUND(freshness_sla_rate, 6) AS freshness_sla_rate,
       ROUND(latest_reconciliation_variance, 6) AS latest_reconciliation_variance,
       open_high_critical_incidents, ROUND(average_change_lead_days, 2) AS average_change_lead_days
FROM latest_release CROSS JOIN critical CROSS JOIN freshness CROSS JOIN latest_recon
CROSS JOIN open_incidents CROSS JOIN lead_time;

Open high/critical

Unresolved incidents whose severity is High or Critical.

Unresolved incidents whose severity is High or Critical.Source: Release decisionTables: fact_release, fact_quality_result, fact_pipeline_run, fact_incident, fact_change_request

Deterministic query executed by scripts/build_analytics_projects.py.

WITH latest_release AS (
  SELECT *, CASE
    WHEN critical_failures > 0 OR signoff_complete = 0 OR rollback_ready = 0 THEN 'HOLD'
    WHEN high_warnings BETWEEN 1 AND 2
         AND exceptions_accepted = 1
         AND exception_owner <> ''
         AND exception_expiry > scheduled_date THEN 'SHIP WITH CAVEAT'
    WHEN high_warnings = 0 THEN 'SHIP'
    ELSE 'HOLD' END AS release_decision
  FROM fact_release ORDER BY scheduled_date DESC LIMIT 1
), recent_quality AS (
  SELECT * FROM fact_quality_result WHERE run_date >= '2026-06-01'
), critical AS (
  SELECT SUM(CASE WHEN status = 'PASS' THEN 1 ELSE 0 END) * 1.0 / COUNT(*) AS critical_pass_rate
  FROM recent_quality WHERE severity = 'Critical'
), freshness AS (
  SELECT SUM(CASE WHEN freshness_minutes <= 240 AND pipeline_status = 'SUCCEEDED' THEN 1 ELSE 0 END) * 1.0 / COUNT(*) AS freshness_sla_rate
  FROM fact_pipeline_run WHERE run_date >= '2026-06-01'
), latest_recon AS (
  SELECT MAX(ABS(dashboard_value - governed_value) / ABS(governed_value))
         AS latest_reconciliation_variance
  FROM fact_pipeline_run WHERE run_date = '2026-06-30'
), open_incidents AS (
  SELECT COUNT(*) AS open_high_critical_incidents FROM fact_incident
  WHERE resolved_at = '' AND severity IN ('High', 'Critical')
), lead_time AS (
  SELECT AVG(julianday(release_date) - julianday(ready_date)) AS average_change_lead_days
  FROM fact_change_request WHERE release_date <= '2026-06-30'
)
SELECT release_decision, ROUND(critical_pass_rate, 6) AS critical_pass_rate,
       ROUND(freshness_sla_rate, 6) AS freshness_sla_rate,
       ROUND(latest_reconciliation_variance, 6) AS latest_reconciliation_variance,
       open_high_critical_incidents, ROUND(average_change_lead_days, 2) AS average_change_lead_days
FROM latest_release CROSS JOIN critical CROSS JOIN freshness CROSS JOIN latest_recon
CROSS JOIN open_incidents CROSS JOIN lead_time;

Avg change lead days

4.33

Ready-to-release days for completed changes.

Ready-to-release days for completed changes.Source: Release decisionTables: fact_release, fact_quality_result, fact_pipeline_run, fact_incident, fact_change_request

Deterministic query executed by scripts/build_analytics_projects.py.

WITH latest_release AS (
  SELECT *, CASE
    WHEN critical_failures > 0 OR signoff_complete = 0 OR rollback_ready = 0 THEN 'HOLD'
    WHEN high_warnings BETWEEN 1 AND 2
         AND exceptions_accepted = 1
         AND exception_owner <> ''
         AND exception_expiry > scheduled_date THEN 'SHIP WITH CAVEAT'
    WHEN high_warnings = 0 THEN 'SHIP'
    ELSE 'HOLD' END AS release_decision
  FROM fact_release ORDER BY scheduled_date DESC LIMIT 1
), recent_quality AS (
  SELECT * FROM fact_quality_result WHERE run_date >= '2026-06-01'
), critical AS (
  SELECT SUM(CASE WHEN status = 'PASS' THEN 1 ELSE 0 END) * 1.0 / COUNT(*) AS critical_pass_rate
  FROM recent_quality WHERE severity = 'Critical'
), freshness AS (
  SELECT SUM(CASE WHEN freshness_minutes <= 240 AND pipeline_status = 'SUCCEEDED' THEN 1 ELSE 0 END) * 1.0 / COUNT(*) AS freshness_sla_rate
  FROM fact_pipeline_run WHERE run_date >= '2026-06-01'
), latest_recon AS (
  SELECT MAX(ABS(dashboard_value - governed_value) / ABS(governed_value))
         AS latest_reconciliation_variance
  FROM fact_pipeline_run WHERE run_date = '2026-06-30'
), open_incidents AS (
  SELECT COUNT(*) AS open_high_critical_incidents FROM fact_incident
  WHERE resolved_at = '' AND severity IN ('High', 'Critical')
), lead_time AS (
  SELECT AVG(julianday(release_date) - julianday(ready_date)) AS average_change_lead_days
  FROM fact_change_request WHERE release_date <= '2026-06-30'
)
SELECT release_decision, ROUND(critical_pass_rate, 6) AS critical_pass_rate,
       ROUND(freshness_sla_rate, 6) AS freshness_sla_rate,
       ROUND(latest_reconciliation_variance, 6) AS latest_reconciliation_variance,
       open_high_critical_incidents, ROUND(average_change_lead_days, 2) AS average_change_lead_days
FROM latest_release CROSS JOIN critical CROSS JOIN freshness CROSS JOIN latest_recon
CROSS JOIN open_incidents CROSS JOIN lead_time;

Quality test pass rate by week

Source: Weekly quality trendTable: fact_quality_result

Deterministic query executed by scripts/build_analytics_projects.py.

SELECT strftime('%Y-W%W', run_date) AS week,
       ROUND(SUM(CASE WHEN status = 'PASS' THEN 1 ELSE 0 END) * 1.0 / COUNT(*), 6) AS pass_rate,
       SUM(CASE WHEN status = 'FAIL' THEN 1 ELSE 0 END) AS failures,
       SUM(CASE WHEN status = 'WARN' THEN 1 ELSE 0 END) AS warnings
FROM fact_quality_result GROUP BY week ORDER BY week;

Quality test pass rate by week data
Week	Pass Rate
2026-W08	100%
2026-W09	100%
2026-W10	100%
2026-W11	100%
2026-W12	100%
2026-W13	100%
2026-W14	100%
2026-W15	97.6%
2026-W16	100%
2026-W17	100%
2026-W18	96.4%
2026-W19	100%
2026-W20	100%
2026-W21	100%
2026-W22	98.2%
2026-W23	100%
2026-W24	97.6%
2026-W25	99.4%
2026-W26	95.8%

Quality pass rate by asset

Source: Asset qualityTable: fact_quality_result

Deterministic query executed by scripts/build_analytics_projects.py.

SELECT asset_id,
       ROUND(SUM(CASE WHEN status = 'PASS' THEN 1 ELSE 0 END) * 1.0 / COUNT(*), 6) AS pass_rate,
       SUM(CASE WHEN status = 'FAIL' THEN 1 ELSE 0 END) AS failures,
       SUM(CASE WHEN status = 'WARN' THEN 1 ELSE 0 END) AS warnings
FROM fact_quality_result GROUP BY asset_id ORDER BY pass_rate ASC;

Quality pass rate by asset data
Asset Id	Pass Rate
CAMPAIGN_SPEND	98.8%
COMMERCE_ORDERS	99.5%
WEB_EVENTS	99.5%
CRM_LEADS	99.6%

Latest reconciliation variance by asset

Source: Latest reconciliationTable: fact_pipeline_run

Deterministic query executed by scripts/build_analytics_projects.py.

SELECT asset_id,
       ROUND(ABS(dashboard_value - governed_value) / ABS(governed_value), 6)
       AS reconciliation_variance,
       ROUND(ABS(dashboard_value - governed_value) / ABS(governed_value) * 10000, 2)
       AS reconciliation_variance_bps
FROM fact_pipeline_run WHERE run_date = '2026-06-30'
ORDER BY reconciliation_variance DESC;

Latest reconciliation variance by asset data
Asset Id	Reconciliation Variance Bps
CRM_LEADS	21.5
COMMERCE_ORDERS	10.72
CAMPAIGN_SPEND	8.45
WEB_EVENTS	8.31

Incidents by root-cause category

Source: Incident root causesTable: fact_incident

Deterministic query executed by scripts/build_analytics_projects.py.

SELECT root_cause, COUNT(*) AS incidents,
       SUM(CASE WHEN severity = 'Critical' THEN 1 ELSE 0 END) AS critical_incidents
FROM fact_incident GROUP BY root_cause ORDER BY incidents DESC, root_cause;

Incidents by root-cause category
Root cause	Incidents	Critical
Channel definition governance	1	0
Duplicate tracking	1	1
Partial partition	1	1
Schema drift	1	0
Taxonomy drift	1	0

Release gate history

Source: Release gatesTable: fact_release

Deterministic query executed by scripts/build_analytics_projects.py.

SELECT release_id, scheduled_date, evidence_date, critical_failures, high_warnings,
       exception_owner, exception_expiry,
       CASE
         WHEN critical_failures > 0 OR signoff_complete = 0 OR rollback_ready = 0 THEN 'HOLD'
         WHEN high_warnings BETWEEN 1 AND 2
              AND exceptions_accepted = 1
              AND exception_owner <> ''
              AND exception_expiry > scheduled_date THEN 'SHIP WITH CAVEAT'
         WHEN high_warnings = 0 THEN 'SHIP'
         ELSE 'HOLD' END AS decision
FROM fact_release ORDER BY scheduled_date DESC;

Release gate history
Release	Scheduled	Evidence date	Critical failures	High warnings	Exception owner	Exception expiry	Decision
REL-007	2026-07-01	2026-06-30	0	1	Marketing Operations	2026-07-15	SHIP WITH CAVEAT
REL-006	2026-06-19	2026-06-19	0	0	—	—	SHIP
REL-005	2026-06-05	2026-06-05	0	0	—	—	SHIP
REL-004	2026-05-12	2026-05-12	0	0	—	—	SHIP
REL-003	2026-05-08	2026-05-08	1	1	—	—	HOLD
REL-002	2026-04-17	2026-04-17	0	0	—	—	SHIP
REL-001	2026-04-14	2026-04-14	2	0	—	—	HOLD

Failure and warning evidence

Source: Failure evidenceTable: fact_quality_result

Deterministic query executed by scripts/build_analytics_projects.py.

SELECT run_date, asset_id, rule_name, severity, status, observed_value, threshold, affected_rows, owner
FROM fact_quality_result WHERE status <> 'PASS'
ORDER BY run_date DESC, CASE severity WHEN 'Critical' THEN 1 ELSE 2 END LIMIT 30;

Failure and warning evidence
Date	Asset	Rule	Severity	Status	Affected	Owner
2026-06-30	CAMPAIGN_SPEND	Unknown taxonomy rate	High	WARN	2	Marketing Operations
2026-06-29	CAMPAIGN_SPEND	Unknown taxonomy rate	High	WARN	2	Marketing Operations
2026-06-28	CAMPAIGN_SPEND	Unknown taxonomy rate	High	WARN	2	Marketing Operations
2026-06-18	COMMERCE_ORDERS	Raw-to-dashboard variance	Critical	FAIL	1	Analytics Engineering
2026-06-18	COMMERCE_ORDERS	Required type validity	High	WARN	4	Analytics Engineering
2026-06-17	COMMERCE_ORDERS	Raw-to-dashboard variance	Critical	FAIL	1	Analytics Engineering
2026-06-17	COMMERCE_ORDERS	Required type validity	High	WARN	4	Analytics Engineering
2026-06-03	CRM_LEADS	Partition row coverage	Critical	FAIL	42	Revenue Analytics
2026-06-03	CRM_LEADS	Raw-to-dashboard variance	Critical	FAIL	1	Revenue Analytics
2026-06-03	CRM_LEADS	Freshness lag	High	WARN	1	Revenue Analytics
2026-05-10	CAMPAIGN_SPEND	Raw-to-dashboard variance	Critical	FAIL	1	Marketing Operations
2026-05-10	CAMPAIGN_SPEND	Unknown taxonomy rate	High	WARN	13	Marketing Operations
2026-05-09	CAMPAIGN_SPEND	Raw-to-dashboard variance	Critical	FAIL	1	Marketing Operations
2026-05-09	CAMPAIGN_SPEND	Unknown taxonomy rate	High	WARN	13	Marketing Operations
2026-05-08	CAMPAIGN_SPEND	Raw-to-dashboard variance	Critical	FAIL	1	Marketing Operations

20 results · Showing first 15

Gate, evidence and limitations

The dashboard deliberately avoids an opaque trust score. A critical failure blocks release. A high warning can ship only with an accepted, owned, expiring caveat. All issues and resolutions are fictional and exist only to demonstrate quality and release-governance methods.

Sources

Release decisionsql/analytics.sql · SQLite 3 · 2026-06-30T18:00:00Z

Deterministic query executed by scripts/build_analytics_projects.py.

SQL query

WITH latest_release AS (
  SELECT *, CASE
    WHEN critical_failures > 0 OR signoff_complete = 0 OR rollback_ready = 0 THEN 'HOLD'
    WHEN high_warnings BETWEEN 1 AND 2
         AND exceptions_accepted = 1
         AND exception_owner <> ''
         AND exception_expiry > scheduled_date THEN 'SHIP WITH CAVEAT'
    WHEN high_warnings = 0 THEN 'SHIP'
    ELSE 'HOLD' END AS release_decision
  FROM fact_release ORDER BY scheduled_date DESC LIMIT 1
), recent_quality AS (
  SELECT * FROM fact_quality_result WHERE run_date >= '2026-06-01'
), critical AS (
  SELECT SUM(CASE WHEN status = 'PASS' THEN 1 ELSE 0 END) * 1.0 / COUNT(*) AS critical_pass_rate
  FROM recent_quality WHERE severity = 'Critical'
), freshness AS (
  SELECT SUM(CASE WHEN freshness_minutes <= 240 AND pipeline_status = 'SUCCEEDED' THEN 1 ELSE 0 END) * 1.0 / COUNT(*) AS freshness_sla_rate
  FROM fact_pipeline_run WHERE run_date >= '2026-06-01'
), latest_recon AS (
  SELECT MAX(ABS(dashboard_value - governed_value) / ABS(governed_value))
         AS latest_reconciliation_variance
  FROM fact_pipeline_run WHERE run_date = '2026-06-30'
), open_incidents AS (
  SELECT COUNT(*) AS open_high_critical_incidents FROM fact_incident
  WHERE resolved_at = '' AND severity IN ('High', 'Critical')
), lead_time AS (
  SELECT AVG(julianday(release_date) - julianday(ready_date)) AS average_change_lead_days
  FROM fact_change_request WHERE release_date <= '2026-06-30'
)
SELECT release_decision, ROUND(critical_pass_rate, 6) AS critical_pass_rate,
       ROUND(freshness_sla_rate, 6) AS freshness_sla_rate,
       ROUND(latest_reconciliation_variance, 6) AS latest_reconciliation_variance,
       open_high_critical_incidents, ROUND(average_change_lead_days, 2) AS average_change_lead_days
FROM latest_release CROSS JOIN critical CROSS JOIN freshness CROSS JOIN latest_recon
CROSS JOIN open_incidents CROSS JOIN lead_time;

Weekly quality trendsql/analytics.sql · SQLite 3 · 2026-06-30T18:00:00Z

Deterministic query executed by scripts/build_analytics_projects.py.

SQL query

SELECT strftime('%Y-W%W', run_date) AS week,
       ROUND(SUM(CASE WHEN status = 'PASS' THEN 1 ELSE 0 END) * 1.0 / COUNT(*), 6) AS pass_rate,
       SUM(CASE WHEN status = 'FAIL' THEN 1 ELSE 0 END) AS failures,
       SUM(CASE WHEN status = 'WARN' THEN 1 ELSE 0 END) AS warnings
FROM fact_quality_result GROUP BY week ORDER BY week;

Asset qualitysql/analytics.sql · SQLite 3 · 2026-06-30T18:00:00Z

Deterministic query executed by scripts/build_analytics_projects.py.

SQL query

SELECT asset_id,
       ROUND(SUM(CASE WHEN status = 'PASS' THEN 1 ELSE 0 END) * 1.0 / COUNT(*), 6) AS pass_rate,
       SUM(CASE WHEN status = 'FAIL' THEN 1 ELSE 0 END) AS failures,
       SUM(CASE WHEN status = 'WARN' THEN 1 ELSE 0 END) AS warnings
FROM fact_quality_result GROUP BY asset_id ORDER BY pass_rate ASC;

Latest reconciliationsql/analytics.sql · SQLite 3 · 2026-06-30T18:00:00Z

Deterministic query executed by scripts/build_analytics_projects.py.

SQL query

SELECT asset_id,
       ROUND(ABS(dashboard_value - governed_value) / ABS(governed_value), 6)
       AS reconciliation_variance,
       ROUND(ABS(dashboard_value - governed_value) / ABS(governed_value) * 10000, 2)
       AS reconciliation_variance_bps
FROM fact_pipeline_run WHERE run_date = '2026-06-30'
ORDER BY reconciliation_variance DESC;

Failure evidencesql/analytics.sql · SQLite 3 · 2026-06-30T18:00:00Z

Deterministic query executed by scripts/build_analytics_projects.py.

SQL query

SELECT run_date, asset_id, rule_name, severity, status, observed_value, threshold, affected_rows, owner
FROM fact_quality_result WHERE status <> 'PASS'
ORDER BY run_date DESC, CASE severity WHEN 'Critical' THEN 1 ELSE 2 END LIMIT 30;

Release gatessql/analytics.sql · SQLite 3 · 2026-06-30T18:00:00Z

Deterministic query executed by scripts/build_analytics_projects.py.

SQL query

SELECT release_id, scheduled_date, evidence_date, critical_failures, high_warnings,
       exception_owner, exception_expiry,
       CASE
         WHEN critical_failures > 0 OR signoff_complete = 0 OR rollback_ready = 0 THEN 'HOLD'
         WHEN high_warnings BETWEEN 1 AND 2
              AND exceptions_accepted = 1
              AND exception_owner <> ''
              AND exception_expiry > scheduled_date THEN 'SHIP WITH CAVEAT'
         WHEN high_warnings = 0 THEN 'SHIP'
         ELSE 'HOLD' END AS decision
FROM fact_release ORDER BY scheduled_date DESC;

Incident root causessql/analytics.sql · SQLite 3 · 2026-06-30T18:00:00Z

Deterministic query executed by scripts/build_analytics_projects.py.

SQL query

SELECT root_cause, COUNT(*) AS incidents,
       SUM(CASE WHEN severity = 'Critical' THEN 1 ELSE 0 END) AS critical_incidents
FROM fact_incident GROUP BY root_cause ORDER BY incidents DESC, root_cause;