Duplicate Customers After an Ingestion Retry
SQL · reliability · Intermediate · about 20 minutes
Impact
The customer table grew overnight and some customers received the same marketing email two or three times.
Symptoms
- The nightly ingestion job failed part-way, was retried automatically, and then succeeded.
- Several rows share the same name, email and phone but have different customer_id values.
- The primary-key constraint raised no error.
- Not every repeated name is a duplicate: two customers called Dan have different phone numbers.
Evidence
Ingestion log
02:00:04 ingest_customers attempt=1 ERROR connection reset after partial commit 02:03:10 ingest_customers attempt=2 ERROR connection reset after partial commit 02:06:15 ingest_customers attempt=3 status=SUCCESS
Row counts after the run
| measure | value |
|---|---|
| rows in customers | 8 |
| distinct (customer_name, email, phone) | 5 |
customers, selected rows
| customer_id | customer_name | phone | |
|---|---|---|---|
| 101 | Alice | alice@mail.com | 555-3311 |
| 103 | Alice | alice@mail.com | 555-3311 |
| 106 | Alice | alice@mail.com | 555-3311 |
| 107 | Dan | dan@mail.com | 555-3344 |
| 108 | Dan | dan@mail.com | 555-3399 |
customers schema
| column | type | note |
|---|---|---|
| customer_id | INT | primary key, assigned on every insert |
| customer_name | VARCHAR | |
| VARCHAR | ||
| phone | VARCHAR |
Investigation task
Return one row per real customer, keeping the original record (the one with the smallest customer_id): customer_id, customer_name, email, phone, ordered by customer_id.
The fix is written and graded in the regular challenge editor. The postmortem unlocks once the incident is resolved.