Rename ntd api tables

[charlie-costanzo]

Description

Recently, we created a new data pipeline to ingest NTD annual reporting data from the DOT data portal API. The data portal API is new, so the only annual reporting data that existed within it at the time was for the year 2022.

Documentation showed no indication that future years' data would be appended to the same endpoint, so it was assumed that future years' data endpoints would be added as new Airflow DAG tasks. When 2023 reporting data was released, DOT appended the data to the 2022 data endpoint and adjusted the documentation to reflect data from the years 2022-2023.

When ingesting the initial 2022 endpoints, the naming conventions used were very 2022-centric. This PR replaces the naming conventions used in the data ingestion and external, source, and staging tables to be year-agnostic. It also replaces the tables referenced in mart tables to utilize the new table names.

Resolves #3523

Type of change

• [x] Bug fix (non-breaking change which fixes an issue)

How has this been tested?

locally with dbt

Post-merge follow-ups

• [x] Actions required (specified below) observe post-merge for expected behavior, change any outstanding/new models to use these new schemas and communicate out new schema.

[github-actions[bot]]

Warehouse report 📦

Checks/potential follow-ups

Checks indicate the following action items may be necessary.

• [ ] For new models, do they all have a surrogate primary key that is tested to be not-null and unique?

New models 🌱

calitp_warehouse.staging.ntd_annual_data_tables.stg_ntd_annualdata\_breakdowns

calitp_warehouse.staging.ntd_annual_data_tables.stg_ntd_annualdata\_breakdowns_by_agency

calitp_warehouse.staging.ntd_annual_data_tables.stg_ntd_annualdata\_capital_expenses_by_capital_use

calitp_warehouse.staging.ntd_annual_data_tables.stg_ntd_annualdata\_capital_expenses_by_mode

calitp_warehouse.staging.ntd_annual_data_tables.stg_ntd_annualdata\_capital_expenses_for_existing_service

calitp_warehouse.staging.ntd_annual_data_tables.stg_ntd_annualdata\_capital_expenses_for_expansion_of_service

calitp_warehouse.staging.ntd_annual_data_tables.stg_ntd_annualdata\_employees_by_agency

calitp_warehouse.staging.ntd_annual_data_tables.stg_ntd_annualdata\_employees_by_mode

calitp_warehouse.staging.ntd_annual_data_tables.stg_ntd_annualdata\_employees_by_mode_and_employee_type

calitp_warehouse.staging.ntd_annual_data_tables.stg_ntd_annualdata\_fuel_and_energy

calitp_warehouse.staging.ntd_annual_data_tables.stg_ntd_annualdata\_fuel_and_energy_by_agency

calitp_warehouse.staging.ntd_annual_data_tables.stg_ntd_annualdata\_funding_sources_by_expense_type

calitp_warehouse.staging.ntd_annual_data_tables.stg_ntd_annualdata\_funding_sources_directly_generated

calitp_warehouse.staging.ntd_annual_data_tables.stg_ntd_annualdata\_funding_sources_federal

calitp_warehouse.staging.ntd_annual_data_tables.stg_ntd_annualdata\_funding_sources_local

calitp_warehouse.staging.ntd_annual_data_tables.stg_ntd_annualdata\_funding_sources_state

calitp_warehouse.staging.ntd_annual_data_tables.stg_ntd_annualdata\_funding_sources_taxes_levied_by_agency

calitp_warehouse.staging.ntd_annual_data_tables.stg_ntd_annualdata\_maintenance_facilities

calitp_warehouse.staging.ntd_annual_data_tables.stg_ntd_annualdata\_maintenance_facilities_by_agency

calitp_warehouse.staging.ntd_annual_data_tables.stg_ntd_annualdata\_metrics

calitp_warehouse.staging.ntd_annual_data_tables.stg_ntd_annualdata\_operating_expenses_by_function

calitp_warehouse.staging.ntd_annual_data_tables.stg_ntd_annualdata\_operating_expenses_by_function_and_agency

calitp_warehouse.staging.ntd_annual_data_tables.stg_ntd_annualdata\_operating_expenses_by_type

calitp_warehouse.staging.ntd_annual_data_tables.stg_ntd_annualdata\_operating_expenses_by_type_and_agency

calitp_warehouse.staging.ntd_annual_data_tables.stg_ntd_annualdata\_service_by_agency

calitp_warehouse.staging.ntd_annual_data_tables.stg_ntd_annualdata\_service_by_mode

calitp_warehouse.staging.ntd_annual_data_tables.stg_ntd_annualdata\_service_by_mode_and_time_period

calitp_warehouse.staging.ntd_annual_data_tables.stg_ntd_annualdata\_stations_and_facilities_by_agency_and_facility_type

calitp_warehouse.staging.ntd_annual_data_tables.stg_ntd_annualdata\_stations_by_mode_and_age

calitp_warehouse.staging.ntd_annual_data_tables.stg_ntd_annualdata\_track_and_roadway_by_agency

calitp_warehouse.staging.ntd_annual_data_tables.stg_ntd_annualdata\_track_and_roadway_by_mode

calitp_warehouse.staging.ntd_annual_data_tables.stg_ntd_annualdata\_track_and_roadway_guideway_age_distribution

calitp_warehouse.staging.ntd_annual_data_tables.stg_ntd_annualdata\_vehicles_age_distribution

calitp_warehouse.staging.ntd_annual_data_tables.stg_ntd_annualdata\_vehicles_type_count_by_agency

DAG

Legend (in order of precedence)

Resource type	Indicator	Resolution
Large table-materialized model	Orange	Make the model incremental
Large model without partitioning or clustering	Orange	Add partitioning and/or clustering
View with more than one child	Yellow	Materialize as a table or incremental
Incremental	Light green
Table	Green
View	White