140 lines
4.0 KiB
Markdown
140 lines
4.0 KiB
Markdown
# The aiimi Data Product
|
|
|
|
## Maintained by: The aiimi Data Engineers
|
|
|
|
## Table overview
|
|
|
|
| Entity ID | Entity Name | Description | Entity Type | Primary Key(s) |
|
|
|-----------|-------------|-------------|-------------|----------------|
|
|
| 1 | transactions | Transaction Fact Table One line per transaction, but only transactions that have actually happened. | gold | transaction_id |
|
|
| 2 | scheduled_transactions | Scheduled Transaction Fact Table One line per transaction, but only transactions that have not actually happened. | gold | scheduled_transaction_id |
|
|
| 3 | accounts | Accounts Dimension Table Holds all the information relating to an account, type 1, 1 line per account. | gold | account_id |
|
|
| 4 | categories | Categories Dimension Table Holds all the information relating to a category, type 1, 1 line per category. | gold | category_id |
|
|
| 5 | payees | Payees Dimension Table Holds all the information relating to a payee, type 1, 1 line per payee. | gold | payee_id |
|
|
| 6 | dates | Good old fashioned Date Dimension | gold | date_id |
|
|
| 7 | accounts | Silver Accounts Table Other random information. | silver | |
|
|
| 8 | categories | Silver Categories Table Other random information. | silver | |
|
|
| 9 | payees | Silver Payees Table Other random information. | silver | |
|
|
| 10 | scheduled_transactions | Silver Scheduled Transactions Table Other random information. | silver | |
|
|
| 11 | transactions | Silver Transactions Table Other random information. | silver | |
|
|
| 12 | accounts | | bronze | |
|
|
| 13 | categories | | bronze | |
|
|
| 14 | payees | | bronze | |
|
|
| 15 | scheduled_transactions | | bronze | |
|
|
| 16 | transactions | | bronze | |
|
|
|
|
## Entity Relationship Diagram
|
|
|
|
```mermaid
|
|
erDiagram
|
|
transactions {
|
|
string transaction_id PK
|
|
int account_id
|
|
int category_id
|
|
int payee_id
|
|
int date_id
|
|
decimal amount
|
|
boolean cleared
|
|
boolean approved
|
|
boolean deleted
|
|
string memo
|
|
string flag_color
|
|
string transfer_account_id
|
|
}
|
|
scheduled_transactions {
|
|
int scheduled_transaction_id PK
|
|
int account_id
|
|
int category_id
|
|
int payee_id
|
|
str date_first
|
|
str date_next
|
|
decimal amount
|
|
string frequency
|
|
boolean deleted
|
|
text memo
|
|
string flag_color
|
|
str transfer_account_id
|
|
}
|
|
accounts {
|
|
int account_id PK
|
|
string account_name
|
|
string account_type
|
|
boolean on_budget
|
|
boolean closed
|
|
text note
|
|
decimal balance
|
|
decimal cleared_balance
|
|
decimal uncleared_balance
|
|
boolean deleted
|
|
}
|
|
categories {
|
|
int category_id PK
|
|
string category_name
|
|
string category_group_name
|
|
boolean hidden
|
|
text note
|
|
decimal budgeted
|
|
decimal activity
|
|
decimal balance
|
|
boolean deleted
|
|
}
|
|
payees {
|
|
int payee_id PK
|
|
string payee_name
|
|
boolean deleted
|
|
}
|
|
dates {
|
|
string date_id PK
|
|
date date
|
|
int year
|
|
int month
|
|
int day
|
|
boolean is_weekday
|
|
int weekday
|
|
}
|
|
scheduled_transactions ||--|| payees : "payee_id"
|
|
transactions ||--|| categories : "category_id"
|
|
scheduled_transactions ||--|| accounts : "account_id"
|
|
transactions ||--|| dates : "date_id"
|
|
scheduled_transactions ||--|| categories : "category_id"
|
|
transactions ||--|| payees : "payee_id"
|
|
transactions ||--|| accounts : "account_id"
|
|
```
|
|
|
|
|
|
## Lineage Report
|
|
|
|
```mermaid
|
|
%%{init: {"flowchart": {"defaultRenderer": "elk"}} }%%
|
|
flowchart LR
|
|
|
|
1[1 gold transactions]
|
|
2[2 gold scheduled_transactions]
|
|
3[3 gold accounts]
|
|
4[4 gold categories]
|
|
5[5 gold payees]
|
|
6[6 gold dates]
|
|
7[7 silver accounts]
|
|
8[8 silver categories]
|
|
9[9 silver payees]
|
|
10[10 silver scheduled_transactions]
|
|
11[11 silver transactions]
|
|
12[12 bronze accounts]
|
|
13[13 bronze categories]
|
|
14[14 bronze payees]
|
|
15[15 bronze scheduled_transactions]
|
|
16[16 bronze transactions]
|
|
|
|
|
|
11 --> 1
|
|
10 --> 2
|
|
7 --> 3
|
|
8 --> 4
|
|
9 --> 5
|
|
12 --> 7
|
|
13 --> 8
|
|
14 --> 9
|
|
15 --> 10
|
|
16 --> 11
|
|
```
|