Five minutes with country in world-indicators

Someone asked what is in country on world-indicators, and the honest answer took one query.

720 rows, no nulls, 30 distinct values. Country name (synthetic panel).

30 values, and they are not evenly spread: Anselm 3.3%, Avalonia 3.3%, Belmara 3.3%, Brasilia Nova 3.3%. The largest takes 3.3% 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 country order by 1 will, in about a second.

select
  count(*)                  as rows,
  count(country)            as present,
  count(*) - count(country) as nulls,
  count(distinct country)   as distinct_values
from world_indicators;

30 values is past the point where a bar chart stays readable. Group the tail explicitly rather than letting a chart library decide which 22 categories to drop for you.

Where this bites: a group-by on this column produces 30 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 30 categories reflows without warning. Values arriving is a schema change that no schema check catches.

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