SQLite is a great choice for doing data analytics locally.
SQLite is a robust database that has been battle tested for over a decade. It is a single file which means it is easy to set up and work with and can be backed up by simply copying the file. Not only that you also get database benefits such as efficient data storage and processing. Best of all, you get to use SQL to interact with the data.
As someone who had the pandas documentation open whenever I was using the pandas library, I get into a flow state much easier when using SQL.
SQL is a language you can take anywhere where you interact with data. So its a language you can learn once and use almost everywhere. That’s highly efficient if you ask me.
But what about big data?
I hear you. In theory SQLite database files can reach a maximum of 140TB in size. More than enough capacity for 99.99% of the kind of data analytics you would want to do locally.
And if working with a lot of data in SQLite is starting to go a bit slow, there is always the option of attaching the SQLite database to DuckDB to speed up those analytical style queries.
Which brings me on to my next point. Because of the long history SQLite has had so far, it is compatible with a huge range of programming languages and libraries. So you can share the SQLite database file and be confident knowing whatever operating system or language they use they will more than likely be able to pick up and start using it.
Pandas (among other Python libraries) can be used to get data in and out of a SQLite database. Or tools like D-tale to view and analyse data in a SQLite table using a GUI!
There are also many tools that can be used if you just want to interact directly with the SQLite database using SQL:
There are also the benefits of other database type things, such as being able to create and define views that live inside the database. These views can be part of data cleaning and processing steps readily available to anyone who grabs a copy of the SQLite file.
SQLite can also enforce datatypes on columns so no sneaky strings make it into a numeric columns
SQLite’s feature set can be extended by using extensions to enable advanced string or mathematical functions.
Or if that’s not enough, it is possible to write user defined functions when using SQLite from within a python program.
By now, hopefully you see the virtues of using SQLite to do some data analytics locally. And I’m not the only one to have this epiphany: