How hgivanrene/csv_to_postgres_pipeline works
Project type and purpose
• This is a containerized ETL (Extract-Transform-Load) data pipeline: a Python script reads a CSV file and loads it into a PostgreSQL database. Docker Compose orchestrates two services: the ETL application and the database.
File structure
• Top-level artifacts:
• Dockerfile (builds the Python ETL image)
• docker-compose.yml (defines two services: etl_app, postgres_db)
• .env (holds DB_NAME, USERNAME, PASSWORD, HOST, PORT)
• scripts/ folder:
• jobs.csv (the source data)
• main.py (pipeline logic: connect via SQLAlchemy, create DDL, read CSV with pandas, load into Postgres)
• queries/ (optional DDL or validation SQL scripts)
README for additional insights
• Confirms the use of Python, pandas, SQLAlchemy, PostgreSQL, Docker & Docker Compose.
• Describes the ETL steps and how Docker Compose brings up the containers.
Main components
a. Host / Developer Machine (runs Docker Engine & Docker Compose)
b. Docker Compose Orchestrator (logical grouping)
c. ETL Service Container
• Code: scripts/main.py
• Libraries: pandas, SQLAlchemy (psycopg2)
• Config: .env environment variables, requirements.txt
• Volume Mount: ./scripts → /app/scripts
d. PostgreSQL Container
• Data Store: database instance on port 5432
• Initialization: runs init_dataset.sql (via mounted scripts/queries if configured)
e. External Data Source (CSV file on host volume)
Relationships and interactions
• Host → Docker Compose: docker-compose up spins up containers on a bridge network.
• ETL Container ← CSV file (mounted volume): reads jobs.csv
• ETL Container ↔ PostgreSQL Container: connects over TCP (port 5432) using SQLAlchemy
• ETL Container → Database:
• Executes DDL (CREATE TABLE)
• Loads data via INSERTs/BULK COPY
• Optionally runs validation queries from scripts/queries
Architectural patterns and design principles
• Single-responsibility containers (one for ETL logic, one for data storage)
• Infrastructure as Code (Dockerfile + docker-compose.yml)
• Config-driven design (.env for credentials, easy to override)
• Modular file layout (separate SQL scripts, code, data)
Essential elements to capture
• Clear, unambiguous labels for each component
• A grouping boundary labeled “Docker Compose Network”