Rejection & validation tracking
Two models track "what went wrong," and they answer different questions — one tells you why a record is bad, the other tells you which records disappeared without a matching outcome.
Two models, two different grains
stock_validation_results — grain: one row per raw_stock_id that failed at least one check. It independently re-implements the Redbook key resolution hierarchy against raw data and flags each record against 6 named failure reasons:
failed_invalid_redbook_keyfailed_invalid_nvic_redbook_keyfailed_missing_data_provider_dealer_idfailed_missing_data_provider_stock_idfailed_missing_nvic_and_redbookfailed_nvic_lookup_no_match
Only rows where at least one of these is true make it into the output. This is the reason — a specific, named cause per failed record.
stock_rejected_per_batch — grain: one row per (raw_stock_id, batch_id). It finds STOCK_RAW rows that reached processing_status = 'complete' but have no matching row in stock_int for that batch — i.e., something that got as far as being marked complete but never actually made it into the enriched table. This is the outcome gap, not a reason — it catches anything that fell out silently, including stock_int's own final gate: rows with no resolved Redbook Vehicle Key are filtered out of stock_int's output entirely, and this is one of the ways that drop becomes visible.
A model file header for stock_validation_results still calls itself "monitoring_stock_rejected" in a comment — that's a stale comment, not a renamed table; the model and its output table are genuinely named stock_validation_results. monitoring_stock_rejected_view is a separate, thin pass-through view over it, for anyone querying by the more discoverable "rejected" name.
The 5 monitoring views, used hierarchically
The project's own recommended order, each one drilling in further:
monitoring_stock_ingestion_summary_view— batch/file-level health: total records, failure counts, dealer-skip percentage. Start here.monitoring_stock_queued_status_view— per-dealer validation trends and health status (HEALTHY/WARNING/CRITICAL).monitoring_stock_resolution_metrics_view— aggregated Redbook key resolution performance, by provider and resolution method.monitoring_stock_redbook_vehicle_key_resolution_tracking_view— detailed, record-level resolution tracking.monitoring_stock_rejected_view— specific record-level failures, including dealer-status rejections.
-- Which specific records are failing, and why
SELECT rejection_reason, rejection_details, data_provider_dealer_id,
COUNT(*) AS failed_records, COUNT(DISTINCT file_name) AS failed_files
FROM VEHICLE_STOCK_DB.VEHICLE_STOCK_SCHEMA.monitoring_stock_rejected_view
WHERE rejection_reason = 'DEALER_STATUS_INVALID'
AND created_at >= CURRENT_DATE - INTERVAL '7 DAYS'
GROUP BY rejection_reason, rejection_details, data_provider_dealer_id
ORDER BY failed_records DESC;There's also monitoring_stock_int_orphaned_records_view — a specific list of stock_stg records that never reached stock_int but would resolve a Redbook key if reprocessed. The view's own comment describes it as the intended input to a scripts/stock-int-backfill/run-backfill.sh recovery script — but no such script exists anywhere in this repo as checked in. Treat the view as a genuinely useful query for finding recoverable records manually; the backfill tooling its comment describes was either never built or has been removed.