# victor-antoniassi/junior_analytics_engineer_test_01 architecture

> Architecture diagram of the GitHub repository victor-antoniassi/junior_analytics_engineer_test_01, generated by GitDiagram from its file tree, README and a sample of source files (last updated 2025-01-22). It is an AI-made overview: check details against the source.

- Interactive diagram: https://gitdiagram.com/victor-antoniassi/junior_analytics_engineer_test_01
- Repository: https://github.com/victor-antoniassi/junior_analytics_engineer_test_01

## Overview

Key Components to Include:

##### Data Sources Layer
- Two main data source categories:
- Students Data (2021-2022)
- Schools Data (2021-2022)

##### Data Processing Pipeline

a. Quality Check Stage
- Connection to Google Sheets for manual verification
- Python/Pandas processing component
- Header validation script component

b. Data Preparation Stage
- Data cleaning scripts
- Unidecode processing component
- Manual correction workflows

c. Database Creation Stage
- SQLite database creation process
- Three main tables (educandos, escolas, escolas_educandos)

##### Output/Storage Layer
- SQLite database as the final storage solution
- Compressed (.gz) storage format

##### Processing Details
- File format transformations (CSV → TSV → SQLite)
- Where data cleaning occurs

- [Source Data] → [Processing Pipeline] → [Storage]
- ↓               ↑
- [Data Dictionaries] [Google Sheets]

##### Additional Elements
- A timeline flow from left to right
- Validation checkpoints
- Data transformation points
- Where manual interventions occur

Key Considerations:

##### Data Flow
- Clear progression from raw data to processed database
- Validation points and quality checks
- Where transformations occur

##### Processing Scripts
- Both main scripts:
- compare_delimited_file_headers
- datasets_to_sqlite
- Their dependencies

##### Quality Control
- Validation points
- Where manual corrections occur
- Data dictionary reference points

##### Final Database Structure
- Three main tables
- Relationships between tables
- Compression step

Remember to:
- Use consistent symbols throughout
- Version/year indicators for the data sources
- Both automated and manual processes clearly
- Major transformation steps

## Diagram (Mermaid)

```mermaid
graph TB
    %% Style Definitions
    classDef source fill:#b3d9ff,stroke:#333
    classDef processing fill:#90EE90,stroke:#333
    classDef manual fill:#FFE5B4,stroke:#333
    classDef storage fill:#DDA0DD,stroke:#333
    classDef validation fill:#FFB6C1,stroke:#333
    classDef dictionary fill:#F0E68C,stroke:#333

    %% Data Sources
    subgraph DataSources["Data Sources Layer"]
        Students["Students Data<br/>(2021-2022)"]:::source
        Schools["Schools Data<br/>(2021-2022)"]:::source
        DataDict["Data Dictionaries"]:::dictionary
        StdHeaders["Standard Headers"]:::dictionary
    end

    %% Quality Check Stage
    subgraph QualityCheck["Quality Check Stage"]
        HeaderVal["Header Validation<br/>Script"]:::validation
        GSheets["Google Sheets<br/>Verification"]:::manual
    end

    %% Data Preparation Stage
    subgraph DataPrep["Data Preparation Stage"]
        Cleaning["Data Cleaning<br/>Scripts"]:::processing
        Unidecode["Unidecode<br/>Processing"]:::processing
        ManualCorr["Manual<br/>Corrections"]:::manual
    end

    %% Database Creation Stage
    subgraph DBCreation["Database Creation Stage"]
        SQLiteCreation["SQLite Database<br/>Creation"]:::processing
        Tables["Database Tables<br/>- educandos<br/>- escolas<br/>- escolas_educandos"]:::storage
    end

    %% Final Storage
    FinalDB["Compressed SQLite<br/>Database (.gz)"]:::storage

    %% Relationships
    Students --> HeaderVal
    Schools --> HeaderVal
    DataDict --> GSheets
    StdHeaders --> HeaderVal
    HeaderVal --> GSheets
    GSheets --> Cleaning
    Cleaning --> Unidecode
    Unidecode --> ManualCorr
    ManualCorr --> SQLiteCreation
    SQLiteCreation --> Tables
    Tables --> FinalDB

    %% Click Events
    click Students "https://github.com/victor-antoniassi/junior_analytics_engineer_test_01/tree/master/datasets_originais/educandos"
    click Schools "https://github.com/victor-antoniassi/junior_analytics_engineer_test_01/tree/master/datasets_originais/escolas"
    click DataDict "https://github.com/victor-antoniassi/junior_analytics_engineer_test_01/tree/master/datasets_originais"
    click HeaderVal "https://github.com/victor-antoniassi/junior_analytics_engineer_test_01/blob/master/data_preparation/scripts/compare_delimited_file_headers/compare_delimited_file_headers.py"
    click GSheets "https://github.com/victor-antoniassi/junior_analytics_engineer_test_01/tree/master/data_preparation/datasets_tratados"
    click Unidecode "https://github.com/victor-antoniassi/junior_analytics_engineer_test_01/tree/master/data_preparation/datasets_tratados"
    click SQLiteCreation "https://github.com/victor-antoniassi/junior_analytics_engineer_test_01/blob/master/data_preparation/scripts/datasets_to_sqlite/datasets_to_sqlite.py"
    click FinalDB "https://github.com/victor-antoniassi/junior_analytics_engineer_test_01/blob/master/sqlite_db/escolas_educandos_sqlite.db.gz"
    click StdHeaders "https://github.com/victor-antoniassi/junior_analytics_engineer_test_01/tree/master/datasets_originais"
```
