# mjeisenh/trackman-data-pipeline architecture

> Architecture diagram of the GitHub repository mjeisenh/trackman-data-pipeline, generated by GitDiagram from its file tree, README and a sample of source files (last updated 2025-10-23). It is an AI-made overview: check details against the source.

- Interactive diagram: https://gitdiagram.com/mjeisenh/trackman-data-pipeline
- Repository: https://github.com/mjeisenh/trackman-data-pipeline

## Overview

##### Project Type and Purpose
- Type: A data ingestion pipeline (ETL) built in Python.
- Purpose:
- Securely connect via FTPS to a remote Trackman server
- Download previous-day CSV folders (YYYY/MM/DD)
- Clean and transform the CSVs (add IDs, convert types)
- Batch-upload to an Azure SQL database
- Run automatically daily via cron

##### Main Components
a. External FTPS Server (Trackman)
b. Scheduling Layer (cron on a host/VM)
- Scripts/csv_to_db.py
- Scripts/ftp_csv_downloader.py
d. Local Storage (optional intermediate folder for CSVs & logs)
e. Azure SQL Database
f. Logging Output (timestamped log files)

##### Relationships and Data Flows
- cron → (at 06:30) → ftp_csv_downloader.py
- ftp_csv_downloader.py → FTPS Server:
- Authenticate via FTPS → Download yesterday’s folder of CSVs → Save to Local Storage
- cron → (at 06:45) → csv_to_db.py
- csv_to_db.py → Local Storage:
- Read CSVs → Clean/transform data (SQLAlchemy + ODBC Driver) → Batch‐insert into Azure SQL DB
- Both scripts → Logging subsystem → Write timestamped log files

##### Architectural Patterns and Design Principles
- Modular ETL: Separate “extract” (downloader) from “load” (db uploader).
- Idempotency: Scripts operate on date partitions; can be re-run safely.
- Externalized Configuration: `.env.example` for credentials + SQL connection strings.
- Scheduling as infrastructure (cron).
- Error handling & logging: Each script logs progress & errors.

- The flow from remote FTPS to local host
- The separation of concerns between downloader and uploader
- The interaction with the Azure SQL database
- The role of scheduling and logging

This level of detail ensures any new team member or stakeholder can quickly grasp how data moves through your pipeline, where points of failure may occur, and which technologies are in use.

## Diagram (Mermaid)

```mermaid
flowchart TB
    %% External Systems
    FTPS["FTPS Server"]:::external
    Azure["Azure SQL Database"]:::external

    %% Storage
    LocalStorage["Local Storage<br/>(CSV folders YYYY/MM/DD)"]:::storage
    Logs["Logging Output<br/>(timestamped log files)"]:::storage

    %% Scheduler
    Scheduler["Cron Scheduler<br/>(06:30 & 06:45)"]:::scheduler

    %% Host/VM Compute
    subgraph "Host/VM (Linux/macOS, Python 3.8+)" 
        direction TB
        Downloader["ftp_csv_downloader.py"]:::process
        Uploader["csv_to_db.py"]:::process
        Env[".env.example"]:::config
        Req["requirements.txt"]:::config
        Readme["README.md"]:::docs
    end

    %% Data Flows
    Scheduler -->|06:30| Downloader
    Scheduler -->|06:45| Uploader

    Downloader -->|"FTPS over TLS"| FTPS
    FTPS -->|"Download CSVs"| LocalStorage

    LocalStorage -->|"Read CSVs"| Uploader
    Uploader -->|"Insert via SQLAlchemy/ODBC"| Azure

    Downloader -->|"Write logs"| Logs
    Uploader -->|"Write logs"| Logs

    %% Config dependency
    Env -.-> Downloader
    Env -.-> Uploader
    Req -.-> Downloader
    Req -.-> Uploader

    %% Click Events
    click Downloader "https://github.com/mjeisenh/trackman-data-pipeline/blob/main/Scripts/ftp_csv_downloader.py"
    click Uploader "https://github.com/mjeisenh/trackman-data-pipeline/blob/main/Scripts/csv_to_db.py"
    click Env "https://github.com/mjeisenh/trackman-data-pipeline/blob/main/.env.example"
    click Req "https://github.com/mjeisenh/trackman-data-pipeline/blob/main/requirements.txt"
    click Readme "https://github.com/mjeisenh/trackman-data-pipeline/blob/main/README.md"

    %% Styling
    classDef external fill:#add8e6,stroke:#333,stroke-width:1px
    classDef process fill:#90ee90,stroke:#333,stroke-width:1px
    classDef storage fill:#d3d3d3,stroke:#333,stroke-width:1px
    classDef scheduler fill:#fffacd,stroke:#333,stroke-width:1px
    classDef config fill:#ffebcd,stroke:#333,stroke-width:1px
    classDef docs fill:#e0ffff,stroke:#333,stroke-width:1px
```
