You use SQLite to back and operate an application. SQLite is a wonderfully lightweight transactional database; it compares more to e.g. MySQL than with OLAP systems like Spark.
SQLite isn't competing for analytical use cases :)
Managing it as separate csvs per customer allowed some incredible optimizations for fanning out processing and performing reporting and dashboarding. The process running pandas allowed us to do much nicer aggregations, pivots, filters, etc., and by not writing it in SQL, we had so much more flexibility in application code and especially in unit & integration testing code.
If data size per customer was going to grow substantially larger, we would have needed to migrate the workload to a backing SQL database, likely Postgres, but the nature of the problem meant this axis of data size was not a problem (every separate csv represented a completely isolated advertising campaign from a customer, with only up to a few million records per campaign).
Using a flask server program to do this in pandas was an aspect that really, really paid off for us.
But in many cases, yeah, simple file use is good enough, as long as important stuff is backed up somewhere / a human can easily re-upload and repair anything needed. It's a <0.1% optimization, it really only saves you noticeable effort when you're doing like millions of those operations per day.
- The parsing code for SQLite is exactly the same C code in every language, making it impossible to make mistakes in reading/writing.
- SQLite likely has stronger transactional writing ability than what your application created.
- Any SQL GUI will allow you to inspect and iterate on queries during development/production.
- SQL is a good first step to writing “join/filtering” queries and is backed by C code/indexes so should be fast for simple stuff.
Of course if you do not need any of those and are able to spend the extra time to write out your “queries” in pandas/Python that will work well too - just another way of doing the same thing.
Personally I like SQL as a first stop for prototyping, with the hope I do not need to use other tools. Joins, transactions and using the disk for state are all ”good enough” starting points, and I can take those techniques to any language I use.