156 lines
3.6 KiB
SQL
156 lines
3.6 KiB
SQL
-- Every row reports a count of invariant violations. The Python driver refuses
|
|
-- to publish the mart unless every count is zero.
|
|
|
|
CREATE OR REPLACE TABLE mart_validation AS
|
|
SELECT
|
|
'clean_internal_event_id_unique' AS check_name,
|
|
count(*) AS violation_count
|
|
FROM (
|
|
SELECT internal_event_id
|
|
FROM clean_events
|
|
GROUP BY internal_event_id
|
|
HAVING count(*) > 1
|
|
)
|
|
|
|
UNION ALL
|
|
|
|
SELECT
|
|
'episode_key_unique',
|
|
count(*)
|
|
FROM (
|
|
SELECT vehicle_token, episode_number
|
|
FROM inspection_episodes
|
|
GROUP BY vehicle_token, episode_number
|
|
HAVING count(*) > 1
|
|
)
|
|
|
|
UNION ALL
|
|
|
|
SELECT
|
|
'mart_key_unique',
|
|
count(*)
|
|
FROM (
|
|
SELECT vehicle_token, episode_number
|
|
FROM feature_mart
|
|
GROUP BY vehicle_token, episode_number
|
|
HAVING count(*) > 1
|
|
)
|
|
|
|
UNION ALL
|
|
|
|
SELECT
|
|
'episode_attempts_reconcile',
|
|
count(*)
|
|
FROM (
|
|
SELECT
|
|
e.vehicle_token,
|
|
e.episode_number
|
|
FROM inspection_episodes AS e
|
|
JOIN (
|
|
SELECT vehicle_token, episode_number, count(*) AS actual_attempts
|
|
FROM sequenced_events
|
|
GROUP BY vehicle_token, episode_number
|
|
) AS a USING (vehicle_token, episode_number)
|
|
WHERE e.attempt_count <> a.actual_attempts
|
|
)
|
|
|
|
UNION ALL
|
|
|
|
SELECT
|
|
'episode_gap_strictly_greater_than_threshold',
|
|
count(*)
|
|
FROM (
|
|
SELECT
|
|
episode_start,
|
|
lag(episode_end) OVER (
|
|
PARTITION BY vehicle_token ORDER BY episode_number
|
|
) AS prior_episode_end
|
|
FROM inspection_episodes
|
|
) AS gaps
|
|
WHERE prior_episode_end IS NOT NULL
|
|
AND episode_start - prior_episode_end
|
|
<= (SELECT episode_gap_days FROM build_config) * INTERVAL '1 day'
|
|
|
|
UNION ALL
|
|
|
|
SELECT
|
|
'prior_episode_count_point_in_time',
|
|
count(*)
|
|
FROM feature_mart
|
|
WHERE prior_episode_count <> episode_number - 1
|
|
|
|
UNION ALL
|
|
|
|
SELECT
|
|
'target_mapping_consistent',
|
|
count(*)
|
|
FROM feature_mart
|
|
WHERE target_nonpass IS DISTINCT FROM CASE
|
|
WHEN first_outcome = 'pass' THEN 0
|
|
WHEN first_outcome IN ('fail', 'reject', 'abort') THEN 1
|
|
ELSE NULL
|
|
END
|
|
|
|
UNION ALL
|
|
|
|
SELECT
|
|
'target_outcome_label_source_consistent',
|
|
count(*)
|
|
FROM feature_mart
|
|
WHERE (first_outcome IS NOT NULL AND (
|
|
target_outcome_label_source IS NULL
|
|
OR target_outcome_label_source
|
|
NOT IN ('overall_result', 'utah_obd_proxy')
|
|
))
|
|
OR (target_outcome_label_source = 'utah_obd_proxy'
|
|
AND source_era IS DISTINCT FROM 'utah')
|
|
|
|
UNION ALL
|
|
|
|
SELECT
|
|
'raw_obd_result_absent_from_mart',
|
|
count(*)
|
|
FROM information_schema.columns
|
|
WHERE table_schema = 'main'
|
|
AND table_name = 'feature_mart'
|
|
AND lower(column_name) = 'obd_result'
|
|
|
|
UNION ALL
|
|
|
|
SELECT
|
|
'audit_bucket_consistent',
|
|
count(*)
|
|
FROM feature_mart
|
|
WHERE vehicle_bucket NOT BETWEEN 0 AND 99
|
|
OR is_vin_audit IS DISTINCT FROM (vehicle_bucket < 10)
|
|
|
|
UNION ALL
|
|
|
|
SELECT
|
|
'eligibility_consistent',
|
|
count(*)
|
|
FROM feature_mart
|
|
WHERE eligible_returning_target IS DISTINCT FROM (
|
|
episode_start >= TIMESTAMP '2016-01-01'
|
|
AND first_outcome IS NOT NULL
|
|
AND prior_episode_count >= 1
|
|
AND prior_total_attempt_count <= 50
|
|
AND coalesce(prior_max_events_in_day, 0) <= 4
|
|
)
|
|
|
|
UNION ALL
|
|
|
|
SELECT
|
|
'temporal_partition_consistent',
|
|
count(*)
|
|
FROM feature_mart
|
|
WHERE temporal_partition IS DISTINCT FROM CASE
|
|
WHEN episode_start < TIMESTAMP '2016-01-01' THEN 'historical_context'
|
|
WHEN episode_start < TIMESTAMP '2023-01-01' THEN 'train'
|
|
WHEN episode_start < TIMESTAMP '2024-01-01' THEN 'tune'
|
|
WHEN episode_start < TIMESTAMP '2025-01-01' THEN 'calibrate'
|
|
WHEN episode_start < TIMESTAMP '2026-01-01' THEN 'test'
|
|
WHEN episode_start < TIMESTAMP '2027-01-01' THEN 'shadow'
|
|
ELSE 'out_of_scope'
|
|
END;
|