Reading borough before trusting it in ride-hail-trips

Profiling borough on ride-hail-trips before anyone builds anything on top of it.

8,000 rows, no nulls, 5 distinct values. Pickup zone: Downtown, Midtown, Uptown, Harbour, Airport.

5 values, and they are not evenly spread: Downtown 30.0%, Midtown 25.4%, Uptown 18.2%, Harbour 14.4%. The largest takes 30.0% on its own.

Read the distinct list rather than the distinct count. Casing differences and trailing whitespace produce values that look identical in a report and group separately in SQL, and the count will not show you that — select distinct borough order by 1 will, in about a second.

select
  count(*)                  as rows,
  count(borough)            as present,
  count(*) - count(borough) as nulls,
  count(distinct borough)   as distinct_values
from ride_hail_trips;

Low cardinality, stable values — this is a column you can group by without thinking about it, and a reasonable candidate for a chart facet. Check the distinct list, not just the count, because a stray casing variant hides in the count and shows up in the group-by.

Where this bites: a group-by on this column produces 5 rows today. If it is a column an upstream system can add values to, it produces an unknown number tomorrow, and any dashboard laid out for 5 categories reflows without warning. Values arriving is a schema change that no schema check catches.

The grain is one row per completed trip, which is the context every one of those numbers depends on. None of them survive a change of grain, which is why "profile the column" and "profile the table" are the same job. Full schema, and the CSV, on the dataset page.