# alerrandrofrederik/etl_python_sqlite architecture

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

- Interactive diagram: https://gitdiagram.com/alerrandrofrederik/etl_python_sqlite
- Repository: https://github.com/alerrandrofrederik/etl_python_sqlite

## Overview

##### Project type and purpose
- This is a batch-oriented ETL (Extract-Transform-Load) pipeline implemented in Python, loading a CSV (`producao_alimentos.csv`) through a series of incremental “job_vX.py” scripts, cleaning and enriching the data, and persisting it into a SQLite database (`dados.db` / `dsadb.db`). The end goal is to make the cleaned and transformed data available for downstream reporting (e.g., Power BI via ODBC).

a. Data Sources (File System)
- producao_alimentos.csv (root)
- demo4/producao_alimentos.csv (demo folder copy)
b. ETL Pipeline (Compute)
- job_v1.py → job_v5.py (root)
- demo4/job_v1.py → job_v5.py (under a demo run folder)
– Each script represents one pipeline stage:
1. job_v1: create DB & table, ingest raw data (fix truncated totals)
2. job_v2: filter rows (quantity > 10), drop/recreate table
3. job_v3: remove decimal separators (“.”) to prevent truncation
4. job_v4: enrich with profit margin, then round margin
5. job_v5: final rounding adjustments
c. Data Store
- SQLite database file(s): dados.db, dsadb.db
d. External Consumer
- ODBC Driver (SQLite ODBC) → Power BI Desktop

- CSV file ──(read)──> Python job_v1.py ──(write)──> SQLite table
- Python job_v1.py ──(iterative update)──> SQLite (via job_v2–job_v5)
- Final SQLite file ──(ODBC connection)──> Power BI reporting

##### Architectural patterns & principles
- Staged ETL: each job script is a separate stage, enforcing a chain of responsibility and allowing incremental development and testing.
- Idempotency: each stage drops or recreates its target table to ensure consistent state on reruns.
- Single-node, file-based database: simplicity for prototyping and local analytics.

##### Technologies & artifacts to annotate
- Python 3 (scripts)
- SQLite (embedded DB)
- CSV file (flat‐file source)
- ODBC Driver (third‐party)
- Power BI (visualization layer)

##### Detail
- File names and output DB filenames (`dados.db` vs `demo4/dsadb.db`) to avoid confusion.

## Diagram (Mermaid)

```mermaid
flowchart TD
    prod_csv["producao_alimentos.csv"]:::input
    demo_csv["demo4/producao_alimentos.csv"]:::input

    subgraph "ETL Pipeline"
        subgraph "Production"
            job1["job_v1.py: create DB & ingest raw data"]:::etl
            job2["job_v2.py: filter rows & recreate table"]:::etl
            job3["job_v3.py: remove decimal separators"]:::etl
            job4["job_v4.py: enrich with profit margin & round"]:::etl
            job5["job_v5.py: final rounding adjustments"]:::etl
        end
        subgraph "Demo"
            demo_job1["job_v1.py: create DB & ingest raw data"]:::etl
            demo_job2["job_v2.py: filter rows & recreate table"]:::etl
            demo_job3["job_v3.py: remove decimal separators"]:::etl
            demo_job4["job_v4.py: enrich with profit margin & round"]:::etl
            demo_job5["job_v5.py: final rounding adjustments"]:::etl
        end
    end

    dados_db["dados.db"]:::store
    dsadb_db["demo4/dsadb.db"]:::store
    odbc["SQLite ODBC Driver"]:::external
    powerbi["Power BI Desktop"]:::external

    prod_csv -->|read| job1 -->|iterative update| job2 -->|iterative update| job3 -->|iterative update| job4 -->|iterative update| job5 -->|write| dados_db
    demo_csv -->|read| demo_job1 --> demo_job2 --> demo_job3 --> demo_job4 --> demo_job5 -->|write| dsadb_db
    dados_db -->|ODBC connection| odbc -->|serve data| powerbi

    click prod_csv "https://github.com/alerrandrofrederik/etl_python_sqlite/blob/main/producao_alimentos.csv"
    click demo_csv "https://github.com/alerrandrofrederik/etl_python_sqlite/blob/main/demo4/producao_alimentos.csv"
    click job1 "https://github.com/alerrandrofrederik/etl_python_sqlite/blob/main/job_v1.py"
    click job2 "https://github.com/alerrandrofrederik/etl_python_sqlite/blob/main/job_v2.py"
    click job3 "https://github.com/alerrandrofrederik/etl_python_sqlite/blob/main/job_v3.py"
    click job4 "https://github.com/alerrandrofrederik/etl_python_sqlite/blob/main/job_v4.py"
    click job5 "https://github.com/alerrandrofrederik/etl_python_sqlite/blob/main/job_v5.py"
    click demo_job1 "https://github.com/alerrandrofrederik/etl_python_sqlite/blob/main/demo4/job_v1.py"
    click demo_job2 "https://github.com/alerrandrofrederik/etl_python_sqlite/blob/main/demo4/job_v2.py"
    click demo_job3 "https://github.com/alerrandrofrederik/etl_python_sqlite/blob/main/demo4/job_v3.py"
    click demo_job4 "https://github.com/alerrandrofrederik/etl_python_sqlite/blob/main/demo4/job_v4.py"
    click demo_job5 "https://github.com/alerrandrofrederik/etl_python_sqlite/blob/main/demo4/job_v5.py"
    click dados_db "https://github.com/alerrandrofrederik/etl_python_sqlite/blob/main/dados.db"
    click dsadb_db "https://github.com/alerrandrofrederik/etl_python_sqlite/blob/main/demo4/dsadb.db"

    classDef input fill:#a6f3a6,stroke:#333,stroke-width:1px
    classDef etl fill:#a6d0f8,stroke:#333,stroke-width:1px
    classDef store fill:#f8c46a,stroke:#333,stroke-width:1px
    classDef external fill:#d8a6f8,stroke:#333,stroke-width:1px
```
