Ask Littlepay about missing device transactions rows

[lauriemerrell]

In the course of investigating #3038, it became clear that one cause of missing transaction IDs in fct_payments_rides is that there are micropayments with no associated device transactions. As an analytics engineer, I want to ask Littlepay (the data vendor) how these rows should be interpreted so that I can correctly handle them in downstream models.

AC:

• Reach out to Littlepay about these rows and ask whether they should be dropped or whether they can send us new data with the missing transactions

Query to identify affected rows:

WITH mapping AS (
  SELECT 
    mp.participant_id,
    littlepay_transaction_id, 
    micropayment_id 
  FROM `cal-itp-data-infra.staging.stg_littlepay__micropayment_device_transactions`
  LEFT JOIN `cal-itp-data-infra-staging.charlie_staging.stg_littlepay__micropayments` mp USING (micropayment_id)
),

device_transactions AS (
  SELECT 
    littlepay_transaction_id, 
  FROM `cal-itp-data-infra.staging.stg_littlepay__device_transactions`
),

compare AS (
  SELECT
    device_transactions.littlepay_transaction_id IS NOT NULL AS has_device_transaction,
    mapping.littlepay_transaction_id,
    mapping.micropayment_id,
    mapping.participant_id
  FROM mapping
  LEFT JOIN device_transactions USING(littlepay_transaction_id)
)

SELECT 
  *
FROM compare
WHERE NOT has_device_transaction