Revenue Totals Wrong After an Upstream Type Change
SQL · schema drift · Beginner · about 15 minutes
Impact
After an upstream release changed transactions.amount from a number to text, the finance revenue query started returning wrong totals without failing.
Symptoms
- The revenue query still runs and returns a result.
- Some amounts now hold placeholders such as N/A and unknown, and some are NULL.
- The query log shows conversion warnings but no errors.
- Revenue no longer reconciles with the payments system.
Evidence
Schema change on transactions.amount
| type | |
|---|---|
| before the release | DECIMAL(12,2) |
| after the release | VARCHAR(50) |
transactions, selected rows
| tx_id | customer | amount |
|---|---|---|
| 1 | Alice | '100' |
| 2 | Bob | 'abc' |
| 4 | Dan | NULL |
| 5 | Eve | ' 500 ' |
| 6 | Frank | 'N/A' |
Row profile
| measure | rows |
|---|---|
| rows in transactions | 8 |
| amount looks like a number | 4 |
| amount is text or NULL | 4 |
Query warnings
Warning 1292 Truncated incorrect DECIMAL value: 'abc' Warning 1292 Truncated incorrect DECIMAL value: 'N/A' Warning 1292 Truncated incorrect DECIMAL value: 'unknown'
Investigation task
Return only the transactions whose amount is a valid number, with amount converted to DECIMAL(12,2): tx_id, customer, amount, ordered by tx_id.
The fix is written and graded in the regular challenge editor. The postmortem unlocks once the incident is resolved.