For me it's ad-hoc analysis on large CSV files. Large meaning well beyond what Excel would be capable of, often larger than fits into memory on my local machine (10s of GiB).

Sometimes I also use xsv to just do a step of the analysis and dive deeper on some subset using pandas.

In my experience both SQLite and Pandas aren't as fast as fast for large files. So they are not really good options.

Pandas is especially bad because it uses a column oriented data structure internally so reading from or writing to CSV is incredibly slow in Pandas. If you can use parquet that's not a problem but unfortunately parquet is not nearly is ubiquitous as csv :(