31 lines
No EOL
1.4 KiB
Markdown
31 lines
No EOL
1.4 KiB
Markdown
# Data quality assessment: Verification Requests with Payment but without Bookings
|
||
|
||
This is a brief explanation of some edge cases that could incur into data quality problems - or potentially, be linked to a bug.
|
||
|
||
# Problem
|
||
|
||
During the initiative of creating business KPIs for the Guest Squad, we are in need of computing a rate between Guest Journeys with Payment and Guest Journeys Created. The assumption we had was that though there are verification requests without an associated booking (for API requests), these shouldn’t have any payments. However, we have found that these cases indeed exist, though they are relatively rare and represent a very small percentage of the total amount paid (0.3% for payments on 2024).
|
||
|
||
We need to see if this is a possible error, maybe somehow the booking records got deleted or if there is an explanation where these cases are correct.
|
||
|
||
# Backend Snippet
|
||
|
||
For further investigation on DWH side
|
||
|
||
```sql
|
||
SELECT b.id_booking,
|
||
vp.id_verification_to_payment,
|
||
vp.payment_paid_date_utc,
|
||
vp.amount_in_gbp,
|
||
vp.payment_status,
|
||
vp.id_verification_request
|
||
FROM intermediate.int_core__verification_payments vp
|
||
LEFT JOIN intermediate.int_core__bookings b
|
||
ON b.id_verification_request = vp.id_verification_request
|
||
WHERE vp.payment_paid_date_utc IS NOT NULL
|
||
AND b.id_booking IS NULL
|
||
AND vp.payment_status = 'Paid'
|
||
ORDER BY vp.payment_paid_date_utc DESC
|
||
```
|
||
|
||
 |