Investigation into Sentry payments test failure: unique_fct_payments_rides_v2_micropayment_id

[charlie-costanzo]

This PR builds off of #2591 by adding more detail to unique_fct_payments_rides_v2_micropayment_id

Takeaways: number of duplicate transactions is very low, but it's unclear where they're coming from. One duplicate micropayment_id appears in our external table, while the rest only appear duplicated in the mart table. More detail below.

Notes:

• 4 micropayment_id duplicated resulting in 8 rows, but it looks like only 2 unique customers
• All transaction resulting in failing rows are from the same participant: clean-air-express
• All transactions are from the same day and hour: 2022-09-19T23:
• All have the same adjustment (DAILY_CAP)

Unexpected:

• In the external table, external_littlepay.micropayments, the only micropayment_id that's duplicated is 74de6675-3261-42f4-a101-f44de4ac9389*
• The two rows for that duplicated micropayment_id both have different extract_filenames

micropayment ids that appear in the test failing rows:
778d656f-6277-41cb-a58f-b64e8fea8d99
52e417a9-9199-4210-9c8a-78a9190c6553
3846b031-e5cf-4f2a-b044-c4226cbe21df
74de6675-3261-42f4-a101-f44de4ac9389

[charlie-costanzo]

Ex query to see duplicate micropayment_ids in mart table:

SELECT
  *
FROM
`cal-itp-data-infra.mart_payments.fct_payments_rides_v2`
    AND (micropayment_id = '778d656f-6277-41cb-a58f-b64e8fea8d99'
    OR micropayment_id = '52e417a9-9199-4210-9c8a-78a9190c6553'
    OR micropayment_id = '3846b031-e5cf-4f2a-b044-c4226cbe21df'
    OR micropayment_id = '74de6675-3261-42f4-a101-f44de4ac9389')
ORDER BY micropayment_id

Ex query to see duplicate micropayment_ids in external table:

SELECT
  *
FROM `cal-itp-data-infra.external_littlepay.micropayments`
WHERE micropayment_id = '778d656f-6277-41cb-a58f-b64e8fea8d99'
    OR micropayment_id = '52e417a9-9199-4210-9c8a-78a9190c6553'
    OR micropayment_id = '3846b031-e5cf-4f2a-b044-c4226cbe21df'
    OR micropayment_id = '74de6675-3261-42f4-a101-f44de4ac9389'
ORDER BY micropayment_id

Original failing test:

with dbt_test__target as (

  select micropayment_id as unique_field
  from `cal-itp-data-infra`.`mart_payments`.`fct_payments_rides_v2`
  where micropayment_id is not null

)

select
    unique_field,
    count(*) as n_records

from dbt_test__target
group by unique_field
having count(*) > 1

Parameters:

• participant_id = 'clean-air-express'
• transaction_time >= "2022-09-19T00:00:00.000Z" AND transaction_time < "2022-09-20T00:00:00.000Z"

[lauriemerrell]

This has been resolved by #2994 and related work