DbtTestFail: test.calitp_warehouse.unique_proportion_fct_vehicle_locations_grouped_0_9999__trip_instance_key.a...

[cal-itp-sentry]

Sentry Issue: CAL-ITP-DATA-INFRA-2D5C

DbtTestFail: test.calitp_warehouse.unique_proportion_fct_vehicle_locations_grouped_0_9999__trip_instance_key.ab78e501d7 - Got 1 result, configured to fail if != 0

Fix fct_vehicle_locations #3599 first and it may fix these errors too.

• not_null_fct_vehicle_locations_grouped_trip_instance_key

  Table: mart_gtfs.fct_vehicle_locations_grouped
  Column: trip_instance_key

  Failing because of 8,718,512 records with null trip_instance_key

select *
from mart_gtfs.fct_vehicle_locations_grouped
where trip_instance_key is null

• unique_proportion_fct_vehicle_locations_grouped_0_9999__trip_instance_key

  Table: mart_gtfs.fct_vehicle_locations_grouped
  Column: trip_instance_key (macro test_unique_proportion at_least: 0.9999)

  Failing because it is returning 0.008506957, need to be at least 0.9999

with validation as (
  select
    sum(case when row_number > 1 then 0 else 1 end) / cast(count(*) as numeric) as unique_proportion
  from (select
    trip_instance_key
    , row_number() over (partition by trip_instance_key) as row_number
  from mart_gtfs.fct_vehicle_locations_grouped) row_counts
),
validation_errors as (
  select
    unique_proportion
  from validation
  where unique_proportion < 0.9999 or unique_proportion > 1
)
select
  *
from validation_errors

[cal-itp-sentry]

Sentry issue: CAL-ITP-DATA-INFRA-2D5B