Research Task - Test New Reconciliation Table

[amandaha8]

Research Task

Laurie created a new table Fct Payments Aggregations Reconciliation. Test this new table by recreating the reconciliation dashboard for MST. Give Laurie feedback by April 17.

UPDATE: cal-itp/data-infra#5130 was merged in, fct_payments_aggregations is now updated with the new columns,

Need to test/remake the questions using the updated table to see if they match with the initial SQL tables.

• Data Required:

• Fct Payments Aggregations Reconciliation

• Expected Outputs / Findings: The duplicate reconciliation dashboard using the new table that has the same results as the original one.

[csuyat-dot]

Discussed with Amanda.

• looked at issue #4699. explained that the new table should replace the metabase sql tables
• tried to compare the columns from fct_payments_aggregations_reconciliation to the metabase sql tables (All Settlements, Main Refunds, Main Sales` >>some columns names dont match 1:1 so will need to test which columns in the new table are equivient to the old tables

Will try to recreate some of the reconciliation tables using the new fct table. Specifically:

• recreate a reconciliation question that uses the all settlements table
• recreate a reconciliation question that uses the main sales table
• recreate a reconciliation question that use the main refunds table
• note any columns that are missing that prevents making any of the questions. (i.e. need RRN, micropayments_in_aggregation columns to complete the question.

[csuyat-dot]

Per email from Laurie

I am hoping that you all can give it a test by the end of next week (April 17) and see if it covers what you need for the Reconciliation dashboards

[amandaha8]

I tried to use the new table to recreate a couple of questions but none of the results matched. https://caltrans.sharepoint.com/:b:/s/DOTPMPHQ-DataandDigitalServices-PaymentsSupportGroup/IQDl9zL8R2EiSp1gwgQUcvKvAbVf-ZFlr1CtvfmYzJQYINo?e=wzTrJY

[csuyat-dot]

Laurie provide a crosswalk from the initial metabae base SQL tables to the new table.

https://caltrans.sharepoint.com/:x:/s/DOTPMPHQ-DDSContractors/IQCoBxZWaKDHRaquRFiCpBorASSbL2DzX-xIiu11ODgOX80?e=Cmj7dC

[amandaha8]

Can't find the equivalent of payment_date column in the new table. Laurie said this is a different table that is not being incorporated into the new table.

[amandaha8]

Having trouble getting the filters to work on this page, all the questions are null.

[amandaha8]

I can't find total_nominal_amount_dollars in the new table. Is it total_micropayment_nominal_amount_dollars?

The amounts for these two queries differ, although not by too much?

[amandaha8]

New table doesn't have the "unavailable" option.

[amandaha8]

The nominal amount in the "unsettled sales" tab don't match.

[amandaha8]

Having getting charge amounts to match.

[amandaha8]

Still having trouble getting the values in the "unsettled" tab to match, but otherwise everything looks fine.