Update NTD validation models for 2024 (Blackcat)

[erikamov]

User story / feature request

As an Analyst
I want to use NTD validation models for 2024 data

Acceptance Criteria

Check if the models bellow are displaying data for the correct year (2024).

Intermediate models:

• cal-itp-data-infra.staging.int_ntd_a10_facilitiesdata
• cal-itp-data-infra.staging.int_ntd_rr20_financial_fare_revenues
• cal-itp-data-infra.staging.int_ntd_rr20_financial_specific_funds
• cal-itp-data-infra.staging.int_ntd_rr20_financial_total_exp
• cal-itp-data-infra.staging.int_ntd_rr20_service_1alldata
• cal-itp-data-infra.staging.int_ntd_rr20_service_3ratios_wide

Mart models:

• cal-itp-data-infra.mart_ntd_validation.fct_ntd_rr20_funds_checks
• cal-itp-data-infra.mart_ntd_validation.fct_ntd_rr20_service_checks

Models bellow are not in use anymore and can be dropped:

• cal-itp-data-infra.staging.stg_ntd_2022_rr20_exp_by_mode
• cal-itp-data-infra.staging.stg_ntd_2022_rr20_financial
• cal-itp-data-infra.staging.stg_ntd_2022_rr20_service

Notes

Process details and changes described on Deliverable 4 - NTD Data Orchestration Pipeline

Based on a conversation with Christian Suyat, some changes need to done for 2024:

• Add new columns on external_table_all_ntdreports.yml (need a list from him)
• Replace some hardcode years used a filters on the sql files or make them based on the current year and last year so we don't need to change it again in the future (example int_ntd_a10_facilitiesdata.sql)

Other related links: DAG, stg, int, mart models.

[erikamov]

Work in progress on 3483-ntd-validation-2024 branch.

[erikamov]

PR is ready for review.

[erikamov]

PR merged, waiting for next DAG run and update tables tomorrow.

[erikamov]

Tables were successfully updated today.

Changes are done, the only pending item is to drop the models bellow that are not in use anymore:

• cal-itp-data-infra.staging.stg_ntd_2022_rr20_exp_by_mode
• cal-itp-data-infra.staging.stg_ntd_2022_rr20_financial
• cal-itp-data-infra.staging.stg_ntd_2022_rr20_service

[csuyat-dot]

Found new column names in the Blackcat 2024 API that need to be added. See below for a list of the new columns names and attached txt file.

• NTDTransitAssetManagement
  Type
  Notes
  LastModifiedDate

• NTDAssetAndResourceInfo
  TotalVehicles
  ActiveVehicles
  DedicatedFleet
  NoCapitalReplacementResponsibility
  AutomatedorAutonomousVehicles
  Manufacturer
  DescribeOtherManufacturer
  Model
  YearRebuilt
  OtherFuelType
  DuelFuelType
  StandingCapacity
  OtherOwnershipType
  EmergencyVehicles
  TypeofLastRenewal
  UsefulLifeBenchmark
  MilesThisYear
  AverageLifetimeMilesPerActiveVehicle

• NTDReportingRR20_Rural
  AnnualVehicleRevMilesComments
  AnnualVehicleRevHoursComments
  AnnualUnlinkedPassTripsComments
  AnnualVehicleMaxServiceComments
  SponsoredServiceUPTComments

• NTDReportingTAMNarrative
  VehiclesToBePurchasesNextYear

updated external_table_all_ntdreports.yaml.txt

[erikamov]

On this last PR:

• Added new columns requested by Christian Suyat on external table all_ntdreports.
• Fixed some issues on NTD validation models:
  • Division by zero error: Added new clauses on fct_ntd_rr20_service_checks to verify if the values are not NULL or Zero before the calculation, and fixed ROUND function to use only at the end of the calculation.
  • Blank value_checked results: Replaced NULL values by " " (space) to make the Concatenation function to return the existing values on fct_ntd_rr20_service_checks, fct_ntd_a30_vomscheck.
  • Blank Organizations: Return Organization names from last year table when the Organization does not exist in the current year on int_ntd_a30_voms_vins_totals
  • No information about the year: Added year_of_data column on fct_ntd_a30_vomscheck

Note: As much as I want to fix indentation and some patterns on those files, I couldn't change much because it was making hard to see the real changes (GIT DIFF), so it could be added to next iterations.

All models were already updated by the DAG and looking good so far. Christian will keep checking.