# 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
```