How would you investigate a discrepancy between expected and actual data values?
Write a PostgreSQL query using expected_metric_values and actual_metric_values to compare values by date and metric. Aggregate multiple actual records at the comparison grain and return only records that are missing from either table or have different values.
metric_date, metric_name, expected_value, actual_value, actual_row_count, status, and variancemetric_date, then metric_name| Column | Type | Description |
|---|---|---|
| expected_idPK | INT | Unique expected-value record identifier |
| metric_date | DATE | Date associated with the metric |
| metric_name | VARCHAR(50) | Metric being validated |
| expected_value | DECIMAL(12,2) | Expected metric value |
| Column | Type | Description |
|---|---|---|
| actual_idPK | INT | Unique actual-value record identifier |
| metric_date | DATE | Date associated with the metric |
| metric_name | VARCHAR(50) | Metric being validated |
| actual_value | DECIMAL(12,2) | Observed metric value |