Ensuring Dashboard Data Integrity

How do you ensure the accuracy and integrity of data when building automated dashboards?

Write a PostgreSQL query using the supplied tables to produce a validation report for every expected site and production date.

Output

1. One row per expected site and production date
2. Columns: site_name, production_date, target_tonnes, reported_tonnes, entry_count, invalid_entry_count, duplicate_entry_count, variance_tonnes, and data_quality_status
3. Include expected dates with no production entries, order by site and production date, and classify each row as VALID, NO_DATA, INVALID, or DUPLICATE

Output details

• Order rows by ascending site_id, then ascending production_date, rather than alphabetically by site_name.
• Retain repeated expected rows with the same site and production date; do not deduplicate target records.
• Treat NULL or negative production_tonnes as invalid, exclude both from reported_tonnes, and treat missing aggregates as 0.
• Calculate duplicate_entry_count as entries minus distinct shift_code values, counting NULL shift codes accordingly.
• Use status precedence NO_DATA, then INVALID, then DUPLICATE, then VALID.
• Preserve the source numeric precision without rounding target_tonnes, reported_tonnes, or variance_tonnes explicitly.