It might seem a little nuts but I’m starting a sister project for Crimson_Cache. This one will take the SQLite database of donors and ingest it as a Postgres database. It will do the same with the daily blood donations databases. And then a separate (DuckDB) database for analysis.
The idea here is I want to mirror or simulate what a real-world blood bank operations might look like. And there isn’t a lot that needs to be done with the data. Let me break down a little more:
CrimsonCache (Local Blood Bank’s database)
SQLite
- Handles daily donations and updates
- Provides ACID guarantees locally
- Lightweight and zero-configuration
- Mimics real single-location operations
Hemolytics (Regional System’s database)
SQLite → Postgres
- Represents uploading to regional database
- Models real data aggregation from multiple centers
- Handles concurrent access properly
- Natural integration with Airflow/Mage
Postgres → DuckDB (Regional System’s Analytics database)
- Analytics on aggregated regional data
- Column-oriented for efficient queries
- Great for time-series analysis
- Perfect for generating reports/metrics
I think this architecture will work for because:
- It models real-world data flow
- Has clear business logic behind each transformation
- Demonstrates ETL skills without over-engineering (there’s just no need for a full medion architecture)
- Keeps costs low while showing best practices
I’ll do a diagram and the usual “X overview” post and diagram as things get firmed up.