19 karma · joined May 14, 2021
There was a really interesting review recently by Prof. Ben Goldacre which touches on a lot of this stuff, I recommend skimming it: https://www.goldacrereview.org/
We replaced SSMS + SQL Server with Python + SQLite run in AWS Lambda. The jobs fetch the database from S3, update with the latest deltas and write out the database and some CSV files to S3. The CSV files drive some Tableau dashboards through Athena.
The SQL usually needs a bit of a rework to make this work, but for the volumes of data we were looking at (we're talking less than a million rows, jobs run once per day) we've seen good performance at low cost. We used DuckDB for a couple of workloads which needed more complicated queries, it's stupid quick.
You should consider writing a full abstract implementation so that it's easy to implement this API for new DBs, Python's abstract base classes are great. I did something similar with the DB API PEP: https://github.com/thesketh/pep249