I'd say compared to Shapefile, it is indeed better in every aspect (to begin with, shp has 8-character column names limit). For some kinds of data and operations GPKG is superior to other geo-formats. Like 1) store a lot of data, but retreive within an area (you can set an arbitrary polygon as a filter with GDAL driver, IIRC), 2) append/delete/modify and have the data indexed -- with CSV here you'll have to just reprocess and rewrite the entire file.

The problem is that in data science you want whole datasets to be atomic, to have reproducible results. So you don't care much of these sub-dataset operations.

Another sudden issue with GPKG and atomicity is that sqlite changes DB modification time every time you just read. So if you use Makefile, which checks for updates by modification time, you either have to let it re-run some updates, or manually touch other files downstream, or rely on separate files that you `touch` (unix tool that updates file's modification time).

I read a Russian OSM blogger Ilya Zverev evangelize for GPKG back in 2016 in his blog: https://shtosm.ru. I guess he was referring to GPKG vs ShapeFile too, not CSV. I think he's totally correct in this. But look above at my other comment with a benchmark: CSV turns out far easier on resources if you have lots of points.

Back in 2017 I've made a tool that could read and write CSV, Fiona-supported formats (GeoJson, GPKG, CSV, Postgres DB), and our proprietary MongoDB. (Here's the tool, without the Mongo feature https://github.com/culebron/erde/ ) And I tried all easily available formats, and every single one has some favorable cases, and sucks at some other (well, Shapefile is outdated, so it's out of competition). Among them, FGB is kinda like better GPKG if you don't need mutations.