Glueing together Python, Postgres, and Metabase.
Don't forget to delete postgres-data/
if you want to wipe everything and start fresh.
-
Transform data from multiple sources into a single source
-
Idempotent ingestion of data from sources
Fetch by month:
- Drop all data from that month from that source
- Re-import data
- Re-clean/transform data
-
Data model? What should a raw[_umcu]_transaction -> transaction flow look like? What's the end goal data model?
Transaction:
id: int
account: str (or id)
person: str (or id)
amount: num
new_balance: num
date: datetime
description: # str
second_party: str
Apache Airflow to schedule these as discrete steps Or just run them manually for now.
Will need custom scrapers for each source. End result will either be in-memory data, JSON, or CSV.
Translate data into a common format. Must determine specification of data model.
# Transaction Sources
Id, SourceName, AccountName, Account#, AccountType
# Transactions
Id, SourceId, Date, Description, Amount, Balance, Note, Raw_Description, CategoryId
# Category
Id, ParentCategoryId, Name
If the input data has extra jargon, try to remove the noise here.
For Example: "Purchase TST* QUARANTINOS 555-555-5555 MI Date 05/06/24 12345678901234567890123 5555 %% Card 52 #4321 MEMO Balance Change -$37.48"...
...Should be reduced to something like "Purchase TST* QUARANTINOS 555-555-5555 MI"
* Date is already tracked in `"Date"` column
* Long numbers are not relevant (What are they?)
* Card Info should not be duplicated on each transaction.
* This info could be stored more efficiently in a separate account/card info table.
* Balance Change is already tracked in `"Amount"` column
Save the cleaned data to a store.
Manually Or ML? Or Rules-based? (regex, keywords, etc.)
# Category Rules
Id, RuleJsonBlob
// RuleJsonBlob JSONSchema
{
"type": "object",
"properties": {
"Name": {"type": "string"},
"Description": {"type": "string"},
"CategoryId": {"type": "integer"}
"Rule": {
"type": "object",
"properties": {
"Type": {"type": "string", "enum": ["regex", "keyword"]},
"Value": {"type": "string"},
},
},
}
}
Metabase or Apache Superset or Others