Five minutes with city in city-air-quality

Someone asked what is in city on city-air-quality, and the honest answer took one query.

5,480 rows, no nulls, 4 distinct values. Ashfield, Bellmoor, Corvallis Bay, or Drayton.

4 values, and they are not evenly spread: Ashfield 25.0%, Bellmoor 25.0%, Corvallis Bay 25.0%, Drayton 25.0%. The largest takes 25.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 city order by 1 will, in about a second.

select
  count(*)               as rows,
  count(city)            as present,
  count(*) - count(city) as nulls,
  count(distinct city)   as distinct_values
from city_air_quality;

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 4 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 4 categories reflows without warning. Values arriving is a schema change that no schema check catches.

The grain is one row per city per day, 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.