Data freshness & source health · Renuv

Monitor data pipeline health and ingestion status across all data sources.

Internal-onlyInfrastructure monitoring

Check advertising data Check search data Check retail data

Live-ready sources

1/3

Data sources with <12h lag, suitable for operational decision-making.

Stale or missing

2

Sources with >12h lag or missing expected updates; review required.

Failed pipelines

0

Ingestion pipelines with errors or authentication failures needing attention.

Data sources

Source freshness status

Source	Category	Last updated	Lag	Status	Coverage	Records	Notes
ads_sp_campaigns	advertising	4/6/2026 2026-04-06	24h 1440m	usable-stale	Current	-	fresh
orders	traffic	4/7/2026 2026-04-07	0h 0m	live-ready	Current	-	fresh
sales_traffic	retail	4/5/2026 2026-04-05	48h 2880m	usable-stale	Current	-	fresh

Source: reporting_amazon.data_freshness_status

Pipeline status

Ingestion health

Source: reporting_amazon.ingestion_pipeline_status

Data quality

Quality checks

Source: reporting_amazon.data_quality_checks

Data sources

Reporting views

Each section is backed by a named reporting view for auditability and traceability.

Data source freshness

CREATE VIEW reporting_amazon.data_freshness_status AS
SELECT
  data_source_name,
  source_category,
  MAX(last_updated_at) AS last_updated_at,
  TIMESTAMP_DIFF(CURRENT_TIMESTAMP(), MAX(last_updated_at), HOUR) AS lag_hours,
  TIMESTAMP_DIFF(CURRENT_TIMESTAMP(), MAX(last_updated_at), MINUTE) AS lag_minutes,
  CASE
    WHEN TIMESTAMP_DIFF(CURRENT_TIMESTAMP(), MAX(last_updated_at), HOUR) < 12 THEN 'live-ready'
    WHEN TIMESTAMP_DIFF(CURRENT_TIMESTAMP(), MAX(last_updated_at), HOUR) < 48 THEN 'usable-stale'
    WHEN TIMESTAMP_DIFF(CURRENT_TIMESTAMP(), MAX(last_updated_at), HOUR) >= 48 THEN 'stale-review'
    ELSE 'missing'
  END AS freshness_status,
  expected_frequency_hours,
  coverage_pct,
  record_count,
  notes
FROM reporting_amazon.ingestion_metadata
WHERE brand_id = ?
GROUP BY data_source_name, source_category, expected_frequency_hours, coverage_pct, record_count, notes
ORDER BY source_category, data_source_name;

Ingestion pipeline status

CREATE VIEW reporting_amazon.ingestion_pipeline_status AS
SELECT
  pipeline_name,
  pipeline_category,
  last_run_at,
  run_status,
  records_ingested,
  error_count,
  error_detail,
  next_scheduled_run
FROM data_ingestion.pipeline_runs
WHERE brand_id = ?
  AND last_run_at >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
ORDER BY last_run_at DESC;

Data quality checks

CREATE VIEW reporting_amazon.data_quality_checks AS
SELECT
  check_name,
  check_description,
  check_status,
  check_detail,
  last_checked_at
FROM data_quality.check_results
WHERE brand_id = ?
  AND last_checked_at >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 24 HOUR)
ORDER BY
  CASE check_status
    WHEN 'fail' THEN 1
    WHEN 'warn' THEN 2
    WHEN 'pass' THEN 3
  END,
  last_checked_at DESC;