How alerrandrofrederik/etl_python_sqlite works
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.